This discussion is archived
9 Replies Latest reply: Nov 18, 2013 4:13 PM by 209ee8b0-1fc3-4c48-b737-45d5557e64ef RSS

managed_standby returns lower arch value but wait_for_log shows latest log sequence number

209ee8b0-1fc3-4c48-b737-45d5557e64ef Newbie
Currently Being Moderated

Hi,

 

The last archive log generated in Primary DB is 241949. The managed_standby view of physical standby database returns

 

select process,status,sequence# from v$managed_standby;

 

PROCESS          STATUS            SEQUENCE#

 

ARCH               CONNECTED       0

ARCH               CLOSING            241814

RFS                  IDLE                   0

RFS                  WRITING            241950

MRP0               WAIT_FOR_LOG  241950

 

Is it ignorable? or Attention needed?

 

regards,

Prem

  • 1. Re: managed_standby returns lower arch value but wait_for_log shows latest log sequence number
    mseberg Guru
    Currently Being Moderated

    Hello;

     

    Normal. 241949 is the last log and 241950 is the next one Oracle is waiting for.

     

    No action required. It will move to 51 and 52 once the Primary generates more logs.

     

    Best Regards

     

    mseberg

  • 2. Re: managed_standby returns lower arch value but wait_for_log shows latest log sequence number
    209ee8b0-1fc3-4c48-b737-45d5557e64ef Newbie
    Currently Being Moderated

    Hi Mseberg,

     

     

    Thanks for replying. Sorry i have asked regarding the difference in ARCH closing and MRP Wait_for_log.

     

    ARCH           CLOSING        241814
    MRP0           WAIT_FOR_LOG   241950
  • 3. Re: managed_standby returns lower arch value but wait_for_log shows latest log sequence number
    mseberg Guru
    Currently Being Moderated

    OK.

     

    CLOSING - Process has completed archival and is closing the archived redo log

     

    WAIT_FOR_LOG - Process is waiting for the archived redo log to be completed

     

    I'm thinking your Apply is behind some.

     

    Best Regards

     

    mseberg

  • 4. Re: managed_standby returns lower arch value but wait_for_log shows latest log sequence number
    209ee8b0-1fc3-4c48-b737-45d5557e64ef Newbie
    Currently Being Moderated

    Hi,

     

    When i use the query

     

     

    select max(sequence#) from v$archived_log where applied='YES';

     

    it shows '241992' (latest updated archive log sequence value) but ARCH closing stopped with this sequence 241814.

     

    when applied returns the latest sequence why the ARCH still points something older sequence. I am very much confused. please suggest.

     

    Thanks,

    Prem

  • 5. Re: managed_standby returns lower arch value but wait_for_log shows latest log sequence number
    mseberg Guru
    Currently Being Moderated

    Hello again;

     

    Try this from your Primary

     

    http://www.visi.com/~mseberg/data_guard/monitor_data_guard_transport.html

     

    Change DEST_ID's as needed and Post your results


    Best Regards


    mseberg

  • 6. Re: managed_standby returns lower arch value but wait_for_log shows latest log sequence number
    209ee8b0-1fc3-4c48-b737-45d5557e64ef Newbie
    Currently Being Moderated

    Hi,

     

     

    This was the result which when i executed the query from primary database.

     

     

    DB_NAME    HOSTNAME       LOG_ARCHIVED LOG_APPLIED APPLIED_TIME   LOG_GAP
    ---------- -------------- ------------ ----------- -------------- -------
    PROD       PrimaryServer            241994      241994 19-NOV/10:06         0

  • 7. Re: managed_standby returns lower arch value but wait_for_log shows latest log sequence number
    mseberg Guru
    Currently Being Moderated

    You don't appear to have an issue.

     

    Log Gap = 0 and the Archived and Applied matched.

     

    On my test system I get better results

     

    SQL> select process,status,sequence# from v$managed_standby;

     

    PROCESS   STATUS        SEQUENCE#

    --------- ------------ ----------

    ARCH      CLOSING            1255

    ARCH      CLOSING            1259

    ARCH      CONNECTED             0

    ARCH      CLOSING            1256

    ARCH      CLOSING            1260

    ARCH      CLOSING            1257

    ARCH      CLOSING            1261

    ARCH      CLOSING            1258

    MRP0      APPLYING_LOG       1262

     

    Did you set LOG_ARCHIVE_MAX_PROCESSES beyond its default value? If no try bumping it up some, maybe 2 or 4 higher.


    Best Regards


    mseberg

  • 8. Re: managed_standby returns lower arch value but wait_for_log shows latest log sequence number
    209ee8b0-1fc3-4c48-b737-45d5557e64ef Newbie
    Currently Being Moderated

    Hi,

     

    Until yesterday we are not facing this before in DR2, the DR1 was going smooth at this point of time. I am afraid, we cannot it let it go for long thinking like the log is being applied.

    We are using LOG_ARCHIVE_MAX_PROCESSES = 2 but i am not pretty sure changing this parameter would solve it.

    Any other suggestion.

     

    Should we take it as serious problem or we can go for one more day like this?

     

    thanks,

    Prem

  • 9. Re: managed_standby returns lower arch value but wait_for_log shows latest log sequence number
    209ee8b0-1fc3-4c48-b737-45d5557e64ef Newbie
    Currently Being Moderated

    Hi,

     

    When i checked the alert log of standby it has following error at the time ARCH CLOSING done. Any idea on this please

     

    Fri Nov 15 22:45:48 2013

    Media Recovery Log E:\ORACLE\ARCH\PROD\ARC0000241812_0606143297.0001

    Media Recovery Waiting for thread 1 sequence 241813 (in transit)

    Fri Nov 15 23:06:59 2013

     

     

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

     

    Fatal NI connect error 12518, connecting to:

    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ProdServer.corp.root.global)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=prod)(INSTANCE_NAME=prod)(CID=(PROGRAM=e:\oracle\proddb\11.2\bin\ORACLE.EXE)(HOST=DR1SERVER)(USER=mpanzoraservice))))

     

      VERSION INFORMATION:

                    TNS for 32-bit Windows: Version 11.2.0.3.0 - Production

                    Windows NT TCP/IP NT Protocol Adapter for 32-bit Windows: Version 11.2.0.3.0 - Production

      Time: 15-NOV-2013 23:06:59

      Tracing not turned on.

      Tns error struct:

        ns main err code: 12564

       

    TNS-12564: TNS:connection refused

        ns secondary err code: 0

        nt main err code: 0

        nt secondary err code: 0

        nt OS err code: 0

     

     

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

     

    Fatal NI connect error 12518, connecting to:

    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ProdServer.corp.root.global)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=prod)(INSTANCE_NAME=prod)(CID=(PROGRAM=e:\oracle\proddb\11.2\bin\ORACLE.EXE)(HOST=DR1SERVER)(USER=mpanzoraservice))))

     

      VERSION INFORMATION:

                    TNS for 32-bit Windows: Version 11.2.0.3.0 - Production

                    Windows NT TCP/IP NT Protocol Adapter for 32-bit Windows: Version 11.2.0.3.0 - Production

      Time: 15-NOV-2013 23:07:00

      Tracing not turned on.

      Tns error struct:

        ns main err code: 12564

       

    TNS-12564: TNS:connection refused

        ns secondary err code: 0

        nt main err code: 0

        nt secondary err code: 0

        nt OS err code: 0

     

     

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

     

    Fatal NI connect error 12518, connecting to:

    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ProdServer.corp.root.global)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=prod)(INSTANCE_NAME=prod)(CID=(PROGRAM=e:\oracle\proddb\11.2\bin\ORACLE.EXE)(HOST=DR1SERVER)(USER=mpanzoraservice))))

     

      VERSION INFORMATION:

                    TNS for 32-bit Windows: Version 11.2.0.3.0 - Production

                    Windows NT TCP/IP NT Protocol Adapter for 32-bit Windows: Version 11.2.0.3.0 - Production

      Time: 15-NOV-2013 23:07:00

      Tracing not turned on.

      Tns error struct:

        ns main err code: 12564

       

    TNS-12564: TNS:connection refused

        ns secondary err code: 0

        nt main err code: 0

        nt secondary err code: 0

        nt OS err code: 0

    Error 12518 received logging on to the standby

    FAL[client, USER]: Error 12518 connecting to PRODPRI for fetching gap sequence

    Errors in file E:\ORACLE\PRODDB\11.2\ADMIN\PROD_DR1SERVER\diag\rdbms\proddr2\prod\trace\prod_pr00_2164.trc:

    ORA-12518: TNS:listener could not hand off client connection

    Errors in file E:\ORACLE\PRODDB\11.2\ADMIN\PROD_DR1SERVER\diag\rdbms\proddr2\prod\trace\prod_pr00_2164.trc:

    ORA-12518: TNS:listener could not hand off client connection

    Fri Nov 15 23:14:12 2013

    RFS[4]: Assigned to RFS process 3024

    RFS[4]: Identified database type as 'physical standby': Client is ARCH pid 5044

    Fri Nov 15 23:14:14 2013

    RFS[5]: Assigned to RFS process 3012

    RFS[5]: Identified database type as 'physical standby': Client is LGWR ASYNC pid 5088

    Primary database is in MAXIMUM PERFORMANCE mode

    RFS[5]: No standby redo logfiles available for thread 1

    RFS[5]: Opened log for thread 1 sequence 241815 dbid 898145939 branch 606143297

    Fri Nov 15 23:14:21 2013

    Fetching gap sequence in thread 1, gap sequence 241813-241813

    FAL[client]: Trying FAL server: PRODPRI

    Fri Nov 15 23:14:27 2013

    RFS[6]: Assigned to RFS process 1008

    RFS[6]: Identified database type as 'physical standby': Client is ARCH pid 5044

    RFS[6]: Selected log 6 for thread 1 sequence 241813 dbid 898145939 branch 606143297

    Fri Nov 15 23:14:29 2013

    Archived Log entry 54703 added for thread 1 sequence 241813 ID 0x113247f dest 1:

    RFS[6]: Selected log 6 for thread 1 sequence 241814 dbid 898145939 branch 606143297

    Archived Log entry 54704 added for thread 1 sequence 241814 ID 0x113247f dest 1:

    Fri Nov 15 23:14:31 2013

    Media Recovery Log E:\ORACLE\ARCH\PROD\ARC0000241813_0606143297.0001

    Media Recovery Log E:\ORACLE\ARCH\PROD\ARC0000241814_0606143297.0001

    Media Recovery Waiting for thread 1 sequence 241815 (in transit)

    Fri Nov 15 23:44:18 2013

    Archived Log entry 54705 added for thread 1 sequence 241815 rlc 606143297 ID 0x113247f dest 3:

    RFS[5]: No standby redo logfiles available for thread 1

    RFS[5]: Opened log for thread 1 sequence 241816 dbid 898145939 branch 606143297

    Fri Nov 15 23:44:23 2013

    Media Recovery Log E:\ORACLE\ARCH\PROD\ARC0000241815_0606143297.0001

    Media Recovery Waiting for thread 1 sequence 241816 (in transit)

    Sat Nov 16 00:02:31 2013

    Non critical error ORA-48913 caught while writing to trace file "E:\ORACLE\PRODDB\11.2\ADMIN\PROD_DR1SERVER\diag\rdbms\proddr2\prod\trace\prod_rfs_3012.trc"

    Error message: ORA-48913: Writing into trace file failed, file size limit [10485760] reached

    Writing to the above trace file is disabled for now on...

    Sat Nov 16 00:06:03 2013

    Archived Log entry 54706 added for thread 1 sequence 241816 rlc 606143297 ID 0x113247f dest 3:

    RFS[5]: No standby redo logfiles available for thread 1

    RFS[5]: Opened log for thread 1 sequence 241817 dbid 898145939 branch 606143297

    Sat Nov 16 00:06:04 2013

    Media Recovery Log E:\ORACLE\ARCH\PROD\ARC0000241816_0606143297.0001

    Sat Nov 16 00:06:20 2013

    Media Recovery Waiting for thread 1 sequence 241817 (in transit)

    Sat Nov 16 00:35:55 2013

    Archived Log entry 54707 added for thread 1 sequence 241817 rlc 606143297 ID 0x113247f dest 3:

    RFS[5]: No standby redo logfiles available for thread 1

    RFS[5]: Opened log for thread 1 sequence 241818 dbid 898145939 branch 606143297

    Sat Nov 16 00:35:56 2013

    Media Recovery Log E:\ORACLE\ARCH\PROD\ARC0000241817_0606143297.0001

    Media Recovery Waiting for thread 1 sequence 241818 (in transit)

    Sat Nov 16 01:05:57 2013

    Archived Log entry 54708 added for thread 1 sequence 241818 rlc 606143297 ID 0x113247f dest 3:

    RFS[5]: No standby redo logfiles available for thread 1

    RFS[5]: Opened log for thread 1 sequence 241819 dbid 898145939 branch 606143297

    Sat Nov 16 01:05:58 2013

    Media Recovery Log E:\ORACLE\ARCH\PROD\ARC0000241818_0606143297.0001

    Media Recovery Waiting for thread 1 sequence 241819 (in transit)

    Sat Nov 16 01:35:55 2013

    Archived Log entry 54709 added for thread 1 sequence 241819 rlc 606143297 ID 0x113247f dest 3:

    RFS[5]: No standby redo logfiles available for thread 1

    RFS[5]: Opened log for thread 1 sequence 241820 dbid 898145939 branch 606143297

    Sat Nov 16 01:36:00 2013

    Media Recovery Log E:\ORACLE\ARCH\PROD\ARC0000241819_0606143297.0001

    Media Recovery Waiting for thread 1 sequence 241820 (in transit)

    Sat Nov 16 02:05:56 2013

    Archived Log entry 54710 added for thread 1 sequence 241820 rlc 606143297 ID 0x113247f dest 3:

    RFS[5]: No standby redo logfiles available for thread 1

    RFS[5]: Opened log for thread 1 sequence 241821 dbid 898145939 branch 606143297

    Sat Nov 16 02:05:59 2013

    Media Recovery Log E:\ORACLE\ARCH\PROD\ARC0000241820_0606143297.0001

    Media Recovery Waiting for thread 1 sequence 241821 (in transit)

    Sat Nov 16 02:35:55 2013

    Archived Log entry 54711 added for thread 1 sequence 241821 rlc 606143297 ID 0x113247f dest 3:

    RFS[5]: No standby redo logfiles available for thread 1

    RFS[5]: Opened log for thread 1 sequence 241822 dbid 898145939 branch 606143297

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points