OraclePortal.org Everything you need about Oracle

You can configure your instance by setting parameters. You can see the parameters that your instance can process by querying v$parameter dynamic performance view.

sql> desc v$parameter ;

Name                                              Null?    Type
 ----------------------------------------- -------- -------------
 NUM                                                          NUMBER
 NAME                                                         VARCHAR2(80)
 TYPE                                                         NUMBER
 VALUE                                                        VARCHAR2(512)
 DISPLAY_VALUE                                              VARCHAR2(512)
 ISDEFAULT                                                 VARCHAR2(9)
 ISSES_MODIFIABLE                                      VARCHAR2(5)
 ISSYS_MODIFIABLE                                      VARCHAR2(9)
 ISINSTANCE_MODIFIABLE                              VARCHAR2(5)
 ISMODIFIED                                                VARCHAR2(10)
 ISADJUSTED                                                VARCHAR2(5)
 ISDEPRECATED                                             VARCHAR2(5)
 DESCRIPTION                                              VARCHAR2(255)
 UPDATE_COMMENT                                       VARCHAR2(255)
 HASH                                                          NUMBER

You don't have to set parameters manually everytime you start your instance, you can keep the values in a file called "parameter file" and your instance reads the parameters stored in that file. If the parameter file is text-based it is called "parameter file" (pfile) , if it is binary it is called "server parameter file" (spfile).

On Linux:

As a rule spfile resides under $ORACLE_HOME/dbs/ directory.

When you start an instance Oracle will try to locate spfile in following order:

1) $ORACLE_HOME/dbs/spfile<SID>.ora => This file should be binary. SID is $ORACLE_SID environment value. If Oracle does not find the file then it looks for it in second location.

2) $ORACLE_HOME/dbs/spfile.ora => This file should be binary. SID is $ORACLE_SID environment value. If Oracle does not find the file then it looks for it in third location.

3) $ORACLE_HOME/dbs/init<SID>.ora => SID is $ORACLE_SID environment value. This file should be text based and it is not a server parameter file indeed. This file is read-only. Instance can not modify this, however it may read values in the file and start instance based on them. You may  modify this file using a text editor like vi and set values manually. It is not recommended to use init<SID>.ora files.

On Windows:

As a rule spfile resides under ORACLE_HOME\database\ directory.

When you start an instance Oracle will try to locate spfile in following order:

1) ORACLE_HOME\database\spfile<SID>.ora => This file should be binary. SID is your ORACLE_SID. If Oracle does not find the file then it looks for it in second location.

2) ORACLE_HOME\database\spfile.ora => This file should be binary. SID is your ORACLE_SID. If Oracle does not find the file then it looks for it in third location.

3) ORACLE_HOME\database\init<SID>.ora => SID is your ORACLE_SID. This file should be text based and it is not a server parameter file indeed. This file is read-only. Instance can not modify this, however it may read values in the file and start instance based on them. You may  modify this file using a text editor like vi and set values manually. It is not recommended to use init<SID>.ora files.

Using SQL*Plus

If you can connect to instance it is easier to find spfile. "spfile" parameter shows the path of parameter file.

sql> show parameter spfile ;

NAME                   TYPE              VALUE
-----------            --------          --------
spfile                  string             /shared_storage/spfileMYDB.ora

 

Did this article help? Our free information service offers more.

Print
Posted by at 07:07
Tags :
instance , oracle_sid , pfile , spfile

0 Comments:

Do you want to say something?
* Your email address won't be published.

Knowledge Base