Oracle Database 11g Some important initialization parameters (i)

Oracle Database 11g Some important initialization parameters (i)

Initialize parameter file location

D:\app\Administrator\admin\orcl1\pfile\init.ora

Init.ora

1, Diagnostic_dest

In 11g, there is a new structure for storing diagnostic information called the Automated Diagnostics repository (AUTOMATICD Diagnostic Repository ADR).

Specify the directory location with some parameters, such as $ORACLE_BASE/DIAG/RDBMS/ORCL/ORCL. The first ORCL is the database name, and the second ORCL is the instance name.

2, Fixed_date

is a new parameter in 11g that allows setting Sysdate to return a constant date instead of the current date. such as Fixed_date=yyyymm-dd Hh24:mi:ss

The default is none, such as Fixed_date=none. Some parameters can be set dynamically.

3. Audit-related parameters

(1) Audit_trail

This parameter is used to turn audit functionality on or off for the database. If it is not open, set this parameter to none or false, such as Audit_trail=none.

If you want to turn on auditing, you can set the following values:

OS Oracle writes audit records to an operating system file.

DB Oracle Logs audit information to the aud$ table under sys.

Db,extended is similar to the DB setting, but provides extended information, such as Sys.aud$.sqlbind and SQLText columns.

XML allows the audit information to be written to an OS file in XML form.

Xml,extended is similar to XML and adds extended information sqlbind,sqltext.

such as Audit_trail=db

No default value, this parameter is a static type.

(2) Audit_file_dest

If you set Audit_trail=os, or xml,xml,extended, this parameter is used to set the directory location for audit information.

such as: audit_file_dest= $ORACLE _base/admin/orcl/audit

Default value: $ORACLE _home/rdbms/audit

Parameter type: Dynamic, can be modified with alter system ... deferred.

(3) Audit_sys_operations

If you set this parameter to True,oracle, all activities of the SYS user or user with SYSDBA and sysoper roles are audited and the audit information is written to a file or table.

such as: Audit_sys_operations=true

Default value: False

Parameter type: static

(4) Ldap_directory_sysauth

is a new parameter in 11g that allows or disables directory authorization for SYSDBA or Sysoper permissions, with a value of Yes or No.

such as: Ldap_directory_sysauth=yes

Default value: None

Parameter type: static

4, with the database name and other commonly used parameters

(1) Db_name and Db_unique_name

The db_name is used to set the name of the database, which is a mandatory parameter. The db_name is the same value as the ORACLE_SID environment variable. Db_name up to 8 characters.

such as: DB_NAME=ORCL

Default value: False

Parameter type: static

DB_UNIQUE_NAME specifies the globally unique database name.

(2) Db_domain

This parameter specifies the fully qualified name of the database or domain name, which is typically the same name as the organization that owns the database.

such as: Db_domain=world

Default value: null

Parameter type: static

(3) instance_name

This parameter specifies the name of the instance in which instance_name has the same value as Db_name, and in RAC, a single database service can be assigned multiple instances.

such as: INSTANCE_NAME=ORCL

Default value: Instance SID

Parameter type: static

(3) Service_Name

This parameter provides a name for the database service, typically a combination of the database name and the database domain

such as: Service_name=orcl.world

Default value: Db_name.db_domain

Parameter type: Dynamic, can be modified with alter system.

(4) compatible

Use this parameter to set the version of the database

such as: copatible=11.1.0

Default value: 11.1.0

Parameter type: static

(5) Instance_type

This parameter is used to specify whether the instance is a DB instance or an automatic storage management instance.

such as: Instance_type=asm

Default value: RDBMS

Parameter type: static

(6) Nls_date_format

This parameter is used to set the Oracle default date format.

such as: Nls_date_format=yyyy-mm-dd HH24:MI:SS

Default value: Depends on the nls_territory variable and operating system.

Parameter type: Semi-dynamic semi-static, can be modified with alter session, but can not be modified with alter system.

5. Parameters related to the file

(1) ifile

You can use IFile to embed other initialization parameters in the Init.ora file.

such as: Ifile=config.ora

Default value: None

Parameter type: static

(2) Confrol_file

Use this parameter to specify the control file. The minimum number of control files is 1,oracle recommended, with at least two control files per instance.

such as: control_file= ($ORACLE _base/oradata/orcl/cont01.ctl, $ORACLE _base/oradata/orcl/cont02.ctl)

Default value: Depending on the operating system

Parameter type: static

(3) Control_file_record_keep_time

This parameter specifies the number of days that are recorded in the Oracle retention control file, and Oracle recommends setting a higher value for this parameter to preserve the online disk backup information in all control files.

such as: control_file_record_wkkp_time=31

Default value: 7

Parameter type: Dynamic, can be modified using ALTER system

(4) Utl_file_dir

When using the Utl_file package to read and write operating system files, the Utl_file_dir parameter can be used to specify the IO directory that Oracle handles.

such as: utl_file_dir= $ORACLE _base/utl_dir

Default value: None indicates no use of

Parameter type: static

6. Parameters related to the managed files

If you decide to use the OMF (Oracle Managed file Oracle managed files) feature, you need to use two parameters to define its format, as follows:

(1) Db_create_file_dest

This parameter specifies the default directory for managing files.

such as: db_create_file_dest= $ORACLE _base/admin/orcl/dbfile

Default value: None

Parameter type: Dynamic, can modify its format with ALTER system or session

(2) Db_create_online_log_dest_n

This parameter specifies the default location for OMF online redo log files and control files.

such as: db_create_online_log_dest_1= $ORACLE _base/admin/orcl/log

Default value: None

Parameter type: Dynamic, can be modified with alter system or Sessin

7. Process and Session Parameters

(1) processes

This parameter sets the upper limit for concurrent connections to database process data.

such as: processed=500

Default value: 6

Parameter type: static

(2) db_writer_processed

This parameter specifies the initial number of instance database write processes

such as: db_writer_processes=8

Default value: 1

Parameter type: static

(3) Open_cursor

This parameter sets a limit on the number of open cursors that a single session can have at a given time.

such as: open_cursor=300

Default value: 50

Parameter type: Dynamic, can be modified with alter system

8. Memory Configuration Parameters

(1) Memory_target

Use this parameter memory_target to specify the memory assigned to Oracle when using automatic memory management to assign intrinsic to an Oracle instance. Unit is KB,MB,GB

such as: memory_target=1024m

Default value: 0

Parameter type: Dynamic

(2) Memory_max_target

This parameter sets the maximum value of the Memory_target parameter setting, ranging from 0 to the maximum memory available to an Oracle instance.

such as: memory_max_target=2g

Default value: 0

Parameter type: static

If Memory_target is specified and Memory_max_target is omitted, the value of Memoery_max_target is the same as the value of Memoery_target.

(3) Db_cache_size

This parameter sets the default cache pool size for buffers that have a base block size (defined by the db_block_size block size).

such as: db_cache_size=720m

Default value: If the Memory_target parameter is specified, this parameter defaults to 0, otherwise the default value is greater than 48M or 4M

Parameter type: Dynamic, can be modified with alter system

(4) Db_kepp_cache_size

The buffer pool typically does the same for the objects placed in it, and any objects remain in the pool as long as the buffer cache has space available. When the pool is full, the oldest objects are deleted, freeing up space for the new objects.

There are two dedicated buffer pools (save pool and recycle pool) that allow you to indicate how you want the buffer to handle a particular object when the object is created.

Use this parameter to set the size of the save pool

such as: db_keep_cache_size=500m

Default value: 0, indicating that this parameter is not configured

Parameter type: Dynamic, can be modified with alter system

(5) Db_recycle_cache_size

This parameter is used to specify the size of the buffer cache recycle pool.

such as: db_recycle_cache_size=200m

Default value: 0, indicating that this parameter is not configured

Parameter type: Dynamic

(6) Db_nk_cache_size

This parameter is used to specify non-standard size buffer buffers. n can use 2,4,8,32 as a parameter.

such as: db_8k_cache_size=4096m

Default value: 0

Parameter type: Dynamic

(7) Client_result_cache_lag

This parameter is used to specify the maximum time that the OCI client query makes another round trip. Unit is seconds

such as: client_result_cache_lag=1000

Default value: 500 (seconds)

Parameter type: static

(8) Client_result_cache_size

This parameter specifies the maximum amount of memory that is allocated to the client for each process result set that is tuned for buffering.

such as: client_result_cache_size=80m

Default value: 0

Parameter type: static

(9) Control_management_pack_access

This parameter is used to activate two management packages for a server.

Diagnostic packages: such as: AWR,ADDM.

Tuning packages: such as; SQL Tuning advisor,sql Access Advisor, etc.

such as: Control_management_pack_access=awr

Default value: Diagnostic+tuning

Parameter type: Dynamic, can be modified with alter system

(Ten) Large_pool_size

This parameter can be understood as a variant of the shared pool and is an upgrade. You can use this parameter to maximize the release of a shared pool for caching SQL queries and data dictionary information.

such as: large_pool_size=1g

Default value: 0

Parameter type: Dynamic, can be modified using ALTER system

9. Archive Log Parameters

(1) Log_archive_dest_n

where n=1,2,3....10 up to 10 locations. This parameter is used to specify the location of the archive log. The database only uses this parameter in archive mode.

such as: log_archive_dest_1= ' location= $ORACLE _base/admin/orcl/arch '

Default value: None

Parameter type: Dynamic, can be specified using ALTER system or session

(2) Log_archive_format

Use this parameter to specify the default format for the archive redo log file name. The format parameters are as follows:

%t represents the thread number, and%s represents the log sequence number%r represents the Redo log ID

such as: log_archive_format= ' Log%t_%s_%r.arc '

Default value: Depending on the operating system

Parameter type: static

10. Distribution Space Parameters

(1) undo_management

If Undo_management is set to auto, the scatter space is used to store the scatter record. Oracle will automatically manage the sub-lock segment.

such as: Undo_management=auot

Default value: Auto

Parameter type: static

(2) Undo_tablespace

This parameter specifies the default tablespace for the scatter record, and if there is no scatter table space, Oracle will use the system fallback segment for the scatter store. If you do not specify this parameter when you create the database, and you select AUM (Automatic unod Management), Oracle creates a default tablespace named Undotbs, which has a 10MB data file that is automatically expanded. There is no maximum limit.

such as: Undo_tablespace=undotbs

L Default value: One available scatter table space

Parameter type: Dynamic, can be modified with alter system.

(3) Undo_retention

This parameter specifies that the amount of important information in the scatter table space, in seconds, can be preserved before being overwritten.

If the scatter data of a newer transaction enters, the old scatter data may be overwritten if there is not enough space in the scatter table space.

For long-running queries, you need to retain older scatter data for consistency purposes. If the data it needs is covered by a more new firm,

Then, at this time of the query, the database may appear with an error message (ORA-01555 snapshot to old snapshot is older).

Example: undo_retention=14400 (4 hours)

Default value: 900 seconds

Parameter type: Dynamic, can be modified using ALTER system

alter system set Undo_retention = 14400

Sql> Show Parameters Undo_retention

NAME TYPE VALUE

-----------------------------------------------------------------------------------------

Undo_retention integer 14400

This article is from the "Silent Dialogue" blog, please be sure to keep this source http://chbinmile.blog.51cto.com/6085145/1872649

Oracle Database 11g Some important initialization parameters (i)