Logical standby SQL Apply Troubleshooting

Problem: After starting the SQL APPLY it stops at initializing stage.

Following messages are logged in alert.log

LOGSTDBY Parameter: MAX_SGA = 1024
LOGSTDBY Parameter: DISABLE_APPLY_DELAY =
LOGSTDBY Parameter: REAL_TIME =
Completed: ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE
Mon Dec 26 04:54:11 2008
LOGSTDBY status: ORA-16111: log mining and apply setting up
Mon Dec 26 04:54:12 2008
LOGMINER: Parameters summary for session# = 22
LOGMINER: Number of processes = 3, Transaction Chunk Size = 201
LOGMINER: Memory Size = 1024M, Checkpoint interval = 5120M

Expected output is similar to following

LOGSTDBY status: ORA-16111: log mining and apply setting up
Mon Dec 26 11:47:44 2008

LOGMINER: Parameters summary for session# = 22
LOGMINER: Number of processes = 6, Transaction Chunk Size = 201
LOGMINER: Memory Size = 1024M, Checkpoint interval = 5120M

LOGMINER: session# = 22, reader process P000 started with pid=63 OS id=14936
LOGMINER: session# = 22, builder process P001 started with pid=64 OS id=14942
LOGMINER: session# = 22, preparer process P002 started with pid=65 OS id=14948
LOGMINER: session# = 22, preparer process P004 started with pid=67 OS id=14952
LOGMINER: session# = 22, preparer process P003 started with pid=66 OS id=14950
LOGMINER: session# = 22, preparer process P005 started with pid=68 OS id=14954


Mon Dec 26 11:47:45 2008
RFS LogMiner: Client enabled and ready for notification
Mon Dec 26 11:47:45 2008
Primary database is in MAXIMUM PERFORMANCE mode

RFS[34]: Successfully opened standby log 15: ‘+ASMG1/TEMPDB/onlinelog/group_15.101.918490673’
Mon Dec 26 11:47:45 2008
RFS LogMiner: Registered logfile [+ASMF1/TEMPDB/STDARCH/3_2054_641082275.dbf] to LogMiner session id [22]

LOGSTDBY Analyzer process P006 started with pid=69 OS id=15047
LOGSTDBY Apply process P011 started with pid=74 OS id=15057
LOGSTDBY Apply process P008 started with pid=71 OS id=15051
LOGSTDBY Apply process P012 started with pid=75 OS id=15059
LOGSTDBY Apply process P014 started with pid=77 OS id=15071
LOGSTDBY Apply process P010 started with pid=73 OS id=15055
LOGSTDBY Apply process P009 started with pid=72 OS id=15053
LOGSTDBY Apply process P007 started with pid=70 OS id=15049
LOGSTDBY Apply process P013 started with pid=76 OS id=15069

+ Other Observations

After starting the SQL APPLY, error is logged in

SQL> SELECT * FROM V$LOGSTDBY;

SERIAL# LOGSTDBY_ID PID TYPE STATUS_CODE STATUS
——– ——————– ———— ————- ———– ———
1361 -1 4631 COORDINATOR 1291 ORA-01291: missing logfile

Which suggests that some log file is missing. +

Following SQL can be used to find out the missing GAP.

SQL > SELECT THREAD#, SEQUENCE#, FILE_NAME FROM DBA_LOGSTDBY_LOG X
WHERE NEXT_CHANGE# NOT IN (SELECT FIRST_CHANGE# FROM DBA_LOGSTDBY_LOG WHERE X.THREAD# = THREAD#) ORDER BY THREAD#,
SEQUENCE#;

To determine the availability of missing logs from the backup, use following RMAN script

RMAN> list backup of archivelog all;

This will give us the SCN / Sequence information. Based on this information, we can now restore the archive logs

RMAN> RUN
{
RESTORE ARCHIVELOG ALL;
OR
RESTORE ARCHIVELOG FROM SCN UNTIL SCN ;
OR
RESTORE ARCHIVELOG FROM SEQUENCE UNTIL SEQUENCE ;
}

Note: Unless we specify new destination, these logs will be restored to FRA.

After restoring the missing archive logs, SQL APPLY will automatically try to fill the gap. If this does not work, we’ll have to manually copy & register the archive logs using

SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE ”;

If FRA is configured on ASM, RMAN will come in picture.

If the missing archive logs are not available in backups, we’ll have to rebuild the logical standby.

Advertisements
This entry was posted in Oracle Data Guard, Oracle Logical Data Guard and tagged . Bookmark the permalink.

3 Responses to Logical standby SQL Apply Troubleshooting

  1. sekhar says:

    HI,

    This blog is just awesome.one should check this daily.

    here i have one doubt,

    how to produce the issue “COORDINATOR ORA-01291: missing logfile”.

    i have a script it checks for any missing log files, i want to reproduce this issue, so that my script can able to check this out.

  2. hadi says:

    This document looks awesome. But I have an issue with logical standby database.
    I have the logical standby database setup on windows. Archivelog file has been shipping from primary database to logical standby database. But it has not been applying to the logical standby side. I have checked the sequence# that last applied sequence# is 5016 and current applying sequence# is 5078. I have deleted lot of archived file from the logical standby side which is not applied . So now I have available sequence# is 5410. So my question is that how can skip those old sequence# and make sequence#5410 as current.. from there I want apply as continue. How can I do that>?

    From Logical Standby database:
    FILE_NAME SEQUENCE# TO_CHAR(TIMESTAMP, APPLIED
    —————————————- ——— —————— ——–
    +DATA/abcDB/archivelog/2015_03_29/thr 5016 29-MAR-15 00:15:11 YES
    ead_1_seq_5016.370.875578511

    +DATA/abcDB/archivelog/2015_03_31/thr 5078 31-MAR-15 13:39:15 CURRENT
    ead_2_seq_5078.336.875799543

    Last applied sequences from primary side 5864.

    THREAD# LAST_APPLIED_LOG
    ——— —————-
    1 5690
    2 5864

    I will be appreciated, if any one could find me the way.

    • Best option is to work with Oracle support for these kind of issues. I never had this situation, so can’t say if it’s possible or not (I believe it is not). The only option is to re-instantiate the logical standby.

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