A Physical Standby database relies on continuous application of archivelogs from a Primary Database to be in synch with it. In Oracle Database versions prior to 10g in the event of an archivelog gone missing or corrupt you had to rebuild the standby database from scratch.
In 10g you can use an incremental backup and recover the standby using the same to compensate for the missing archivelogs as shown below
In the case below archivelogs with sequence numbers 137 and 138 which are required on the standby are deleted to simulate this problem.
Step 1: On the standby database check the current scn.
STDBY>>select current_scn from v$database;
CURRENT_SCN
-----------
4793543
Step 2: On the primary database create the needed incremental backup from the above SCN
C:\Documents and Settings\frego>rman target /
Recovery Manager: Release 10.2.0.2.0 - Production on Wed Oct 25 13:39:04 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: USYD (DBID=768471617)
RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN 4793543 DATABASE
FORMAT 'C:\temp\bkup_%U';
Starting backup at 25/OCT/06
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\USYD\SYSTEM01.DBF
input datafile fno=00002 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\USYD\UNDOTBS01.DBF
input datafile fno=00003 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\USYD\SYSAUX01.DBF
input datafile fno=00004 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\USYD\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 25/OCT/06
channel ORA_DISK_1: finished piece 1 at 25/OCT/06
piece handle=C:\TEMP\BKUP_02I0NSNE_1_1 tag=TAG20061025T134102 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 25/OCT/06
channel ORA_DISK_1: finished piece 1 at 25/OCT/06
piece handle=C:\TEMP\BKUP_03I0NSOI_1_1 tag=TAG20061025T134102 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 25/OCT/06
Step 3: Cancel managed recovery at the standby database
STDBY>>recover managed standby database cancel;
Media recovery complete.
Move the backup files to a new folder called INCR_BACKUP so that they are the only files in that folder.
Step 4: Catalog the Incremental Backup Files at the Standby Database
C:\oracle\product\10.2.0\db_1\database>rman target /
Recovery Manager: Release 10.2.0.2.0 - Production on Wed Oct 25 14:54:27 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: USYD (DBID=768471617, not open)
RMAN> CATALOG START WITH 'C:\temp\INCR_BACKUP';
searching for all files that match the pattern C:\temp\INCR_BACKUP
List of Files Unknown to the Database
=====================================
File Name: C:\TEMP\INCR_BACKUP\BKUP_02I0NSNE_1_1
File Name: C:\TEMP\INCR_BACKUP\BKUP_03I0NSOI_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: C:\TEMP\INCR_BACKUP\BKUP_02I0NSNE_1_1
File Name: C:\TEMP\INCR_BACKUP\BKUP_03I0NSOI_1_1
Step 5: Apply the Incremental Backup to the Standby Database
RMAN> RECOVER DATABASE NOREDO;
Starting recover at 25/OCT/06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=151 devtype=DISK
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: C:\ORACLE\PRODUCT\10.2.0\ORADATA\STDB
Y\SYSTEM01.DBF
destination for restore of datafile 00002: C:\ORACLE\PRODUCT\10.2.0\ORADATA\STDB
Y\UNDOTBS01.DBF
destination for restore of datafile 00003: C:\ORACLE\PRODUCT\10.2.0\ORADATA\STDB
Y\SYSAUX01.DBF
destination for restore of datafile 00004: C:\ORACLE\PRODUCT\10.2.0\ORADATA\STDB
Y\USERS01.DBF
channel ORA_DISK_1: reading from backup piece C:\TEMP\INCR_BACKUP\BKUP_02I0NSNE_
1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\TEMP\INCR_BACKUP\BKUP_02I0NSNE_1_1 tag=TAG20061025T134102
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished recover at 25/OCT/06
Step 6: Put the standby database back to managed recovery mode.
STDBY>> recover managed standby database nodelay disconnect;
Media recovery complete.
From the alert.log you will notice that the standby database is still looking for the old log files
*************************************************
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 137-137
DBID 768471617 branch 600609988
**************************************************
This is because the controlfile has not been updated.
Hence the standby controlfile has to be recreated
On the primary
PRIM>>alter database create standby controlfile as 'C:\temp\standby01.ctl';
System altered.
Copy the standby control file to the standby site and restart the standby database in managed recovery mode...
Managed Standby Recovery not using Real Time Apply
parallel recovery started with 2 processes
Media Recovery Waiting for thread 1 sequence 139
As you can see from the above output the standby is now looking for the next archivelog.
If you have the luxury of time at your disposal you can do an export of the standby database in read only mode.
10 comments:
Hi Rego,
Good example.
The incremental backup as of SCN can be used in Oracle Database 10g R2 to roll forward standby database. In Oracle Database 10g R1, the incrementally updated backup (Note: 290814.1) is used to roll forward the standby database.
Vaibhav
Hi
Good explanation of the feature with good example.
"Copy the standby control file to the standby site and restart the standby database in managed recovery mode..."
How do I copy the Standby Controlfile to Standby Site , if the ASM is in use ?
I do not have access to an ASM database at the moment
but if you connect to the standby instance using RMAN
you should be able to
do something like below
restore controlfile to '+DG' from '/tmp/standby01.ctl';
Lemme know how it goes
Do we have to rename data file and log files after using control file from primary on standby? Contol file from primary will hold names of primary data file and log files, right?
Manish
Manish,
Yes you do. When you mount the standby database you can see the names of the datafiles your archivelogs will expect by doing a
select * from v$datafile;
Thanks for a good clue. This saved lot of my time identify issue with the control file. I was looking else where, spent almost an hour..
How do you rename the data file and log files after restore controlfile to standby with diff directory? The v$datafile still holds old datafile info. Several datafiles were added on primary but did not appear on standby.
hi,
I have followed the steps and applied the incremental backup's. I have created standby controlfile on primary, shutdown the standby and then copied the standby controlfile as control01.ctl, control02.clt and then startup mount.
but I get the following error:
RMAN> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "recover": expecting one of: "mount, open"
RMAN-01007: at line 1 column 16 file: standard input
Also,
On primary :
SQL> alter system switch logfile;
SQL> SELECT MAX(R.SEQUENCE#) LAST_SEQ_RECD, MAX(L.SEQUENCE#) LAST_SEQ_SENT FROM
V$ARCHIVED_LOG R, V$LOG L WHERE
R.DEST_ID=2 AND L.ARCHIVED='YES';
LAST_SEQ_RECD LAST_SEQ_SENT
------------- -------------
8950 8950
on standby
idle> SELECT MAX(R.SEQUENCE#) LAST_SEQ_RECD, MAX(L.SEQUENCE#) LAST_SEQ_SENT FROM
V$ARCHIVED_LOG R, V$LOG L WHERE
R.DEST_ID=2 AND L.ARCHIVED='YES';
LAST_SEQ_RECD LAST_SEQ_SENT
------------- -------------
8949 8951
Why is the LAST_SEQ_SENT on the standby ahead of the primary ??
Can you please advise. Thanks
Great tutorial.
For ASM database, I found related topic here: http://www.oracle-forums.com/roll-forward-a-physical-standby-database-10g-using-rman-incremental-backups-t696.html
Post a Comment