The day has come when DBA teams can no longer afford to ignore Oracle Enterprise Manager Grid Control (current release being 10g Release 5 at the time of this writing). It is not practical to do so, due to the immense complexity of today’s Oracle enterprise database software. The core database facilities are becoming more and more sophisticated, and it is extremely tedious to use only the command line to manage these complex options. Some DBAs may write and maintain scripts to do so; others may buy scripts from the market, and such scripts may not be guaranteed to work for new releases of the database. Thus, they have to be endlessly tested and maintained.
Furthermore, Oracle strongly recommends the use of Oracle Grid Control for managing sophisticated options like its fully active-active clustered database option, Oracle Real Application Clusters (Oracle RAC). This is so especially in the area of analyzing cluster performance, which is greatly aided by Oracle Grid Control.
In this article, you will learn the traditional use of UNIX shell scripts and crontab to set up and schedule Oracle Recovery Manager (Oracle RMAN) backups, including a technical explanation of the supplied scripts (in Part I). You will also see how the same end result of setting up and scheduling Oracle RMAN backups can be achieved seamlessly and easily via Oracle Grid Control, without using any UNIX shell scripts or cron (in Part II). The demonstration presented in this article is drawn from the real-life experiences of a DBA working at a large corporate site, who used both approaches in his career history.
Background
When the earliest Oracle databases were being backed up, they were offline (“cold”) backups. The DBA manually performed the backup as an OS file copy of all database files after shutting down the database. Some enterprising administrators started writing UNIX shell scripts to do the work of shutting down the database, copying the files using OS commands, and then starting the database again. The traditional UNIX scheduler cron was used to call the script at the appropriate time. So there was some level of automation, if one discounted the manual effort of writing, implementing, and testing the scripts. They also had to maintain the scripts for changes—the database name might change, or more databases might appear on the same server that would need to be backed up, so the scripts would need to be modified.
In Oracle Database Version 6, online (“hot”) backups were introduced. This meant that the database could stay open and process transactions at the same time as the backup was being performed. But the caveat was that each tablespace had to be placed in a special backup mode before the OS file copy could take place. So, the DBAs modified their scripts to connect to the database, get a list of tablespaces, put each tablespace in backup mode, perform the OS file copy backup, and finally take the tablespaces off backup mode. The scripts were becoming slightly more complex.
Eventually in Oracle Database Version 8, there was a groundbreaking event—Oracle Recovery Manager (RMAN) was introduced as the recommended backup method for Oracle databases. However, even though it was technically superior to the older backup mode online backups, there was some resistance to Oracle RMAN due to Oracle’s initial requirement for a separate catalog database to hold a history of the backups. Many DBAs thought it was illogical to have a second database to back up the first database.
Oracle realized this and soon made it possible to use the control file of the database itself to hold the history of the backups. This reduced the reliance on a separate catalog database. In Oracle Database Versions 8i, 9i, and 10g, the result was that the control file method of Oracle RMAN backups started being accepted by mainstream DBAs, and Oracle RMAN started being used to a greater extent on production databases.
However, because UNIX shell scripts and cron had been used in the past to execute and schedule offline and online database backups, Oracle RMAN inherited this history. The older UNIX shell scripts were modified to connect to Oracle RMAN and perform the backup. There was no need any longer to put the tablespaces in backup mode, but everything else remained the same.
So, for a number of years, the traditional approach to Oracle’s RMAN utility was exactly the same as before: the use of UNIX shell scripts and cron.
In this age of sophisticated database management tools such as Oracle Grid Control, this may seem archaic, but there are a large number of companies who still use a shell script to call Oracle RMAN, and there are some who use layer upon layer of shell scripts.
Oracle purposely simplified the Oracle RMAN syntax to make the job of backup and recovery much easier, but the purpose is defeated if the Oracle RMAN commands are hidden under sublayers of OS shell scripts. Even experienced DBAs who walk into these large companies find it difficult to understand the customized shell scripts, although they know how to use Oracle RMAN.
Thus productivity in such companies is reduced, and the act of debugging errors is made much more difficult due to the script layers. And if the scripts are to be modified in the future to cater to changed requirements, there are high maintenance costs.
Next, on to the comparative approaches.
Part I: The Traditional Approach
For the purpose of our demonstration, the production server is proddb001 and the database on this server is FIN1P, a production Oracle database with the company’s financial information. The DBA has been asked to set up and schedule Oracle RMAN backups for this database. The DBA writes the shell scripts and places them on the server following the setup steps below.
These steps must be followed for every database server requiring Oracle RMAN backups for its databases. If the databases are on an active-passive cluster—for example, a SUN HA cluster or any such technology—then the steps would be followed for each server.
- As the root UNIX user add the line “oracle” to /etc/cron.d/cron.allow in order to allow the oracle UNIX user to use the cron utility in UNIX.
- As the oracle UNIX user, add the following to the crontab:
30 21 * * * dba/scripts/rman_backup_db.sh FIN1P
As per crontabl syntax, this calls the rman_backup_db.sh script at 21:30 hours on each day. The script is asked to execute against the FIN1P database by specifying this database name as the first and only argument.
- Log on as a DBA to database FIN1P in SQL*Plus and create an externally identified user :
create user ops$oracle identified externally; grant dba to ops$oracle;
This has the effect that the Oracle UNIX user can log on to SQL*Plus as the Oracle database user without specifying the password—in other words, the user is identified externally.
This is the technique used most often with scripts that log in to the database, especially those that require DBA rights for database-level backups. It is not a good idea to hard-code database passwords in scripts, because the scripts are UNIX files and may be readable by anyone on the computer (unless the file permissions are locked down). So as a safety precaution, an externally identified Oracle user can be used to log in as the DBA and perform the backup. This circumvents the need for specifying the database password in the script.
- In this approach you would use a Filer volume for the Oracle RMAN backups. Database standards for the corporate environment maintain that the backup volume must be mounted as /U99 at the server level.
In /etc/vfstab, make sure that there is an entry for the backup mount point:
ausmelb-corp-netappsfiler-tier3:/vol/vol1/dbbackup - /U99 nfs - yes hard,vers=3,intr,suid,proto=udp,rsize=32768,wsize=32768
NetApp documentation should be consulted on the appropriate mount options, because these may differ as per the UNIX flavor of the host, the version of the filer, and the technology in use.
If there is no such entry, consult with the storage department in corporate IT and ask them to allocate a volume to the server and add it to this file. The mount point will now be mounted every time the server is rebooted, or it can be mounted manually using the mount command as the root UNIX user:
mkdir /U99 mount /U99
- As the root UNIX user, execute the following at the UNIX command prompt:
chown –R oracle:dba /U99
This is to make sure that the /U99 backup mount point and all subdirectories (as specified by the –R argument) under this mount point are owned by the oracle UNIX user and dba UNIX group, so Oracle RMAN is able to create backup pieces under this mount point.
- Assuming that /home/oracle is the Oracle UNIX user’s home directory, perform the following steps as the Oracle UNIX user:
mkdir /home/oracle/dba mkdir /home/oracle/dba/scripts mkdir /home/oracle/dba/logs mkdir /home/oracle/dba/work mkdir /U99/FIN1P mkdir /U99/FIN1P/rmancmd mkdir /U99/FIN1P/log
These commands create the UNIX subdirectories into which the UNIX script is placed and to which the logs are written, as well as creating temporary working directories and the directories for the generated Oracle RMAN command script and Oracle RMAN runtime log file.
- Under the subdirectory /home/oracle/dba/scripts, create the file rman_backup_db.sh using vi or any UNIX editor. The script, when executed every day at the specified time set in cron, will generate the following Oracle RMAN command file /U99/FIN1P/rmancmd/rman_FIN1P.cmd:
# # Creation Porus Homi Havewala 07/06/2008 # # Configure RMAN settings CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS; CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '$BACKUPDIR/cf_%F'; CONFIGURE BACKUP OPTIMIZATION ON; CONFIGURE DEVICE TYPE disk PARALLELISM 3; CONFIGURE DEFAULT DEVICE TYPE TO disk; CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '$BACKUPDIR/b_%U'; CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '$BACKUPDIR/b_%U'; CONFIGURE CHANNEL 3 DEVICE TYPE DISK FORMAT '$BACKUPDIR/b_%U'; CONFIGURE SNAPSHOT CONTROLFILE NAME TO '$BACKUPDIR/snapcf_${DBNAME}.f'; # Perform backup of database and archivelogs, deleting backed up archivelogs BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT; # Maintainance commands for crosschecks and deleting expired backups ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK; CROSSCHECK BACKUP; DELETE NOPROMPT EXPIRED BACKUP; DELETE NOPROMPT OBSOLETE DEVICE TYPE DISK; CROSSCHECK ARCHIVELOG ALL; DELETE NOPROMPT EXPIRED ARCHIVELOG ALL; # End of RMAN command script
As can be seen from the Oracle RMAN commands, a recovery window of three days is specified as the Oracle RMAN retention policy, which means that all backups necessary to recover up to the last three days of data will be retained. Obviously this setting may need to be changed, depending on the size of the database and the free space available in the backup mount point /U99 at any point. The DBA needs to closely monitor this space and change the recovery window if necessary.
Control file autobackup is also configured, so the control file will be automatically backed up along with every database or archive log backup. This is especially important because we are using the nocatalog mode of Oracle RMAN, so all the history of our backups is only in the control file.
A parallelism setting of 3 is used, which means that three Oracle RMAN channels will be used to actually create the backup pieces. This will increase the speed of the backup, depending on the structure of the disk subsystem.
A full backup of the database and the archive logs is then performed, and the archive logs that are backed up are deleted immediately by Oracle RMAN due to the delete input clause.
After the backups are completed, maintenance jobs are performed that cross-check the existing backups and archive logs and make sure that they are present on-disk. All expired (not found) and obsolete (as per the retention policy) database, archive log, and control file backup entries are deleted from the control file records. The maintenance commands also ensure that the obsolete database backups and control file autobackups are physically deleted from the disk.
- Make the rman_backup_db.sh script executable at the UNIX level by setting the user-executable file permission:
cd /home/oracle/dba/scripts chmod u+x rman_backup_db.sh
- Test out the rman_backup_db.sh script by calling the script from the UNIX command prompt manually, instead of waiting for the cron scheduled execution to take place:
cd /home/oracle/dba/scripts ./rman_backup_db.sh FIN1P
This should execute the Oracle RMAN command script on the FIN1P database and take a complete database and archive log backup. After the backup is completed, inspect the Oracle RMAN log file generated to make sure that there are no errors and that the backup has completed successfully with Oracle RMAN backup pieces created in the backup directory /U99/FIN1P.
- On the next day, make sure that the scheduled Oracle RMAN backup in the UNIX cron has been started and completed successfully. If not, correct the cron entry.
Conclusions
As you can see, there are a number of manual steps in the traditional approach. The DBA using this approach estimates that two to three hours will be required to set up and test the Oracle RMAN backup in this manner, on every new provisioned computer.
Of course a rush job can be performed, but because rushing is more prone to human error, management takes two hours as the final estimate. This is the time consideration.
If there are a number of Oracle RMAN deployments happening on projects every week in a large corporate site, the time quickly adds up to a number of expensive DBA man-days.
The other consideration is the maintenance aspect of the UNIX shell scripts. Management must retain DBAs who are familiar with UNIX shell scripting. In some cases, the initial scripts have been written in Perl. So there would be a need for familiarity with that language. Because the choice of scripting language is open, there may be sites using other scripting languages as well. Also, scripts may be written in totally different ways to do the same job, because there are no scripting standards in place. The code may be totally uncommented or sparsely commented, and there may be missing or incomplete documentation. Everything is up to the initial script writer. New DBAs inherit the scripts and must try their own hand at fixing things, spending many hours understanding the scripts, experimenting with the code, and making any future maintenance changes. These are very common scenarios in the real world.
Oracle Enterprise Manager Grid Control aims to eliminate most of these issues. Next, we will see how to set up and schedule Oracle RMAN backups in a straightforward, no-nonsense way using the vast capabilities of Oracle Enterprise Manager Grid Control.
Part II: The Oracle Enterprise Manager Grid Control Approach
As noted previously, the major benefit of this approach is the time savings achieved in the tasks of setting up, testing, and day-to-day maintenance of the Oracle RMAN backups. Removing the dependence on UNIX-level scripts eliminates the necessity of hiring and retaining shell script specialists. Using Oracle Grid Control results in greatly simplified debugging of errors, because there are no shell scripting issues—the DBA can concentrate solely on the Oracle RMAN commands. Future maintenance and changes to the Oracle RMAN backups will be easier.
This full comparison of both approaches, when demonstrated in the real world, has already convinced many major companies and financial institutions to consider the use of Oracle Grid Control for Oracle RMAN backups and other database management tasks.
For the purpose of this demonstration, the production database is FIN1P—a production Oracle database containing the company’s financial data. The DBA’s task is to set up and schedule Oracle RMAN backups for the database.
The DBA has access to a central Oracle Grid Control site for managing and monitoring the multiple database targets for which his/her team has responsibility. The presumption is that the Oracle Grid Control site has been deployed using best practices and scalable architecture, so it can handle multiple database management tasks such as Oracle RMAN backups, Oracle Data Guard setup and monitoring, database cloning, and so on.
For the appropriate architecture, see my recently published article on Oracle Technology Network, “Grid Control Architecture for Very Large Sites”, which explains how a large central Oracle Grid Control site was able to manage and monitor 600 to 700 targets including databases, servers, and listeners.
It is always preferable to use the latest version of Oracle Grid Control at the central site. To find the exact version of Oracle Grid Control installed, go to the Oracle Grid Control home page and select the About Oracle Enterprise Manager link at the bottom of the page. In the case of this demonstration, the version is Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0 which was the latest version available at the time of installing the demonstration.
Often security is cited as a concern for a central management site; what if one group of DBAs gains access to production databases for which they are not responsible? Oracle Grid Control, however, is sophisticated enough to allow the creation of multiple administrator logins who have access to specific groups of targets. This allows multiple database teams in the corporate environment to log in and only have management access or view access to their own group of targets—whether it be database, application server, host server, or listener.
Thus potentially any of the DBA team members responsible for the FIN1P database could log in to the Oracle Grid Control console with their administrator login and would have Oracle Grid Control management rights to the FIN1P database, host, and associated listener targets.
Preinstall Tasks
Initially, there are a few preinstall tasks required to set up the environment, as follows:
- A separate Filer volume will be used for the Oracle RMAN backups. As per corporate database standards, the backup volume must be mounted as /U99 at the server level. The entry for the backup mount point should be present in /etc/vfstab, and the mount point should appear in the list when the df –k (disk free in Kilobytes) command is executed at the UNIX prompt. If not, follow the UNIX-level steps as the root UNIX user, to add the entry in /etc/vfstab, and then mount the volume as /U99. Also change the ownership via this command:
chown –R oracle:dba /U99
This is to ensure that the /U99 backup mount point and all subdirectories (as specified by the –R argument) under this mount point are owned by the Oracle UNIX user and DBA UNIX group, so that Oracle RMAN is able to create backup pieces under this mount point.
- The Oracle Enterprise Manager Agent is preinstalled on each database server that is to be accessed via Oracle Grid Control. The most popular method to perform this install is via the pull method, which uses an AgentDownload script and the wget utility to pull the Agent install files from the central site directly onto the server. Other common methods are the push method, as well as the normal GUI installation using the CD or software directly on the target server.
Ideally, if the Provisioning Pack of Oracle Grid Control is used, it is possible to keep a “Gold Copy” of Oracle installations, in this case the Agent can already be preinstalled in the gold copy. The gold copy is then provisioned onto new servers.
The Agent is to be installed in its own Agent home on the server, for which at least 2GB of space should have been allocated. Once installed and configured, the Agent starts to upload XML files to the central Oracle Management Service (OMS) server, and metrics about the targets on this database server begin to be stored in the central Oracle Grid Control repository.
- In Oracle Grid Control, a collection of associated targets is called a target group. This is normally used for grouping together targets to be associated with a particular corporate department, application, or DBA team.
Such a target group, FINGRP, has been created by the Oracle Grid Control owner SYSMAN (password-controlled by the super-administrator of the central Oracle Grid Control site, normally a DBA of the central database team). This target group contains the FIN1P database, its listener, and also the host the database runs on.
A new Oracle Grid Control administrator, FINADM, has also been created. This administrator is assigned full management privileges over the target group FINGRP.
The following steps must now be followed for the database requiring Oracle RMAN backups to be set up and scheduled. If the database is on an active-passive cluster—for example, a SUN HA cluster or any such technology—then these steps would be followed for each server, because the database could potentially run on either of the nodes in this active-passive technology and have a different target name for each node in Oracle Grid Control. In the case of Oracle RAC there is no such issue, because there is only one database shared by both or multiple nodes, and it can be backed up easily and efficiently by Oracle RMAN.
Setup Steps
- Log in to the Oracle Grid Control console using the FINADM login. You are able to see only the targets for which your team is responsible. Move to the Targets tab, click Databases, and then select the FIN1P database. This now displays the FIN1P database home page.
The name of the user logged on to the console is clearly visible in the topmost bar of the browser as Oracle Enterprise Manager (FINADM) – Database Instance FIN1P.
- Select the Maintenance tab. On this tab, under High Availability, the options visible are Backup/Recovery, Backup/Recovery Settings, Oracle Secure Backup, and Data Guard. Select Backup Settings under Backup/Recovery Settings. At this point, the Oracle Grid Control login FINADM is asked to log in to the actual database itself.
Log in as a user with DBA privileges, to set up and schedule the Oracle RMAN backup. It is not required to log in with SYSDBA or SYSOPER rights unless the intention is to start up, shut down, perform complete or incomplete recovery, change the archive log mode, or perform other such database administrative work. Connect as NORMAL instead.
Do not check Save as Preferred Credential,because that would mean the login would be available without a password to any DBA who has logged on to the console as FINADM.
Note that this procedure signifies that there are two levels of security in Oracle Grid Control—the first level to log in to the console and the second to log in to the database itself.
- Backup Settings is now displayed as three tabs: Device, Backup Set, and Policy. Under Device, enter 3 as the Parallelism (concurrent streams to disk drives). This means that three Oracle RMAN channels will be used to actually create the backup pieces. This will increase the speed of the backup, depending on the structure of the disk subsystem.
The Disk Backup Location is not specified, so it uses the flash recovery area set up at the database level for the purpose of the backup. In this case, we presume that the flash recovery area of the database has been set up as /U99 as per the corporate standard.
- The Disk Backup Type has three radio buttons. You can ask for either a Backup Set, a Compressed Backup Set, or an Image Copy. Compressed Oracle RMAN backups were first made available in Oracle Database 10g. Select this option for the most optimal use of backup space.
- Enter the host credentials on the lower portion of this tab, and check Save as Preferred Credential. For a database based on a UNIX host, use the Oracle UNIX user. For a windows host, use
\ to log on.
The logon credentials in Windows occasionally do not work, even though\ is used. The solution is to add the Log on as a Batch Job privilege to the user in the Windows Control Panel -> Administrative Tools -> Local Security Policy -> Local policies -> User rights assignment -> Log on as a Batch Job, and add the Windows user that is being used in the host credentials.
Note that the menu path mentioned here pertains to Windows XP Professional and may be slightly different for other Windows versions. This solution is as per Oracle MetaLink Note 109188.1.
- To make sure that the host credentials work correctly, click Test Disk Backup at the upper corner of the screen. This runs a physical backup test using Oracle RMAN and verifies that the actual backup can be physically created at the disk backup location, which is the flash recovery area—/U99 in this case. The backup test runs and, when completed successfully, displays the message Disk Backup Test Successful! at the top of the Backup Settings -> Device tab.
- Move to the Backup Set tab. At this point, nothing is to be changed on this tab, which specifies the maximum backup piece (file) size and certain tape settings such as copies of datafile and archive log backups. These settings are kept at the default.
- Move to the Policy tab. This is where the backup policy is specified, and it is particularly important for the control of Oracle RMAN backups.
First check the box to Automatically backup the control file and server parameter file (SPFILE) with every backup and database structural change.
This is the control file and SPFILE autobackup. You can specify the location of the autobackup or default to the flash recovery area /U99. Checking this option is highly recommended so that the control file and SPFILE will be automatically backed up along with every database or archive log backup. This is especially important because we are using the nocatalog mode of Oracle RMAN, so the history of our backups is in the control file. This is also another reason that the binary SPFILE is recommended for every database instead of the earlier text PFILE, because the SPFILE can easily be backed up by Oracle RMAN in this way.
Check Optimize the whole database backup by skipping unchanged files such as read-only and offline datafiles that have been backed up.This is known as backup optimization.
Check Enable block change tracking for faster incremental backups. This is a new feature inOracle RMAN 10g that keeps track of all changed blocks in a small file (approximately 11MB) so that incremental backups do not need to scan the whole database for block changes, as was the case in Oracle9i Database. This technique results in a much faster incremental backup, making a compelling reason to upgrade to the latest versions of the database.
For the block change tracking file, specify the location and filename as D:\ORADATA\FIN1P\BLKCHGTRCK.DBF. This must be specified because the database area is not set for the FIN1P database.
Under Retention Policy, select Retain at least the specified number of full backups for each datafileand specify 1 as the number of backups to retain. This means that the retention policy is based on redundancy instead of a recovery window.
The redundancy is specified in terms of the number of backups to keep at any one time. If a new backup is made, then the previous backup is marked as obsolete and can be deleted. The recovery window is specified in terms of the number of days; backups are retained so that recovery is possible up to this many days in the past. For example, if a recovery window of 3 days is specified as the Oracle RMAN retention policy, it means that all backups necessary to recover up to the last three days’ worth of data will be retained. Obviously this setting may need to be changed depending on the size of the database and the free space available in the backup mount point /U99 at any time. The DBA needs to closely monitor this space and change the recovery window if necessary.
- Click OK; this saves the backup settings. The settings are actually saved in the control file of the database. This can be confirmed by logging in to Oracle RMAN at the command prompt and looking at the configuration settings via the show all command:
-- Set the Oracle Sid in Windows C:\>set %ORACLE_SID%=FIN1P -- Move to the 11g Oracle Home C:\>cd C:\app\HaviPoriH\product\11.1.0\db_1\bin -- Start RMAN in the nocatalog mode connecting to the setOracle Sid C:\app\HaviPoriH\product\11.1.0\db_1\BIN> rman target=/nocatalog Recovery Manager: Release 11.1.0.6.0 - Production on SatJun 14 22:01:47 2008 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database: FIN1P (DBID=3660855775) using target database control file instead of recoverycatalog RMAN> show all; RMAN configuration parameters for database withdb_unique_name FIN1P are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION ON; CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPEDISK TO '%F'; # default CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSEDBACKUPSET PARALLELISM 3; CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;# default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BZIP2'; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO'C:\APP\HAVIPORIH\PRODUCT\11.1.0\DB_1\DATABASE\SNCFFIN1P.ORA'; # default
It is obvious that the changes in the Oracle Grid Control Backup Settings screen have filtered down to the configuration settings in Oracle RMAN, which are stored in the control file of the FIN1P database. The settings without the # default comment are the ones that have changed. For example, the backup optimization is shown as ON, the control file autobackup is shown as ON, and parallelism is set to 3 as a compressed backup set—all settings that were changed in Oracle Grid Control.
- Now click Schedule Backup on the Maintenance tab of the FIN1P database. Under Customized Backup, select Whole Database and click Schedule Customized Backup.
Oracle’s visionary approach is to back up the database to disk—in other words, the flash recovery area—to enable fast disk-based recovery for the client and to minimize business downtime. This is the main purpose of the flash recovery area.
Once a week or at any other interval of time, the disk-based database backup—in other words, the flash recovery area—can be backed up to tape using this option.
- On the Scheduled Customized Backup: Optionspage, select Full Backup, and Online Backup as the backup mode.
Under Advanced,check the following options:
- Also back up all archived logs on disk
- Delete all archived logs from disk after they are successfully backed up
- Delete obsolete backups
The first two options enable the backup of all archive logs along with the full database backup, and the deletion of the archive logs from disk after they are backed up. The Delete obsolete backups enables the deletion of backups that are marked as obsolete—those backups that are no longer required to satisfy the retention policy.
- Click the Encryption expandable icon at the end of the page. This expands the encryption fields that are new in Oracle RMAN 11g.
To create encrypted backups on-disk with Oracle RMAN, the database must have the Advanced Security database option installed. This is a licensable option; an extra license fee is payable to Oracle in addition to the Enterprise Edition license.
For creating encrypted Oracle RMAN backups directly onto tape, Oracle Secure Backup is known to be the only supported interface. This is another licensable option.
Select Secure the backup using Recovery Manager encryptionand specify a password for the user-supplied password option. Backups will be encrypted using this password. It is also possible to use the Oracle encryption wallet to encrypt the backup, and it is advised to use both encryption approaches for flexibility in using either the wallet or the password when restoring the Oracle RMAN backup.
Note that AES256 has been selected as the encryption algorithm.
- On the next page confirm that the Disk Backup Location is the flash recovery area and proceed to the following page.
- The Scheduling page appears, where it is possible to enter the time zone of your location, the start date and time of the backup, the repeat frequency, and the end date (or if the schedule is to continue indefinitely).
The default suggested setting is 2 a.m. the following morning. The default is accepted so that the backup will start at this time.
The interval is selected as Frequency 1 Day, because as per the corporate standard a full database backup should be performed each day.
Then select Repeat until Indefinite, so that the schedule is continued indefinitely.
- Click Next. The Review screen now appears, on which the following settings are displayed: Destination Disk
Backup Type Full Backup
Backup Mode Online Backup
Encryption Algorithm AES256
Encryption Mode Oracle Encryption Wallet, Password
Flash Recovery Area /U99
Disk Parallelism 3
The Oracle RMAN script is generated by Oracle Grid Control and displayed on the screen:
set encryption on for all tablespaces algorithm 'AES256' identified by '%PASSWORD'; backup device type disk tag '%TAG' database; backup device type disk tag '%TAG' archivelog all not backed up delete all input; allocate channel for maintenance type disk; delete noprompt obsolete device type disk; release channel;
These commands perform the actions selected in the Oracle Grid Control screens, such as turning on encryption when performing a full database and archive log backup, deleting archive logs after backup, and then deleting obsolete backups.
It is possible to click Edit RMAN Scriptand modify the script before submitting; however, this prevents a return to the previous screens in this wizard. Nevertheless, the Oracle RMAN script is modified manually as follows—with some extra maintenance commands added to cross-check backups and archive logs and to delete expired backups and archive logs:
set encryption on for all tablespaces algorithm 'aes256' identified by '%password'; backup device type disk tag '%tag' database; backup device type disk tag '%tag' archivelog all not backed up delete all input; allocate channel for maintenance device type disk; crosscheck backup; delete noprompt expired backup; delete noprompt obsolete device type disk; crosscheck archivelog all; delete noprompt expired archivelog all; release channel;
- Click Submit Job. Once the job is successfully submitted, click View Job to see its progress. The job now appears under the Jobstab of Oracle Grid Control, under Job Activity, as a Scheduled job.
$rman_script="set encryption on for all tablespaces algorithm 'aes256' identified by '%password'; backup device type disk tag '%tag' database; backup device type disk tag '%tag' archivelog all not backed up delete all input; allocate channel for maintenance device type disk; crosscheck backup; delete noprompt expired backup; delete noprompt obsolete device type disk; crosscheck archivelog all; delete noprompt expired archivelog all; release channel; "; &br_save_agent_env(); &br_prebackup($l_db_connect_string, $l_is_cold_backup, $l_use_rcvcat, $l_db_10_or_higher, $l_backup_strategy, "FALSE"); my $result = &br_backup(); exit($result);
- It is possible, if so desired, to edit the scheduled job and change the schedule, the credentials, or the access. In the latter case, different access levels of View orFullcan be allocated to different Oracle Grid Control administrators. FINADM is seen as the owner of the job on this page.
- To set the Preferred Credentials for the database and host, click Preferences at the top of the Oracle Grid Control console and then click the Preferred Credentials link. This allows setting the logon name and password for both the database and the host. The Test button can be used to verify the username and password. After verification, click Apply.
- After setting the Preferred Credentials, click the Jobs tab of the Oracle Grid Control console. This shows the scheduled Oracle RMAN backup job.
- For the purpose of a backup test, change the schedule of the job to make itrun immediately. When this is done and the job is submitted, the job appearswith status Running. Press F5every few seconds to refresh the browser and watch the progress of the job.
- The job disappears from the JobActivity screen. Change the status to All and click Go.This shows all jobs, including jobs with a problem. The FIN1P backup job appears as a problem.
Output Log Recovery Manager: Release 11.1.0.6.0 - Production on SatJun 14 23:29:03 2008 Copyright (c) 1982, 2007, Oracle. All rights reserved. RMAN> connected to target database: FIN1P (DBID=3660855775) using target database control file instead of recoverycatalog RMAN> echo set on RMAN> set encryption on for all tablespaces algorithm'aes256' identified by *; executing command: SET encryption RMAN> backup device type disk tag '%tag' database; Starting backup at 14-JUN-08 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=125 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=123 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=122 device type=DISK channel ORA_DISK_1: starting compressed full datafilebackup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001name=D:\ORADATA\FIN1P\SYSTEM01.DBF channel ORA_DISK_1: starting piece 1 at 14-JUN-08 channel ORA_DISK_2: starting compressed full datafilebackup set channel ORA_DISK_2: specifying datafile(s) in backup set input datafile file number=00002name=D:\ORADATA\FIN1P\SYSAUX01.DBF input datafile file number=00004name=D:\ORADATA\FIN1P\USERS01.DBF channel ORA_DISK_2: starting piece 1 at 14-JUN-08 channel ORA_DISK_3: starting compressed full datafilebackup set channel ORA_DISK_3: specifying datafile(s) in backup set input datafile file number=00005name=D:\ORADATA\FIN1P\EXAMPLE01.DBF input datafile file number=00003name=D:\ORADATA\FIN1P\UNDOTBS01.DBF channel ORA_DISK_3: starting piece 1 at 14-JUN-08 RMAN-03009: failure of backup command on ORA_DISK_1channel at 06/14/2008 23:29:35 ORA-19914: unable toencrypt backup ORA-28365: wallet isnot open continuing other job steps, job failed will not be re-run RMAN-03009: failure of backupcommand on ORA_DISK_2 channel at 06/14/2008 23:29:35 ORA-19914: unable toencrypt backup ORA-28365: wallet isnot open continuing other job steps, job failed will not be re-run RMAN-00571:================================================= RMAN-00569: ======== ERROR MESSAGE STACK FOLLOWS ========== RMAN-00571:================================================= RMAN-03009: failure of backupcommand on ORA_DISK_3 channel at 06/14/2008 23:29:37 ORA-19914: unable toencrypt backup ORA-28365: wallet isnot open
This error has occurred because the Oracle wallet is not open or has not been created for the database. To create the encryption wallet for the FIN1P database, the following lines are first added to the sqlnet.ora file on the database server:
ENCRYPTION_WALLET_LOCATION= (SOURCE=(METHOD=FILE)(METHOD_DATA= (DIRECTORY=C:\app\HaviPoriH\product\11.1.0\db_1)))
The listener for the database is then restarted so that these changes take effect.
To create a new master key and begin using encrypted RMAN backups as well as Transparent Data Encryption (TDE) for the data, issue the following command:
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "
";
Click the Retry button on the Job page for the failed job. The backup job now executes again, and this time it completes successfully.
The Oracle RMAN output is seen to first set on encryption using the AES256 algorithm. This is followed by creation of a compressed full backup set for the database, and a control file and SPFILE autobackup. After this, the archive logs are backed up as a compressed archive log backup set, and the archive logs are then deleted from disk by Oracle RMAN due to the delete input clause.
Whenever Oracle RMAN wants to perform recovery, it is able to go to the archive log backup sets and extract the archive log it requires. This is one of the great benefits of Oracle RMAN. It contrasts with the manual recovery that DBAs performed in the past, extracting the archive logs from backup archives (UNIX tape archives or “tars”) and placing them where the recovery mechanism of Oracle could find them and use them.
The Oracle RMAN backup pieces for the database and archive logs, and the control file and SPFILE autobackups, are created in their respective directories under the flash recovery area (/U99 in this case):
\FIN1P\BACKUPSET\2008_06_15\ \FIN1P\AUTOBACKUP\2008_06_15\
The creation of the subdirectories for the database name, backup set, autobackup, and the date the backups have taken place are all automatically performed by Oracle due to the fact that the database is using the flash recovery area.
The archive log backup is followed by another control file and SPFILE backup. This happens because autobackup of these important files occurs after any backup, whether it’s a backup of the database or of the archive logs. This is then followed by the maintenance actions. These cross-check the existing backups and archive logs and make sure that they are present on-disk. All expired (not found) and obsolete (as per the retention policy) database, archive log, and control file backup entries are deleted from the control file records. The maintenance commands also ensure that the obsolete database backups and control file autobackups are physically deleted from the disk.
An important point to note is that if the database is restarted at any time in the future, the wallet must be reopened again. Otherwise, the Oracle RMAN backup fails with these errors:
ORA-19914: unable to encrypt backup ORA-28365: wallet is not open
This is resolved by opening the wallet again using the following command:
alter system set wallet open identified by "
";
- The Oracle RMAN output has been examined in detail, and the backup job has been verified as fully successful. At the Oracle Grid Control console, move to the Jobs tab. Select status All and click Go to find the verified job.
If you edit this job again, you are only able to change the access, not the schedule. So you need to re-create the job using the same steps, but this time use the schedule of the daily early morning run, repeated each day, indefinitely.
When the job is scheduled thus, it appears in the Jobs tab of the Oracle Grid Control console with the status of Scheduled.
Control console and can take corrective action if necessary. It is also possible to receive e-mail notifications from Oracle Grid Control on the failure of any scheduled jobs.
Conclusion
Although at first glance it seems that the Oracle Grid Control approach to setting up Oracle RMAN backups has a far greater number of steps than the traditional UNIX shell script and cron approach, in the practical sense these steps can be performed well within 10 to 15 minutes, because they are wizard driven and autogenerate the necessary scripts. This does not include the setup of the Oracle Enterprise Manager Agent on each server. The Agent is not just for Oracle RMAN backup purposes but is the foundation of all Oracle Grid Control management and monitoring activities for that database server.
Thus there are considerable time savings in this approach, as compared to the two or more hours required for the manual setup and testing of Oracle RMAN UNIX-scripted backups that were discussed in Part I. This leads to savings on expensive DBA man-days, especially if there are a large number of projects requiring databases to be provisioned. Backup strategy has always been a very important aspect of Oracle Database, and it is essential that backups are easily set up in a consistent manner for each production and test database.
Because the Oracle RMAN backup setup is largely wizard driven, the chances for human error are minimal. Scripts have been generated by Oracle Grid Control and scheduled by its own job scheduling mechanism, thus eliminating the need to hire and retain rare skills in shell script maintenance. There is no question of using any other script language, because Oracle Grid Control does all the script generation. Debugging is also greatly simplified. For any new requirement that may arise—for example, incremental backups or image copies—a new backup job can be easily created using the Oracle Grid Control console and the old job deleted. So maintenance and future enhancements are also made a great deal easier.
One other great advantage of the Oracle Grid Control approach is that the latest advances in database technology are available as options in the Wizards, thus leading to a new awareness of the possibilities. In the real world, it is often seen that old Oracle RMAN scripts used in Oracle8i Database are still being used unchanged in Oracle9i Database and 10g, mainly because of the lack of knowledge in the DBAs maintaining the scripts. This results in new Oracle RMAN features not being used, such as compression of backups, or block change tracking for incremental backups, or encryption. When the Oracle Grid Control approach is used instead, it results in the DBAs being made aware of the new Oracle RMAN possibilities so that these can be used to the fullest benefit.
When Oracle Grid Control is showcased to management and DBAs through the demonstration in this article, there is no need for further proof—the manifold benefits of this approach are visible beyond any doubt. More and more companies will necessarily set up and schedule Oracle RMAN backups using the vast capabilities of Oracle Enterprise Manager Grid Control. There will be no stopping this immensely beneficial juggernaut.
REFERENCES
Oracle RMAN Backups: Pushing the "Easy" Button, by Porus Homi Havewala, Oracle Coporation
REFERENCES
Oracle RMAN Backups: Pushing the "Easy" Button, by Porus Homi Havewala, Oracle Coporation
let me share my experience with regard to the service of fix mdf, it automatically eliminates data corruption issues in selected databases
ReplyDelete