1 2 Previous Next 21 Replies Latest reply: Jan 29, 2013 4:33 PM by 509471 RSS

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

    940147
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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