This discussion is archived
14 Replies Latest reply: Nov 8, 2012 2:39 AM by mseberg RSS

primary db get error ORA-03113 in maximum protection mode

972755 Newbie
Currently Being Moderated
primary db pfile :


cds.__db_cache_size=230686720
cds.__java_pool_size=4194304
cds.__large_pool_size=4194304
cds.__shared_pool_size=71303168
cds.__streams_pool_size=0
*.archive_lag_target=0
*.background_dump_dest='/opt/oracle/database/cds/bdump'
*.control_files='+data/control01.ctl','+data/control02.ctl'
*.db_name='cds'
*.db_recovery_file_dest_size=2147483648
*.db_recovery_file_dest='+data1'
*.db_unique_name='cds'
*.dg_broker_start=FALSE
*.fal_client='cds'
*.fal_server='cdssty'
*.log_archive_config='DG_CONFIG=(cds,cdssty)'
*.log_archive_dest_1='LOCATION=+data2 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cds'
*.log_archive_dest_2='SERVICE=cdssty LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cdssty'
*.log_archive_dest_state_2='ENABLE'
cds.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=1
*.log_archive_min_succeed_dest=1
*.sga_target=300m
*.standby_archive_dest='+data2'
*.standby_file_management='AUTO'
*.undo_management='auto'
*.undo_tablespace='undocds01'
*.user_dump_dest='/opt/oracle/database/cds/udump'

standby db pfile :-
cdssty.__db_cache_size=213909504
cdssty.__java_pool_size=4194304
cdssty.__large_pool_size=4194304
cdssty.__shared_pool_size=88080384
cdssty.__streams_pool_size=0
*.archive_lag_target=0
*.background_dump_dest='/opt/oracle/database/cdssty/bdump'
*.control_files='+DATA01/control01.ctl','+DATA01/control02.ctl'#Restore Controlfile
*.db_file_name_convert='+data','+data01'
*.db_name='cds'
*.db_recovery_file_dest_size=2147483648
*.db_recovery_file_dest='+data02'
*.db_unique_name='cdssty'
*.dg_broker_start=FALSE
*.fal_client='CDSSTY'
*.fal_server='CDS'
*.log_archive_config='DG_CONFIG=(cdssty,cds)'
*.log_archive_dest_1='LOCATION=+data03 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cdssty'
*.log_archive_dest_2='SERVICE=cds LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cds'
cdssty.log_archive_dest_state_1='ENABLE'
cdssty.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=1
*.log_archive_min_succeed_dest=1
cdssty.log_archive_trace=0
*.log_file_name_convert='+data','+data01'
*.sga_target=300m
*.standby_archive_dest='+data03'
cdssty.standby_archive_dest='+DATA03'
*.standby_file_management='AUTO'
*.undo_management='auto'
*.undo_tablespace='undocds01'
*.user_dump_dest='/opt/oracle/database/cdssty/udump'


primary database is in maximum protection mode

stanbdy db has two standby redolog file groups

llistener at both the end is up

when i try to open primary databsae i get the error blow:

ERROR at line 1:
ORA-03113: end-of-file on communication channel

i read the alert log and its log are as blow:


Database mounted in Exclusive Mode
Completed: ALTER DATABASE MOUNT
Wed Nov 7 04:28:00 2012
alter database open
Wed Nov 7 04:28:01 2012
LGWR: STARTING ARCH PROCESSES
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0 started with pid=16, OS id=13841
Wed Nov 7 04:28:01 2012
LGWR: Primary database is in MAXIMUM PROTECTION mode
LGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR
LGWR: Minimum of 1 synchronous standby database required
Wed Nov 7 04:28:01 2012
Errors in file /opt/oracle/database/cds/bdump/cds_lgwr_13720.trc:
ORA-16072: a minimum of one standby database destination is required
Wed Nov 7 04:28:01 2012
Errors in file /opt/oracle/database/cds/bdump/cds_lgwr_13720.trc:
ORA-16072: a minimum of one standby database destination is required
LGWR: terminating instance due to error 16072
Instance terminated by LGWR, pid = 13720

and the contents of /opt/oracle/database/cds/bdump/cds_lgwr_13720.trc are:

/opt/oracle/database/cds/bdump/cds_lgwr_13720.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /opt/oracle/product
System name: Linux
Node name: Host1
Release: 2.6.18-128.el5
Version: #1 SMP Wed Dec 17 11:42:39 EST 2008
Machine: i686
Instance name: cds
Redo thread mounted by this instance: 1
Oracle process number: 6
Unix process pid: 13720, image: oracle@Host1 (LGWR)

*** 2012-11-07 04:28:01.123
*** SERVICE NAME:() 2012-11-07 04:28:01.122
*** SESSION ID:(45.1) 2012-11-07 04:28:01.122
Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR
Standby database verification failed:16072
ORA-16072: a minimum of one standby database destination is required
error 16072 detected in background process
ORA-16072: a minimum of one standby database destination is required


if i open primary db in maximum availability mode its working fine ..
the problme is only with maximum protection mode


plz tell me what is wrong with my configuration
  • 1. Re: primary db get error ORA-03113 in maximum protection mode
    mseberg Guru
    Currently Being Moderated
    On the Primary

    startup mount

    alter database set standby database to maximize performance;

    shutdown immediate;

    startup mount

    alter database open;

    Best Regards

    mseberg

    I don't see these parameters :

    compatible
    remote_login_passwordfile='EXCLUSIVE'


    and this is set too low

    log_archive_max_processes ( try 6 )

    You should have both of these also

    LOG_ARCHIVE_DEST_STATE_1
    LOG_ARCHIVE_DEST_STATE_2

    Edited by: mseberg on Nov 6, 2012 5:12 AM
  • 2. Re: primary db get error ORA-03113 in maximum protection mode
    972755 Newbie
    Currently Being Moderated
    Hi ,thanx for reply ...

    but i have no problme when i try to open primary db in maximum availability its open without any error,but when i try to open it in maximum protection mode its will gave an error..i post later..i have set max archive process to 4 at both primary and standby as u suggest me in your reply ..but ..still the problem is not solved..i m getting the same error
    help me out to find the solution for that

    thanx in advance
  • 3. Re: primary db get error ORA-03113 in maximum protection mode
    Shivananda Rao Guru
    Currently Being Moderated
    When you are using your Pirmary database in Maximum Protection mode, Oracle recommends that you have a minimum of 2 standby databases.
    ORA-16072: a minimum of one standby database destination is required
    LGWR: terminating instance due to error 16072
    Instance terminated by LGWR, pid = 13720
    This error is quite usual when using maximum protection and having only 1 standby database.

    Refer this http://docs.oracle.com/cd/B28359_01/server.111/b28294/protection.htm The document contains the details about the Modes and on how to change the Protection mode on your primary database.
  • 4. Re: primary db get error ORA-03113 in maximum protection mode
    mseberg Guru
    Currently Being Moderated
    Hello again;

    Oracle recommends that a minimum of two standby databases be used to protect a primary database that runs in maximum protection mode to prevent a single standby database failure from causing the primary database to shut down.

    Its a good idea to run maximize performance mode for awhile at least until you are sure your system is stable to switch to maximum protection mode.

    Here's the parameters I look for on any Data Guard setup :

    FAL_SERVER
    FAL_CLIENT          ( Not needed in Oracle 11 )
    STANDBY_FILE_MANAGEMENT
    DB_UNIQUE_NAME
    DB_FILE_NAME_CONVERT ( Can work without )
    LOG_FILE_NAME_CONVERT ( Can work without )
    LOG_ARCHIVE_DEST_1
    LOG_ARCHIVE_DEST_2
    LOG_ARCHIVE_DEST_STATE_1
    LOG_ARCHIVE_DEST_STATE_2
    LOG_ARCHIVE_MAX_PROCESSES
    REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE
    COMPATIBLE

    Query to check mode
    select open_mode from v$database;
    Best Regards

    mseberg
  • 5. Re: primary db get error ORA-03113 in maximum protection mode
    972755 Newbie
    Currently Being Moderated
    Hi, thanx for ur reply ,

    but i m using oracle 10g release 2, as per my knowledge min standby database require for maximum protection mode is 1.
    plz correct me if i m wrong .
  • 6. Re: primary db get error ORA-03113 in maximum protection mode
    Shivananda Rao Guru
    Currently Being Moderated
    969752 wrote:
    Hi, thanx for ur reply ,

    but i m using oracle 10g release 2, as per my knowledge min standby database require for maximum protection mode is 1.
    plz correct me if i m wrong .
    No. Oracle recommends to have 2 standby database when you are using Maximum Protection mode.

    Though the document I posted in my preivous post is with 11g, the prinicple involved in the Modes of Dataguard are all the same ;)

    Hope this helps !
    http://docs.oracle.com/cd/B19306_01/server.102/b14239/log_transport.htm#i1178539
    http://docs.oracle.com/cd/B19306_01/server.102/b14239/concepts.htm#i1029121
  • 7. Re: primary db get error ORA-03113 in maximum protection mode
    mseberg Guru
    Currently Being Moderated
    Hello;

    You don't need two Standby and its not the cause of your error.
    oerr ora 16072
    16072, 00000, "a minimum of one standby database destination is required"
    // *Cause:  No standby database archive log destinations were specified.
    // *Action: Specify a standby archive log destination in the initialization
    //          parameter file.
    recommends is NOT the same word as require

    1 Standby is 1 standby, Oracle documentation should not be twisted to some other meaning.

    OERR: ORA-16072 a minimum of one standby database destination is required [ID 172804.1] minimum of one Not two, not a minimum of two.


    Source
    1.4 Data Guard Protection Modes

    Data Guard Concepts and Administration 10g Release 2 (10.2) B14239-05
    To ensure data loss cannot occur, the primary database shuts down if a fault prevents it from writing its redo stream to the standby redo log of at least one transactionally consistent standby database.
    However it makes no sense to Maximum Protection with only one Standby in my opinion.

    Best Regards

    mseberg
  • 8. Re: primary db get error ORA-03113 in maximum protection mode
    LaserSoft Journeyer
    Currently Being Moderated
    Hi

    To participate in MAXIMUM PROTECTION the following requirements must be met:

    Redo Archival Process : LGWR
    Network Transmission mode : SYNC
    Disk Write Option : AFFIRM
    Standby Redo Logs : Yes

    Important Point is :
    ===========

    Oracle recommends that a minimum of two standby databases be used to protect a primary database that runs in maximum protection mode to prevent a single standby database failure from causing the primary database to shut down.

    Thanks
    LaserSoft
  • 9. Re: primary db get error ORA-03113 in maximum protection mode
    972755 Newbie
    Currently Being Moderated
    hi thanx again,

    i read both the oracle links u suggest me in your previous post ,but i have not found any thing like,min 2 standby database require for maximum protection mode,

    in first link :-

    5.6.1.1 Maximum Protection Mode

    This protection mode ensures that no data loss will occur if the primary database fails. To provide this level of protection, the redo data needed to recover each transaction must be written to both the local online redo log and to the standby redo log on at least one standby database before the transaction commits_. To ensure data loss cannot occur, the primary database shuts down if a fault prevents it from writing its redo stream to at least one remote standby redo log. For multiple-instance RAC databases, Data Guard shuts down the primary database if it is unable to write the redo records to at least one properly configured database instance. The maximum protection mode requires that at least one standby instance has a standby redo log and the LGWR, SYNC, andAFFIRM attributes be used on the LOG_ARCHIVE_DEST_n parameter for this destination.



    and second link:-

    Maximum protection This protection mode ensures that no data loss will occur if the primary database fails. To provide this level of protection, the redo data needed to recover each transaction must be written to both the local online redo log and to the standby redo log on at least one standby database before the transaction commits_. To ensure data loss cannot occur, the primary database shuts down if a fault prevents it from writing its redo stream to the standby redo log of at least one transactionally consistent standby database.

    Plz correct me if i m wrong

    thanx in advance
  • 10. Re: primary db get error ORA-03113 in maximum protection mode
    972755 Newbie
    Currently Being Moderated
    Hi thanx for reply ,

    plz check my Pfile in my first post all the requirement are met,and i have not found anyting like , minimum of two standby databases be used to protect a primary database for
    maximum protection mode.

    plz sugget me some oracle recommendations to clear me that.might be u r right but for 11g, but i dont think its also in 10g

    plz correct me if i m wrong.
  • 11. Re: primary db get error ORA-03113 in maximum protection mode
    Shivananda Rao Guru
    Currently Being Moderated
    I am copying from the document http://docs.oracle.com/cd/B19306_01/server.102/b14239/log_transport.htm#i1178539 that I posted in my previous post.
    Note:
    Oracle recommends that a Data Guard configuration that is running in maximum protection mode contains at least two standby databases that  meet the requirements listed in Table 5-2. That way, the primary database can continue processing if one of the standby databases cannot receive redo data from the primary database.
    Oracle says that you require min of 2 standby so that if the standby 1 is not in sync with primary, then atleast standby 2 remains in sync with primary. If both the standbys are out of sync, then Oracle forcefully brings down your primary database.

    In your case, you are using Maximum Protection mode with only 1 standby. Still the dataguard principle is achievable, but I suspect that your standby is out of sync and hence Oracle is forcefully bringing down your primary database. Change the protection mode, and look out for the reason why the standby is out of sync.
  • 12. Re: primary db get error ORA-03113 in maximum protection mode
    LaserSoft Journeyer
    Currently Being Moderated
    Book : Oracle Database 10g-High Availability with RAC, Flashback & Data Guard

    Matthew Hart
    Scott Jesse
    McGraw-Hill/Osborne

    Maximum Protection

    To provide this level of protection, the redo data needed to recover each transaction must be written to both the local redo log and to a standby redo log on at least one standby database before the transaction commits. To ensure that data loss cannot occur, the primary database will shut down if a fault prevents it from writing its redo stream to at least one remote standby redo log.

    To participate in maximum protection, the following requirements must be met:

    The primary must use the LGWR SYNC AFFIRM attributes in the archive destination used to archive to the standby.

    The standby must be configured with standby redo logs.

    At least one standby must be available.

    By using the LGWR SYNC AFFIRM attributes in the archive destination going to the standby, we are stating that a commit complete message will not be returned to the user until we have successfully written the redo change into the standby redo logs on the standby host. Only after that write has been confirmed can the user continue with other transactions. As you can see, the speed of the network will have a great impact on the speed at which commits on the primary database can complete. You would not want to have your standby participating in maximum protection mode over a WAN or over a high latency network. A good recommendation for maximum protection mode is to have at least two standby databases available to satisfy the primary's requirements for maximum protection with one of the standby databases available on a high-speed low-latency network.
  • 13. Re: primary db get error ORA-03113 in maximum protection mode
    972755 Newbie
    Currently Being Moderated
    Hi all Thanx For Ur Ans And Suggestions ,
    i have fix my problem its due to* AFFIRM attribute, i forget to set that and by default its NOAFFIRM,*
    might be it will help u in near future,
    and here i just want to add one more thing that,min no of database for maximum protection is 1 its not 2,i have implemented that,and my data guard running fine,with one standby in maximum protection mode

    oracle recommend to use min 2 standby db for Maximum Protection (MP),but piratically its 1 ,you can use data guard in Maximum Protection with 1 primary and 1 standby,
    as per my knowledge oralce suggest 2 min standby db for Maximum Protection to Achieve Maximum Availability also with Maximum Protection, so that if one standby db not accessible for write redo,primary still available for operation bocz its already have one standby to write.so we can achive both Maximum Protection and Maximum Availability by using more then 1 standby in data guard.

    so its clear now
    by using more the one standby db we can Achieve Maximum Availability with Maximum Protection,one stbd down,one is available for writing so primary still available (its Maximum Availability) for operation with no data loss(Maximum Protection)

    Plz correct me If I m wrong ....
  • 14. Re: primary db get error ORA-03113 in maximum protection mode
    mseberg Guru
    Currently Being Moderated
    Hello again;

    "AFFIRM attribute, I forget to set that and by default its NOAFFIRM"

    Thanks for the follow up. I was about to set up a test on this because of the BS answer you were getting about "Maximum Protection".


    "you can use data guard in Maximum Protection with 1 primary and 1 standby"

    Yes you can. If you look at my posts I state "recommends is NOT the same word as require".


    The main issue with only Standby is a single point of failure. It the network drops for a few minutes your Primary is down.
    It creates a new 24/7 system. But 1 is all that is needed.

    Thanks again for the follow up.

    Best Regards

    mseberg

Legend

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