SPFILE and INIT.ORA

Hi Peers,


This is the article which will explain you about init parameter reposistory feature.
However this is basic level but helping us to understand stronger.


SPFILE and INIT.ORA

Oracle9i allows more and more parameters to be changed on-the-fly without having torestart the database. Consequently, the DBA has to remember, more and more often, to change the parameter file accordingly before the next restart of the database. Oracle9i enables dynamically altered parameters to be simultaneously written to the parameter file using server parameter files (SPFILE) so that changes are consistent with ALTER SYSTEM.


$ Up to version 8i, Oracle traditionally stored initialization parameters in a text file INIT.ORA (PFILE).
$ With Oracle9i, server parameter files (SPFILE) can also be used.
$ An SPFILE can be regarded as a repository for initialization parameters which is located on the database server.
$ SPFILEs are small binary files that cannot be edited.
$ Editing SPFILEs corrupts the file and either the instance fails to start or an active instance may crash.

eg ( Content ):


*.db_block_size=8192
*.db_domain='ttc.trivadis.com'
*.db_file_multiblock_read_count=16
*.db_files=1022
*.db_name='TVD901A'
...

*. means:


all instances of this database system. This is also valid syntax in conventional INIT.ORA files.



*. This is particularly interesting for Real Application Cluster (RAC) – see below for more information.


At database startup, if no PFILE is specified at the OS-dependent default location

($ORACLE_HOME/dbs under UNIX, $ORACLE_HOME\database under NT),


the startup command searches for:

1. spfile${ORACLE_SID}.ora

2. spfile.ora

3. init${ORACLE_SID}.ora



Of course, the option of explicitly specifying a PFILE is still available.
 
SQL> connect sys/manager as sysdba


Connected to an idle instance.

SQL> startup pfile=/tmp/initTVD901A.ora

ORACLE instance started.

Total System Global Area 172967504 bytes

...

SQL>

However, a SPFILE cannot be specified for STARTUP with PFILE :


SQL> startup pfile='tmp/spfileTVD901A.ora'

LRM-00101: unknown parameter name 's044'

ORA-01078: failure in processing system parameters

To facilitate this, the best option is to create a conventional INIT.ORA with default name:



init${ORACLE_SID}.ora


This file only contains the path of the SPFILE:

SPFILE = c:\oracle\admin\db1\pfile\spfileDB1.ora

Create SPFILE

A SPFILE is initially created from a conventional text initialization parameters file (PFILE,
INIT.ORA). This can be carried out without a started instance. SYSDBA or SYSOPER
privileges are required to create the SPFILE.


If SPFILE is available at the default location, STARTUP uses this SPFILE if no PFILE is specified.

Creating an SPFILE from a PFILE at the default location:

SQL> CREATE SPFILE FROM PFILE='/u00/app/oracle/admin/TVD901A/pfile/initTVD901A.ora';

File created.

SQL>

Creating an SPFILE from a PFILE at the non-default location:


SQL> CREATE SPFILE='/u00/app/oracle/admin/TVD901A/pfile/spfileTVD901A.ora'

FROM PFILE='/u00/app/oracle/admin/TVD901A/pfile/initTVD901A.ora';

File created.

SQL>
 
The best solution is to create a (conventional) INIT.ORA with default name in the default location,
which contains only the path to the SPFILE.
Working with links, if necessary, on UNIX:

sqlplus "/ as sysdba"

startup create spfile='/u00/app/oracle/admin/TDBT901A/pfile/spfileTVD901A.ora' from
pfile='/u00/app/oracle/admin/SID/pfile/initTVD901A.ora';

shutdown immediate

exit

cd $ORACLE_HOME/dbs

ln -s /u00/app/oracle/admin/TDBT901A/pfile/spfileTVD901A.ora .

sqlplus "/ as sysdba"

startup

Backing up an SPFILE




The CREATE PFILE command can be used to back up an SPFILE:



SQL> CREATE PFILE='/u00/app/oracle/admin/TVD901A/pfile/bck_init.ora' FROM SPFILE;



File created.



SQL>


This creates a directly usable INIT.ORA file:


*.background_dump_dest='/u00/app/oracle/admin/TVD901A/bdump'
*.compatible='9.0.1'
*.control_file_record_keep_time=90
*.core_dump_dest='/u00/app/oracle/admin/TVD901A/cdump'


Continue.........