This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Nov 9, 2012 4:10 AM by CKPT RSS

Archived log file are not shifted to standby side and applied

vk82 Explorer
Currently Being Moderated
Hi Guru's,
DB
My OS Version:Windows Server 2003
DB Version:11.2.0.1.0

I am trying to create Physical Standby DB on my test machine after setting all the parameters and start standby db in mount mode when i do the verification of archived log files that need to be shift from pri site to standby site i am getting:

no rows selected.

I am following the below mentioned doc for reference

http://docs.oracle.com/cd/E11882_01/server.112/e25608/create_ps.htm#i63561

Can anyone from your side can help me to sort out this problem.


Thanks in advance
  • 1. Re: Archived log file are not shifted to standby side and applied
    Sunny kichloo Expert
    Currently Being Moderated
    Is your MRP process running on standby database

    Share the output of below mentioned query on standby database

    SQL>select process,status from v$managed_standby;

    Also If MRP is absent in above output start it by below mentioned query

    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

    And once it is started Do log switch and share the output
  • 2. Re: Archived log file are not shifted to standby side and applied
    Shivananda Rao Guru
    Currently Being Moderated
    Post the following:

    From standby database:
    select process,status,sequence# from v$managed_standby;
    From the primary database:
    select severity,error_code,message,timestamp from v$dataguard_status where dest_id=2;
    I hope that on the primary database the parameter that is shipping archives to standby is using log_archive_dest_2
  • 3. Re: Archived log file are not shifted to standby side and applied
    vk82 Explorer
    Currently Being Moderated
    SQL>select process,status from v$managed_standby;


    PROCESS STATUS
    --------- ------------
    ARCH CLOSING
    ARCH CONNECTED
    ARCH CONNECTED
    ARCH CONNECTED
    RFS IDLE
    RFS IDLE
    RFS IDLE
    RFS IDLE

    8 rows selected.

    but after running your second query i got the result below as :

    SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

    SEQUENCE# APPLIED
    ---------- ---------
    14 NO
    15 NO
    16 NO
    17 NO
    18 NO
    19 NO
    20 NO
    21 NO
    22 NO
    23 NO
    24 NO

    SEQUENCE# APPLIED
    ---------- ---------
    25 NO
    26 NO
    27 NO
    28 NO
    29 NO
    30 NO
    31 NO
    32 NO
    33 NO
    34 NO

    21 rows selected.


    but here applied column contains all no what does this really means and also i want to check what i do on primary so that it reflects on standby scnerio based if anyone can provide me so that my concept about standby is more clear
  • 4. Re: Archived log file are not shifted to standby side and applied
    Shivananda Rao Guru
    Currently Being Moderated
    SQL>select process,status from v$managed_standby;
    PROCESS STATUS
    ------------
    ARCH CLOSING
    ARCH CONNECTED
    ARCH CONNECTED
    ARCH CONNECTED
    RFS IDLE
    RFS IDLE
    RFS IDLE
    RFS IDLE
    
    8 rows selected.
    I do not see MRP running on your standby database. Do as below:

    on standby database:
    sql>alter database recover managed standby database disconnect from session;
    Post from primary:
    sql>select max(sequence#) from v$archived_log;
    Post from standby:
    sql>select process,status,sequence# from v$managed_standby;
    sql>select max(sequence#) from v$archived_log where applied='YES';
    Check if the max sequence generated on primary is same as the max sequence applied on standby. If not, then please post from the primary:

    Primary:
    sql>select severity,error_code,message,timestamp from v$datagaurd_status where dest_id=2;
  • 5. Re: Archived log file are not shifted to standby side and applied
    Sunny kichloo Expert
    Currently Being Moderated
    Start MRP process with steps mentioned above
  • 6. Re: Archived log file are not shifted to standby side and applied
    vk82 Explorer
    Currently Being Moderated
    SQL> select max(sequence#) from v$archived_log;

    MAX(SEQUENCE#)
    --------------
    39

    SQL> select process,status,sequence# from v$managed_standby;

    PROCESS STATUS SEQUENCE#
    --------- ------------ ----------
    ARCH CLOSING 38
    ARCH CLOSING 36
    ARCH CONNECTED 0
    ARCH CLOSING 37

    SQL> select max(sequence#) from v$archived_log where applied='YES';

    MAX(SEQUENCE#)
    --------------
    39

    Yes the max sequence generated on primary is same as the max sequence applied on standby.

    And what is MRP and why it is important can you please let me know abt this
  • 7. Re: Archived log file are not shifted to standby side and applied
    CKPT Guru
    Currently Being Moderated
    vk82 wrote:
    SQL> select max(sequence#) from v$archived_log;

    MAX(SEQUENCE#)
    --------------
    39

    SQL> select process,status,sequence# from v$managed_standby;

    PROCESS STATUS SEQUENCE#
    --------- ------------ ----------
    ARCH CLOSING 38
    ARCH CLOSING 36
    ARCH CONNECTED 0
    ARCH CLOSING 37

    SQL> select max(sequence#) from v$archived_log where applied='YES';

    MAX(SEQUENCE#)
    --------------
    39

    Yes the max sequence generated on primary is same as the max sequence applied on standby.

    And what is MRP and why it is important can you please let me know abt this
    Whenever you created standby database, Make a habit of tail to alert log file to monitor whats happening on the standby database, Besides MRP is an Media Recovery Process , which applies archies/redo on standby database.
    MRP can apply based on the redo transport, If you are using real-time apply then Redo will be writtened to the standby redo log files at the same time recovery will be performed by MRP process, If you are not using Real-Time apply then whenever a archive generated on primary that archive will be transported to the standby and applied on the standby database.
    It is recommended to use Realtime apply, so that you can avoid much data lost.


    However you are in 11gR2, You can use below query to monitor
    SQL> SELECT * FROM V$STANDBY_EVENT_HISTOGRAM WHERE NAME = 'apply lag'  AND COUNT > 0;
    NAME             TIME UNIT                  COUNT LAST_TIME_UPDATED
    ---------- ---------- ---------------- ---------- --------------------
    apply lag           0 seconds                 431 08/05/2012 22:14:21
    apply lag           1 seconds                   7 08/05/2012 22:13:31
    SQL>
    Edited by: CKPT on Nov 6, 2012 6:49 PM
  • 8. Re: Archived log file are not shifted to standby side and applied
    Sunny kichloo Expert
    Currently Being Moderated
    Also MRP will be seen in standby site.Not in Primary Site


    SQL> select process,status,sequence# from v$managed_standby;
  • 9. Re: Archived log file are not shifted to standby side and applied
    Shivananda Rao Guru
    Currently Being Moderated
    MRP stands for Managed Recovery Process which needs to be always active in standby database. This keeps the standby database in recovery mode and applies the redo information shipped from primary.
    vk82     
         
    Handle:     vk82
    Status Level:     Newbie (35)
    Registered:     Nov 21, 2010
    Total Posts:     722
    Total Questions:     176 (111 unresolved)
    Name     07982
    Location     New Delhi
    If you feel that your questions have been answered, then please consider closing them by providing appropriate points. Please keep the forum clean !
  • 10. Re: Archived log file are not shifted to standby side and applied
    vk82 Explorer
    Currently Being Moderated
    After trying to run the CKPT advised sql statement:

    SQL> SELECT * FROM V$STANDBY_EVENT_HISTOGRAM WHERE NAME = 'apply lag' AND COUNT > 0;

    no rows selected.


    What i need to do after this.
  • 11. Re: Archived log file are not shifted to standby side and applied
    CKPT Guru
    Currently Being Moderated
    vk82 wrote:
    After trying to run the CKPT advised sql statement:

    SQL> SELECT * FROM V$STANDBY_EVENT_HISTOGRAM WHERE NAME = 'apply lag' AND COUNT > 0;

    no rows selected.


    What i need to do after this.
    This view is to calculate lag, Are you using Real-Time Apply? I don't think so.
  • 12. Re: Archived log file are not shifted to standby side and applied
    vk82 Explorer
    Currently Being Moderated
    So according to you i need to use Real -Time Apply in case if it is not done
  • 13. Re: Archived log file are not shifted to standby side and applied
    CKPT Guru
    Currently Being Moderated
    Yes, to enable real time apply you must do below things

    1) change redo transport from ARCH to LGWR in log_archive_dest_n of remote destination
    2) create standby redo log files on standby of same or more size than online redo log files and create same or more number of redo log groups than online redo logs
    3) create standby redo logs on primary, of course it's an optional but useful in case of switchover to avoid delay on configurations.

    And this is not any extra cost option either if you have EE license. And you can avoid data Liston case of any online redo log corruption.
  • 14. Re: Archived log file are not shifted to standby side and applied
    vk82 Explorer
    Currently Being Moderated
    I already done the same below is my init file for pri

    DB_NAME=orcl
    DB_UNIQUE_NAME=orcl
    LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,sbyorcl)'
    LOG_ARCHIVE_DEST_1='LOCATION=/home/oracle/app/oracle/flash_recovery_area/orcl/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
    LOG_ARCHIVE_DEST_2='SERVICE=sbyorcl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sbyorcl'
    LOG_ARCHIVE_DEST_STATE_1=ENABLE
    LOG_ARCHIVE_DEST_STATE_2=ENABLE
    REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
    LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
    LOG_ARCHIVE_MAX_PROCESSES=30
    FAL_SERVER=sbyorcl
    FAL_CLIENT=orcl
    DB_FILE_NAME_CONVERT='sbyorcl','orcl'
    LOG_FILE_NAME_CONVERT='/home/oracle/app/oracle/flash_recovery_area/sbyorcl/','/home/oracle/app/oracle/flash_recovery_area/orcl/'
    STANDBY_FILE_MANAGEMENT=AUTO


    What i need to do next
1 2 Previous Next

Legend

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