4 Replies Latest reply on Apr 9, 2013 1:16 PM by mseberg

    scope=spfile in DB_FILE_NAME_CONVERT

    Neo-b
    Neo-b Apr 9, 2013 12:51 PM
      Hello All,

      I am using Oracle 11g R2 and I am configuring Data guard between a primary database (RAC 2 nodes) and a physical standby database single instance.

      I want to create my standby database without shutting down the primary database, one of the requirement is to set the below 2 parameters :
      alter system set DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/STANDBY','+DATA/ORCL/DATAFILE' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/STANDBY','+DATA/ORCL/ONLINELOG' scope=spfile;
      These parameters cannot be set at the memory level, it can be set only if i mentioned "scope=spfile"
      Does that require to restart my primary database? or it will directly take affect even if scope=spfile?

      Regards,
      • 1947 Views
      • Tags:
        • 1. Re: scope=spfile in DB_FILE_NAME_CONVERT
          TSharma-Oracle
          TSharma-Oracle Apr 9, 2013 12:55 PM (in response to Neo-b)
          If you cannot set the parameter in memory, it will require a restart. So YES you will have to restart in order to make these parameter effective.
          • 2. Re: scope=spfile in DB_FILE_NAME_CONVERT
            mseberg
            mseberg Apr 9, 2013 12:57 PM (in response to Neo-b)
            Hello;

            DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT will throw an ORA-02096 or ORA-02095 if you use scope=both.

            Database restart ( bounce required )

            So you have to use scope=spfile;

            Best Regards

            mseberg
            • 3. Re: scope=spfile in DB_FILE_NAME_CONVERT
              Neo-b
              Neo-b Apr 9, 2013 1:13 PM (in response to mseberg)
              Dataguard is working even without restarting the database. log transmission from primary to standby !
              • 4. Re: scope=spfile in DB_FILE_NAME_CONVERT
                mseberg
                mseberg Apr 9, 2013 1:16 PM (in response to Neo-b)
                I tested and here's my results:
                SQL> alter system set DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/STANDBY','+DATA/ORCL/DATAFILE' scope=both;
alter system set DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/STANDBY','+DATA/ORCL/DATAFILE' scope=both
                 *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this
option

SQL> alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/STANDBY','+DATA/ORCL/ONLINELOG' scope=both;    
alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/STANDBY','+DATA/ORCL/ONLINELOG' scope=both
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
                The Oracle document says ALTER SESSION for both but my test results say above.

                If it works, its works.

                Best Regards

                mseberg
                1 person found this helpful