Introduction to Oracle Database startup parameter files and related SQL statements

OracleThe database must be started.Parameter file. This article summarizes the Oracle database startup parameter file and provides some SQL statements related to the startup parameter file. Let's take a look at this part.

Summary of parameter files:

1. Initial parameter file: pfile (initialization parameter file)

2. server parameter file: spfile (server-side parameter file)

1. pfile is a text file, and spfile is a binary file

2. pfile can be modified through a text editor. spfile is mainly modified through alter system set parameter = value [scope = memory | spfile | both ].

When Oracle is started, the order of reading parameter files is:

1. spfile $ ORACLE_SID.ORA (the spfile created when the database is created)

2. spfile. ora (oracle default spfile file)

3. init $ ORACLE_SID.ora (pfile created when the database is created)

4. init. ora (oracle default pfile file)

Oracle will read these parameter files in order. If 1 is not found, it will find 2 until it is found.

Common SQL statements related to parameter files

1. view the position of the currently used spfile and pfile:

Show parameter spfile/show parameter pfile

2. view the specific settings of the parameter file.

Show parameters

3. Get pfile from spfile

Create pfile = 'pfilesid. ora 'from spfile or create pfile = 'pfilesid. ora' from spfile = '/$ ORACLE_HOME/dbs/spfile. ora'

4. Modify parameters

Alter system set parameter = value [scope = memory | spfile | both]

1. The modification takes effect now, and restarting the database does not.

Scope = memory

2. The modification does not take effect now. It will take effect the next time you restart the database.

Scope = spfile

3. After modification, the changes will take effect, and the next restart of the database will also take effect.

Scope = both

Notice: Command alter system set parameter = value what is the default value when the scope option is not added? Most friends may answer the both answer without thinking about it. The actual situation is that when oracle is started with the spfile parameter, the default value is both, and when it is started with pfile, the default value is memory, that is, only the current value is modified. If you cannot determine the startup parameter file, you can use show parameter spfile to view it.

5. Specify the initial parameter file to start the database

Startup pfile = '/$ ORACLE_HOME/dbs/init $ ORACLE_SID.ora'

The specified server parameter file spfile cannot be used to start the database.

You can first convert the spfile to pfile, and then use this command to achieve the goal.

6. Check whether the system is started with pfile or spfile.

Show parameter spfile

If it is a null value, it is started by pfile.

This article introduces Oracle Database startup parameter files.