use of parameter files
TRANSCRIPT
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 file2. 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.
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.oraSQL>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=1800SQL>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.
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=MEMORYSQL>SHOW PARAMETER UNDO_RETENTION;
NAME TYPE VALUEundo_retention integer 1800
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP;
SQL>SHOW PARAMETER UNDO_RETENTION;NAME TYPE VALUEundo_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 VALUEAUDIT_SYS_OPERATIONS BOOLEAN FALSE
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 valueSQL>ALTER SYSTEM SET AUDIT_SYS_OPERATIONS=TRUE SCOPE=BOTHSQL>SYSTEM ALTERED.SQL>SHOW PARAMETER AUDIT_SYS_OPERATIONS;NAME TYPE VALUEAUDIT_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=BOTHNAME TYPE VALUEundo_retention integer 1800SQL>SHUTDOWN IMMEDIATE;SQL>STARTUP;
SQL>SHOW PARAMETER UNDO_RETENTION;
NAME TYPE VALUE
undo_retention integer 1800