1 2 Previous Next 21 Replies Latest reply: Nov 9, 2012 6:10 AM by CKPT RSS

    Archived log file are not shifted to standby side and applied

    vk82
      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
          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
            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
              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
                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
                  Start MRP process with steps mentioned above
                  • 6. Re: Archived log file are not shifted to standby side and applied
                    vk82
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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