Parameters File in Oracle

A Parameter is a key Value Pair
i.e db_block_size, db_name each contain a value in a files.

Types of parameter:

tns.ora parameter files: There are some parameters related to network. This file maps the net services to connect with network.

Listener.ora Parameter: is a file tells on which port to be connected for accessing DB.

Database parameters: its known as init.ora. It consists the parameters that required to start the database. Normally init.ora file reside with the name concating with the Site Identifier Name(SID). If the database SID is XE. then the init file name is initXE.ora. If we check this file we will get some parameters which suggest how the database will be configured.[Its like the blueprint of a home]. It contains some default parameters. But there parameters are the basic parameters must be in this file is Control File, DB_BLOCK_SIZE, DB_NAME. As this file is needed for the start of the database, we can use it as:

startup pfile=initXE.ora

We can change the name of the init.ora file for different types of requirements. Sometimes it makes confusion for multiple existence of the same file. Another problem is as this file is editable using text editor, for any unwanted mistake file may not be  updated with the correct value. Then for the corrupted file, DB wont start up properly. To get rid from such type of problem, starting from the Oracle 9i, Oracle introduces another file called SP file[System Parameter]. It may reside only one copy and another things its a binary file. And you are not able to change it manually. Only oracle can modify it. and for modifying it we next execute command for it. All the parameters are saved in a dynamic performance table named v$parameter. To get the value of a parameter, after connecting to DB through sqlplus, we can execute

desc v$parameter;

in this file we will get more than 250 parameters.

Select value from v$parameter where name='sql_trace'; || or || show parameter sql_trace


We can modify the value of parameter. And for the effecting it we can follow two steps:

alter system set sql_trace=true scope=memory;

using the above query, we are changing the parameter value until the running the database.

scope memory: until the instance will be shutdown
scope sp file: it will preserve the value in the SP file. It will be effected after restartigng the database
scope both: it will effect the change immediately in all places

In two ways we can get back after corruption of SP file. By using the unix command strings, we can get all those key value pairs those can be stored init.ora file. We can start database through it. And after that we can create SP file from it.

Whenever a database start all the non default parameter are put into a file named alert.log From that file we can check those. From that file we can create a (anyname.)ora file, and after starting using this file we can create SP fiel from the init file.