VMCD.ORG

Focus on DB architect mail:ylouis83#gmail.com

Recover physical standby database after loss of archive log

Posted by admin on March 20th, 2012

今天报表数据库的备库出现了问题,由于监控脚本出现了问题,主机空间耗尽 而没有及时发出邮件,导致归档无法进行,DB停滞在一个时间点,同时在主库 由于只保留了2天的归档 导致这部分归档没有传输至备库,等我们发现问题时,主库已经删除了归档,备库接近8天gap无法恢复。由于主库超过3个T ,重新用备份恢复一个standby 时间上是不允许的,并且主库上也没有空间存放这么大的备份集,于是采取了增量备份的方法,跳过了这个GAP。下面记录这次恢复过程

查看备库日志发现:

Tue Mar 13 02:45:00 2012
ARC1: Closing local archive destination LOG_ARCHIVE_DEST_1: '/data/oracle/oradata/track1/arch/1_30021_754336076.dbf' (error 19502) (track1)
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance track1 - Archival Error
ORA-16038: log 7 sequence# 30021 cannot be archived
ORA-19502: write error on file "", block number  (block size=)
ORA-00312: online log 7 thread 1: '/data/oracle/oradata/track1/standbyredo7.log'
Tue Mar 13 02:45:00 2012
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance track1 - Archival Error


Tue Mar 13 04:06:56 2012
Non critical error ORA-00001 caught while writing to trace file "/data/oracle/diag/rdbms/trackdg/track1/trace/track1_rfs_9177.trc"
Error message: Linux-x86_64 Error: 28: No space left on device
Additional information: 1
Writing to the above trace file is disabled for now on...
Creating archive destination file : /data/oracle/oradata/track1/arch/1_30038_754336076.dbf (808922 blocks)
Non critical error ORA-00001 caught while writing to trace file "/data/oracle/diag/rdbms/trackdg/track1/trace/track1_rfs_9175.trc"
Error message: Linux-x86_64 Error: 28: No space left on device
Additional information: 1
Writing to the above trace file is disabled for now on...


Fetching gap sequence in thread 1, gap sequence 30028-30127
Mon Mar 19 20:35:42 2012
Fetching gap sequence in thread 1, gap sequence 30028-30127
Mon Mar 19 20:35:52 2012
Fetching gap sequence in thread 1, gap sequence 30028-30127
Mon Mar 19 20:36:02 2012
Fetching gap sequence in thread 1, gap sequence 30028-30127
Mon Mar 19 20:36:12 2012
Fetching gap sequence in thread 1, gap sequence 30028-30127

发现从13号的2点 主机space 已经被耗尽. 13-17号的归档全部丢失(gap sequence 30028-30127)近100个归档

下面采取增量备份的方法恢复这个数据库:

在备库查找当前的SCN
 
SQL> select current_scn from v$database;
 
        
CURRENT_SCN
---------------------
--
        16895677901

        
 
 
 
 
 
在主库用这个
SCN  通过增量备份 备份出新的backupset :
 
 
 
RMAN> run {
2allocate channel c1 device type disk;
3allocate channel c2 device type disk;
4allocate channel c3 device type disk;
5allocate channel c4 device type disk;
6BACKUP INCREMENTAL FROM SCN 16895677901  DATABASE FORMAT '/data/track_standby_%U' tag 'track_standby';
7release channel c1;
8release channel c2;
9release channel c3;
10> release channel c4;
11>  }

将这个备份集 SCP 到备库的/data2 目录下 由于主备采用了convert 转换了文件的路径 需要通过set newname 来恢复备库 大致脚本如下:

[oracle@db59 track1]$ rman target /
 
Recovery Manager: Release 11.2.0.2.0 - Production on Mon Mar 19 23:44:58 2012
 
Copyright (c) 1982, 2009, Oracle and/or its affiliatesAll rights reserved.
 
connected to target database: TRACK1 (DBID=3812187020, not open)
 
RMAN> CATALOG START WITH '/data2/track_standby';
 
searching for all files that match the pattern /data2/track_standby
 
List of Files Unknown to the Database
=====================================
File Name: /data2/track_standby_76n694la_1_1
 
.. ...
 
....
 
File Name: /data2/track_standby_9bn69dqo_1_1 (一共71个)
 
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
 
List of Cataloged Files
=======================
File Name: /data2/track_standby_76n694la_1_1
 
...
 
File Name: /data2/track_standby_9bn69dqo_1_1
 
 
 
 
RMAN> run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
recover database noredo;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
 
[
oracle@db59 ~]$ tailalert
 
Incremental restore complete of datafile 280 /data/oracle/oradata/track1/lg_track3_100.dbf
 
checkpoint is 36031098956
Incremental restore complete of datafile 294 /data/oracle/oradata/track1/lg_track4_004.dbf
 
checkpoint is 36031098956
Mon Mar 19 17:54:45 2012
Incremental restore complete of datafile 28 /data/oracle/oradata/track1/lg_track18.dbf
 
checkpoint is 36025918455
 
last deallocation scn is 19965755207
Incremental restore complete of datafile 286 /data2/oracle/oradata/track1/lg_track119.dbf
 
checkpoint is 36025918455
Incremental restore complete of datafile 245 /data/oracle/oradata/track1/lg_track3_68.dbf
 
checkpoint is 36025918455
Incremental restore complete of datafile 241 /data/oracle/oradata/track1/lg_track3_64.dbf
 
..

完成后,由于主库在期间添加过数据文件,使用rman copy 将 file 296,297 copy 到本地,scp至备库的相应位置,主库生成standby controlfile 在备库通过rename file 重新定义file路径:

alter database rename file  '/data/oracle/oradata/track1/system.dbf' to    '/data/oracle/oradata/track1/system.dbf';                                   
..
alter database rename file  '/storage/disk01/lg_track3_92.dbf' to                            '/data/oracle/oradata/track1/lg_track3_92.dbf';                                   
alter database rename file  '/storage/disk02/lg_track3_93.dbf' to                            '/data/oracle/oradata/track1/lg_track3_93.dbf';                                   
alter database rename file  '/storage/disk03/lg_track3_94.dbf' to                            '/data/oracle/oradata/track1/lg_track3_94.dbf';                                   
alter database rename file  '/storage/disk04/lg_track3_95.dbf' to                            '/data/oracle/oradata/track1/lg_track3_95.dbf';                                   
alter database rename file  '/storage/disk05/lg_track3_96.dbf' to                            '/data/oracle/oradata/track1/lg_track3_96.dbf';                                   
alter database rename file  '/storage/disk06/lg_track3_97.dbf' to                            '/data/oracle/oradata/track1/lg_track3_97.dbf';                                   
alter database rename file  '/storage/disk07/lg_track3_98.dbf' to                            '/data/oracle/oradata/track1/lg_track3_98.dbf';                                   
alter database rename file  '/storage/disk00/lg_track3_99.dbf' to                            '/data/oracle/oradata/track1/lg_track3_99.dbf';                                   
alter database rename file  '/storage/disk01/lg_track3_100.dbf' to                           '/data/oracle/oradata/track1/lg_track3_100.dbf';
..
....
 
alter database rename file  '/storage/disk06/lg_track4_006.dbf' to  '/data2/oracle/oradata/track1/lg_track4_006.dbf';                                                                   
alter database rename file  '/storage/disk02/lg_track4_007.dbf' to  '/data2/oracle/oradata/track1/lg_track4_007.dbf';

添加standby logfile

SQL> alter system set standby_file_management=MANUAL;
 
System altered.
 
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 6('/data2/oracle/oradata/track1/standbyredo6.log') SIZE 500M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 7('/data2/oracle/oradata/track1/standbyredo7.log') SIZE 500M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 8('/data2/oracle/oradata/track1/standbyredo8.log') SIZE 500M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 9('/data2/oracle/oradata/track1/standbyredo9.log') SIZE 500M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 10('/data2/oracle/oradata/track1/standbyredo10.log') SIZE 500M;

开启日志apply

SQL> alter system set standby_file_management=auto;
 
System altered.
 
SQL> alter database recover managed standby database disconnect from session using current logfile;

观察日志 经过将近4个小时 终于追上了主库

Recovery of Online Redo Log: Thread 1 Group 12 Seq 31152 Reading mem 0
  Mem# 0: /data2/oracle/oradata/track1/standbyredo12.log
Tue Mar 20 00:28:09 2012
RFS[3]: Selected log 11 for thread 1 sequence 31153 dbid -482780276 branch 754336076
Tue Mar 20 00:28:13 2012
Archived Log entry 101 added for thread 1 sequence 31152 ID 0xe339e88c dest 1:
Tue Mar 20 00:28:16 2012
Media Recovery Waiting for thread 1 sequence 31153 (in transit)
Recovery of Online Redo Log: Thread 1 Group 11 Seq 31153 Reading mem 0
  Mem# 0: /data2/oracle/oradata/track1/standbyredo11.log
Tue Mar 20 00:32:40 2012
RFS[3]: Selected log 12 for thread 1 sequence 31154 dbid -482780276 branch 754336076
Tue Mar 20 00:32:42 2012
Media Recovery Waiting for thread 1 sequence 31154 (in transit)
Recovery of Online Redo Log: Thread 1 Group 12 Seq 31154 Reading mem 0


NAME        VALUE     UNIT    TIME_COMPUTED   DATUM_TIME
------------------------------ -------------------- ------------------------------ ------------------------------ ------------------------------
transport lag        +00 00:00:00     day(2) to second(0) interval   03/20/2012 00:37:51   03/20/2012 00:37:51
apply lag        +00 00:00:18     day(2) to second(0) interval   03/20/2012 00:37:51   03/20/2012 00:37:51
apply finish time        +00 00:00:00.078     day(2) to second(3) interval   03/20/2012 00:37:51
estimated startup time        17     second    03/20/2012 00:37:51


SQL> select current_scn from v$database;

      CURRENT_SCN
-----------------
      36050243871

SQL> select * from  v$archive_gap;

no rows selected

在这种比较极端的情况下选择这种恢复的方法,如果重做备库 将花费大量的时间与空间,使用增量备份,极大的缩短了恢复的时间

2 Responses to “Recover physical standby database after loss of archive log”

  1. admin Says:

    set newname command made easier in 11gR2

    From 11gR2 oracle introduced new options for “SET NEWNAME” command.

    1.SET NEWNAME FOR DATAFILE and SET NEWNAME FOR TEMPFILE
    2.SET NEWNAME FOR TABLESPACE
    3.SET NEWNAME FOR DATABASE

    The following variables are introduced for SET NEWNAME from 11gR2 :-

    %b The file name remains same as the original. For example, if a datafile is named D:\oracle\oradata\matrix\test.dbf, then %b results in test.dbf.
    %f Specifies the absolute file number of the datafile for which the new name is generated.
    %I Specifies the DBID.
    %N Specifies the tablespace name.
    %U Specifies the following format: data-D-%d_id-%I_TS-%N_FNO-%f.

    Time to test

    In my test, i am creating a duplicate database ORCL, from the rman backup of MATRIX database. As i am doing it on Windows box, i do hit ORA-600 [KSMFPG5], [0xAEC0000], which is a bug, and also mentioned in one of my previous blog.

    1. taking backup of matrix database –

    RMAN> run{
    2> backup database format ‘D:\oracle\backup\matrix\%d_%s_%p';
    3> backup archivelog all format ‘D:\oracle\backup\matrix\arc_%d_%s_%p';
    4> }

    Starting backup at 11-JAN-12
    using channel ORA_DISK_1
    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=D:\ORACLE\ORADATA\MATRIX\SYSTEM01.DBF
    …………..
    channel ORA_DISK_1: finished piece 1 at 11-JAN-12
    piece handle=D:\ORACLE\BACKUP\MATRIX\ARC_MATRIX_10_1 tag=TAG20120111T133016 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
    Finished backup at 11-JAN-12
    The database as well as the archivelog backupset exist in D:\ORACLE\BACKUP\MATRIX\

    2. Duplicating db MATRIX to ORCL. Before executing the command, created ORCL pfile , passwordfile and windows service using oradim utility.

    D:\scripts>set oracle_sid=ORCL

    D:\scripts>sqlplus / as sysdba

    SQL*Plus: Release 11.2.0.2.0 Production on Wed Jan 11 13:32:14 2012

    Copyright (c) 1982, 2010, Oracle. All rights reserved.

    Connected to an idle instance.

    SQL> startup nomount
    ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
    ORACLE instance started.

    Total System Global Area 150667264 bytes
    Fixed Size 1382112 bytes
    Variable Size 92277024 bytes
    Database Buffers 50331648 bytes
    Redo Buffers 6676480 bytes
    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    D:\scripts>
    D:\scripts>
    D:\scripts>rman auxiliary /

    Recovery Manager: Release 11.2.0.2.0 – Production on Wed Jan 11 13:33:02 2012

    Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

    connected to auxiliary database: ORCL (not mounted)

    RMAN> run {
    2>
    3> SET NEWNAME FOR DATABASE TO ‘D:\oracle\oradata\orcl\%b';
    4> SET NEWNAME FOR TEMPFILE 1 TO ‘D:\oracle\oradata\orcl\temp01.dbf’ ;
    5>
    6> DUPLICATE DATABASE ‘MATRIX’ DBID 2312606933
    7> TO ORCL
    8> BACKUP LOCATION ‘D:\oracle\backup\matrix’
    9> LOGFILE
    10> GROUP 1 (‘D:\oracle\oradata\orcl\redo01a.log’,
    11> ‘D:\oracle\oradata\orcl\redo01b.log’) SIZE 50M REUSE,
    12> GROUP 2 (‘D:\oracle\oradata\orcl\redo02a.log’,
    13> ‘D:\oracle\oradata\orcl\redo02b.log’) SIZE 50M REUSE,
    14> GROUP 3 (‘D:\oracle\oradata\orcl\redo03a.log’,
    15> ‘D:\oracle\oradata\orcl\redo03b.log’) SIZE 50M REUSE;
    16> }

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    Starting Duplicate Db at 11-JAN-12

    contents of Memory Script:
    {
    sql clone “alter system set db_name =
    ”MATRIX” comment=
    ”Modified by RMAN duplicate” scope=spfile”;
    sql clone “alter system set db_unique_name =
    ”ORCL” comment=
    ”Modified by RMAN duplicate” scope=spfile”;
    shutdown clone immediate;
    startup clone force nomount
    restore clone primary controlfile from ‘D:\ORACLE\BACKUP\matrix\MATRIX_9_1′;
    alter clone database mount;
    }
    executing Memory Script

    sql statement: alter system set db_name = ”MATRIX” comment= ”Modified by RMAN duplicate” scope=spfile

    sql statement: alter system set db_unique_name = ”ORCL” comment= ”Modified by RMAN duplicate” scope=spfile

    Oracle instance shut down

    Oracle instance started

    Total System Global Area 150667264 bytes

    Fixed Size 1382112 bytes
    Variable Size 92277024 bytes
    Database Buffers 50331648 bytes
    Redo Buffers 6676480 bytes

    Starting restore at 11-JAN-12
    allocated channel: ORA_AUX_DISK_1
    channel ORA_AUX_DISK_1: SID=130 device type=DISK

    channel ORA_AUX_DISK_1: restoring control file
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
    output file name=D:\ORACLE\ORADATA\ORCL\CONTROL01.CTL
    output file name=D:\ORACLE\ORADATA\ORCL\CONTROL02.CTL
    Finished restore at 11-JAN-12

    database mounted
    released channel: ORA_AUX_DISK_1
    allocated channel: ORA_AUX_DISK_1
    channel ORA_AUX_DISK_1: SID=130 device type=DISK

    contents of Memory Script:
    {
    set until scn 859591;
    set newname for datafile 1 to
    “D:\oracle\oradata\orcl\SYSTEM01.DBF”;
    set newname for datafile 2 to
    “D:\oracle\oradata\orcl\SYSAUX01.DBF”;
    set newname for datafile 3 to
    “D:\oracle\oradata\orcl\UNDOTBS01.DBF”;
    set newname for datafile 4 to
    “D:\oracle\oradata\orcl\USERS01.DBF”;
    set newname for datafile 5 to
    “D:\oracle\oradata\orcl\EXAMPLE01.DBF”;
    restore
    clone database
    ;
    }
    executing Memory Script

    executing command: SET until clause

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    Starting restore at 11-JAN-12
    using channel ORA_AUX_DISK_1

    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00001 to D:\oracle\oradata\orcl\SYSTEM01.DBF
    channel ORA_AUX_DISK_1: restoring datafile 00002 to D:\oracle\oradata\orcl\SYSAUX01.DBF
    channel ORA_AUX_DISK_1: restoring datafile 00003 to D:\oracle\oradata\orcl\UNDOTBS01.DBF
    channel ORA_AUX_DISK_1: restoring datafile 00004 to D:\oracle\oradata\orcl\USERS01.DBF
    channel ORA_AUX_DISK_1: restoring datafile 00005 to D:\oracle\oradata\orcl\EXAMPLE01.DBF
    channel ORA_AUX_DISK_1: reading from backup piece D:\ORACLE\BACKUP\MATRIX\MATRIX_8_1
    channel ORA_AUX_DISK_1: piece handle=D:\ORACLE\BACKUP\MATRIX\MATRIX_8_1 tag=TAG20120111T132903
    channel ORA_AUX_DISK_1: restored backup piece 1
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05
    Finished restore at 11-JAN-12

    contents of Memory Script:
    {
    switch clone datafile all;
    }
    executing Memory Script

    datafile 1 switched to datafile copy
    input datafile copy RECID=6 STAMP=772292110 file name=D:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
    datafile 2 switched to datafile copy
    input datafile copy RECID=7 STAMP=772292110 file name=D:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF
    datafile 3 switched to datafile copy
    input datafile copy RECID=8 STAMP=772292110 file name=D:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
    datafile 4 switched to datafile copy
    input datafile copy RECID=9 STAMP=772292110 file name=D:\ORACLE\ORADATA\ORCL\USERS01.DBF
    datafile 5 switched to datafile copy
    input datafile copy RECID=10 STAMP=772292111 file name=D:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF

    contents of Memory Script:
    {
    set until scn 859591;
    recover
    clone database
    delete archivelog
    ;
    }
    executing Memory Script

    executing command: SET until clause

    Starting recover at 11-JAN-12
    using channel ORA_AUX_DISK_1

    starting media recovery

    channel ORA_AUX_DISK_1: starting archived log restore to default destination
    channel ORA_AUX_DISK_1: restoring archived log
    archived log thread=1 sequence=10
    channel ORA_AUX_DISK_1: reading from backup piece D:\ORACLE\BACKUP\MATRIX\ARC_MATRIX_10_1
    channel ORA_AUX_DISK_1: piece handle=D:\ORACLE\BACKUP\MATRIX\ARC_MATRIX_10_1 tag=TAG20120111T133016
    channel ORA_AUX_DISK_1: restored backup piece 1
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
    archived log file name=D:\ORACLE\ARCHIVELOG\ORCL\ORCL_0000000010_0772282200_0001.ARC thread=1 sequence=10
    channel clone_default: deleting archived log(s)
    archived log file name=D:\ORACLE\ARCHIVELOG\ORCL\ORCL_0000000010_0772282200_0001.ARC RECID=1 STAMP=772292118
    media recovery complete, elapsed time: 00:00:03
    Finished recover at 11-JAN-12
    Oracle instance started

    Total System Global Area 150667264 bytes

    Fixed Size 1382112 bytes
    Variable Size 92277024 bytes
    Database Buffers 50331648 bytes
    Redo Buffers 6676480 bytes

    contents of Memory Script:
    {
    sql clone “alter system set db_name =
    ”ORCL” comment=
    ”Reset to original value by RMAN” scope=spfile”;
    sql clone “alter system reset db_unique_name scope=spfile”;
    shutdown clone immediate;
    }
    executing Memory Script

    sql statement: alter system set db_name = ”ORCL” comment= ”Reset to original value by RMAN” scope=spfile

    sql statement: alter system reset db_unique_name scope=spfile

    Oracle instance shut down
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of Duplicate Db command at 01/11/2012 13:36:16
    RMAN-05501: aborting duplication of target database
    RMAN-04014: startup failed: ORA-00600: internal error code, arguments: [17099], [], [], [], [], [], [], [], [], [], [], []
    ORA-00600: internal error code, arguments: [KSMFPG5], [0xAEC0000], [], [], [], [], [], [], [], [], [], []

    RMAN> exit
    For the ORA-600, recreated the controlfile, opened the database using RESETLOGS option and added temp file.

    In earlier versions of oracle we had to mention SET NEWNAME command to rename the duplicate datafiles while restoring/duplicating the database, where we mentioned it for each and every datafile.

    # set new filenames for the datafiles
    SET NEWNAME FOR DATAFILE 1 TO ‘/dup/oracle/oradata/trgt/system01.dbf’;
    SET NEWNAME FOR DATAFILE 2 TO ‘/dup/oracle/oradata/trgt/undotbs01.dbf’;

    From 11gR2 simply using SET NEWNAME FOR DATABASE has made things easier.

  2. admin Says:

    这是参考的一篇国外的blog的文档
    recover physical standby database after loss of archive log
    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

    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.

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>