A! Help: Roll Foward a Physical Standby on 11gR1

Friday, January 21, 2011

Roll Foward a Physical Standby on 11gR1

Incremental backups could be used to roll forward a physical standby that lags far behind the primary or has archivelog gaps that cannot be resolved manually (archive log could be already deleted). The steps on this are documented on Oracle Data Guard Concepts and Administration. But this doesn't work exactly as it is. Also these steps are bit different from the original 10gR2 documentation which only specified taking a incremental backup and doing a noredo recovery. There was no need to restore the control file. But in this 11gR1 documents as well as current 10gR2 documents it's mentioned to restore the standby controlfile.

It is assumed there's a valid data guard configuration and it's functioning properly. Then a missing archive log situation is created by shutting down the standby DB,defering the log shipping and deleting the logs created using rman.

1. Once the standby is started after this and log shipping is enabled, expect to see a archive log gap.

SEQUENCE# APPLIED
---------- ---------
      1187 NO
      1179 YES
      1178 YES
      1177 YES
      1176 YES
      1175 YES
      1174 YES
      1173 YES
      1172 YES

SQL> select * from v$archive_gap;

   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
         1          1179           1186

2. Cancel the log apply service and get the current scn of the standby database.

SQL>  alter database recover managed standby database cancel;

Database altered.

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
   12197632

3. Create an incremental backup on the standby starting with the scn obtain above. It helps to have automatic controlfile backup on at this stage.

RMAN> BACKUP INCREMENTAL FROM SCN 12197632 DATABASE FORMAT '/home/oracle/forstandby%U' tag 'forstandby';

Starting backup at 21-Jan-2011 14:45:59

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1056 device type=DISK
backup will be obsolete on date 28-Jan-2011 14:46:00
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/data/oradata/ENT11G1/datafile/o1_mf_ravelbo_5zb2lltr_.dbf
input datafile file number=00007 name=/data/oradata/ENT11G1/datafile/o1_mf_bxindex_5zb2lzvc_.dbf
input datafile file number=00008 name=/data/oradata/ENT11G1/datafile/o1_mf_bxlobs_5zb2mf60_.dbf
input datafile file number=00002 name=/data/oradata/ENT11G1/datafile/o1_mf_sysaux_5q7x746p_.dbf
input datafile file number=00001 name=/data/oradata/ENT11G1/datafile/o1_mf_system_5q7x745y_.dbf
input datafile file number=00003 name=/data/oradata/ENT11G1/datafile/o1_mf_undotbs1_5q7x746t_.dbf
input datafile file number=00004 name=/data/oradata/ENT11G1/datafile/o1_mf_users_5q7x747g_.dbf
input datafile file number=00005 name=/data/oradata/ENT11G1/datafile/o1_mf_example_5q7xg0kk_.dbf
channel ORA_DISK_1: starting piece 1 at 21-Jan-2011 14:46:00
channel ORA_DISK_1: finished piece 1 at 21-Jan-2011 14:47:45
piece handle=/home/oracle/forstandby12m2m3t8_1_1 tag=FORSTANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/data/oradata/ENT11G1/datafile/o1_mf_tbx2ktbs_5zb2t0jn_.dbf
channel ORA_DISK_1: starting piece 1 at 21-Jan-2011 14:47:45
channel ORA_DISK_1: finished piece 1 at 21-Jan-2011 14:47:48
piece handle=/home/oracle/forstandby13m2m40h_1_1 tag=FORSTANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

using channel ORA_DISK_1
backup will be obsolete on date 28-Jan-2011 14:47:48
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 21-Jan-2011 14:47:49
channel ORA_DISK_1: finished piece 1 at 21-Jan-2011 14:47:50
piece handle=/home/oracle/forstandby14m2m40k_1_1 tag=FORSTANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-Jan-2011 14:47:50

4. Copy the incrmental backup files from the primary db server to standby db server and catalog them by running the following command on standby.

RMAN> catalog start with '/home/oracle/forstand';

Starting implicit crosscheck backup at 21-JAN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1069 device type=DISK
Finished implicit crosscheck backup at 21-JAN-11

Starting implicit crosscheck copy at 21-JAN-11
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 21-JAN-11

searching for all files in the recovery area
cataloging files...
no files cataloged

searching for all files that match the pattern /home/oracle/forstand

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/forstandby14m2m40k_1_1
File Name: /home/oracle/forstandby13m2m40h_1_1
File Name: /home/oracle/forstandby12m2m3t8_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: /home/oracle/forstandby14m2m40k_1_1
File Name: /home/oracle/forstandby13m2m40h_1_1
File Name: /home/oracle/forstandby12m2m3t8_1_1

5. Start the standby database in nomount mode

RMAN> startup force nomount;

Oracle instance started

Total System Global Area    4275781632 bytes

Fixed Size                     2166536 bytes
Variable Size                788529400 bytes
Database Buffers            3472883712 bytes
Redo Buffers                  12201984 bytes

6. The next step on the oracle documentation is to restore the standby controlfile but this will not work (unless connected to recovery catalog)

RMAN> RESTORE STANDBY CONTROLFILE FROM TAG 'forstandby';

Starting restore at 21-JAN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1088 device type=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/21/2011 15:13:58
RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP

7. Create the standby controlfile from one of the incrimental backup that was identified earlier as the one carrying the controlfile backup

RMAN> restore standby controlfile from '/home/oracle/forstandby14m2m40k_1_1';

Starting restore at 21-JAN-11
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/data/oradata/STDBY/controlfile/control01.ctl
Finished restore at 21-JAN-11

8. Mount the database and run the recover noredo command. In cases with OMF or ASM useage it might require datafiles to be renamed using alter database rename file ...

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> recover database noredo;

Starting recover at 21-JAN-11
Starting implicit crosscheck backup at 21-JAN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1075 device type=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 21-JAN-11

Starting implicit crosscheck copy at 21-JAN-11
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 21-JAN-11

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /data/flash/STDBY/archivelog/2011_01_21/o1_mf_1_1176_6mm828b5_.arc
File Name: /data/flash/STDBY/archivelog/2011_01_21/o1_mf_1_1179_6mm829sr_.arc
File Name: /data/flash/STDBY/archivelog/2011_01_21/o1_mf_1_1178_6mm8293o_.arc
File Name: /data/flash/STDBY/archivelog/2011_01_21/o1_mf_1_1172_6mm7rk5o_.arc
File Name: /data/flash/STDBY/archivelog/2011_01_21/o1_mf_1_1173_6mm7rkhd_.arc
File Name: /data/flash/STDBY/archivelog/2011_01_21/o1_mf_1_1174_6mm7rp6j_.arc
File Name: /data/flash/STDBY/archivelog/2011_01_21/o1_mf_1_1187_6mm8d368_.arc
File Name: /data/flash/STDBY/archivelog/2011_01_21/o1_mf_1_1175_6mm827ww_.arc
File Name: /data/flash/STDBY/archivelog/2011_01_21/o1_mf_1_1177_6mm828oz_.arc
File Name: /data/flash/STDBY/archivelog/2011_01_21/o1_mf_1_1186_6mm8f5jb_.arc

using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /data/oradata/STDBY/datafile/o1_mf_system_5q7x745y_.dbf
destination for restore of datafile 00002: /data/oradata/STDBY/datafile/o1_mf_sysaux_5q7x746p_.dbf
destination for restore of datafile 00003: /data/oradata/STDBY/datafile/o1_mf_undotbs1_5q7x746t_.dbf
destination for restore of datafile 00004: /data/oradata/STDBY/datafile/o1_mf_users_5q7x747g_.dbf
destination for restore of datafile 00005: /data/oradata/STDBY/datafile/o1_mf_example_5q7xg0kk_.dbf
destination for restore of datafile 00006: /data/oradata/STDBY/datafile/o1_mf_ravelbo_5zb2lltr_.dbf
destination for restore of datafile 00007: /data/oradata/STDBY/datafile/o1_mf_bxindex_5zb2lzvc_.dbf
destination for restore of datafile 00008: /data/oradata/STDBY/datafile/o1_mf_bxlobs_5zb2mf60_.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/forstandby12m2m3t8_1_1
channel ORA_DISK_1: piece handle=/home/oracle/forstandby12m2m3t8_1_1 tag=FORSTANDBY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00009: /data/oradata/STDBY/datafile/o1_mf_tbx2ktbs_5zb2t0jn_.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/forstandby13m2m40h_1_1
channel ORA_DISK_1: piece handle=/home/oracle/forstandby13m2m40h_1_1 tag=FORSTANDBY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished recover at 21-JAN-11

9. At the end of the recovery enable log apply and check the apply status of the archive logs

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

SQL> select sequence#,applied from v$archived_log order by 1 desc;

 SEQUENCE# APPLIED
---------- ---------
      1188 IN-MEMORY
      1187 NO
      1186 NO
      1179 NO
      1178 NO
      1177 NO
      1176 NO
      1175 NO
      1174 NO
      1173 NO
      1172 NO

10. Run several log switches on the primary and check the log apply status on the standby to confrim logs are applied

SQL> alter system switch logfile;

System altered.    

SQL> select sequence#,applied from v$archived_log order by 1 desc;

 SEQUENCE# APPLIED
---------- ---------
      1193 IN-MEMORY
      1192 YES
      1191 YES
      1190 YES
      1189 YES
      1188 YES
      1187 NO
      1186 NO
      1179 NO
      1178 NO
      1177 NO

More on metalink note Roll Forward a Physical Standby Database Using RMAN Incremental Backups "Recover Database Noredo" Does Nothing [ID 841765.1]

A! Help

Labels

Friday, January 21, 2011

Roll Foward a Physical Standby on 11gR1

About Me

Downloads

Quick Response

Popular

Blog Archive

Total Pageviews

Followers

Oracle Documentation