Sunday, December 23, 2012

Creating Standby database with different filesystem


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"<alter database register logfile $src_file;
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.