今天报表数据库的备库出现了问题，由于监控脚本出现了问题，主机空间耗尽 而没有及时发出邮件，导致归档无法进行，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 {
2> allocate channel c1 device type disk;
3> allocate channel c2 device type disk;
4> allocate channel c3 device type disk;
5> allocate channel c4 device type disk;
6> BACKUP INCREMENTAL FROM SCN 16895677901 DATABASE FORMAT ‘/data/track_standby_%U’ tag ‘track_standby’;
7> release channel c1;
8> release channel c2;
9> release 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 affiliates. All 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
在这种比较极端的情况下选择这种恢复的方法，如果重做备库 将花费大量的时间与空间，使用增量备份，极大的缩短了恢复的时间
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.
这是参考的一篇国外的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.