This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Feb 9, 2013 5:04 AM by 794388 RSS

Automatic Gap Resolution Not  Working

794388 Newbie
Currently Being Moderated
I observed " Possible network Disconnect with primary" message in alert.log of logical standby.

Gap between archive logs are not resolved automatically since a month, although FAL_SERVER and FAL_CLIENT parameters are properly set.
As before that,was able to fetch gap automatically.
  • 1. Re: Automatic Gap Resolution Not  Working
    mseberg Guru
    Currently Being Moderated
    What error if any is in your alert log?

    Can you tnsping from primary to standby database? This could be a network issue.

    What are these set to on standby and primary?

    FAL_SERVER
    FAL_CLIENT

    Check v$dataguard_status fixed view for further information and error messages

    EX.

    select * from v$dataguard_status where timestamp > sysdate -1/24

    Or spool the output to a file

    Or

    select * from (select message from v$dataguard_status order by timestamp desc) where rownum <= 10;

    A good double check f what the standby has not received :

    SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL WHERE LOCAL.SEQUENCE# NOT IN (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND THREAD# = LOCAL.THREAD#);

    mseberg

    Edited by: mseberg on Jul 14, 2011 4:34 AM
  • 2. Re: Automatic Gap Resolution Not  Working
    794388 Newbie
    Currently Being Moderated
    MESSAGE

    Primary database is in MAXIMUM PERFORMANCE mode
    RFS[36]: Successfully opened standby log 1: '/u01/app/oracle/oradata/PROD/stby_log_5A.rdo'
    Primary database is in MAXIMUM PERFORMANCE mode
    RFS[36]: Successfully opened standby log 1: '/u01/app/oracle/oradata/PROD/stby_log_5A.rdo'
    Primary database is in MAXIMUM PERFORMANCE mode
    RFS[36]: Successfully opened standby log 1: '/u01/app/oracle/oradata/PROD/stby_log_5A.rdo'
    Primary database is in MAXIMUM PERFORMANCE mode
    RFS[36]: Successfully opened standby log 1: '/u01/app/oracle/oradata/PROD/stby_log_5A.rdo'
    Primary database is in MAXIMUM PERFORMANCE mode
    RFS[36]: Successfully opened standby log 1: '/u01/app/oracle/oradata/PROD/stby_log_5A.rdo'

    On Primary :
    FAL_SERVER : <TNS to point my physical standby>
    FAL_CLEINT : <TNS to point my primary itself>

    On Logical Standby :

    FAL_SERVER : <TNS to Point my Primary>
    FAL_CLIENT : <TNS to Point my Logical Standby itself>

    All TNS services are Pingable to each other.
    Sometimes ago it was able to solve gap automatically.
    This problem arises since last month. I need to copy log file from primary and manually registering/applying to standby.

    Thanks,
  • 3. Re: Automatic Gap Resolution Not  Working
    mseberg Guru
    Currently Being Moderated
    So v$dataguard_status shows no information? Or is that where MESSAGE came from?

    Best Regards

    mseberg
  • 4. Re: Automatic Gap Resolution Not  Working
    794388 Newbie
    Currently Being Moderated
    No,It just shows informational msg.I dont find any error msgs.

    Is is mandatory to perform registration of log file , while sql apply is stopped.

    Ex.
    Alter Database Register logical logfile '<Log file Path>'

    Is it mandatory to restart logical apply after registering logfile?..

    alter database stop logical standby apply
    and
    alter database start logical standby apply

    Thanks.
  • 5. Re: Automatic Gap Resolution Not  Working
    mseberg Guru
    Currently Being Moderated
    Not sure about the "mandatory" but that how I would do it.

    Can you provide your OS and Oracle version?

    mseberg
  • 6. Re: Automatic Gap Resolution Not  Working
    BPeasland Guru
    Currently Being Moderated
    " Possible network Disconnect with primary"
    I've had this before too when we had network issues between our primary and standby sites. FAL will initially try to get the missing log but then stop. So you'll have to query V$ARCHIVE_GAP to see which logs are missing and then manually transfer them to the standby db server. Once the missing logs are there, register them:

    alter database register logfile '/path/filename';

    The standby will then apply the logs and continue on its merry way. If this continues to be a problem, you will need to work with your network admin to resolve the issue. Previously, I had a rogue firewall causing problem and a WAN accelerator causing problems.

    HTH,
    Brian
  • 7. Re: Automatic Gap Resolution Not  Working
    CKPT Guru
    Currently Being Moderated
    What is the alert log is saying when you start MRP on logical standby database?

    SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

    check for errors in both primary & logical too.
  • 8. Re: Automatic Gap Resolution Not  Working
    794388 Newbie
    Currently Being Moderated
    Hi CKPT,

    When I hv started sql apply I found Entries on standby Like this.

    alter database start logical standby apply immediate
    Thu Jul 14 23:49:59 2011
    ALTER DATABASE START LOGICAL STANDBY APPLY (oracle)
    Thu Jul 14 23:49:59 2011
    with optional part
    IMMEDIATE
    Attempt to start background Logical Standby process
    LOGSTDBY Parameter: PRESERVE_COMMIT_ORDER = FALSE
    LOGSTDBY Parameter: LOG_AUTO_DELETE = TRUE
    LOGSTDBY Parameter: DISABLE_APPLY_DELAY =
    LOGSTDBY Parameter: REAL_TIME =
    LOGSTDBY Parameter: SYNCPOINTINTERVAL = 4294967295
    LSP0 started with pid=51, OS id=19471
    Thu Jul 14 23:49:59 2011
    Completed: alter database start logical standby apply immediate
    Thu Jul 14 23:50:00 2011
    LOGSTDBY status: ORA-16111: log mining and apply setting up
    Thu Jul 14 23:50:00 2011
    LOGMINER: Parameters summary for session# = 1
    LOGMINER: Number of processes = 3, Transaction Chunk Size = 201
    LOGMINER: Memory Size = 30M, Checkpoint interval = 150M
    LOGMINER: session# = 1, reader process P000 started with pid=52 OS id=24170
    LOGMINER: session# = 1, builder process P001 started with pid=53 OS id=24172
    LOGMINER: session# = 1, preparer process P002 started with pid=54 OS id=24174
    Thu Jul 14 23:50:00 2011
    LOGMINER: Begin mining logfile for session 1 thread 1 sequence 13051, /u01/app/oracle/archive/PROD/ARCH_PROD_13051_1_738570685.arc
    LOGSTDBY Analyzer process P003 started with pid=55 OS id=24176
    LOGSTDBY Apply process P004 started with pid=56 OS id=24178
    LOGSTDBY Apply process P006 started with pid=58 OS id=24182
    LOGSTDBY Apply process P005 started with pid=57 OS id=24180
    LOGSTDBY Apply process P007 started with pid=59 OS id=24184
    LOGSTDBY Apply process P008 started with pid=60 OS id=24186

    But i dont find any error msg/warning on Primary Regarding this event.

    Hi mseberg,

    My Oracle Version is 10.2.0.4 and Os Is RHEL 5.1.

    Hi, BPeasland      

    there is no network issue between Sites...even I can Also Tnsping my service without any issue.


    Yesterday I noticed with Parameter On My Primary init.ora regarding dg setup.


    log_archive_dest_3=
    SERVICE=LOGST LGWR ASYN
    C NET_TIMEOUT=180 MAX_CONNECTI
    ONS=3 VALID_FOR=(ONLINE_LOGFIL
    ES,PRIMARY_ROLE) DB_UNIQUE_NAM
    E=logst

    We Are Using "Async" mode and net_timeout Parameter is Set ..
    Is there any requirement of net_timeout with ASYNC mode?.

    can I Use String LIKE
    log_archive_dest_3=
    SERVICE=LOGST LGWR ASYNC REOPEN=15
    MAX_CONNECTIONS=3 VALID_FOR=(ONLINE_LOGFIL
    ES,PRIMARY_ROLE) DB_UNIQUE_NAM
    E=logst

    Thanks.

    Edited by: oramas on Jul 14, 2011 10:57 PM

    These Are the alert Logs from my Primary database :

    Thu Jul 14 22:01:34 2011
    Thread 1 cannot allocate new log, sequence 13086
    Checkpoint not complete
    Current log# 10 seq# 13085 mem# 0: /u01/app/oracle/oradata/PRI/redo10.log
    Current log# 10 seq# 13085 mem# 1: /u01/app/oracle/oradata/PRI/redo10a.log
    LGWR: Standby redo logfile selected for thread 1 sequence 13086 for destination LOG_ARCHIVE_DEST_2
    Thu Jul 14 22:01:36 2011
    Thread 1 advanced to log sequence 13086 (LGWR switch)
    Current log# 11 seq# 13086 mem# 0: /u01/app/oracle/oradata/PRI/redo11.log
    Current log# 11 seq# 13086 mem# 1: /u01/app/oracle/oradata/PRI/redo11a.log
    Thu Jul 14 22:01:43 2011
    LNS: Standby redo logfile selected for thread 1 sequence 13086 for destination LOG_ARCHIVE_DEST_3
    Thu Jul 14 22:01:46 2011
    Thread 1 cannot allocate new log, sequence 13087
    Checkpoint not complete
    Current log# 11 seq# 13086 mem# 0: /u01/app/oracle/oradata/PRI/redo11.log
    Current log# 11 seq# 13086 mem# 1: /u01/app/oracle/oradata/PRI/redo11a.log
    LGWR: Standby redo logfile selected for thread 1 sequence 13087 for destination LOG_ARCHIVE_DEST_2
    Thu Jul 14 22:01:48 2011
    Thread 1 advanced to log sequence 13087 (LGWR switch)
    Current log# 12 seq# 13087 mem# 0: /u01/app/oracle/oradata/PRI/redo12.log
    Current log# 12 seq# 13087 mem# 1: /u01/app/oracle/oradata/PRI/redo12a.log
    Thu Jul 14 22:02:01 2011
    Thread 1 cannot allocate new log, sequence 13088
    Checkpoint not complete
    Current log# 12 seq# 13087 mem# 0: /u01/app/oracle/oradata/PRI/redo12.log
    Current log# 12 seq# 13087 mem# 1: /u01/app/oracle/oradata/PRI/redo12a.log
    LGWR: Standby redo logfile selected for thread 1 sequence 13088 for destination LOG_ARCHIVE_DEST_2
    Thu Jul 14 22:02:07 2011
    Thread 1 advanced to log sequence 13088 (LGWR switch)
    Current log# 9 seq# 13088 mem# 0: /u01/app/oracle/oradata/PRI/redo09.log

    My Destination 2 is Physical Standby.As It sends file of 13086 to both destination
    And in 13087 and 13088 It cant send file to my Destination 3.w/o Giving Any Error.
    And after sometimes it starts sending files..
    This causes GAP on My logical..And it cant resolve This GAP..
    So I need to manually Copy This Files.

    Thanks

    Edited by: oramas on Jul 14, 2011 11:06 PM
  • 9. Re: Automatic Gap Resolution Not  Working
    794388 Newbie
    Currently Being Moderated
    Hi All,
    Please find Archive Log generation rate at my Primary.
    In this I have Received File 13082,but Afterwards log files are generated within second,all these files I dont receive,and have to manually copy.
    and finally i received 13091 and same problem starts and finally i received 13098.

    20:44 ARCH_PROD_13079_1_738570685.arc
    21:05 ARCH_PROD_13080_1_738570685.arc
    21:35 ARCH_PROD_13081_1_738570685.arc
    22:00 ARCH_PROD_13082_1_738570685.arc
    *22:01 ARCH_PROD_13083_1_738570685.arc*
    *22:01 ARCH_PROD_13084_1_738570685.arc*
    *22:01 ARCH_PROD_13085_1_738570685.arc*
    *22:01 ARCH_PROD_13086_1_738570685.arc*
    *22:02 ARCH_PROD_13087_1_738570685.arc*
    *22:02 ARCH_PROD_13088_1_738570685.arc*
    *22:02 ARCH_PROD_13089_1_738570685.arc*
    *22:05 ARCH_PROD_13090_1_738570685.arc*
    22:16 ARCH_PROD_13091_1_738570685.arc
    *22:17 ARCH_PROD_13092_1_738570685.arc*
    *22:17 ARCH_PROD_13093_1_738570685.arc*
    *22:17 ARCH_PROD_13094_1_738570685.arc*
    *22:17 ARCH_PROD_13095_1_738570685.arc*
    *22:17 ARCH_PROD_13096_1_738570685.arc*
    *22:17 ARCH_PROD_13097_1_738570685.arc*
    22:27 ARCH_PROD_13098_1_738570685.arc
    23:00 ARCH_PROD_13099_1_738570685.arc
    23:20 ARCH_PROD_13100_1_738570685.arc
    23:55 ARCH_PROD_13101_1_738570685.arc

    Thanks.
  • 10. Re: Automatic Gap Resolution Not  Working
    CKPT Guru
    Currently Being Moderated
    Hi,

    Why that archive gap's are not resolved automatically.
    post once from standby database.
    select * from v$managed_standby;
  • 11. Re: Automatic Gap Resolution Not  Working
    794388 Newbie
    Currently Being Moderated
    PROCESS     PID     STATUS     CLIENT_PROCESS     CLIENT_PID     CLIENT_DBID     GROUP#     RESETLOG_ID     THREAD#     SEQUENCE#     BLOCK#     BLOCKS     DELAY_MINS     KNOWN_AGENTS     ACTIVE_AGENTS
    
    ARCH     2336     CLOSING       ARCH             2336          1566020426     12     692926683     1      74117          8193     2045          0          0     0
    ARCH     2338     CLOSING       ARCH             2338            1566020426     1     738570685     1      13211          8193     2045          0          0     0
    ARCH     2340     CLOSING       ARCH             2340          1566020426     1     738570685     1      13212          8193     2045          0          0     0
    ARCH     2342     CLOSING       ARCH             2342          1566020426     1     738570685     1      13210          8193     2045          0          0     0
    RFS     26135     IDLE       N/A             3698          1536390141     N/A          0     0          0                0        0             0          0      0
    RFS     26542     IDLE       LGWR             1252          1536390141     10     738570685     1      13213          4366     1          0          0     0
    Edited by: oramas on Jul 16, 2011 9:27 AM
  • 12. Re: Automatic Gap Resolution Not  Working
    CKPT Guru
    Currently Being Moderated
    oramas wrote:
    PROCESS     PID     STATUS     CLIENT_PROCESS     CLIENT_PID     CLIENT_DBID     GROUP#     RESETLOG_ID     THREAD#     SEQUENCE#     BLOCK#     BLOCKS     DELAY_MINS     KNOWN_AGENTS     ACTIVE_AGENTS
    
    ARCH     2336     CLOSING       ARCH             2336          1566020426     12     692926683     1      74117          8193     2045          0          0     0
    ARCH     2338     CLOSING       ARCH             2338            1566020426     1     738570685     1      13211          8193     2045          0          0     0
    ARCH     2340     CLOSING       ARCH             2340          1566020426     1     738570685     1      13212          8193     2045          0          0     0
    ARCH     2342     CLOSING       ARCH             2342          1566020426     1     738570685     1      13210          8193     2045          0          0     0
    RFS     26135     IDLE       N/A             3698          1536390141     N/A          0     0          0                0        0             0          0      0
    RFS     26542     IDLE       LGWR             1252          1536390141     10     738570685     1      13213          4366     1          0          0     0
    Edited by: oramas on Jul 16, 2011 9:27 AM
    why the client process is ARCH for some and LGWR for some of them?

    have you changed any mode recently?
    can you bounce the standby once and start MRP?
  • 13. Re: Automatic Gap Resolution Not  Working
    794388 Newbie
    Currently Being Moderated
    Hi CKPT,


    No, I had not changed mode,its running in maximum performance mode from creation.

    I have Once bounced database when these problem raised for first time, a month ago.

    I can Plan for again for that.

    Is there any role of NET_TIMEOUT in these issue?.
    Should I remove that?.
    And My Standby Is Logical Standby.

    Thanks

    Edited by: oramas on Jul 17, 2011 7:40 AM
  • 14. Re: Automatic Gap Resolution Not  Working
    CKPT Guru
    Currently Being Moderated
    What are the changes you made in log_archive_dest_2 parameters after bounce.

    you can change these parameter even in database is open no need to bounce.
    But i was just curious to know why the client_process for some threads is LGWR and for some is ARCH.

    If you bounce and start MRP, later it will be easier to diagnosis further and also change post all the standby configuration parameters here.

    Thanks
1 2 Previous Next

Legend

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