RMAN : Consistent Backup, Restore and Recovery using RMAN (Doc ID 162855.1)

rongshiyuan發表於2014-05-13
REST

RMAN : Consistent Backup, Restore and Recovery using RMAN (Doc ID 162855.1)


***Checked for relevance on 14-Aug-2013*** Oracle9i Lab 1 , but is applicable for higher RDBMS versions aswell Consistent Backup, Restore and Recovery using RMAN In Oracle9i, Recovery Manager still works the same as in 8 and 8i with enhancements for ease of use and manageability. Using the updateable persistent configuration options, backing up the database is easier then ever. To begin lets look where information is stored in the database about RMAN backups. For testing we'll use the target database controlfile without a recovery catalog. By setting up the autocontrolfile backup feature we can use the controlfile backups for a recovery catalog. 1. V$ tables - What does the controlfile know? What are the views and synonyms related to backup and rman? Let's take a look before backups are done to see what information they hold for us. SQL> set pagesize 60 SQL> column object_name format a30 SQL> select object_name from dba_objects 2 where object_name like '%BACKUP%' 3 and object_type = 'SYNONYM'; select object_name from dba_objects where object_name like '%RMAN%' and object_type = 'SYNONYM'; OBJECT_NAME -------------------------------- V$BACKUP_CORRUPTION V$BACKUP_DATAFILE V$BACKUP_DEVICE V$BACKUP_PIECE V$BACKUP_REDOLOG V$BACKUP_SET V$BACKUP_SYNC_IO V$RMAN_CONFIGURATION V$BACKUP V$BACKUP_ASYNC_IO Other controlfile views to reference are: v$controlfile_record_section, v$copy_corruption, v$database, v$datafile, v$datafile_copy, v$offline_range, v$proxy_archivelog, v$proxy_datafile, v$session_longops, v$database_block_corruption 2. RMAN Configuration Check the default configuration in RMAN and then check the V$ view to see what is stored. Then update the configuration in RMAN and take a look at the view again. This is accomplished in RMAN using the "show all" command. % sqlplus "/ as sysdba" set pagesize 60 spool rman_new_conf.lst column name format a30 column VALUE format a31 set echo on; set serveroutput on; select * from v$rman_configuration; spool off; CONF# NAME VALUE ---------- ------------------------------ ------------------------------- 1 RETENTION POLICY TO REDUNDANCY 3 2 BACKUP OPTIMIZATION OFF 3 DEFAULT DEVICE TYPE TO DISK 4 CONTROLFILE AUTOBACKUP ON 5 CONTROLFILE AUTOBACKUP FORMAT DISK TO '/proj/SME9i/backup/%F' FOR DEVICE TYPE 6 DEVICE TYPE DISK PARALLELISM 2 7 DATAFILE BACKUP COPIES FOR DEV DISK TO 1 ICE TYPE 8 ARCHIVELOG BACKUP COPIES FOR D DISK TO 1 EVICE TYPE 9 MAXSETSIZE TO UNLIMITED 9 rows selected. Since we are not using a recovery catalog let's look at the controlfile since this is our catalog without a recovery catalog. In sqlplus run: select TYPE, RECORDS_TOTAL, RECORDS_USED from v$controlfile_record_section where type like '%BACKUP%'; TYPE RECORDS_TOTAL RECORDS_USED ------------------ ------------- ------------ BACKUP SET 101 0 BACKUP PIECE 204 0 BACKUP DATAFILE 210 0 BACKUP REDOLOG 53 0 BACKUP CORRUPTION 185 0 The records used column is still zero since we have not taken any backups. The records are inserted into these views after successful creation of a backupset. When RMAN reads the files to backup, if any corruption is encountered then the corruption views are populated with file#, block# and contiguous blocks after the initial corrupt block. 3. Create a persistent configuration for reuse Create a persistent backup configuration. Once these parameters are configured RMAN will continue to reuse the configured options for subsequent backups unless you override the option within your script or to clear or disable it. In 9i RMAN you use the "show" command to see the currently configured options. SHOW show_operand [,show_operand ...]; show_operand: RETENTION POLICY | EXCLUDE | BACKUP COPIES | CHANNEL | DEFAULT DEVICE TYPE | DEVICE TYPE | SNAPSHOT CONTROLFILE | ... ALL RMAN> show all; using target database controlfile instead of recovery catalog RMAN configuration parameters are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/beta/app/oracle/product/9.0.1/dbs/snapc f_V901.f'; # default You can script the configuration you want to use within a run block to change more then one parameter or just change a single parameter at a time from the RMAN prompt not using the run block. run { # Use the configure commands to create your backup policy. # When complete these will be the new persistent # configuration parameters for RMAN in the controlfile # of the target database. # CONFIGURE RETENTION POLICY TO REDUNDANCY 3; CONFIGURE BACKUP OPTIMIZATION OFF; CONFIGURE DEFAULT DEVICE TYPE TO DISK; CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/proj/SME9i/backup/%F'; CONFIGURE DEVICE TYPE DISK PARALLELISM 2; CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; CONFIGURE MAXSETSIZE TO UNLIMITED; CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/proj/SME9i/backup/snapf_prod9.f'; } RMAN> show all; RMAN configuration parameters are: CONFIGURE RETENTION POLICY TO REDUNDANCY 3; CONFIGURE BACKUP OPTIMIZATION OFF; CONFIGURE DEFAULT DEVICE TYPE TO DISK; CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/proj/SME9i/backup/%F'; CONFIGURE DEVICE TYPE DISK PARALLELISM 2; CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; CONFIGURE MAXSETSIZE TO UNLIMITED; CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/proj/SME9i/backup/snapf_prod9.f'; 4. Create a consistent backup script Create an RMAN script to generate closed database backups without a recovery catalog. We set the autocontrolfile backup feature during configuration so the backup controlfile will become our catalog to avoid the overhead of maintaining a second database for recovery. % vi backup_lvl0_cold.rcv ## Consistent backup # connect target; run { shutdown immedate; startup mount pfile=/proj/SME9i/prod9/pfile/initprod9.ora; backup incremental level 0 database format '/proj/SME9i/backup/%d_closed_%U' tag=prod9_closed_LVL0; shutdown; startup pfile=/proj/SME9i/prod9/pfile/initprod9.ora; } exit 5. Create a backup using backup_lvl0_cold.rcv. Make a backup of the target database using the new script. Rerun the loop script in sqlplus to generate changes and run another backup of the database. Do this until you have 4 backups of the database. How many backup records were created for the backup we did? Let's look at the controlfile and verify what's there. select TYPE, RECORDS_TOTAL, RECORDS_USED from v$controlfile_record_section where type like '%BACKUP%'; TYPE RECORDS_TOTAL RECORDS_USED ------------------ ------------- ------------ BACKUP SET 101 3 BACKUP PIECE 204 3 BACKUP DATAFILE 210 9 BACKUP REDOLOG 53 0 BACKUP CORRUPTION 185 0 We have 2 backupsets from the parallelism 2 in the channel persistent configuration set in step #3. The files to channel algorithm will split the files across the allocated channels, since there are 2 channels there will be 2 backupsets that consist of the datafile backups. The 3rd is the controlfile backup created with "AUTOCONTROLFILE BACKUP" feature. Use the "by summary" option to list your backups in RMAN. This shows a short summary of your backupsets. 1 - channel datafile backupset 2 - channel datafile backupset 3 - autocontrolfile backup RMAN> list backup summary; List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Tag ------- -- -- - ----------- --------------- ------- ------- --- 1 B F A DISK 20-JUL-01 1 1 2 B F A DISK 20-JUL-01 1 1 3 B F A DISK 20-JUL-01 1 1 The "list backup" command will also show the files associated with the backupset key. It uses verbose by default. 6. Sample procedure for change generation. This is a test procedure to generate 5 1M archived logs or just to generate changes to the database to create blocks for backup. Useful for incremental backup testing. -- loop.sql -- set echo off drop table team1; create table team1 (col1 number, col2 date); drop sequence team1_seq; create sequence team1_seq start with 1; truncate table team1; begin for i in 1..100 loop for i in 1..200 loop insert into team1 values(team1_seq.nextval, sysdate); end loop; commit; end loop; end; / -- end 7. Create a test schema. Create a schema to generate changes to the database so there will changed blocks for backup. Then connect as that user and run "loop.sql". This can be used to make changes for incremental backups. SQL> create user team1 identified by team1 2 default tablespace users 3 temporary tablespace temp; User created. SQL> grant connect, resource to team1; Grant succeeded. SQL> connect team1/team1 Error accessing PRODUCT_USER_PROFILE Warning: Product user profile information not loaded! You may need to run PUPBLD.SQL as SYSTEM Connected. SQL> @loop.sql drop table team1 * ERROR at line 1: ORA-00942: table or view does not exist Table created. drop sequence team1_seq * ERROR at line 1: ORA-02289: sequence does not exist Sequence created. Table truncated. PL/SQL procedure successfully completed. SQL> exit 8. Create a new closed database backup of the database. % rman trace b_prod9_2_closed.log RMAN> run { shutdown immediate; startup mount pfile=/proj/SME9i/prod9/pfile/initprod9.ora; backup incremental level 0 database format '/proj/SME9i/backup/%d_closed_%U'; shutdown; startup pfile=/proj/SME9i/prod9/pfile/initprod9.ora; } 9. Verify the new backup information in sqlplus and compare with RMAN. sqlplus "/ as sysdba" select TYPE, RECORDS_TOTAL, RECORDS_USED from v$controlfile_record_section where type like '%BACKUP%'; TYPE RECORDS_TOTAL RECORDS_USED ------------------ ------------- ------------ BACKUP SET 101 6 BACKUP PIECE 204 6 BACKUP DATAFILE 210 18 BACKUP REDOLOG 53 0 BACKUP CORRUPTION 185 0 RMAN> list backup summary; using target database controlfile instead of recovery catalog List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Tag ------- -- -- - ----------- --------------- ------- ------- --- 1 B F A DISK 20-JUL-01 1 1 2 B F A DISK 20-JUL-01 1 1 3 B F A DISK 20-JUL-01 1 1 4 B F A DISK 20-JUL-01 1 1 5 B F A DISK 20-JUL-01 1 1 6 B F A DISK 20-JUL-01 1 1 RMAN> list backup by file; List of Datafile Backups ======================== File Key TY LV S Ckp SCN Ckp Time #Pieces #Copies Tag ---- ------- - -- - ---------- --------- ------- ------- --- 1 5 B F A 97763 20-JUL-01 1 1 2 B F A 96266 20-JUL-01 1 1 2 4 B F A 97763 20-JUL-01 1 1 1 B F A 96266 20-JUL-01 1 1 3 4 B F A 97763 20-JUL-01 1 1 1 B F A 96266 20-JUL-01 1 1 4 5 B F A 97763 20-JUL-01 1 1 2 B F A 96266 20-JUL-01 1 1 5 5 B F A 97763 20-JUL-01 1 1 2 B F A 96266 20-JUL-01 1 1 6 4 B F A 97763 20-JUL-01 1 1 1 B F A 96266 20-JUL-01 1 1 7 5 B F A 97763 20-JUL-01 1 1 2 B F A 96266 20-JUL-01 1 1 8 4 B F A 97763 20-JUL-01 1 1 1 B F A 96266 20-JUL-01 1 1 List of Controlfile Backups =========================== CF Ckp SCN Ckp Time BS Key S #Pieces #Copies Tag ---------- --------- ------- - ------- ------- --- 97763 20-JUL-01 6 A 1 1 96266 20-JUL-01 3 A 1 1 RMAN> exit The above shows the controlfile backups are in BS_KEY 3 and 6 after 2 backups. List by file shows details for which file belongs to which backupset. 10. Accounting for all files in the target. SQL> column name format a50 SQL> select file#, name from v$datafile; FILE# NAME ---------- -------------------------------------------------- 1 /proj/SME9i/prod9/data/system01.dbf 2 /proj/SME9i/prod9/data/undotbs01.dbf 3 /proj/SME9i/prod9/data/cwmlite01.dbf 4 /proj/SME9i/prod9/data/drsys01.dbf 5 /proj/SME9i/prod9/data/example01.dbf 6 /proj/SME9i/prod9/data/indx01.dbf 7 /proj/SME9i/prod9/data/tools01.dbf 8 /proj/SME9i/prod9/data/users01.dbf 8 rows selected. What and where is file #9? /proj/SME9i/prod9/data/control01.ctl The controlfile autotbackup from the controlfile persistent configuration does a backup of the controlfile when you issue a backup command. BACKUP DATABASE BACKUP TABLESPACE BACKUP DATAFILE BACKUP ARCHIVELOG RMAN> report need backup; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 3 Report of files with less than 3 redundant backups File #bkps Name ---- ----- ----------------------------------------------------- 1 2 /proj/SME9i/prod9/data/system01.dbf 2 2 /proj/SME9i/prod9/data/undotbs01.dbf 3 2 /proj/SME9i/prod9/data/cwmlite01.dbf 4 2 /proj/SME9i/prod9/data/drsys01.dbf 5 2 /proj/SME9i/prod9/data/example01.dbf 6 2 /proj/SME9i/prod9/data/indx01.dbf 7 2 /proj/SME9i/prod9/data/tools01.dbf 8 2 /proj/SME9i/prod9/data/users01.dbf In sqlplus run the team1 procedure again to generate more changes. RMAN> @b_prod9_closed.rcv Here is what I have in my backup directory. Take a look at what's in yours. [otcsol1]/proj/SME9i/backup> ls -g ls -FC -g total 1491252 -rw-r----- 1 udba 1511936 Jul 20 10:09 c-2094960375-20010720-00 -rw-r----- 1 udba 1511936 Jul 20 11:31 c-2094960375-20010720-01 -rw-r----- 1 udba 1511936 Jul 20 12:07 c-2094960375-20010720-02 -rw-r----- 1 udba 129495552 Jul 20 10:09 PROD9_closed_01cvcpvf_1_1 -rw-r----- 1 udba 122544640 Jul 20 10:09 PROD9_closed_02cvcpvf_1_1 -rw-r----- 1 udba 129909248 Jul 20 11:30 PROD9_closed_04cvcup9_1_1 -rw-r----- 1 udba 122544640 Jul 20 11:31 PROD9_closed_05cvcup9_1_1 -rw-r----- 1 udba 129909248 Jul 20 12:06 PROD9_closed_07cvd0ua_1_1 -rw-r----- 1 udba 122544640 Jul 20 12:07 PROD9_closed_08cvd0ua_1_1 -rw-r----- 1 udba 1503232 Jul 20 12:07 snapf_prod9.f Since the retention policy is redundancy 3, let's see what needs backup or if we've satisfied the policy. RMAN> report need backup; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 3 Report of files with less than 3 redundant backups File #bkps Name ---- ----- ----------------------------------------------------- RMAN> report obsolete; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 3 no obsolete backups found RMAN> Nothing to obsolete yet. Only 3 backups. Run team# loop.sql to generate more changes. Run sqlplus and verify the control file v$ backup information. select TYPE, RECORDS_TOTAL, RECORDS_USED from v$controlfile_record_section where type like '%BACKUP%'; TYPE RECORDS_TOTAL RECORDS_USED ------------------ ------------- ------------ BACKUP SET 101 12 BACKUP PIECE 204 12 BACKUP DATAFILE 210 36 BACKUP REDOLOG 53 0 BACKUP CORRUPTION 185 0 [otcsol1]/proj/SME9i/backup> ls -g ls -FC -g total 1987620 -rw-r----- 1 udba 1511936 Jul 20 10:09 c-2094960375-20010720-00 -rw-r----- 1 udba 1511936 Jul 20 11:31 c-2094960375-20010720-01 -rw-r----- 1 udba 1511936 Jul 20 12:07 c-2094960375-20010720-02 -rw-r----- 1 udba 1511936 Jul 20 12:22 c-2094960375-20010720-03 -rw-r----- 1 udba 129495552 Jul 20 10:09 PROD9_closed_01cvcpvf_1_1 -rw-r----- 1 udba 122544640 Jul 20 10:09 PROD9_closed_02cvcpvf_1_1 -rw-r----- 1 udba 129909248 Jul 20 11:30 PROD9_closed_04cvcup9_1_1 -rw-r----- 1 udba 122544640 Jul 20 11:31 PROD9_closed_05cvcup9_1_1 -rw-r----- 1 udba 129909248 Jul 20 12:06 PROD9_closed_07cvd0ua_1_1 -rw-r----- 1 udba 122544640 Jul 20 12:07 PROD9_closed_08cvd0ua_1_1 -rw-r----- 1 udba 129909248 Jul 20 12:22 PROD9_closed_0acvd1ps_1_1 -rw-r----- 1 udba 122544640 Jul 20 12:22 PROD9_closed_0bcvd1ps_1_1 -rw-r----- 1 udba 1503232 Jul 20 12:22 snapf_prod9.f SQL> set pagesize 60 SQL> select RECID, STAMP, COMPLETION_TIME, INCREMENTAL_LEVEL from v$backup_set; RECID STAMP COMPLETION_TIME INCREMENTAL_LEVEL ---------- ---------- --------------- ----------------- 1 435578964 20-JUL-01 0 2 435578982 20-JUL-01 0 3 435578987 20-JUL-01 4 435583853 20-JUL-01 0 5 435583874 20-JUL-01 0 6 435583877 20-JUL-01 7 435586019 20-JUL-01 0 8 435586027 20-JUL-01 0 9 435586036 20-JUL-01 10 435586934 20-JUL-01 0 11 435586942 20-JUL-01 0 12 435586945 20-JUL-01 12 rows selected. SQL> set pagesize 60 SQL> column handle format a32 SQL> column tag format a18 SQL> select RECID,SET_STAMP, TAG, STATUS, HANDLE from v$backup_piece order by SET_STAMP; RECID SET_STAMP TAG S HANDLE ---------- ---------- ------------------ - -------------------------------- 1 435578863 A /proj/SME9i/backup/PROD9_closed_ 01cvcpvf_1_1 <<= DB Backupset 2 435578863 A /proj/SME9i/backup/PROD9_closed_ 02cvcpvf_1_1 <<= DB Backupset 3 435578986 A /proj/SME9i/backup/c-2094960375- 20010720-00 <<= Controlfile backup 4 435583785 A /proj/SME9i/backup/PROD9_closed_ 04cvcup9_1_1 <<= DB Backupset 5 435583785 A /proj/SME9i/backup/PROD9_closed_ 05cvcup9_1_1 <<= DB Backupset 6 435583876 A /proj/SME9i/backup/c-2094960375- 20010720-01 <<= Controlfile backup 7 435585994 PROD9_CLOSED_LVL0 A /proj/SME9i/backup/PROD9_closed_ 07cvd0ua_1_1 <<= DB Backupset 8 435585994 PROD9_CLOSED_LVL0 A /proj/SME9i/backup/PROD9_closed_ 08cvd0ua_1_1 <<= DB Backupset 9 435586035 A /proj/SME9i/backup/c-2094960375- 20010720-02 <<= Controlfile backup 10 435586876 PROD9_CLOSED_LVL0 A /proj/SME9i/backup/PROD9_closed_ 0acvd1ps_1_1 <<= DB Backupset 11 435586876 PROD9_CLOSED_LVL0 A /proj/SME9i/backup/PROD9_closed_ 0bcvd1ps_1_1 <<= DB Backupset 12 435586944 A /proj/SME9i/backup/c-2094960375- 20010720-03 <<= Controlfile backup 12 rows selected. What do we have in the backup directory? [otcsol1]/proj/SME9i/backup> ls -g ls -FC -g total 1987620 -rw-r----- 1 udba 1511936 Jul 20 10:09 c-2094960375-20010720-00 -rw-r----- 1 udba 1511936 Jul 20 11:31 c-2094960375-20010720-01 -rw-r----- 1 udba 1511936 Jul 20 12:07 c-2094960375-20010720-02 -rw-r----- 1 udba 1511936 Jul 20 12:22 c-2094960375-20010720-03 -rw-r----- 1 udba 129495552 Jul 20 10:09 PROD9_closed_01cvcpvf_1_1 -rw-r----- 1 udba 122544640 Jul 20 10:09 PROD9_closed_02cvcpvf_1_1 -rw-r----- 1 udba 129909248 Jul 20 11:30 PROD9_closed_04cvcup9_1_1 -rw-r----- 1 udba 122544640 Jul 20 11:31 PROD9_closed_05cvcup9_1_1 -rw-r----- 1 udba 129909248 Jul 20 12:06 PROD9_closed_07cvd0ua_1_1 -rw-r----- 1 udba 122544640 Jul 20 12:07 PROD9_closed_08cvd0ua_1_1 -rw-r----- 1 udba 129909248 Jul 20 12:22 PROD9_closed_0acvd1ps_1_1 -rw-r----- 1 udba 122544640 Jul 20 12:22 PROD9_closed_0bcvd1ps_1_1 -rw-r----- 1 udba 1503232 Jul 20 12:22 snapf_prod9.f [otcsol1]/proj/SME9i/backup> What backups obsolete now? RMAN> report obsolete; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 3 Report of obsolete backups and copies Type Key Completion Time Filename/Handle -------------------- ------ ------------------ -------------------- Backup Set 1 20-JUL-01 Backup Piece 1 20-JUL-01 /proj/SME9i/backup/PROD9_closed_0 1cvcpvf_1_1 Backup Set 2 20-JUL-01 Backup Piece 2 20-JUL-01 /proj/SME9i/backup/PROD9_closed_0 2cvcpvf_1_1 Backup Set 3 20-JUL-01 Backup Piece 3 20-JUL-01 /proj/SME9i/backup/c-2094960375-2 0010720-00 We now have an obsolete controlfile and 2 bakup sets. These are the 1st backups we took. Backupsets 1, 2, and 3. To remove the backupsets that exceed the retention policy set run "delete obsolete" command and you will be prompted for Y/N to remove the physical backupsets and pieces. RMAN> delete obsolete; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 3 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=9 devtype=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: sid=10 devtype=DISK Deleting the following obsolete backups and copies: Type Key Completion Time Filename/Handle -------------------- ------ ------------------ -------------------- Backup Set 1 20-JUL-01 Backup Piece 1 20-JUL-01 /proj/SME9i/backup/PROD9_closed_0 1cvcpvf_1_1 Backup Set 2 20-JUL-01 Backup Piece 2 20-JUL-01 /proj/SME9i/backup/PROD9_closed_0 2cvcpvf_1_1 Backup Set 3 20-JUL-01 Backup Piece 3 20-JUL-01 /proj/SME9i/backup/c-2094960375-2 0010720-00 Do you really want to delete the above objects (enter YES or NO)? y deleted backup piece backup piece handle=/proj/SME9i/backup/PROD9_closed_01cvcpvf_1_1 recid=1 stamp=4 35578863 deleted backup piece backup piece handle=/proj/SME9i/backup/PROD9_closed_02cvcpvf_1_1 recid=2 stamp=4 35578863 deleted backup piece backup piece handle=/proj/SME9i/backup/c-2094960375-20010720-00 recid=3 stamp=43 5578987 The backups are removed from the backup directory and the controlfile. [otcsol1]/proj/SME9i/backup> ls -g ls -FC -g total 1492068 -rw-r----- 1 udba 1511936 Jul 20 11:31 c-2094960375-20010720-01 -rw-r----- 1 udba 1511936 Jul 20 12:07 c-2094960375-20010720-02 -rw-r----- 1 udba 1511936 Jul 20 12:22 c-2094960375-20010720-03 -rw-r----- 1 udba 129909248 Jul 20 11:30 PROD9_closed_04cvcup9_1_1 -rw-r----- 1 udba 122544640 Jul 20 11:31 PROD9_closed_05cvcup9_1_1 -rw-r----- 1 udba 129909248 Jul 20 12:06 PROD9_closed_07cvd0ua_1_1 -rw-r----- 1 udba 122544640 Jul 20 12:07 PROD9_closed_08cvd0ua_1_1 -rw-r----- 1 udba 129909248 Jul 20 12:22 PROD9_closed_0acvd1ps_1_1 -rw-r----- 1 udba 122544640 Jul 20 12:22 PROD9_closed_0bcvd1ps_1_1 -rw-r----- 1 udba 1503232 Jul 20 12:22 snapf_prod9.f There were 13 backup objects before delete obsolete and there are now 10. The 3 backup sets are removed. Looking at it in RMAN now: RMAN> list backup by file; List of Datafile Backups ======================== File Key TY LV S Ckp SCN Ckp Time #Pieces #Copies Tag ---- ------- - -- - ---------- --------- ------- ------- --- 1 11 B F A 100734 20-JUL-01 1 1 PROD9_CLOSED_LVL0 8 B F A 99238 20-JUL-01 1 1 PROD9_CLOSED_LVL0 5 B F A 97763 20-JUL-01 1 1 2 10 B F A 100734 20-JUL-01 1 1 PROD9_CLOSED_LVL0 7 B F A 99238 20-JUL-01 1 1 PROD9_CLOSED_LVL0 4 B F A 97763 20-JUL-01 1 1 3 10 B F A 100734 20-JUL-01 1 1 PROD9_CLOSED_LVL0 7 B F A 99238 20-JUL-01 1 1 PROD9_CLOSED_LVL0 4 B F A 97763 20-JUL-01 1 1 4 11 B F A 100734 20-JUL-01 1 1 PROD9_CLOSED_LVL0 8 B F A 99238 20-JUL-01 1 1 PROD9_CLOSED_LVL0 5 B F A 97763 20-JUL-01 1 1 5 11 B F A 100734 20-JUL-01 1 1 PROD9_CLOSED_LVL0 8 B F A 99238 20-JUL-01 1 1 PROD9_CLOSED_LVL0 5 B F A 97763 20-JUL-01 1 1 6 10 B F A 100734 20-JUL-01 1 1 PROD9_CLOSED_LVL0 7 B F A 99238 20-JUL-01 1 1 PROD9_CLOSED_LVL0 4 B F A 97763 20-JUL-01 1 1 7 11 B F A 100734 20-JUL-01 1 1 PROD9_CLOSED_LVL0 8 B F A 99238 20-JUL-01 1 1 PROD9_CLOSED_LVL0 5 B F A 97763 20-JUL-01 1 1 8 10 B F A 100734 20-JUL-01 1 1 PROD9_CLOSED_LVL0 7 B F A 99238 20-JUL-01 1 1 PROD9_CLOSED_LVL0 4 B F A 97763 20-JUL-01 1 1 List of Controlfile Backups =========================== CF Ckp SCN Ckp Time BS Key S #Pieces #Copies Tag ---------- --------- ------- - ------- ------- --- 100734 20-JUL-01 12 A 1 1 99238 20-JUL-01 9 A 1 1 97763 20-JUL-01 6 A 1 1 The records are no longer available (RECID 1, 2, and 3) in RMAN. set pagesize 60 column handle format a32 column tag format a18 select RECID,SET_STAMP, TAG, STATUS, HANDLE from v$backup_piece order by SET_STAMP; RECID SET_STAMP TAG S HANDLE ---------- ---------- ------------------ - -------------------------------- 1 435578863 D /proj/SME9i/backup/PROD9_closed_ 01cvcpvf_1_1 2 435578863 D /proj/SME9i/backup/PROD9_closed_ 02cvcpvf_1_1 3 435578986 D /proj/SME9i/backup/c-2094960375- 20010720-00 4 435583785 A /proj/SME9i/backup/PROD9_closed_ 04cvcup9_1_1 5 435583785 A /proj/SME9i/backup/PROD9_closed_ 05cvcup9_1_1 6 435583876 A /proj/SME9i/backup/c-2094960375- 20010720-01 7 435585994 PROD9_CLOSED_LVL0 A /proj/SME9i/backup/PROD9_closed_ 07cvd0ua_1_1 8 435585994 PROD9_CLOSED_LVL0 A /proj/SME9i/backup/PROD9_closed_ 08cvd0ua_1_1 9 435586035 A /proj/SME9i/backup/c-2094960375- 20010720-02 10 435586876 PROD9_CLOSED_LVL0 A /proj/SME9i/backup/PROD9_closed_ 0acvd1ps_1_1 11 435586876 PROD9_CLOSED_LVL0 A /proj/SME9i/backup/PROD9_closed_ 0bcvd1ps_1_1 12 435586944 A /proj/SME9i/backup/c-2094960375- 20010720-03 12 rows selected. select TYPE, RECORDS_TOTAL, RECORDS_USED from v$controlfile_record_section where type like '%BACKUP%'; TYPE RECORDS_TOTAL RECORDS_USED ------------------ ------------- ------------ BACKUP SET 101 12 BACKUP PIECE 204 12 BACKUP DATAFILE 210 36 BACKUP REDOLOG 53 0 BACKUP CORRUPTION 185 0 Because we are maintaining the backups using a set backup policy the records_used will increase/decrease as you manage your backups. 2. Restore of a database w/o a catalog and noachivelog mode. Restoring using the backups you've taken. Now the fun begins. First we'll remove the database to simulate our own disaster to recover from. [otcsol1]/proj/SME9i/prod9/rmanlab> cd ../data [otcsol1]/proj/SME9i/prod9/data> ls -ltr total 1320832 -rw-r----- 1 usupport udba 41947136 Jul 12 14:49 temp01.dbf -rw-r----- 1 usupport udba 1049088 Jul 20 13:59 redo03.log -rw-r----- 1 usupport udba 1049088 Jul 20 13:59 redo02.log -rw-r----- 1 usupport udba 26218496 Jul 20 14:00 users01.dbf -rw-r----- 1 usupport udba 209719296 Jul 20 14:00 undotbs01.dbf -rw-r----- 1 usupport udba 10489856 Jul 20 14:00 tools01.dbf -rw-r----- 1 usupport udba 340791296 Jul 20 14:00 system01.dbf -rw-r----- 1 usupport udba 1049088 Jul 20 14:00 redo01.log -rw-r----- 1 usupport udba 26218496 Jul 20 14:00 indx01.dbf -rw-r----- 1 usupport udba 10489856 Jul 20 14:00 example01.dbf -rw-r----- 1 usupport udba 20975616 Jul 20 14:00 drsys01.dbf -rw-r----- 1 usupport udba 20975616 Jul 20 14:00 cwmlite01.dbf -rw-r----- 1 usupport udba 1503232 Jul 20 14:00 control03.ctl -rw-r----- 1 usupport udba 1503232 Jul 20 14:00 control02.ctl -rw-r----- 1 usupport udba 1503232 Jul 20 14:00 control01.ctl [otcsol1]/proj/SME9i/prod9/data> rm * rm: remove control01.ctl (yes/no)? y rm: remove control02.ctl (yes/no)? y rm: remove control03.ctl (yes/no)? y rm: remove cwmlite01.dbf (yes/no)? y rm: remove drsys01.dbf (yes/no)? y rm: remove example01.dbf (yes/no)? y rm: remove indx01.dbf (yes/no)? y rm: remove redo01.log (yes/no)? y rm: remove redo02.log (yes/no)? y rm: remove redo03.log (yes/no)? y rm: remove system01.dbf (yes/no)? y rm: remove temp01.dbf (yes/no)? y rm: remove tools01.dbf (yes/no)? y rm: remove undotbs01.dbf (yes/no)? y rm: remove users01.dbf (yes/no)? y [otcsol1]/proj/SME9i/prod9/data> y [otcsol1]/proj/SME9i/prod9/data> ls -la total 4 drwxr-xr-x 2 usupport udba 512 Jul 20 14:20 ./ drwxr-xr-x 11 usupport udba 512 Jul 19 13:43 ../ OK it's all gone now and you have to bring it back with RMAN... Note: Make sure you set the NLS_LANG on the target for the recovery session at the Unix prompt if the database is not using the US7ASCII characterset. Ex. whatever your database characterset is you are restoring. setenv NLS_LANG AMERICAN_AMERICA.WE8ISO8859P1 or setenv NLS_LANG AMERICAN_AMERICA.UTF8 To recover the database using an autobackup of the control file without a recovery catalog: 1. Use SQL*Plus to start, but not mount, the database. For example, run: SQL> STARTUP NOMOUNT 2. Start RMAN but do not connect to the target database: % rman RMAN> 3. Set the database identifier for the target database with SET DBID. RMAN displays the DBID whenever you connect to the target. You can also get it by running LIST or by querying the catalog (refer to "Restoring When Multiple Databases Share the same Name: Example"). For example, run: SET DBID 2094960375; Note: You can use log files to determine the DBID also. Everytime RMAN connects to the database the DBID is displayed if the database is open or mounted. 4. Connect to the target database. For example, run: CONNECT TARGET 5. Restore the backup control file, then perform recovery. Do the following: a. Optionally, specify the most recent backup time stamp that RMAN can use when searching for a control file autobackup to restore. b. If a non-default format was used to create the control file, then specify a non-default format for the restore of the control file. c. If the channel that created the control file autobackup was device type sbt, then you must allocate one or more sbt channels. Because no repository is available, you cannot use automatic channels. If the autobackup was created on a disk channel, however, then you do not need to manually allocate a channel. d. Restore the autobackup of the control file, optionally setting the maximum number of days backward that RMAN can search (up to 366) and the initial sequence number that it should use in its search for the first day. e. Mount the database. Note that because the repository is now available, any automatic channels that you configured are also available. f. If the online logs are inaccessible, then restore and recover the database as described in "Performing Incomplete Restore and Recovery". You must terminate recovery by setting the UNTIL clause to a time, log sequence, or SCN before the online redo logs. If the online logs are usable, then restore and recover the database as described in "Performing Complete Restore and Recovery". In this example, the online redo logs have been lost. This example limits the restore of the control file autobackup, then performs recovery of the database to log sequence 13243, which is the most recent archived log: # manually allocate one or more channels RUN { SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE disk TO '/proj/SME9i/backup/%F'; ALLOCATE CHANNEL d1 DEVICE TYPE disk; RESTORE CONTROLFILE FROM AUTOBACKUP MAXSEQ 5 # start at sequence 5 and count down MAXDAYS 5; # start at UNTIL TIME and search back 5 days MOUNT DATABASE; } executing command: SET CONTROLFILE AUTOBACKUP FORMAT allocated channel: d1 channel d1: sid=9 devtype=DISK Starting restore at 20-JUL-01 channel d1, looking for controlfile autobackup on day: 20010720 channel d1, controlfile autobackup found: /proj/SME9i/backup/c-2094960375-200107 20-03 channel d1, controlfile autobackup restore complete replicating controlfile input filename=/proj/SME9i/prod9/data/control01.ctl output filename=/proj/SME9i/prod9/data/control02.ctl output filename=/proj/SME9i/prod9/data/control03.ctl Finished restore at 20-JUL-01 database mounted released channel: d1 r_prod9_db.log: RMAN> run { 2> restore database; 3> recover database noredo; 4> alter database open resetlogs; 5> } Backup the database after resetlogs. A new 3rd autocontrolfile backup is created. We used the old -03 to recover with so backup -03 never really existed since the current controlfile now would have been backup -03 of the controlfile. Starting Control File Autobackup at 20-JUL-01 piece handle=/proj/SME9i/backup/c-2094960375-20010720-03 comment=NONE Finished Control File Autobackup at 20-JUL-01 [otcsol1]/proj/SME9i/backup> ls -g ls -FC -g total 1985460 -rw-r----- 1 udba 1511936 Jul 20 11:31 c-2094960375-20010720-01 -rw-r----- 1 udba 1511936 Jul 20 12:07 c-2094960375-20010720-02 -rw-r----- 1 udba 1511936 Jul 20 16:18 c-2094960375-20010720-03 -rw-r----- 1 udba 129909248 Jul 20 11:30 PROD9_closed_04cvcup9_1_1 -rw-r----- 1 udba 122544640 Jul 20 11:31 PROD9_closed_05cvcup9_1_1 -rw-r----- 1 udba 129909248 Jul 20 12:06 PROD9_closed_07cvd0ua_1_1 -rw-r----- 1 udba 122544640 Jul 20 12:07 PROD9_closed_08cvd0ua_1_1 -rw-r----- 1 udba 129909248 Jul 20 12:22 PROD9_closed_0acvd1ps_1_1 -rw-r----- 1 udba 122544640 Jul 20 12:22 PROD9_closed_0bcvd1ps_1_1 -rw-r----- 1 udba 129909248 Jul 20 16:18 PROD9_closed_0dcvdfjd_1_1 -rw-r----- 1 udba 122544640 Jul 20 16:18 PROD9_closed_0ecvdfjd_1_1 -rw-r----- 1 udba 1503232 Jul 20 12:22 snapf_prod9.f [otcsol1]/proj/SME9i/backup> ls -g ls -FC -g total 992724 -rw-r----- 1 udba 1511936 Jul 20 16:18 c-2094960375-20010720-03 -rw-r----- 1 udba 129909248 Jul 20 12:22 PROD9_closed_0acvd1ps_1_1 -rw-r----- 1 udba 122544640 Jul 20 12:22 PROD9_closed_0bcvd1ps_1_1 -rw-r----- 1 udba 129909248 Jul 20 16:18 PROD9_closed_0dcvdfjd_1_1 -rw-r----- 1 udba 122544640 Jul 20 16:18 PROD9_closed_0ecvdfjd_1_1 -rw-r----- 1 udba 1503232 Jul 20 12:22 snapf_prod9.f RMAN> report need backup; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 3 Report of files with less than 3 redundant backups File #bkps Name ---- ----- ----------------------------------------------------- 1 2 /proj/SME9i/prod9/data/system01.dbf 2 2 /proj/SME9i/prod9/data/undotbs01.dbf 3 2 /proj/SME9i/prod9/data/cwmlite01.dbf 4 2 /proj/SME9i/prod9/data/drsys01.dbf 5 2 /proj/SME9i/prod9/data/example01.dbf 6 2 /proj/SME9i/prod9/data/indx01.dbf 7 2 /proj/SME9i/prod9/data/tools01.dbf 8 2 /proj/SME9i/prod9/data/users01.dbf select TYPE, RECORDS_TOTAL, RECORDS_USED from v$controlfile_record_section where type like '%BACKUP%'; TYPE RECORDS_TOTAL RECORDS_USED ------------------ ------------- ------------ BACKUP SET 101 14 BACKUP PIECE 204 14 BACKUP DATAFILE 210 44 BACKUP REDOLOG 53 0 BACKUP CORRUPTION 185 0 Summary: In this lesson you should have learned how to configure RMAN to use the target database controlfile as the recovery catalog using autocontrolfile backups. You also learned how to created a persistent backup configuration for reuse. Then you created level 0 and level 1 consistent backup using RMAN. After simulating your own disaster you successfully restored and recovered the database using the backups taken with RMAN. RELATED DOCUMENTS ----------------- Oracle9i Recovery Manager Users Guide and Reference Manuals.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17252115/viewspace-1160646/，如需轉載，請註明出處，否則將追究法律責任。

相關文章