10 Replies Latest reply: Aug 1, 2013 7:44 AM by nagulan.selvakumar RSS

    Archive log are not getting copied on standby server

    user10745179

      Hi,

       

      We have configured dataguard on 10.2.0.5.0, my archive logs on primary not getting copied on standby,

       

      select dest_id,error from v$archive_dest also shows no error,

       

      Standby alert log shows

       

      Clearing online redo logfile 1 complete

      Media Recovery Waiting for thread 1 sequence 248996

      Wed Jul 31 22:22:12  2013

      Completed: alter database recover managed standby database disconnect from session

       

      Also output on standby is as follows:

       

      SQL> select max(sequence#) from v$archived_log;

       

      MAX(SEQUENCE#)

      --------------

       

      SQL> select sequence#,applied from V$archived_log order by sequence#;

       

      no rows selected

       

       

       

      FAL Setting for primary :

       

      SQL> show parameter fal

       

      NAME                                 TYPE        VALUE

      ------------------------------------ ----------- -----------

      fal_client                           string      ORCL

      fal_server                           string      ORCLDR

       

      FAL setting for Standby

       

      SQL> show parameter fal

       

      NAME                                 TYPE        VALUE

      ------------------------------------ ----------- -------------------

      fal_client                           string      orcldr

      fal_server                           string      orcl

       

       

       

      Thanks

        • 1. Re: Archive log are not getting copied on standby server
          mseberg

          Hello;

           

          Can you post the INIT's from both the primary and standby sides?

           

          Best Regards

           

          mseberg

          • 2. Re: Archive log are not getting copied on standby server
            nagulan.selvakumar

            Hi,

             

            1. Do you have identical password file on both primary and standby?

            2. Please provide o/p from primary database

             

            show parameter log_archive_dest_2

            show parameter log_archive_dest_state_2

            alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

            select ERROR_CODE,TIMESTAMP,MESSAGE from V$DATAGUARD_STATUS where DEST_ID=2;


            Thank you!!

            • 3. Re: Archive log are not getting copied on standby server
              user10745179

              Hi,

               

              Please find below the init file of both primary & standby

              Primary:

              orcl.__db_cache_size=293601280

              orcl.__java_pool_size=16777216

              orcl.__large_pool_size=4194304

              orcl.__shared_pool_size=289406976

              orcl.__streams_pool_size=0

              *.aq_tm_processes=0

              *.audit_file_dest='E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\ADUMP'

              *.background_dump_dest='E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\BDUMP'

              *.compatible='10.2.0.1.0'

              *.control_files='E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL','E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL','E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL'

              *.core_dump_dest='E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\CDUMP'

              *.db_block_size=8192

              *.db_domain=''

              *.db_file_multiblock_read_count=16

              *.db_name='orcl'

              *.db_unique_name='ORCL'

              *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

              *.fal_client='ORCL'

              *.fal_server='ORCLDR'

              *.job_queue_processes=10

              *.log_archive_dest_1='LOCATION=E:\ARCHIVE\QUANTIS'

              *.log_archive_dest_2='LOCATION=orcldr'

              *.log_archive_dest_state_2='ENABLE'

              *.LOG_ARCHIVE_FORMAT='arc_%t_%s_%r.arc'

              *.nls_length_semantics='BYTE'

              *.open_cursors=300

              *.pga_aggregate_target=203423744

              *.processes=150

              *.remote_login_passwordfile='EXCLUSIVE'

              *.resource_manager_plan=''

              *.sga_target=612368384

              *.standby_file_management='AUTO'

              *.undo_management='AUTO'

              *.undo_retention=900

              *.undo_tablespace='UNDOTBS1'

              *.user_dump_dest='E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP'

               

               

               

              ***************************************************************************************************************************************

              Standby :

              orcl.__db_cache_size=222298112

              orcldr.__db_cache_size=436207616

              orcl.__java_pool_size=16777216

              orcldr.__java_pool_size=4194304

              orcl.__large_pool_size=4194304

              orcldr.__large_pool_size=4194304

              orcl.__shared_pool_size=360710144

              orcldr.__shared_pool_size=159383552

              orcl.__streams_pool_size=0

              orcldr.__streams_pool_size=0

              *.aq_tm_processes=0

              *.audit_file_dest='E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\ADUMP'

              *.background_dump_dest='E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\BDUMP'

              *.compatible='10.2.0.1.0'

              *.control_files='E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL','E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL','E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL'

              *.core_dump_dest='E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\CDUMP'

              *.db_block_size=8192

              *.db_domain=''

              *.db_file_multiblock_read_count=16

              *.db_name='orcl'

              *.db_unique_name='ORCLDR'

              *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

              *.fal_client='orcldr'

              *.fal_server='orcl'

              *.job_queue_processes=10

              *.log_archive_dest_1='LOCATION=E:\ARCHIVE\QUANTIS'

              *.LOG_ARCHIVE_DEST_2='SERVICE=orcl'

              *.LOG_ARCHIVE_DEST_STATE_1='ENABLE'

              *.log_archive_dest_state_2='DEFER'

              *.LOG_ARCHIVE_FORMAT='arc_%t_%s_%r.arc'

              *.LOG_ARCHIVE_MAX_PROCESSES=10

              *.nls_length_semantics='BYTE'

              *.open_cursors=300

              *.pga_aggregate_target=203423744

              *.processes=150

              *.remote_login_passwordfile='EXCLUSIVE'

              *.resource_manager_plan=''

              *.sga_target=612368384

              *.standby_file_management='AUTO'

              *.undo_management='AUTO'

              *.undo_retention=900

              *.undo_tablespace='UNDOTBS1'

              *.user_dump_dest='E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP'

              • 4. Re: Archive log are not getting copied on standby server
                nagulan.selvakumar

                Hi,

                 

                Is the Primary and standby reside on same server?

                Please take a look at "log_archive_dest_2='LOCATION=orcldr'". If you use different server for primary and standby this should be like

                log_archive_dest_2='SERVICE=<connect_string>'


                Thank you!!

                • 5. Re: Archive log are not getting copied on standby server
                  user10745179

                  Hi,

                   

                  Password file is same on both the servers.

                   

                  Below is thr output of the queries :

                   

                  SQL> show parameter log_archive_dest_2

                   

                  NAME                                 TYPE        VALUE

                  ------------------------------------ ----------- ------------------------------

                  log_archive_dest_2                   string      LOCATION=orcldr

                  SQL>

                  SQL> show parameter log_archive_dest_state_2

                   

                  NAME                                 TYPE        VALUE

                  ------------------------------------ ----------- ------------------------------

                  log_archive_dest_state_2             string      ENABLE

                  SQL> alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

                   

                  Session altered.

                   

                  SQL> alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

                   

                  Session altered.

                   

                  SQL> select ERROR_CODE,TIMESTAMP,MESSAGE from V$DATAGUARD_STATUS where DEST_ID=2

                  ;

                   

                  no rows selected

                  • 6. Re: Archive log are not getting copied on standby server
                    mseberg

                    Hello;

                     

                    Both your log_archive_dest_n are way off.

                     

                    Primary Example :

                     

                    log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/PRIMARY/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMARY'

                    log_archive_dest_2='SERVICE=STANDBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY'

                     

                    Standby Example :

                     

                    log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/STANDBY/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STANDBY'

                    log_archive_dest_2='SERVICE=PRIMARY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMARY'

                     

                     

                    Correct and check again.

                     

                    Data Guard on Oracle 10 Step by Step

                     

                    Best Regards

                     

                    mseberg

                    • 7. Re: Archive log are not getting copied on standby server
                      user10745179

                      Thanks for quick reply,

                       

                      I have changed the log_archive_dest_2 to service

                      registered the logfiles, but still archives are not getting copied to standby,

                       

                      Thanks

                      • 8. Re: Archive log are not getting copied on standby server
                        nagulan.selvakumar

                        Now could you get the output for the below.

                         

                        select ERROR_CODE,TIMESTAMP,MESSAGE from V$DATAGUARD_STATUS where DEST_ID=2;          --     From Primary.


                        select database_role, open_mode from v$database;          --          From Standby.


                        Thank you!!

                        • 9. Re: Archive log are not getting copied on standby server
                          user10745179

                          Hi,

                           

                          SQL> select ERROR_CODE,TIMESTAMP,MESSAGE from V$DATAGUARD_STATUS where DEST_ID=2;

                           

                          no rows selected

                           

                           

                          SQL> select database_role, open_mode from v$database;

                           

                          DATABASE_ROLE    OPEN_MODE

                          ---------------- ----------

                          PHYSICAL STANDBY MOUNTED

                           

                          But, one more doubt, due to network failure,archive log sequence 247374 was the last log applied,

                          as we have built the dr again with hot backup of prodution, & production is now on archive log sequence 249975.

                           

                          but the v$archive_process shows following:

                           

                          SQL> select * from V$ARCHIVE_PROCESSES;

                           

                             PROCESS STATUS     LOG_SEQUENCE STAT

                          ---------- ---------- ------------ ----

                                   0 ACTIVE                0 IDLE

                                   1 ACTIVE           247374 BUSY

                                   2 STOPPED               0 IDLE

                                   3 STOPPED               0 IDLE

                                   4 STOPPED               0 IDLE

                                   5 STOPPED               0 IDLE

                                   6 STOPPED               0 IDLE

                                   7 STOPPED               0 IDLE

                                   8 STOPPED               0 IDLE

                                   9 STOPPED               0 IDLE

                                  10 STOPPED               0 IDLE

                           

                             PROCESS STATUS     LOG_SEQUENCE STAT

                          ---------- ---------- ------------ ----

                                  11 STOPPED               0 IDLE

                                  12 STOPPED               0 IDLE

                                  13 STOPPED               0 IDLE

                                  14 STOPPED               0 IDLE

                                  15 STOPPED               0 IDLE

                                  16 STOPPED               0 IDLE

                                  17 STOPPED               0 IDLE

                                  18 STOPPED               0 IDLE

                                  19 STOPPED               0 IDLE

                                  20 STOPPED               0 IDLE

                                  21 STOPPED               0 IDLE

                           

                             PROCESS STATUS     LOG_SEQUENCE STAT

                          ---------- ---------- ------------ ----

                                  22 STOPPED               0 IDLE

                                  23 STOPPED               0 IDLE

                                  24 STOPPED               0 IDLE

                                  25 STOPPED               0 IDLE

                                  26 STOPPED               0 IDLE

                                  27 STOPPED               0 IDLE

                                  28 STOPPED               0 IDLE

                                  29 STOPPED               0 IDLE

                           

                          30 rows selected.

                           

                          Thanks

                          • 10. Re: Archive log are not getting copied on standby server
                            nagulan.selvakumar

                            Hi,

                             

                            I've gone through the pfile again to find the parameter log_archive_config is missing which is very very essential for dataguard configuration.

                            Please add the parameter in both the pfiles. This can also be done dynamically with alter system if you use spfile.

                             

                            log_archive_config='DG_CONFIG=(orcl,orcldr)'

                             

                            Also the difference between the logs seems to be huge. You can try syncing your standby with RMAN incremental backups. Check below link for the same.

                            Oracle tips and techs: Recover archive gaps in standby database from primary using RMAN incremental backups

                             

                            Thank you!!