5 SPFILE Commands - Which Command you Miss ?

SPFILE in Oracle Play a role for parameter file which is used at time of instance start and defines databaes behavior on the basis of values given to the parameters. In lower version of database Database used to start with Text version of file called as PFILE. Now, PFILE is still there but database used to use SPFILE a binary version of file.

In traditional PFILE, To make any change in to parameter Database Administrator has to restart the database but now DBA can change database parameter using SPFILE without restarting Instance.

When DBA creates a new database, SPFILE is created automatically at it's default location ORACLE_HOME/dbs in UNIX and ORACLE_HOME\database in windows environment. One most important fact about SPFILE, this file is not editable using OS commands. DBA can only change this file values using database commands "alter system". 

5 Spfile Commands for DBA's

 In this post I am listing 5 spfile commands and believe readers know about these commands if no make a note of the command for future reference.

1. Creating SPFILE from PFILE or Vise-Verse: If Somehow, DBA lost database spfile then, He can recreate it from pfile exists at default location or by providing exact location of pfile.

To create spfile from pfile use below command:

SQL> create spfile from pfile='$ORACLE_HOME/dbs/initorcl.ora';

File Created.

Creating spfile doesn't means that, database is using SPFILE, To achieve this DBA has to restart the Database and then by default SPFILE will be used.

Create pfile from spfile:

DBA can also create pfile from spfile for backup or to see parameters in spfile.
SQL> create pfile from spfile;
File created.

SQL> !
[oracle@database OPatch]$ cd /etc/oracle/oracle/dbs/

[oracle@database dbs]$ ls init*
Pfile is created at default location ORACLE_HOME/dbs with name as init{instanceName}.ora. Here, pfile created is initorcl.ora.

If you have a RAC database with ASM, In that case SPFILE will be stored at shared ASM location. All Database instances will use same spfile, here you will find two types of parameters first same across all database instances and second instance specific parameters.

In the above output, DBA can see that pga_aggregate_target, sga_target and streams_pool_size are instance specific parameters, that's why they have instance name as prefix for them. other parameters like audit_trail, audit_sys_operations and cluster_database are database parametrs, so they are consistent across database instances.

The method of creating spfile and pfile in RAC with ASM is also same in as explained above.

2. Changing SPFILE Initialization Parameter Values: parameters specified in SPFILE can by changed using database commands "alter system". There are two types of Spfile parameters. Dynamic parameter and Static parameter.

Dynamic SPIFLE parameter can be changed and take effect on database instance immediately. like SGA_TARGET is a dynamic parameter.

Static SPFILE Parameter can be changed any time but to take effect, DBA has to restart the Database instance like SGA_MAX_SIZE is a static parameter.

When changing SPFILE parameters SCOPE is defined, which indicates, do we need this parameter change only into memory or keep it across database restarts. There are mainly three type of scopes.

SCOPE = SPFILE :- No change is made to the current running instance, The scope applied for both static and dynamic parameters. The Change applied will take effect after instance restart.

SCOPE = MEMORY :- The change is applied in memory only and will take effect immediately. This scope is only allowed for dynamic parameters. Changes done are not available across database restart.

SCOPE = BOTH  :- The change is applied in both the server parameter file and memory. This scope parameter Only applied for dynamic parameters.

Example of Dynamic Parameter with "SCOPE=BOTH"
SQL> show parameter sga_
NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
sga_max_size                         big integer                      584M
sga_target                           big integer                      400M

SQL> alter system set sga_target=500M scope=both;
System altered.

SQL> show parameter sga_target;
NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
sga_target                           big integer                      500M

You can notice that sga_target is a dynamic parameter, So changes has taken effect immediately and will persists across instance restarts.

Example of Static Parameter with "SCOPE=SPFILE"

Since sga_max_size is a static parameter, so DBA can only change it's value using scope=spfile which will take effect after instance restart.

SQL> alter system set sga_max_size=510M scope=both;
alter system set sga_max_size=510M scope=both
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

Here is the right command to change sga_max_size parameter value.
SQL> alter system set sga_max_size=512M scope=spfile;
System altered.

SQL> show parameter sga_max_size;           
NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
sga_max_size                         big integer                      584M

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area  535662592 bytes
Fixed Size                  1345376 bytes
Variable Size             331352224 bytes
Database Buffers          197132288 bytes
Redo Buffers                5832704 bytes
Database mounted.
Database opened.

SQL> show parameter sga_max_size;
NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
sga_max_size                         big integer                      512M

If you are working on RAC env then you have to specify Is the change for all database instance or for specific one only ?

Below is an Example of a Two node RAC env. generating archiving log files on Local System rather than Shared location.
SQL> show parameter log_archive_dest_state_1
NAME                     TYPE   VALUE
log_archive_dest_state_1 string enable

SQL> alter system set log_archive_dest_1='LOCATION=/RAC1' sid='RAC1';
SQL> alter system set log_archive_dest_1='LOCATION=/RAC2' sid='RAC2';
SQL> alter system archive log current;

To apply changes across all database instances instead on single instance DBA should use sid='*' as last parameter.

3. Display SPFILE parameter value: To check spfile parameter value DBA can use 'show parameter' command.

sql> show paramter {parameter_name};

SQL> show parameter sga_max_size;

4. Check SPFILE or PFILE is Currently Used: As we know, Database can be started using either spfile or pfile. So DBA has to cross check which file database is using for as of know:

SQL> select name,value from V$parameter where name like 'spfile' order by name;

It gives path of spfile file which is getting used by database for starting up. If no value is given means database is using Pfile. See How to find database is using Spfile or Pfile ? 

5. Recreating SPFILE without Backup: There could be a situation when DBA doesn't have spfile backup, pfile or any other way to restore Spfile. In that case generating spfile from alert logs can play a trick for you.

Were you missing any command from them or I am missing any command. Please share. !!

No comments:

Post a Comment