Subject: RMAN: Setup and Usage in Oracle8 and 8i Creation Date: 25-APR-2000 PURPOSE ------- How To: Setup an Oracle8 or 8i database for backup using Recovery Manager SCOPE & APPLICATION ------------------- All users wanting to learn more about RMAN and as a guide for usage. RMAN: Setup and Usage in an Oracle8 or 8i Environment ----------------------------------------------------- The purpose of this excerise is to give you some hands on experience using Recovery Manager command line interface. We hope when you complete these labs you will have a better understanding of how RMAN works and what is required to use RMAN. 1.0 RMAN Setup We are assuming in this scenario that we have 2 databases RCAT and PROD. The SQL*Net connections should already be created and tested. 1.1 Decisions before creating the catalog owner and user. You must decide if a recovery catalog will be used or if you will be using the target database controlfile. It is always recomended that you use a recovery catalog. For larger system the use of a recovery catalog can increase the backup performance. Also if the target database controlfiles are lost recovery can become difficult if not impossible. The catalog can be created in a test or development database. You may also create an ordinary Oracle database to specifically be used for backup and recovery purposes. In our lab there will be 2 databases. A taget database and recovery catalog database. They will be on the same machine so hopefully we can accomodate have separate disks to house them both. 1.2 Generic setup of the recovery catalog: 1.2a In the database designated as the recovery database we need to create the user RMAN in Server Manager. This step is generic in 8.0 and 8.1. svrmgrl> connect internal connected svrmgrl> create tablespace CATALOG datafile '/u01/oracle/rcat/cat1rcat.dbf size 20M; statement processed svrmgrl> create user rman identified by rman temporary tablespace TEMP default tablespace CATALOG quota unlimited on CATALOG; statement processed svrmgrl> grant recovery_catalog_owner to rman; statement processed 1.2b Now the RMAN user is ready to create the recovery catalog. Note: This step only applies for an Oracle8 8.0.x catalog creation. svrmgrl> connect rman/rman@rcat connected svrmgrl> @?/rdbms/admin/catrman.sql This only takes a few minutes to run. Once complete we'll have all the metadata required for the recovery catalog created under the RMAN schema and in the RMAN default tablespace. 1.2c In Oracle8i the catalog is created a little differently. Note: This step only applies to Oracle8i 8.1.5 and greater. From the UNIX shell run: % set ORACLE_SID=RCAT % rman catalog rman/rman RMAN> create catalog; This will generate the recovery catalog schema in the default tablespace for RMAN. 1.2d Now we have a recovery catalog and need to register the target database with the catalog to get the target databases controlfile information. What is RMAN looking at when we look at the controlfile? Take a look at the v$controlfile_record_section view to determine what information we store in the controlfile in Oracle8 and up. Back to the UNIX prompt: % set ORACLE_SID=PROD % rman TARGET / RCVCAT rman/rman@rcat ( There are a number of ways to connect to the RMAN subsystem. If an @ symbol is used on the TARGET side of the command then an external passwordfile must be used and the INIT.ORA parameter REMOTE_LOGIN_PASSWORDFILE must be exclusive. Refer to your Oracle Server Administration guide for more information about password files.) Recovery Manager: Release 8.0.5.2.0 - Production RMAN-06005: connected to target database: PROD RMAN-06008: connected to recovery catalog database RMAN> register database; Once complete the DBID, DB_NAME, and structure are captured in the recovery catalog. To verify the registration run: RMAN> list incarnation of database; RMAN-03022: compiling command: list RMAN-06240: List of Database Incarnations RMAN-06241: DB Key Inc Key DB Name DB ID CUR Reset SCN Reset Time RMAN-06242: ------- ------- -------- ---------- --- --------- ---------- RMAN-06243: 1 2 PROD 3351020544 YES 1 10-AUG-99 Now we have the target database information stored in the recovery catalog. Notice the current column (CUR) as this become important later. When the database is opened with the RESETLOGS option the catalog must also be reset. This is accomplished by running: RMAN> reset database; This command lets RMAN know we have reset the TARGET database after an incomplete recovery. The next backup is the baseline for the new incarnation of the PROD database and the current TARGET would be listed separatly in the list command. We'll cover this more later on. 2.0 Making Backups with Recovery Manager 2.0a We are ready to move to the next step now. There are rules for everything and this is no exception. To begin making RMAN backups we need to know some things. 1. Limitations: With the Oracle8 standard Edition you can allocate only 1 channel at a time. This means you will have to allocate then deallocate to swtch between SBT_TAPE and disk channels. The Oracle8 Enterprise Edition can allocate up to 4 channels with the default MML provided on the Oralce8 media. This is a limitation of the 3rd party MML not Recovery Manager. The Oracle8 Standard Edition can only allocate only 1 channel at a time. If you have operations that require tape and disk channels you will have to write the allocate and release the channels when the operation requires, such as replicating the controlfile during restore. With a fully licenced third party backup software that is on the Oracle BSP list you can get the MML directly from the vendor, link it in and then you are limited only be the 3rd party software capabilities. 2.1 Snapshot Controlfile When RMAN needs to resynchronize from a read-consistent version of the control file, it creates a temporary snapshot control file. The default name for the snapshot control file is port-specific. Use the set snapshot controlfile name command to change the name of the snapshot control file; subsequent snapshot control files that RMAN creates use the name specified in the command. For example, start RMAN and then enter: set snapshot controlfile name to '/oracle/dba/prod/snap_prod.ctl'; You can also set the snapshot control file name to a raw device. This operation is important for OPS databases in which more than one instance in the cluster use RMAN because server sessions on each node must be able to create a snapshot control file with the same name and location. For example, enter: set snapshot controlfile name to '/dev/vgd_1_0/rlvt5'; If one RMAN job is already backing up the control file while another needs to create a new snapshot control file, you may see the following message: RMAN-08512: waiting for snapshot controlfile enqueue Under normal circumstances, a job that must wait for the control file enqueue will wait for a brief interval and will then successfully retrieve theenqueue. Recovery Manager makes up to five attempts to get the enqueue and then fails the job. The conflict is usually caused when two jobs areboth backing up the control file, and the job that starts backing up the control file first waits for service from the media manager. 2.2 Tescase 1 Cold backup 2.2a Taking a cold(offline) full backup up to a DISK channel. % set ORACLE_SID=PROD svrmgrl> connect internal connected svrmgrl> shutdown immediate; svrmgrl> startup mount % rman target / rcvcat rman/rman@rcat run { allocate channel c1 type disk format '/backup/u00/oracle/prod/df_%d_%p_%c'; backup (database); sql 'alter database open'; release channel c1; } 2.2b Verify the existance of the backupset in the catalog. RMAN> list backupset of database; RMAN-03022: compiling command: list RMAN-03025: performing implicit partial resync of recovery catalog RMAN-03023: executing command: partial resync RMAN-08003: starting partial resync of recovery catalog RMAN-08005: partial resync complete RMAN-06230: List of Datafile Backups RMAN-06231: Key File Type LV Completion_time Ckp SCN Ckp Time RMAN-06232: ------- ---- ------ -- --------------- ---------- -------- RMAN-06233: 1386 5 Full 21-OCT-99 468068 21-OCT-99 2.2c The same full backup from 2.2a to SBT_TAPE % set ORACLE_SID=PROD svrmgrl> connect internal connected svrmgrl> shutdown immediate; svrmgrl> startup mount % rman target / rcvcat rman/rman@rcat run { allocate channel t1 type 'SBT_TAPE' format 'df_%d_%p_%c'; backup (database); sql 'alter database open'; release channel t1; } 2.2d HOT/OPEN backup (online) uning incremental level 0 Note: The database must be in archivelog mode to run hot/open backups. Verify this with the "archive log list" command: 1. Start svrmgrl and connect internal set ORACLE_SID=PROD svrmgrl> connect internal svrmgrl> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination '/u01/oracle/prod/archive' Oldest online log sequence 151 Next log sequence to archive 152 Current log sequence 152 As long as archivelog mode is set we can continue now. 2. Start Recovery Manager and run a hot/open database backup. In this scenario we are performing a hot/open backup of the database and another backup of the archived redologs deleting the logs after backup. This avoids the DBA having to keep up with the log_archive_dest space management. % set ORACLE_SID=PROD % rman target / rcvcat rman/rman@rcat RMAN> run { allocate channel c1 type disk; format '/backup/u00/oracle/prod/df_%d_%s_%p'; backup (database include current controlfile); release channel c1; allocate channel c1 type disk format '/backup/u00/oracle/prod/al_%d_%s_%p'; backup (archivelog all delete input); release channel c1; } 2.2e So far we have only allocated 1 channel. This will create a set of input buffers and output buffers. Let's talk about backups and backup types. What types of backups can we do? 1. Full - All used blocks are copied to output. 2. Incremental - Backing up all used blocks since a backup => LEVEL N Level is a base number used at block level to identify if the block is => the incremental number specified. It's really not that bad. If I back up using an LEVEL 2 incremental backup strategy then 1st I would have a full or LEVEL 0 incremental backup for the baseline starting on Sunday. Then perform a LEVEL 2 backup on Monday and then again on Tuesday, I only backup what changes each day. When I do a LEVEL 1 backup on Wednesday the I get all blocks that have changed since the LEVEL 0 backup because LEVEL 1 is < the the 2 it continues to backup what the level 2 backups had already done a backup of the 2 days before. Next run the LEVEL 2 backups Thursday, Friday, and Saturday, then back to the Sunday Level 0 backup so we get a fresh copy of all used blocks in the database. LVL - Sunday Monday Tuesday Wednesday Thursday Friday Saturday 0 Baseline ( All Used Blocks ) 2 ------| 2 --------| 1 --------------------------| 2 ----------| 2 ---------| 2 ----------| 0 Baseline New With this strategy we can restore and recover a database only having to restore 2-3 incrementals besides the baseline to recover the database to as current a state as possible. The nice thing is when you tell RMAN to recover it will try and perform a complete recovery and when you set until for incomplete recovery RMAN still determines what backups will be needed to recover until a point in time, a logseq, or an scn number. All set until recoveries are still resolved to an scn by RMAN if "until time" or "until logseq" are used. set snapshot controlfile name to '@snapcf_TARGET.f'; run { allocate channel t1 type 'sbt_tape'; sql "alter system archive log current"; backup incremental level 2; format 'df_%t_%s_%p' database; sql "alter system archive log all"; backup format 'cf_%t_%s_%p' current controlfile; } Incremental backups are only available in the Oracle Enterprise Edition. The levels 0 and 1-4 are only reference points. One level does not backup blocks any differently. But the level that was last used will be retained in the block header so when you specify a higher level the cadidate blocks are verified in descending order. 2.3 Backing up just the archivelogs You can select to backup the archived log files in the LOG_ARCHIVE_DEST and remove them with the "delete input" option to reclaim the space avoiding time spent scrambling to clear the space after it runs out of disk resources and the database has stopped. If space management is priority due to lots of archive redo logs you can create a script to backup the files to tape and remove them upon backup completion. Schedule as a CRON job to run as often as needed. Run { allocate channel t1 type 'sbt_tape'; allocate channel t2 type 'sbt_tape'; backup filesperset 20 format 'al_%t_%s_%p' archivelog all delete input; release channel t1; } With many files you may want to allocate additional channels to increase backup performance. By limiting FILESPERSET we can cause multiple sets to be created and each set copied in parallel to tape. 3.0 Restoring and recovering with Recovery manager 1. Thare are various states the database can be in for different situations. a. If you restore the controlfile you must "startup nomount" b. If restoring the SYSTEM datafiles "startup mount" c. Restoring datafiles or tablespaces other than SYSTEM the database can be online but the datafile or tablespace must be taken offline. 3.0a In this example the database is online and in archivelog mode: run { allocate channel d1 type disk; sql "alter tablespace XXXX offline immediate;" restore tablespace XXXX; recover tablespace XXXX; sql "alter tablespace XXXX online; The database stayed online and the XXXX tablespace was restored and recovered with little impact to the rest of the database. 3.0b In this example the database is offline to restore the controlfile, we still have the online logs so complete recovery is possible: % set ORACLE_SID=PROD svrmgrl> connect internal svrmgrl> shutdown abort; svrmgrl> startup nomount; rman target / rcvcat rman/rman@rcat run { allocate channel t1 type 'sbt_tape'; allocate channel d1 type disk; restore controlfile to 'd:\target\ctl1targ.ora'; replicate controlfile from 'd:\target\ctl1targ.ora'; restore database; sql "alter database mount"; recover database; sql "alter database open"; } 3.0c In this example we'll restore the system tablespace % set ORACLE_SID=PROD svrmgrl> connect internal svrmgrl> shutdown immediate; svrmgrl> startup mount; rman target / rcvcat rman/rman@rcat run { allocate channel d1 type disk; restore tablespace SYSTEM; recover database; sql "alter database open"; } 3.0d Restoring the database to a new server 1. You must have the Oracle binaries installed on the node to restore to and have updated the sqlnet information on that node so Recovery Manager can connect to the new instance started at nomount. We will perform time based recovery. % set ORACLE_SID=PROD svrmgrl> connect internal svrmgrl> shutdown abort; svrmgrl> startup nomount; rman target / rcvcat rman/rman@rcat run { set until time 'Jul 01 1999 00:05:00'; allocate channel d1 type disk; set newname for datafile '/u04/oracle/prod/sys1prod.dbf' to '/u02/oracle/prod/sys1prod.dbf'; set newname for datafile '/u04/oracle/prod/usr1prod.dbf' to '/u02/oracle/prod/usr1prod.dbf'; set newname for datafile '/u04/oracle/prod/tmp1prod.dbf' to '/u02/oracle/prod/tmp1prod.dbf'; set newname for datafile '/u04/oracle/prod/rbs1prod.dbf' to '/u02/oracle/prod/rbs1prod.dbf'; set newname for datafile '/u04/oracle/prod/indx1prod.dbf' to '/u02/oracle/prod/indx1prod.dbf'; restore controlfile to '/u02/oracle/prod/ctl1prod.ora'; replicate controlfile from '/u02/oracle/prod/ctl1prod.ora'; restore database; sql "alter database mount"; switch datafile all; recover database; sql "alter database open resetlogs"; release channel d1; } Note: Switch in this command updates the restore controlfile with the new datafile path name just like "alter database datafile rename.." Since we are restoreing to a new server we will not have the use of the online redo logs so complete recovery is not possible and the database is open with the resetlogs option. WEBIV - Note: 60954.1 Alert: There are known issues using RMAN commands on RAW devices. * The recover database command has been used in each recovery but what does it do and how does it behave? 3.0.e Resynchronizing the Recovery Catalog 1. Why is it important resync the catalaog? 2. How often should we resync? When RMAN performs a resynchronization, it compares the recovery catalog to either the current control file of the target database or a backup control file and updates it with information that is missing or changed. Resynchronizations can be full or partial. In a partial resynchronization, RMAN reads the current control file to update changed information, but does not resynchronize metadata about the database physical schema: datafiles, tablespaces, redo threads, rollback segments (only if the database is open), and online redo logs. In a full resynchronization, RMAN updates all changed records, including those for the database schema. A full or partial resync is done when one of the following commands are issued: backup, copy, crosscheck (8i), delete expired backupset (8i), duplicate (8i), list, recover, report, restore and switch. 3. How do you resync the recovery catalog manually with the target? set ORACLE_SID=target_db rman target / rcvcat rman/rman@rcat rman> resync catalog; RMAN-03022: compiling command: resync RMAN-03023: executing command: resync RMAN-08002: starting full resync of recovery catalog RMAN-08004: full resync complete Note: Resync operations The resync operation can be setup as a UNIX cron job or an NT AT job run from command line or if using Enterprise Manager this can be a scheduled job in the EM repository. If tablespaces are setup to autoextend and the redo log files are switching frequently the intraval for resync should be set to minimize loss of these changes. 4.0 Recovery Catalog Reporting and Maintinence 4.1 Making lists of archivelogs. There are several different ways to list this information. Backupset and Copy are key words and are replacable depending on whether you are listing backupsets or copies of files. 4.1a list backupset of archivelog all; <- Oracle8 8.0.x list backup of archivelog; <- Oracle8i 8.1.x # To list all of the archivelogs backed up. list backupset of archivelog all; # To list a range of archivelogs to determine the BP key#. list backupset of archivelog from logseq 200 until logseq 300 thread1; # Using date as a range specifier. List archives older then 3 days. list backupset of archivelog until time "sysdate - 3"; 4.2 The LIST command can show the database in 3 ways. List incarnation, backupset, or copy. The incarnation is a list of the database incarnations. The list backupset or copy uses an object list. An object is either a datafile, tablespace, database, archivelog or controlfile. # List how many incarnations of the database and the key numbers. list incarnation of database; # List backupsets or a backupset from the target database. list backupset of database; or list backupset of database until time "sysdate-14"; # List backups of the tablespaces. list backupset of tablespace 'USERS'; # List backups by datafile. list backupset of datafile 5; 4.3 Using the RMAN metadata to form reports about the target. The report command can be used to verify the recoverablity of a target. You can report NEED BACKUP, UNRECOVERABLE, OBSOLETE or SCHEMA. # To report what has not had a backup for 5 days or more for the # database. report need backup days 5 database; # Report which backupsets are not consistant and cannot be used for # recovery. report unrecoverable database; # If you are only concerned about the last 14 days and want to find backupsets that can be removed from the catalog use: report obsolete redundancy 14; # To report all the datafiles and tablespaces in the target. report schema; # To report files that will require the application of 5 incremental backups to recover the database. report need backup incremental 5 database; # The following commands reports the database schema in the present a week ago, two weeks ago, and a month ago: report schema; report schema at time 'SYSDATE-7'; report schema at time "TO_DATE('12/20/99','MM/DD/YY')"; # You can list all backupset older than 90 days assuming you don't have any backup before 3000 days as follows : list backupset of database from time SYSDATE-3000 until time SYSDATE-90; Note: The date format given in string after from time should match NLS_DATE_FORMAT, or you can use to_date function. This is the string directly passed to SELECT statement. RELATED DOCUMENTS ----------------- Oracle8 and 8i Backup and Recovery Guides