Oracle initialization parameter File learning ____oracle

Overview of server initialization parameter files

Oracle's initialization parameter file holds the initialization parameter configuration information necessary to create and start the database instance.
The initialization parameter file is corrupted and the database cannot be started.

In 11g, there are 341 initialization parameters, both explicit and implicit.

Prior to 9i, explicit parameters and their storage in a text file are referred to as text initialization parameter files. The default name is Init<sid>. ORA. The default location is the%oracle_home%/database directory.
The 11g database is created automatically in the%oracle_database%/admin/<sid>/pfile directory.

A local initialization parameter file when the text initializes the parameter file, either starting the local database or the remote database, requires reading a local text initialization parameter file, and using the parameter settings in it to configure the database instance. Therefore, to start the database remotely, you must save a copy of the initialization parameter file in the remote host. In addition, the modification of the text initialization parameter file must be done manually through administration.

For the above reasons, the server initialization parameter file was introduced in the database after 9i. The server initialization parameter file is a binary file saved on the database server side. The instance automatically reads the server initialization parameter file from the server. In addition, the execute ALTER SYSTEM statement in the data modifies the initialization parameters and, by default (Socope=both), is permanently recorded in the server initialization parameter file.

The server initialization parameter file is created using the Create SPFILE statement based on an existing text initialization parameter file. The server initialization parameter file is created automatically when you create a database using DBCA. Server initialization parameter file default name is Spfile<sid>. ORA. The default location is stored in the%oracle_home%/dbs directory. you can use Notepad or WordPad to open the server initialization parameter file and view the contents. However, you cannot modify the contents of it. Otherwise, file corruption is caused.

When the startup database is executed, the system looks for initialization parameter files in the following order:

1. Check if the text initialization parameter file is specified with the Pfile parameter

2. If you do not use the Pfile parameter, look for the default name server initialization parameter file in the default location

3, did not find the default server initialization parameter file, then in the default location to find the default name of the text initialization parameter file

You can use the show parameter SPFile command or query dynamic performance View V$parameter View the server initialization parameter file used by the current database second, create a server initialization parameter file

1. Create a text initialization parameter file that contains an explicit initialization parameter and store the file on the database server.

The text initialization parameter file should be created with the following considerations in mind:
* Initialization parameter file only sets the initialization parameters that need to be set, others take the system default
* Initialization parameter file can only include assignment statement and comment statement, comment statement begins with #, single line
* Initialize parameter parts of parameter file in order, parameter name and value are not case-sensitive
* If one row sets multiple parameters, it needs to be separated by a space
* If the parameter has more than one value, enclose the values in parentheses, separated by commas
* For arguments with string type values, if a character creation contains spaces or tabs, you must use either single or double quote strings to

2. Connect to Oracle Database with Sysoper or SYSDBA identity

3, the use of text initialization parameters file to create a server initialization parameter file

Create spfile=[= ' SPFile name '] from pfile[= ' spfile name ']|memory;

three, commonly used initialization parameters:processes = 150 Notes: Specifies the maximum number of operating system user processes that can be connected to an Oracle server at the same time. This value should allow all background processes, such as the job queue (SNP) process and the parallel execution (pnnn) process, to be performed. Value range: 6 to a value based on the operating system. Default value: Determined by parallel_max_servers timed_statistics= TRUE Description: Collects operating system timing information that can be used to optimize database and SQL statements. To prevent overhead from requesting time from the operating system, set the value to zero. Setting this value to TRUE is also useful for viewing the progress of long-running operations. Value Range: TRUE | False default value: false shared_pool_size = 50331648 Description: Specifies the size of the shared pool in bytes. Shared pools include objects such as shared cursors, stored procedures, control structures, and parallel execution of message buffers. A larger value can improve the performance of a multiuser system. Value range: KB-depends on the operating system. Default value: If the 64-bit operating system, the value is 64MB; In other cases, the value is 16MB. Large_pool_size = 8388608 explains: Specifies the size of the allocation heap for a large pool, which can be used as session memory by a shared server, as a message buffer for parallel execution and as a disk I/O buffer for Rman backup and recovery. Value range: 600K (minimum); >= 20000M (The maximum value is based on the operating system). Default value: 0 unless a parallel execution or dbwr_io_slaves java_pool_size = 33554432 description is specified: In bytes, specifies the size of the Java storage pool, which is used to store Java methods and classes that are defined in shared memory, and the A Java object that is ported to the Java session space at the end. Value range: Depends on the operating system.
Default value: Depending on the operating system control_files = d:\oracle\oradata\mydb\CONTROL01. Ctl,d:\oracle\oradata\mydb\control02. Ctl,d:\oracle\oradata\mydb\control03. CTL Description: Specifies one or more control file names. Oracle recommends using multiple files for different devices or OS file mirrors. Value range: 1-8 file name (with path name). Default value: Depending on the operating system db_block_size = 8192 Description: The size (in bytes) of an Oracle database block. This value is set when the database is created and cannot be changed thereafter. Value range: 1024-65536 (depends on the operating system). Default value: 2048 (depending on the operating system) Db_cache_size = 25165824 Description: Specifies a buffer of the standard block size for the cache. Value range: at least 16M. Default value: 48M compatible = 9.2.0.0.0 Description: Allows you to use a new release, while ensuring backward compatibility with previous versions. Value range: Default is the current release. Default value: Determined by release db_file_multiblock_read_count= 16 Description: The maximum number of blocks read during an I/O operation involving a full continuous scan. Value range: Depends on the operating system. Default value: 8 Fast_start_mttr_target = 300 Description: Specifies the amount of time (estimated seconds) to recover from a single database routine crash. Fast_start_mttr_target is internally converted into a set of parameters to modify the operation of the database, thereby controlling its recovery time within a certain range of the total "restore Average Time (MTTR)". This parameter is only supported for versions that have the Quick Launch recovery feature. Value range: [0,3600]. It calculates the number of data buffer cache entries, and is greater than the limit of the number of blocks in the maximum log. Default value: 0 undo_management = AUTO Description: Specifies which undo space management mode should be used by the system. If set to AUTO, the routine will start in SMU mode. Otherwise, it will start in Rbu mode. In Rbu mode, the undo space is allocated externally like a fallback segment. In SMU mode, undo space is allocated externally as if it were an undo tablespace. Value range: AUTO or MANUAL default: If you startThe undo_management parameter is ignored when the first routine is used, and the default value is MANUAL, and the routine is started in Rbu mode. If this is not the first routine, the routine is started by the undo mode that is used when the other existing routines are started.

Undo_tablespace = UNDOTBS1 Description: Undo tablespace is used only to store undo information. Undo_tablespace is only allowed in System Management Undo (SMU) mode. The routine will use the specified undo Tablespace,<undoname>. If the tablespace does not exist, or is not an undo tablespace, or is being used by another routine, the rules STARTUP will fail. Default value: Each database contains 0 or more undo tablespaces. In SMU mode, each ORACLE routine is assigned one (and only one) undo tablespace.

Undo_retention = 10800 Description: undo_retention
parameter is used to specify the total amount of committed undo information to be retained in the database. You can set this parameter value when the routine starts. You can calculate the amount of undo space that is required to satisfy the undo reservation requirement: undospace = rd * UPS, where undospace is represented by the number of undo blocks, an rd in seconds undo_retention, and the UPS is represented by the number of undo blocks per second. Value range: The maximum allowable value is (2 * * 32) seconds. Default value: 30 seconds.

remote_login_passwordfile= EXCLUSIVE Description: Specifies whether the operating system or a file checks the password of the user with permissions. If set to NONE, Oracle ignores the password file. If set to exclusive, each user with permissions will be authenticated using the password file for the database. If set to shared, multiple databases will share the SYS and INTERNAL password file users. Value Range: NONE | SHARED | Exclusive Default value: NONE

Db_domain = Description: Specifies the name of the database extension (for example, US. oracle.com) to make the database name created in a domain unique, it is recommended that you specify this value. Value range: Any string that is delimited by a period and can be up to 128 characters long. Default value: World

instance_name = MyDB Description: Used to uniquely identify a database routine when multiple routines use the same service name. Instance_name should not be confused with the SID, which is actually the unique identification of the routines that share memory on a single host. Value range: any alphanumeric character. Default value: Database SID

Dispatchers = (protocol=tcp) (SERVICE=MYDBXDB) Description: Sets the number and type of schedulers for setting up a shared environment that uses shared servers. You can specify several options for this parameter. For more information, see the Oracle8i Administrator's Guide and Oracle Net Administrator's Guide. This is an example of a string value: ' (protocol=tcp) (dispatchers=3) '. Value range: A valid specified value for the parameter. Default value: NULL

job_queue_processes = 10 Description: Only for replication environments. It specifies the number of SNP job queue processes per routine (SNP0, ...). SNP9, Snpa, ... SNPZ). To automatically update a table snapshot or perform a request created by Dbms_job, set the parameter to 1 or greater. Value range: 0 to 36 default value: 0

Hash_join_enabled = True describes: If set to true, the optimizer will consider a hash join when calculating the most efficient join method. Oracle recommends that the Data Warehouse application should use a true value. Value Range: TRUE | FALSE Default value: TRUE

Background_dump_dest = D:\oracle\admin\mydb\bdump Description: Specifies the path name (directory or disk) to which the trace file is written for the background process (LGWR, DBW N, and so on) during the Oracle operation. It also defines the location of the database alert files that record important events and messages. Value range: Any valid directory name. Default value: Oracle_home/rdbms/log (depending on the operating system)

User_dump_dest =d:\oracle\admin\mydb\udump Description: Specifies the pathname for the directory in which the server will write debug trace files as a user process identity. For example, the directory can be set: the NT operating system
C:/ORACLE/UTRC; /ORACLE/UTRC on UNIX operating systems; Or disk$ur3:[oracle on the VMS operating system. UTRC]. Value range: A valid local path name, directory, or disk. Default value: Depends on the operating system

Core_dump_dest = D:\oracle\admin\mydb\cdump Description: Specifies the directory name for the core dump location (for Unix). Value range: Any valid directory name. Default value: Oracle_home/dbs

Sort_area_size = 524288 Description: sort_area_size specifies, in bytes, the maximum amount of memory used by the sort. When the sorting is complete, the rows are returned and memory is freed. Increasing this value can increase the efficiency of large sorting. If the amount of memory is exceeded, a temporary disk segment is used. Value range: the value (minimum) equivalent to 6 database blocks to the value (maximum) determined by the operating system. Default value: Depends on the operating system

db_name = mydb Description: A database identifier that corresponds to the name specified in the CREATE DATABASE statement. Value range: Any valid name can have up to 8 characters. Default value: None (but should be specified)

Open_cursors = 300 Notes: Specifies the maximum number of cursors (environment zones) that can be opened at one time, and limits the size of the PL/SQL cursor cache that Pl/sql uses to avoid parsing again when the user executes the statement again. Set this value high enough to prevent the application from running out of open cursors. Value range: 1-operating system limit value. Default value: 64

Star_transformation_enabled= FALSE explains: Determines whether a cost based query transformation will be applied to a star query. If set to true, the optimizer will consider applying a cost-based conversion to a star query; If set to False, no conversions are used; If set to Temp_disable, the query conversion is considered, but no temporary tables are used. Value Range: TRUE | FALSE | Temp_disable Default value: FALSE

query_rewrite_enabled = FALSE Description: Enables or disables query rewriting of materialized views. A specific materialized view is enabled only if session parameters and individual materialized views are enabled, and cost based optimization is enabled. Value Range: TRUE | False Default value: False

Pga_aggregate_target = 25165824 Description: Specifies the target PGA total memory for all server processes connected to the routine. Set this parameter to a positive number before you enable the automatic setting of the workspace. This part of memory does not reside in the SGA. The database uses this parameter value as the amount of PGA memory it is using. When this parameter is set, the SGA is subtracted from the total amount of system memory available for the Oracle routine. The remaining amount of memory can then be allocated to Pga_aggregate_target. Value range: integer plus letter K, M, or G to specify this limit as kilobytes, megabytes, or gigabytes. The minimum value is 10M, the maximum is the 4000G default value: Unspecified, which means that automatic optimization of the workspace is completely disabled.

Aq_tm_processes = 1 Notes: If greater than 0, time monitoring for queue messages is enabled. This time value can be used to specify the delay and expiration attributes for the message (for application development). Value range: 0-10 default value: 0 Four, modify initialization parameters:

1. Alter session set Reply-level modifications
2. Alter system set
Dynamic Initialization parameters: Effective immediately after modification, for the current database instance

Static initialization parameters: Initialization parameters are not valid in the current instance after modification, parameters are saved to the server initialization parameter file, and the next time the database is restarted
Scope=spfile. You can only modify the parameter values of the server initialization parameter file, which has no effect on the current database instance. Modified scope=memory for dynamic parameters and static parameters. Only the initialization parameter values in memory can be modified. Only for changes to dynamic parameters, the current instance takes effect immediately

Scope=both. Modifies the in-memory and initialization parameter files. is suitable for dynamic parameter file modification

3. You can use ALTER system resetparameter_name=parameter_value to clear an explicit parameter. Restore system defaults After cleanup Five, export server initialization file parameters

You can use the Create PFILE statement to export a server initialization parameter file to a text initialization parameter file. You can consider exporting in the following situations:

1. Create a backup of the server initialization parameter file

2. To facilitate the diagnosis of database failures, you need to obtain information about all the initialization parameters that are used by the current database instance. The effect is equivalent to performing show parameter
command or query dynamic performance View V$parameter

3. In order to modify the parameter values in the server initialization parameter file, first export the server initialization parameter file to the text initialization parameter file, then manually modify the parameters in the text initialization parameter file, and create a new server initialization parameter file according to the modified text parameter file. vi. recover missing or corrupted server initialization parameter files

1. If the database is running, you can execute the CREATE SPFile from memory statement, rebuilding the server initialization parameters based on the current memory initialization parameters

2. If there are valid text initialization parameters, you can create them with text parameters: Create SPFile from Pfile rebuild server initialization parameters

3, use the server initialization parameter file backup restore lost or corrupted server parameter file

4, the above methods are not available, you can first use the alarm file to display the initialization parameter values (database startup will use the initialization parameter file write to the alarm file) to reconstruct the text initialization parameter file, and then use the Create SPFile from Pfile way.