14 Replies Latest reply: Nov 8, 2012 4:39 AM by mseberg RSS

    primary db get error ORA-03113 in maximum protection mode

    972755
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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