DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: ORA-16112 - Logical Standby database log apply service hanging....

  1. #1
    Join Date
    Feb 2004
    Location
    NYC
    Posts
    39

    ORA-16112 - Logical Standby database log apply service hanging....

    Hi,

    Is there anyone who experienced such situation --
    I am trying to build a Logical Standby Database(9i), everything is fine, standby database is up, log transport service is working well, standby DB can receive the logs transported from primary database.
    Then I enable Log Aplly service:
    SQL>ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL;
    After this cmd, the LSP have been up and running for 22 hours, it seems endless, I kept on checking the status, execept get "ORA-16111: log mining and apply setting up" at the beginning, always see below message:
    "
    SQL> SELECT TYPE,STATUS FROM V$LOGSTDBY;

    TYPE STATUS
    --------------------------------------------------------------------------------
    COORDINATOR
    ORA-16112: log mining and apply stopping

    SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS WHERE NAME = 'coordinator state';

    NAME
    ----------------------------------------------------------------
    VALUE
    ----------------------------------------------------------------
    coordinator state
    INITIALIZING
    "
    I do have a large DB(about 77G), is it normal to spend so long time to initialze LSP?
    And do I need to do anything if see ORA-16112: apply stopping?

    I thought LSP might stop, so try to re-enable LSP, got below error message:
    "SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;
    ALTER DATABASE START LOGICAL STANDBY APPLY
    *
    ERROR at line 1:
    ORA-16105: Logical Standby is already running in background

    Thanks a lots!

    Betty

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    whats in the alert log

  3. #3
    Join Date
    Feb 2004
    Location
    NYC
    Posts
    39

    The alert log file

    "tail -100 alter_dev.log" -- get below message :

    ALTER DATABASE REGISTER LOGICAL LOGFILE '/devfin1/standbyArc/arch_1_30075.arc'
    Sun Mar 20 19:42:47 2005
    There are 1 logfiles specified.
    ALTER DATABASE REGISTER LOGICAL LOGFILE
    Completed: ALTER DATABASE REGISTER LOGICAL LOGFILE '/devfin1/
    Sun Mar 20 19:43:06 2005
    ALTER DATABASE REGISTER LOGICAL LOGFILE '/devfin1/standbyArc/arch_1_30076.arc'
    Sun Mar 20 19:43:06 2005
    There are 1 logfiles specified.
    ALTER DATABASE REGISTER LOGICAL LOGFILE
    Completed: ALTER DATABASE REGISTER LOGICAL LOGFILE '/devfin1/
    Sun Mar 20 19:46:57 2005
    ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL 3180452738
    Sun Mar 20 19:46:57 2005
    ALTER DATABASE START LOGICAL STANDBY APPLY
    with optional part
    INITIAL 3180452738
    LSP0 started with pid=15
    Sun Mar 20 19:46:58 2005
    Attempt to start background Logical Standby process
    Completed: ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL
    Sun Mar 20 19:46:58 2005
    LOGSTDBY event: ORA-16111: log mining and apply setting up

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    ok when I did this i got caught on the number of processes on the standby database - was exceeding the limit. Can't remmeber if this was recorded in the alert log though

  5. #5
    Join Date
    Feb 2004
    Location
    NYC
    Posts
    39
    I issued "recover database until change 3180452737 using backup controlfile" ;
    Then "alter database open resetlogs";
    Then register log arch_1_30075.arc;
    Then "ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL 3180452738".

    I don't think the LSP hanging is due to I specified the SCN when I apply INITIAL.
    The end SCN=3180452737 in arch_1_30074.arc, the start SCN=3180452738 in arch_1_30075.arc.

  6. #6
    Join Date
    Oct 2002
    Posts
    807
    I'm not really up on logical standby's. But here's a useful link http://download-west.oracle.com/docs...ly.htm#1017636

    Can you upload a spooled output of the below?

    COLUMN STATUS FORMAT A50
    COLUMN TYPE FORMAT A12
    SELECT TYPE, HIGH_SCN, STATUS FROM V$LOGSTDBY;

    COLUMN NAME FORMAT A35
    COLUMN VALUE FORMAT A35
    SELECT NAME, VALUE FROM V$LOGSTDBY_STATS
    WHERE NAME LIKE 'coordinator%' or NAME LIKE 'transactions%';

    ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI:SS';
    COLUMN STATUS FORMAT A60
    SELECT EVENT_TIME, STATUS, EVENT FROM DBA_LOGSTDBY_EVENTS
    ORDER BY EVENT_TIME, COMMIT_SCN;

    SELECT FILE_NAME, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE#,
    TIMESTAMP, DICT_BEGIN, DICT_END, THREAD# FROM DBA_LOGSTDBY_LOG
    ORDER BY SEQUENCE#;

    SELECT APPLIED_SCN, NEWEST_SCN FROM D BA_LOGSTDBY_PROGRESS;

    ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI:SS';

    SELECT L.SEQUENCE#, L.FIRST_TIME,
    (CASE WHEN L.NEXT_CHANGE# < P.READ_SCN THEN 'YES'
    WHEN L.FIRST_CHANGE# < P.APPLIED_SCN THEN 'CURRENT'
    ELSE 'NO' END) APPLIED
    FROM DBA_LOGSTDBY_LOG L, DBA_LOGSTDBY_PROGRESS P
    ORDER BY SEQUENCE#;

  7. #7
    Join Date
    Feb 2004
    Location
    NYC
    Posts
    39
    SQL> SELECT TYPE, HIGH_SCN, STATUS FROM V$LOGSTDBY;

    TYPE HIGH_SCN STATUS
    ------------ ---------- --------------------------------------------------
    COORDINATOR ORA-16112: log mining and apply stopping

    SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS WHERE NAME LIKE 'coordinator%' or NAME LIKE 'transactions%';

    NAME VALUE
    ----------------------------------- -----------------------------------
    coordinator state INITIALIZING
    transactions ready 0
    transactions applied 0
    coordinator uptime 2546

    SQL> SELECT EVENT_TIME, STATUS, EVENT FROM DBA_LOGSTDBY_EVENTS ORDER BY EVENT_TIME, COMMIT_SCN;

    EVENT_TIME STATUS
    ------------------ ------------------------------------------------------------
    EVENT
    --------------------------------------------------------------------------------
    20-MAR-05 19:46:58 ORA-16111: log mining and apply setting up

    SQL> SELECT FILE_NAME, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE#, TIMESTAMP, DICT_BEGIN, DICT_END, THREAD# FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;

    FILE_NAME
    --------------------------------------------------------------------------------
    SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP DIC DIC THREAD#
    ---------- ------------- ------------ ------------------ --- --- ----------
    /devfin1/standbyArc/arch_1_30075.arc
    30075 3180452738 3180453321 20-MAR-05 19:42:48 NO NO 1

    /devfin1/standbyArc/arch_1_30076.arc
    30076 3180453321 3180453354 20-MAR-05 19:43:06 NO NO 1

    /devfin1/standbyArc/arch_1_30077.arc
    30077 3180453354 3180460541 20-MAR-05 21:23:38 NO NO 1
    ......too many logfiles...OMITTED.......
    ..............................OMMITED...
    /devfin1/standbyArc/arch_1_30218.arc
    30218 3181709641 3181720027 22-MAR-05 13:56:47 NO NO 1

    /devfin1/standbyArc/arch_1_30219.arc
    30219 3181720027 3181726092 22-MAR-05 14:01:57 NO NO 1


    145 rows selected.

    SQL> SELECT L.SEQUENCE#, L.FIRST_TIME, (CASE WHEN L.NEXT_CHANGE# < P.READ_SCN THEN 'YES' WHEN L.FIRST_CHANGE# < P.APPLIED_SCN THEN 'CURRENT' ELSE 'NO' END) APPLIED FROM DBA_LOGSTDBY_LOG L, DBA_LOGSTDBY_PROGRESS P ORDER BY SEQUENCE#;

    SEQUENCE# FIRST_TIME APPLIED
    ---------- ------------------ -------
    30075 20-MAR-05 18:55:06 NO
    30076 20-MAR-05 18:58:06 NO
    30077 20-MAR-05 18:58:10 NO
    .......from 30078 till 30217...OMITED...HERE...
    30218 22-MAR-05 13:46:58 NO
    30219 22-MAR-05 13:56:41 NO
    145 rows selected.

  8. #8
    Join Date
    Oct 2002
    Posts
    807
    Are there any lsp trace files? Nothing new in your alert log yet, I presume..

    Btw, what version and platform? If it's 9.2.0.1, it might be buggy (based on what people have to say on metalink). Your best bet is to open a tar.
    Last edited by Axr2; 03-22-2005 at 03:43 PM.

  9. #9
    Join Date
    Feb 2004
    Location
    NYC
    Posts
    39
    No any new message on alert file since "alter database start logical standby apply initial change SCN"; (2 days gone)
    No LSP trace file;

    The platform is Suse Linux 9.0
    DB: 9.2.0.4

  10. #10
    Join Date
    Feb 2004
    Location
    NYC
    Posts
    39
    Got answer from metalink --
    There is an unpublished Bug 2185415, which is fixed in 9.2.0.2

    But my DB is 9.2.0.4, i am going to open a Tar to ask....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width