Parameter file of Oracle Storage Structure

Oracle has many parameter files, but the most important one is database parameter files. Database parameter files include pfile and spfile. Pfile must be manually modified. It is a text file, and spfile is a binary file, which can be changed dynamically.

Spfile is available for the following reasons:

1) parameter files cannot be reproduced. We know that pfile is required to start the database on the client. Spfile always exists on the database server host itself and cannot be placed on the client, thus ensuring information consistency;

2) spfile can be modified to spfile by using the alter system command without manual modification.

To ensure simplicity, we recommend that you store the spfile by default.

An oracle database cannot be started without the parameter file. It can be seen that the database parameter file is quite important. You can use the v $ parameter view to query the parameter values, or show parameter. I personally think show parameter is easy to use, because it can be configured with the default % parameter value %; while the advantages of v $ parameter, you can find multiple fields, more accurate positioning of the information we want.

Oracle's task in nomount stage is to read the parameter file and search for it under $ ORACLE_HOME/dbs. The search sequence is spfile <ORACLE_SID>. ora, spfile. ora, init <ORACLE_SID>. ora.

Next, let's talk about several common operations of spfile and pfile.

The operations on several spfiles are as follows:
1) convert to spfile: (operate in nomount state)
Show parameter spfile; -- check whether spfile is used
Create spfile from pfile; -- note that the default location is used for storage.
Startup force; -- restart
Show parameter spfile;

2) set the value in spfile
Alter system set parameter = value <comment = 'text'> <deferred>
<Scope = memory | spfile | both>
<Sid = 'sid | * '>
Comment: comment. The update_comment field in v $ parameter can be queried.
Deferred: effective for newly established sessions
Scope: scope.

3) Cancel spfile settings
Alter system reset parameter <scope = memory | spfile | both> sid = <'sid | * '>
The sid here is required. Single Instance available default sid = '*'

4) Create pfile from spfile
Create pfile = filename from spfile;

5) corrected the corrupted spfile.
Use the alarm log Content to create a pfile, and then use the create spfile to convert a spfile

We use pfile in two ways: http://www.bkjia.com
1) Use pfile to start the database
Startup pfile = 'filename ';
2) use pfile to create spfile
Create spfile from pfile = 'filename ';

 

Next we will paste all the content of a spfile and take a look at the content.

The following is the spfile content. Let's take a look at it:
[Oracle @ localhost dbs] $ strings spfileorcl. ora
Orcl. _ db_cache_size = 176160768
Orcl. _ Java _pool_size = 4194304
Orcl. _ large_pool_size = 4194304
Orcl. _ shared_pool_size = 54525952
Orcl. _ streams_pool_size = 0
/******************** I am a splitting line ***********/
The above section is the allocation of SGA
/******************** I am a splitting line ***********/
*. Audit_file_dest = '/u01/app/oracle/admin/orcl/adump'
*. Background_dump_dest = '/u01/app/oracle/admin/orcl/bdump'/* Alarm log location */
*. Compatible = '10. 2.0.1.0 '/* version */
*. Control_files = '/u01/app/oracle/oradata/ORCL/controlfile/o1_mf_7xzsjpnk _. ctl ','/u01/app/oracle/flash_recovery_area/ORCL/controlfile/o1_mf_7xzsjqdita _. ctl '/* Control File Location */
*. Core_dump_dest = '/u01/app/oracle/admin/orcl/cdump'
*. Db_block_size = 8192/* default data block size of the database */
*. Db_create_file_dest = '/u01/app/oracle/oradata'
*. Db_domain = 'oracle. com'
*. Db_file_multiblock_read_count = 16
*. Db_name = 'orcl '/* Database Name, minimum parameter for database startup */
*. Db_recovery_file_dest = '/u01/app/oracle/flash_recovery_area'
*. Db_recovery_file_dest_size = 2147483648
*. Dispatchers = '(PROTOCOL = TCP) (SERVICE = orclXDB )'
*. Job_queue_processes = 10
*. Open_cursors = 300
*. Pga_aggregate_target = 81788928
*. Processses = 150
*. Remote_login_passwordfile = 'clusive '/* optional parameter of the password file */
. Sga_target = 243269632
*. Undo_management = 'auto'/* undo tablespace Management Method */
*. Undo_tablespace = 'undotbs1'
*. User_dump_dest = '/u01/app/oracle/admin/orcl/udump'/* tracking file storage location. For example, if you set alter system set SQL _TRACE = true, the content will be saved here */