Stop and Start Log Shipping for an Oracle Standby Database

How to stop and start the Log shipping? With this procedure, we can simulate a disaster crash at the main site.

For our example, we use PRD and PRD-STBY to differentiate the two sites, and SAP is installed in Windows.

1 - Stop the log shipping at the PRD Site

C:\>sqlplus / as sysdba

*** Do Some validations Infos
SQL> select name,open_mode from v$database;
PRD       READ WRITE

SQL> select max(sequence#) from v$log_history;
         54275

SQL> alter system switch logfile;

SQL> select max(sequence#) from v$log_history;
         54276

SQL> select status, DEST_NAME, DESTINATION from v$archive_dest where status = 'VALID';
VALID      LOG_ARCHIVE_DEST_1    E:\oracle\PRD\saparch
VALID      LOG_ARCHIVE_DEST_2    prd_standby

SQL> show parameter LOG_ARCHIVE_DEST_2;
log_archive_dest_2     string      Service=prd_standby lgwr async
                                    VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
                                    db_unique_name=standby
SQL>

NOW DEACTIVATE THE LOG SHIPPING!!!!


SQL> alter system set log_archive_dest_state_2=defer scope=both;
System altered.

SQL> show parameter log_archive_dest_state_2
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
log_archive_dest_state_2             string      DEFER

SQL> select max(sequence#) from v$log_history;
         54276

SQL> alter system switch logfile;

SQL> select max(sequence#) from v$log_history;
         24277

You can check the Alert Log of the DR Side, you will see which last is applied and see Network Error

2 - Login to the PRD-STBY Site

C:\> NOTEPAD E:\oracle\PRD\saptrace\background\alert_PRD.log

RFS[2]: Archived Log: 'E:\ORACLE\PRD\SAPARCH\PRD_54276.1.834712720.DBF'
Primary database is in MAXIMUM PERFORMANCE mode
Sun Jun 19 12:33:32 2013
Media Recovery Log E:\ORACLE\PRD\SAPARCH\PRD_54268.1.834712720.DBF
Media Recovery Delayed for 240 minute(s) (thread 1 sequence 54269)
Sun Jun 19 12:51:27 2013
RFS[2]: Archived Log: 'E:\ORACLE\PRD\SAPARCH\PRD_54277.1.834712720.DBF'
RFS[2]: Possible network disconnect with primary database

3 -  RE-START LOG SHIPPING

3.1 – Validate the Status of Standby Database.

3.2 – Validate the last available redo-log

DIR E:\oracle\PRD\saparch\*dbf (You will see only the last shipped)

3.3 – Activate the Log Shipping (Real Production Site-PRD)

C:\>sqlplus / as sysdba

*** Do Some validations Infos

SQL> select name,open_mode from v$database;
NAME      OPEN_MODE
--------- ----------
PRD       READ WRITE

SQL> select max(sequence#) from v$log_history;

         54279

SQL> select status, DEST_NAME, DESTINATION from v$archive_dest where status = 'VALID';

STATUS     DEST_NAME             DESTINATION
---------  ----------            ----------------------------------
VALID      LOG_ARCHIVE_DEST_1    E:\oracle\PRD\saparch

SQL> show parameter LOG_ARCHIVE_DEST_2;

NAME                   TYPE        VALUE
---------------------- ----------- ---------------------------------------------
log_archive_dest_2     string      Service=prd_standby lgwr async
                                    VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
                                    db_unique_name=standby

SQL> show parameter log_archive_dest_state_2
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
log_archive_dest_state_2             string      DEFER

NOW ACTIVATE THE LOG SHIPPING!!!!


SQL> alter system set log_archive_dest_state_2=enable scope=both;

SQL> show parameter log_archive_dest_state_2
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
log_archive_dest_state_2             string      ENABLE


SQL> select max(sequence#) from v$log_history;
         54279

SQL> alter system switch logfile;

SQL> select max(sequence#) from v$log_history;
         54280

3.4 – Validate the shipping logs and appliance in theStandby Site (PRD-STBY)

DIR E:\oracle\PRD\saparch\*dbf
 
3.5 – Rebuild Synchronization without Delay (You are logged into the PRD-STBY Site)

C:\> SQLPLUS / AS SYSDBA
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

3.6 – Validate the Status of Standby Database. (PRD_STBY)
   

3.7 – Rebuild Synchronization With 180mn Delay (3 hours for example)

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 180 DISCONNECT FROM SESSION;
EXIT

And you are done!