Primary and Standby out of sync issue.

Errors Observed –

Thread 1 advanced to log sequence 42642 (LGWR switch)

Current log# 7 seq# 42642 mem# 0: +REDO_DG01/rp27db1_ttc/onlinelog/group_7.288.823921543

Current log# 7 seq# 42642 mem# 1: +REDO_DG02/rp27db1_ttc/onlinelog/group_7.288.823921547

LNS: Standby redo logfile selected for thread 1 sequence 42642 for destination LOG_ARCHIVE_DEST_2
Tue Nov 26 12:10:35 2013
Archived Log entry 40018 added for thread 1 sequence 42641 ID 0xfc7bdacf dest 1:
Tue Nov 26 12:10:36 2013
ARC2: Standby redo logfile selected for thread 1 sequence 42641 for destination LOG_ARCHIVE_DEST_2
Tue Nov 26 12:11:52 2013
ALTER SYSTEM SET log_archive_dest_state_2=’ENABLE’ SCOPE=BOTH;
Tue Nov 26 12:21:36 2013
LNS: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3113)
LNS: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
Errors in file /opt/oracle/diag/rdbms/rp27db1_ttc/RP27DB11/trace/RP27DB11_lns1_16642.trc:
ORA-03113: end-of-file on communication channel
LGWR: I/O error 3113 archiving log 7 to ‘rp27db1_ttce’
Errors in file /opt/oracle/diag/rdbms/rp27db1_ttc/RP27DB11/trace/RP27DB11_lns1_16642.trc:
ORA-03113: end-of-file on communication channel
Errors in file /opt/oracle/diag/rdbms/rp27db1_ttc/RP27DB11/trace/RP27DB11_lns1_16642.trc:
ORA-03113: end-of-file on communication channel
LNS: Failed to archive log 7 thread 1 sequence 42642 (3
113)

SOLUTION:
——–

On primary database use the following command:

SQL> select dest_id,error from v$archive_dest;

DEST_ID ERROR

———- —————————————————————–

1

2 ORA-03113: end-of-file on communication channel

3

4

SQL> SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;

Take an incremental backup of primary from the SCN where standby is lagging behind and apply on the standby server
*******************************************************************************************************************
>>>>>> On STANDBY database query the v$database view and record the current SCN of the standby database:

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN

———–

1.3945E+10

SQL> SELECT to_char(CURRENT_SCN) FROM V$DATABASE;

TO_CHAR(CURRENT_SCN)

—————————————-

13945141914

>>>>>> Stop Redo Apply on the standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

*

ERROR at line 1:

ORA-16136: Managed Standby Recovery not active

If you see the above error, it means Managed Recovery is already off

>>>>>> You can also confirm from the view v$managed_standby to see if the MRP is running or not

SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

>>>>>> Connect to the primary database as the RMAN target and create an incremental

backup from the current SCN of the standby database:

RMAN> BACKUP INCREMENTAL FROM SCN 13945141914 DATABASE FORMAT ‘/tmp/ForStandby_%U’ tag ‘FOR STANDBY’

>>>>>> Do a recovery of the standby database using the incremental backup of primary taken above:

On the Standby server, without connecting to recovery catalog, catalog the backupset of the incremental

backup taken above. Before this, of course you need to copy the backup piece of the incremental backup taken above to a location accessible to standby server.

$ rman nocatalog target /

RMAN> CATALOG BACKUPPIECE ‘/dump/proddb/inc_bkup/ForStandby_1qjm8jn2_1_1’;

>>>>>> Now in the same session, start the recovery

RMAN> RECOVER DATABASE NOREDO;

>>>>>> You should see something like:

Starting recover at 2013-11-17 04:59:57

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=309 devtype=DISK

channel ORA_DISK_1: starting incremental datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

….

..

..

.

channel ORA_DISK_1: reading from backup piece /dump/proddb/inc_bkup/ForStandby_1qjm8jn2_1_1

channel ORA_DISK_1: restored backup piece 1

piece handle=/dump/proddb/inc_bkup/ForStandby_1qjm8jn2_1_1 tag=FOR STANDBY

channel ORA_DISK_1: restore complete, elapsed time: 01:53:08

Finished recover at 2013-11-25 05:20:3

>>>>>> Delete the backup set from standby:

RMAN> DELETE BACKUP TAG ‘FOR STANDBY’;

using channel ORA_DISK_1

List of Backup Pieces

BP Key BS Key Pc# Cp# Status Device Type Piece Name

——- ——- — — ———– ———– ———-

17713 17713 1 1 AVAILABLE DISK /dump/proddb/inc_bkup/ForStandby_1qjm8jn2_1_1

Do you really want to delete the above objects (enter YES or NO)? YES

deleted backup piece

backup piece handle=/dump/proddb/inc_bkup/ForStandby_1qjm8jn2_1_1 recid=17713 stamp=660972421

Deleted 1 objects

>>>>>> Try to start the managed recovery.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

If you get an error here, you need to ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

If no error, then using the view v$managed_standby, verify that MRP process is started and has the status APPLYING_LOGS.

6. After this, check whether the logs are being applied on the standby or not:

SQL> SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;

>>>>>> Note:

After doing a recovery using the incremental backup, you will not see the sequence#’s which were visible earlier

with APPLIED=NO because they have been absorbed as part of the incremental backup and applied on standby during recovery.

The APPLIED column starts showing YES for the logs which are being transmitted now, this means logs are being applied.

Check the status of MRP process in the view v$managed_standby. The status should be APPLYING_LOGS for the duration

that available logs are being applied and once all available logs have been applied, the status should be WAITING_FOR_LOGS

>>>>>> Another check to verify that primary and standby are in sync. Run the following query on both standby and primary:

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

Output should be same on both databases.

*******************************************************************************************

>>>>>> Still find that logs are being transmitted but not being applied on the standby

Register all these archived logs with the standby database.

alter database register logfile

Advertisements

About shanojkumar

* Has 12 years of experience in Planning, Implementing, Managing and Monitoring the Oracle Databases as an (Oracle 10g & 11g OCP) Certified Oracle Database Administrator.
This entry was posted in Oracle and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s