13 Replies Latest reply: Dec 31, 2013 3:31 AM by Mr.D. RSS

    Question about Physical Standby DB

    Mr.D.

      Hi.

      i've implemented a physical standby db using rman duplicate.

      I've created standby logfiles.

      I've enabled log shipping from primary and activare recovery on the standby with

      ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

       

      Now looking v$managed_standby

       

      SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

      PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
      --------- ------------ ---------- ---------- ---------- ----------
      ARCH      CLOSING               2        260     491520        440
      ARCH      CLOSING               2        259     233472         98
      ARCH      CONNECTED             0          0          0          0
      ARCH      CLOSING               2        258     710656       1910
      ARCH      CLOSING               1        278     555008       1898
      ARCH      CLOSING               1        277    1241088        962
      RFS       IDLE                  0          0          0          0
      RFS       IDLE                  0          0          0          0
      MRP0      WAIT_FOR_GAP          1        263          0          0
      RFS       IDLE                  1        279     315899         14
      RFS       IDLE                  2        261     360706          1

      RFS       IDLE                  0          0          0          0
      RFS       IDLE                  0          0          0          0

       

       

      I'm not using Real Time Apply, so what is the correct process?
      Primary write update on redo logs, RFS process copy them in standby redo and than apply to db?

       

      SQL> SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE#,applied FROM V$ARCHIVED_LOG;

         THREAD#  SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# APPLIED
      ---------- ---------- ------------- ------------ ---------
               1        272    3423327608   3423363542 NO
               2        253    3422708752   3423327635 NO
               1        271    3422708357   3423327608 NO
               1        273    3423363542   3423426397 NO
               2        254    3423327635   3423426692 NO
               2        255    3423426692   3423427529 NO
               1        274    3423426397   3423925171 NO
               2        256    3423427529   3423925250 NO
               1        275    3423925171   3424667073 NO
               2        257    3423925250   3424667141 NO
               1        276    3424667073   3425468068 NO
               2        258    3424667141   3425468422 NO
               2        259    3425468422   3425891919 NO
               1        277    3425468068   3425891914 NO
               1        278    3425891914   3426517678 NO
               2        260    3425891919   3426517748 NO

       

      why archived log on standby are not applied?

       

      Thank you.

        • 1. Re: Question about Physical Standby DB
          Anar Godjaev

          Hi,

           

          Can you please send me result in Primary database:

           

          SELECT status, error

          FROM V$ARCHIVE_DEST_STATUS

          WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE';

          • 2. Re: Question about Physical Standby DB
            mseberg

            Hello;

             

            The best source of information on a problem like this is the Primary and Standby alert logs. Can you check them for an error or message why apply is not work and post?

             

            Best Regards

             

            mseberg

            • 3. Re: Question about Physical Standby DB
              Mr.D.

              Hi,

              i've a gap of archived log,

              I've performed a duplicate database without dorecover.

              I've restored archivedlog manually now thy are applied.

               

              My question now is RFS copy update from primary to standby or i need the archived log shipped?

              If a crash occur i've the standby alligned at the last archived log or with primary redo?

              • 4. Re: Question about Physical Standby DB
                saurabh

                check for archive log sequence 263 on the standby database is present or not. if not manually copy the same from production.

                • 5. Re: Question about Physical Standby DB
                  mseberg

                  If you cannot provide the information requested from your alert log(s) it will be next to impossible to know what is going on.

                   

                  Please post the error and or message from your alert log(s)

                   

                  Best Regards

                   

                  mseberg

                  • 6. Re: Question about Physical Standby DB
                    Mr.D.

                    Hi, archived are restored manually so now all archived are applied.

                     


                    • 7. Re: Question about Physical Standby DB
                      Shivendra Narain Nirala

                      Hi

                       

                      Check your alert log  for

                      returning error ORA-16191

                      ORA-16191 -Primary log shipping client not logged on standby

                       

                       

                      Solution :-

                      1.Defer the remote archival destination (log_archive_dest_state_n) parameter in primary. In case of RAC defer LOG_ARCHIVE_DEST_STATE_n in all nodes.

                      2.check parameter REMOTE_PASSWORDFILE is set to either EXCLUSIVE or SHARED on the both database .

                      3.Delete the old password file and create the new one for both the DBs.In case of RAC shutdown all instance of standby before recreating the password file by below command :-

                       

                       

                      orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=sys entries=5 force=y ignorecase=Y

                       

                       

                      4. set the parameter SEC_CASE_SENSITIVE_LOGON=FALSE on both DBs.

                      5.Enable the remote archival destination (log_archive_dest_state_n) parameter in primary.

                      6. Log into standby database and stop the recovery as

                       

                       

                      SQL> alter database recover managed standby database cancel;

                       

                       

                      7.  Now restart the recovery as

                       

                       

                      SQL>alter database recover managed standby database disconnect from session ;

                       

                       

                      8.Perform a log switch on the primary database and check the archive sequence ,archive destination  and alert logfile.

                       

                       

                      SQL>select DEST_ID, STATUS, DESTINATION, ERROR from V$ARCHIVE_DEST;

                       

                      Regards

                      Shivendra Narain Nirala

                      • 8. Re: Question about Physical Standby DB
                        Mr.D.

                        Hi,

                        now standby is aligned to primary.

                        My questions now are:

                        is RFS process responsible to copy update from primary to standby or i need the archived log shipped?

                        If a crash occurs is the standby aligned at the last archived log or with primary redo?

                        • 9. Re: Question about Physical Standby DB
                          saurabh

                          In automatic recovery. The RFS process will copy all the information from primary to standby and MRP process applies it to database.

                           

                          If crash occurs the standby is recovered upto last archive log in your case.  

                          • 10. Re: Question about Physical Standby DB
                            Mr.D.

                            And the standby log file?

                            I see RFS process works in v$manager_standby:

                             

                            1* select process, pid, status, thread#, sequence#, block#,blocks from v$managed_standby

                             

                            PROCESS          PID STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS

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

                            ARCH           25518 CLOSING               2        332     208896        137

                            ARCH           25520 CLOSING               1        353    1415168       1418

                            ARCH           25522 CONNECTED             0          0          0          0

                            ARCH           25524 CLOSING               1        352    1044480        334

                            ARCH           25526 CLOSING               2        334     657408       1027

                            ARCH           25528 CLOSING               2        331      10240        993

                            RFS            27618 IDLE                  0          0          0          0

                            RFS            27595 IDLE                  0          0          0          0

                            MRP0           25736 WAIT_FOR_LOG          1        354          0          0

                            RFS            25708 IDLE                  0          0          0          0

                            RFS            25996 IDLE                  0          0          0          0

                            RFS            25972 IDLE                  0          0          0          0

                            RFS            26813 IDLE                  1        354     184533         11

                            RFS            26815 IDLE                  2        335     224810          1


                            RFS works, loading blocks.

                            Standby logs are  useless?

                            • 11. Re: Question about Physical Standby DB
                              CKPT

                              RFS works, loading blocks.

                              Standby logs are  useless?

                               

                              Please run below command from Standby share the output

                              SQL> select recovery_mode from v$archive_dest_status where recovery_mode !='IDLE';

                               

                              Also, you can check whether redo is writing or not from v$standby_log and the columns you can check are last_change#,last_time where these values are also get updated.

                              • 12. Re: Question about Physical Standby DB
                                Mr.D.

                                Thank you CKPT.

                                Another question, why it use only 2 standby log??

                                 

                                SQL> select GROUP#, LAST_CHANGE#,to_char(LAST_TIME,'dd/mm/yyyy hh24:mi:ss') from v$standby_log;

                                    GROUP# LAST_CHANGE# TO_CHAR(LAST_TIME,'
                                ---------- ------------ -------------------
                                         7   3565790312 31/12/2013 09:36:29
                                         8   3565790313 31/12/2013 09:36:29
                                         9
                                        10
                                        11
                                        12
                                        13
                                        14

                                8 rows selected.

                                 

                                My standby is active about 20days.

                                 

                                Thank you

                                • 13. Re: Question about Physical Standby DB
                                  Mr.D.

                                  Sorry,

                                  now it uses other 2 standby log, after primary switch

                                   

                                  select GROUP#, LAST_CHANGE#,to_char(LAST_TIME,'dd/mm/yyyy hh24:mi:ss') from v$standby_log

                                      GROUP# LAST_CHANGE# TO_CHAR(LAST_TIME,'
                                  ---------- ------------ -------------------
                                           7
                                           8
                                           9   3567421522 31/12/2013 10:29:40
                                          10   3567421545 31/12/2013 10:29:40
                                          11
                                          12
                                          13
                                          14