This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Jan 29, 2013 2:33 PM by 509471 RSS

Archive log gap is created is standby when ever audit trail is set to DB

940147 Newbie
Currently Being Moderated
Hi

I am a new dba. I am facing a problem at production server that whenever audit_trail parameter is set to db , archive log gap is created at the standby site.

My database version is 10.2.0.4
Os is windows 2003 R2

Audit_trail parameter is set to db only in primary site, after setting the parameter as db when I bounced the database and switched the logfile , archive log gap is created in the standby..I am using LGWR mode of log transport.

Is there any relation beteen audit_trail and log transport ?
Please note that my archive log location of both the sites has sufficient disk space and the drive is working fine.Also my primary and standby is in WAN.

Please help me in this.Any help will be highly appreciated.

Here a trace file which may be helpful to give any opinion.


Dump file d:\oracle\admin\sbiofac\bdump\sbiofac_lns1_6480.trc
Tue Jun 05 13:46:02 2012
ORACLE V10.2.0.4.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows Server 2003 Version V5.2 Service Pack 2
CPU : 2 - type 586, 1 Physical Cores
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:16504M/18420M, Ph+PgF:41103M/45775M, VA:311M/2047M
Instance name: sbiofac

Redo thread mounted by this instance: 1

Oracle process number: 21

Windows thread id: 6480, image: ORACLE.EXE (LNS1)


*** SERVICE NAME:() 2012-06-05 13:46:02.703
*** SESSION ID:(534.1) 2012-06-05 13:46:02.703
*** 2012-06-05 13:46:02.703 58902 kcrr.c
LNS1: initializing for LGWR communication
LNS1: connecting to KSR channel
Success
LNS1: subscribing to KSR channel
Success
*** 2012-06-05 13:46:02.750 58955 kcrr.c
LNS1: initialized successfully ASYNC=1
Destination is specified with ASYNC=61440
*** 2012-06-05 13:46:02.875 73045 kcrr.c
Sending online log thread 1 seq 2217 [logfile 1] to standby
Redo shipping client performing standby login
*** 2012-06-05 13:46:03.656 66535 kcrr.c
Logged on to standby successfully
Client logon and security negotiation successful!
Archiving to destination sbiofacdr ASYNC blocks=20480
Allocate ASYNC blocks: Previous blocks=0 New blocks=20480
Log file opened [logno 1]
*** 2012-06-05 13:46:44.046
Error 272 writing standby archive log file at host 'sbiofacdr'
ORA-00272: error writing archive log
*** 2012-06-05 13:46:44.078 62692 kcrr.c
LGWR: I/O error 272 archiving log 1 to 'sbiofacdr'
*** 2012-06-05 13:46:44.078 60970 kcrr.c
kcrrfail: dest:2 err:272 force:0 blast:1
*** 2012-06-05 13:47:37.031
*** 2012-06-05 13:47:37.031 73045 kcrr.c
Sending online log thread 1 seq 2218 [logfile 2] to standby
*** 2012-06-05 13:47:37.046 73221 kcrr.c
Shutting down [due to no more ASYNC destination]
Redo Push Server: Freeing ASYNC PGA buffer
LNS1: Doing a channel reset for next time around...
  • 1. Re: Archive log gap is created is standby when ever audit trail is set to DB
    mseberg Guru
    Currently Being Moderated
    Hello;

    Expected. This is an Oracle 10 issue. In Oracle 11 this is fixed, Oracle is smart enough to handle it.

    Don't set the audit trail to DB in Oracle 10. The conflict is Oracle 10 does not handle this in a reader database.

    Best Regards

    mseberg
  • 2. Re: Archive log gap is created is standby when ever audit trail is set to DB
    mseberg Guru
    Currently Being Moderated
    Hello again

    Here an old thread which give more details

    startup problem with an standby database

    Best Regards

    mseberg

    Edited by: mseberg on Jun 6, 2012 7:40 AM
  • 3. Re: Archive log gap is created is standby when ever audit trail is set to DB
    940147 Newbie
    Currently Being Moderated
    Can you explain it a little more detail so that I can give a proper proof , that this is happening due to 10g bug.
  • 4. Re: Archive log gap is created is standby when ever audit trail is set to DB
    mseberg Guru
    Currently Being Moderated
    Hello again;

    Sorry for the delay. I have this thread where "Dr. Paranoid" the product manager for Data Guard makes several comments on this subject :



    Active Data Guard and SYS.AUD$

    Best Regards

    mseberg
  • 5. Re: Archive log gap is created is standby when ever audit trail is set to DB
    940147 Newbie
    Currently Being Moderated
    Hii

    I am not opening the standby in read only mode. I am keeping it in mount mode only . I believe this should not make the database to write audit record at the standby site .

    Also please note , that I am not setting audit_trail in standby at all. I am only setting it to primary . So as per architecture , audit records should be written to standby database through LGWR transport service .

    Please help .


    Thanks ..
  • 6. Re: Archive log gap is created is standby when ever audit trail is set to DB
    mseberg Guru
    Currently Being Moderated
    Right you situtation is different but the cause appears to be the same.

    Can you change the AUDIT_TRAIL initialization parameter to OS?

    Keep in mind the DB setting will come back to haunt you if you ever do a switchover.

    Any chance of upgrading to Oracle 11?

    Best Regards

    mseberg
  • 7. Re: Archive log gap is created is standby when ever audit trail is set to DB
    940147 Newbie
    Currently Being Moderated
    Hi ,

    No chance of upgradation as of now , I have to make it work keeping audit_trail as db . While performing switch over I will set the parameter as required. But the main issue has to be resolved first .

    Is there a way to monitor packet size of oracle . Or may be , packet tracing in SQL net , so that I can generate some logs that can tell me , exactly where the packets are getting stuck .


    Please help .

    Thanks ..
  • 8. Re: Archive log gap is created is standby when ever audit trail is set to DB
    mseberg Guru
    Currently Being Moderated
    OK, for you primary can you post these parameter settings?

    FAL_SERVER
    FAL_CLIENT
    STANDBY_FILE_MANAGEMENT
    DB_FILE_NAME_CONVERT
    LOG_FILE_NAME_CONVERT
    log_archive_dest_1
    log_archive_dest_2
    LOG_ARCHIVE_DEST_STATE_1
    LOG_ARCHIVE_DEST_STATE_2
    LOG_ARCHIVE_MAX_PROCESSES

    Also this setting from your standby

    STANDBY_FILE_MANAGEMENT

    In theory you should not have an issue until you try to write to the Read_Only. So I'm wondering about STANDBY_FILE_MANAGEMENT causing an issue.

    For example if there is insufficient space on the Standby for Audit and STANDBY_FILE_MANAGEMENT is set to MANUAL there this might explain what is occuring.

    I would thin the alert log on the Standby would give us a clue.



    Best Regards

    mseberg

    Edited by: mseberg on Jun 6, 2012 8:22 AM
  • 9. Re: Archive log gap is created is standby when ever audit trail is set to DB
    940147 Newbie
    Currently Being Moderated
    Hi ,


    This is setting for my primary

    FAL_SERVER=ofacdr
    FAL_CLIENT=ofac
    STANDBY_FILE_MANAGEMENT=AUTO
    DB_FILE_NAME_CONVERT= M:\oracle\oradata\ofac\, O:\oracle\oradata\ofac\
    LOG_FILE_NAME_CONVERT=M:\oracle\oradata\ofac\, O:\oracle\oradata\ofac\
    log_archive_dest_1=location=N:\oracle\ofac\archive\ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=ofac
    log_archive_dest_2=SERVICE=ofacdr LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=ofacdr
    LOG_ARCHIVE_DEST_STATE_1=ENABLE
    LOG_ARCHIVE_DEST_STATE_2=ENABLE
    LOG_ARCHIVE_MAX_PROCESSES=8


    When I am checking achive processes status by issuing this query

    select process,status from v$managed_standby;

    It is showing that the LNS process is stopping which should be in the writing state.

    I have posted the trace file output regarding this.

    Please help.


    Thanks,
  • 10. Re: Archive log gap is created is standby when ever audit trail is set to DB
    mseberg Guru
    Currently Being Moderated
    OK

    Great details thanks!!

    Are The SDU/TDU settings are configured in the Oracle Net files on both primary and standby ? I will see if I have an example.

    The parameters appear fine.

    There was an Oracle document 386417.1 on this, I have not double checked if its still available. ( CHECK - Oracle 9 but worth a galance )

    Will Check and post here.

    I have these listed too. ( Will check all three and see if they still exist )

    When to modify, when not to modify the Session data unit (SDU) [ID 99715.1] ( CHECK - still there but very old )
    SQL*Net Packet Sizes (SDU & TDU Parameters) [ID 44694.1] ( CHECK - Best by far WOULD REVIEW FIRST )

    Any chance your firewall limit the Packet size?


    Best Regards

    mseberg

    Edited by: mseberg on Jun 6, 2012 12:36 PM

    Edited by: mseberg on Jun 6, 2012 12:43 PM


    Additional document

    The relation between MTU (Maximum Transmission Unit) and SDU (Session Data Unit) [ID 274483.1]

    Edited by: mseberg on Jun 6, 2012 12:50 PM


    Still later

    Not sure if this helps but I played around will this on Oracle 11 a little, here that example:
    # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/network/admin/listener.ora
    # Generated by Oracle configuration tools.
    
     
    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = yourdomain.com)(PORT = 1521))
        )
      )
    
    
    SID_LIST_LISTENER = (SID_LIST =(SID_DESC =(SID_NAME = STANDBY)
                          (ORACLE_HOME = /u01/app/oracle/product/11.2.0)
                          (SDU=32767)
                          (GLOBAL_DBNAME = STANDBY_DGMGRL.yourdomain.com)))    
     
    
    ADR_BASE_LISTENER = /u01/app/oracle
    
    
    INBOUND_CONNECT_TIMEOUT_LISTENER=120
    Edited by: mseberg on Jun 6, 2012 12:57 PM


    Also of interest

    Redo is transporting in 10gR2 versions.

    http://www.oracle.com/technetwork/database/features/availability/maa-wp-10gr2-dataguardnetworkbestpr-134557.pdf

    Edited by: mseberg on Jun 6, 2012 1:11 PM
  • 11. Re: Archive log gap is created is standby when ever audit trail is set to DB
    940147 Newbie
    Currently Being Moderated
    Hi ,


    No SDU and TDU settings are not configured in primary or standby still now . I will have to configure it according to the docs you specified.

    I will tell you the results soon .

    Thanks !

    Edited by: saikat.das on Jun 6, 2012 9:56 PM

    Edited by: saikat.das on Jun 6, 2012 9:57 PM
  • 12. Re: Archive log gap is created is standby when ever audit trail is set to DB
    Stefan Abraham Newbie
    Currently Being Moderated
    Hi,

    Please have a look at - http://space.itpub.net/11134237/viewspace-684341

    can be firewall issue in your case as well..

    BR
    Stefan
  • 13. Re: Archive log gap is created is standby when ever audit trail is set to DB
    940147 Newbie
    Currently Being Moderated
    Hii

    I checked firewall , and it is configured in such way that , it will not scan archive log files and the fix up feature is disabled.

    Regarding the packet issue , can you provide way so that oracle net packet sizes can be checked.

    before trying to set SDU , I have to know the optimum value of that , That can be only calculated , I if can some how know what is the size of the
    oracle packets flowing through network.

    Please help.

    Thanks ..

    Edited by: saikat.das on Jun 10, 2012 11:30 AM
  • 14. Re: Archive log gap is created is standby when ever audit trail is set to DB
    mseberg Guru
    Currently Being Moderated
    Hello again;

    I believe you can discover you redo generation rate by using LOG_ARCHIVE_TRACE :

    SQL> show parameter LOG_ARCHIVE_TRACE

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    log_archive_trace integer 0

    http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams107.htm

    Besides SDU, you can set SEND_BUF_SIZE and RECV_BUF_SIZE

    This is the Oracle document on this :

    http://docs.oracle.com/cd/B19306_01/network.102/b14212/performance.htm

    You can set SDU in the tnsnames.ora as well :
    PRIMARY =
      (DESCRIPTION =
        (SDU=32767)
           (ADDRESS=(PROTOCOL=tcp) (HOST=<hostname>) (PORT=1521)0
        (CONNECT_DATA=
          (SERVICE_NAME=PRIMARY.hostname))
    )
    The "Database Net Services Administrator's Guide" link post above is probably the best source of information.

    Best Regards

    mseberg
1 2 Previous Next

Legend

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