Physical Standby database on the Same host with Primary having ASM and Standby having normal
==================================================================
filesystem.
=======
Primary Hostname : rhel.manzoor.com
Primary db name : test11
Seconday db name : test12
1. Enable the archive log mode in primary database.
SQL> shut immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
2. Enable the force logging in the db.
SQL> alter database force logging.
3. Add the Parameters for Standby configuration.
SQL> alter system set log_archive_config = 'DG_CONFIG=(test11,test12)' scope=both;
System altered.
SQL> alter system set log_archive_dest_1 = 'location=+DATA/archive_log valid_for=(all_logfiles,all_roles) db_unique_name=test11' scope=both;
System altered.
SQL> alter system set log_archive_dest_2 = 'service=test12 lgwr async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=test12' scope=both;
System altered.
SQL> alter system set log_archive_dest_state_1 = 'enable' scope=both;
System altered.
SQL> alter system set log_archive_dest_state_2 = 'defer' scope=both;
System altered.
## Below parameters will be used when the role has been changed.
SQL> alter system set fal_client = 'test11' scope=both;
System altered.
SQL> alter system set fal_server = 'test12' scope=both;
System altered.
SQL> alter system set db_file_name_convert='/optware/oracle/orafiles/test12','+DATA/test11' scope=spfile;
System altered.
SQL> alter system set log_file_name_convert='/optware/oracle/orafiles/test12','+DATA/test11' scope=spfile;
System altered.
SQL> alter system set standby_file_management = auto scope=both;
System altered.
4. Create an pfile from the spfile on the primary database.
> create pfile='/u01/app/inittest11.ora' from spfile;
> ! cat /u01/app/inittest11.ora
*.audit_file_dest='/optware/oracle/admin/test11/adump'
*.audit_trail='DB'
*.compatible='11.2.0.0.0'
*.control_files='+DATA/test11/control01.ctl','+DATA/test11/control02.ctl'#Restore Controlfile
*.core_dump_dest='/optware/oracle/diag/rdbms/test11/test11/cdump'
*.db_block_size=8192
*.db_cache_size=52M
*.db_domain=''
*.db_file_name_convert='/optware/oracle/orafiles/test12','+DATA/test11'
*.db_name='test11'
*.db_recovery_file_dest='/optware/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=3852M
*.diagnostic_dest='/optware/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=test11XDB)'
*.fal_client='test11'
*.fal_server='test12'
*.local_listener='lsnr_test11'
*.log_archive_config='DG_CONFIG=(test11,test12)'
*.log_archive_dest_1='location=+DATA/archive_log valid_for=(all_logfiles,all_roles) db_unique_name=test11'
*.log_archive_dest_2='service=test12 lgwr async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=test12'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='defer'
*.log_archive_format='%t_%s_%r.dbf'
*.log_buffer=5906432# log buffer update
*.log_file_name_convert='/optware/oracle/orafiles/test12','+DATA/test11'
*.memory_target=400M
*.open_cursors=300
*.optimizer_capture_sql_plan_baselines=FALSE
*.optimizer_dynamic_sampling=2
*.optimizer_mode='ALL_ROWS'
*.plsql_warnings='DISABLE:ALL'# PL/SQL warnings at init.ora
*.processes=150
*.query_rewrite_enabled='TRUE'
*.remote_login_passwordfile='EXCLUSIVE'
*.result_cache_max_size=1M
*.skip_unusable_indexes=TRUE
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
4. Create the password for the primary database and test the password.
$ cd /optware/oracle/product/11.2.0/db_1/dbs
$ orapwd file=orapwtest11 password=sys123 ignorecase=y
[oracle@rhel dbs]$ sqlplus sys/sys123@test11 as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 21 08:34:05 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> exit
5. Create the directories required for standby database.
$ mkdir -p /optware/oracle/admin/test12/adump
$ mkdir -p /optware/oracle/diag/rdbms/test11/test11/cdump
$ mkdir -p /optware/oracle/orafiles/test12/archive_log
5. Create the parameter file for the standby database by modifying the above.
*.audit_file_dest='/optware/oracle/admin/test12/adump'
*.audit_trail='DB'
*.compatible='11.2.0.0.0'
*.control_files='/optware/oracle/orafiles/test12/control01.ctl','/optware/oracle/orafiles/test12/control02.ctl'#Restore Controlfile
*.core_dump_dest='/optware/oracle/diag/rdbms/test11/test11/cdump'
*.db_block_size=8192
*.db_cache_size=52M
*.db_domain=''
*.db_file_name_convert='+DATA/test11','/optware/oracle/orafiles/test12'
*.db_name='test11'
*.db_recovery_file_dest='/optware/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=3852M
*.diagnostic_dest='/optware/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=test12XDB)'
*.fal_client='test12'
*.fal_server='test11'
*.local_listener='lsnr_test12'
*.log_archive_config='DG_CONFIG=(test11,test12)'
*.log_archive_dest_1='location=/optware/oracle/orafiles/test12/archive_log valid_for=(all_logfiles,all_roles) db_unique_name=test12'
*.log_archive_dest_2='service=test11 lgwr async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=test11'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='defer'
*.log_archive_format='%t_%s_%r.dbf'
*.log_buffer=5906432# log buffer update
*.log_file_name_convert='+DATA/test11','/optware/oracle/orafiles/test12'
*.memory_target=400M
*.open_cursors=300
*.optimizer_capture_sql_plan_baselines=FALSE
*.optimizer_dynamic_sampling=2
*.optimizer_mode='ALL_ROWS'
*.plsql_warnings='DISABLE:ALL'# PL/SQL warnings at init.ora
*.processes=150
*.query_rewrite_enabled='TRUE'
*.remote_login_passwordfile='EXCLUSIVE'
*.result_cache_max_size=1M
*.skip_unusable_indexes=TRUE
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
6. Create a listener for the standby database and register the db statically in the listener and start the listener.
Enter the below in listener.ora file.
LSNRTEST12 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rhel.manzoor.com)(PORT = 21000))
)
)
SID_LIST_LSNRTEST12 =
(SID_LIST =
(SID_DESC =
(SID_NAME = test12)
(ORACLE_HOME=/optware/oracle/product/11.2.0/db_1)
)
)
[oracle@rhel admin]$ lsnrctl start lsnrtest12
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 21-DEC-2012 09:21:20
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Starting /optware/grid/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /optware/grid/network/admin/listener.ora
Log messages written to /optware/oracle/diag/tnslsnr/rhel/lsnrtest12/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rhel.manzoor.com)(PORT=21000)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rhel.manzoor.com)(PORT=21000)))
STATUS of the LISTENER
------------------------
Alias lsnrtest12
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 21-DEC-2012 09:21:20
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /optware/grid/network/admin/listener.ora
Listener Log File /optware/oracle/diag/tnslsnr/rhel/lsnrtest12/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rhel.manzoor.com)(PORT=21000)))
Services Summary...
Service "test12" has 1 instance(s).
Instance "test12", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
7. Update the below entry in tnsnames.ora file, which will be verified for the local_listener parameter.
lsnr_test12 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = rhel.manzoor.com) (PORT = 21000)))
(CONNECT_DATA =
(SERVER = DEDICATED) (SERVICE_NAME = test12))
)
Update the Netservice name for standby database.
test12 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = rhel.manzoor.com) (PORT = 21000)))
(CONNECT_DATA =
(SERVER = DEDICATED) (SERVICE_NAME = test12))
)
7. Now create a pfile using the above modifed parameters for standby datase.
$ vi /optware/oracle/inittest12.ora
*.audit_file_dest='/optware/oracle/admin/test12/adump'
*.audit_trail='DB'
*.compatible='11.2.0.0.0'
*.control_files='/optware/oracle/orafiles/test12/control01.ctl','/optware/oracle/orafiles/test12/control02.ctl'#Restore Controlfile
*.core_dump_dest='/optware/oracle/diag/rdbms/test11/test11/cdump'
*.db_block_size=8192
*.db_cache_size=52M
*.db_domain=''
*.db_file_name_convert='+DATA/test11','/optware/oracle/orafiles/test12'
*.db_name='test11'
*.db_unique_name='test12'
*.db_recovery_file_dest='/optware/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=3852M
*.diagnostic_dest='/optware/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=test12XDB)'
*.fal_client='test12'
*.fal_server='test11'
*.local_listener='lsnr_test12'
*.log_archive_config='DG_CONFIG=(test11,test12)'
*.log_archive_dest_1='location=/optware/oracle/orafiles/test12/archive_log valid_for=(all_logfiles,all_roles) db_unique_name=test12'
*.log_archive_dest_2='service=test11 lgwr async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=test11'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='defer'
*.log_archive_format='%t_%s_%r.dbf'
*.log_buffer=5906432# log buffer update
*.log_file_name_convert='+DATA/test11','/optware/oracle/orafiles/test12'
*.memory_target=400M
*.open_cursors=300
*.optimizer_capture_sql_plan_baselines=FALSE
*.optimizer_dynamic_sampling=2
*.optimizer_mode='ALL_ROWS'
*.plsql_warnings='DISABLE:ALL'# PL/SQL warnings at init.ora
*.processes=150
*.query_rewrite_enabled='TRUE'
*.remote_login_passwordfile='EXCLUSIVE'
*.result_cache_max_size=1M
*.skip_unusable_indexes=TRUE
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
8. Create a password for the standby database, the password should be same as the primary db password.
$ cd $ORACLE_HOME/dbs
$ orapwd file=orapwtest12 password=sys123 ignorecase=y
9. Start up the standby instance in nomount state.
[oracle@rhel ~]$export ORACLE_SID=test12
[oracle@rhel ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 21 09:52:51 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile='/optware/oracle/inittest12.ora';
File created.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 418484224 bytes
Fixed Size 1336932 bytes
Variable Size 260049308 bytes
Database Buffers 150994944 bytes
Redo Buffers 6103040 bytes
SQL> exit
10) Using the rman create the standby database.
[oracle@rhel admin]$ export ORACLE_SID=test11
[oracle@rhel admin]$ rman target / auxiliary sys/sys123@test12
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Dec 21 10:01:53 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: TEST11 (DBID=3487132874)
connected to auxiliary database: TEST11 (not mounted)
RMAN> duplicate target database for standby from active database;
Starting Duplicate Db at 21-DEC-12
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/optware/oracle/product/11.2.0/db_1/dbs/orapwtest11' auxiliary format
'/optware/oracle/product/11.2.0/db_1/dbs/orapwtest12' ;
}
executing Memory Script
Starting backup at 21-DEC-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=64 device type=DISK
Finished backup at 21-DEC-12
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/optware/oracle/orafiles/test12/control01.ctl';
restore clone controlfile to '/optware/oracle/orafiles/test12/control02.ctl' from
'/optware/oracle/orafiles/test12/control01.ctl';
}
executing Memory Script
Starting backup at 21-DEC-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/optware/oracle/product/11.2.0/db_1/dbs/snapcf_test11.f tag=TAG20121221T100211 RECID=23 STAMP=802605731
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 21-DEC-12
Starting restore at 21-DEC-12
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 21-DEC-12
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 2 to
"/optware/oracle/orafiles/test12/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/optware/oracle/orafiles/test12/system01.dbf";
set newname for datafile 2 to
"/optware/oracle/orafiles/test12/sysaux01.dbf";
set newname for datafile 3 to
"/optware/oracle/orafiles/test12/undotbs01.dbf";
set newname for datafile 4 to
"/optware/oracle/orafiles/test12/users01.dbf";
set newname for datafile 5 to
"/optware/oracle/orafiles/test12/fda_ts01.dbf";
set newname for datafile 6 to
"/optware/oracle/orafiles/test12/ggs_tblspc01.dbf";
set newname for datafile 8 to
"/optware/oracle/orafiles/test12/ahamed_tblspc01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/optware/oracle/orafiles/test12/system01.dbf" datafile
2 auxiliary format
"/optware/oracle/orafiles/test12/sysaux01.dbf" datafile
3 auxiliary format
"/optware/oracle/orafiles/test12/undotbs01.dbf" datafile
4 auxiliary format
"/optware/oracle/orafiles/test12/users01.dbf" datafile
5 auxiliary format
"/optware/oracle/orafiles/test12/fda_ts01.dbf" datafile
6 auxiliary format
"/optware/oracle/orafiles/test12/ggs_tblspc01.dbf" datafile
8 auxiliary format
"/optware/oracle/orafiles/test12/ahamed_tblspc01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 2 to /optware/oracle/orafiles/test12/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 21-DEC-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/test11/system01.dbf
output file name=/optware/oracle/orafiles/test12/system01.dbf tag=TAG20121221T100221
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:46
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/test11/sysaux01.dbf
output file name=/optware/oracle/orafiles/test12/sysaux01.dbf tag=TAG20121221T100221
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=+DATA/test11/ahamed_tblspc01.dbf
output file name=/optware/oracle/orafiles/test12/ahamed_tblspc01.dbf tag=TAG20121221T100221
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA/test11/ggs_tblspc01.dbf
output file name=/optware/oracle/orafiles/test12/ggs_tblspc01.dbf tag=TAG20121221T100221
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/test11/undotbs01.dbf
output file name=/optware/oracle/orafiles/test12/undotbs01.dbf tag=TAG20121221T100221
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/test11/users01.dbf
output file name=/optware/oracle/orafiles/test12/users01.dbf tag=TAG20121221T100221
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/test11/fda_ts01.dbf
output file name=/optware/oracle/orafiles/test12/fda_ts01.dbf tag=TAG20121221T100221
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 21-DEC-12
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=23 STAMP=802605856 file name=/optware/oracle/orafiles/test12/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=24 STAMP=802605856 file name=/optware/oracle/orafiles/test12/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=25 STAMP=802605856 file name=/optware/oracle/orafiles/test12/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=26 STAMP=802605856 file name=/optware/oracle/orafiles/test12/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=27 STAMP=802605856 file name=/optware/oracle/orafiles/test12/fda_ts01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=28 STAMP=802605856 file name=/optware/oracle/orafiles/test12/ggs_tblspc01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=29 STAMP=802605856 file name=/optware/oracle/orafiles/test12/ahamed_tblspc01.dbf
Finished Duplicate Db at 21-DEC-12
RMAN> exit
Recovery Manager complete.
11) Add the standby redolog files in primary and standby database.
[oracle@rhel admin]$ export ORACLE_SID=test11
SQL> alter database add standby logfile thread 1 group 4 '+DATA/test11/redo04.log' size 52428800;
Database altered.
SQL> alter database add standby logfile thread 1 group 5 '+DATA/test11/redo05.log' size 52428800;
Database altered.
SQL> alter database add standby logfile thread 1 group 6 '+DATA/test11/redo06.log' size 52428800;
Database altered.
SQL> exit
[oracle@rhel admin]$ export ORACLE_SID=test12
SQL> alter database add standby logfile thread 1 group 4 '/optware/oracle/orafiles/test12/redo04.log' size 52428800;
Database altered.
SQL> alter database add standby logfile thread 1 group 5 '/optware/oracle/orafiles/test12/redo05.log' size 52428800;
Database altered.
SQL> alter database add standby logfile thread 1 group 6 '/optware/oracle/orafiles/test12/redo06.log' size 52428800;
Database altered.
12) Enable the logshiping to standby database in priamry db and check the destination status.
[oracle@rhel admin]$ export ORACLE_SID=test11
SQL> alter system set log_archive_dest_state_2 = 'enable' scope=both;
System altered.
SQL> COL DEST_NAME FORM A40;
SQL> COL DESTINATION FORM A20;
SQL> COL ERROR Form A10;
SQL> select dest_name, status, destination, error, db_unique_name from v$archive_dest_status WHERE DEST_ID IN (1,2);
DEST_NAME STATUS DESTINATION ERROR DB_UNIQUE_NAME
---------------------------------------- --------- -------------------- ----------------------------------------------------------------- ------------------------------
LOG_ARCHIVE_DEST_1 VALID +DATA/archive_log test11
LOG_ARCHIVE_DEST_2 VALID test12 test12
13) Do a Log switch.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
14) In Standby database enable the MRP process and start the real time apply.
[oracle@rhel admin]$ export ORACLE_SID=test12
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
15) Check the status of the processes in standby.
SQL> select process, status from V$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CLOSING
ARCH CONNECTED
ARCH CLOSING
ARCH CLOSING
RFS IDLE
RFS IDLE
RFS IDLE
RFS IDLE
MRP0 APPLYING_LOG
9 rows selected.
-- The MRP process is applying the logs.
SQL> select sequence#, archived, applied from V$archived_log order by 1;
SEQUENCE# ARC APPLIED
---------- --- ---------
28 YES YES
29 YES YES
30 YES YES
31 YES IN-MEMORY
16) Check whether primary and standby are in sync, do a logswitch in primary and check
the status.
[oracle@rhel admin]$ export ORACLE_SID=test11
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> select * from V$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 34 52428800 512 1 NO CURRENT 1565593 21-DEC-12 2.8147E+14
2 1 32 52428800 512 1 YES ACTIVE 1565204 21-DEC-12 1565589 21-DEC-12
3 1 33 52428800 512 1 YES ACTIVE 1565589 21-DEC-12 1565593 21-DEC-12
[oracle@rhel admin]$ export ORACLE_SID=test12
SQL> select * from V$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 34 52428800 512 1 YES CLEARING_CURRENT 1565593 21-DEC-12 1565204 21-DEC-12
3 1 33 52428800 512 1 YES CLEARING 1565589 21-DEC-12 1565593 21-DEC-12
2 1 32 52428800 512 1 YES CLEARING 1565204 21-DEC-12 1565589 21-DEC-12
SQL> select sequence#, archived, applied from V$archived_log order by 1;
SEQUENCE# ARC APPLIED
---------- --- ---------
28 YES YES
29 YES YES
30 YES YES
31 YES YES
32 YES YES
33 YES IN-MEMORY
6 rows selected.
--Now both the primary and the standby database are in sync.
Role Change Testing:-
=====================
1. In the primary database check whether the databse is ready for the switch over.
SQL> select SWITCHOVER_STATUS from V$database;
SWITCHOVER_STATUS
--------------------
TO STANDBY
-- The status is showing as "TO STANDBY" hence it is ready now to switch over.
2. Do switchover on the primary db.
SQL> alter database commit to switchover to physical standby;
Database altered.
SQL> shut immediate;
SQL> startup mount;
ORACLE instance started.
Total System Global Area 263639040 bytes
Fixed Size 1335892 bytes
Variable Size 192941484 bytes
Database Buffers 62914560 bytes
Redo Buffers 6447104 bytes
Database mounted.
SQL> select database_role from V$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
3. Now check the status in the standby database and change the role to primary.
export ORACLE_SID=test12
SQL> select switchover_status from V$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
SQL> alter database commit to switchover to primary;
Database altered.
SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 418484224 bytes
Fixed Size 1336932 bytes
Variable Size 260049308 bytes
Database Buffers 150994944 bytes
Redo Buffers 6103040 bytes
Database mounted.
Database opened.
4. Enable the log shipping to the current standby database and check the destination status.
SQL> alter system set log_archive_dest_state_2 = 'enable' scope=both;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> COL DEST_NAME FORM A40;
SQL> COL DESTINATION FORM A20;
SQL> col error form a10;
SQL> select dest_name, status, destination, error, db_unique_name from v$archive_dest_status WHERE DEST_ID IN (1,2);
DEST_NAME STATUS DESTINATION ERROR DB_UNIQUE_NAME
---------------------------------------- --------- -------------------- ---------- ------------------------------
LOG_ARCHIVE_DEST_1 VALID /optware/oracle/oraf test12
iles/test12/archive_
log
LOG_ARCHIVE_DEST_2 VALID test11 test11
-Both the destination status shows Valid.
5. Start the MRP process in the current standby database.
export ORACLE_SID=test11
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select process, status from V$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CLOSING
ARCH CONNECTED
ARCH CONNECTED
ARCH CLOSING
RFS IDLE
RFS IDLE
MRP0 APPLYING_LOG
7 rows selected.
6. Check whether both the primary and db are in sync.
export ORACLE_SID=test12
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> select * from V$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 43 52428800 512 1 NO CURRENT 1566811 21-DEC-12 2.8147E+14
2 1 41 52428800 512 1 YES INACTIVE 1566803 21-DEC-12 1566808 21-DEC-12
3 1 42 52428800 512 1 YES INACTIVE 1566808 21-DEC-12 1566811 21-DEC-12
SQL> select dest_id, archived , max(sequence#) from V$archived_log group by dest_id,archived order by 1;
DEST_ID ARC MAX(SEQUENCE#)
---------- --- --------------
1 YES 42
2 YES 42
export ORACLE_SID=test11
SQL> select * from V$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 43 52428800 512 1 YES CLEARING_CURRENT 1566811 21-DEC-12 2.8147E+14
3 1 39 52428800 512 1 YES CLEARING 1566499 21-DEC-12 1566800 21-DEC-12
2 1 41 52428800 512 1 YES CLEARING 1566803 21-DEC-12 1566499 21-DEC-12
SQL> select sequence#, archived, applied from V$archived_log where sequence#= 42;
SEQUENCE# ARC APPLIED
---------- --- ---------
42 YES IN-MEMORY
-- Both the primary and the standby datbase are in sync.
--Now revert back the db roles using the above procedure.
--Now the primary db is test11 and the standby is test12
Redo log gap syncing scenario.
=============================
scenario :-
Due to some network issues / any other type of issues the archive logs are not transferred to the standby site.
There are more no. of logfiles are not transferred. Say if we solve the issue the fal process automatically
starts to transfers the archivelog files but it will take some time incase there are huge no. of files.
Instead we can take a backup of the missing archivelog and restore on the destination site.
Below is the test case.
=======================
export ORACLE_SID=test11
SQL> alter system set log_archive_dest_state_2 = 'defer' scope=both;
System altered.
SQL> begin
2 for i in 1..30 loop
3 execute immediate 'alter system switch logfile';
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 79 52428800 512 1 YES INACTIVE 1567854 21-DEC-12 1567857 21-DEC-12
2 1 80 52428800 512 1 YES INACTIVE 1567857 21-DEC-12 1567860 21-DEC-12
3 1 81 52428800 512 1 NO CURRENT 1567860 21-DEC-12 2.8147E+14
export ORACLE_SID=test12
SQL> select * from V$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 49 52428800 512 1 YES CLEARING 1567459 21-DEC-12 1567606 21-DEC-12
3 1 51 52428800 512 1 YES CLEARING_CURRENT 1567612 21-DEC-12 1567459 21-DEC-12
2 1 50 52428800 512 1 YES CLEARING 1567606 21-DEC-12 1567612 21-DEC-12
SQL> select max(sequence#) from v$archived_log where applied = 'YES';
MAX(SEQUENCE#)
--------------
51
## here in the standby site up to the log sequence 51 has been applied, now the current sequence in the primary
site is 81, hence we will take the backup of the archived log from 52 to 80.
export ORACLE_SID=test11
RMAN> run
2> {
3> allocate channel a1 device type disk format '/optware/oracle/archive_log_between_52and80_%t_%r_%d';
4> backup archivelog sequence between 52 and 80;
5> release channel a1;
6> }
allocated channel: a1
channel a1: SID=51 device type=DISK
Starting backup at 21-DEC-12
channel a1: starting archived log backup set
channel a1: specifying archived log(s) in backup set
input archived log thread=1 sequence=52 RECID=91 STAMP=802619116
input archived log thread=1 sequence=53 RECID=92 STAMP=802619119
input archived log thread=1 sequence=54 RECID=93 STAMP=802619119
input archived log thread=1 sequence=55 RECID=94 STAMP=802619122
input archived log thread=1 sequence=56 RECID=95 STAMP=802619122
input archived log thread=1 sequence=57 RECID=96 STAMP=802619125
input archived log thread=1 sequence=58 RECID=97 STAMP=802619125
input archived log thread=1 sequence=59 RECID=98 STAMP=802619125
input archived log thread=1 sequence=60 RECID=99 STAMP=802619125
input archived log thread=1 sequence=61 RECID=100 STAMP=802619128
input archived log thread=1 sequence=62 RECID=101 STAMP=802619128
input archived log thread=1 sequence=63 RECID=103 STAMP=802619128
input archived log thread=1 sequence=64 RECID=102 STAMP=802619128
input archived log thread=1 sequence=65 RECID=104 STAMP=802619131
input archived log thread=1 sequence=66 RECID=105 STAMP=802619131
input archived log thread=1 sequence=67 RECID=106 STAMP=802619134
input archived log thread=1 sequence=68 RECID=107 STAMP=802619134
input archived log thread=1 sequence=69 RECID=108 STAMP=802619137
input archived log thread=1 sequence=70 RECID=109 STAMP=802619138
input archived log thread=1 sequence=71 RECID=110 STAMP=802619140
input archived log thread=1 sequence=72 RECID=111 STAMP=802619143
input archived log thread=1 sequence=73 RECID=112 STAMP=802619143
input archived log thread=1 sequence=74 RECID=113 STAMP=802619146
input archived log thread=1 sequence=75 RECID=114 STAMP=802619146
input archived log thread=1 sequence=76 RECID=115 STAMP=802619149
input archived log thread=1 sequence=77 RECID=116 STAMP=802619149
input archived log thread=1 sequence=78 RECID=117 STAMP=802619149
input archived log thread=1 sequence=79 RECID=118 STAMP=802619149
input archived log thread=1 sequence=80 RECID=119 STAMP=802619149
channel a1: starting piece 1 at 21-DEC-12
channel a1: finished piece 1 at 21-DEC-12
piece handle=/optware/oracle/archive_log_between_52and80_802619620_%r_TEST11 tag=TAG20121221T135339 comment=NONE
channel a1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-DEC-12
released channel: a1
RMAN> exit
export ORACLE_SID=test12
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> exit
[oracle@rhel oracle]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Dec 21 14:02:13 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: TEST11 (DBID=3487132874, not open)
RMAN> catalog backuppiece '/optware/oracle/archive_log_between_52and80_802619620_%r_TEST11';
using target database control file instead of recovery catalog
cataloged backup piece
backup piece handle=/optware/oracle/archive_log_between_52and80_802619620_%r_TEST11 RECID=4 STAMP=802620173
RMAN> list backup of archivelog all;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
4 38.50K DISK 00:00:00 21-DEC-12
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20121221T135339
Piece Name: /optware/oracle/archive_log_between_52and80_802619620_%r_TEST11
List of Archived Logs in backup set 4
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 52 1567739 21-DEC-12 1567742 21-DEC-12
1 53 1567742 21-DEC-12 1567748 21-DEC-12
1 54 1567748 21-DEC-12 1567751 21-DEC-12
1 55 1567751 21-DEC-12 1567757 21-DEC-12
1 56 1567757 21-DEC-12 1567760 21-DEC-12
1 57 1567760 21-DEC-12 1567766 21-DEC-12
1 58 1567766 21-DEC-12 1567769 21-DEC-12
1 59 1567769 21-DEC-12 1567772 21-DEC-12
1 60 1567772 21-DEC-12 1567775 21-DEC-12
1 61 1567775 21-DEC-12 1567781 21-DEC-12
1 62 1567781 21-DEC-12 1567784 21-DEC-12
1 63 1567784 21-DEC-12 1567787 21-DEC-12
1 64 1567787 21-DEC-12 1567790 21-DEC-12
1 65 1567790 21-DEC-12 1567796 21-DEC-12
1 66 1567796 21-DEC-12 1567799 21-DEC-12
1 67 1567799 21-DEC-12 1567806 21-DEC-12
1 68 1567806 21-DEC-12 1567809 21-DEC-12
1 69 1567809 21-DEC-12 1567814 21-DEC-12
1 70 1567814 21-DEC-12 1567820 21-DEC-12
1 71 1567820 21-DEC-12 1567824 21-DEC-12
1 72 1567824 21-DEC-12 1567830 21-DEC-12
1 73 1567830 21-DEC-12 1567833 21-DEC-12
1 74 1567833 21-DEC-12 1567839 21-DEC-12
1 75 1567839 21-DEC-12 1567842 21-DEC-12
1 76 1567842 21-DEC-12 1567848 21-DEC-12
1 77 1567848 21-DEC-12 1567851 21-DEC-12
1 78 1567851 21-DEC-12 1567854 21-DEC-12
1 79 1567854 21-DEC-12 1567857 21-DEC-12
1 80 1567857 21-DEC-12 1567860 21-DEC-12
RMAN> restore archivelog sequence between 52 and 80;
Starting restore at 21-DEC-12
using channel ORA_DISK_1
archived log for thread 1 with sequence 52 is already on disk as file /optware/oracle/orafiles/test12/archive_log/1_52_799248578.dbf
archived log for thread 1 with sequence 53 is already on disk as file /optware/oracle/orafiles/test12/archive_log/1_53_799248578.dbf
archived log for thread 1 with sequence 54 is already on disk as file /optware/oracle/orafiles/test12/archive_log/1_54_799248578.dbf
archived log for thread 1 with sequence 55 is already on disk as file /optware/oracle/orafiles/test12/archive_log/1_55_799248578.dbf
archived log for thread 1 with sequence 56 is already on disk as file /optware/oracle/orafiles/test12/archive_log/1_56_799248578.dbf
archived log for thread 1 with sequence 57 is already on disk as file /optware/oracle/orafiles/test12/archive_log/1_57_799248578.dbf
archived log for thread 1 with sequence 58 is already on disk as file /optware/oracle/orafiles/test12/archive_log/1_58_799248578.dbf
archived log for thread 1 with sequence 59 is already on disk as file /optware/oracle/orafiles/test12/archive_log/1_59_799248578.dbf
archived log for thread 1 with sequence 60 is already on disk as file /optware/oracle/orafiles/test12/archive_log/1_60_799248578.dbf
archived log for thread 1 with sequence 61 is already on disk as file /optware/oracle/orafiles/test12/archive_log/1_61_799248578.dbf
archived log for thread 1 with sequence 62 is already on disk as file /optware/oracle/orafiles/test12/archive_log/1_62_799248578.dbf
archived log for thread 1 with sequence 63 is already on disk as file /optware/oracle/orafiles/test12/archive_log/1_63_799248578.dbf
archived log for thread 1 with sequence 64 is already on disk as file /optware/oracle/orafiles/test12/archive_log/1_64_799248578.dbf
archived log for thread 1 with sequence 65 is already on disk as file /optware/oracle/orafiles/test12/archive_log/1_65_799248578.dbf
archived log for thread 1 with sequence 66 is already on disk as file /optware/oracle/orafiles/test12/archive_log/1_66_799248578.dbf
archived log for thread 1 with sequence 67 is already on disk as file /optware/oracle/orafiles/test12/archive_log/1_67_799248578.dbf
archived log for thread 1 with sequence 68 is already on disk as file /optware/oracle/orafiles/test12/archive_log/1_68_799248578.dbf
archived log for thread 1 with sequence 69 is already on disk as file /optware/oracle/orafiles/test12/archive_log/1_69_799248578.dbf
archived log for thread 1 with sequence 70 is already on disk as file /optware/oracle/orafiles/test12/archive_log/1_70_799248578.dbf
archived log for thread 1 with sequence 71 is already on disk as file /optware/oracle/orafiles/test12/archive_log/1_71_799248578.dbf
archived log for thread 1 with sequence 72 is already on disk as file /optware/oracle/orafiles/test12/archive_log/1_72_799248578.dbf
archived log for thread 1 with sequence 73 is already on disk as file /optware/oracle/orafiles/test12/archive_log/1_73_799248578.dbf
archived log for thread 1 with sequence 74 is already on disk as file /optware/oracle/orafiles/test12/archive_log/1_74_799248578.dbf
archived log for thread 1 with sequence 75 is already on disk as file /optware/oracle/orafiles/test12/archive_log/1_75_799248578.dbf
archived log for thread 1 with sequence 76 is already on disk as file /optware/oracle/orafiles/test12/archive_log/1_76_799248578.dbf
archived log for thread 1 with sequence 77 is already on disk as file /optware/oracle/orafiles/test12/archive_log/1_77_799248578.dbf
archived log for thread 1 with sequence 78 is already on disk as file /optware/oracle/orafiles/test12/archive_log/1_78_799248578.dbf
archived log for thread 1 with sequence 79 is already on disk as file /optware/oracle/orafiles/test12/archive_log/1_79_799248578.dbf
archived log for thread 1 with sequence 80 is already on disk as file /optware/oracle/orafiles/test12/archive_log/1_80_799248578.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 21-DEC-12
RMAN> exit
Now start the MRP process.
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
-- Do a logswitch on the primary site and enable the dest state.
SQL> alter system set log_archive_dest_state_2 = 'enable' scope=both;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
Check the status on the standby site.
export ORACLE_SID=test12
SQL> select * from V$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 82 52428800 512 1 YES CLEARING 1569880 21-DEC-12 1569885 21-DEC-12
3 1 84 52428800 512 1 YES CLEARING_CURRENT 1569891 21-DEC-12 1569880 21-DEC-12
2 1 83 52428800 512 1 YES CLEARING 1569885 21-DEC-12 1569891 21-DEC-12
SQL> select sequence#, archived, applied from V$archived_log where sequence# > 51 order by 1;
SEQUENCE# ARC APPLIED
---------- --- ---------
52 YES YES
53 YES YES
54 YES YES
55 YES YES
56 YES YES
57 YES YES
58 YES YES
59 YES YES
60 YES YES
61 YES YES
62 YES YES
63 YES YES
64 YES YES
65 YES YES
66 YES YES
67 YES YES
68 YES YES
69 YES YES
70 YES YES
71 YES YES
72 YES YES
73 YES YES
74 YES YES
75 YES YES
76 YES YES
77 YES YES
78 YES YES
79 YES YES
80 YES YES
81 YES YES
82 YES YES
83 YES YES
32 rows selected.
--Now all the logs are applied.
Scenario 2 --
Some archive log files are deleted before the same is been transferred to the standby site.
All standby mpr process is waiting for the log and not proceeding, but here we have the
backup of the archive log files. Lets test this scenario.
export ORACLE_SID=test11
SQL> alter system set log_archive_dest_state_2 = 'defer' scope=both;
System altered.
SQL> begin
2 for i in 1..50 loop
3 execute immediate 'alter system switch logfile';
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> show parameter dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=test12 lgwr async noaf
firm valid_for=(online_logfile
s,primary_role) db_unique_name
=test12
SQL> alter system set log_archive_dest_2='' scope=both;
System altered.
SQL> exit
[oracle@rhel trace]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Dec 21 15:03:16 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: TEST11 (DBID=3487132874)
RMAN> backup device type disk format '/optware/oracle/archive_bkp_of_prm_%U' archivelog all delete input;
Starting backup at 21-DEC-12
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=84 RECID=126 STAMP=802623032
input archived log thread=1 sequence=85 RECID=127 STAMP=802623177
input archived log thread=1 sequence=86 RECID=128 STAMP=802623180
input archived log thread=1 sequence=87 RECID=129 STAMP=802623250
input archived log thread=1 sequence=88 RECID=130 STAMP=802623540
input archived log thread=1 sequence=89 RECID=132 STAMP=802623541
input archived log thread=1 sequence=90 RECID=131 STAMP=802623540
input archived log thread=1 sequence=91 RECID=133 STAMP=802623541
input archived log thread=1 sequence=92 RECID=134 STAMP=802623544
input archived log thread=1 sequence=93 RECID=135 STAMP=802623544
input archived log thread=1 sequence=94 RECID=136 STAMP=802623547
input archived log thread=1 sequence=95 RECID=137 STAMP=802623547
input archived log thread=1 sequence=96 RECID=138 STAMP=802623547
input archived log thread=1 sequence=97 RECID=139 STAMP=802623548
input archived log thread=1 sequence=98 RECID=140 STAMP=802623548
input archived log thread=1 sequence=99 RECID=141 STAMP=802623549
input archived log thread=1 sequence=100 RECID=142 STAMP=802623549
input archived log thread=1 sequence=101 RECID=143 STAMP=802623550
input archived log thread=1 sequence=102 RECID=144 STAMP=802623553
input archived log thread=1 sequence=103 RECID=145 STAMP=802623554
input archived log thread=1 sequence=104 RECID=146 STAMP=802623554
input archived log thread=1 sequence=105 RECID=147 STAMP=802623554
input archived log thread=1 sequence=106 RECID=148 STAMP=802623554
input archived log thread=1 sequence=107 RECID=149 STAMP=802623554
input archived log thread=1 sequence=108 RECID=150 STAMP=802623556
input archived log thread=1 sequence=109 RECID=151 STAMP=802623556
input archived log thread=1 sequence=110 RECID=152 STAMP=802623558
input archived log thread=1 sequence=111 RECID=153 STAMP=802623558
input archived log thread=1 sequence=112 RECID=154 STAMP=802623561
input archived log thread=1 sequence=113 RECID=155 STAMP=802623561
input archived log thread=1 sequence=114 RECID=156 STAMP=802623564
input archived log thread=1 sequence=115 RECID=157 STAMP=802623564
input archived log thread=1 sequence=116 RECID=158 STAMP=802623567
input archived log thread=1 sequence=117 RECID=159 STAMP=802623567
input archived log thread=1 sequence=118 RECID=160 STAMP=802623570
input archived log thread=1 sequence=119 RECID=161 STAMP=802623570
input archived log thread=1 sequence=120 RECID=162 STAMP=802623573
input archived log thread=1 sequence=121 RECID=163 STAMP=802623573
input archived log thread=1 sequence=122 RECID=164 STAMP=802623573
input archived log thread=1 sequence=123 RECID=165 STAMP=802623573
input archived log thread=1 sequence=124 RECID=166 STAMP=802623576
input archived log thread=1 sequence=125 RECID=167 STAMP=802623576
input archived log thread=1 sequence=126 RECID=168 STAMP=802623579
input archived log thread=1 sequence=127 RECID=169 STAMP=802623579
input archived log thread=1 sequence=128 RECID=170 STAMP=802623579
input archived log thread=1 sequence=129 RECID=171 STAMP=802623579
input archived log thread=1 sequence=130 RECID=172 STAMP=802623582
input archived log thread=1 sequence=131 RECID=173 STAMP=802623582
input archived log thread=1 sequence=132 RECID=174 STAMP=802623585
input archived log thread=1 sequence=133 RECID=175 STAMP=802623585
input archived log thread=1 sequence=134 RECID=177 STAMP=802623586
input archived log thread=1 sequence=135 RECID=176 STAMP=802623586
input archived log thread=1 sequence=136 RECID=178 STAMP=802623589
input archived log thread=1 sequence=137 RECID=179 STAMP=802623589
input archived log thread=1 sequence=138 RECID=180 STAMP=802623658
input archived log thread=1 sequence=139 RECID=181 STAMP=802623840
channel ORA_DISK_1: starting piece 1 at 21-DEC-12
channel ORA_DISK_1: finished piece 1 at 21-DEC-12
piece handle=/optware/oracle/archive_bkp_of_prm_0vnte4b2_1_1 tag=TAG20121221T150401 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: deleting archived log(s)
archived log file name=+DATA/archive_log/1_84_799248578.dbf RECID=126 STAMP=802623032
archived log file name=+DATA/archive_log/1_85_799248578.dbf RECID=127 STAMP=802623177
archived log file name=+DATA/archive_log/1_86_799248578.dbf RECID=128 STAMP=802623180
archived log file name=+DATA/archive_log/1_87_799248578.dbf RECID=129 STAMP=802623250
archived log file name=+DATA/archive_log/1_88_799248578.dbf RECID=130 STAMP=802623540
archived log file name=+DATA/archive_log/1_89_799248578.dbf RECID=132 STAMP=802623541
archived log file name=+DATA/archive_log/1_90_799248578.dbf RECID=131 STAMP=802623540
archived log file name=+DATA/archive_log/1_91_799248578.dbf RECID=133 STAMP=802623541
archived log file name=+DATA/archive_log/1_92_799248578.dbf RECID=134 STAMP=802623544
archived log file name=+DATA/archive_log/1_93_799248578.dbf RECID=135 STAMP=802623544
archived log file name=+DATA/archive_log/1_94_799248578.dbf RECID=136 STAMP=802623547
archived log file name=+DATA/archive_log/1_95_799248578.dbf RECID=137 STAMP=802623547
archived log file name=+DATA/archive_log/1_96_799248578.dbf RECID=138 STAMP=802623547
archived log file name=+DATA/archive_log/1_97_799248578.dbf RECID=139 STAMP=802623548
archived log file name=+DATA/archive_log/1_98_799248578.dbf RECID=140 STAMP=802623548
archived log file name=+DATA/archive_log/1_99_799248578.dbf RECID=141 STAMP=802623549
archived log file name=+DATA/archive_log/1_100_799248578.dbf RECID=142 STAMP=802623549
archived log file name=+DATA/archive_log/1_101_799248578.dbf RECID=143 STAMP=802623550
archived log file name=+DATA/archive_log/1_102_799248578.dbf RECID=144 STAMP=802623553
archived log file name=+DATA/archive_log/1_103_799248578.dbf RECID=145 STAMP=802623554
archived log file name=+DATA/archive_log/1_104_799248578.dbf RECID=146 STAMP=802623554
archived log file name=+DATA/archive_log/1_105_799248578.dbf RECID=147 STAMP=802623554
archived log file name=+DATA/archive_log/1_106_799248578.dbf RECID=148 STAMP=802623554
archived log file name=+DATA/archive_log/1_107_799248578.dbf RECID=149 STAMP=802623554
archived log file name=+DATA/archive_log/1_108_799248578.dbf RECID=150 STAMP=802623556
archived log file name=+DATA/archive_log/1_109_799248578.dbf RECID=151 STAMP=802623556
archived log file name=+DATA/archive_log/1_110_799248578.dbf RECID=152 STAMP=802623558
archived log file name=+DATA/archive_log/1_111_799248578.dbf RECID=153 STAMP=802623558
archived log file name=+DATA/archive_log/1_112_799248578.dbf RECID=154 STAMP=802623561
archived log file name=+DATA/archive_log/1_113_799248578.dbf RECID=155 STAMP=802623561
archived log file name=+DATA/archive_log/1_114_799248578.dbf RECID=156 STAMP=802623564
archived log file name=+DATA/archive_log/1_115_799248578.dbf RECID=157 STAMP=802623564
archived log file name=+DATA/archive_log/1_116_799248578.dbf RECID=158 STAMP=802623567
archived log file name=+DATA/archive_log/1_117_799248578.dbf RECID=159 STAMP=802623567
archived log file name=+DATA/archive_log/1_118_799248578.dbf RECID=160 STAMP=802623570
archived log file name=+DATA/archive_log/1_119_799248578.dbf RECID=161 STAMP=802623570
archived log file name=+DATA/archive_log/1_120_799248578.dbf RECID=162 STAMP=802623573
archived log file name=+DATA/archive_log/1_121_799248578.dbf RECID=163 STAMP=802623573
archived log file name=+DATA/archive_log/1_122_799248578.dbf RECID=164 STAMP=802623573
archived log file name=+DATA/archive_log/1_123_799248578.dbf RECID=165 STAMP=802623573
archived log file name=+DATA/archive_log/1_124_799248578.dbf RECID=166 STAMP=802623576
archived log file name=+DATA/archive_log/1_125_799248578.dbf RECID=167 STAMP=802623576
archived log file name=+DATA/archive_log/1_126_799248578.dbf RECID=168 STAMP=802623579
archived log file name=+DATA/archive_log/1_127_799248578.dbf RECID=169 STAMP=802623579
archived log file name=+DATA/archive_log/1_128_799248578.dbf RECID=170 STAMP=802623579
archived log file name=+DATA/archive_log/1_129_799248578.dbf RECID=171 STAMP=802623579
archived log file name=+DATA/archive_log/1_130_799248578.dbf RECID=172 STAMP=802623582
archived log file name=+DATA/archive_log/1_131_799248578.dbf RECID=173 STAMP=802623582
archived log file name=+DATA/archive_log/1_132_799248578.dbf RECID=174 STAMP=802623585
archived log file name=+DATA/archive_log/1_133_799248578.dbf RECID=175 STAMP=802623585
archived log file name=+DATA/archive_log/1_134_799248578.dbf RECID=177 STAMP=802623586
archived log file name=+DATA/archive_log/1_135_799248578.dbf RECID=176 STAMP=802623586
archived log file name=+DATA/archive_log/1_136_799248578.dbf RECID=178 STAMP=802623589
archived log file name=+DATA/archive_log/1_137_799248578.dbf RECID=179 STAMP=802623589
archived log file name=+DATA/archive_log/1_138_799248578.dbf RECID=180 STAMP=802623658
archived log file name=+DATA/archive_log/1_139_799248578.dbf RECID=181 STAMP=802623840
Finished backup at 21-DEC-12
RMAN> EXIT
Recovery Manager complete.
SQL> alter system set log_archive_dest_2 = 'service=test12 lgwr async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=test12' scope=both;
System altered.
SQL> alter system set log_archive_dest_state_2 = 'enable' scope=both;
System altered.
-- Check the status in the standby database.
SQL> select sequence#, archived, applied from v$archived_log order by 1;
SEQUENCE# ARC APPLIED
---------- --- ---------
74 YES YES
75 YES YES
76 YES YES
77 YES YES
78 YES YES
79 YES YES
80 YES YES
81 YES YES
82 YES YES
83 YES YES
140 YES NO
SEQUENCE# ARC APPLIED
---------- --- ---------
141 YES NO
142 YES NO
-- We could see that there is a gap between 83 and the 140.
SQL> select * from V$archive_gap;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 84 139
-- Check the backup of archivelog in primary database.
export ORACLE_SID=test11
RMAN> list backup of archivelog from sequence 83 until sequence 140;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
7 86.61M DISK 00:00:07 21-DEC-12
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20121221T145411
Piece Name: /optware/oracle/arc_bkp_of_prm_0rnte3ru_1_1
List of Archived Logs in backup set 7
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 83 1569885 21-DEC-12 1569891 21-DEC-12
1 84 1569891 21-DEC-12 1592856 21-DEC-12
1 85 1592856 21-DEC-12 1593418 21-DEC-12
1 86 1593418 21-DEC-12 1593553 21-DEC-12
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
8 56.00K DISK 00:00:00 21-DEC-12
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20121221T145411
Piece Name: /optware/oracle/arc_bkp_of_prm_0snte3sa_1_1
List of Archived Logs in backup set 8
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 87 1593553 21-DEC-12 1593679 21-DEC-12
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
10 3.53M DISK 00:00:02 21-DEC-12
BP Key: 10 Status: AVAILABLE Compressed: NO Tag: TAG20121221T150059
Piece Name: /optware/oracle/archive_log_bkp_0unte45e_1_1
List of Archived Logs in backup set 10
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 84 1569891 21-DEC-12 1592856 21-DEC-12
1 85 1592856 21-DEC-12 1593418 21-DEC-12
1 86 1593418 21-DEC-12 1593553 21-DEC-12
1 87 1593553 21-DEC-12 1593679 21-DEC-12
1 88 1593679 21-DEC-12 1593992 21-DEC-12
1 89 1593992 21-DEC-12 1593995 21-DEC-12
1 90 1593995 21-DEC-12 1593999 21-DEC-12
1 91 1593999 21-DEC-12 1594002 21-DEC-12
1 92 1594002 21-DEC-12 1594008 21-DEC-12
1 93 1594008 21-DEC-12 1594011 21-DEC-12
1 94 1594011 21-DEC-12 1594017 21-DEC-12
1 95 1594017 21-DEC-12 1594020 21-DEC-12
1 96 1594020 21-DEC-12 1594023 21-DEC-12
1 97 1594023 21-DEC-12 1594027 21-DEC-12
1 98 1594027 21-DEC-12 1594030 21-DEC-12
1 99 1594030 21-DEC-12 1594034 21-DEC-12
1 100 1594034 21-DEC-12 1594037 21-DEC-12
1 101 1594037 21-DEC-12 1594041 21-DEC-12
1 102 1594041 21-DEC-12 1594047 21-DEC-12
1 103 1594047 21-DEC-12 1594050 21-DEC-12
1 104 1594050 21-DEC-12 1594054 21-DEC-12
1 105 1594054 21-DEC-12 1594057 21-DEC-12
1 106 1594057 21-DEC-12 1594060 21-DEC-12
1 107 1594060 21-DEC-12 1594063 21-DEC-12
1 108 1594063 21-DEC-12 1594067 21-DEC-12
1 109 1594067 21-DEC-12 1594070 21-DEC-12
1 110 1594070 21-DEC-12 1594076 21-DEC-12
1 111 1594076 21-DEC-12 1594079 21-DEC-12
1 112 1594079 21-DEC-12 1594085 21-DEC-12
1 113 1594085 21-DEC-12 1594088 21-DEC-12
1 114 1594088 21-DEC-12 1594094 21-DEC-12
1 115 1594094 21-DEC-12 1594097 21-DEC-12
1 116 1594097 21-DEC-12 1594103 21-DEC-12
1 117 1594103 21-DEC-12 1594106 21-DEC-12
1 118 1594106 21-DEC-12 1594112 21-DEC-12
1 119 1594112 21-DEC-12 1594115 21-DEC-12
1 120 1594115 21-DEC-12 1594121 21-DEC-12
1 121 1594121 21-DEC-12 1594124 21-DEC-12
1 122 1594124 21-DEC-12 1594127 21-DEC-12
1 123 1594127 21-DEC-12 1594130 21-DEC-12
1 124 1594130 21-DEC-12 1594136 21-DEC-12
1 125 1594136 21-DEC-12 1594139 21-DEC-12
1 126 1594139 21-DEC-12 1594145 21-DEC-12
1 127 1594145 21-DEC-12 1594148 21-DEC-12
1 128 1594148 21-DEC-12 1594151 21-DEC-12
1 129 1594151 21-DEC-12 1594154 21-DEC-12
1 130 1594154 21-DEC-12 1594159 21-DEC-12
1 131 1594159 21-DEC-12 1594162 21-DEC-12
1 132 1594162 21-DEC-12 1594168 21-DEC-12
1 133 1594168 21-DEC-12 1594171 21-DEC-12
1 134 1594171 21-DEC-12 1594175 21-DEC-12
1 135 1594175 21-DEC-12 1594178 21-DEC-12
1 136 1594178 21-DEC-12 1594183 21-DEC-12
1 137 1594183 21-DEC-12 1594186 21-DEC-12
1 138 1594186 21-DEC-12 1594276 21-DEC-12
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
11 3.59M DISK 00:00:02 21-DEC-12
BP Key: 11 Status: AVAILABLE Compressed: NO Tag: TAG20121221T150401
Piece Name: /optware/oracle/archive_bkp_of_prm_0vnte4b2_1_1
List of Archived Logs in backup set 11
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 84 1569891 21-DEC-12 1592856 21-DEC-12
1 85 1592856 21-DEC-12 1593418 21-DEC-12
1 86 1593418 21-DEC-12 1593553 21-DEC-12
1 87 1593553 21-DEC-12 1593679 21-DEC-12
1 88 1593679 21-DEC-12 1593992 21-DEC-12
1 89 1593992 21-DEC-12 1593995 21-DEC-12
1 90 1593995 21-DEC-12 1593999 21-DEC-12
1 91 1593999 21-DEC-12 1594002 21-DEC-12
1 92 1594002 21-DEC-12 1594008 21-DEC-12
1 93 1594008 21-DEC-12 1594011 21-DEC-12
1 94 1594011 21-DEC-12 1594017 21-DEC-12
1 95 1594017 21-DEC-12 1594020 21-DEC-12
1 96 1594020 21-DEC-12 1594023 21-DEC-12
1 97 1594023 21-DEC-12 1594027 21-DEC-12
1 98 1594027 21-DEC-12 1594030 21-DEC-12
1 99 1594030 21-DEC-12 1594034 21-DEC-12
1 100 1594034 21-DEC-12 1594037 21-DEC-12
1 101 1594037 21-DEC-12 1594041 21-DEC-12
1 102 1594041 21-DEC-12 1594047 21-DEC-12
1 103 1594047 21-DEC-12 1594050 21-DEC-12
1 104 1594050 21-DEC-12 1594054 21-DEC-12
1 105 1594054 21-DEC-12 1594057 21-DEC-12
1 106 1594057 21-DEC-12 1594060 21-DEC-12
1 107 1594060 21-DEC-12 1594063 21-DEC-12
1 108 1594063 21-DEC-12 1594067 21-DEC-12
1 109 1594067 21-DEC-12 1594070 21-DEC-12
1 110 1594070 21-DEC-12 1594076 21-DEC-12
1 111 1594076 21-DEC-12 1594079 21-DEC-12
1 112 1594079 21-DEC-12 1594085 21-DEC-12
1 113 1594085 21-DEC-12 1594088 21-DEC-12
1 114 1594088 21-DEC-12 1594094 21-DEC-12
1 115 1594094 21-DEC-12 1594097 21-DEC-12
1 116 1594097 21-DEC-12 1594103 21-DEC-12
1 117 1594103 21-DEC-12 1594106 21-DEC-12
1 118 1594106 21-DEC-12 1594112 21-DEC-12
1 119 1594112 21-DEC-12 1594115 21-DEC-12
1 120 1594115 21-DEC-12 1594121 21-DEC-12
1 121 1594121 21-DEC-12 1594124 21-DEC-12
1 122 1594124 21-DEC-12 1594127 21-DEC-12
1 123 1594127 21-DEC-12 1594130 21-DEC-12
1 124 1594130 21-DEC-12 1594136 21-DEC-12
1 125 1594136 21-DEC-12 1594139 21-DEC-12
1 126 1594139 21-DEC-12 1594145 21-DEC-12
1 127 1594145 21-DEC-12 1594148 21-DEC-12
1 128 1594148 21-DEC-12 1594151 21-DEC-12
1 129 1594151 21-DEC-12 1594154 21-DEC-12
1 130 1594154 21-DEC-12 1594159 21-DEC-12
1 131 1594159 21-DEC-12 1594162 21-DEC-12
1 132 1594162 21-DEC-12 1594168 21-DEC-12
1 133 1594168 21-DEC-12 1594171 21-DEC-12
1 134 1594171 21-DEC-12 1594175 21-DEC-12
1 135 1594175 21-DEC-12 1594178 21-DEC-12
1 136 1594178 21-DEC-12 1594183 21-DEC-12
1 137 1594183 21-DEC-12 1594186 21-DEC-12
1 138 1594186 21-DEC-12 1594276 21-DEC-12
1 139 1594276 21-DEC-12 1594450 21-DEC-12
-- We can see that the backup piece "/optware/oracle/archive_bkp_of_prm_0vnte4b2_1_1" is holding
the archive logs between 84 and 139. The BS (Backup Set) key is 11 for this backup.
Take a disk backup of this backup set.
RMAN> run
2> {
3> allocate channel a1 device type disk format '/optware/oracle/bkp_piece_holding_84and139_%U';
4> backup backupset 11;
5> }
allocated channel: a1
channel a1: SID=38 device type=DISK
Starting backup at 21-DEC-12
channel a1: input backup set: count=31, stamp=802623842, piece=1
channel a1: starting piece 1 at 21-DEC-12
channel a1: backup piece /optware/oracle/archive_bkp_of_prm_0vnte4b2_1_1
piece handle=/optware/oracle/bkp_piece_holding_84and139_0vnte4b2_1_2 comment=NONE
channel a1: finished piece 1 at 21-DEC-12
channel a1: backup piece complete, elapsed time: 00:00:01
Finished backup at 21-DEC-12
released channel: a1
--Now restore the archive log from the backup piece /optware/oracle/bkp_piece_holding_84and139_0vnte4b2_1_2
on the statndby site.
export ORACLE_SID=test12
[oracle@rhel trace]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Dec 21 15:39:13 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: TEST11 (DBID=3487132874, not open)
RMAN> catalog backuppiece '/optware/oracle/bkp_piece_holding_84and139_0vnte4b2_1_2';
using target database control file instead of recovery catalog
cataloged backup piece
backup piece handle=/optware/oracle/bkp_piece_holding_84and139_0vnte4b2_1_2 RECID=5 STAMP=802625984
RMAN> restore archivelog from sequence 84 until sequence 139;
Starting restore at 21-DEC-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=31 device type=DISK
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=84
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=85
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=86
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=87
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=88
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=89
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=90
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=91
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=92
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=93
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=94
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=95
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=96
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=97
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=98
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=99
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=100
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=101
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=102
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=103
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=104
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=105
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=106
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=107
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=108
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=109
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=110
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=111
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=112
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=113
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=114
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=115
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=116
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=117
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=118
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=119
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=120
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=121
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=122
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=123
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=124
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=125
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=126
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=127
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=128
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=129
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=130
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=131
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=132
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=133
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=134
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=135
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=136
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=137
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=138
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=139
channel ORA_DISK_1: reading from backup piece /optware/oracle/bkp_piece_holding_84and139_0vnte4b2_1_2
channel ORA_DISK_1: piece handle=/optware/oracle/bkp_piece_holding_84and139_0vnte4b2_1_2 tag=TAG20121221T150401
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 21-DEC-12
RMAN> exit
Now check the status.
SQL> select * from V$archive_gap;
no rows selected
SQL> select sequence#, archived, applied from v$archived_log where sequence# > 83 order by 1;
SEQUENCE# ARC APPLIED
---------- --- ---------
84 YES YES
85 YES YES
86 YES YES
87 YES YES
88 YES YES
89 YES YES
90 YES YES
91 YES YES
92 YES YES
93 YES YES
94 YES YES
SEQUENCE# ARC APPLIED
---------- --- ---------
95 YES YES
96 YES YES
97 YES YES
98 YES YES
99 YES YES
100 YES YES
101 YES YES
102 YES YES
103 YES YES
104 YES YES
105 YES YES
SEQUENCE# ARC APPLIED
---------- --- ---------
106 YES YES
107 YES YES
108 YES YES
109 YES YES
110 YES YES
111 YES YES
112 YES YES
113 YES YES
114 YES YES
115 YES YES
116 YES YES
SEQUENCE# ARC APPLIED
---------- --- ---------
117 YES YES
118 YES YES
119 YES YES
120 YES YES
121 YES YES
122 YES YES
123 YES YES
124 YES YES
125 YES YES
126 YES YES
127 YES YES
SEQUENCE# ARC APPLIED
---------- --- ---------
128 YES YES
129 YES YES
130 YES YES
131 YES YES
132 YES YES
133 YES YES
134 YES YES
135 YES YES
136 YES YES
137 YES YES
138 YES YES
SEQUENCE# ARC APPLIED
---------- --- ---------
139 YES YES
140 YES YES
141 YES YES
142 YES YES
143 YES IN-MEMORY
60 rows selected.
--Now the primary and the standby is in sync.
Scenario 3 - In this scenario we will see that the archive destination got
filled and the application are not able to connect to the db, aslo the sys user
not able to connect to the sqlplus / rman.
The archivelogs are not deleted because these are not transferred to the standby
site because of some issues. Now we need to remove some archivelogs in the ASM
diskgroup to solve the hang issue, but at the same time we need these archives
to sync the standby database.
The only option left is to copy the missing archive files manually to the standby
site and then remove these files from the diskgroup.
export ORACLE_SID=test11
SQL> alter system set log_archive_dest_state_2 = 'defer' scope=both;
System altered.
SQL> show parameter log_archive_dest_2;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=test12 lgwr async noaf
firm valid_for=(online_logfile
s,primary_role) db_unique_name
=test12
SQL> alter system set log_archive_dest_2='' scope=both;
System altered.
SQL> begin
2 for i in 1..50 loop
3 execute immediate 'alter system switch logfile';
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 199 52428800 512 1 YES INACTIVE 1633805 22-DEC-12 1633808 22-DEC-12
2 1 200 52428800 512 1 YES INACTIVE 1633808 22-DEC-12 1633811 22-DEC-12
3 1 201 52428800 512 1 NO CURRENT 1633811 22-DEC-12 2.8147E+14
export ORACLE_SID=test12
SQL> select sequence#, archived, applied from v$archived_log order by 1;
SEQUENCE# ARC APPLIED
---------- --- ---------
140 YES YES
141 YES YES
142 YES YES
143 YES YES
144 YES YES
145 YES YES
146 YES YES
147 YES YES
148 YES YES
149 YES YES
150 YES YES
SEQUENCE# ARC APPLIED
---------- --- ---------
151 YES YES
-- Here the standby has been appiled upto 151 log sequence, the logs between 152 and 201 are missing .
Now the situation is like we are not able to connect to the primary database either through
sqlplus or through rman beacuse of archive destination fill. If we can able to connect to rman
then we can take the backup of the missing archives and restore on the standby but now the case
is differnet, we have left with the option to copy the archivelogs manually to the destination
site. Lets try out.
[oracle@rhel ~]$ asmcmd
ASMCMD> lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 4096 1048576 4094 1927 0 1927 0 N DATA/
ASMCMD> cd data
ASMCMD> ls -l
Type Redund Striped Time Sys Name
Y ASM/
Y DB_UNKNOWN/
N archive_log/
N test11/
ASMCMD> cd archive_log
ASMCMD> ls
1_150_799248578.dbf
1_151_799248578.dbf
1_152_799248578.dbf
1_153_799248578.dbf
1_154_799248578.dbf
1_155_799248578.dbf
1_156_799248578.dbf
1_157_799248578.dbf
1_158_799248578.dbf
1_159_799248578.dbf
1_160_799248578.dbf
1_161_799248578.dbf
1_162_799248578.dbf
1_163_799248578.dbf
1_164_799248578.dbf
1_165_799248578.dbf
1_166_799248578.dbf
1_167_799248578.dbf
1_168_799248578.dbf
1_169_799248578.dbf
1_170_799248578.dbf
1_171_799248578.dbf
1_172_799248578.dbf
1_173_799248578.dbf
1_174_799248578.dbf
1_175_799248578.dbf
1_176_799248578.dbf
1_177_799248578.dbf
1_178_799248578.dbf
1_179_799248578.dbf
1_180_799248578.dbf
1_181_799248578.dbf
1_182_799248578.dbf
1_183_799248578.dbf
1_184_799248578.dbf
1_185_799248578.dbf
1_186_799248578.dbf
1_187_799248578.dbf
1_188_799248578.dbf
1_189_799248578.dbf
1_190_799248578.dbf
1_191_799248578.dbf
1_192_799248578.dbf
1_193_799248578.dbf
1_194_799248578.dbf
1_195_799248578.dbf
1_196_799248578.dbf
1_197_799248578.dbf
1_198_799248578.dbf
1_199_799248578.dbf
1_200_799248578.dbf
1_201_799248578.dbf
1_202_799248578.dbf
ASMCMD> cp +DATA/archive_log/1_152_799248578.dbf /optware/oracle/orafiles/test12/archive_log
copying +DATA/archive_log/1_152_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_152_799248578.dbf
-- We can write a small script to copy the archive logs.
#!/bin/ksh
#
# For copying ASM files to OS on the same host.
export ORACLE_SID=+ASM
i=153
## model should be 1_$i_799248578.dbf
while [ $i -le 202 ]
do
filetocp="1_"$i"_799248578.dbf"
echo "Now copying file : $filetocp"
src_file="+DATA/archive_log/"$filetocp
asmcmd cp $src_file /optware/oracle/orafiles/test12/archive_log
i=`expr $i + 1`
done
echo "Files are copied"
[oracle@rhel ~]$ chmod 755 bkpfromasmcmd
[oracle@rhel ~]$ ./bkpfromasmcmd
Now copying file : 1_153_799248578.dbf
copying +DATA/archive_log/1_153_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_153_799248578.dbf
Now copying file : 1_154_799248578.dbf
copying +DATA/archive_log/1_154_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_154_799248578.dbf
Now copying file : 1_155_799248578.dbf
copying +DATA/archive_log/1_155_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_155_799248578.dbf
Now copying file : 1_156_799248578.dbf
copying +DATA/archive_log/1_156_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_156_799248578.dbf
Now copying file : 1_157_799248578.dbf
copying +DATA/archive_log/1_157_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_157_799248578.dbf
Now copying file : 1_158_799248578.dbf
copying +DATA/archive_log/1_158_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_158_799248578.dbf
Now copying file : 1_159_799248578.dbf
copying +DATA/archive_log/1_159_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_159_799248578.dbf
Now copying file : 1_160_799248578.dbf
copying +DATA/archive_log/1_160_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_160_799248578.dbf
Now copying file : 1_161_799248578.dbf
copying +DATA/archive_log/1_161_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_161_799248578.dbf
Now copying file : 1_162_799248578.dbf
copying +DATA/archive_log/1_162_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_162_799248578.dbf
Now copying file : 1_163_799248578.dbf
copying +DATA/archive_log/1_163_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_163_799248578.dbf
Now copying file : 1_164_799248578.dbf
copying +DATA/archive_log/1_164_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_164_799248578.dbf
Now copying file : 1_165_799248578.dbf
copying +DATA/archive_log/1_165_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_165_799248578.dbf
Now copying file : 1_166_799248578.dbf
copying +DATA/archive_log/1_166_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_166_799248578.dbf
Now copying file : 1_167_799248578.dbf
copying +DATA/archive_log/1_167_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_167_799248578.dbf
Now copying file : 1_168_799248578.dbf
copying +DATA/archive_log/1_168_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_168_799248578.dbf
Now copying file : 1_169_799248578.dbf
copying +DATA/archive_log/1_169_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_169_799248578.dbf
Now copying file : 1_170_799248578.dbf
copying +DATA/archive_log/1_170_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_170_799248578.dbf
Now copying file : 1_171_799248578.dbf
copying +DATA/archive_log/1_171_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_171_799248578.dbf
Now copying file : 1_172_799248578.dbf
copying +DATA/archive_log/1_172_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_172_799248578.dbf
Now copying file : 1_173_799248578.dbf
copying +DATA/archive_log/1_173_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_173_799248578.dbf
Now copying file : 1_174_799248578.dbf
copying +DATA/archive_log/1_174_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_174_799248578.dbf
Now copying file : 1_175_799248578.dbf
copying +DATA/archive_log/1_175_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_175_799248578.dbf
Now copying file : 1_176_799248578.dbf
copying +DATA/archive_log/1_176_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_176_799248578.dbf
Now copying file : 1_177_799248578.dbf
copying +DATA/archive_log/1_177_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_177_799248578.dbf
Now copying file : 1_178_799248578.dbf
copying +DATA/archive_log/1_178_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_178_799248578.dbf
Now copying file : 1_179_799248578.dbf
copying +DATA/archive_log/1_179_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_179_799248578.dbf
Now copying file : 1_180_799248578.dbf
copying +DATA/archive_log/1_180_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_180_799248578.dbf
Now copying file : 1_181_799248578.dbf
copying +DATA/archive_log/1_181_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_181_799248578.dbf
Now copying file : 1_182_799248578.dbf
copying +DATA/archive_log/1_182_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_182_799248578.dbf
Now copying file : 1_183_799248578.dbf
copying +DATA/archive_log/1_183_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_183_799248578.dbf
Now copying file : 1_184_799248578.dbf
copying +DATA/archive_log/1_184_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_184_799248578.dbf
Now copying file : 1_185_799248578.dbf
copying +DATA/archive_log/1_185_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_185_799248578.dbf
Now copying file : 1_186_799248578.dbf
copying +DATA/archive_log/1_186_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_186_799248578.dbf
Now copying file : 1_187_799248578.dbf
copying +DATA/archive_log/1_187_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_187_799248578.dbf
Now copying file : 1_188_799248578.dbf
copying +DATA/archive_log/1_188_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_188_799248578.dbf
Now copying file : 1_189_799248578.dbf
copying +DATA/archive_log/1_189_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_189_799248578.dbf
Now copying file : 1_190_799248578.dbf
copying +DATA/archive_log/1_190_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_190_799248578.dbf
Now copying file : 1_191_799248578.dbf
copying +DATA/archive_log/1_191_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_191_799248578.dbf
Now copying file : 1_192_799248578.dbf
copying +DATA/archive_log/1_192_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_192_799248578.dbf
Now copying file : 1_193_799248578.dbf
copying +DATA/archive_log/1_193_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_193_799248578.dbf
Now copying file : 1_194_799248578.dbf
copying +DATA/archive_log/1_194_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_194_799248578.dbf
Now copying file : 1_195_799248578.dbf
copying +DATA/archive_log/1_195_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_195_799248578.dbf
Now copying file : 1_196_799248578.dbf
copying +DATA/archive_log/1_196_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_196_799248578.dbf
Now copying file : 1_197_799248578.dbf
copying +DATA/archive_log/1_197_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_197_799248578.dbf
Now copying file : 1_198_799248578.dbf
copying +DATA/archive_log/1_198_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_198_799248578.dbf
Now copying file : 1_199_799248578.dbf
copying +DATA/archive_log/1_199_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_199_799248578.dbf
Now copying file : 1_200_799248578.dbf
copying +DATA/archive_log/1_200_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_200_799248578.dbf
Now copying file : 1_201_799248578.dbf
copying +DATA/archive_log/1_201_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_201_799248578.dbf
Now copying file : 1_202_799248578.dbf
copying +DATA/archive_log/1_202_799248578.dbf -> /optware/oracle/orafiles/test12/archive_log/1_202_799248578.dbf
Files are copied
--Now all the missing archive logs are copied to the standby site, we can now remove the logs from 53 to 202
from the primary db.
We can modify the script which we used earlier to copy the files as below.
#!/bin/ksh
#
# For removing files from ASM.
export ORACLE_SID=+ASM
i=153
## model should be 1_$i_799248578.dbf
while [ $i -le 202 ]
do
filetocp="1_"$i"_799248578.dbf"
echo "Now removing file : $filetocp"
src_file="+DATA/archive_log/"$filetocp
asmcmd rm -f $src_file
i=`expr $i + 1`
done
echo "Files are removed"
[oracle@rhel ~]$ ./bkpfromasmcmd
Now removing file : 1_153_799248578.dbf
Now removing file : 1_154_799248578.dbf
Now removing file : 1_155_799248578.dbf
Now removing file : 1_156_799248578.dbf
Now removing file : 1_157_799248578.dbf
Now removing file : 1_158_799248578.dbf
Now removing file : 1_159_799248578.dbf
Now removing file : 1_160_799248578.dbf
Now removing file : 1_161_799248578.dbf
Now removing file : 1_162_799248578.dbf
Now removing file : 1_163_799248578.dbf
Now removing file : 1_164_799248578.dbf
Now removing file : 1_165_799248578.dbf
Now removing file : 1_166_799248578.dbf
Now removing file : 1_167_799248578.dbf
Now removing file : 1_168_799248578.dbf
Now removing file : 1_169_799248578.dbf
Now removing file : 1_170_799248578.dbf
Now removing file : 1_171_799248578.dbf
Now removing file : 1_172_799248578.dbf
Now removing file : 1_173_799248578.dbf
Now removing file : 1_174_799248578.dbf
Now removing file : 1_175_799248578.dbf
Now removing file : 1_176_799248578.dbf
Now removing file : 1_177_799248578.dbf
Now removing file : 1_178_799248578.dbf
Now removing file : 1_179_799248578.dbf
Now removing file : 1_180_799248578.dbf
Now removing file : 1_181_799248578.dbf
Now removing file : 1_182_799248578.dbf
Now removing file : 1_183_799248578.dbf
Now removing file : 1_184_799248578.dbf
Now removing file : 1_185_799248578.dbf
Now removing file : 1_186_799248578.dbf
Now removing file : 1_187_799248578.dbf
Now removing file : 1_188_799248578.dbf
Now removing file : 1_189_799248578.dbf
Now removing file : 1_190_799248578.dbf
Now removing file : 1_191_799248578.dbf
Now removing file : 1_192_799248578.dbf
Now removing file : 1_193_799248578.dbf
Now removing file : 1_194_799248578.dbf
Now removing file : 1_195_799248578.dbf
Now removing file : 1_196_799248578.dbf
Now removing file : 1_197_799248578.dbf
Now removing file : 1_198_799248578.dbf
Now removing file : 1_199_799248578.dbf
Now removing file : 1_200_799248578.dbf
Now removing file : 1_201_799248578.dbf
Now removing file : 1_202_799248578.dbf
Files are removed.
-- Now we are able to connect to the primary db instance.
SQL> alter system set log_archive_dest_state_2 = 'enable' scope=both;
System altered.
--Now lets see whether the standby database is sync now.
export ORACLE_SID=test12
SQL> select sequence#, archived, applied from v$archived_log order by 1;
SEQUENCE# ARC APPLIED
---------- --- ---------
151 YES YES
152 YES YES
202 YES NO
203 YES NO
204 YES NO
-- Even after the archived logs are copied still the MRP process is waiting for the gap.
SQL> SELECT PROCESS , STATUS FROM v$MANAGED_STANDBY;
PROCESS STATUS
--------- ------------
ARCH CLOSING
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
RFS IDLE
RFS IDLE
MRP0 WAIT_FOR_GAP
7 rows selected.
-- Snap from alertlog file.
============================================================================================================
Media Recovery Waiting for thread 1 sequence 153
Fetching gap sequence in thread 1, gap sequence 153-201
Completed: alter database recover managed standby database using current logfile disconnect from session
Sat Dec 22 10:58:24 2012
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 153-201
DBID 3487132874 branch 799248578
FAL[client]: All defined FAL servers have been attempted.
============================================================================================================
-- The gap is not filled up even though the file is placed, hence now we need to manually register
these logfiles.
We can use the below script to register in the db.
#!/bin/ksh
#
#
export ORACLE_SID=test12
export ORACLE_HOME=$ORACLE_HOME
i=153
## model should be 1_$i_799248578.dbf
while [ $i -le 202 ]
do
filetoregister="1_"$i"_799248578.dbf"
echo "Now registering file : "$filetoregister
src_file="'/optware/oracle/orafiles/test12/archive_log/"$filetoregister"'"
echo "alter database register logfile " $src_file
sqlplus -s "/ as sysdba"<
EOF
i=`expr $i + 1`
done
echo "Files are registered"
[oracle@rhel ~]$ ./bkpfromasmcmd
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_155_799248578.dbf'
Database altered.
Now registering file : 1_156_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_156_799248578.dbf'
Database altered.
Now registering file : 1_157_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_157_799248578.dbf'
Database altered.
Now registering file : 1_158_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_158_799248578.dbf'
Database altered.
Now registering file : 1_159_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_159_799248578.dbf'
Database altered.
Now registering file : 1_160_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_160_799248578.dbf'
Database altered.
Now registering file : 1_161_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_161_799248578.dbf'
Database altered.
Now registering file : 1_162_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_162_799248578.dbf'
Database altered.
Now registering file : 1_163_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_163_799248578.dbf'
Database altered.
Now registering file : 1_164_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_164_799248578.dbf'
Database altered.
Now registering file : 1_165_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_165_799248578.dbf'
Database altered.
Now registering file : 1_166_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_166_799248578.dbf'
Database altered.
Now registering file : 1_167_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_167_799248578.dbf'
Database altered.
Now registering file : 1_168_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_168_799248578.dbf'
Database altered.
Now registering file : 1_169_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_169_799248578.dbf'
Database altered.
Now registering file : 1_170_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_170_799248578.dbf'
Database altered.
Now registering file : 1_171_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_171_799248578.dbf'
Database altered.
Now registering file : 1_172_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_172_799248578.dbf'
Database altered.
Now registering file : 1_173_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_173_799248578.dbf'
Database altered.
Now registering file : 1_174_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_174_799248578.dbf'
Database altered.
Now registering file : 1_175_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_175_799248578.dbf'
Database altered.
Now registering file : 1_176_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_176_799248578.dbf'
Database altered.
Now registering file : 1_177_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_177_799248578.dbf'
Database altered.
Now registering file : 1_178_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_178_799248578.dbf'
Database altered.
Now registering file : 1_179_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_179_799248578.dbf'
Database altered.
Now registering file : 1_180_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_180_799248578.dbf'
Database altered.
Now registering file : 1_181_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_181_799248578.dbf'
Database altered.
Now registering file : 1_182_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_182_799248578.dbf'
Database altered.
Now registering file : 1_183_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_183_799248578.dbf'
Database altered.
Now registering file : 1_184_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_184_799248578.dbf'
Database altered.
Now registering file : 1_185_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_185_799248578.dbf'
Database altered.
Now registering file : 1_186_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_186_799248578.dbf'
Database altered.
Now registering file : 1_187_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_187_799248578.dbf'
Database altered.
Now registering file : 1_188_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_188_799248578.dbf'
Database altered.
Now registering file : 1_189_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_189_799248578.dbf'
Database altered.
Now registering file : 1_190_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_190_799248578.dbf'
Database altered.
Now registering file : 1_191_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_191_799248578.dbf'
Database altered.
Now registering file : 1_192_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_192_799248578.dbf'
Database altered.
Now registering file : 1_193_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_193_799248578.dbf'
Database altered.
Now registering file : 1_194_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_194_799248578.dbf'
Database altered.
Now registering file : 1_195_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_195_799248578.dbf'
Database altered.
Now registering file : 1_196_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_196_799248578.dbf'
Database altered.
Now registering file : 1_197_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_197_799248578.dbf'
Database altered.
Now registering file : 1_198_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_198_799248578.dbf'
Database altered.
Now registering file : 1_199_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_199_799248578.dbf'
Database altered.
Now registering file : 1_200_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_200_799248578.dbf'
Database altered.
Now registering file : 1_201_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_201_799248578.dbf'
Database altered.
Now registering file : 1_202_799248578.dbf
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_202_799248578.dbf'
alter database register logfile '/optware/oracle/orafiles/test12/archive_log/1_202_799248578.dbf'
*
ERROR at line 1:
ORA-16089: archive log has already been registered
Files are registered
-- Lets check now the standby database.
SQL> SELECT PROCESS , STATUS FROM v$MANAGED_STANDBY;
PROCESS STATUS
--------- ------------
ARCH CLOSING
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
RFS IDLE
RFS IDLE
MRP0 APPLYING_LOG
7 rows selected.
SQL> select sequence#, archived, applied from V$archived_log order by 1
SEQUENCE# ARC APPLIED
---------- --- ---------
184 YES YES
185 YES YES
186 YES YES
187 YES YES
188 YES YES
189 YES YES
190 YES YES
191 YES YES
192 YES YES
193 YES YES
194 YES YES
SEQUENCE# ARC APPLIED
---------- --- ---------
195 YES YES
196 YES YES
197 YES YES
198 YES YES
199 YES YES
200 YES YES
201 YES YES
202 YES YES
203 YES YES
204 YES IN-MEMORY
--Now both the primary and the standby db are in sync.
--Take the backup of the db, since we have deleted some archives.
Secnario 4 - The prirmary database has been opened with resetlogs option,
if the standby datbase is running in flash back mode then we can flashback
the standby database to sync with the primary if the flashback is not enabled
then we need to re-create the standby database. In this scenario the flashback
has been enabled in the standby database.
export ORACLE_SID=test11
SQL> select * from V$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 145 10485760 512 1 NO CURRENT 1716767 23-DEC-12 2.8147E+14
2 1 143 10485760 512 1 YES INACTIVE 1716758 23-DEC-12 1716763 23-DEC-12
3 1 144 52428800 512 1 YES INACTIVE 1716763 23-DEC-12 1716767 23-DEC-12
export ORACLE_SID=test12
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 145 10485760 512 1 YES CLEARING_CURRENT 1716767 23-DEC-12 2.8147E+14
3 1 141 52428800 512 1 YES CLEARING 1716749 23-DEC-12 2.8147E+14
2 1 143 10485760 512 1 YES CLEARING 1716758 23-DEC-12 2.8147E+14
SQL> select sequence#, archived, applied from v$archived_log;
SEQUENCE# ARC APPLIED
---------- --- ---------
137 YES YES
138 YES YES
139 YES YES
140 YES YES
141 YES YES
142 YES YES
143 YES YES
144 YES IN-MEMORY
-- both the database in sync.
-- Lets do an incomplete recover on the priarmy database (We have already taken a full db backup).
export ORACLE_SID=test11
RMAN> list backup;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
14 7.72M DISK 00:00:03 23-DEC-12
BP Key: 16 Status: AVAILABLE Compressed: YES Tag: TAG20121223T181306
Piece Name: /optware/oracle/full_bkp_of_prm_1gntjo5j_1_1
List of Archived Logs in backup set 14
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 1 1651603 22-DEC-12 1651606 22-DEC-12
1 2 1651606 22-DEC-12 1651611 22-DEC-12
1 3 1651611 22-DEC-12 1677285 23-DEC-12
1 4 1677285 23-DEC-12 1677483 23-DEC-12
1 5 1677483 23-DEC-12 1686725 23-DEC-12
1 6 1686725 23-DEC-12 1686796 23-DEC-12
1 7 1686796 23-DEC-12 1686803 23-DEC-12
1 8 1686803 23-DEC-12 1686908 23-DEC-12
1 9 1686908 23-DEC-12 1688039 23-DEC-12
1 10 1688039 23-DEC-12 1688043 23-DEC-12
1 11 1688043 23-DEC-12 1688047 23-DEC-12
1 12 1688047 23-DEC-12 1689100 23-DEC-12
1 13 1689100 23-DEC-12 1689104 23-DEC-12
1 14 1689104 23-DEC-12 1689110 23-DEC-12
1 15 1689110 23-DEC-12 1689113 23-DEC-12
1 16 1689113 23-DEC-12 1710362 23-DEC-12
1 17 1710362 23-DEC-12 1713361 23-DEC-12
1 18 1713361 23-DEC-12 1713462 23-DEC-12
1 19 1713462 23-DEC-12 1713468 23-DEC-12
1 20 1713468 23-DEC-12 1713525 23-DEC-12
1 21 1713525 23-DEC-12 1713531 23-DEC-12
1 22 1713531 23-DEC-12 1713573 23-DEC-12
1 23 1713573 23-DEC-12 1713576 23-DEC-12
1 24 1713576 23-DEC-12 1713581 23-DEC-12
1 25 1713581 23-DEC-12 1713586 23-DEC-12
1 26 1713586 23-DEC-12 1713884 23-DEC-12
1 27 1713884 23-DEC-12 1714447 23-DEC-12
1 28 1714447 23-DEC-12 1714466 23-DEC-12
1 29 1714466 23-DEC-12 1714494 23-DEC-12
1 30 1714494 23-DEC-12 1714509 23-DEC-12
1 31 1714509 23-DEC-12 1714597 23-DEC-12
1 32 1714597 23-DEC-12 1714865 23-DEC-12
1 33 1714865 23-DEC-12 1715124 23-DEC-12
1 34 1715124 23-DEC-12 1715197 23-DEC-12
1 35 1715197 23-DEC-12 1715398 23-DEC-12
1 36 1715398 23-DEC-12 1715402 23-DEC-12
1 37 1715402 23-DEC-12 1715407 23-DEC-12
1 38 1715407 23-DEC-12 1715488 23-DEC-12
1 39 1715488 23-DEC-12 1715539 23-DEC-12
1 40 1715539 23-DEC-12 1715594 23-DEC-12
1 41 1715594 23-DEC-12 1715597 23-DEC-12
1 42 1715597 23-DEC-12 1716011 23-DEC-12
1 43 1716011 23-DEC-12 1716142 23-DEC-12
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
15 Full 269.06M DISK 00:00:53 23-DEC-12
BP Key: 17 Status: AVAILABLE Compressed: YES Tag: TAG20121223T181313
Piece Name: /optware/oracle/full_bkp_of_prm_1hntjo5p_1_1
List of Datafiles in backup set 15
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1716157 23-DEC-12 +DATA/test11/system01.dbf
2 Full 1716157 23-DEC-12 +DATA/test11/sysaux01.dbf
3 Full 1716157 23-DEC-12 +DATA/test11/undotbs01.dbf
4 Full 1716157 23-DEC-12 +DATA/test11/users01.dbf
5 Full 1716157 23-DEC-12 +DATA/test11/fda_ts01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
16 Full 1.08M DISK 00:00:04 23-DEC-12
BP Key: 18 Status: AVAILABLE Compressed: YES Tag: TAG20121223T181313
Piece Name: /optware/oracle/full_bkp_of_prm_1intjo7g_1_1
SPFILE Included: Modification time: 23-DEC-12
SPFILE db_unique_name: TEST11
Control File Included: Ckp SCN: 1716210 Ckp time: 23-DEC-12
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
17 39.50K DISK 00:00:00 23-DEC-12
BP Key: 19 Status: AVAILABLE Compressed: YES Tag: TAG20121223T181413
Piece Name: /optware/oracle/full_bkp_of_prm_1jntjo7l_1_1
List of Archived Logs in backup set 17
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 44 1716142 23-DEC-12 1716219 23-DEC-12
-- The current logsequence is 145, we will do a restore upto log sequence 100.
-- shutdown the database and startup in mount state.
RMAN> run
2> {
3> set until sequence 100;
4> restore database;
5> recover database;
6> alter database open resetlogs;
7> }
executing command: SET until clause
Starting restore at 23-DEC-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK
flashing back control file to SCN 1716581
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/test11/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to +DATA/test11/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to +DATA/test11/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to +DATA/test11/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to +DATA/test11/fda_ts01.dbf
channel ORA_DISK_1: reading from backup piece /optware/oracle/full_bkp_of_prm_1hntjo5p_1_1
channel ORA_DISK_1: piece handle=/optware/oracle/full_bkp_of_prm_1hntjo5p_1_1 tag=TAG20121223T181313
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 23-DEC-12
Starting recover at 23-DEC-12
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 45 is already on disk as file +DATA/archive_log/1_45_802704822.dbf
archived log for thread 1 with sequence 46 is already on disk as file +DATA/archive_log/1_46_802704822.dbf
archived log for thread 1 with sequence 47 is already on disk as file +DATA/archive_log/1_47_802704822.dbf
archived log for thread 1 with sequence 48 is already on disk as file +DATA/archive_log/1_48_802704822.dbf
archived log for thread 1 with sequence 49 is already on disk as file +DATA/archive_log/1_49_802704822.dbf
archived log for thread 1 with sequence 50 is already on disk as file +DATA/archive_log/1_50_802704822.dbf
archived log for thread 1 with sequence 51 is already on disk as file +DATA/archive_log/1_51_802704822.dbf
archived log for thread 1 with sequence 52 is already on disk as file +DATA/archive_log/1_52_802704822.dbf
archived log for thread 1 with sequence 53 is already on disk as file +DATA/archive_log/1_53_802704822.dbf
archived log for thread 1 with sequence 54 is already on disk as file +DATA/archive_log/1_54_802704822.dbf
archived log for thread 1 with sequence 55 is already on disk as file +DATA/archive_log/1_55_802704822.dbf
archived log for thread 1 with sequence 56 is already on disk as file +DATA/archive_log/1_56_802704822.dbf
archived log for thread 1 with sequence 57 is already on disk as file +DATA/archive_log/1_57_802704822.dbf
archived log for thread 1 with sequence 58 is already on disk as file +DATA/archive_log/1_58_802704822.dbf
archived log for thread 1 with sequence 59 is already on disk as file +DATA/archive_log/1_59_802704822.dbf
archived log for thread 1 with sequence 60 is already on disk as file +DATA/archive_log/1_60_802704822.dbf
archived log for thread 1 with sequence 61 is already on disk as file +DATA/archive_log/1_61_802704822.dbf
archived log for thread 1 with sequence 62 is already on disk as file +DATA/archive_log/1_62_802704822.dbf
archived log for thread 1 with sequence 63 is already on disk as file +DATA/archive_log/1_63_802704822.dbf
archived log for thread 1 with sequence 64 is already on disk as file +DATA/archive_log/1_64_802704822.dbf
archived log for thread 1 with sequence 65 is already on disk as file +DATA/archive_log/1_65_802704822.dbf
archived log for thread 1 with sequence 66 is already on disk as file +DATA/archive_log/1_66_802704822.dbf
archived log for thread 1 with sequence 67 is already on disk as file +DATA/archive_log/1_67_802704822.dbf
archived log for thread 1 with sequence 68 is already on disk as file +DATA/archive_log/1_68_802704822.dbf
archived log for thread 1 with sequence 69 is already on disk as file +DATA/archive_log/1_69_802704822.dbf
archived log for thread 1 with sequence 70 is already on disk as file +DATA/archive_log/1_70_802704822.dbf
archived log for thread 1 with sequence 71 is already on disk as file +DATA/archive_log/1_71_802704822.dbf
archived log for thread 1 with sequence 72 is already on disk as file +DATA/archive_log/1_72_802704822.dbf
archived log for thread 1 with sequence 73 is already on disk as file +DATA/archive_log/1_73_802704822.dbf
archived log for thread 1 with sequence 74 is already on disk as file +DATA/archive_log/1_74_802704822.dbf
archived log for thread 1 with sequence 75 is already on disk as file +DATA/archive_log/1_75_802704822.dbf
archived log for thread 1 with sequence 76 is already on disk as file +DATA/archive_log/1_76_802704822.dbf
archived log for thread 1 with sequence 77 is already on disk as file +DATA/archive_log/1_77_802704822.dbf
archived log for thread 1 with sequence 78 is already on disk as file +DATA/archive_log/1_78_802704822.dbf
archived log for thread 1 with sequence 79 is already on disk as file +DATA/archive_log/1_79_802704822.dbf
archived log for thread 1 with sequence 80 is already on disk as file +DATA/archive_log/1_80_802704822.dbf
archived log for thread 1 with sequence 81 is already on disk as file +DATA/archive_log/1_81_802704822.dbf
archived log for thread 1 with sequence 82 is already on disk as file +DATA/archive_log/1_82_802704822.dbf
archived log for thread 1 with sequence 83 is already on disk as file +DATA/archive_log/1_83_802704822.dbf
archived log for thread 1 with sequence 84 is already on disk as file +DATA/archive_log/1_84_802704822.dbf
archived log for thread 1 with sequence 85 is already on disk as file +DATA/archive_log/1_85_802704822.dbf
archived log for thread 1 with sequence 86 is already on disk as file +DATA/archive_log/1_86_802704822.dbf
archived log for thread 1 with sequence 87 is already on disk as file +DATA/archive_log/1_87_802704822.dbf
archived log for thread 1 with sequence 88 is already on disk as file +DATA/archive_log/1_88_802704822.dbf
archived log for thread 1 with sequence 89 is already on disk as file +DATA/archive_log/1_89_802704822.dbf
archived log for thread 1 with sequence 90 is already on disk as file +DATA/archive_log/1_90_802704822.dbf
archived log for thread 1 with sequence 91 is already on disk as file +DATA/archive_log/1_91_802704822.dbf
archived log for thread 1 with sequence 92 is already on disk as file +DATA/archive_log/1_92_802704822.dbf
archived log for thread 1 with sequence 93 is already on disk as file +DATA/archive_log/1_93_802704822.dbf
archived log for thread 1 with sequence 94 is already on disk as file +DATA/archive_log/1_94_802704822.dbf
archived log for thread 1 with sequence 95 is already on disk as file +DATA/archive_log/1_95_802704822.dbf
archived log for thread 1 with sequence 96 is already on disk as file +DATA/archive_log/1_96_802704822.dbf
archived log for thread 1 with sequence 97 is already on disk as file +DATA/archive_log/1_97_802704822.dbf
archived log for thread 1 with sequence 98 is already on disk as file +DATA/archive_log/1_98_802704822.dbf
archived log for thread 1 with sequence 99 is already on disk as file +DATA/archive_log/1_99_802704822.dbf
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=38
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=39
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=40
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=41
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=42
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=43
channel ORA_DISK_1: reading from backup piece /optware/oracle/full_bkp_of_prm_1gntjo5j_1_1
channel ORA_DISK_1: piece handle=/optware/oracle/full_bkp_of_prm_1gntjo5j_1_1 tag=TAG20121223T181306
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=+DATA/archive_log/1_38_802704822.dbf thread=1 sequence=38
archived log file name=+DATA/archive_log/1_39_802704822.dbf thread=1 sequence=39
archived log file name=+DATA/archive_log/1_40_802704822.dbf thread=1 sequence=40
archived log file name=+DATA/archive_log/1_41_802704822.dbf thread=1 sequence=41
archived log file name=+DATA/archive_log/1_42_802704822.dbf thread=1 sequence=42
archived log file name=+DATA/archive_log/1_43_802704822.dbf thread=1 sequence=43
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=44
channel ORA_DISK_1: reading from backup piece /optware/oracle/full_bkp_of_prm_1jntjo7l_1_1
channel ORA_DISK_1: piece handle=/optware/oracle/full_bkp_of_prm_1jntjo7l_1_1 tag=TAG20121223T181413
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=+DATA/archive_log/1_44_802704822.dbf thread=1 sequence=44
archived log file name=+DATA/archive_log/1_45_802704822.dbf thread=1 sequence=45
archived log file name=+DATA/archive_log/1_46_802704822.dbf thread=1 sequence=46
archived log file name=+DATA/archive_log/1_47_802704822.dbf thread=1 sequence=47
archived log file name=+DATA/archive_log/1_48_802704822.dbf thread=1 sequence=48
archived log file name=+DATA/archive_log/1_49_802704822.dbf thread=1 sequence=49
archived log file name=+DATA/archive_log/1_50_802704822.dbf thread=1 sequence=50
archived log file name=+DATA/archive_log/1_51_802704822.dbf thread=1 sequence=51
archived log file name=+DATA/archive_log/1_52_802704822.dbf thread=1 sequence=52
archived log file name=+DATA/archive_log/1_53_802704822.dbf thread=1 sequence=53
archived log file name=+DATA/archive_log/1_54_802704822.dbf thread=1 sequence=54
archived log file name=+DATA/archive_log/1_55_802704822.dbf thread=1 sequence=55
archived log file name=+DATA/archive_log/1_56_802704822.dbf thread=1 sequence=56
archived log file name=+DATA/archive_log/1_57_802704822.dbf thread=1 sequence=57
archived log file name=+DATA/archive_log/1_58_802704822.dbf thread=1 sequence=58
archived log file name=+DATA/archive_log/1_59_802704822.dbf thread=1 sequence=59
archived log file name=+DATA/archive_log/1_60_802704822.dbf thread=1 sequence=60
archived log file name=+DATA/archive_log/1_61_802704822.dbf thread=1 sequence=61
archived log file name=+DATA/archive_log/1_62_802704822.dbf thread=1 sequence=62
archived log file name=+DATA/archive_log/1_63_802704822.dbf thread=1 sequence=63
archived log file name=+DATA/archive_log/1_64_802704822.dbf thread=1 sequence=64
archived log file name=+DATA/archive_log/1_65_802704822.dbf thread=1 sequence=65
archived log file name=+DATA/archive_log/1_66_802704822.dbf thread=1 sequence=66
archived log file name=+DATA/archive_log/1_67_802704822.dbf thread=1 sequence=67
archived log file name=+DATA/archive_log/1_68_802704822.dbf thread=1 sequence=68
archived log file name=+DATA/archive_log/1_69_802704822.dbf thread=1 sequence=69
archived log file name=+DATA/archive_log/1_70_802704822.dbf thread=1 sequence=70
archived log file name=+DATA/archive_log/1_71_802704822.dbf thread=1 sequence=71
archived log file name=+DATA/archive_log/1_72_802704822.dbf thread=1 sequence=72
archived log file name=+DATA/archive_log/1_73_802704822.dbf thread=1 sequence=73
archived log file name=+DATA/archive_log/1_74_802704822.dbf thread=1 sequence=74
archived log file name=+DATA/archive_log/1_75_802704822.dbf thread=1 sequence=75
archived log file name=+DATA/archive_log/1_76_802704822.dbf thread=1 sequence=76
archived log file name=+DATA/archive_log/1_77_802704822.dbf thread=1 sequence=77
archived log file name=+DATA/archive_log/1_78_802704822.dbf thread=1 sequence=78
archived log file name=+DATA/archive_log/1_79_802704822.dbf thread=1 sequence=79
archived log file name=+DATA/archive_log/1_80_802704822.dbf thread=1 sequence=80
archived log file name=+DATA/archive_log/1_81_802704822.dbf thread=1 sequence=81
archived log file name=+DATA/archive_log/1_82_802704822.dbf thread=1 sequence=82
archived log file name=+DATA/archive_log/1_83_802704822.dbf thread=1 sequence=83
archived log file name=+DATA/archive_log/1_84_802704822.dbf thread=1 sequence=84
archived log file name=+DATA/archive_log/1_85_802704822.dbf thread=1 sequence=85
archived log file name=+DATA/archive_log/1_86_802704822.dbf thread=1 sequence=86
archived log file name=+DATA/archive_log/1_87_802704822.dbf thread=1 sequence=87
archived log file name=+DATA/archive_log/1_88_802704822.dbf thread=1 sequence=88
archived log file name=+DATA/archive_log/1_89_802704822.dbf thread=1 sequence=89
archived log file name=+DATA/archive_log/1_90_802704822.dbf thread=1 sequence=90
archived log file name=+DATA/archive_log/1_91_802704822.dbf thread=1 sequence=91
archived log file name=+DATA/archive_log/1_92_802704822.dbf thread=1 sequence=92
archived log file name=+DATA/archive_log/1_93_802704822.dbf thread=1 sequence=93
archived log file name=+DATA/archive_log/1_94_802704822.dbf thread=1 sequence=94
archived log file name=+DATA/archive_log/1_95_802704822.dbf thread=1 sequence=95
archived log file name=+DATA/archive_log/1_96_802704822.dbf thread=1 sequence=96
archived log file name=+DATA/archive_log/1_97_802704822.dbf thread=1 sequence=97
archived log file name=+DATA/archive_log/1_98_802704822.dbf thread=1 sequence=98
archived log file name=+DATA/archive_log/1_99_802704822.dbf thread=1 sequence=99
media recovery complete, elapsed time: 00:00:03
Finished recover at 23-DEC-12
database opened
-- Now the primary database has been opened with resetlogs option, now lets see
the log sequence in both.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 1 10485760 512 1 YES INACTIVE 1716582 23-DEC-12 1716585 23-DEC-12
2 1 2 10485760 512 1 YES ACTIVE 1716585 23-DEC-12 1716661 23-DEC-12
3 1 3 52428800 512 1 NO CURRENT 1716661 23-DEC-12 2.8147E+14
export ORACLE_SID=test12
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 145 10485760 512 1 YES CLEARING_CURRENT 1716767 23-DEC-12 2.8147E+14
3 1 141 52428800 512 1 YES CLEARING 1716749 23-DEC-12 2.8147E+14
2 1 143 10485760 512 1 YES CLEARING 1716758 23-DEC-12 2.8147E+14
SQL> select sequence#, archived, applied from V$archived_log;
SEQUENCE# ARC APPLIED
---------- --- ---------
139 YES YES
140 YES YES
141 YES YES
142 YES YES
143 YES YES
144 YES YES
2 YES NO
1 YES NO
3 YES NO
export ORACLE_SID=test11
SQL> select RESETLOGS_CHANGE# -2 from V$database;
RESETLOGS_CHANGE#-2
-------------------
1716580
export ORACLE_SID=test12
SQL> select current_scn from V$database;
CURRENT_SCN
-----------
1717607
--Now the standby database scn is higher, flash back to the primary db scn.
SQL> flashback database to scn 1716580;
Flashback complete.
SQL> select process, status from V$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CLOSING
ARCH CLOSING
ARCH CONNECTED
ARCH CLOSING
RFS IDLE
RFS IDLE
RFS IDLE
RFS IDLE
8 rows selected.
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
SQL> select sequence#, archived, applied from v$archived_log;
SEQUENCE# ARC APPLIED
---------- --- ---------
21 YES YES
22 YES YES
23 YES YES
24 YES YES
26 YES YES
25 YES YES
27 YES YES
28 YES YES
29 YES YES
30 YES YES
31 YES YES
SEQUENCE# ARC APPLIED
---------- --- ---------
32 YES YES
33 YES IN-MEMORY
-- Do some log switch on the primary and check whether it is sync.
export ORACLE_SID=test11
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 37 10485760 512 1 NO CURRENT 1719004 23-DEC-12 2.8147E+14
2 1 35 10485760 512 1 YES INACTIVE 1718995 23-DEC-12 1718999 23-DEC-12
3 1 36 52428800 512 1 YES INACTIVE 1718999 23-DEC-12 1719004 23-DEC-12
export ORACLE_SID=test12
SQL> select sequence#, archived, applied from v$archived_log;
SEQUENCE# ARC APPLIED
---------- --- ---------
32 YES YES
33 YES YES
34 YES YES
35 YES YES
36 YES IN-MEMORY
-- Now both the primary and the standby are in sync.
