6 Replies Latest reply: Mar 26, 2013 12:34 PM by 977635 RSS

    ORA-16020: fewer destinations available than specified by LOG_ARCHIVE_MIN_S

    977635
      Hi all,

      I'm testing setup of primary and standby database and everything seems to work normally.

      Now, I want to set up DG Broker so I can test switching it over and back.

      So, I went through steps by Gavin (ref. http://gavinsoorma.com/2010/03/11g-data-guard-broker-dgmgrl-configuration-quick-steps/).
      All went well until I showed configuration after enabling the broker.
      Then I got error: ORA-16020: fewer destinations available than specified by LOG_ARCHIVE_MIN_SUCCEED_DEST

      So, then I check the DG log files and on the standby database, I see errors:
      2013-03-26 11:03:52.457                      Executing SQL [ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH sid='*']
      2013-03-26 11:03:52.457                      SQL Execution error=604, sql=[ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH sid='*']. See error stack below.
      2013-03-26 11:03:52.457                        ORA-00604: error occurred at recursive SQL level 1
      2013-03-26 11:03:52.457                        ORA-02097: parameter cannot be modified because specified value is invalid
      2013-03-26 11:03:52.457                        ORA-16020: fewer destinations available than specified by LOG_ARCHIVE_MIN_SUCCEED_DEST
      2013-03-26 11:03:52.458                      RSM0: Failed to set property LogArchiveMinSucceedDest
      2013-03-26 11:03:52.458                      RSM0: return status of set property 'LogArchiveMinSucceedDest' is ORA-16788
      2013-03-26 11:03:52.458 02010000  1827537550 Operation EDIT_RES_PROP canceled during phase 1, error = ORA-16788
      2013-03-26 11:03:52.458 02010000  1827537550 DMON: Entered rfm_release_chief_lock() for EDIT_RES_PROP
      2013-03-26 11:03:52.460 02010000  1827537550 DMON: EDIT_RES_PROP operation completed
      So now, i check the DG InconsistentProperties
      First, standby:   tststby2
      ================
      DGMGRL> show database 'tststby2' InconsistentProperties;
      INCONSISTENT PROPERTIES
         INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE
              tststby2 LogArchiveMinSucceedDest                    1                                         1
      
      Then, primary:  tstprim2
      ================
      DGMGRL> show database 'tstprim2' InconsistentProperties;
      INCONSISTENT PROPERTIES
         INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE
      So, looking now in the database, I check standby and it shows:

      log_archive_min_succeed_dest integer 1

      And, checking primary, it shows:

      log_archive_min_succeed_dest integer 1

      Okay, so what's next?
      I tried resetting the LogArchiveMinSucceedDest to see if that would help, but it just gave me error.
      DGMGRL> edit database 'tststby2' set property 'LogArchiveMinSucceedDest'=1;
      Error: ORA-16788: unable to set one or more database configuration property values
      I do see that my LOG_ARCHIVE_DEST_STATE_2 is set to ENABLE in primary, and is set to DEFER in standby, but it doesn't seem to let me change it in standby.

      What else can I check?


      Adding a couple of more things that might shed light on this:
      ======================================
      on tstprim2 (primary)
      ==============
      
      SQL> sho parameter convert
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      db_file_name_convert                 string      tststby2, tstprim2
      log_file_name_convert                string      tststby2, tstprim2
      on tststby2 (standby)
      ==============
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      db_file_name_convert                 string      tstprim2, tststby2
      log_file_name_convert                string      tstprim2, tststby2
      
      SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;
      
      PROCESS   STATUS
      --------- --------------------
      ...
      ARCH      CLOSING
      ARCH      CLOSING
      RFS       IDLE
      RFS       IDLE
      RFS       IDLE
      RFS       IDLE
      MRP0      APPLYING_LOG  <== as you can see, the standby database is working properly
      
      35 rows selected.
      Edited by: 974632 on Mar 26, 2013 9:06 AM
        • 1. Re: ORA-16020: fewer destinations available than specified by LOG_ARCHIVE_MIN_S
          mseberg
          Hello;

          Not 100 percent sure but I would check this note :

          Broker overrides local LOG_ARCHIVE_DEST_n destination on Bystander Standby [ID 1364467.1]

          Best Regards

          mseberg
          • 2. Re: ORA-16020: fewer destinations available than specified by LOG_ARCHIVE_MIN_S
            977635
            Okay, I will check that.
            Meanwhile, I'm trying a standard manual switchover, which worked fine.
            But, when I try to switch logfiles, it is just hanging.
            When i check alert log, it shows errors:
            Tue Mar 26 12:24:31 2013
            ARCH: Archival stopped, error occurred. Will continue retrying
            ORACLE Instance tststby2 - Archival Error
            ORA-16014: log 1 sequence# 380 not archived, no available destinations
            ORA-00312: online log 1 thread 1: '/redo1/tststby2/redo01a.rdo'
            ORA-00312: online log 1 thread 1: '/redo2/tststby2/redo01b.rdo'
            Tue Mar 26 12:24:33 2013
            krsk_srl_archive_int: Enabling archival of deferred physical standby SRLs
            krsk_srl_archive_int: Enabling archival of deferred physical standby SRLs
            I have a feeling it might be because I don't have my dest_2 set correctly.
            The reason I suspect that is because, when I try to enable dest_2, I see error:
            SQL> alter system archive log all;
            alter system archive log all
            *
            ERROR at line 1:
            ORA-16020: fewer destinations available than specified by LOG_ARCHIVE_MIN_SUCCEED_DEST
            
            
            SQL> alter system set log_archive_dest_state_2=enable;
            alter system set log_archive_dest_state_2=enable
            *
            ERROR at line 1:
            ORA-02097: parameter cannot be modified because specified value is invalid
            ORA-16028: new LOG_ARCHIVE_DEST_STATE_2 causes less destinations than LOG_ARCHIVE_MIN_SUCCEED_DEST requires
            My dest_2 is set at (remember this is now primary on my standby server):
            log_archive_dest_2                   string      SERVICE=tststby2 LGWR ASYNC 
                                                             VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
                                                             DB_UNIQUE_NAME=tststby2
            My dest_1 is set at:
            log_archive_dest_1                   string      LOCATION=USE_DB_RECOVERY_FILE_DEST
                                                             VALID_FOR=(ALL_LOGFILES,ALL_ROLES) 
                                                             DB_UNIQUE_NAME=tstprim2
            Okay, so now, out of desperation, here's what I did, which seems to have worked.
            (I had previously also enabled dest_state_3 per the instructions in the original web page notes)
            SQL> alter system set log_archive_dest_3='LOCATION=/arch/tststby2';
            
            System altered.
            
            SQL> alter system archive log all;
            
            System altered.
            Now, when I check my location, i see new files just put into that location.
            $ <tststby2> /arch/tststby2>ll
            total 13484
            drwxr-xr-x 2 oracle dba    4096 Mar 26 12:47 ./
            drwxr-xr-x 6 oracle 300    4096 Mar 22 10:56 ../
            -rw-r----- 1 oracle dba 7453184 Mar 26 12:47 log_1_376_809878919.arc
            -rw-r----- 1 oracle dba 6177792 Mar 26 12:47 log_1_377_809878919.arc
            -rw-r----- 1 oracle dba   14336 Mar 26 12:47 log_1_378_809878919.arc
            -rw-r----- 1 oracle dba   96768 Mar 26 12:47 log_1_380_809878919.arc
            -rw-r----- 1 oracle dba   47616 Mar 26 12:47 log_1_381_809878919.arc
            One other point of reference is that I created my primary and standby based on your webpage:
            http://www.visi.com/~mseberg/data_guard_on_oracle_11_step_by_step.html
            The only main differences are path locations and names of primary and standby database.

            (Thank you mseberg. You are very good explaining things very well.)
            • 3. Re: ORA-16020: fewer destinations available than specified by LOG_ARCHIVE_MIN_S
              mseberg
              Hello again;

              You may be compounding the problem :
              SQL> alter system set log_archive_dest_3='LOCATION=/arch/tststby2';
              Once you start using Broker you cannot mix in SQL. If you do it will lead to Inconsistent Properties as the Broker configuration will not match the database configuration.

              Once you start using Broker, you have to use Broker only. If you create a Pfile from Spfile after a Broker you will probably notice Broker has added entries to the spfile.

              Anyway 1364467.1 is my best shot for the other issue.

              Best Regards

              mseberg
              • 4. Re: ORA-16020: fewer destinations available than specified by LOG_ARCHIVE_MIN_S
                977635
                Okay, thanks mseberg.

                I will try the steps in that note.

                For now, all seems well, except when I check switchover_status on primary (was standby):
                SQL> select switchover_status from v$database;
                
                SWITCHOVER_STATUS
                --------------------
                NOT ALLOWED
                • 5. Re: ORA-16020: fewer destinations available than specified by LOG_ARCHIVE_MIN_S
                  mseberg
                  Hello again;

                  If you force a log switch do these things happen :

                  The log is transferred to the new standby.
                  The log is applied on the new standby.
                  The switchover status updates from NOT ALLOWED to something better.


                  I generally run this on both sides.
                  select switchover_status from v$database;
                  I have found the database in Standby sometimes shows and issue not seen from the Primary.


                  Also after a switchover the status may take a few minutes to clear, but probably less than five.


                  I like this to monitor :

                  http://www.visi.com/~mseberg/data_guard/monitor_data_guard_transport.html

                  Thanks for the great details.


                  Best Regards

                  mseberg
                  • 6. Re: ORA-16020: fewer destinations available than specified by LOG_ARCHIVE_MIN_S
                    977635
                    Thanks again for all your help mseberg.
                    Since I already messed up my environment with setting the dest_3, I'm going to recreate my entire DG test environment (per your document again) and start over.
                    This time, however, I need to create both instances with the same name - this is how our production environment is set up.
                    Then, I'm going to try to enable the broker and get that working properly.

                    I'm just a bit confused how to use the same name for the SID on both servers, but I'll give it a try.
                    I have our production environment to look at for comparisons.