Parameter file management

                                             


When you startup the database the first file which is going to be read is Parameter file.
The parameter files are two type

1. Spfile

2. Pfile

Spfile :  It's a binary file,Only Oracle engine can modify it
Pfile   :  It's a  text file,only DBA can modify it.

Every time Oracle prefers to read spfile, If it doesn't exists it reads pfile.

How to identify the database is using spfile or pfile ?

If the VALUE of the below shows null means it is using pfile.
If it is showing value means it is using spfile.

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /dbhome_1/dbs/spfilemydb.ora

How to create spfile  ?

If the database is using pfile then connect to the SQLPLUS then execute the below

SQL>create spfile from pfile;

Restart the database then the DB uses the spfile.

How to change the parameter values  if we are using pfile ?

Let me take an example, if you want to change the value of the undo_tablespace  from undotbs to undotbs1

Below are the steps we have to follow.

undo_tablespace ---> undotbs  to undotbs1

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      undotbs
SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS
USERS
BIGTBS
DIFBLK
UNDOTBS1
TEMPX

8 rows selected.

SQL> alter system set undo_tablespace=UNDOTBS1;

System altered.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Modify the parameter value in the pfile. If you don't update the value here then in next bounce database brings up with old value (undotbs).

[oracle@ogg2 dbs]$ vi initmydb.ora


How to change the parameter values  if we are using spfile ?

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /dbhome_1/dbs/spfilemydb.ora
SQL> alter system set undo_tablespace=undotbs;

System altered.