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

最新推荐文章于 2022-09-15 14:05:30 发布

cqhiabc50405

最新推荐文章于 2022-09-15 14:05:30 发布

阅读量78

点赞数

Recover physical standby database after loss of archive log – roll forward
By Ramesh Menon

This tutorial simulates a scenario, where in you are unable to recover your standby database because of a corrupted archive log file. The premise is that the archive log is corrupted on both production and standby, and we do not have a backup of this archive log.

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

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

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

{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>

Get the current SCN of standby database

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

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

 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.

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

{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

Copy backupiece to standby database server and catalog in standby controlfile

{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.

Recover standby from RMAN

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>

Refresh controlfile of standby from production

On production

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

On standby, copy the controlfile from production

{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')

Startup and see recovery point

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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/758322/viewspace-719475/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/758322/viewspace-719475/

cqhiabc50405

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Recover physical standby database after loss of archive log – roll forward(转)

Recover physical standby database after loss of archive log – roll forwardBy Ramesh MenonThis tutorial s...
复制链接

扫一扫