Every Oracle instance needs an initialization parameters to startup. This file determines the attributes and corresponding values for the instance as well as the database.
A few examples of these attributes(parameters) are listed below
o Database Name – DB_NAME
o Database Block Size – DB_BLOCK_SIZE
o Control Files to be used by the database – CONTROL_FILES
o Maximum Size of SGA – SGA_MAX_SIZE
o Number of instances that will access the database – CLUSTER_DATABASE_INSTANCES
Parameters like those shown above, are entered in the parameter file also called the initialization parameter file or the PFILE. Starting with Oracle 9i,
Oracle provides another kind of parameter file called the SPFILE
• The PFILE or INIT.ORA file :
The pfile is a static text file located in the $ORACLE_HOME/dbs directory on Unix or the %ORACLE_HOME%/database directory on a windows machine.
This file can be edited using a text editor. The format is normally init.ora.
• The SPFILE or the Server Parameter File :
The spfile is a dynamic binary file and can be updated directly using the ALTER SYSTEM SET statement. This file is also located in the $ORACLE_HOME/dbs directory on Unix or the %ORACLE_HOME%/database directory on a windows machine. With this method, you do not need a copy of the
PFILE to start the instance. It is not advised to edit a SPFILE as it would become corrupt.
• Advantages of using the SPFILE
o Unlike the PFILE, the SPFILE can be automatically backedup using the RMAN utility.
o Dynamic parameters can be directly changed to the SPFILE, unlike the PFILE which has to be edited physically to make changes persistent,
• How to start a database using pfile or spfile?
Oracle searches for the parameter file in the following order.
Search for the SPFILE (spfile.ora in the $ORACLE_HOME/dbs directory. If found, start using this file.
If SPFILE is not found, then search for the init in the $ORACLE_HOME/dbs directory. If found, start using this file.
The IFILE parameter inside the init.ora file can point to another configuration file anywhere on the filesystem.
To start an Oracle instance (say ORCL) using the default initORCL.ora file in the $ORACLE_HOME/dbs directory
On Unix prompt
$ export ORACLE_SID=ORCL
On Sql Prompt
SQL> startup;
To start an instance using a pfile other than from the dbs location ,
SQL> startup pfile=’/tmp/initORCL.ora’;
It is also possible to use an IFILE parameter within the INIT.ora file, to point to another configuration file.
Even if you give an ALTER SYSTEM SET command from SQL prompt, the values will not be saved to the init.ora file.
p.s. There is no PFILE=” alternative for SPFILE.
• Find out which type of parameter file is being used presently
Use the following at SQL Prompt
SQL> SHOW PARAMETER SPFILE
SQL> SHOW PARAMETER PFILE
SQL> select name, value from V$parameter
where upper(name) in (‘SPFILE’,'PFILE’)
• How do I view what parameters are
o Use the SHOW PARAMETER or just SHOW PARAMETER (to see all parameters) from the SQL prompt
o Query the V$PARAMETER view
o Query the V$SPPARAMETER view
• Making changes to the SPFILE
Changes to SPFILE can be made only by recreating it from a PFILE or by the ALTER SYSTEM SET or ALTER SESSION SET command. Depending on the type of attribute (dynamic or static, determined from ISSES_MODIFIABLE OR ISSYS_MODIFIABLE fields of V$PARAMETER view), the ALTER SYSTEM SET COMMAND can change the value to the following scopes
o MEMORY – Value can be changed only for the instance, either session level or system level. This is the default for PFILE
o SPFILE – This option will update the SPFILE, and the changes will take effect on next startup
o BOTH – Thos option will both change the value for the instance as well as write changes to the SPFILE for next startup
• Creating a SPFILE and PFILE
The PFILE can be manually created via a text editor. Once this file is ready, you can create the SPFILE using the following command
SQL> CREATE SPFILE FROM PFILE=’location of pfile’;
If you want to create a PFILE from the SPFILE, do the following
SQL> CREATE PFILE FROM SPFILE;
• Backup/Restore the SPFILE
To backup the SPFILE automatically with every backup, do the following from the RMAN prompt
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
To restore the SPFILE
RMAN> RESTORE SPFILE FROM AUTOBACKUP TO ‘/tmp/spfileORCL.ora’; — Must use the TO option if the instance is started using the SPFILE
No comments:
Post a Comment