Oracle initialization parameter file knowledge, oracle knowledge

Oracle initialization parameter file knowledge, oracle knowledge

1. Server initialization parameter file Overview

The oracle initialization parameter file stores the initialization parameter configuration information required for database instance creation and startup.

The initialization parameter file is corrupted and the database cannot be started.

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

In versions earlier than 9i, explicit parameters are stored in a text file, which is called a text initialization parameter file. The default name is INIT. ORA. The default location is % ORACLE_HOME %/database directory.

When the 11g database is created, it is automatically in the % ORACLE_DATABASE %/admin // pfile directory.

A local initialization parameter file during the text initialization parameter file. Whether you start a local database or a remote database, you must read a local text initialization parameter file, and use the parameter settings to configure the database instance. Therefore, to remotely start the database, you must save a copy of the initialization parameter file in the remote host. In addition, you must manually modify the text initialization parameter file.

For the above reason, the server initialization parameter file is introduced in the database after 9i. The server initialization parameter file is a binary file stored on the database server. The instance automatically reads the server initialization parameter file from the server. In addition, the alter system statement is executed in the data to modify the initialization parameters. By default, the parameters are permanently recorded in the server initialization parameter file.

The server initialization parameter file is created using the create spfile statement based on the existing text initialization parameter file. The server initialization parameter file is automatically created when you use dbca to create a database. The default name of the server initialization parameter file is SPFILE. 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 content. However, the content cannot be modified. Otherwise, the file is damaged.

When starting the database in startup, the system looks for the initialization parameter file in the following order:

1. Check whether the pfile parameter is used to specify the text initialization parameter file

2. If the pfile parameter is not used, search for the default name server initialization parameter file in the default location

3. If the default server initialization parameter file is not found, search for the text initialization parameter file with the default name in the default location

You can use the show parameter spfile command or query the dynamic performance view v $ parameter to view the server initialization parameter files used by the current database.

Ii. Create a server initialization parameter file

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

Note the following when creating a text initialization parameter file:

* In the initialization parameter file, you only need to set the initialization parameters. Others use the System Default

* The initialization parameter file can only contain values and comments. The comment statement starts with # And is a single line.

* Initialize the order of parameters in the parameter file. The parameter name and value are case-insensitive.

* If multiple parameters are set for a row, separate them with spaces.

* If the parameter has multiple values, enclose these values in parentheses and separate them with commas.

* For parameters with string-type values, if a character contains spaces or tabs, it must be caused by single quotes or double quotation marks.

2. Connect to the oracle database as sysoper or sysdba

3. Use the text initialization parameter file to create the server initialization parameter file

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

Iii. Common initialization parameters:

Processes = 150 Description: specifies the maximum number of user processes that can be connected to an Oracle Server at the same time. This value should allow execution of all background processes, such as Job Queue (SNP) processes and parallel execution (Pnnn) processes. Value Range: 6 to a value based on the operating system. Default Value: timed_statistics = TRUE determined by PARALLEL_MAX_SERVERS. Description: Collects timing information of the operating system, which can be used to optimize databases and SQL statements. To avoid overhead caused by request time from the operating system, set this value to zero. Setting this value to TRUE is also useful for viewing the progress of a long operation. Value range: TRUE | FALSE default value: FALSE shared_pool_size = 50331648 Description: the size of the Shared Pool is specified in bytes. The shared pool contains objects such as shared cursors, stored procedures, control structures, and parallel execution message buffers. A large value can improve the performance of multi-user systems. Value Range: 300 KB-depends on the operating system. Default Value: 64 MB for a 64-bit operating system; 16 MB for other cases. Large_pool_size = 8388608 Description: Specifies the size of the heap allocated to a large pool, which can be used by the Shared Server as the session memory, message Buffer Used for parallel execution and disk I/O Buffer Used for RMAN backup and recovery. Value Range: 600 K (minimum value);> = 20000 M (maximum value depends on the operating system ). Default Value: 0. unless parallel execution or DBWR_IO_SLAVES java_pool_size = 33554432 is configured, the size of the Java storage pool is specified in bytes, it is used to store the representation of Java methods and classes defined in the shared memory, and the Java objects transplanted to the Java session space at the end of the call. Value range: Depends on the operating system.

Default Value: control_files = d: \ oracle \ oradata \ mydb \ CONTROL01.CTL, d: \ oracle \ oradata \ mydb \ CONTROL02.CTL, d: \ oracle \ oradata \ mydb \ CONTROL03.CTL Description: specify one or more control file names. Oracle recommends that you use multiple files for different devices or OS file mirrors. Value Range: 1-8 file name (with path name ). Default Value: db_block_size = 8192 according to the operating system. Description: size (in bytes) of an Oracle database block ). This value is set when you create a database and cannot be changed later. Value Range: 1024-65536 (depending on the operating system ). Default Value: 2048 (depending on the operating system) db_cache_size = 25165824 Description: Specify the buffer of the standard block size for the cache. Value Range: at least 16 Mb. Default Value: 48 M compatible = 9.2.0.0.0 Description: allows you to use a new release and ensures backward compatibility with previous versions. Value Range: The default value is the current release. Default Value: determined by the release version db_file_multiblock_read_count = 16 Description: Maximum number of blocks read during an I/O operation involving a completely continuous scan. Value range: Depends on the operating system. Default Value: 8 fast_start_mttr_target = 300 Description: specifies the time required to recover from the crash of a single data warehouse routine (Estimated number of seconds ). FAST_START_MTTR_TARGET is converted into a set of parameters internally to modify database operations, so that the recovery time is controlled within a certain range of the total "average recovery time (MTTR. This parameter is supported only in versions with the "Quick Start fault recovery" function. Value Range: []. It calculates the number of cache entries in the data buffer and exceeds the limit of the maximum number of logs. Default Value: 0 undo_management = AUTO Description: Specifies which undo space management mode should be used by the system. If it is set to AUTO, the routine starts in SMU mode. Otherwise, it will start in RBU mode. In RBU mode, the Undo space is allocated externally as the rollback segment. In SMU mode, undo space is allocated externally just like undo tablespace. Value Range: AUTO or MANUAL default value: If the UNDO_MANAGEMENT parameter is ignored when the first routine is started, the default MANUAL is used and the routine starts in RBU mode. If this is not the first routine, it is started in the Undo mode used when other existing routines are started.

Undo_tablespace = UNDOTBS1 Description: Undo tablespace is only used to store undo information. UNDO_TABLESPACE can only be used in System Management undo (SMU) mode. The routine uses the specified undo tablespace ,. If the tablespace does not exist, is not undo, or is being used by another routine, the routine STARTUP fails. Default Value: each database contains 0 or more undo tablespaces. In SMU mode, one (and only one) undo tablespace is assigned to each ORACLE routine.

Undo_retention = 10800 Note: UNDO_RETENTION

This parameter is used to specify the total amount of submitted undo information to be retained in the database. You can set this parameter value when the routine is started. You can calculate the amount of UndoSpace required to meet the UndoSpace retention requirements: UndoSpace = RD * UPS. UndoSpace is represented by the number of UndoSpace blocks, and RD is represented by UNDO_RETENTION in seconds, UPS is represented by the number of Undo blocks per second. Value Range: the maximum value allowed 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 a user with permissions. If it is set to NONE, Oracle ignores the password file. If it is set to EXCLUSIVE, the database password file will be used to verify each user with permissions. If it is 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: it is recommended to specify the database name extension (for example, US. ORACLE. COM. Value Range: any string separated by periods. It can contain a maximum of 128 characters. Default Value: WORLD

Instance_name = mydb Description: when multiple routines use the same service name, it is used to uniquely identify a data warehouse routine. INSTANCE_NAME should not be confused with SID. It is actually the unique identifier of each routine that shares memory on a host. Value range: Any letter or digit. Default Value: Database SID

Dispatchers = (PROTOCOL = TCP) (SERVICE = mydbXDB) Description: set the number and type of scheduler to set the shared environment of the Shared Server. You can specify several options for this parameter. For more information, see "Oracle8i Administrator Guide" and "Oracle Net Administrator's Guide ". This is an example of a string value: '(PROTOCOL = TCP) (DISPATCHERS = 3 )'. Value Range: valid value of the parameter. Default Value: NULL

Job_queue_processes = 10 note: it is only used to copy the environment. It specifies the number of SNP Job Queue processes for each routine (SNP0,... SNP9, SNPA,... SNPZ ). To automatically update a table snapshot or execute a request created by DBMS_JOB, set this parameter to 1 or a greater value. Value Range: 0 to 36 default value: 0

Hash_join_enabled = TRUE: if it is set to TRUE, the optimizer considers hash join when calculating the most effective join method. Oracle recommends that the data warehouse application use the 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) of the trail file written to the background process (LGWR, DBW n, etc.) during Oracle operations ). It also defines the location of the database warning file that records 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: Specify the path name for the directory where the server writes the debug trace file as a user Process Identity. For example, the directory can be set as follows:

C:/ORACLE/UTRC;/oracle/utrc on UNIX; or DISK $ UR3 on VMS: [ORACLE. 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 of the core dump location (for UNIX ). Value range: Any valid directory name. Default Value: ORACLE_HOME/dbs

Sort_area_size = 524288 Note: SORT_AREA_SIZE is in bytes and specifies the maximum memory used for sorting. After sorting is completed, the rows are returned and the memory is released. Increasing this value can improve the efficiency of large-scale sorting. If the memory size exceeds this limit, a temporary disk segment is used. Value Range: equivalent to the value (minimum value) of 6 database blocks to the value (maximum value) determined by the operating system ). Default Value: Depends on the operating system.

Db_name = mydb Description: a DATABASE identifier, which corresponds to the name specified in the create database statement. Value range: Any valid name can contain a maximum of 8 characters. Default Value: none (but should be specified)

Open_cursors = 300 Description: specifies the maximum number of cursors (Environment regions) that can be opened at a time for a session, and limits the cache size of PL/SQL cursors, to avoid re-syntax analysis when you execute the statement again. Set this value to high enough to prevent the application from exhausting open cursors. Value Range: 1-operating system limit value. Default Value: 64

Star_transformation_enabled = FALSE: determines whether the cost-based Query conversion will be applied to the star query. If set to TRUE, the optimizer applies cost-based conversions to star queries. If set to FALSE, no conversions are used. If set to TEMP_DISABLE, query conversions are considered, however, temporary tables are not used. Value range: TRUE | FALSE | default value of TEMP_DISABLE: FALSE

Query_rewrite_enabled = FALSE description: enable or disable Query Rewriting for the materialized view. A specific materialized view is only enabled under the following conditions: 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 Note: specify the total memory of the target PGA for all server processes connected to the routine. Set this parameter to a positive value before enabling the automatic workspace. This part of memory does not reside in SGA. The database uses this parameter value as the target PGA memory used by the database. When this parameter is set, SGA is subtracted from the total system memory that can be used for Oracle routines. You can then allocate the remaining memory to pga_aggregate_target. Value Range: an integer with letters K, M, or G to specify this limit as kilobytes, megabytes, or gigabytes. The minimum value is 10 M, and the maximum value is 4000G. The default value is "unspecified", indicating that automatic optimization of the workspace is completely disabled.

Aq_tm_processes = 1 Note: if it is greater than zero, the time monitoring of queue messages is enabled. This time value can be used to specify the delay and failure attributes of a message (for application development ). Value Range: 0-10 default value: 0

4. Modify initialization parameters:

1. alter session set... session-level Modification

2. alter system set ·····

Dynamic initialization parameter: takes effect immediately after modification, acting on the current database instance

Static initialization parameters: After the initialization parameters are modified, the parameters will not take effect in the current instance. The parameters will be saved to the server initialization parameter file and will take effect the next time you restart the database.

Scope = spfile. Only the parameter values of the server initialization parameter file can be modified, which does not affect the current database instance. This parameter is applicable to modifying scope = memory for dynamic and static parameters. Only the initialization parameter values in the memory can be modified. It is only suitable for modifying dynamic parameters and the current instance takes effect immediately.

Scope = both. Modify and initialize parameters in the memory file. Is suitable for modifying dynamic parameter files

3. Use alter system resetparameter_name = parameter_value to clear an explicit parameter. Restore system default after clearing

V. Export server initialization file Parameters

You can use the create pfile statement to export the server initialization parameter file as a text initialization parameter file. Export can be considered in the following cases:

1. Create a backup of the server initialization parameter file

2. To facilitate the diagnosis of database Fault Causes, You need to obtain the settings of all initialization parameters used by the current database instance. The result is equivalent to executing show parameter.

Command or query the dynamic performance view v $ parameter

3. to modify the parameter values in the server initialization parameter file, first export the server initialization parameter file to the text initialization parameter file, and then manually modify the parameters in the text initialization parameter file, create a new server initialization parameter file based on the modified parameters file.

6. Restore the lost or damaged server initialization parameter file

1. If the database is running, run the create spfile from memory statement to recreate the server initialization parameters based on the initialization parameters in the current memory.

2. If there are valid text initialization parameters, you can use the text parameters to create: create spfile from pfile to recreate the server initialization parameters.

3. Use backup of the server initialization parameter file to restore the lost or damaged server parameter file

4. When none of the above methods is available, you can first use the initialization parameter values shown in the alarm file (the database will write the initialization parameter file to the alarm file at startup) to recreate the text initialization parameter file, then, use the create spfile from pfile method.