4 Replies Latest reply: Sep 17, 2012 1:20 AM by Prashant Dixit RSS

    Set primary database to maximize availability; ORA-02231

    Prashant Dixit
      Hi Folks,


      Today while trying to change Protection Mode for one of my Primary Database i started recieving below provided error codes:

      SQL> alter database set primary database to maximize availability;
      alter database set primary database to maximize availability
      *
      ERROR at line 1:
      ORA-02231: missing or invalid option to ALTER DATABASE



      When tried to change the Log Export Mode using DGMGRL Command Line it started throwing errors about stanby redo logs:

      DGMGRL> edit database 'orcl' set property 'logxptmode'='SYNC';
      edit database 'orcl' set property 'logxptmode'='SYNC';
      Error: ORA-16789: missing standby redo logs



      Details -
      OS: Linux Red Hat 5
      DB: Oracle 10g r1
      Env: VM Machines.


      Current Details:

      SQL> show parameter log_arc

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      log_archive_config string DG_CONFIG=orcl
      log_archive_dest_1 string location=USE_DB_RECOVERY_FILE_
      DEST


      SQL> select protection_mode from v$database;

      PROTECTION_MODE
      --------------------
      MAXIMUM PERFORMANCE



      SQL> select status from v$instance;

      STATUS
      ------------
      OPEN



      Please Assist.


      Thanks
      Prashant Dixit.
        • 1. Re: Set primary database to maximize availability; ORA-02231
          585179
          OracleAnt ... wrote:
          Hi Folks,

          SQL> alter database set primary database to maximize availability;
          alter database set primary database to maximize availability
          *
          ERROR at line 1:
          ORA-02231: missing or invalid option to ALTER DATABASE
          Hi,

          The correct command is
          ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE  AVAILABILITY;
          Hope it helps


          Cheers
          • 2. Re: Set primary database to maximize availability; ORA-02231
            Shivananda Rao
            Please refer this for the right syntax

            http://docs.oracle.com/cd/B28359_01/server.111/b28294/protection.htm

            http://www.oracledistilled.com/oracle-database/high-availability/data-guard/changing-the-data-guard-protection-mode-using-the-data-guard-command-line-interface/
            DGMGRL> edit database 'orcl' set property 'logxptmode'='SYNC';
            edit database 'orcl' set property 'logxptmode'='SYNC';
            Error: ORA-16789: missing standby redo logs
            You need to have created standby redo logs in order to set the LOGXPTMODE to SYNC
            • 3. Re: Set primary database to maximize availability; ORA-02231
              Mahir M. Quluzade
              Hi,

              I think you must add Standby Redo logfiles .
              SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/oracle/ordata/<primary>/log1c.rdo') SIZE 500M;
              SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/oracle/ordata/<primary>/log2c.rdo') SIZE 500M;
              SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/oracle/ordata/<primary>/log3c.rdo') SIZE 500M;
              After that you can edit database primary log transport to SYNC and in broker managed data guard you must chage logxptmode to sync on standby ,too

              With
               
              DGMGRL> edit database  <primary> set property LogXptMode="SYNC";
              DGMGRL> edit database  <standdby> set property LogXptMode="SYNC";
              
              DGMGRL> edit configuration set protection mode AS MAXPROTECTION; 
              
              Max protection is require restart  primary database; 
              
              DGMGRL> shutdown immediate; -- if connected to  primary 
              DGMGRL> startup; 
              DGMGRL> edit configuration set protyection mode as MAXAVAILIBILITY;
              Regards
              Mahir M. Quluzade
              www.mahir-quluzade.com

              Edited by: Mahir M. Quluzade on Sep 17, 2012 10:07 AM
              • 4. Re: Set primary database to maximize availability; ORA-02231
                Prashant Dixit
                Thanks Everyone for replying and for your Valuable suggestions.
                Problem Fixed. !!


                DGMGRL> edit database 'orcl' set property 'logxptmode'='SYNC';
                edit database 'orcl' set property 'logxptmode'='SYNC';
                Error: ORA-16789: missing standby redo logs



                SQL> alter database add standby logfile ('/u01/app/oracle/oradata/orcl/log1.rdo') size 20m;

                Database altered.



                DGMGRL> edit database orcl set property logxptmode="SYNC";
                edit database orcl set property logxptmode="SYNC";
                Property "logxptmode" updated