This discussion is archived
10 Replies Latest reply: May 7, 2013 2:19 AM by 1004902 RSS

Standby DB cannot do auto recovery

1004902 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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
    MahirM.Quluzade Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    It was due to firewall "SQL ALJ"

Legend

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