8 Replies Latest reply: Jan 2, 2014 1:59 AM by Viren Patel RSS

    ORA-16724: cannot resolve gap for one or more standby databases

    vk82

      Hi All,

       

      OS:- RHEL5

      DB:-11GR2

       

      Primary DB Name:- abc

      Standby DB Name:- xyz

       

       

      While trying to configure the dgmgrl

       

      DGMGRL> connect sys/oracle

      Connected.

      DGMGRL> show configuration

      Configuration      -     DGConfig1

      Protection Mode: Max Performance

      Database:

      abc     -     Primary database

      xyz     -     Physical standby database

       

      Fast -Start Failover: DISABLED

      Configuration Status:

      DISABLED

       

      DGMGRL> enable configuration

      Enabled

      DGMGRL> show configuration

       

      Configuration  - DGConfig1

       

           Protection Mode: MaxPerformance

           Databases:

           abc     -     Primary database

                Error:     ORA-16724: cannot resolve gap for one or more standby databases

           xyz     -     Phsyical standby database

                Error:     ORA-16700:the standby database has diverged from the primary database

       

      Fast-Start Failover:     DISABLED

      Configuration Status:

      ERROR

       

       

      On primary site:

       

      SQL> SELECT CURRENT_SCN FROM V$DATABASE;

       

      CURRENT_SCN

      ______________

      1447457

       

       

       

      On standby site:-

       

      SQL> SELECT CURRENT_SCN FROM V$DATABASE;

       

      CURRENT_SCN

      ______________

      1395640

       

       

      Can anyone of you help me to troubleshoot this problem and tell me the concept behind this why this error occurs.

        • 1. Re: ORA-16724: cannot resolve gap for one or more standby databases
          CKPT

               Protection Mode: MaxPerformance

               Databases:

               abc     -     Primary database

                    Error:     ORA-16724: cannot resolve gap for one or more standby databases

               xyz     -     Phsyical standby database

                    Error:     ORA-16700:the standby database has diverged from the primary database

           

           

          have you performed failover recnely ? check below query from both primary and standby database.

          SQL> select resetlogs_Change# from v$database;

           

          If you are sure that resetlogs_change# is same, you can fix GAP between primary and standby database? If the gap seqeunces are available on primary then go ahead to troubleshoot why it is not transferring (or) share the alert log or findings from you what are the causes.

           

          If the GAP is huge, then i suggest you to perform incremental backup to perform recovery on standby(rolling forward) uisng below URL

          http://www.oracle-ckpt.com/rman-incremental-backups-to-roll-forward-a-physical-standby-database-2/

          • 2. Re: ORA-16724: cannot resolve gap for one or more standby databases
            vk82

            My primary database alert log file looks as:-

            Tue Dec 31 23:53:33 2013

            ORA-16198: LGWR received timedout error from KSR

            Errors in file /u01/app/oracle/diag/rdbms/noida/noida/trace/noida_lgwr_5163.trc:

            ORA-16198: Timeout incurred on internal channel during remote archival

            Error 16198 for archive log file 2 to 'ggn'

            Destination LOG_ARCHIVE_DEST_2 no longer supports SYNCHRONIZATION

            LGWR: Failed to archive log 2 thread 1 sequence 71 (16198)

            Tue Dec 31 23:53:38 2013

            AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail

            Thread 1 advanced to log sequence 71 (LGWR switch)

              Current log# 2 seq# 71 mem# 0: +DATA/noida/onlinelog/group_2.269.835150637

              Current log# 2 seq# 71 mem# 1: +FRA/noida/onlinelog/group_2.262.835150641

            Tue Dec 31 23:53:38 2013

            Using STANDBY_ARCHIVE_DEST parameter default value as +FRA/noida/onlinelog/

            ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;

            Tue Dec 31 23:53:39 2013

            Archived Log entry 94 added for thread 1 sequence 70 ID 0x5eb799d8 dest 1:

            Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED

            Tue Dec 31 23:53:41 2013

            NSS2 started with pid=69, OS id=12485

            Tue Dec 31 23:54:11 2013

            ORA-16198: LGWR received timedout error from KSR

            Errors in file /u01/app/oracle/diag/rdbms/noida/noida/trace/noida_lgwr_5163.trc:

            ORA-16198: Timeout incurred on internal channel during remote archival

            Error 16198 for archive log file 3 to 'ggn'

            Destination LOG_ARCHIVE_DEST_2 no longer supports SYNCHRONIZATION

            LGWR: Failed to archive log 3 thread 1 sequence 72 (16198)

            Tue Dec 31 23:54:16 2013

            AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail

            Thread 1 advanced to log sequence 72 (LGWR switch)

              Current log# 3 seq# 72 mem# 0: +DATA/noida/onlinelog/group_3.270.835150645

              Current log# 3 seq# 72 mem# 1: +FRA/noida/onlinelog/group_3.263.835150651

            Tue Dec 31 23:54:16 2013

            Expanded controlfile section 11 from 94 to 188 records

            Requested to grow by 94 records; added 3 blocks of records

            Archived Log entry 95 added for thread 1 sequence 71 ID 0x5eb799d8 dest 1:

            Tue Dec 31 23:54:24 2013

            ARC3: Standby redo logfile selected for thread 1 sequence 70 for destination LOG_ARCHIVE_DEST_2

            Tue Dec 31 23:59:38 2013

            Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED

            Tue Dec 31 23:59:38 2013

            NSS2 started with pid=72, OS id=12703

            Wed Jan 01 00:00:01 2014

            LGWR: Standby redo logfile selected for thread 1 sequence 73 for destination LOG_ARCHIVE_DEST_2

            Thread 1 advanced to log sequence 73 (LGWR switch)

              Current log# 1 seq# 73 mem# 0: +DATA/noida/onlinelog/group_1.268.835150625

              Current log# 1 seq# 73 mem# 1: +FRA/noida/onlinelog/group_1.261.835150631

            Wed Jan 01 00:00:01 2014

            Archived Log entry 97 added for thread 1 sequence 72 ID 0x5eb799d8 dest 1:

            Wed Jan 01 00:00:21 2014

            ARC6: Standby redo logfile selected for thread 1 sequence 72 for destination LOG_ARCHIVE_DEST_2

            Wed Jan 01 00:28:16 2014

            ORA-16198: LGWR received timedout error from KSR

            LGWR: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (16198)

            LGWR: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned

            Errors in file /u01/app/oracle/diag/rdbms/noida/noida/trace/noida_lgwr_5163.trc:

            ORA-16198: Timeout incurred on internal channel during remote archival

            Error 16198 for archive log file 1 to 'ggn'

            Wed Jan 01 00:29:22 2014

            LGWR: Failed to archive log 1 thread 1 sequence 73 (16198)

            Thread 1 advanced to log sequence 74 (LGWR switch)

              Current log# 2 seq# 74 mem# 0: +DATA/noida/onlinelog/group_2.269.835150637

              Current log# 2 seq# 74 mem# 1: +FRA/noida/onlinelog/group_2.262.835150641

            Wed Jan 01 00:29:24 2014

            Archived Log entry 99 added for thread 1 sequence 73 ID 0x5eb799d8 dest 1:

            Wed Jan 01 00:34:41 2014

            NSS2 started with pid=69, OS id=13244

            Wed Jan 01 00:35:05 2014

            LGWR: Standby redo logfile selected for thread 1 sequence 75 for destination LOG_ARCHIVE_DEST_2

            Thread 1 advanced to log sequence 75 (LGWR switch)

              Current log# 3 seq# 75 mem# 0: +DATA/noida/onlinelog/group_3.270.835150645

              Current log# 3 seq# 75 mem# 1: +FRA/noida/onlinelog/group_3.263.835150651

            Wed Jan 01 00:35:05 2014

            Archived Log entry 100 added for thread 1 sequence 74 ID 0x5eb799d8 dest 1:

            Wed Jan 01 00:35:25 2014

            ARC5: Standby redo logfile selected for thread 1 sequence 74 for destination LOG_ARCHIVE_DEST_2

             

             

             

            My Standby alert logfile looks as below:-

             

            Tue Dec 31 23:59:37 2013

            RFS[4]: Assigned to RFS process 11596

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

            Wed Jan 01 00:00:01 2014

            RFS[5]: Assigned to RFS process 11611

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

            Primary database is in MAXIMUM PERFORMANCE mode

            RFS[5]: Selected log 4 for thread 1 sequence 73 dbid 1589093083 branch 835150625

            Wed Jan 01 00:00:07 2014

            Fetching gap sequence in thread 1, gap sequence 2-71

            Wed Jan 01 00:00:21 2014

            RFS[6]: Assigned to RFS process 11634

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

            RFS[6]: Selected log 5 for thread 1 sequence 72 dbid 1589093083 branch 835150625

            Wed Jan 01 00:00:23 2014

            Archived Log entry 33 added for thread 1 sequence 72 ID 0x5eb799d8 dest 1:

            Wed Jan 01 00:00:28 2014

            FAL[client]: Failed to request gap sequence

            GAP - thread 1 sequence 2-71

            DBID 1589093083 branch 835574282

            FAL[client]: All defined FAL servers have been attempted.

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

            Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization

            parameter is defined to a value that is sufficiently large

            enough to maintain adequate log switch information to resolve

            archivelog gaps.

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

            Wed Jan 01 00:05:43 2014

            db_recovery_file_dest_size of 5120 MB is 7.19% used. This is a

            user-specified limit on the amount of space that will be used by this

            database for recovery-related files, and does not reflect the amount of

            space available in the underlying filesystem or ASM diskgroup.

            Wed Jan 01 00:28:22 2014

            RFS[5]: Possible network disconnect with primary database

            Wed Jan 01 00:34:40 2014

            RFS[7]: Assigned to RFS process 12940

            RFS[7]: Identified database type as 'physical standby': Client is ARCH pid 5237

            Wed Jan 01 00:35:04 2014

            RFS[8]: Assigned to RFS process 12955

            RFS[8]: Identified database type as 'physical standby': Client is LGWR SYNC pid 5163

            Primary database is in MAXIMUM PERFORMANCE mode

            RFS[8]: Selected log 5 for thread 1 sequence 75 dbid 1589093083 branch 835150625

            Wed Jan 01 00:35:08 2014

            Fetching gap sequence in thread 1, gap sequence 2-73

            Wed Jan 01 00:35:25 2014

            RFS[9]: Assigned to RFS process 12984

            RFS[9]: Identified database type as 'physical standby': Client is ARCH pid 5243

            RFS[9]: Selected log 6 for thread 1 sequence 74 dbid 1589093083 branch 835150625

            Wed Jan 01 00:35:26 2014

            Archived Log entry 34 added for thread 1 sequence 74 ID 0x5eb799d8 dest 1:

            Wed Jan 01 00:35:29 2014

            FAL[client]: Failed to request gap sequence

            GAP - thread 1 sequence 2-73

            DBID 1589093083 branch 835574282

            FAL[client]: All defined FAL servers have been attempted.

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

            Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization

            parameter is defined to a value that is sufficiently large

            enough to maintain adequate log switch information to resolve

            archivelog gaps.

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

            • 3. Re: ORA-16724: cannot resolve gap for one or more standby databases
              CKPT

              Before going with below issue, initially i asked you to check "select resetlogs_change# from v$database" from both primary and standby databases to ensure they both are on same page.

               

              If resetlogs_change is same on both primary and standby then follow

              FAL[client]: Failed to request gap sequence

              GAP - thread 1 sequence 2-73

              DBID 1589093083 branch 835574282

              FAL[client]: All defined FAL servers have been attempted.

              So standby unable to fetch from sequence 2 to 73, Now

              1) They are avialble on primary ?

              2) If they are available and still unable to fetch? then use below script for further troubleshooting. Post output from both primary and standbyd atabases.

              http://www.oracle-ckpt.com/dataguard_troubleshoot_snapper/

               

              3) If archives not avilable on primary then you have to perform incremental roll forward technique

              http://www.oracle-ckpt.com/rman-incremental-backups-to-roll-forward-a-physical-standby-database-2/

               

              But please follow step by step what i have written.

              • 4. Re: ORA-16724: cannot resolve gap for one or more standby databases
                vk82

                Thanks for your reply

                 

                On my primary site when i issue the command mention on the blog i will get the below result:-

                 

                set feedback off

                set trimspool on

                set line 500

                set pagesize 50

                column name for a30

                column display_value for a30

                column ID format 99

                column "SRLs" format 99

                column active format 99

                col type format a4

                column ID format 99

                column "SRLs" format 99

                column active format 99

                col type format a4

                col PROTECTION_MODE for a20

                col RECOVERY_MODE for a20

                col db_mode for a15

                SELECT name, display_value FROM v$parameter WHERE name IN ('db_name','db_unique_name','log_archive_config','log_archive_dest_2','log_archive_dest_state_2','fal_client','fal_server','standby_file_management','standby_archive_dest','db_file_name_convert','log_file_name_convert','remote_login_passwordfile','local_listener','dg_broker_start','dg_broker_config_file1','dg_broker_config_file2','log_archive_max_processes') order by name;

                 

                 

                 

                OUTPUT:-

                 

                NAME                     DISPLAY_VALUE

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

                db_file_name_convert     +DATA1/ggn/datafile/, +DATA/no
                ida/datafile/

                 

                db_name                  noida
                db_unique_name           noida
                dg_broker_config_file1   /u01/app/oracle/product/11.2.0
                /dbhome_1/dbs/dr1noida.dat

                 

                dg_broker_config_file2   /u01/app/oracle/product/11.2.0
                /dbhome_1/dbs/dr2noida.dat

                 

                dg_broker_start          TRUE

                fal_client

                fal_server               GGN

                local_listener

                log_archive_config       dg_config=(noida,ggn)
                log_archive_dest_2       service="ggn", LGWR SYNC AFFIR
                M delay=0 OPTIONAL compression
                =DISABLE max_failure=0 max_con
                nections=1   reopen=300 db_uni
                que_name="ggn" net_timeout=30
                valid_for=(online_logfile,pri
                mary_role)

                 

                log_archive_dest_state_2 ENABLE
                log_archive_max_processes30
                log_file_name_convert    +DATA/GGN/ONLINELOG/, +DATA/NO
                IDA/ONLINELOG

                 

                remote_login_passwordfileEXCLUSIVE
                standby_archive_dest     ?/dbs/arch
                standby_file_management 

                AUTO

                 

                 

                 

                 

                 

                 

                 

                 

                 

                 

                 

                 

                 

                SQL> SELECT name,db_unique_name,protection_mode,DATABASE_ROLE,OPEN_MODE,switchover_status from v$database;

                 

                NAME       DB_UNIQUE_NAME                 PROTECTION_MODE      DATABASE_R OPEN_MODE            SWITCHOVER_STATUS

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

                NOIDA      noida                          MAXIMUM PERFORMANCE  PRIMARY    READ WRITE           FAILED DESTINATION

                 

                 

                SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

                 

                   THREAD# MAX(SEQUENCE#)

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

                         1             77

                 

                 

                SQL> select severity,error_code,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') "timestamp" , message from v$dataguard_status where dest_id=2;

                 

                SEVERITY        ERROR_CODE timestamp            MESSAGE

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

                Error                16198 01-JAN-2014 03:55:49 Error 16198 for archive log file 3 to 'ggn'

                Error                 1034 01-JAN-2014 03:56:32 PING[ARC2]: Heartbeat failed to connect to standby 'ggn'. Error is 103

                 

                 

                 

                SQL> select ds.dest_id id

                  2  , ad.status

                  3  , ds.database_mode db_mode

                  4  , ad.archiver type

                  5  , ds.recovery_mode

                  6  , ds.protection_mode

                  7  , ds.standby_logfile_count "SRLs"

                  8  , ds.standby_logfile_active active

                  9  , ds.archived_seq#

                10  from v$archive_dest_status ds

                11  , v$archive_dest ad

                12  where ds.dest_id = ad.dest_id

                13  and ad.status != 'INACTIVE'

                14  order by

                15  ds.dest_id;

                 

                ID STATUS    DB_MODE         TYPE RECOVERY_MODE        PROTECTION_MODE      SRLs ACTIVE ARCHIVED_SEQ#

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

                  1 VALID     OPEN            ARCH IDLE                 MAXIMUM PERFORMANCE     0      0            77

                  2 ERROR     UNKNOWN         LGWR IDLE                 MAXIMUM PERFORMANCE     0      0             0

                 

                 

                SQL> select    name

                  2  ,    floor(space_limit / 1024 / 1024) "Size MB"

                  3  ,    ceil(space_used  / 1024 / 1024) "Used MB"

                  4  from    v$recovery_file_dest

                  5  order by name;

                 

                NAME                                                            Size MB    Used MB

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

                +FRA                                                               5120        164

                 

                 

                 

                 

                 

                I check the alert log file i am getting below error in alert log file:-

                 

                  VERSION INFORMATION:

                        TNS for Linux: Version 11.2.0.1.0 - Production

                        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production

                  Time: 01-JAN-2014 04:30:26

                  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 12514, connecting to:

                (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host2.oracle.com)(PORT=12001)))(CONNECT_DATA=(SERVICE_NAME=ggn_DGB)(CID=(PROGRAM=oracle)(HOST=host1.oracle.com)(USER=oracle))))

                 

                  VERSION INFORMATION:

                        TNS for Linux: Version 11.2.0.1.0 - Production

                        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production

                  Time: 01-JAN-2014 04:30:26

                  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 12514, connecting to:

                (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host2.oracle.com)(PORT=12001)))(CONNECT_DATA=(SERVICE_NAME=ggn_DGB)(CID=(PROGRAM=oracle)(HOST=host1.oracle.com)(USER=oracle))))

                 

                  VERSION INFORMATION:

                        TNS for Linux: Version 11.2.0.1.0 - Production

                        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production

                  Time: 01-JAN-2014 04:30:26

                  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

                 

                 

                but whe i checked by tnsping i am getting the connectivity

                • 5. Re: ORA-16724: cannot resolve gap for one or more standby databases
                  CKPT

                  Again you missed my first question, If you cant provide its very difficult to track the errors step by step. Please provide resetlogs_change# from both database, this request is 3rd time.

                  Fatal NI connect error 12514, connecting to:

                  From primary to standby unable to establish connection, So from Standby listener there are no services either registered (or) the standby is unavailable.

                   

                  If standby is up at least in mount and listener also up, then try to register instance with listener by using (SQL> alter system register) or

                  you can have static listener entry.

                  You can check here  http://edstevensdba.wordpress.com/2011/03/19/ora-12514/

                  • 6. Re: ORA-16724: cannot resolve gap for one or more standby databases
                    vk82

                    Thanks CKPT for your reply

                     

                    On primary site:-

                     

                    SQL> SELECT RESETLOGS_CHANGE# FROM V$DATABASE;

                     

                    RESETLOGS_CHANGE#

                    ______________________

                     

                              754488

                     

                     

                    On standby site:-

                     

                    SQL> SELECT RESETLOGS_CHANGE# FROM V$DATABASE;

                     

                    RESETLOGS_CHANGE#

                    ______________________

                     

                              754488

                     

                     

                    But what exactly i am able to see now is in the alert log file on both machines

                     

                    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

                     

                     

                    But when i check the archive logs this time, now they are transported and applied as well

                     

                    on the standby

                     

                    and i am able to ping both db from primary machine

                    but from standby machine i am only able to ping standby database not primary database

                     

                    i am getting error:-

                     

                    ORA-12543: TNS:destination host unreacheable

                    • 7. Re: ORA-16724: cannot resolve gap for one or more standby databases
                      CKPT

                      ORA-12543: TNS:destination host unreacheable

                      Might unable to ping the host, can you use IP address instead of hostname in oracle net service and try again?

                      • 8. Re: ORA-16724: cannot resolve gap for one or more standby databases
                        Viren Patel

                        Hi,

                         

                        ORA-16724: cannot resolve gap for one or more standby databases error says that stand by database can not find archive log to recover database to match primary database.

                        May be stand by archive log corrupt OR may be deleted.

                         

                        Your stand by DB is 51817 logs behind

                         

                        Resolution for this is

                        If you find the missing archive logs in your previous primary database backup, Extract those archive logs and provide to stand by database.

                        OR

                        Take a fresh backup of primary and create new standby.

                         

                        Viren