This discussion is archived
10 Replies Latest reply: Aug 1, 2013 5:44 AM by nagulan.selvakumar RSS

Archive log are not getting copied on standby server

user10745179 Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

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

Legend

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