dataguard log shipping problem

•  Print 
•  Email

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!

ora -600 internal error and database connectivity issue.

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-00554: initialization of internal recovery manager package failed

RMAN-04005: error from target database:

ORA-00600: internal error code, arguments: [ksm_mga_pseg_cbk_attach:map_null], [], [], [], [], [], [], [], [], [], [], []

ORA-27300: OS system dependent operation:open failed with status: 2

ORA-27301: OS failure message: No such file or directory

ORA-27302: failure occurred at: sskgm_mga_at

09-04-19 06:20:05 Alert file: /tmp/PXRPDB.rman_archivelog_backup.error_found.txt

09-04-19 06:20:05 Errors found

crsctl status res -t init

check the crsd log

/oraclebase/app/oracle/product/18.0.0/grid/log/omexd001/crsd

oswatcher

ps -ef |grep osw

Below 1 and 2 need to executed as root user.

1)OS watcher process.

Node 1

+++++++

[oracle@wdbfffadm01 oswbb]$ pwd

/home/oracle/oswbb/oswbb

Cd /home/oracle/oswbb/oswbb  --root user

./startOSW.sh

Output file

# cd /opt/oracle.oswatcher/osw/archive

Run for 1 hour and issue

Cd /home/oracle/oswbb/oswbb  --root user

./stopOSW.sh

Node 2:

pxdwdbaffffdm02 :

[oracle@wdbadm02 oswbb]$ pwd

/home/oracle/oswbb

Cd /home/oracle/oswbb

./startOSW.sh

Output file in

 cd /opt/oracle.oswatcher/osw/archive

cd /home/oracle/oswbb

./stopOSW.sh