How To Modify The Content Of An SPFILE Parameter File
Do one of the following:
A. Use the ALTER SYSTEM command to modify the SPFILE currently in use.
To determine if an spfile is being used:
SQL> select value from v$parameter where name='spfile';
To modify the contents of the spfile:
SQL> alter system set undo_management=auto scope=spfile;
System altered.
This sets undo_management to auto.
SQL> alter system set resource_manager_plan='' scope=spfile;
System altered.
This sets the resource_manager_plan parameter to null.
NOTE:
=====
Parameters starting with an underscore must be enclosed with "" (double quotes) to prevent an ORA-00911: invalid character
B. Use the export method.
1. Export the SPFILE to a PFILE. The resulting PFILE will be an editable ASCII file:
Examples:
SQL> create pfile [= 'pfile_name' ] from spfile [= 'spfile_name'];
SQL> create pfile from spfile;
The PFILE is created in directory $ORACLE_HOME/dbs and named init<SID>.ora.
SQL> create pfile = '/testcasesADC/137483.1/initV1023U.ora' from spfile;
The PFILE is created as '/testcasesADC/137483.1/initV1023U.ora'
SQL> create pfile = '/testcasesADC/137483.1/initV1023U_test.ora' from spfile = '/usupport/64bit/app/oracle/product/10.2.0.3/dbs/spfileV1023U.ora';
The PFILE is created as '/testcasesADC/137483.1/initV1023U_test.ora' and the location of the spfile was explicit.
2. Modify the PFILE parameter value with a text editor and save the file:
undo_management=auto
resource_manager_plan=''
3. Recreate the SPFILE from the modified PFILE:
If the instance is down:
SQL> create spfile='/usupport/64bit/app/oracle/product/10.2.0.3/dbs/spfileV1023U.ora' from pfile='/testcasesADC/137483.1/initV1023U_test.ora';
If the instance is up and you try to recreate the spfile the database was started with a naming conflicts occurs. You will recieve error ORA-32002.
SQL> create spfile='/usupport/64bit/app/oracle/product/10.2.0.3/dbs/spfileV1023U.ora' from pfile='/testcasesADC/137483.1/initV1023U_test.ora';
ERROR at line 1:
ORA-32002: cannot create SPFILE already being used by the instance
To overcome this error specify a different file name and then rename the spfile once the instance is shutdown:
SQL> create spfile='/usupport/64bit/app/oracle/product/10.2.0.3/dbs/spfileV1023U_new.ora' from pfile='/testcasesADC/137483.1/initV1023U_test.ora';.
Do one of the following:
A. Use the ALTER SYSTEM command to modify the SPFILE currently in use.
To determine if an spfile is being used:
SQL> select value from v$parameter where name='spfile';
To modify the contents of the spfile:
SQL> alter system set undo_management=auto scope=spfile;
System altered.
This sets undo_management to auto.
SQL> alter system set resource_manager_plan='' scope=spfile;
System altered.
This sets the resource_manager_plan parameter to null.
NOTE:
=====
Parameters starting with an underscore must be enclosed with "" (double quotes) to prevent an ORA-00911: invalid character
B. Use the export method.
1. Export the SPFILE to a PFILE. The resulting PFILE will be an editable ASCII file:
Examples:
SQL> create pfile [= 'pfile_name' ] from spfile [= 'spfile_name'];
SQL> create pfile from spfile;
The PFILE is created in directory $ORACLE_HOME/dbs and named init<SID>.ora.
SQL> create pfile = '/testcasesADC/137483.1/initV1023U.ora' from spfile;
The PFILE is created as '/testcasesADC/137483.1/initV1023U.ora'
SQL> create pfile = '/testcasesADC/137483.1/initV1023U_test.ora' from spfile = '/usupport/64bit/app/oracle/product/10.2.0.3/dbs/spfileV1023U.ora';
The PFILE is created as '/testcasesADC/137483.1/initV1023U_test.ora' and the location of the spfile was explicit.
2. Modify the PFILE parameter value with a text editor and save the file:
undo_management=auto
resource_manager_plan=''
3. Recreate the SPFILE from the modified PFILE:
If the instance is down:
SQL> create spfile='/usupport/64bit/app/oracle/product/10.2.0.3/dbs/spfileV1023U.ora' from pfile='/testcasesADC/137483.1/initV1023U_test.ora';
If the instance is up and you try to recreate the spfile the database was started with a naming conflicts occurs. You will recieve error ORA-32002.
SQL> create spfile='/usupport/64bit/app/oracle/product/10.2.0.3/dbs/spfileV1023U.ora' from pfile='/testcasesADC/137483.1/initV1023U_test.ora';
ERROR at line 1:
ORA-32002: cannot create SPFILE already being used by the instance
To overcome this error specify a different file name and then rename the spfile once the instance is shutdown:
SQL> create spfile='/usupport/64bit/app/oracle/product/10.2.0.3/dbs/spfileV1023U_new.ora' from pfile='/testcasesADC/137483.1/initV1023U_test.ora';.
No comments:
Post a Comment