9 Replies Latest reply: Nov 18, 2013 6:13 PM by 1055320 RSS

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

    1055320

      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

          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
            1055320

            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

              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
                1055320

                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

                  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
                    1055320

                    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

                      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
                        1055320

                        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
                          1055320

                          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