This discussion is archived
8 Replies Latest reply: Dec 2, 2010 2:37 PM by user76317 RSS

Changing  DB_FILE_NAME_CONVERT

Nilton Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    SCOPE=SPFILE should work. Can you post the on-screen error as it is ?
  • 2. Re: Changing  DB_FILE_NAME_CONVERT
    user222828 Explorer
    Currently Being Moderated
    Modify the parameter in your init file and then re-create spfile from modified init file.
  • 3. Re: Changing  DB_FILE_NAME_CONVERT
    Nilton Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    716550 Pro
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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
    user76317 Newbie
    Currently Being Moderated
    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>

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points