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!