1 2 Previous Next 25 Replies Latest reply: Aug 16, 2013 10:04 AM by 76423b7a-ff47-4e1f-bd68-5bd592e956d9 RSS

    ORA-16783: cannot resolve gap for database

    AlexAntonyArokiaraj
      I have two databases emadb and emadbdg, governed by Data Guard. emadb is currently primary. emadbdg is currently Physical Standby.

      ORA16783 - Cannot resolve gap for database is one I think, is causing the problem. Can anyone help troubleshoot the issue below. Logs attached herewith.

      Data Guard Output

      DGMGRL> show configuration verbose

      Configuration - DRSolution

      Protection Mode: MaxAvailability
      Databases:
      emadb - Primary database
      Error: ORA-16825: multiple errors or warnings, including fast-start failover-related errors or warnings, detected for the database

      emadbdg - (*) Physical standby database
      Warning: ORA-16817: unsynchronized fast-start failover configuration

      (*) Fast-Start Failover target

      Properties:
      FastStartFailoverThreshold = '30'
      OperationTimeout = '30'
      FastStartFailoverLagLimit = '30'
      CommunicationTimeout = '180'
      FastStartFailoverAutoReinstate = 'TRUE'
      FastStartFailoverPmyShutdown = 'FALSE'
      BystandersFollowRoleChange = 'ALL'

      Fast-Start Failover: ENABLED

      Threshold: 30 seconds
      Target: emadbdg
      Observer: emarn1
      Lag Limit: 30 seconds (not in use)
      Shutdown Primary: FALSE
      Auto-reinstate: TRUE

      Configuration Status:
      ERROR


      DGMGRL> show database verbose emadb

      Database - emadb

      Role: PRIMARY
      Intended State: TRANSPORT-ON
      Instance(s):
      emadb

      Database Error(s):
      ORA-16783: cannot resolve gap for database emadbdg

      Database Warning(s):
      ORA-16817: unsynchronized fast-start failover configuration

      Properties:
      DGConnectIdentifier = 'emadb'
      ObserverConnectIdentifier = ''
      LogXptMode = 'SYNC'
      DelayMins = '0'
      Binding = 'optional'
      MaxFailure = '0'
      MaxConnections = '1'
      ReopenSecs = '300'
      NetTimeout = '30'
      RedoCompression = 'DISABLE'
      LogShipping = 'ON'
      PreferredApplyInstance = ''
      ApplyInstanceTimeout = '0'
      ApplyParallel = 'AUTO'
      StandbyFileManagement = 'AUTO'
      ArchiveLagTarget = '0'
      LogArchiveMaxProcesses = '4'
      LogArchiveMinSucceedDest = '1'
      DbFileNameConvert = 'emadbdg, emadb'
      LogFileNameConvert = '/opt/app/oracle/oradata/emadbdg, /opt/app/oracle/oradata/emadb'
      FastStartFailoverTarget = 'emadbdg'
      InconsistentProperties = '(monitor)'
      InconsistentLogXptProps = '(monitor)'
      SendQEntries = '(monitor)'
      LogXptStatus = '(monitor)'
      RecvQEntries = '(monitor)'
      SidName = 'emadb'
      StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=emarn1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=emadb_DGMGRL)(INSTANCE_NAME=emadb)(SERVER=DEDICATED)))'
      StandbyArchiveLocation = '/opt/app/oracle/oradata/emadb/archivelog1'
      AlternateLocation = ''
      LogArchiveTrace = '0'
      LogArchiveFormat = '%t_%s_%r.dbf'
      TopWaitEvents = '(monitor)'

      Database Status:
      ERROR

      DGMGRL> show database verbose emadbdg

      Database - emadbdg

      Role: PHYSICAL STANDBY
      Intended State: APPLY-ON
      Transport Lag: (unknown)
      Apply Lag: (unknown)
      Real Time Query: OFF
      Instance(s):
      emadbdg

      Database Warning(s):
      ORA-16817: unsynchronized fast-start failover configuration

      Properties:
      DGConnectIdentifier = 'emadbdg'
      ObserverConnectIdentifier = ''
      LogXptMode = 'SYNC'
      DelayMins = '0'
      Binding = 'OPTIONAL'
      MaxFailure = '0'
      MaxConnections = '1'
      ReopenSecs = '300'
      NetTimeout = '30'
      RedoCompression = 'DISABLE'
      LogShipping = 'ON'
      PreferredApplyInstance = ''
      ApplyInstanceTimeout = '0'
      ApplyParallel = 'AUTO'
      StandbyFileManagement = 'AUTO'
      ArchiveLagTarget = '0'
      LogArchiveMaxProcesses = '4'
      LogArchiveMinSucceedDest = '1'
      DbFileNameConvert = 'emadb, emadbdg'
      LogFileNameConvert = '/opt/app/oracle/oradata/emadb, /opt/app/oracle/oradata/emadbdg'
      FastStartFailoverTarget = 'emadb'
      InconsistentProperties = '(monitor)'
      InconsistentLogXptProps = '(monitor)'
      SendQEntries = '(monitor)'
      LogXptStatus = '(monitor)'
      RecvQEntries = '(monitor)'
      SidName = 'emadbdg'
      StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=emarn2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=emadbdg_DGMGRL)(INSTANCE_NAME=emadbdg)(SERVER=DEDICATED)))'
      StandbyArchiveLocation = '/opt/app/oracle/oradata/emadbdg/archivelog1'
      AlternateLocation = ''
      LogArchiveTrace = '0'
      LogArchiveFormat = '%t_%s_%r.dbf'
      TopWaitEvents = '(monitor)'

      Database Status:
      WARNING

      DGMGRL>
      DGMGRL>
      DGMGRL>
      DGMGRL>
      DGMGRL> exit
        • 1. Re: ORA-16783: cannot resolve gap for database
          CKPT
          use below script by using spool from both primary and standby databases, then post results in coded format.
          http://www.oracle-ckpt.com/dataguard_troubleshoot_snapper/
          • 2. Re: ORA-16783: cannot resolve gap for database
            AlexAntonyArokiaraj
            Thank you very much for your reply. Please find the output of the required scripts from the primary db and the secondary db.

            Primary Database Script
            SQL> set feedback off
            SQL> set trimspool on
            SQL> set line 500
            SQL> set pagesize 50
            SQL> column name for a30
            SQL> column display_value for a30
            SQL> column ID format 99
            SQL> column "SRLs" format 99
            SQL> column active format 99
            SQL> col type format a4
            SQL> column ID format 99
            SQL> column "SRLs" format 99
            SQL> column active format 99
            SQL> col type format a4
            SQL> col PROTECTION_MODE for a20
            SQL> col RECOVERY_MODE for a20
            SQL> col db_mode for a15
            SQL> 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;
            
            NAME                           DISPLAY_VALUE
            ------------------------------ ------------------------------
            db_file_name_convert           emadbdg, emadb
            db_name                        emadb
            db_unique_name                 emadb
            dg_broker_config_file1         /opt/app/oracle/product/db/dbs
                                           /dr1emadb.dat
            
            dg_broker_config_file2         /opt/app/oracle/product/db/dbs
                                           /dr2emadb.dat
            
            dg_broker_start                TRUE
            fal_client                     emadb
            fal_server                     emadbdg
            local_listener
            log_archive_config             DG_CONFIG=(emadb,emadbdg)
            log_archive_dest_2             service="emadbdg", LGWR SYNC A
                                           FFIRM delay=0 optional compres
                                           sion=disable max_failure=0 max
                                           _connections=1 reopen=300 db_u
                                           nique_name="emadbdg" net_timeo
                                           ut=30, valid_for=(all_logfiles
                                           ,primary_role)
            
            log_archive_dest_state_2       ENABLE
            log_archive_max_processes      4
            log_file_name_convert          /opt/app/oracle/oradata/emadbd
                                           g, /opt/app/oracle/oradata/ema
                                           db
            
            remote_login_passwordfile      EXCLUSIVE
            standby_archive_dest           ?/dbs/arch
            standby_file_management        AUTO
            SQL> col name for a10
            SQL> col DATABASE_ROLE for a10
            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
            ---------- ------------------------------ -------------------- ---------- -------------------- --------------------
            EMADB      emadb                          MAXIMUM AVAILABILITY PRIMARY    READ WRITE           UNRESOLVABLE GAP
            SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
            
               THREAD# MAX(SEQUENCE#)
            ---------- --------------
                     1            925
            SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
              2  FROM
              3  (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
              4  (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
              5  WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
            
                Thread Last Sequence Received Last Sequence Applied Difference
            ---------- ---------------------- --------------------- ----------
                     1                     48                    48          0
                     1                     48                    48          0
            SQL> col severity for a15
            SQL> col message for a70
            SQL> col timestamp for a20
            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
            --------------- ---------- -------------------- ----------------------------------------------------------------------
            Warning                  0 26-DEC-2012 13:09:07 LGWR: Standby redo logfile selected to archive thread 1 sequence 33
            Warning               3113 26-DEC-2012 13:33:12 LGWR: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (311
                                                            3)
            
            Warning               3113 26-DEC-2012 13:33:12 LGWR: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
            Error                 3113 26-DEC-2012 13:33:12 Error 3113 for archive log file 3 to 'emadbdg'
            Error                 1041 26-DEC-2012 13:33:14 LGWR: Error 1041 disconnecting from destination LOG_ARCHIVE_DEST_2 sta
                                                            ndby host 'emadbdg'
            
            Warning               3113 26-DEC-2012 13:33:20 ARC1: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (311
                                                            3)
            
            Error                12514 26-DEC-2012 13:33:20 ARC1: Error 12514 attaching to RFS for reconnect
            Error                 3113 26-DEC-2012 13:33:20 PING[ARC1]: Error 3113 when pinging standby emadbdg.
            Error                12514 26-DEC-2012 13:39:20 PING[ARC1]: Heartbeat failed to connect to standby 'emadbdg'. Error is
                                                             12514.
            
            Error                12514 26-DEC-2012 13:40:20 PING[ARC1]: Heartbeat failed to connect to standby 'emadbdg'. Error is
                                                             12514.
            
            Error                12514 26-DEC-2012 13:41:20 PING[ARC1]: Heartbeat failed to connect to standby 'emadbdg'. Error is
                                                             12514.
            
            Error                12514 26-DEC-2012 13:42:20 PING[ARC1]: Heartbeat failed to connect to standby 'emadbdg'. Error is
                                                             12514.
            
            Error                12514 26-DEC-2012 13:43:20 PING[ARC1]: Heartbeat failed to connect to standby 'emadbdg'. Error is
                                                             12514.
            
            Error                12514 26-DEC-2012 13:44:20 PING[ARC1]: Heartbeat failed to connect to standby 'emadbdg'. Error is
                                                             12514.
            
            Error                12514 26-DEC-2012 13:45:20 PING[ARC1]: Heartbeat failed to connect to standby 'emadbdg'. Error is
                                                             12514.
            
            Error                12514 26-DEC-2012 13:46:20 PING[ARC1]: Heartbeat failed to connect to standby 'emadbdg'. Error is
                                                             12514.
            
            Error                12514 26-DEC-2012 13:47:20 PING[ARC1]: Heartbeat failed to connect to standby 'emadbdg'. Error is
                                                             12514.
            
            Error                12514 26-DEC-2012 13:48:20 PING[ARC1]: Heartbeat failed to connect to standby 'emadbdg'. Error is
                                                             12514.
            
            Warning                  0 26-DEC-2012 13:49:01 LGWR: Standby redo logfile selected to archive thread 1 sequence 34
            Warning               3113 26-DEC-2012 13:49:55 LGWR: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (311
                                                            3)
            
            SEVERITY        ERROR_CODE timestamp            MESSAGE
            --------------- ---------- -------------------- ----------------------------------------------------------------------
            
            Warning               3113 26-DEC-2012 13:49:55 LGWR: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
            Error                 3113 26-DEC-2012 13:49:55 Error 3113 for archive log file 1 to 'emadbdg'
            Error                 1041 26-DEC-2012 13:49:57 LGWR: Error 1041 disconnecting from destination LOG_ARCHIVE_DEST_2 sta
                                                            ndby host 'emadbdg'
            
            Warning               3113 26-DEC-2012 13:50:21 ARC1: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (311
                                                            3)
            
            Error                12514 26-DEC-2012 13:50:21 ARC1: Error 12514 attaching to RFS for reconnect
            Error                 3113 26-DEC-2012 13:50:21 PING[ARC1]: Error 3113 when pinging standby emadbdg.
            Warning                  0 26-DEC-2012 13:54:57 LGWR: Standby redo logfile selected to archive thread 1 sequence 35
            Warning               3113 26-DEC-2012 13:58:49 LGWR: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (311
                                                            3)
            
            Warning               3113 26-DEC-2012 13:58:49 LGWR: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
            Error                 3113 26-DEC-2012 13:58:49 Error 3113 for archive log file 2 to 'emadbdg'
            Error                 1041 26-DEC-2012 13:58:51 LGWR: Error 1041 disconnecting from destination LOG_ARCHIVE_DEST_2 sta
                                                            ndby host 'emadbdg'
            
            Warning               3113 26-DEC-2012 13:59:22 ARC1: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (311
                                                            3)
            
            Error                12514 26-DEC-2012 13:59:22 ARC1: Error 12514 attaching to RFS for reconnect
            Error                 3113 26-DEC-2012 13:59:22 PING[ARC1]: Error 3113 when pinging standby emadbdg.
            Error                12528 26-DEC-2012 14:00:38 PING[ARC1]: Heartbeat failed to connect to standby 'emadbdg'. Error is
                                                             12528.
            
            Warning                  0 26-DEC-2012 14:00:49 LGWR: Standby redo logfile selected to archive thread 1 sequence 37
            Warning               3113 26-DEC-2012 14:01:51 LGWR: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (311
                                                            3)
            
            Warning               3113 26-DEC-2012 14:01:51 LGWR: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
            Error                 3113 26-DEC-2012 14:01:51 Error 3113 for archive log file 1 to 'emadbdg'
            Error                 1041 26-DEC-2012 14:01:53 LGWR: Error 1041 disconnecting from destination LOG_ARCHIVE_DEST_2 sta
                                                            ndby host 'emadbdg'
            
            Error                12528 26-DEC-2012 14:07:24 PING[ARC1]: Heartbeat failed to connect to standby 'emadbdg'. Error is
                                                             12528.
            
            Error                12528 26-DEC-2012 14:08:24 PING[ARC1]: Heartbeat failed to connect to standby 'emadbdg'. Error is
                                                             12528.
            
            Error                12528 26-DEC-2012 14:09:24 PING[ARC1]: Heartbeat failed to connect to standby 'emadbdg'. Error is
                                                             12528.
            
            Error                12528 26-DEC-2012 14:10:24 PING[ARC1]: Heartbeat failed to connect to standby 'emadbdg'. Error is
            
            SEVERITY        ERROR_CODE timestamp            MESSAGE
            --------------- ---------- -------------------- ----------------------------------------------------------------------
                                                             12528.
            
            Error                12528 26-DEC-2012 14:11:24 PING[ARC1]: Heartbeat failed to connect to standby 'emadbdg'. Error is
                                                             12528.
            
            Error                12514 26-DEC-2012 14:12:24 PING[ARC1]: Heartbeat failed to connect to standby 'emadbdg'. Error is
                                                             12514.
            
            Warning                  0 26-DEC-2012 14:12:36 LGWR: Standby redo logfile selected to archive thread 1 sequence 39
            Warning               3113 26-DEC-2012 14:13:31 LGWR: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (311
                                                            3)
            
            Warning               3113 26-DEC-2012 14:13:31 LGWR: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
            Error                 3113 26-DEC-2012 14:13:31 Error 3113 for archive log file 3 to 'emadbdg'
            Error                 1041 26-DEC-2012 14:13:32 LGWR: Error 1041 disconnecting from destination LOG_ARCHIVE_DEST_2 sta
                                                            ndby host 'emadbdg'
            
            Warning               3113 26-DEC-2012 14:14:25 ARC1: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (311
                                                            3)
            
            Error                12514 26-DEC-2012 14:14:25 ARC1: Error 12514 attaching to RFS for reconnect
            Error                 3113 26-DEC-2012 14:14:25 PING[ARC1]: Error 3113 when pinging standby emadbdg.
            Error                12528 26-DEC-2012 14:20:25 PING[ARC1]: Heartbeat failed to connect to standby 'emadbdg'. Error is
                                                             12528.
            
            Error                12528 26-DEC-2012 14:21:25 PING[ARC1]: Heartbeat failed to connect to standby 'emadbdg'. Error is
                                                             12528.
            
            Error                12528 26-DEC-2012 14:22:25 PING[ARC1]: Heartbeat failed to connect to standby 'emadbdg'. Error is
                                                             12528.
            
            Error                12528 26-DEC-2012 14:23:09 PING[ARC1]: Heartbeat failed to connect to standby 'emadbdg'. Error is
                                                             12528.
            
            Warning              16086 26-DEC-2012 14:23:19 LGWR: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (160
                                                            86)
            
            Warning              16086 26-DEC-2012 14:23:19 LGWR: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
            Error                16086 26-DEC-2012 14:23:19 Error 16086 for archive log file 2 to 'emadbdg'
            Warning              16086 26-DEC-2012 14:28:27 LGWR: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (160
                                                            86)
            
            Warning              16086 26-DEC-2012 14:28:27 LGWR: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
            Error                16086 26-DEC-2012 14:28:27 Error 16086 for archive log file 3 to 'emadbdg'
            Error                12514 26-DEC-2012 14:34:25 PING[ARC1]: Heartbeat failed to connect to standby 'emadbdg'. Error is
                                                             12514.
            
            
            SEVERITY        ERROR_CODE timestamp            MESSAGE
            --------------- ---------- -------------------- ----------------------------------------------------------------------
            Warning                  0 26-DEC-2012 14:34:40 LGWR: Standby redo logfile selected to archive thread 1 sequence 43
            Warning               3113 26-DEC-2012 15:50:56 LGWR: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (311
                                                            3)
            
            Warning               3113 26-DEC-2012 15:50:56 LGWR: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
            Error                 3113 26-DEC-2012 15:50:56 Error 3113 for archive log file 1 to 'emadbdg'
            Error                 1041 26-DEC-2012 15:50:58 LGWR: Error 1041 disconnecting from destination LOG_ARCHIVE_DEST_2 sta
                                                            ndby host 'emadbdg'
            
            Warning               3113 26-DEC-2012 15:51:38 ARC1: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (311
                                                            3)
            
            Error                12514 26-DEC-2012 15:51:38 ARC1: Error 12514 attaching to RFS for reconnect
            Error                 3113 26-DEC-2012 15:51:38 PING[ARC1]: Error 3113 when pinging standby emadbdg.
            Warning                  0 26-DEC-2012 15:56:20 LGWR: Standby redo logfile selected to archive thread 1 sequence 44
            Warning                  0 26-DEC-2012 15:56:24 LGWR: Standby redo logfile selected to archive thread 1 sequence 45
            Warning                  0 26-DEC-2012 15:56:45 LGWR: Standby redo logfile selected to archive thread 1 sequence 46
            Warning                  0 26-DEC-2012 22:01:57 LGWR: Standby redo logfile selected to archive thread 1 sequence 47
            Warning                  0 27-DEC-2012 06:00:39 LGWR: Standby redo logfile selected to archive thread 1 sequence 48
            Warning                  0 27-DEC-2012 14:01:50 LGWR: Standby redo logfile selected to archive thread 1 sequence 49
            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            48
              2 VALID     MOUNTED-STANDBY LGWR MANAGED REAL TIME AP RESYNCHRONIZATION       4      1            48
                                               PLY
            
            SQL> column FILE_TYPE format a20
            SQL> col name format a60
            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
            ------------------------------------------------------------ ---------- ----------
            /opt/app/oracle/flash_recovery_area                               32768      25608
            SQL> spool off
            Standby Script
            SQL> set feedback off
            SQL> set trimspool on
            SQL> set line 500
            SQL> set pagesize 50
            SQL> set linesize 200
            SQL> column name for a30
            SQL> column display_value for a30
            SQL> col value for a10
            SQL> col PROTECTION_MODE for a15
            SQL> col DATABASE_Role for a15
            SQL> 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;
            
            NAME                           DISPLAY_VALUE
            ------------------------------ ------------------------------
            db_file_name_convert           emadb, emadbdg
            db_name                        emadb
            db_unique_name                 emadbdg
            dg_broker_config_file1         /opt/app/oracle/product/db/dbs
                                           /dr1emadbdg.dat
            
            dg_broker_config_file2         /opt/app/oracle/product/db/dbs
                                           /dr2emadbdg.dat
            
            dg_broker_start                TRUE
            fal_client                     emadbdg
            fal_server                     emadb
            local_listener
            log_archive_config             DG_CONFIG=(emadb,emadbdg)
            log_archive_dest_2             service="emadb", LGWR SYNC AFF
                                           IRM delay=0 optional compressi
                                           on=disable max_failure=0 max_c
                                           onnections=1 reopen=300 db_uni
                                           que_name="emadb" net_timeout=3
                                           0, valid_for=(all_logfiles,pri
                                           mary_role)
            
            log_archive_dest_state_2       ENABLE
            log_archive_max_processes      4
            log_file_name_convert          /opt/app/oracle/oradata/emadb,
                                            /opt/app/oracle/oradata/emadb
                                           dg
            
            remote_login_passwordfile      EXCLUSIVE
            standby_archive_dest           ?/dbs/arch
            standby_file_management        AUTO
            SQL> col name for a10
            SQL> col DATABASE_ROLE for a10
            SQL> SELECT name,db_unique_name,protection_mode,DATABASE_ROLE,OPEN_MODE from v$database;
            
            NAME       DB_UNIQUE_NAME                 PROTECTION_MODE DATABASE_R OPEN_MODE
            ---------- ------------------------------ --------------- ---------- --------------------
            EMADB      emadbdg                        MAXIMUM AVAILAB PHYSICAL S MOUNTED
                                                      ILITY           TANDBY
            
            SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
            
               THREAD# MAX(SEQUENCE#)
            ---------- --------------
                     1             42
            SQL> select process, status,thread#,sequence# from v$managed_standby;
            
            PROCESS   STATUS          THREAD#  SEQUENCE#
            --------- ------------ ---------- ----------
            ARCH      CLOSING               1         48
            ARCH      CONNECTED             0          0
            ARCH      CONNECTED             0          0
            ARCH      CLOSING               1         47
            RFS       IDLE                  0          0
            RFS       IDLE                  1         49
            RFS       IDLE                  0          0
            MRP0      WAIT_FOR_GAP          1         29
            RFS       IDLE                  0          0
            SQL> col name for a30
            SQL> select * from v$dataguard_stats;
            
            NAME                           VALUE      UNIT                           TIME_COMPUTED                  DATUM_TIME
            ------------------------------ ---------- ------------------------------ ------------------------------ ------------------------------
            transport lag                  +00 00:00: day(2) to second(0) interval   12/27/2012 15:00:10            12/27/2012 15:00:09
                                           00
            
            apply lag                                 day(2) to second(0) interval   12/27/2012 15:00:10
            apply finish time                         day(2) to second(3) interval   12/27/2012 15:00:10
            estimated startup time         12         second                         12/27/2012 15:00:10
            SQL> select * from v$archive_gap;
            SQL> col name format a60
            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
            ------------------------------------------------------------ ---------- ----------
            /opt/app/oracle/flash_recovery_area                               65536          0
            SQL> spool off
            • 3. Re: ORA-16783: cannot resolve gap for database
              Pinela
              hi alex,

              the gap can mean 2 things: archs can be shipped and not applied, or, not even shipped.
              are the required archivelogs on the standby filesystem?

              depending on the answer to the previous questions, you may have a transport issue (maybe connectivity), an apply issue (recovery not active) , or something more.
              this can also help
              password file in data guard envinormrnt

              check if you can tnsping the standby from the primary, and vice-versa.
              check if you can connect with sqlplus.

              br,
              jpinela.
              • 4. Re: ORA-16783: cannot resolve gap for database
                AlexAntonyArokiaraj
                Thanks for your reply Pinela

                I had done a roll forward standby database using RMAN Incremental backup from Primary database.

                I Tried to sqlplus as standby db user, however I got this error when I started the Standby DB in mount mode
                SQL> startup    
                ORACLE instance started.
                
                Total System Global Area 8351150080 bytes
                Fixed Size              2243120 bytes
                Variable Size           5419042256 bytes
                Database Buffers      2852126720 bytes
                Redo Buffers             77737984 bytes
                Database mounted.
                ORA-10458: standby database requires recovery
                ORA-01152: file 1 was not restored from a sufficiently old backup
                ORA-01110: data file 1: '/opt/app/oracle/oradata/emadbdg/system01.dbf'
                • 5. Re: ORA-16783: cannot resolve gap for database
                  Pinela
                  hi alex,
                  no problem.

                  can you describe the method you used to create the standby?

                  so the problem is the message you are getting (ora-01152) and not that you have a gap in the standby.
                  that is a future problem :)

                  if you try to start the recovery process do you get the same error?
                  SQL> recover managed standby database disconnect from session;
                  br,
                  jpinela.
                  • 6. Re: ORA-16783: cannot resolve gap for database
                    AlexAntonyArokiaraj
                    TNS Ping output from Primary DB to Standby DB
                    emadba@emarn1:~> tnsping emadbdg
                    
                    TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 27-DEC-2012 16:37:06
                    
                    Copyright (c) 1997, 2010, Oracle.  All rights reserved.
                    
                    Used parameter files:
                    
                    
                    Used TNSNAMES adapter to resolve the alias
                    Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=emarn2)(PORT=1521)) (CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=emadbdg)))
                    OK (0 msec)
                    TNS Ping from Standby DB to Primary DB
                    emadba@emarn2:~> tnsping emadb
                    
                    TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 27-DEC-2012 16:37:12
                    
                    Copyright (c) 1997, 2010, Oracle.  All rights reserved.
                    
                    Used parameter files:
                    
                    
                    Used TNSNAMES adapter to resolve the alias
                    Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=emarn1)(PORT=1521)) (CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=emadb)))
                    OK (0 msec)
                    emadba@emarn2:~> 
                    • 7. Re: ORA-16783: cannot resolve gap for database
                      AlexAntonyArokiaraj
                      This is the method I used to do the standby recovery.

                      [http://shivanandarao.wordpress.com/2012/03/26/roll-forward-physical-standby-database-using-rman-incremental-backup/]

                      The Managed Recovery Process is already active
                      ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE
                      Attempt to start background Managed Standby Recovery process (emadbdg)
                      Thu Dec 27 16:37:08 2012
                      MRP0 started with pid=36, OS id=1701 
                      MRP0: Background Managed Standby Recovery process started (emadbdg)
                       started logmerger process
                      Thu Dec 27 16:37:13 2012
                      Managed Standby Recovery starting Real Time Apply
                      Parallel Media Recovery started with 16 slaves
                      Waiting for all non-current ORLs to be archived...
                      All non-current ORLs have been archived.
                      Media Recovery Waiting for thread 1 sequence 29
                      Fetching gap sequence in thread 1, gap sequence 29-42
                      Thu Dec 27 16:37:15 2012
                      Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE
                      • 8. Re: ORA-16783: cannot resolve gap for database
                        AlexAntonyArokiaraj
                        I am able to connect to the standby db through sysdba, however not using instance name.
                        emadba@emarn2:~> sqlplus emauser/(password here)@emadbdg
                        
                        SQL*Plus: Release 11.2.0.2.0 Production on Thu Dec 27 16:46:28 2012
                        
                        Copyright (c) 1982, 2010, Oracle.  All rights reserved.
                        
                        ERROR:
                        ORA-01033: ORACLE initialization or shutdown in progress
                        Process ID: 0
                        Session ID: 0 Serial number: 0
                        
                        
                        Enter user-name: 
                        • 9. Re: ORA-16783: cannot resolve gap for database
                          AlexAntonyArokiaraj
                          Thank you

                          Now the error disappeared during starting up the database in mount state
                          emadba@emarn2:~> sqlplus / as sysdba
                          
                          SQL*Plus: Release 11.2.0.2.0 Production on Thu Dec 27 16:48:50 2012
                          
                          Copyright (c) 1982, 2010, Oracle.  All rights reserved.
                          
                          
                          Connected to:
                          Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
                          With the Partitioning, OLAP, Data Mining and Real Application Testing options
                          
                          SQL> shutdown abort
                          ORACLE instance shut down.
                          SQL> startup mount
                          ORACLE instance started.
                          
                          Total System Global Area 8351150080 bytes
                          Fixed Size              2243120 bytes
                          Variable Size           5419042256 bytes
                          Database Buffers      2852126720 bytes
                          Redo Buffers             77737984 bytes
                          Database mounted.
                          • 10. Re: ORA-16783: cannot resolve gap for database
                            Pinela
                            run this on the standby:
                            SQL> select max(sequence#) from v$archived_log where applied='YES'
                            and this on primary:
                            SQL> archive log list
                            so you did perform steps 4 (and the rest), specifically, steps 4 and 8?

                            br,
                            jpinela.
                            • 11. Re: ORA-16783: cannot resolve gap for database
                              AlexAntonyArokiaraj
                              Primary Node
                              SQL> archive log list
                              Database log mode            Archive Mode
                              Automatic archival            Enabled
                              Archive destination            /opt/app/oracle/oradata/emadb/archivelog1
                              Oldest online log sequence     65
                              Next log sequence to archive   67
                              Current log sequence            67
                              
                              SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
                              
                                 THREAD# MAX(SEQUENCE#)
                              ---------- --------------
                                    1           925
                              Standby Node
                              SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
                              
                                 THREAD# MAX(SEQUENCE#)
                              ---------- --------------
                                    1            66
                              
                              SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
                              
                                 THREAD# MAX(SEQUENCE#)
                              ---------- --------------
                                    1            42
                              2. Yes, I had done stpes 4 and 8
                              • 12. Re: ORA-16783: cannot resolve gap for database
                                AlexAntonyArokiaraj
                                Also I m getting this message in the alert file for more than a day, since I had performed this RollForward Standby DB
                                Fetching gap sequence in thread 1, gap sequence 29-42
                                Thu Dec 27 17:01:45 2012
                                Fetching gap sequence in thread 1, gap sequence 29-42
                                Thu Dec 27 17:01:56 2012
                                Fetching gap sequence in thread 1, gap sequence 29-42
                                Thu Dec 27 17:02:06 2012
                                Fetching gap sequence in thread 1, gap sequence 29-42
                                Thu Dec 27 17:02:16 2012
                                Fetching gap sequence in thread 1, gap sequence 29-42
                                • 13. Re: ORA-16783: cannot resolve gap for database
                                  AlexAntonyArokiaraj
                                  I am seeing this output in Primary DB alert log
                                  ***********************************************************************
                                  
                                  Fatal NI connect error 12514, connecting to:
                                   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=emarn2)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=emadbdg)(CID=(PROGRAM=oracle)(HOST=emarn1)(USER=emadba))))
                                  
                                    VERSION INFORMATION:
                                       TNS for Linux: Version 11.2.0.2.0 - Production
                                       TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.2.0 - Production
                                    Time: 27-DEC-2012 16:48:56
                                    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
                                  Thu Dec 27 16:49:06 2012
                                  
                                  
                                  ***********************************************************************
                                  • 14. Re: ORA-16783: cannot resolve gap for database
                                    Shivananda Rao
                                    Hello,
                                    Fatal NI connect error 12514, connecting to:
                                     (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=emarn2)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=emadbdg)(CID=(PROGRAM=oracle)(HOST=emarn1)(USER=emadba))))
                                    Are you able to connect to the standby database as shown below
                                    sqlplus sys/<pwd>@standbydb as sysdba
                                    I don't think so. Add a static entry for the standby instance in the listener.ora file on the standby server. Sample is as shown below:
                                    LISTENER =
                                      (DESCRIPTION_LIST =
                                        (DESCRIPTION =
                                          (ADDRESS = (PROTOCOL = TCP)(HOST = <Standby Hostname>)(PORT = 1521))
                                        )
                                      )
                                    
                                    SID_LIST_LISTENER=
                                     (SID_LIST=
                                      (SID_DESC=
                                        (SID_NAME=<standby SID>)
                                        (ORACLE_HOME=<ORACLE_HOME path on standby server)
                                      )
                                     )
                                    Regards,
                                    Shivananda
                                    1 2 Previous Next