Recover physical standby database after loss of archive log – roll forward(轉)

The tutorial shows an example of how we can use RMAN incremental backup to recover the standby in such a scenario.

1
2
3
4
5
6
7
8
9

Source 
 DBNAME   PRODDB 
 Oracle Home  /u01/ora10g
 Archive Dest  /u02/PRODDB/arch
 
Destination 
 DBNAME   PRODDB
 Oracle Home  /u01/ora10g
 Archive Dest  /u02/PRODDB/arch

1. Synch primary and standby. Defer application of logs on standby.
2. Let us now create some dummy tables and switch the logfile on primary. This is the log file that would be needed for recovery

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44

{PRIMARY} /u01/ora10g/backup $ sqlplus   SQL*Plus: Release 10.2.0.3.0 - Production on Wed May 19 12:37:34 2010   Copyright (c) 1982, 2006, Oracle. All Rights Reserved.   Enter user-name: demo/demo   Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, OLAP and Data Mining options   SQL> select * from tab;   TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- OBJLIST TABLE   SQL> create table object_list as select * from dba_objects 2 union select * from dba_objects;   Table created.   SQL> conn / as sysdba Connected. SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /u02/PRODDB/arch/ Oldest online log sequence 59 Next log sequence to archive 61 Current log sequence 61 SQL> alter system switch logfile;   System altered.   SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u02/PRODDB/arch/ Oldest online log sequence 60 Next log sequence to archive 62 Current log sequence 62 SQL>

3. Get the current SCN of standby database

1 2 3 4 5 6 7

STBYHOST(PRODDB)SQL >select current_scn from v$database;   CURRENT_SCN -------------------------------- 7765466164256   STBYHOST(PRODDB)SQL >

4. To find out which is the next archive log the standby database needs for recovery, run the recover command on the standby database without applying the archive logs

1 2 3 4 5 6 7 8 9 10 11

STBYHOST(PRODDB)SQL >recover standby database ; ORA-00279: change 7765466164257 generated at 05/19/2010 11:55:11 needed for thread 1 ORA-00289: suggestion : /u02/PRODDB/arch/PRODDB_1_717173775_60.arc ORA-00280: change 7765466164257 for thread 1 is in sequence #60     Specify log: {=suggested | filename | AUTO | CANCEL} CANCEL Media recovery cancelled. STBYHOST(PRODDB)SQL >

Archive Sequence 60 is needed for recovery. Let us now assume that this archive log is not available on disk or on tape backup.

5. Use SCN in step 3 to take an incremental backup on disk on primary database

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

{PRIMARY} /u01/ora10g/backup $ export ORACLE_SID=PRODDB {PRIMARY} /u01/ora10g/backup $ rman target / .... RMAN> BACKUP INCREMENTAL FROM SCN 7765466164256 DATABASE FORMAT '/u01/ora10g/backup/proddb_standby_%U' tag 'proddb_standby'; Starting backup at 19-MAY-10 using channel ORA_DISK_1 RMAN-06755: WARNING: datafile 5: incremental-start SCN is too recent; using checkpoint SCN 7765465268142 instead RMAN-06755: WARNING: datafile 6: incremental-start SCN is too recent; using checkpoint SCN 7765465268142 instead channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00003 name=/restorevol/BACKUPREST/PRODDB/data/PRODDB_SYSAUX_01.dbf input datafile fno=00005 name=/restorevol/BACKUPREST/PRODDB/data/PRODDB_TS_LMTDATA_01 skipping datafile 00005 because it has not changed input datafile fno=00006 name=/restorevol/BACKUPREST/PRODDB/data/PRODDB_TS_LMTDATA_02.dbf skipping datafile 00006 because it has not changed input datafile fno=00001 name=/restorevol/BACKUPREST/PRODDB/data/PRODDB_SYSTEM_01.dbf input datafile fno=00002 name=/restorevol/BACKUPREST/PRODDB/data/PRODDB_UNDOTBS_01.dbf input datafile fno=00004 name=/restorevol/BACKUPREST/PRODDB/data/PRODDB_TOOLS_01.dbf channel ORA_DISK_1: starting piece 1 at 19-MAY-10 channel ORA_DISK_1: finished piece 1 at 19-MAY-10 piece handle=/u01/ora10g/backup/proddb_standby_fjle2nl3_1_1 tag=proddb_standby comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:55 Finished backup at 19-MAY-10

6. Copy backupiece to standby database server and catalog in standby controlfile

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

{STBYHOST} /u01/ora10g/backup/PRODDB $ scp ora10r3@PRIMARY:/u01/ora10g/backup/proddb_standby_fjle2nl3_1_1 . Password: Password: proddb_standby_fjle2nl3_ 100% |********************************************************************************************| 5848 KB 00:00 {STBYHOST} /misuatdata/PRODDB $ pwd /misuatdata/PRODDB {STBYHOST} /u01/PRODDB $ rman target /   Recovery Manager: Release 10.2.0.3.0 - Production on Wed May 19 13:04:30 2010   Copyright (c) 1982, 2005, Oracle. All rights reserved.   connected to target database: PRODDB (DBID=2326455671, not open)   RMAN> CATALOG START WITH '/u01/ora10g/backup/PRODDB/proddb_standby';   searching for all files that match the pattern /u01/ora10g/backup/PRODDB/proddb_standby   List of Files Unknown to the Database ===================================== File Name: /u01/ora10g/backup/PRODDB/proddb_standby_fjle2nl3_1_1   Do you really want to catalog the above files (enter YES or NO)? YES cataloging files... cataloging done   List of Cataloged Files ======================= File Name: /u01/ora10g/backup/PRODDB/proddb_standby_fjle2nl3_1_1   RMAN> exit     Recovery Manager complete.

7. Recover standby from RMAN

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

RMAN> RECOVER DATABASE NOREDO 2> ;   Starting recover at 19-MAY-10 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=148 devtype=DISK datafile 5 not processed because file is read-only datafile 6 not processed because file is read-only channel ORA_DISK_1: starting incremental datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set destination for restore of datafile 00001: /u02/PRODDB/oradata/data/PRODDB_SYSTEM_01.dbf destination for restore of datafile 00002: /u02/PRODDB/oradata/data/PRODDB_UNDOTBS_01.dbf destination for restore of datafile 00003: /u02/PRODDB/oradata/data/PRODDB_SYSAUX_01.dbf destination for restore of datafile 00004: /u02/PRODDB/oradata/data/PRODDB_TOOLS_01.dbf channel ORA_DISK_1: reading from backup piece /u01/ora10g/backup/PRODDB/proddb_standby_fjle2nl3_1_1 channel ORA_DISK_1: restored backup piece 1 piece handle=/u01/ora10g/backup/PRODDB/proddb_standby_fjle2nl3_1_1 tag=proddb_standby channel ORA_DISK_1: restore complete, elapsed time: 00:00:16 Finished recover at 19-MAY-10   RMAN>

8. Refresh controlfile of standby from production

On production

1 2 3 4 5

SQL> alter database create standby controlfile as '/tmp/proddb_stby.ctl';   Database altered.   SQL>

On standby, copy the controlfile from production

1 2 3 4

{STBYHOST} /u02/PRODDB/oradata/cntrl $ scp ora10r3@PRIMARY:/tmp/proddb_stby.ctl . Password: proddb_stby.ctl 100% |********************************************************************************************| 14096 KB 00:01 {STBYHOST} /u02/PRODDB/oradata/cntrl $

Change init.ora

1	control_files = ('/u02/PRODDB/oradata/cntrl/proddb_stby.ctl')

9. Startup and see recovery point

1 2 3 4 5 6 7 8

STBYHOST(PRODDB)SQL >recover standby database; ORA-00279: change 7765466166167 generated at 05/19/2010 12:54:59 needed for thread 1 ORA-00289: suggestion : /u02/PRODDB/arch/PRODDB_1_717173775_62.arc ORA-00280: change 7765466166167 for thread 1 is in sequence #62     Specify log: {=suggested | filename | AUTO | CANCEL}

You can see that the recovery has skipped archive 60 and 61, which were part of the incremental backup.