SATISH
d.satish2794@gmail.com
Use of the parameter files of Oracle Database
This tutorial is going to give brief explanation about use of the parameter files (SP file & Pfile).
Go to Oracle Home location of your database and open which is called directory of the
file DATABASE($ORACLE_HOME/database).
In the database directory where the parameter file of Oracle database can be found(SP
file).
Oracle has two types of parameter files ---------
1. SP File--server parameter file
2. P File- parameter File
The database created by using database configuration assistance. The database
configuration assistance (DBCA) tool only gives sp file.
The SP file for our database called orcl (database name) is called spfileorcl.ora.
The spfileorcl.ora is a binary file and which can’t be edited and opened by manually.
Now create P file from SP File.
SATISH
d.satish2794@gmail.com
P File is a test file that can be opened and modified by manually.
Type a command in sql plus
SQL>SHOW PARAMETER UNDO_RETENTION;
NAME TYPE VALUE
------------------------------------ ----------- ------
undo_retention integer 900
The value of the seen by making the use of the spfile.
Oracle always preferred spfile by default.
Shutdown the database so that we can start the database by using pfile.
Type the command
SQL>STARTUP PFILE=$ORACLE_HOME/databse/initorcl.ora
SQL>SHOW PARAMETER UNDO_RETENTION;
NAME TYPE VALUE
------------------------------------ ----------- ------
undo_retention integer 900
This value coming from pfile and its current value is 900.
So that we can change the pfile of the parameter UNDO_RENTENTION
SQL>ALTER SYSTEM SET UNDO_RETENTION=1800
SQL>SHOW PARAMETER UNDO_RETENTION;
NAME TYPE VALUE
------------------------------------ ----------- ------
undo_retention integer 1800
However am going to shutdown the database
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP PFILE=LOCATION OF THE PFILE;
SQL>ALTER SYSTEM SET UNDO_RETENTION=1800
SQL>SHOW PARAMETER UNDO_RETENTION;
NAME TYPE VALUE
------------------------------------ ----------- ------
undo_retention integer 900
The value of parameter you have seen not modified the reason is in the pfile only
dynamic change affected only the current instance.
SATISH
d.satish2794@gmail.com
If we want to change parameter we have to do manually.
Go to the $ORACLE_HOME/database/initorcl.ora and opened do it manually.
The only the problem with pfile when you want changes the parameter file we should
shutdown the Database and startup the database which can changes affected.
However again shutdown the database and startup without the pfile.
SQL>STARTUP;
SQL>SHOW PARAMETER UNDO_RETENTION;
SQL>SHOW PARAMETER UNDO_RETENTION;
NAME TYPE VALUE
------------------------------------ ----------- ------
undo_retention integer 900
Notice that the value of the parameter is 900. The reason is the change we have made
before in the pfile. So, pfile and splfile don’t automatically synchronize.
It is possible to changes of the parameter file by using scope option
There are three different types of values we have
1.MEMORY
2.SCOPE
3.BOTH
SQL>ALTER SYSTEM SET UNDO_RETENTION=1800 SCOPE=MEMORY
SQL>SHOW PARAMETER UNDO_RETENTION;
NAME TYPE VALUE
undo_retention integer 1800
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP;
SQL>SHOW PARAMETER UNDO_RETENTION;
NAME TYPE VALUE
undo_retention integer 900
The value for UNDO_RETENTION is should be back to 900. The reason is when we use
scope=memory, only the change does in current instance running.
There is some parameter value which can’ be altered.
SQL>SHOW PARAMTER AUDIT_SYS_OPERATIONS;
NAME TYPE VALUE
AUDIT_SYS_OPERATIONS BOOLEAN FALSE
SATISH
d.satish2794@gmail.com
This type of parameters values can’t be altered.
SQL>ALTER SYSTEM SET AUDIT_SYS_OPERATIONS=TRUE
It ll give the error ORA-029095
So we can use scope option ‘SPFILE’ for changing the value
SQL>ALTER SYSTEM SET AUDIT_SYS_OPERATIONS=TRUE SCOPE=BOTH
SQL>SYSTEM ALTERED.
SQL>SHOW PARAMETER AUDIT_SYS_OPERATIONS;
NAME TYPE VALUE
AUDIT_SYS_OPERATIONS BOOLEAN FALSE
After alter parameter using scope=both if we see the parameter it ll show old parameter
value false. The reason is oracle has return the change spfile which can be read and
write if we startup the database.
We have to shutdown database and startup the database for modification we have
done before.
If we want change the parameters permanently we can use scope option SCOPE=BOTH
even though we are shutdown the database and startup, the changed parameter will
not be modified.
SQL>ALTER SYSTEM SET UNDO_RETENTION=1800 SCOPE=BOTH
NAME TYPE VALUE
undo_retention integer 1800
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP;
SQL>SHOW PARAMETER UNDO_RETENTION;
NAME TYPE VALUE
undo_retention integer 1800
