A parameter is key value pair. One of the parameter example is like db_block_size whose value is 8192 (8 kb)

 

so db_block_size is key

 

and 8192 is value

 

and another name example is db_name=orcl

 

 

A group of similar parameters are put together in file and that file is called Parameter file.

 

NOW LETS DISCUSS ABOUT PARAMETER FILE. The first parameter file is something related to network and that is

 

1. Tnsnames.ora=This is a network file so what tnsnames.ora suggest is that this file map the net

services name to connect discriptor. In simple word what is the alias name where we can connect to a database.

 

 

2. LISTENER.ORA=is the file which tell on which PORT LISTENER is going to run. By default LISTENER PORT IS 1521 but there

is feature where you can change the default parameter to 1523.

 

 

3. DATABASE PARAMETER FILE : ----> This is historically known as INIT.ORA. So init.ora / database parameter file

consist of parameter that required to start the database. In this lesson we going to concentrate mostly on this.

 

[oracle@storage dbs]$  cd /u01/app/oracle/product/11.2.0/db_1/dbs

[oracle@storage dbs]$ ls

hc_DBUA0.dat  hc_orcl.dat  init.ora  initorcl.ora  lkORCL  orapworcl  snapcf_orcl.f

[oracle@storage dbs]$

 

Here my init.ora name is initorcl.ora so what is ORCL. ORCL is oracle sid i.e. site identifier

 

WHAT IS SITE IDENTIFIER ? ORACLE_HOME IS WHERE U INSTALL ORACLE.

Site Identifier is a value. SITEIDENTIFIER + ORACLE HOME = UNIQUE KEY

 

SID + ORACLE HOME TOGETHER CREATES A UNIQUE KEY AND that UNIQUE KEY is attached to the SGA (SYSTEM GLOBAL AREA).

For whatever reason if you give wrong SID name then it give u an error saying ORACLE IS NOT AVAILABLE and the reason

for this is that whatever unique key you are generating that key is not able to attached to SGA. To startup database

you need INIT with SID i.e.INITORCL.ORA . In my case my SID name is ORCL so therefor i am seeing initorcl.ora

 

 

SO IF YOU LOOKING AT INITORCL.ORA YOU are going to seem some value like KEY VALUE PAIR.

 

CONTROL_FILES=''

DB_NAME=ORCL;

SESSION=20;

 

so essentially what this parameter suggest how the instance is configure ?

 

 

If you look at initorcl.ora file the you will see at least 10 to 15 parameter which are default parameter

but in real in order to start DATABASE you actually required three parameters.

 

1. CONTROL FILE

2. DB_BLOCK_SIZE

3. DATABASE NAME

 

ISO if you have above three parameters then all other parameters are either default or can be computed

based on above three parameters.

 

NOTE : UNDER YOU LOCAL SERVER YOU WILL SEE ONLY 10 TO 15 PARAMETER BUT IN YOUR PRODUCTION YOU MAY SEE LOTS OF VALUES WILL BE THERE.

 

DESC V$PARAMETER;

SELECT COUNT(*) FROM V$PARAMETER;

 

 

HOW TO CHECK PARAMETER ? LETS SAY YOU WANT TO CHECK SQL  TRACE PARAMETER.

 

SHOW PARAMETER SQL_TRACE; OUTPUT SHOWS SQL TRACE PARAMETER IS SET TO FALSE

 

SHOW PARAMETER SQL_TRACE; second option

 

NOW HOW TO MODIFY THE PARAMETER. There are two where you can modify parameter

 

first one going inside init.orra

second is use alter command

 

alter system set sql_trace= true scope=memory;

 

AGAIN CHECK THE VALUE OF SQL TRACE it will show true

 

SHOW PARAMETER SQL_TRACE;

 

LET'S UNDERSTAND THIS CLAUSE SCOPE=MEMORY

 

ONCE DB STARTED, THE DB STARTED WITH THE DEFAULT PARAMETER WHICH WAS SQL TRACE = FALSE

 

THEN WHEN I SAY SCOPE=MEMORY, THIS MEANS SQL TRACE VALUE WILL BE TRUE AS LONG AS REMAINS ON. AFTER SOMETIME

IF YOU SHUTDOWN AND MAKE DATABASE ON AND CHECK SQL TRACE PARAMETER THEN THE STATUS WILL SHOW BACK TO FALSE AGAIN.

 

 

 

DB STARTED ---FALSE

|

|

|

|----------TRUE

|

|

|

DB SHUTDOWN AND RESTART ----FALSE

 

 

THEN WHEN I SAY SCOPE=SPFILE, THIS MEANS SQL TRACE VALUE WILL BE FALSE AS LONG AS REMAINS ON. AFTER SOMETIME

IF YOU SHUTDOWN AND MAKE DATABASE ON AND CHECK SQL TRACE PARAMETER THEN THE STATUS WILL SHOW TRUE. SO HERE IN THIS CASE IN ORDER TO GET TRUE STATUS

YOU NEED TO SHUTDOWN AND RESTART DATABASE

 

 

 

DB STARTED ---FALSE

|

|

|

|----------FALSE

|

|

|

DB SHUTDOWN AND START ---TRUE

 

 

 

 

THEN WHEN I SAY SCOPE=BOTH, THIS MEANS SQL TRACE VALUE WILL BE TRUE RIGHT AWAY AS LONG AS REMAINS ON. AFTER SOMETIME

IF YOU SHUTDOWN AND MAKE DATABASE ON AND CHECK SQL TRACE PARAMETER THEN THE STATUS WILL SHOW TRUE. SO HERE IN THIS CASE IN ORDER TO GET TRUE STATUS

YOU DO NOT NEED TO SHUTDOWN OR RESTART THE DATABASE

 

 

DB STARTED ---TRUE

|

|

|

|----------TRUE

|

|

|

DB SHUTDOWN AND START ---TRUE

 

 

 

ANOTHER THING YOU CAN DO IS CREATE PFILE FROM SPFILE BY GIVING A COMMAND:

 

create PFILE from SPFILE;

 

 

ANOTHER THING IS WHAT IF DATABASE FILE GET CORRUPTED ?

 

As i have already told you that SPFILE is an binary file.

 

 

1. By using UNIX command STRINGS.

2. Alert log : WHEN DB get started all this NON DEFAULT PARAMETER VALUE are put in alert.log . You can take those value and create pfile say 2.ora