10 Replies Latest reply: May 7, 2013 4:19 AM by 1004902 RSS

    Standby DB cannot do auto recovery

    1004902
      Hi Oracle Data Guard GUrus,

      I have done the configs as per the guide here http://docs.oracle.com/cd/B12037_01/server.101/b10823/scenarios.htm
      This is a physical standby db

      Primary initBP1.ora
      compatible = "11.2.0"
      log_archive_dest_1 = "LOCATION=H:\oracle\BP1\oraarch\BP1arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=BP1"
      log_archive_dest_2 = "SERVICE=BP1_DR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=BP1_DR"
      log_archive_dest_state_1 = "ENABLE"
      log_archive_dest_state_2 = "ENABLE"
      fal_client = "BP1"
      fal_server = "BP1_DR"
      log_archive_config = "DG_CONFIG=(BP1,BP1_DR)"
      log_archive_format = "%t_%s_%r.dbf"
      db_recovery_file_dest = "H:\oracle\BP1\oraflash"
      standby_file_management = "AUTO"
      remote_os_authent = TRUE
      remote_login_passwordfile= "EXCLUSIVE"
      local_listener = "(ADDRESS=(PROTOCOL=TCP)(HOST=172.19.1.73)(PORT=1521)(COMMUNITY=SAP.WORLD))"
      db_name = "BP1"

      DR site --> initBP1.ora
      compatible = "11.2.0"
      log_archive_dest_1 = "LOCATION=H:\oracle\BP1\oraarch\BP1arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=BP1_DR"
      log_archive_dest_state_1 = "ENABLE"
      log_archive_dest_state_2 = "ENABLE"
      standby_archive_dest = "H:\ORACLE\BP1\ORAARCH\BP1ARCH"
      fal_client = "BP1_DR"
      fal_server = "BP1"
      log_archive_config = "DG_CONFIG=(BP1,BP1_DR)"
      log_archive_format = "%t_%s_%r.dbf"
      db_recovery_file_dest = "H:\oracle\BP1\oraflash"
      standby_file_management = "AUTO"
      remote_login_passwordfile= "EXCLUSIVE"
      db_name = "BP1"
      db_unique_name = "BP1_DR"


      Steps : As per the above official link
      ** I copied the password file PWDBP1.ORA for primary to DR
      ** The SQLNET.ora, tnsnames.ora,...all ok
      ** I am able to login from Primary to DR and viceversa using SQLPLUS sys@bp1 as sysdba and sqlplus sys@bp1_dr as sysdba both ways
      ** I copied the primary db to DR and applied the archive log to latest using "recover standby database"
      ** Then I registered the last archive log file in the DR site

      SQL> SELECT SUBSTR(NAME,1,25) FILE_NAME, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

      FILE_NAME
      ---------------------------------------------------------------------------
      SEQUENCE# APPLIED
      ---------- ---------
      H:\ORACLE\BP1\ORAARCH\BP1
      536 YES

      H:\ORACLE\BP1\ORAARCH\BP1
      571 YES

      Issues I have :

      1) In the primary I intermittenly get ORA-03113, but the log is getting shiped to seconday archive destination... after some time --> I checked the size --> it is ok
      2) The log is not getting applied automatically on the seconday / dr site...

      Any idea..

      This is the error message on the DR site
      Tue Apr 23 16:51:45 2013
      WARN: ARCH: Terminating pid 1932 hung on an I/O operation
      WARN: ARCH: Terminating pid 6920 hung on an I/O operation
      WARN: ARCH: Terminating pid 1728 hung on an I/O operation
      krsv_proc_kill: Killing 1 processes (Process by index)
      Tue Apr 23 16:51:46 2013
      WARN: ARC2: Terminating pid 1932 hung on an I/O operation
      WARN: ARC2: Terminating pid 6920 hung on an I/O operation
      WARN: ARC2: Terminating pid 1728 hung on an I/O operation
      krsv_proc_kill: Killing 1 processes (Process by index)
      krsv_proc_kill: Killing 1 processes (Process by index)
      Tue Apr 23 16:51:50 2013
      RFS[56]: Assigned to RFS process 5464
      RFS[56]: Opened log for thread 1 sequence 578 dbid -1807329834 branch 810490646
      Tue Apr 23 16:54:14 2013
      RFS[57]: Assigned to RFS process 7108
      RFS[57]: Opened log for thread 1 sequence 572 dbid -1807329834 branch 810490646
      Tue Apr 23 16:54:16 2013
      WARN: ARCH: Terminating pid 6672 hung on an I/O operation
      WARN: ARCH: Terminating pid 5292 hung on an I/O operation
      WARN: ARCH: Terminating pid 980 hung on an I/O operation
      krsv_proc_kill: Killing 1 processes (Process by index)
      Tue Apr 23 16:54:17 2013
      Primary database is in MAXIMUM PERFORMANCE mode
      RFS[58]: Assigned to RFS process 6292
      RFS[58]: No standby redo logfiles created
      RFS[58]: Opened log for thread 1 sequence 581 dbid -1807329834 branch 810490646
      krsv_proc_kill: Killing 1 processes (Process by index)
      krsv_proc_kill: Killing 1 processes (Process by index)
      Tue Apr 23 16:54:19 2013
      RFS[59]: Assigned to RFS process 6920
      RFS[59]: Opened log for thread 1 sequence 574 dbid -1807329834 branch 810490646
      Tue Apr 23 16:54:20 2013
      RFS[60]: Assigned to RFS process 1680
      RFS[60]: Opened log for thread 1 sequence 577 dbid -1807329834 branch 810490646
      Tue Apr 23 16:54:20 2013
      RFS[61]: Assigned to RFS process 6900
      RFS[61]: Opened log for thread 1 sequence 580 dbid -1807329834 branch 810490646
      Tue Apr 23 16:56:15 2013
      RFS[62]: Assigned to RFS process 6940
      RFS[62]: Opened log for thread 1 sequence 579 dbid -1807329834 branch 810490646
      Tue Apr 23 16:56:15 2013
      RFS[63]: Assigned to RFS process 696
      Tue Apr 23 16:56:15 2013
      RFS[64]: Assigned to RFS process 6656
      RFS[64]: Opened log for thread 1 sequence 576 dbid -1807329834 branch 810490646
      Tue Apr 23 16:56:15 2013
      RFS[65]: Assigned to RFS process 6068
      RFS[65]: Opened log for thread 1 sequence 573 dbid -1807329834 branch 810490646
      RFS[63]: Opened log for thread 1 sequence 575 dbid -1807329834 branch 810490646

      How I do I make sure that the DR site is recovering automatically ?

      Thanks


      Issues I have
        • 1. Re: Standby DB cannot do auto recovery
          JohnWatson
          Please excuse me for asking a very basic question, but have you actually started the managed recovery process?

          ALTER DATABASE RECOVER MANAGED STANDBY DATABASE.....
          • 2. Re: Standby DB cannot do auto recovery
            1004902
            Hello there,

            I did that below is the alert.log

            ARC3 started with pid=24, OS id=6440
            ARC1: Archival started
            ARC2: Archival started
            ARC2: Becoming the heartbeat ARCH
            ARC2: Becoming the active heartbeat ARCH
            ARC3: Archival started
            ARC0: STARTING ARCH PROCESSES COMPLETE
            Tue Apr 23 15:34:29 2013
            alter database recover managed standby database disconnect from session
            Tue Apr 23 15:34:29 2013
            MRP0 started with pid=25, OS id=4120
            started logmerger process
            Tue Apr 23 15:34:34 2013
            Managed Standby Recovery not using Real Time Apply
            Parallel Media Recovery started with 4 slaves
            Waiting for all non-current ORLs to be archived...
            All non-current ORLs have been archived.
            Clearing online redo logfile 1 G:\ORACLE\BP1\ORIGLOGA\LOG_G11M1.DBF
            Clearing online log 1 of thread 1 sequence number 573
            Clearing online redo logfile 1 complete
            Clearing online redo logfile 2 G:\ORACLE\BP1\ORIGLOGB\LOG_G12M1.DBF
            Clearing online log 2 of thread 1 sequence number 566
            Clearing online redo logfile 2 complete
            Clearing online redo logfile 3 G:\ORACLE\BP1\ORIGLOGA\LOG_G13M1.DBF
            Clearing online log 3 of thread 1 sequence number 571
            Clearing online redo logfile 3 complete
            Clearing online redo logfile 4 G:\ORACLE\BP1\ORIGLOGB\LOG_G14M1.DBF
            Clearing online log 4 of thread 1 sequence number 572
            Clearing online redo logfile 4 complete
            Media Recovery Waiting for thread 1 sequence 572 (in transit)
            Completed: alter database recover managed standby database disconnect from session
            Tue Apr 23 15:39:24 2013
            WARN: ARCH: Terminating pid 6328 hung on an I/O operation
            WARN: ARCH: Terminating pid 6644 hung on an I/O operation
            krsv_proc_kill: Killing 1 processes (Process by index)
            krsv_proc_kill: Killing 1 processes (Process by index)
            Tue Apr 23 15:39:26 2013
            RFS[3]: Assigned to RFS process 7016
            RFS[3]: Opened log for thread 1 sequence 572 dbid -1807329834 branch 810490646

            ... Above is the AlerBP1.log on the DR site


            My Question is the logs are coming into the STANDBY archive log. How do I make sure that the incoming archive log are being applied to the Standby DB?


            I also created the STANDBY redo logs as per the doc


            alter database add standby logfile group 10 ('G:\ORACLE\BP1\ORIGLOGA\LOG_G101M1.DBF', 'F:\ORACLE\BP1\MIRRLOGA\LOG_G101M2.DBF' ) size 200M;
            alter database add standby logfile group 11 ('G:\ORACLE\BP1\ORIGLOGA\LOG_G111M1.DBF', 'F:\ORACLE\BP1\MIRRLOGA\LOG_G111M2.DBF' ) size 200M;
            alter database add standby logfile group 12 ('G:\ORACLE\BP1\ORIGLOGA\LOG_G121M1.DBF', 'F:\ORACLE\BP1\MIRRLOGA\LOG_G121M2.DBF' ) size 200M;


            Size is same as primary
            • 3. Re: Standby DB cannot do auto recovery
              Hemant K Chitale
              Have you identified why you are getting these errors ?
              WARN: ARCH: Terminating pid 6328 hung on an I/O operation
              WARN: ARCH: Terminating pid 6644 hung on an I/O operation
              krsv_proc_kill: Killing 1 processes (Process by index)
              krsv_proc_kill: Killing 1 processes (Process by index)
              The archivelogs are arriving out of sequence. It hasn't yet completed receiving sequence 572 which it is waiting for to do media recovery from.


              Hemant K Chitale
              • 4. Re: Standby DB cannot do auto recovery
                Mahir M. Quluzade
                Hi,
                1001899 wrote:
                Hello there,

                I did that below is the alert.log

                ARC3 started with pid=24, OS id=6440
                ARC1: Archival started
                ARC2: Archival started
                ARC2: Becoming the heartbeat ARCH
                ARC2: Becoming the active heartbeat ARCH
                ARC3: Archival started
                ARC0: STARTING ARCH PROCESSES COMPLETE
                Tue Apr 23 15:34:29 2013
                alter database recover managed standby database disconnect from session
                Tue Apr 23 15:34:29 2013
                MRP0 started with pid=25, OS id=4120
                started logmerger process
                Tue Apr 23 15:34:34 2013
                Managed Standby Recovery not using Real Time Apply
                Parallel Media Recovery started with 4 slaves
                Waiting for all non-current ORLs to be archived...
                All non-current ORLs have been archived.
                Clearing online redo logfile 1 G:\ORACLE\BP1\ORIGLOGA\LOG_G11M1.DBF
                Clearing online log 1 of thread 1 sequence number 573
                Clearing online redo logfile 1 complete
                Clearing online redo logfile 2 G:\ORACLE\BP1\ORIGLOGB\LOG_G12M1.DBF
                Clearing online log 2 of thread 1 sequence number 566
                Clearing online redo logfile 2 complete
                Clearing online redo logfile 3 G:\ORACLE\BP1\ORIGLOGA\LOG_G13M1.DBF
                Clearing online log 3 of thread 1 sequence number 571
                Clearing online redo logfile 3 complete
                Clearing online redo logfile 4 G:\ORACLE\BP1\ORIGLOGB\LOG_G14M1.DBF
                Clearing online log 4 of thread 1 sequence number 572
                Clearing online redo logfile 4 complete
                Media Recovery Waiting for thread 1 sequence 572 (in transit)
                Completed: alter database recover managed standby database disconnect from session
                Tue Apr 23 15:39:24 2013
                WARN: ARCH: Terminating pid 6328 hung on an I/O operation
                WARN: ARCH: Terminating pid 6644 hung on an I/O operation
                krsv_proc_kill: Killing 1 processes (Process by index)
                krsv_proc_kill: Killing 1 processes (Process by index)
                Tue Apr 23 15:39:26 2013
                RFS[3]: Assigned to RFS process 7016
                RFS[3]: Opened log for thread 1 sequence 572 dbid -1807329834 branch 810490646

                ... Above is the AlerBP1.log on the DR site


                My Question is the logs are coming into the STANDBY archive log. How do I make sure that the incoming archive log are being applied to the Standby DB?
                You can check , from v$archived_log.applied column, YES or NO.

                On Standby
                 select sequence#, applied from v$archived_log;
                and you must sure your MRP (Media Recovery Process) is runing
                 select process from  v$managed_standby; 
                >
                I also created the STANDBY redo logs as per the doc


                alter database add standby logfile group 10 ('G:\ORACLE\BP1\ORIGLOGA\LOG_G101M1.DBF', 'F:\ORACLE\BP1\MIRRLOGA\LOG_G101M2.DBF' ) size 200M;
                alter database add standby logfile group 11 ('G:\ORACLE\BP1\ORIGLOGA\LOG_G111M1.DBF', 'F:\ORACLE\BP1\MIRRLOGA\LOG_G111M2.DBF' ) size 200M;
                alter database add standby logfile group 12 ('G:\ORACLE\BP1\ORIGLOGA\LOG_G121M1.DBF', 'F:\ORACLE\BP1\MIRRLOGA\LOG_G121M2.DBF' ) size 200M;
                It is good, Standby redo logs required when you are using real time apply, and MAX PROTECTION, MAX AVAILABILITY protection modes.

                Regards
                Mahir M. Quluzade
                http://www.mahir-quluzade.com
                • 5. Re: Standby DB cannot do auto recovery
                  Shivananda Rao
                  Hello,

                  Refer this MOS document *Logs are not shipped to the physical standby database [ID 1130523.1]*
                  Might be helpful.


                  Regards,
                  Shivananda
                  • 6. Re: Standby DB cannot do auto recovery
                    1004902
                    Hi, I checked 1130523.1. I did as per the instructions. I also restarted both Primary and DR. Now I getting new error in Primary
                    @@@@@@@@@@@@@@
                    *** 2013-04-24 17:03:41.503 4645 krsu.c
                    Logged on to standby successfully
                    Client logon and security negotiation successful!

                    *** 2013-04-24 17:09:46.443
                    Error 3113 ending stream destination LOG_ARCHIVE_DEST_2 standby host 'BP1_DR'
                    RFS network connection lost at host 'BP1_DR' error 3113
                    Error 3113 closing standby archive log file at host 'BP1_DR'
                    *** 2013-04-24 17:09:46.443 2957 krsi.c
                    krsi_dst_fail: dest:2 err:3113 force:0 blast:1
                    kcrrwkx: unknown error:3113
                    *** 2013-04-24 17:09:46.459 4320 krsh.c
                    WARN: ARC2: Terminating pid 6756 hung on an I/O operation
                    @@@@@@@@@@@@@@@@@@@@


                    I checked the DB. I found note ID 1152846.1 --> says Juniper firewall change. Change was to the firewall tftp size increase.

                    I spoke to network team. They say they dont have any firewalls between Primary and DR.

                    I am kind of stuck. I did telnet to 1521 listener port from each system to every other system. I was also able to use sqlplus sys@.... as sysdba for each system to every other system. all works fine.

                    The redo_transport_user=sys on both sides in init.ora. and the password file is copied from primary to DR and are in sync

                    I am kind of lost...

                    Thanks guys for ur efforts.
                    • 7. Re: Standby DB cannot do auto recovery
                      Hemant K Chitale
                      You still need to follow up on the I/O errors reported in the standby alert.log


                      Hemant K Chitale
                      • 8. Re: Standby DB cannot do auto recovery
                        1004902
                        Hi Guys

                        Thanks for your help. It was amazing to find the issue after 2 weeks of struggle. It is the firewall. The firewall team disabled "SQL ALG" between the sites. Now the oracle DG is working fine. It has started shipping / roll foward all the pending logs. I opened the DR with read only and see all the changes there...

                        I have to configure 7 databases with oracle DG. I need some tips to monitoring these DB's....

                        Any quick guide with oracle OEM setups?.... I just want to quick start with a cook book instead going though full documentation

                        Many thanks
                        • 9. Re: Standby DB cannot do auto recovery
                          Hemant K Chitale
                          So the
                          WARN: ARC2: Terminating pid 6756 hung on an I/O operation
                          errors were Network I/O --- ARCH writing to the Standby



                          Hemant K Chitale
                          • 10. Re: Standby DB cannot do auto recovery
                            1004902
                            It was due to firewall "SQL ALJ"