ORACLE Database-parameter file

This section describes the database parameter files. You can use NetServicesAdministrator's sGuide to learn how to configure and create tnsnames. ora (search for servers on the network) and listener. ora (start network listener), sqlnet. ora (parses the connection string that appears in the connection), cman. ora and ldap. ora and other files. 1. query parameters

This section describes the database parameter files. You can use the Net Services Administrator's Guide to learn how to configure and create tnsnames. ora (search for servers on the network) and listener. ora (start network listener), sqlnet. ora (parses the connection string that appears in the connection), cman. ora and ldap. ora and other files. 1. query parameters

This section describes the database parameter files. You can use the Net Services Administrator's Guide to learn how to configure and create tnsnames. ora (search for servers on the network) and listener. ora (start network listener), sqlnet. ora (parses the connection string that appears in the connection), cman. ora and ldap. ora and other files.

1. Parameters

How to view parameters:

L V $ view: V $ PARAMETER

For more information about the V $ view or dictionary view, see the Oracle Database Reference manual.

L SHOW PARAMETER

L function: DBMS_UTILITY.GET_PARAMETER_VALUE

APIs are provided for all users to query the V $ parameter. Some parameters with memory parameters and excessively large values are restricted.

2. Initial File

The initial file (init. ora) is a historical parameter file of oracle. A new parameter file is introduced in 9i Release 1: Server parameter file.

If the default value is used, you can set this parameter in the parameter file. The main purpose of using this parameter file is to obtain the database name and the location of the control file.

L default naming conventions for initial files:

Init $ ORACLE_SID.ora (Unix/Linux)

Init % Oracle_SID %. ora (Windows)

L The default storage path is:

$ ORACLE_HOME/dbs (Unix/Linux)

$ ORACLE_HOME % \ DATABASE (Windows)

L specify the parameter file loaded by the instance:

You can use the IFILE command in the default parameter file. The current file will contain the content of the specified file:

IFILE =/u01/app/oracle/admin/orcl/pfile/init. ora.0520152052

You can reference parameter files in other paths in the parameter files in the default path.

You can also use the pfile = filename option to load the parameter file in the specified path, instead of the default path. For example: startup pfile =/u01/app/oracle/admin/orcl/pfile/init. ora.0520152052.

L modify parameters in the init. ora parameter file:

Init. ora is a text file that can be directly modified using any text editor. The ORACLE database does not provide commands to maintain parameters in the init. ora parameter file. If the instance uses the init. ora parameter file, even if the alter system command is used to set the parameter value, the init. ora file will not be modified and will be changed permanently as an instance. Only by manually modifying the init. ora parameter file used to start the database can the file be taken as the default value for database startup and permanently valid.

When the view starts a database, if you are remotely or locally, you must have database parameter files on the remote client or on the Database Host. Therefore, multiple parameter files may occur, and updates between them will not be synchronized effectively, which will lead to inconsistent startup database parameters. Server parameter files are introduced to standardize database parameter management.

3. Server parameter file

SPFILE:

1. SPFILE must be stored on the database server and cannot be used on the client. This ensures a single source of parameter information.

2. The alter system command is provided to modify parameters and write them into SPFILE. manual parameter maintenance is not required.

L default naming conventions for SPFILE:

Spfile $ ORACLE_SID.ora (Unix/Linux)

Spfile $ ORACLE_SID %. ora (Windows)

Default SPFIlE path:

$ ORACLE_HOME/dbs/(Unix/Linux)

% ORACLE_HOME/database/(Windows)

L view the spfile parameter:

No spfile file is created.

L create a spfile file and use spfile to start the database:

In this way, the spfile file will be created based on init. ora in the default path. We can also specify the source pfile and the generated spfile:

Create spfile = $ ORACLE_HOME/dbs/spfileepps. orafrom pfile $ ORACLE_HOME/dbs/initepps. ora

After the database instance is restarted, view the spfile parameters:

The database instance is started with spfile.

L set the parameter values in SPFILE:

SPFILE is a binary file and cannot be directly edited using a text editor. Therefore, the alter system command is provided for modification:

Alter system serparameter = value

<> Indicates the optional part, and the pipeline symbol (|) indicates an option in the candidate list.

Comment = 'text' is an optional comment related to the parameter settings. This comment will appear in the UPDATE_COMMENT field of V $ PARAMETER.

Deferred specifies whether the system modification takes effect only for future sessions (which is invalid for the current session, including sessions that execute the modification ). By default, the alter system command takes effect immediately, but some parameters cannot be modified immediately. You can only modify these parameters for newly created sessions. You can query the value of V $ PARAMETER to see the parameters that must use deferred:

SCOPE = MEMORY | SPFILE | BOTH indicates the "SCOPE" of this parameter ":

SCOPE = MEMORY is only modified in the instance, and will not be saved after the database is restarted.

SCOPE = SPFILE: only modify values in SPFILE. The modification does not take effect until the database is restarted and SPFILE is processed again. Some parameters can only be modified using this option.

SCOPE = BOTH indicates that parameters are modified in BOTH the memory and SPFILE.

Use the SPFILE parameter file. The default scope value is BOTH. Use the init. ora parameter file. The default value is MEMORY and the unique valid value.

Sid = 'sid | * 'is mainly used in the cluster environment. The default value is sid = '*'.

L cancel the value setting in SPFILE

Cancel the parameter value setting, use the default value specified by the SYSTEM, use the alter system command, but use the RESET clause:

Alter systemreset parameter Sid = 'sid | *'

This parameter will be deleted from the spfile parameter.

L create PFILE from SPFILE

This is to convert the binary file of spfile into a plain text file:

Createpfile = $ ORACLE_HOME/dbs/initepps. ora from spfile $ ORACLE_HOME/dbs/spfileepps. ora

Purpose:

1. Create a "one-time" parameter file to start the database for maintenance. Set some special parameter settings for maintenance. Pfile = Specifies the startup parameter file.

2. Maintain the modification history and record the modification in the comment. Before each parameter modification, create a text file in spfile as a backup.

L modify the corrupted SPFILE

If SPFILE is damaged or lost, use the following methods to restore it.

SPFILE is a binary file. You cannot view the parameter configuration directly, but you can view it using the strings command:

You can also get the relevant parameter settings from the warning log (the warning log will be explained in the warning log Section ):

Copy the parameter settings, create a PFILE file, and then create a SPFILE file to restore the file.