Re: Waiting for Dictionary logs on logical standy for more than 15 hours
- From: Ravi Gaur <ravigaur1@xxxxxxxxx>
- To: Sundar.Mahadevan@xxxxxxx
- Date: Tue, 17 Aug 2010 12:29:54 -0500
Sundar,
Take a look at the output from this query and see if you see any missing log
sequences (likely) --
SELECT FILE_NAME, SEQUENCE# AS SEQ#, FIRST_CHANGE# AS FCHANGE#,NEXT_CHANGE#
AS NCHANGE#, TIMESTAMP, DICT_BEGIN AS BEG, DICT_END
AS END, THREAD# AS THR#,Applied FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;
Manually copy these over and register it on the logical side.
HTH
- Ravi
On Tue, Aug 17, 2010 at 9:21 AM, Mahadevan, Sundar <Sundar.Mahadevan@xxxxxxx
> wrote:
> Hi All,
>
> Greetings. I have a primary and logical standby on 10.2.0.3.0 (Solaris 10
> 64 bit). I created the logical standby with RMAN. The command “alter
> database start logical standby apply immediate” completed without any error
> and log standby status is “LOGSTDBY status: ORA-16111: log mining and apply
> setting up” for more than 15 hours now. I did a crosscheck archive log all
> on both primary as well as logical standby. On primary there are many
> “validation failed for archived log errors” while standby is ok.
>
>
>
> Alert log snippet from logical standby
>
>
>
> Completed: alter database start logical standby apply immediate
>
> Mon Aug 16 16:46:38 2010
>
> LOGSTDBY status: ORA-16111: log mining and apply setting up
>
> Mon Aug 16 16:46:38 2010
>
>
>
>
>
> SQL> SELECT SESSION_ID, STATE FROM V$LOGSTDBY_STATE;
>
>
>
> SESSION_ID STATE
>
> ---------- ----------------------------------------------------------------
>
> 1 WAITING FOR DICTIONARY LOGS
>
>
>
>
>
> SYS@wsst03dg SQL> set lines 150
>
> SELECT PERCENT_DONE, COMMAND FROM V$LOGMNR_DICTIONARY_LOAD WHERE
> SESSION_ID = (SELECT SESSION_ID FROM V$LOGSTDBY_STATE);SYS@wsst03dg SQL>
>
>
>
> PERCENT_DONE
>
> ------------
>
> COMMAND
>
>
> ------------------------------------------------------------------------------------------------------------------------------------------------------
>
> 0
>
>
>
>
>
> SYS@wsst03dg SQL> SELECT SID, SERIAL#, SPID, TYPE FROM V$LOGSTDBY_PROCESS;
>
>
>
> SID SERIAL# SPID TYPE
>
> ---------- ---------- ------------ ------------------------------
>
> 147 1 25363 COORDINATOR
>
>
>
> SYS@wsst03dg SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY
> HH24:MI:SS';
>
> Session altered.
>
>
>
> SYS@wsst03dg SQL> SELECT APPLIED_TIME, APPLIED_SCN, MINING_TIME,
> MINING_SCN FROM V$LOGSTDBY_PROGRESS;
>
>
>
> APPLIED_TIME APPLIED_SCN MINING_TIME MINING_SCN
>
> -------------------- ----------- -------------------- ----------
>
> 12-AUG-2010 16:04:03 85613206
>
>
>
> SYS@wsst03dg SQL>
>
>
>
> My question to you is:
>
>
>
> 1) is logical standby waiting on archived logs from primary ?
>
> 2) would a DELETE EXPIRED ARCHIVELOG ALL; or DELETE OBSOLETE; do the
> trick?
>
> 3) I do not see any error messages on alert log on both primary and
> logical standby. Is there anything that I am missing out?
>
>
>
> I will be going in for a rebuild of logical standby in an hour since the
> users are waiting on this. If someone could respond before then, I would
> really appreciate it. Please forgive me for the time limit restriction.
> Appreciate this wonderful group for its effort, willingness to help and
> time.
>
>
>
> --Sundar
>
>
>
Other related posts:
