Roll Forward Physical Standby Database using RMAN incremental backup
There would be scenarios where the standby database lags far behind from the primary database leading to Archive Gap. It could be due to one of the following reasons
1. Might be due to the network outage between the primary and the standby database leading to the archive gaps. Data guard would be able to detect the archive gaps automatically and can fetch the missing logs as soon as the connection is re-established.
2. It could also be due to archive logs getting missed out on the primary database or the archives getting corrupted and there would be no valid backups.
In such cases where the standby lags far behind from the primary database, incremental backups can be used as one of the methods to roll forward the physical standby database to have it in sync with the primary database.
Oracle Database version : 11.2.0.1.0 My Oracle Database is using ASM.
Primary database : primary Standby database : standby
Primary Host : primary Standby Host : standby
The maximum archivelog sequence generated on the Primary Database is 203.
SQL> select status,instance_name,database_role from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE
------------ ---------------- ----------------
OPEN primary PRIMARY
SQL> select thread#,max(sequence#) from v$archived_log group bythread#;
THREAD# MAX(SEQUENCE#)
------- ----------------
1 203
On the standby database, the maximum archivelog sequence that is applied is sequence 159.
SQL> select status,instance_name,database_role from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE
------------ ---------------- ----------------
MOUNTED standby PHYSICAL STANDBY
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
------- ----------------
1 159
The standby database is lagging behind the primary database by around 140 archives (203 – 159).
Step 1: Take a note of the Current SCN of the Physical Standby Database.
Standby Database:
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2358948
Note down the CURRENT_SCN value of the standby database (2358948) to proceed further.
Step 2 : Cancel the Managed Recovery Process on the Standby database.
Standby Database:
1
SQL>alter database recover managed standby database cancel;
Step 3: On the Primary database, take the incremental SCN backup from the SCN that is currently recorded on the standby database (2358948)
Connect to the primary database and take the incremental SCN backup.
Primary Database:
[oracle@primary ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jan 29 11:49:26 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRIMARY (DBID=1737398252)
RMAN> backup incremental from scn 2358948 database format '/tmp/stnd_backp_%U.b ak';
Starting backup at 29-JAN-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=135 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/primary/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/primary/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/primary/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/primary/example01.dbf
input datafile file number=00004 name=/u01/app/oracle/primary/users01.dbf
channel ORA_DISK_1: starting piece 1 at 29-JAN-16
channel ORA_DISK_1: finished piece 1 at 29-JAN-16
piece handle=/tmp/stnd_backp_06qsj6e6_1_1.bak tag=TAG20160129T115550 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
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 29-JAN-16
channel ORA_DISK_1: finished piece 1 at 29-JAN-16
piece handle=/tmp/stnd_backp_07qsj6g9_1_1.bak tag=TAG20160129T115550 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 29-JAN-16
RMAN> backup current controlfile for standby format '/tmp/stnd_%U.ctl';
Starting backup at 29-JAN-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 29-JAN-16
channel ORA_DISK_1: finished piece 1 at 29-JAN-16
piece handle=/tmp/stnd_08qsj6k7_1_1.ctl tag=TAG20160129T115903 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 29-JAN-16
RMAN> quit
Primary Database :
[oracle@dev bkp]$ pwd
/u02/bkp
[oracle@dev bkp]$ ls -lrt
total 13576
-rw-r----- 1 oracle oinstall 540672 Mar 25 15:47 stnd_backp_10n6p3nl_1_1.bak
-rw-r----- 1 oracle oinstall 13336576 Mar 25 15:47 stnd_backp_11n6p3p4_1_1.bak
[oracle@dev bkp]$ scp stnd* uat:/u02/bkp
oracle@uat's password:
stnd_backp_10n6p3nl_1_1.bak 100% 528KB 528.0KB/s 00:00
stnd_backp_11n6p3p4_1_1.bak 100% 13MB 6.4MB/s 00:02
[oracle@dev bkp]$ cd /u02
[oracle@dev u02]$ ls -lrt stnd*
-rw-r----- 1 oracle oinstall 13336576 Mar 25 15:48 stnd_12n6p3qt_1_1.ctl
[oracle@dev u02]$ scp stnd* uat:/u02
oracle@uat's password:
stnd_12n6p3qt_1_1.ctl 100% 13MB 12.7MB/s 00:01
[oracle@dev u02]$
Step 6: On the standby server, connect the Standby Database through RMAN and catalog the copied incremental backups so that the Controlfile of the Standby Database would be aware of these incremental backups.
I had the incremental backuppieces copied to the location ‘/u02/bkp‘ on the standby server.
Standby Database:
[oracle@standby tmp]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jan 29 12:16:58 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRIMARY (DBID=1737398252, not open)
RMAN> catalog start with '/u02/bkp';
using target database control file instead of recovery catalog
searching for all files that match the pattern /u02/bkp
List of Files Unknown to the Database
=====================================
File Name: /u02/bkp/stnd_backp_07qsj6g9_1_1.bak
File Name: /u02/bkp/stnd_08qsj6k7_1_1.ctl
File Name: /u02/bkp/stnd_backp_06qsj6e6_1_1.bak
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u02/bkp/stnd_backp_07qsj6g9_1_1.bak
File Name: /u02/bkp/stnd_08qsj6k7_1_1.ctl
File Name: /u02/bkp/stnd_backp_06qsj6e6_1_1.bak
RMAN> recover database noredo;
Starting recover at 29-JAN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=8 device type=DISK
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: /u01/app/oracle/standby/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/standby/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/standby/undotbs01.dbf
destination for restore of datafile 00004: /u01/app/oracle/standby/users01.dbf
destination for restore of datafile 00005: /u01/app/oracle/standby/example01.dbf
channel ORA_DISK_1: reading from backup piece /u02/bkp/stnd_backp_06qsj6e6_1_1.b ak
channel ORA_DISK_1: piece handle=/u02/bkp/stnd_backp_06qsj6e6_1_1.bak tag=TAG201 60129T115550
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished recover at 29-JAN-16
RMAN> shutdown immediate
database dismounted
Oracle instance shut down
RMAN> startup nomount
connected to target database (not started)
Oracle instance started
Total System Global Area 726540288 bytes
Fixed Size 2256792 bytes
Variable Size 465567848 bytes
Database Buffers 251658240 bytes
Redo Buffers 7057408 bytes
RMAN> restore standby controlfile from '/u02/bkp/stnd_08qsj6k7_1_1.ctl';
Starting restore at 29-JAN-16
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=/u01/app/oracle/standby/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/standby/control02.ctl
Finished restore at 29-JAN-16
RMAN> shutdown immediate
Oracle instance shut down
RMAN> startup mount
Recovery Manager complete.
[oracle@standby tmp]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 29 12:32:38 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> sho parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0.0.0
SQL> alter system set compatible='11.2.0.4.0' scope=spfile;
System altered.
SQL> startup mount
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 726540288 bytes
Fixed Size 2256792 bytes
Variable Size 465567848 bytes
Database Buffers 251658240 bytes
Redo Buffers 7057408 bytes
Database mounted.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/standby/system01.dbf
/u01/app/oracle/standby/sysaux01.dbf
/u01/app/oracle/standby/undotbs01.dbf
/u01/app/oracle/standby/users01.dbf
/u01/app/oracle/standby/example01.dbf
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@standby tmp]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jan 29 12:35:37 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRIMARY (DBID=1737398252, not open)
RMAN> shutdown immediate
using target database control file instead of recovery catalog
database dismounted
Oracle instance shut down
RMAN> startup mount
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 726540288 bytes
Fixed Size 2256792 bytes
Variable Size 465567848 bytes
Database Buffers 251658240 bytes
Redo Buffers 7057408 bytes
RMAN> catalog start with '/u01/app/oracle/standby'
2> ;
Starting implicit crosscheck backup at 29-JAN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=131 device type=DISK
Crosschecked 8 objects
Finished implicit crosscheck backup at 29-JAN-16
Starting implicit crosscheck copy at 29-JAN-16
using channel ORA_DISK_1
Finished implicit crosscheck copy at 29-JAN-16
searching for all files in the recovery area
cataloging files...
no files cataloged
searching for all files that match the pattern /u01/app/oracle/standby
no files found to be unknown to the database
RMAN> catalog start with '/u01/app/oracle/standby/'
2> ;
searching for all files that match the pattern /u01/app/oracle/standby/
no files found to be unknown to the database
RMAN> quit
Recovery Manager complete.
[oracle@standby tmp]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 29 12:39:52 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter database recover managed standby database disconnectfrom session;
alter database recover managed standby database disconnectfrom session
*
ERROR at line 1:
ORA-00274: illegal recovery option DISCONNECTFROM
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CLOSING 212
RFS IDLE 0
RFS IDLE 0
RFS IDLE 213
MRP0 WAIT_FOR_LOG 213
8 rows selected.
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 212
SQL>
No comments:
Post a Comment