Profileer Vs SPFile in Oracle

 When associate degree Oracle Instance is started, the characteristics of the Instance ar established by parameters such at intervals the data format parameter file. These data format parameters ar either hold on in an exceedingly PFILE or SPFILE. SPFILEs ar obtainable in Oracle 9i and higher than. All previous releases of Oracle ar victimisation PFILEs.

SPFILEs offer the subsequent benefits over PFILEs:

An SPFILE is backed-up with RMAN (RMAN cannot backup PFILEs)

Reduce human errors. The SPFILE is maintained by the server. Parameters ar checked before changes ar accepted.

Eliminate configuration issues (no have to be compelled to have a neighborhood PFILE if you wish to begin Oracle from a foreign machine)

Easy to seek out - hold on in an exceedingly central location

What is the distinction between a PFILE and SPFILE:

A PFILE may be a static, client-side document that has got to be updated with a regular text editor like \"notepad\" or \"vi\". This file usually reside on the server, however, you wish a neighborhood copy if you wish to begin Oracle from a foreign machine. DBA\'s normally ask this file because the INIT.ORA file.

An SPFILE (Server Parameter File), on the opposite hand, may be a persistent server-side computer file that may solely be changed with the \"ALTER SYSTEM SET\" command. this suggests you not want a neighborhood copy of the pfile to begin the info from a foreign machine. piece of writing associate degree SPFILE can corrupt it, and you\'ll not be able to begin your info any longer.

How can i do know if my info is employing a PFILE or SPFILE:

Execute the subsequent question to envision if your info was started with a PFILE or SPFILE:

SQL> choose DECODE(value, NULL, \'PFILE\', \'SPFILE\') \"Init File Type\" 

       FROM sys.v_$parameter wherever name = \'spfile\';

You can additionally use the V$SPPARAMETER read to ascertain if you\'re employing a PFILE or not: if the \"value\" column is NULL for all parameters, you\'re employing a PFILE.

Viewing Parameters Settings:

One will read parameter values victimisation one in every of the subsequent strategies (regardless if they were set via PFILE or SPFILE):

The \"SHOW PARAMETERS\" command from SQL*Plus (i.e.: SHOW PARAMETERS timed_statistics)

V$PARAMETER read - show the presently in impact parameter values

V$PARAMETER2 read - show the presently in impact parameter values, however \"List Values\" ar shown in multiple rows

V$SPPARAMETER read - show the present contents of the server parameter file.

Starting a info with a PFILE or SPFILE:

Oracle searches for an appropriate data format parameter go in the subsequent order:

Try to use the spfile$.ora go in $ORACLE_HOME/dbs (Unix) or ORACLE_HOME/database (Windows)

Try to use the spfile.ora go in $ORACLE_HOME/dbs (Unix) or ORACLE_HOME/database (Windows)

Try to use the init$.ora go in $ORACLE_HOME/dbs (Unix) or ORACLE_HOME/database (Windows)

One will override the default location by specifying the PFILE parameter at info startup:

SQL> STARTUP PFILE=\'/oradata/spfileORCL.ora\'

Note that there\'s not constant \"STARTUP SPFILE=\" command. One will solely use the higher than choice with SPFILE\'s if the PFILE you purpose to (in the instance above), contains one \'SPFILE=\' parameter inform to the SPFILE that ought to be used. Example:

SPFILE=/path/to/spfile

Changing SPFILE parameter values:

While a PFILE is altered with any text editor, the SPFILE may be a computer file. The \"ALTER SYSTEM SET\" associate degreed \"ALTER SYSTEM RESET\" commands is accustomed amendment parameter values in an SPFILE. look into these examples:

SQL> ALTER SYSTEM SET open_cursors=300 SCOPE=SPFILE;

SQL> ALTER SYSTEM SET timed_statistics=TRUE

COMMENT=\'Changed by Frank on one Gregorian calendar month 2003\'

SCOPE=BOTH

  SID=\'*\';

The SCOPE parameter is set to SPFILE, MEMORY or BOTH:

- MEMORY: Set for the present instance solely. this is often the default behaviour if a PFILE was used at STARTUP.

- SPFILE: update the SPFILE, the parameter can go with next info startup

- BOTH: have an effect on the present instance and persist to the SPFILE. this is often the default behaviour if associate degree SPFILE was used at STARTUP.

The COMMENT parameter (optional) specifies a user remark.

The SID parameter (optional; solely used with RAC) indicates the instance that the parameter applies (Default is *: all Instances).

Use the subsequent syntax to line parameters that take multiple (a list of) values:

SQL> ALTER SYSTEM SET utl_file_dir=\'/tmp/\',\'/oradata\',\'/home/\' SCOPE=SPFILE;

Use this syntax to line unsupported data format parameters (obviously only if Oracle Support instructs you to line it):

SQL> ALTER SYSTEM SET \"_allow_read_only_corruption\"=TRUE SCOPE=SPFILE;

Execute one in every of the subsequent command to get rid of a parameter from the SPFILE:

SQL> ALTER SYSTEM RESET timed_statistics SCOPE=SPFILE SID=‘*’;

SQL> ALTER SYSTEM SET timed_statistics = \'\' SCOPE=SPFILE;

Converting between PFILES and SPFILES:

One will simply migrate from a PFILE to SPFILE or the other way around. Execute the subsequent commands from a user with SYSDBA or SYSOPER privileges:

SQL> produce PFILE FROM SPFILE; 

SQL> produce SPFILE FROM PFILE;

One can even specify a non-default location for either (or both) the PFILE and SPFILE parameters. look into this example:

SQL> produce SPFILE=\'/oradata/spfileORCL.ora\' from PFILE=\'/oradata/initORCL.ora\';

Here is another procedure for ever-changing SPFILE parameter values victimisation the higher than method:

Export the SPFILE with: produce PFILE=‘pfilename’ FROM SPFILE = ‘spfilename’;

Edit the ensuing PFILE with a text editor

Shutdown and startup the info with the PFILE option: STARTUP PFILE=filename

Recreate the SPFILE with: produce SPFILE=‘spfilename’ FROM PFILE=‘pfilename’;

On succeeding startup, use STARTUP while not the PFILE parameter and therefore the new SPFILE are going to be used.

Parameter File Backups:

RMAN (Oracle\'s Recovery Manager) can backup the SPFILE with the info management file if setting \"CONFIGURE CONTROLFILE AUTOBACKUP\" is ON (the default is OFF). PFILEs can\'t be backed-up with RMAN. look into this example:

RMAN> put together CONTROLFILE AUTOBACKUP ON;

Use the subsequent RMAN command to revive associate degree SPFILE:

RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;