8 Replies Latest reply: Dec 2, 2010 4:37 PM by ecarceller RSS

    Changing  DB_FILE_NAME_CONVERT

    Nilton
      Oracle Database 10g Enterprise Edition
      Oracle Enterprise Linux


      I am trying to learn creating Dataguard configuration on my laptop. Since I am creating both Primary and Standby databases on the same laptop using same Oracle home I need to use db_file_name_convert and Log_file_name_convert. But I just can't change db_file_name_convert parameter. I am using spfile.

      I am getting ORA-2096 errors. That this parameter cannot be modified with these options.

      Can anyone tell me how to change this parameter.

      I have tried various ways,

      ALTER SYSTEM SET DB_FILE_NAME_CONVERT=('ABC','XYZ') SCOPE= BOTH;
      ALTER SYSTEM SET DB_FILE_NAME_CONVERT=('ABC','XYZ') SCOPE=SPFILE;
      ALTER SYSTEM SET DB_FILE_NAME_CONVERT=('ABC','XYZ') SCOPE=BOTH DEFERRED;
      ALTER SYSTEM SET DB_FILE_NAME_CONVERT=('ABC','XYZ') SCOPE=SPFILE DEFERRED;

      Everytime I am getting 2096 error.
        • 1. Re: Changing  DB_FILE_NAME_CONVERT
          USER101
          SCOPE=SPFILE should work. Can you post the on-screen error as it is ?
          • 2. Re: Changing  DB_FILE_NAME_CONVERT
            user222828
            Modify the parameter in your init file and then re-create spfile from modified init file.
            • 3. Re: Changing  DB_FILE_NAME_CONVERT
              Nilton
              This is the error I am getting


              This is the primary database.

              SQL> select name from v$database;

              NAME


              ---------

              XYZ


              SQL> show parameter db_file_name_convert



              NAME TYPE VALUE
              -----------
              ------------------------------------ ------------------------------

              db_file_name_convert string


              SQL> alter system set db_file_name_convert=('ABC','XYZ') scope=spfile;



              alter system set db_file_name_convert=('ABC','XYZ') scope=spfile

              *

              ERROR at line 1:
              ORA-02096: specified initialization parameter is not modifiable with this
              option





              SQL> alter system set db_file_name_convert=('ABC','XYZ') scope=spfile deferred;



              alter system set db_file_name_convert=('ABC','XYZ') scope=spfile deferred


              *

              ERROR at line 1:
              ORA-02096: specified initialization parameter is not modifiable with this
              option



              SQL> alter system set db_file_name_convert=('ABC','XYZ') deferred scope=spfile;


              alter system set db_file_name_convert=('ABC','XYZ') deferred scope=spfile

              *

              ERROR at line 1:
              ORA-02096: specified initialization parameter is not modifiable with this
              option



              SQL> alter system set db_file_name_convert=('ABC','XYZ') scope=memory;


              alter system set db_file_name_convert=('ABC','XYZ') scope=memory

              *


              ERROR at line 1:
              ORA-02096: specified initialization parameter is not modifiable with this
              option


              SQL> alter system set db_file_name_convert=('ABC','XYZ') scope=spfile sid='*';


              alter system set db_file_name_convert=('ABC','XYZ') scope=spfile sid='*'


              *


              ERROR at line 1:
              ORA-02096: specified initialization parameter is not modifiable with this
              option


              SQL> alter system set db_file_name_convert=('ABC','XYZ') scope=spfile sid='*' deferred;



              alter system set db_file_name_convert=('ABC','XYZ') scope=spfile sid='*' deferred

              *


              ERROR at line 1:
              ORA-02096: specified initialization parameter is not modifiable with this
              option
              • 4. Re: Changing  DB_FILE_NAME_CONVERT
                Nilton
                I know this. I actually tried to change init file and then restart database. It will indeed take the value and database restarts with no problem.

                But I want to know why SPFILE can't be modified. Is everyone facing the same problem ? or just me ? Is it not possible to change this parameter with spfile at all ?

                I may be making a mistake somewhere. But can't figure out where .
                • 5. Re: Changing  DB_FILE_NAME_CONVERT
                  Anil Malkai
                  Hello ,
                  DB_FILE_NAME_CONVERT is not modifiable using alter system command. you will have to set it in pfile and start your db using this file.

                  next this is thing you are trying to set DB_FILE_NAME_CONVERT to change the db name. its wrong. this is used for changing the datafile location.

                  lets say your prumary database has its datafiles at f:\oracle\datafile. but when it comes to standby location you dont have this location or dont want to keep it at f:\ itself. then use this parameter to change the directiory structure for standby database.

                  ex:
                  SQL> sho parameter DB_FILE_NAME_CONVERT

                  NAME TYPE VALUE
                  ------------------------------------ ----------- ------------------------------
                  db_file_name_convert string H:\ORADATA\, F:\ORADATA\

                  Anil Malkai
                  • 6. Re: Changing  DB_FILE_NAME_CONVERT
                    user222828
                    It's modifiable at the session level and that's it.

                    SQL> show parameter db_file_name

                    NAME TYPE VALUE
                    ------------------------------------ ----------- ------------------------------
                    db_file_name_convert string


                    SQL> alter system set db_file_name_convert='/u02/oradata/jamie','/u02/oradata/jamie1';
                    alter system set db_file_name_convert='/u02/oradata/jamie','/u02/oradata/jamie1'
                    *
                    ERROR at line 1:
                    ORA-02096: specified initialization parameter is not modifiable with this
                    option

                    SQL> alter session set db_file_name_convert='/u02/oradata/jamie','/u02/oradata/jamie1';

                    Session altered.

                    SQL> show parameter db_file_name;

                    NAME TYPE VALUE
                    ------------------------------------ ----------- ------------------------------
                    db_file_name_convert string /u02/oradata/jamie, /u02/orada
                    ta/jamie1


                    Thanks
                    • 7. Re: Changing  DB_FILE_NAME_CONVERT
                      USER101
                      Here is the answer
                      SQL>  select name,value,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name like 'db_file_name%';
                      
                      NAME                           VALUE                                   ISSES ISSYS_MOD
                      ------------------------------ --------------------------------------- ----- ---------
                      db_file_name_convert                                                   TRUE  FALSE
                      • 8. Re: Changing  DB_FILE_NAME_CONVERT
                        ecarceller
                        DBMS 10.2.0.2
                        I had the same issue and the explanation about ISSYS_MODIFIABLE being FALSE seemed to settle it. I mean it is very clear, right? Still I had in my head that I had done that in the past. Then I realized that I had started the instance with a pfile and I had no spfile. So I created the spfile from pfile, shutdown the engine started up nomount and voila. Experts: explanations would be welcome.

                        SQL> create spfile from pfile;

                        File created.

                        SQL> shutdown immediate
                        ORA-01507: database not mounted


                        ORACLE instance shut down.
                        SQL> startup nomount
                        ORACLE instance started.

                        Total System Global Area 1073741824 bytes
                        Fixed Size                  1264892 bytes
                        Variable Size             905970436 bytes
                        Database Buffers          155189248 bytes
                        Redo Buffers               11317248 bytes
                        SQL> alter system set LOG_FILE_NAME_CONVERT='/u01/oracle/proddata/','/u01/tofixora/fixtdata/' scope=spfile;

                        System altered.

                        SQL> c/LOG/DB/
                        +1* alter system set DB_FILE_NAME_CONVERT='/u01/oracle/proddata/','/u01/tofixora/fixtdata/' scope=spfile+
                        SQL> /

                        System altered.

                        SQL>