Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Sep 8, 2004

Oracle's SPFILE - Part 2

By Amar Kumar Padhi

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 'o?=Qo?=o?=o?=o?=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).

» See All Articles by Columnist Amar Kumar Padhi



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date