Some parameters can be dynamically modified to affect the present instance, while others require the instance to be brought down so that changes can take affect. This remains the same when using PFILE or SPFILE. A simple search on the net will reveal a lot of information regarding PFILE and SPFILE.
Parameter files are by default situated in $ORACLE_HOME/dbs (on UNIX) or %ORACLE_HOME%\database (on Windows) directory.
SPFILE
SPFILE stands for Server Parameter File. It is an extension of the initialization parameter storage mechanism, which allows some additional advantages as compared to the simple text based init.ora file. This feature aims at advancing towards Oracle's self-tuning capabilities. As the name states, the file is present on the server side and is not required on the client to start the instance.
*
It is a binary file and the use of editors to modify it is not supported. The only way of changing parameter values is by using the ALTER SYSTEM SET/RESET command.
*
If using the PFILE option, parameter changes done with the ALTER SYSTEM command need to be manually changed in the init.ora file to reflect the changes on future startups. With SPFILE, the ALTER SYSTEM command can update the binary file as well. This allows the changes to be persistent across startups.
*
Since it is binary, it is prone to becoming corrupt if things go wrong. As a backup strategy, create copies of the SPFILE and convert it to a text-based PFILE for safe keeping.
*
The ISSYS_MODIFIABLE column in V$PARAMETER tells us whether the parameters are static or dynamic. Static parameters require the instance to be restarted while dynamic parameters can take effect immediately upon being changed.
SQL> select distinct issys_modifiable from v$parameter;
ISSYS_MODIFIABLE
---------------------------
DEFERRED
FALSE
IMMEDIATE
If the ISSYS_MODIFIABLE value is set to FALSE for a parameter, it means that the parameter cannot change its value in the lifetime of the instance; the database needs to be restarted for changes to take effect. A parameter set to IMMEDATE value means that it is dynamic and can be set to change the present active instance as well as future database restarts. A parameter set to DEFERRED is also dynamic, but changes only affect subsequent sessions, currently active sessions will not be affected and retain the old parameter value.
*
The PFILE is required from the client from where the instance is being started. The SPFILE is not required to be from the client from where the instance is started. This comes in handy when we are trying to start the database remotely.
*
If you have a standby database, the primary database parameter changes are not propagated to the standby. This needs to be done manually and SPFILE does not aid in this.
*
If no SPFILE exists and one needs to be created, Oracle requires that the instance should be started at least once with the PFILE and then the SPFILE created. SPFILE is the default mode in new releases.
*
In a multi-instance Real Application cluster system, a single copy of the SPFILE can be used for all instances. The SPFILE maintains different format styles to support both the common values for all instances as well as specific values for individual instances.
*
The default behavior of the STARTUP command is changed to look for an SPFILE first, when no PFILE option is explicitly specified.
*
The contents of SPFILE can be obtained from V$SPPARAMETER view. The parameters having ISSPECIFIED column set to TRUE are the ones present in the binary file.
*
You only need to be connected as SYSDBA/SYSOPER to create the SPFILE or PFILE. The database need not be started. This option is useful in case the SPFILE has been corrupted and you need to rebuild it from a PFILE.
Which SPFILE on startup
If you have multiple SPFILEs, it will be confusing to identify which file is linked to which instance. By default, the SPFILE name is spfile
1. The alert log can be looked at. If the log shows the SPFILE parameter in "System parameters with non-default value" section, then it is clear that the instance was started with the pfile internally calling the SPFILE using the parameter.
2. Check the SPFILE parameter value in V$PARAMETER view.
ALTER SYSTEM command for SPFILE
SPFILE can be modified by using the ALTER SYSTEM command. This command allows us to set and reset the parameter values.
alter system set
scope=
comment=<'comments'>
deferred
sid=
The SCOPE clause of the command decides how the changes will take effect. This option can have the following values:
MEMORY - changes are active for the current instance only and are lost on restart.
SPFILE - changes are stored in the SPFILE and are activated on the next startup, the presently active instance is not affected. Static parameters need this option to change their values.
BOTH - changes are effective immediately for the present instance and are stored in SPFILE for future startups. This is the default.
COMMENT is optional and can be specified to store remarks or audit information.
DEFERRED option is used to set parameter values for future connecting sessions. Currently active sessions are not affected and they retain the old parameter value. The option is required for parameters that have the ISSSYS_MODIFIABLE column value in V$PARAMETER set to 'DEFERRED'. It is optional if the ISSYS_MODIFIABLE value is set to 'IMMEDIATE'. For static parameters, this cannot be specified.
The SID clause is valid for Real Application Clusters only. Setting this to a specific SID value changes the parameter value for that particular instance only. Setting this to '*' will affect all instances on the cluster--this is the default if the instance was started using the SPFILE. If the instance was started using PFILE then Oracle assumes the current instance as default.
On starting the instance
When an instance is started, if SPFILE is present, it will be used; otherwise, Oracle searches for a default PFILE. If a PFILE is explicitly specified in the STARTUP command Oracle uses it.
SQL> startup pfile=initdb1.ora
In the above case, the PFILE can also have an SPFILE parameter set so that a different SPFILE can be used to start the instance rather than the default one.
There is no option to specify an SPFILE manually while starting the database. This is identified and picked up by Oracle either by using the SPFILE parameter in PFILE or by searching the default paths.
In the next part of this series, we will cover (with examples) the various ways of working with SPFILE and some common issues encountered.
The first part of this series introduced the System Parameter file and its attributes. Below are some practical examples of working with the SPFILE.
Examples of using SPFILE
The examples below were tried on Oracle 10g (10.1.0.2) on Linux platform (FC1). This will behave the same way on other platforms as well.
Creating an SPFILE.
The actual file names can also be specified in the create command.
SQL> create spfile from pfile;
File created.
Creating PFILE from SPFILE.
The actual file names can also be specified in the create command. This option is also known as exporting the SPFILE. This is helpful in the following ways:
1. As a means of text-based backup.
2. For verifying and analyzing the values currently being used.
3. Modifying the export file and then recreating the SPFILE.
SQL> create pfile from spfile;
File created.
SQL> create pfile='initdb1.ora.bkp' from spfile;
File created.
SQL> create pfile='initdb2.ora.bkp' from spfile='spfiledb2.ora';
File created.
Checking at OS level:
[oracle@amzone dbs]$ file initdb1.ora
initdb1.ora: ASCII text
[oracle@amzone dbs]$ file spfiledb1.ora
spfiledb1.ora: data
[oracle@amzone dbs]$ file initdb1.ora.bkp
initdb1.ora.bkp: ASCII text
Providing the wrong file name gives an OS error:-
SQL> create pfile='initdb2.ora.bkp' from spfile='spfiledbx';
create pfile='initdb2.ora.bkp' from spfile='spfiledbx'
*
ERROR at line 1:
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Reverting to PFILE
Reverting to a PFILE can be done by simply creating a PFILE from SPFILE. Remove the existing SPFILE from the default directory. The SPFILE parameter should no longer be present in the PFILE. Oracle resorts to using the PFILE.
Changing static parameters in the SPFILE
There are parameters that cannot be changed in the lifetime of an instance. The database needs to be restarted for the modified parameters to take effect. If SPFILE is enabled, then the only alternative for changing static parameters is to post the changes to the file and not try changing the current instance.
Changing static parameters requires the SPFILE option for the SCOPE parameter with ALTER SYSTEM. These parameters cannot be changed for the presently active instance, i.e., with scope set to MEMORY/BOTH. All parameter that have the column ISSYS_MODIFIABLE column set to FALSE in the V$PARAMETER view, fall in this category. The scope should be SPFILE so that the parameters take effect on the next restart. The example below shows that the ALTER SYSTEM command will not put the new value in the V$PARAMETER view but will show it in the V$SPPARAMETER view as the SPFILE is updated.
SQL> select name, value, issys_modifiable from v$parameter where name = 'utl_file_dir';
NAME VALUE ISSYS_MODIFIABLE
------------------------------ -------------------- ---------------------------
utl_file_dir FALSE
SQL> alter system set utl_file_dir='/tmp' scope=both;
alter system set utl_file_dir='/tmp' scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set utl_file_dir='/tmp' scope=spfile;
System altered.
SQL> select name, value, issys_modifiable from v$parameter where name = 'utl_file_dir';
NAME VALUE ISSYS_MODIFIABLE
------------------------------ -------------------- ---------------------------
utl_file_dir FALSE
SQL> select name, value, isspecified from v$spparameter where name = 'utl_file_dir';
NAME VALUE ISSPECIFIED
------------------------------ -------------------- ------------------
utl_file_dir /tmp TRUE
Using ALTER SYSTEM command
1. Though SPFILE can be created from a PFILE with SYSDBA/SYSOPER privilege (without actually starting the database), parameter values can only be modified with ALTER SYSTEM that will work after the STARTUP command (also in nomount stage) is issued. The example below uses MAX_ENABLED_ROLES parameter; this one is deprecated in Oracle 10g and mentioned here as a sample case only.
SQL> alter system set max_enabled_roles=20 scope=spfile;
alter system set max_enabled_roles=20 scope=spfile
*
ERROR at line 1:
ORA-01034: ORACLE not available
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 146800640 bytes
Fixed Size 777836 bytes
Variable Size 124789140 bytes
Database Buffers 20971520 bytes
Redo Buffers 262144 bytes
SQL> alter system set max_enabled_roles=20 scope=spfile;
System altered.
Identifying the SPFILE being used from V$PARAMETER.
SQL> select name, value from v$parameter where name = 'spfile';
--alternatively, show parameter spfile can also be used.
NAME VALUE
------------ ------------------------------------------------------------
spfile /home/oracle/product/10.1.0/db1/dbs/spfiledb1.ora
SQL> select count(1) from v$spparameter where value is not null;
COUNT(1)
----------
25
SQL> select count(1) from v$spparameter where isspecified = 'TRUE';
COUNT(1)
----------
26
Storing comments for SPFILE changes
Comments can be added along with the parameter value changes in the ALTER SYSTEM command. Only the last provided comment is available. The previous comment is overwritten with the new comment, when provided as part of the command. The UPDATE_COMMENT column in V$SPPARAMETER stores the provided comment.
In case you plan to maintain a log of the changes, one option would be create a routine and change the parameter using it rather than direct statements. This way, the routine can store additional information like the old value, new value provided, comment provided, change date etc., into another table for audit purpose.
SQL> alter system set user_dump_dest = '/home/oracle/product/10.1.0/db1/admin/db1/udump'
2 comment='Changed for maintenance 070804' scope=both;
System altered.
SQL> select name, value from v$parameter where name='user_dump_dest';
NAME VALUE
------------------------------ ----------------------------------------
user_dump_dest /home/oracle/product/10.1.0/db1/admin/db
1/udump
SQL> select name, value, update_comment from v$spparameter where name = 'user_dump_dest';
NAME VALUE UPDATE_COMMENT
----------------- ---------------------------- ------------------------------
user_dump_dest /home/oracle/product/10.1.0/ Changed for maintenance 070804
db1/admin/db1/udump
Working with Control files
Oracle recommends the following method to change the control file path or name.
From SQL*Plus:
SQL> create pfile from spfile;
File created.
SQL> shutdown;
Database closed.
Database dismounted.
ORACLE instance shut down.
OS command prompt:
[oracle@amzone db1]$ vi initdb1.ora
--manually modify the paths of the controlfile or rename them in init.ora.
[oracle@amzone db1]$ cp control01.ctl control01.org
--making a backup copy, just in case.
[oracle@amzone db1]$ mv control01.ctl db1_01.ctl
[oracle@amzone db1]$ mv control02.ctl db1_02.ctl
[oracle@amzone db1]$ mv control03.ctl db1_03.ctl
[oracle@amzone dbs]$ mv spfiledb1.ora spfilexxx.ora
--delete/move the spfile.
Go back to the SQL*Plus session and start the database:
SQL> startup pfile= '/home/oracle/product/10.1.0/db1/dbs/initdb1.ora';
ORACLE instance started.
Total System Global Area 146800640 bytes
Fixed Size 777836 bytes
Variable Size 124789140 bytes
Database Buffers 20971520 bytes
Redo Buffers 262144 bytes
Database mounted.
Database opened.
SQL> create spfile from pfile;
File created.
Shutdown and restart the database to check on the SPFILE usage.
ORA-07446 error
While experimenting on one account I received ORA-07446 for exporting to a PFILE. I could not identify the cause but it was resolved by restarting the SQL*Plus session again.
SQL> startup nomount;
..
SQL> alter system set max_enabled_roles=20 scope=spfile;
System altered.
SQL> alter system set user_dump_dest='/tmp' scope=both;
System altered.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> create pfile from spfile;
create pfile from spfile
*
ERROR at line 1:
ORA-07446: sdnfy: bad value '�Q����E' for parameter user_dump_dest.
ORA-07446: sdnfy: bad value '' for parameter user_dump_dest.
Metalink note 166608.1 states that on a first conversion of PFILE to SPFILE, shutting down and trying to start the instance in the same SQL*Plus session gives the 0RA-03113 error. The solution is to simply reconnect as SYSDBA again and start the database.
Bug 2372332 affects version 9.2.0.1 (fixed in 9.2.0.2 and above) and expects the parameters in the SPFILE to be of lower case. Modifying a parameter that is present, in upper case, in the SPFILE, will result in multiple entries in the SPFILE. The alternative is to export the parameters to a PFILE, make changes and re-create an SPFILE from it.
On Fedora Core 1 Platform, the PFILE created from SPFILE looks like the example below. As you can see, the parameters are prefixed with '*.', but these do not cause any issue in starting the database from the PFILE. The SPFILE has a similar format but in binary mode.
*.background_dump_dest='/home/oracle/product/10.1.0/db1/admin/db1/bdump'
*.compatible='10.1.0.2.0'
*.control_files='/home/oracle/product/10.1.0/oradata/db1/control01.ctl',
'/home/oracle/product/10.1.0/oradata/db1/control02.ctl',
'/home/oracle/product/10.1.0/oradata/db1/control03.ctl'
*.core_dump_dest='/home/oracle/product/10.1.0/db1/admin/db1/cdump'
*.db_block_size=8192
*.db_cache_size=20971520
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='db1'
...
Conclusion
As mentioned earlier, the SPFILE option aims at advancing towards oracle's self-tuning capabilities. Just like other database files, SPFILE should also be backed up. It is a good practice to export the SPFILE and store the text format, as a safety measure (as long as it is available!).
(http://www.dbasupport.com/oracle/ora10g/spfile01.shtml)
No comments:
Post a Comment