5 Replies Latest reply: May 6, 2012 9:05 AM by mseberg RSS

    Redo log file seem not to transfer to standby database!

    871977
      Oracle 11gr2 on Oracle Linux 5

      Primary Database

      SQL>select SEQUENCE# from v$log;

      SEQUENCE#
      ----------
      16
      14
      15

      SQL>

      Standby Database

      SQL> select SEQUENCE# from v$log;

      SEQUENCE#
      ----------
      16
      15
      14

      SQL>

      Sequence# of the Primary and Standby is the same after i issued alter system switch logfile; Primary

      But

      I check archived_log on Primary as below.

      SQL> SELECT sequence#, first_time, next_time
      FROM v$archived_log
      ORDER BY sequence#
      / 2 3 4

      SEQUENCE# FIRST_TIM NEXT_TIME
      ---------- --------- ---------
      7 06-MAY-12 06-MAY-12
      8 06-MAY-12 06-MAY-12
      9 06-MAY-12 06-MAY-12
      10 06-MAY-12 06-MAY-12
      11 06-MAY-12 06-MAY-12
      11 06-MAY-12 06-MAY-12
      12 06-MAY-12 06-MAY-12
      12 06-MAY-12 06-MAY-12
      13 06-MAY-12 06-MAY-12
      13 06-MAY-12 06-MAY-12
      14 06-MAY-12 06-MAY-12

      SEQUENCE# FIRST_TIM NEXT_TIME
      ---------- --------- ---------
      14 06-MAY-12 06-MAY-12
      15 06-MAY-12 06-MAY-12
      15 06-MAY-12 06-MAY-12

      14 rows selected.

      SQL>


      And on Standby are different

      SQL> SELECT sequence#, first_time, next_time
      FROM v$archived_log
      ORDER BY sequence#
      / 2 3 4

      SEQUENCE# FIRST_TIM NEXT_TIME
      ---------- --------- ---------
      11 06-MAY-12 06-MAY-12

      SQL>

      How can i solve this?

      Thank you!
        • 1. Re: Redo log file seem not to transfer to standby database!
          mseberg
          Dan;

          The query you are using on the Primary may be incorrect. For one it does not show enough information.

          Try this query :

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

          And Post the results :

          If there is an issue both Alert logs generally are a great source of information on why its not work. Look for errors in both and post.

          Common Issues

          Password file not moved Standby and renamed as needed.
          Parameter log_archive_dest_n set incorrectly.
          Parameter LOG_ARCHIVE_DEST_STATE_n set incorrectly.
          Other parameter set incorrectly - (FAL_SERVER, DB_UNIQUE_NAME, DB_FILE_NAME_CONVERT, LOG_FILE_NAME_CONVERT)
          Parameter compatible mismatched between databases.
          Missing of incorrect entry in tnsnames.ora or listener.ora
          Applied not started correctly on Standby





          Best Regards

          mseberg

          Edited by: mseberg on May 6, 2012 6:43 AM
          • 2. Re: Redo log file seem not to transfer to standby database!
            871977
            Thanks mseberg,

            I executed On Primary

            DB_NAME HOSTNAME LOG_ARCHIVED LOG_APPLIED APPLIED_TIME LOG_GAP
            ---------- -------------- ------------ ----------- -------------- -------
            PC00PRMY EDT3R17P0 15 11 06-MAY/14:10 4

            SQL>

            I executed On Standby

            DB_NAME HOSTNAME LOG_ARCHIVED LOG_APPLIED APPLIED_TIME LOG_GAP
            ---------- -------------- ------------ ----------- -------------- -------
            PC00PRMY EDT3R17P2 11 06-MAY/14:10

            SQL>

            Please advise!
            • 3. Re: Redo log file seem not to transfer to standby database!
              mseberg
              So you have a gap of 4.

              What do the alert logs show?

              I would look to see if the archive is there not applying or if the archive is just not transporting.

              Then we can address the real issue.

              Best Regards

              mseberg
              • 4. Re: Redo log file seem not to transfer to standby database!
                871977
                This is log on Primary DB

                Errors in file /u01/app/oracle/diag/rdbms/pc00prmy/pc00prmy/trace/pc00prmy_arc3_
                4251.trc:
                ORA-16058: standby database instance is not mounted
                PING[ARC3]: Heartbeat failed to connect to standby 'pc00sby1'. Error is 16058.
                Sun May 06 14:08:14 2012
                Errors in file /u01/app/oracle/diag/rdbms/pc00prmy/pc00prmy/trace/pc00prmy_arc3_
                4251.trc:
                ORA-16058: standby database instance is not mounted
                PING[ARC3]: Heartbeat failed to connect to standby 'pc00sby1'. Error is 16058.
                Sun May 06 14:09:15 2012
                Errors in file /u01/app/oracle/diag/rdbms/pc00prmy/pc00prmy/trace/pc00prmy_arc3_
                4251.trc:
                ORA-16058: standby database instance is not mounted
                PING[ARC3]: Heartbeat failed to connect to standby 'pc00sby1'. Error is 16058.
                Sun May 06 14:09:18 2012
                ALTER SYSTEM ARCHIVE LOG
                Sun May 06 14:09:18 2012
                Thread 1 advanced to log sequence 12 (LGWR switch)
                Current log# 3 seq# 12 mem# 0: /u01/app/oracle/oradata/pc00prmy/redo03.log
                Sun May 06 14:09:18 2012
                Archived Log entry 5 added for thread 1 sequence 11 ID 0xcd8de2c6 dest 1:
                Sun May 06 14:09:19 2012
                Errors in file /u01/app/oracle/diag/rdbms/pc00prmy/pc00prmy/trace/pc00prmy_arc1_
                4247.trc:
                ORA-16058: standby database instance is not mounted
                FAL[server, ARC1]: Error 16058 creating remote archivelog file 'pc00sby1'
                FAL[server, ARC1]: FAL archive failed, see trace file.
                Errors in file /u01/app/oracle/diag/rdbms/pc00prmy/pc00prmy/trace/pc00prmy_arc1_
                4247.trc:
                ORA-16055: FAL request rejected
                ARCH: FAL archive failed. Archiver continuing
                ORACLE Instance pc00prmy - Archival Error. Archiver continuing.
                Sun May 06 14:15:50 2012
                ******************************************************************
                LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
                ******************************************************************
                Sun May 06 14:15:50 2012
                Thread 1 advanced to log sequence 13 (LGWR switch)
                Current log# 1 seq# 13 mem# 0: /u01/app/oracle/oradata/pc00prmy/redo01.log
                Sun May 06 14:15:50 2012
                Archived Log entry 7 added for thread 1 sequence 12 ID 0xcd8de2c6 dest 1:
                LNS: Standby redo logfile selected for thread 1 sequence 12 for destination LOG_
                ARCHIVE_DEST_2
                ******************************************************************
                LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
                ******************************************************************
                LNS: Standby redo logfile selected for thread 1 sequence 13 for destination LOG_
                ARCHIVE_DEST_2
                Thread 1 advanced to log sequence 14 (LGWR switch)
                Current log# 2 seq# 14 mem# 0: /u01/app/oracle/oradata/pc00prmy/redo02.log
                ******************************************************************
                LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
                ******************************************************************
                Sun May 06 14:15:51 2012
                Archived Log entry 10 added for thread 1 sequence 13 ID 0xcd8de2c6 dest 1:
                LNS: Standby redo logfile selected for thread 1 sequence 14 for destination LOG_
                ARCHIVE_DEST_2
                Thread 1 cannot allocate new log, sequence 15
                Checkpoint not complete
                Current log# 2 seq# 14 mem# 0: /u01/app/oracle/oradata/pc00prmy/redo02.log
                Thread 1 advanced to log sequence 15 (LGWR switch)
                Current log# 3 seq# 15 mem# 0: /u01/app/oracle/oradata/pc00prmy/redo03.log
                Sun May 06 14:15:52 2012
                Archived Log entry 12 added for thread 1 sequence 14 ID 0xcd8de2c6 dest 1:
                ******************************************************************
                LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
                ******************************************************************
                LNS: Standby redo logfile selected for thread 1 sequence 15 for destination LOG_
                ARCHIVE_DEST_2
                Sun May 06 14:22:08 2012
                Thread 1 advanced to log sequence 16 (LGWR switch)
                Current log# 1 seq# 16 mem# 0: /u01/app/oracle/oradata/pc00prmy/redo01.log
                Sun May 06 14:22:08 2012
                ******************************************************************
                LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
                ******************************************************************
                Sun May 06 14:22:08 2012
                Archived Log entry 14 added for thread 1 sequence 15 ID 0xcd8de2c6 dest 1:
                [oracle@edt3r17p0 oracle]$

                This is on Standby DB


                2012-05-06 20:45:33.636000 +07:00
                RFS[402]: Assigned to RFS process 5507
                RFS[402]: Identified database type as 'physical standby': Client is ARCH pid 4251
                2012-05-06 20:46:33.861000 +07:00
                RFS[403]: Assigned to RFS process 5511
                RFS[403]: Identified database type as 'physical standby': Client is ARCH pid 4251
                2012-05-06 20:47:34.060000 +07:00
                RFS[404]: Assigned to RFS process 5519
                RFS[404]: Identified database type as 'physical standby': Client is ARCH pid 4251
                2012-05-06 20:48:34.261000 +07:00
                RFS[405]: Assigned to RFS process 5523
                RFS[405]: Identified database type as 'physical standby': Client is ARCH pid 4251
                2012-05-06 20:49:34.466000 +07:00
                RFS[406]: Assigned to RFS process 5527
                RFS[406]: Identified database type as 'physical standby': Client is ARCH pid 4251
                2012-05-06 20:50:34.663000 +07:00
                RFS[407]: Assigned to RFS process 5531
                RFS[407]: Identified database type as 'physical standby': Client is ARCH pid 4251
                2012-05-06 20:51:34.856000 +07:00
                RFS[408]: Assigned to RFS process 5535
                RFS[408]: Identified database type as 'physical standby': Client is ARCH pid 4251
                2012-05-06 20:52:35.119000 +07:00
                RFS[409]: Assigned to RFS process 5546
                RFS[409]: Identified database type as 'physical standby': Client is ARCH pid 4251
                2012-05-06 20:53:35.318000 +07:00
                RFS[410]: Assigned to RFS process 5552
                RFS[410]: Identified database type as 'physical standby': Client is ARCH pid 4251
                • 5. Re: Redo log file seem not to transfer to standby database!
                  mseberg
                  OK;

                  ORA-16058 - generally this is a listener error on the standby database.

                  Check my listener.ora example here :

                  http://www.visi.com/~mseberg/data_guard_on_oracle_11_step_by_step.html ( also a good compare of your setup )

                  Use lsnrctl status on the standby to double check everything.

                  If if change the listener stop and restart it.

                  Most likely you were right from the get go and this is a TRANSPORT issue and is caused by the Standby listener.

                  I have a another example here : ( shorter )


                  http://www.visi.com/~mseberg/Data_Guard_Listener_Example.html

                  Would also double check the listener GLOBAL_DBNAME setting on the standby.

                  If that does not work and you think the listener is not the issue, stop apply and restart the database and apply.

                  Have something I have to do, but will check back later.

                  Best Regards

                  mseberg