In the post I'll show you to deal with the issue when the archive log physical removed from the Primary Database server before to apply it over the Standby Database.
Here:
Database_Type Database_Unique_Name File_Management
Primary DB1 NON-ASM
Standby SDB1 ASM
first I'll create the scenario where :
1. I'll disable the second archive destination state over the Primary Database which is pointing for Standby Database service.
2. Once it is disable , I'll create drop and create a table under user 'SCOTT'.
3. The generated archive logs will move to the another directory.
4. Then will start the secondary archive log destination so it will start pointing to the standby database server.
5. The Standby recovery will identify the missing archive logs and will throw the message into the alert log file for missing archive logs.
After that I'll start the recovery of the missing archive logs on the standby database.
Before starting lets have a look on the Primary and Standby Database status.
At Primary
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DB1 READ WRITE PRIMARY
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/fast_recovery_area/db1/archivelog/
Oldest online log sequence 108
Next log sequence to archive 110
Current log sequence 110
SQL> show parameter log_archive_dest_state_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string ENABLE
At Standby Database.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DB1 READ ONLY WITH APPLY PHYSICAL STANDBY
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +DATA/
Oldest online log sequence 108
Next log sequence to archive 0
Current log sequence 110
At Primary Site.
Lets Disable the secondary archive log destination and create some table under 'Scott' schema.
And after that move the generated archives into another directory.
SQL> alter system set log_archive_dest_state_2='DEFER' scope=both;
System altered.
SQL> show parameter log_archive_dest_state_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string DEFER
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
TEST TABLE
SQL> drop table test purge;
Table dropped.
SQL> create table test
2 as
3 select * from emp;
Table created.
SQL> select count(*) from test;
COUNT(*)
----------
14
SQL> commit;
Commit complete.
[oracle@node1 archivelog]$ ls -l
total 376384
-rw-r----- 1 oracle oinstall 868864 Jun 26 18:14 1_100_850229880.dbf
-rw-r----- 1 oracle oinstall 421376 Jun 26 18:27 1_101_850229880.dbf
-rw-r----- 1 oracle oinstall 42317824 Jun 27 01:30 1_102_850229880.dbf
-rw-r----- 1 oracle oinstall 33522688 Jun 27 11:26 1_103_850229880.dbf
-rw-r----- 1 oracle oinstall 6123520 Jun 27 13:25 1_104_850229880.dbf
-rw-r----- 1 oracle oinstall 86016 Jun 27 13:28 1_105_850229880.dbf
-rw-r----- 1 oracle oinstall 1191424 Jun 27 13:30 1_106_850229880.dbf
-rw-r----- 1 oracle oinstall 102912 Jun 27 13:33 1_107_850229880.dbf
-rw-r----- 1 oracle oinstall 932352 Jun 27 14:00 1_108_850229880.dbf
-rw-r----- 1 oracle oinstall 2560 Jun 27 14:01 1_109_850229880.dbf
-rw-r----- 1 oracle oinstall 515584 Jun 27 14:14 1_110_850229880.dbf
-rw-r----- 1 oracle oinstall 1024 Jun 27 14:14 1_111_850229880.dbf
-rw-r----- 1 oracle oinstall 3584 Jun 27 14:15 1_112_850229880.dbf
....
[oracle@node1 archivelog]$ mkdir -p bkp/
[oracle@node1 archivelog]$ mv 1_1* bkp/
Enable the secondary archive destination state.
SQL> alter system set log_archive_dest_state_2='ENABLE' scope=both;
System altered.
SQL> show parameter log_archive_dest_state_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string ENABLE
At Secondary Site.
SQL> select * from v$archive_gap;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 110 112
SQL> select VALUE from v$dataguard_stats
2 where name='transport lag';
VALUE
----------------------------------------------------------------
+00 00:31:22
Excerpts from alert log
*****************************************************************************************
ARC2 started with pid=30, OS id=12037
Media Recovery Waiting for thread 1 sequence 110
Fetching gap sequence in thread 1, gap sequence 110-112
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the heartbeat ARCH
ARC1: Becoming the active heartbeat ARCH
Fri Jun 27 14:26:24 2014
ARC3 started with pid=31, OS id=12039
Fri Jun 27 14:26:25 2014
Using STANDBY_ARCHIVE_DEST parameter default value as +DATA
************************************************************************************
Check the current scn number of the standby database.
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1327971
SQL> conn scott/tiger
Connected.
SQL> select created from user_objects where object_name ='TEST';
CREATED
---------
26-JUN-14
The Test Table creation date is showing the earlier time which we had deleted on the Production database but not showing the current one.
Take the incremental backup from the database current scn over the Primary Database.
At Primary Site.
RMAN> backup incremental from scn 1327971 database format '/u01/bkp/inc_stby_%U.bkp';
Starting backup at 27-JUN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=52 device type=DISK
backup will be obsolete on date 04-JUL-14
archived logs will not be kept or backed up
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/oradata/db1/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/db1/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/db1/test01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/db1/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/db1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 27-JUN-14
channel ORA_DISK_1: finished piece 1 at 27-JUN-14
piece handle=/u01/bkp/inc_stby_33pbt6b9_1_1.bkp tag=TAG20140627T143832 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
using channel ORA_DISK_1
backup will be obsolete on date 04-JUL-14
archived logs will not be kept or backed up
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 27-JUN-14
channel ORA_DISK_1: finished piece 1 at 27-JUN-14
piece handle=/u01/bkp/inc_stby_34pbt6cc_1_1.bkp tag=TAG20140627T143832 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 27-JUN-14
RMAN> exit
[oracle@node1 bkp]$ ls -l
total 13444
-rw-r----- 1 oracle oinstall 3588096 Jun 27 14:39 inc_stby_33pbt6b9_1_1.bkp
-rw-r----- 1 oracle oinstall 10158080 Jun 27 14:39 inc_stby_34pbt6cc_1_1.bkp
Copy the backup to the Standby Host.
[oracle@node1 bkp]$ scp orapwdb1 node2:/u01/app/oracle/product/11.2.0/db_1/dbs/
[oracle@node1 bkp]$ scp -r * node2:/u01/bkp/
oracle@node2's password:
inc_stby_33pbt6b9_1_1.bkp 100% 3504KB 3.4MB/s 00:01
inc_stby_34pbt6cc_1_1.bkp 100% 9920KB 9.7MB/s 00:00
At Standby Site.
Shut down the Standby Database, catalog the incremental backup and perform the recovery.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1033670656 bytes
Fixed Size 1349476 bytes
Variable Size 595593372 bytes
Database Buffers 432013312 bytes
Redo Buffers 4714496 bytes
Database mounted.
[oracle@node2 ~]$ rman target /
connected to target database: DB1 (DBID=1444093493, not open)
RMAN> catalog start with '/u01/bkp';
using target database control file instead of recovery catalog
searching for all files that match the pattern /u01/bkp
List of Files Unknown to the Database
=====================================
File Name: /u01/bkp/inc_stby_2rpbqsba_1_1.bkp
File Name: /u01/bkp/inc_stby_2qpbqsa7_1_1.bkp
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/bkp/inc_stby_2rpbqsba_1_1.bkp
File Name: /u01/bkp/inc_stby_2qpbqsa7_1_1.bkp
RMAN> report schema;
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name SDB1
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 710 SYSTEM *** +DATA/sdb1/system01.dbf
2 570 SYSAUX *** +DATA/sdb1/sysaux01.dbf
3 30 UNDOTBS1 *** +DATA/sdb1/undotbs01.dbf
4 5 USERS *** +DATA/sdb1/users01.dbf
5 500 TEST *** +DATA/sdb1/test01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 +DATA/sdb1/temp01.dbf
RMAN> recover database noredo;
Starting recover at 27-JUN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 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: +DATA/sdb1/system01.dbf
destination for restore of datafile 00002: +DATA/sdb1/sysaux01.dbf
destination for restore of datafile 00003: +DATA/sdb1/undotbs01.dbf
destination for restore of datafile 00004: +DATA/sdb1/users01.dbf
destination for restore of datafile 00005: +DATA/sdb1/test01.dbf
channel ORA_DISK_1: reading from backup piece /u01/bkp/inc_stby_33pbt6b9_1_1.bkp
channel ORA_DISK_1: piece handle=/u01/bkp/inc_stby_33pbt6b9_1_1.bkp tag=TAG20140627T143832
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished recover at 27-JUN-14
RMAN>
Create the Standby Controlfile from the Production server and ship that on the standby site.
At Primary Site.
RMAN> backup current controlfile for standby format '/u01/bkp/stby_control.ctl';
Starting backup at 26-JUN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 device type=DISK
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 26-JUN-14
channel ORA_DISK_1: finished piece 1 at 26-JUN-14
piece handle=/u01/bkp/stby_control.ctl tag=TAG20140626T175537 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 26-JUN-14
[oracle@node1 bkp]$ scp stby_control.ctl node2:/u01/bkp/
oracle@node2's password:
stby_control.ctl 100% 9856KB 9.6MB/s 00:00
[oracle@node1 bkp]$
At Standby Site.
Bounce the Standby Database in nomount mode , restore the controlfile from the controlfile backup.
SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1033670656 bytes
Fixed Size 1349476 bytes
Variable Size 595593372 bytes
Database Buffers 432013312 bytes
Redo Buffers 4714496 bytes
[oracle@node2 bkp]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jun 27 14:51:29 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DB1 (not mounted)
RMAN> restore standby controlfile from '/u01/bkp/stby_control.ctl';
Starting restore at 27-JUN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+DATA/sdb1/controlfile/current.275.851347679
Finished restore at 27-JUN-14
RMAN>
SQL> alter database mount;
Database altered.
connected to target database: DB1 (DBID=1444093493, not open)
RMAN> report schema;
Starting implicit crosscheck backup at 27-JUN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=31 device type=DISK
Crosschecked 19 objects
Finished implicit crosscheck backup at 27-JUN-14
Starting implicit crosscheck copy at 27-JUN-14
using channel ORA_DISK_1
Crosschecked 4 objects
Finished implicit crosscheck copy at 27-JUN-14
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +data/SDB1/ARCHIVELOG/2014_06_27/thread_1_seq_107.284.851350363
File Name: +data/SDB1/ARCHIVELOG/2014_06_27/thread_1_seq_108.263.851350365
File Name: +data/SDB1/ARCHIVELOG/2014_06_27/thread_1_seq_109.259.851350365
File Name: +data/SDB1/ARCHIVELOG/2014_06_27/thread_1_seq_113.262.851350651
File Name: +data/SDB1/ARCHIVELOG/2014_06_27/thread_1_seq_114.277.851351187
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name SDB1
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 710 SYSTEM *** +DATA/sdb1/system01.dbf
2 570 SYSAUX *** +DATA/sdb1/sysaux01.dbf
3 30 UNDOTBS1 *** +DATA/sdb1/undotbs01.dbf
4 5 USERS *** +DATA/sdb1/users01.dbf
5 500 TEST *** +DATA/sdb1/test01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 +DATA/sdb1/temp01.dbf
Start the Recovery and check the status of the table created under SCOTT schema and the Database Status.
At Standby Site.
SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DB1 READ ONLY WITH APPLY PHYSICAL STANDBY
SQL> conn scott/tiger
Connected.
SQL> select created from user_objects where object_name ='TEST';
CREATED
--------
27-JUN-14
SQL> conn /as sysdba
Connected.
SQL> select current_scn from v$database;
CURRENT_SCN
----------
1331998
SQL> select VALUE from v$dataguard_stats
2 where name='transport lag';
VALUE
----------------------------------------------------------------
+00 00:00:00
References.
http://saruamit4.wordpress.com/2014/05/03/recovering-standby-database-using-scn-based-backup/#more-441
Here:
Database_Type Database_Unique_Name File_Management
Primary DB1 NON-ASM
Standby SDB1 ASM
first I'll create the scenario where :
1. I'll disable the second archive destination state over the Primary Database which is pointing for Standby Database service.
2. Once it is disable , I'll create drop and create a table under user 'SCOTT'.
3. The generated archive logs will move to the another directory.
4. Then will start the secondary archive log destination so it will start pointing to the standby database server.
5. The Standby recovery will identify the missing archive logs and will throw the message into the alert log file for missing archive logs.
After that I'll start the recovery of the missing archive logs on the standby database.
Before starting lets have a look on the Primary and Standby Database status.
At Primary
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DB1 READ WRITE PRIMARY
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/fast_recovery_area/db1/archivelog/
Oldest online log sequence 108
Next log sequence to archive 110
Current log sequence 110
SQL> show parameter log_archive_dest_state_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string ENABLE
At Standby Database.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DB1 READ ONLY WITH APPLY PHYSICAL STANDBY
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +DATA/
Oldest online log sequence 108
Next log sequence to archive 0
Current log sequence 110
At Primary Site.
Lets Disable the secondary archive log destination and create some table under 'Scott' schema.
And after that move the generated archives into another directory.
SQL> alter system set log_archive_dest_state_2='DEFER' scope=both;
System altered.
SQL> show parameter log_archive_dest_state_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string DEFER
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
TEST TABLE
SQL> drop table test purge;
Table dropped.
SQL> create table test
2 as
3 select * from emp;
Table created.
SQL> select count(*) from test;
COUNT(*)
----------
14
SQL> commit;
Commit complete.
[oracle@node1 archivelog]$ ls -l
total 376384
-rw-r----- 1 oracle oinstall 868864 Jun 26 18:14 1_100_850229880.dbf
-rw-r----- 1 oracle oinstall 421376 Jun 26 18:27 1_101_850229880.dbf
-rw-r----- 1 oracle oinstall 42317824 Jun 27 01:30 1_102_850229880.dbf
-rw-r----- 1 oracle oinstall 33522688 Jun 27 11:26 1_103_850229880.dbf
-rw-r----- 1 oracle oinstall 6123520 Jun 27 13:25 1_104_850229880.dbf
-rw-r----- 1 oracle oinstall 86016 Jun 27 13:28 1_105_850229880.dbf
-rw-r----- 1 oracle oinstall 1191424 Jun 27 13:30 1_106_850229880.dbf
-rw-r----- 1 oracle oinstall 102912 Jun 27 13:33 1_107_850229880.dbf
-rw-r----- 1 oracle oinstall 932352 Jun 27 14:00 1_108_850229880.dbf
-rw-r----- 1 oracle oinstall 2560 Jun 27 14:01 1_109_850229880.dbf
-rw-r----- 1 oracle oinstall 515584 Jun 27 14:14 1_110_850229880.dbf
-rw-r----- 1 oracle oinstall 1024 Jun 27 14:14 1_111_850229880.dbf
-rw-r----- 1 oracle oinstall 3584 Jun 27 14:15 1_112_850229880.dbf
....
[oracle@node1 archivelog]$ mkdir -p bkp/
[oracle@node1 archivelog]$ mv 1_1* bkp/
Enable the secondary archive destination state.
SQL> alter system set log_archive_dest_state_2='ENABLE' scope=both;
System altered.
SQL> show parameter log_archive_dest_state_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string ENABLE
At Secondary Site.
SQL> select * from v$archive_gap;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 110 112
SQL> select VALUE from v$dataguard_stats
2 where name='transport lag';
VALUE
----------------------------------------------------------------
+00 00:31:22
Excerpts from alert log
*****************************************************************************************
ARC2 started with pid=30, OS id=12037
Media Recovery Waiting for thread 1 sequence 110
Fetching gap sequence in thread 1, gap sequence 110-112
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the heartbeat ARCH
ARC1: Becoming the active heartbeat ARCH
Fri Jun 27 14:26:24 2014
ARC3 started with pid=31, OS id=12039
Fri Jun 27 14:26:25 2014
Using STANDBY_ARCHIVE_DEST parameter default value as +DATA
************************************************************************************
Check the current scn number of the standby database.
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1327971
SQL> conn scott/tiger
Connected.
SQL> select created from user_objects where object_name ='TEST';
CREATED
---------
26-JUN-14
The Test Table creation date is showing the earlier time which we had deleted on the Production database but not showing the current one.
Take the incremental backup from the database current scn over the Primary Database.
At Primary Site.
RMAN> backup incremental from scn 1327971 database format '/u01/bkp/inc_stby_%U.bkp';
Starting backup at 27-JUN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=52 device type=DISK
backup will be obsolete on date 04-JUL-14
archived logs will not be kept or backed up
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/oradata/db1/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/db1/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/db1/test01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/db1/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/db1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 27-JUN-14
channel ORA_DISK_1: finished piece 1 at 27-JUN-14
piece handle=/u01/bkp/inc_stby_33pbt6b9_1_1.bkp tag=TAG20140627T143832 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
using channel ORA_DISK_1
backup will be obsolete on date 04-JUL-14
archived logs will not be kept or backed up
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 27-JUN-14
channel ORA_DISK_1: finished piece 1 at 27-JUN-14
piece handle=/u01/bkp/inc_stby_34pbt6cc_1_1.bkp tag=TAG20140627T143832 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 27-JUN-14
RMAN> exit
[oracle@node1 bkp]$ ls -l
total 13444
-rw-r----- 1 oracle oinstall 3588096 Jun 27 14:39 inc_stby_33pbt6b9_1_1.bkp
-rw-r----- 1 oracle oinstall 10158080 Jun 27 14:39 inc_stby_34pbt6cc_1_1.bkp
Copy the backup to the Standby Host.
[oracle@node1 bkp]$ scp orapwdb1 node2:/u01/app/oracle/product/11.2.0/db_1/dbs/
[oracle@node1 bkp]$ scp -r * node2:/u01/bkp/
oracle@node2's password:
inc_stby_33pbt6b9_1_1.bkp 100% 3504KB 3.4MB/s 00:01
inc_stby_34pbt6cc_1_1.bkp 100% 9920KB 9.7MB/s 00:00
At Standby Site.
Shut down the Standby Database, catalog the incremental backup and perform the recovery.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1033670656 bytes
Fixed Size 1349476 bytes
Variable Size 595593372 bytes
Database Buffers 432013312 bytes
Redo Buffers 4714496 bytes
Database mounted.
[oracle@node2 ~]$ rman target /
connected to target database: DB1 (DBID=1444093493, not open)
RMAN> catalog start with '/u01/bkp';
using target database control file instead of recovery catalog
searching for all files that match the pattern /u01/bkp
List of Files Unknown to the Database
=====================================
File Name: /u01/bkp/inc_stby_2rpbqsba_1_1.bkp
File Name: /u01/bkp/inc_stby_2qpbqsa7_1_1.bkp
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/bkp/inc_stby_2rpbqsba_1_1.bkp
File Name: /u01/bkp/inc_stby_2qpbqsa7_1_1.bkp
RMAN> report schema;
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name SDB1
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 710 SYSTEM *** +DATA/sdb1/system01.dbf
2 570 SYSAUX *** +DATA/sdb1/sysaux01.dbf
3 30 UNDOTBS1 *** +DATA/sdb1/undotbs01.dbf
4 5 USERS *** +DATA/sdb1/users01.dbf
5 500 TEST *** +DATA/sdb1/test01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 +DATA/sdb1/temp01.dbf
RMAN> recover database noredo;
Starting recover at 27-JUN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 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: +DATA/sdb1/system01.dbf
destination for restore of datafile 00002: +DATA/sdb1/sysaux01.dbf
destination for restore of datafile 00003: +DATA/sdb1/undotbs01.dbf
destination for restore of datafile 00004: +DATA/sdb1/users01.dbf
destination for restore of datafile 00005: +DATA/sdb1/test01.dbf
channel ORA_DISK_1: reading from backup piece /u01/bkp/inc_stby_33pbt6b9_1_1.bkp
channel ORA_DISK_1: piece handle=/u01/bkp/inc_stby_33pbt6b9_1_1.bkp tag=TAG20140627T143832
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished recover at 27-JUN-14
RMAN>
Create the Standby Controlfile from the Production server and ship that on the standby site.
At Primary Site.
RMAN> backup current controlfile for standby format '/u01/bkp/stby_control.ctl';
Starting backup at 26-JUN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 device type=DISK
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 26-JUN-14
channel ORA_DISK_1: finished piece 1 at 26-JUN-14
piece handle=/u01/bkp/stby_control.ctl tag=TAG20140626T175537 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 26-JUN-14
[oracle@node1 bkp]$ scp stby_control.ctl node2:/u01/bkp/
oracle@node2's password:
stby_control.ctl 100% 9856KB 9.6MB/s 00:00
[oracle@node1 bkp]$
At Standby Site.
Bounce the Standby Database in nomount mode , restore the controlfile from the controlfile backup.
SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1033670656 bytes
Fixed Size 1349476 bytes
Variable Size 595593372 bytes
Database Buffers 432013312 bytes
Redo Buffers 4714496 bytes
[oracle@node2 bkp]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jun 27 14:51:29 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DB1 (not mounted)
RMAN> restore standby controlfile from '/u01/bkp/stby_control.ctl';
Starting restore at 27-JUN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+DATA/sdb1/controlfile/current.275.851347679
Finished restore at 27-JUN-14
RMAN>
SQL> alter database mount;
Database altered.
connected to target database: DB1 (DBID=1444093493, not open)
RMAN> report schema;
Starting implicit crosscheck backup at 27-JUN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=31 device type=DISK
Crosschecked 19 objects
Finished implicit crosscheck backup at 27-JUN-14
Starting implicit crosscheck copy at 27-JUN-14
using channel ORA_DISK_1
Crosschecked 4 objects
Finished implicit crosscheck copy at 27-JUN-14
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +data/SDB1/ARCHIVELOG/2014_06_27/thread_1_seq_107.284.851350363
File Name: +data/SDB1/ARCHIVELOG/2014_06_27/thread_1_seq_108.263.851350365
File Name: +data/SDB1/ARCHIVELOG/2014_06_27/thread_1_seq_109.259.851350365
File Name: +data/SDB1/ARCHIVELOG/2014_06_27/thread_1_seq_113.262.851350651
File Name: +data/SDB1/ARCHIVELOG/2014_06_27/thread_1_seq_114.277.851351187
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name SDB1
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 710 SYSTEM *** +DATA/sdb1/system01.dbf
2 570 SYSAUX *** +DATA/sdb1/sysaux01.dbf
3 30 UNDOTBS1 *** +DATA/sdb1/undotbs01.dbf
4 5 USERS *** +DATA/sdb1/users01.dbf
5 500 TEST *** +DATA/sdb1/test01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 +DATA/sdb1/temp01.dbf
Start the Recovery and check the status of the table created under SCOTT schema and the Database Status.
At Standby Site.
SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DB1 READ ONLY WITH APPLY PHYSICAL STANDBY
SQL> conn scott/tiger
Connected.
SQL> select created from user_objects where object_name ='TEST';
CREATED
--------
27-JUN-14
SQL> conn /as sysdba
Connected.
SQL> select current_scn from v$database;
CURRENT_SCN
----------
1331998
SQL> select VALUE from v$dataguard_stats
2 where name='transport lag';
VALUE
----------------------------------------------------------------
+00 00:00:00
References.
http://saruamit4.wordpress.com/2014/05/03/recovering-standby-database-using-scn-based-backup/#more-441
No comments:
Post a Comment