Oracle parameter file Learning

Oracle parameter files are critical to Oracle. No parameter file. Oracle cannot be started.
Parameter files are divided
Spfile: The parameter file added after oracle9iR1. It is a binary file and cannot be edited directly.
Pfile file, text file, can be edited directly,
These two files can be converted to each other.
I. Use the parameter file to start oracle
Oracle and the default read order is:
1.1: read first: spfile starts oracle. The read file is:
$ ORACLE_HOME/dbs/spfile $ ORACLE_SID.ora
1.2: If the spfile $ ORACLE_SID.ora does not exist, read another spfile:
$ ORACLE_HOME/dbs/spfile. ora
1.3: when neither of the current two files exists. Then start reading the pfile. The pfile file is:
$ ORACLE_HOME/dbs/init $ ORACLE_SID.ora

In some States, you may need to specify the pfile startup mode. You can manually specify pfile to start the oracle database. The method is as follows:
1.4: there is a valid pfile file, but it does not comply with the oracle default read command and path, then use
Plsql> startup pfile = '/u1/app/oracle/product/10.2.0/db_1/dbs/initest2.ora'

1.5: the content of a specified pfile used to start oracle can also be as follows:
The content of initest2.ora is:
IFILE = '/u1/app/oracle/product/10.2.0/db_1/pfile/init. ora'
The IFILE command is similar to the # include command in C. It will include the content of the specified file in the current file. The preceding command contains the init. ora file in a non-default location.
When you start a database using plsql> startup pfile = '/u1/app/oracle/product/10.2.0/db_1/dbs/initest2.ora. Indicates the actually used
Pfile: '/u1/app/oracle/product/10.2.0/db_1/pfile/init. ora'

1.6: The pfile file can also be redirected to the spfile file. The content of initest2.ora is:
# Pfile link to SPFILE
SPFILE = '/u1/app/oracle/product/10.2.0/db_1/spfile123.ora'
Log_archive_start = false

Use plsql> startup pfile = '/u1/app/oracle/product/10.2.0/db_1/dbs/initest2.ora' to start the database. Spfile123.ora is used to start the database, and
The parameter log_archive_start = false will overwrite the same specified parameter value in spfile123.ora.

This condition is mainly applicable to one situation, for example, due to misoperations. As a result, sga_target = 2147483648 in Spfile is modified, exceeding the system memory limit. This will cause startup failure.
At this time. You can use pfile and specify the spfile in pfile. And the additional parameter sga_target = 524288000.

2. Modify the parameter file:
2. A: You can directly modify the pfile in A text editor.
2. B: Modify the spfile file using commands in oracle

Alter system set parameter = value <comment = 'text'> <deferred> <scope = memory | spfile | both> <sid = 'sid | * '>
Note: <> the inner part is optional.
2.1 For <comment = 'text'>, it is used when DBA updates the parameter file. Add comments. Easy to track the modification history of parameters
For example, alter system set pga_aggregate_target = 775 M comment = 'Update by harvey test 2 ';
Then, you can query the UPDATE_COMMENT field in v $ parameter. Notes for the pga_aggregate_target parameter record will be updated
In addition, the experiment shows that. Multiple modifications to the same parameter. Only the last modified comment is retained. If the comment information is not specified at the last time,
UPDATE_COMMENT is empty.

2.2: deferred indicates whether the system modification takes effect only for future sessions (invalid for currently established sessions, including executing this repair)
Modified sessions ). By default, the alter system command takes effect immediately, but some parameters cannot be modified immediately.
Modify these parameters for a new session. You can use the following query to see which parameters must use deferred:
Select name from v $ parameter where ISSYS_MODIFIABLE = 'referred ';

2.3: <scope = memory | spfile | both>. memory indicates that it takes effect only for the currently running instance and does not modify the spfile. Spfile indicates that only spfile is modified and the current instance is not changed.
Both indicates modifying the spfile and taking effect for the currently running instance. By default, if no scope is specified, the value is both.
Note: When pfile is specified to start the database. The default value is memory. If both and spfile are specified, an error is returned:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup

2.4: <sid = 'sid | * '> it is mainly used in the cluster environment. The default value is sid = '*'. In this way, the unique
Specify parameter settings in one location. Unless you use Oracle RAC, you do not need to specify sid =, but the following 2.5 exceptions

2.5: cancel the value setting in SPFILE-that is, we do not want SPFILE to have this parameter setting at all. To delete it, run the following command:
Alter system reset parameter <scope = memory | spfile | both> sid = 'sid | *'
Note: The SID = part is no longer optional. You must specify the sid to modify it.

Iii. parameter file conversion:
3.1: Convert pfile to spfile,
3.1.1: When the default pfile File $ ORACLE_HOME/dbs/init $ ORACLE_SID.ora is used to start oracle. Command;
Create spfile from pfile; the default generated spfile is: $ ORACLE_HOME/dbs/spfile $ ORACLE_SID.ora
3.1.2: When a non-default pfile is used for startup. The complete path name of pfile must be specified:
Create spfile from pfile = '/u1/app/oracle/product/10.2.0/db_1/dbs/initest2.ora'

Note: You can also specify the path and name of the generated spfile: create spfile = '/u1/app/oracle/product/10.2.0/db_1/dbs/abc. ora' from pfile;

3.2: Convert spfile to pfile.
3.2.1 only when oracle is started with spfile. Use create pfile from spfile. The default pfile is pfile.
3.2.2: You can use the strings $ ORACLE_HOME/dbs/spfile $ ORACLE_SID.ora command to directly extract the text content in spfile.
3.3: whether pfile or spfile is used to start oracle, you can use
Create pfile = 'xyz' from spfile = 'abc' or create spfile = 'abc' from pfile = 'xyz' to convert pfile and spfile.

4. Processing When spfile is damaged:
4.1: You can use the strings $ ORACLE_HOME/dbs/spfile $ ORACLE_SID.ora command to directly extract the text content in spfile to generate pfile.
4.2: If no pfile exists, you can read the content of the alter log. Generate pfile
4.3: After pfile is started. Generate a spfile.

Link: http://space.itpub.net/180324/viewspace-661625