1 2 Previous Next 15 Replies Latest reply: Apr 16, 2013 2:57 PM by Javid RSS

    Setting Multiple log_archive_dest_n in Data Guar Environment

    Javid
      Hi..

      I faced one strange thing in my data guard environment. I have primary database ( Oracle 11gR2 on Windows Server 2008 R2) and standby database on different Windows 2008 Server R2..
      I configured log_archive_dest_2=(SERVICE=TESTST...) and log_archive_dest_5=(SERVICE=TESTST2 ...) (TEST and TESTST2 is the net service names for the same SID (testst)), then i set those destination's state as : log_archive_dest_state_2=enable ,log_archive_dest_state_5=defer ..Then i did few switch logs ,but when i checked my standby db some logs was not shipped from primary db ..for example the order of sequence in standby db is as : 930 ,932,934,935,937 and standby database does not fetch the missed logs from primary db ..

      But when i changed the destination's state as : log_archive_dest_state_2=defer ,log_archive_dest_state_5=enable that strange case does not occur...I mean all logs are shipped normally to standby db ...
        • 1. Re: Setting Multiple log_archive_dest_n in Data Guar Environment
          mseberg
          Hello;

          Would test the connection using these from the Primary side.

          SQL>connect sys/password@TESTST as sysdba

          SQL>connect sys/password@TESTST2 as sysdba

          You can rule quite a bit out depending upon your results.

          Also can you give a little more detail on you issue. Are both 2 and 5 enabled? What results are you expecting?

          Is it possible LOG_ARCHIVE_DEST_STATE_5 is set to ALTERNATE?

          Best Regards

          mseberg

          Edited by: mseberg on Apr 15, 2013 7:44 AM
          • 2. Re: Setting Multiple log_archive_dest_n in Data Guar Environment
            Javid
            Yes i can easly connect using those net service names,i mean there is no any doubt with my connections to standby database...

            I'm checking two cases ...I enabled log_archive_dest_state_2 and disabled log_archive_dest_state_5=defer ,then when i switched logs frequently ,then there are some sequences in primary database those that were not shipped to standby ...but opposite case ,i mean log_archive_dest_state_2=defer and log_archive_dest_state_5=enable is working normally

            Edited by: Javid on 15.04.2013 6:06
            • 3. Re: Setting Multiple log_archive_dest_n in Data Guar Environment
              mseberg
              OK. I understand. Odd.

              Any chance one of the TNSNAMES has SDU, SEND_BUF_SIZE, RECV_BUF_SIZE set and the other does not?

              Is LOG_ARCHIVE_DUPLEX_DEST set?

              Best Regards

              mseberg
              • 4. Re: Setting Multiple log_archive_dest_n in Data Guar Environment
                teits
                hi,

                put exactly the values you have in:
                log_archive_dest_state_2=???
                log_archive_dest_state_5=???
                log_archive_dest_2=???
                log_archive_dest_5=???
                can you check alertlog files to see if there is any error related to redo transport when you set log_archive_dest_state_2=enable. post here please.

                Tobi
                • 5. Re: Setting Multiple log_archive_dest_n in Data Guar Environment
                  Javid
                  no the parameters you mentioned have'nt been set ...

                  Edited by: Javid on Apr 15, 2013 10:10 AM
                  • 6. Re: Setting Multiple log_archive_dest_n in Data Guar Environment
                    Javid
                    log_archive_dest_state_5=defer
                    log_archive_dest_state_2=enable
                    log_archive_dest_2='SERVICE=TESTST ASYNC NOAFFIRM DB_UNIQUE_NAME=TESTST VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)'
                    log_archive_dest_5='SERVICE=TESTST2 ASYNC NOAFFIRM DB_UNIQUE_NAME=TESTST VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)'
                    in the above case while i'm doing switch logs it is just not moves some logs to standby,and my standby database requestes for the missed archive logs but ,primary rejects its request
                    Error in alert.log of primary database
                    ORA-16055: FAL request rejected
                    ARCH: FAL archive failed. Archiver continuing
                    log_archive_dest_state_5=enable
                    log_archive_dest_state_2=defer
                    log_archive_dest_2='SERVICE=TESTST ASYNC NOAFFIRM DB_UNIQUE_NAME=TESTST VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)'
                    log_archive_dest_5='SERVICE=TESTST2 ASYNC NOAFFIRM DB_UNIQUE_NAME=TESTST VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)'
                    in that case all is going well...
                    • 7. Re: Setting Multiple log_archive_dest_n in Data Guar Environment
                      mseberg
                      It's a good one. Assume you have reviewed these :

                      ORA-16401 and ORA-16055 reported in primary alert.log when redolog switch is over frequently [ID 1243177.1]

                      Database Hanging during Archival - FAL archive failed ORA-16055 [ID 1142856.1]

                      Do TESTST and TESTST2 use the same port in the tnsnames.ora?

                      If you uncover the exact cause please consider sharing it with this forum.

                      Another thought

                      If Data Broker is in use and a change was made with SQL.

                      LOG_ARCHIVE_DEST_n Parameter Attributes can conflict. If you rule out broker and post both INIT's maybe something will jump out.

                      Best Regards

                      mseberg

                      Edited by: mseberg on Apr 15, 2013 12:29 PM

                      Edited by: mseberg on Apr 15, 2013 12:34 PM

                      Edited by: mseberg on Apr 15, 2013 12:43 PM
                      • 8. Re: Setting Multiple log_archive_dest_n in Data Guar Environment
                        Javid
                        Both TESTST and TESTST2 use the same 1521 port ,cause i have only one listener.I doesn't use Data Broker ...

                        If it happens when redo log switch is over frequently ,then why it doesn't happen in another case i mentioned above ?

                        Edited by: Javid on Apr 15, 2013 10:44 AM
                        • 9. Re: Setting Multiple log_archive_dest_n in Data Guar Environment
                          mseberg
                          You have a great question. Why not in both places?

                          If you want to post both INIT's I will review.


                          Best Regards

                          mseberg
                          • 10. Re: Setting Multiple log_archive_dest_n in Data Guar Environment
                            Javid
                            Yes that is my quesition ....!!!

                            Unfortunately the databases is in my work computer..I can post it only tomorrow (((

                            But if you can ask which parameter you need to know i can post it now ..

                            Edited by: Javid on Apr 15, 2013 10:57 PM

                            Edited by: Javid on Apr 15, 2013 11:07 PM
                            • 11. Re: Setting Multiple log_archive_dest_n in Data Guar Environment
                              Javid
                              Primary's INIT
                              test.__db_cache_size=230686720
                              test.__java_pool_size=4194304
                              test.__large_pool_size=4194304
                              test.__oracle_base='C:\app\Administrator'#ORACLE_BASE set from environment
                              test.__pga_aggregate_target=335544320
                              test.__sga_target=629145600
                              test.__shared_io_pool_size=0
                              test.__shared_pool_size=369098752
                              test.__streams_pool_size=8388608
                              *.audit_file_dest='C:\app\Administrator\admin\test\adump'
                              *.audit_trail='db'
                              *.compatible='11.2.0.0.0'
                              *.control_files='C:\app\Administrator\oradata\test\control01.ctl','C:\app\Administrator\flash_recovery_area\test\control02.ctl'
                              *.db_block_size=8192
                              *.db_domain=''
                              *.db_file_name_convert='D:\app\Administrator\oradata\testst\','C:\app\Administrator\oradata\test\'
                              *.db_name='test'
                              *.db_recovery_file_dest='E:\flash_recovery_area'
                              *.db_recovery_file_dest_size=6000000000
                              *.db_unique_name='test'
                              *.diagnostic_dest='C:\app\Administrator'
                              *.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
                              *.fal_server='testst'
                              *.log_archive_config='DG_CONFIG=(test,testst)'
                              *.log_archive_dest_2='SERVICE=TESTST ASYNC AFFIRM DB_UNIQUE_NAME=testst VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)'
                              *.log_archive_dest_5='SERVICE=TESTST2 ASYNC AFFIRM DB_UNIQUE_NAME=testst VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)'
                              *.log_archive_dest_state_2='enable'
                              *.log_archive_dest_state_5='defer'
                              *.log_archive_format='ARC%S_%R.%T'
                              *.log_archive_max_processes=30
                              *.log_file_name_convert='D:\app\Administrator\oradata\testst\','C:\app\Administrator\oradata\test\'
                              *.memory_target=962592768
                              *.open_cursors=300
                              *.processes=150
                              *.remote_login_passwordfile='EXCLUSIVE'
                              *.standby_file_management='AUTO'
                              *.undo_tablespace='UNDOTBS1'
                              Standby's INIT :
                              testst.__db_cache_size=234881024
                              testst.__java_pool_size=4194304
                              testst.__large_pool_size=4194304
                              testst.__oracle_base='D:\app\Administrator'#ORACLE_BASE set from environment
                              testst.__pga_aggregate_target=335544320
                              testst.__sga_target=629145600
                              tesst.__shared_io_pool_size=0
                              testst.__shared_io_pool_size=0
                              testst.__shared_pool_size=369098752
                              testst.__streams_pool_size=4194304
                              *.audit_file_dest='D:\app\Administrator\admin\testst\adump'
                              *.audit_trail='db'
                              *.compatible='11.2.0.0.0'
                              *.control_files='D:\app\Administrator\oradata\testst\control01.ctl','D:\app\Administrator\flash_recovery_area\testst\control02.ctl'
                              *.db_block_size=8192
                              *.db_domain=''
                              *.db_file_name_convert='C:\app\Administrator\oradata\test\','D:\app\Administrator\oradata\testst\'
                              *.db_name='test'
                              *.db_recovery_file_dest='D:\app\Administrator\flash_recovery_area'
                              *.db_recovery_file_dest_size=6000000000
                              *.db_unique_name='testst'
                              *.diagnostic_dest='D:\app\Administrator'
                              *.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
                              *.fal_server='TEST'
                              *.log_archive_config='DG_CONFIG=(test,testst)'
                              :*.log_archive_format='ARC%S_%R.%T'
                              *.log_archive_max_processes=30
                              *.log_file_name_convert='C:\app\Administrator\oradata\test\','D:\app\Administrator\oradata\testst\','E:\REDOLOGS\','D:\app\Administrator\oradata\testst\'
                              *.memory_target=962592768
                              *.open_cursors=300
                              *.processes=150
                              *.remote_login_passwordfile='EXCLUSIVE'
                              *.service_names='testst'
                              *.standby_file_management='AUTO'
                              *.undo_tablespace='UNDOTBS1'
                              • 12. Re: Setting Multiple log_archive_dest_n in Data Guar Environment
                                mseberg
                                Hello;

                                The thing that jumps out is you don't have a local arching destination setup using the LOCATION attribute. That's OK because you are using FRA. But your FRA is not clear.
                                control_files='C:\app\Administrator\oradata\test\control01.ctl','C:\app\Administrator\flash_recovery_area\test\control02.ctl'
                                
                                db_recovery_file_dest='E:\flash_recovery_area'
                                You have a control file on C: , but E:\flash_recovery_area' seems to be the FRA area. Does E: and its folder exist? If not this could cause your issue but I have no idea what Oracle might be doing because of it.

                                The standby ( while not your issue ) is missing log_archive_dest_state_n and log_archive_dest_n so it can never assume the Primary role.

                                The parameter "log_archive_max_processes" can probably be set lower than 30, while the default is not enough, 30 is probably too many and some memory is being wasted for nothing.

                                Best Regards

                                mseberg
                                • 13. Re: Setting Multiple log_archive_dest_n in Data Guar Environment
                                  Javid
                                  No, firstly FRA was at disk C, then for lack of disk space in C i changed it to disk E.( Disk E actually exists) ..

                                  You are right that i haven't fully prepared standby database to assume primary role...

                                  We attempted to test same scenario in Oracle Linux ..but when we tried to set two log_archive_dest_n parameter with the different net service names , Oracle didn't allow us to set it ...but in Windows i can set it without problem....

                                  Edited by: Javid on Apr 16, 2013 9:49 PM
                                  • 14. Re: Setting Multiple log_archive_dest_n in Data Guar Environment
                                    mseberg
                                    Hello;

                                    Mahir M. Quluzade and been working this issue very hard ( great guy ). I was able to recreate your issue
                                    ALTER SYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=BOTH;
                                    Tue Apr 16 14:05:10 2013
                                    ALTER SYSTEM SET log_archive_dest_state_5='ENABLE' SCOPE=BOTH;
                                    Tue Apr 16 14:05:24 2013
                                    NSA5 started with pid=36, OS id=30419 
                                    Tue Apr 16 14:05:27 2013
                                    Thread 1 advanced to log sequence 399 (LGWR switch)
                                      Current log# 3 seq# 399 mem# 0: /u01/app/oracle/oradata/PRIMARY/redo03.log
                                    Tue Apr 16 14:05:27 2013
                                    Archived Log entry 788 added for thread 1 sequence 398 ID 0x624dc4bb dest 1:
                                    ******************************************************************
                                    LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_5
                                    ******************************************************************
                                    LNS: Standby redo logfile selected for thread 1 sequence 399 for destination LOG_ARCHIVE_DEST_5
                                    Tue Apr 16 14:05:28 2013
                                    ARC4: Archive log rejected (thread 1 sequence 398) at host 
                                    But SYS connect work...
                                    SQL> connect sys/password@faraway as sysdba
                                    Connected.
                                    faraway is the service for my dest_5

                                    My knee jerk is to restart apply on the Standby but I would like to test more.

                                    In any event you found something I have never seen before. Cool.

                                    h3. Update

                                    Logs transfer, but never apply. This adds to the knee jerk about restarting MRP. If that works then I for one have a new Data Guard rule. In any event you system is not the issue.

                                    Best Regards

                                    mseberg
                                    1 2 Previous Next