Shulou(Shulou.com)06/01 Report--

Some important initialization parameters of Oracle database 11g (1)

Initialization 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 automatic Diagnostic Information Base (Automaticd Diagnostic Repository ADR).

Specify the directory location with parameters, such as $ORACLE_BASE/diag/rdbms/orcl/orcl. Where 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 you to set sysdate to return a constant date instead of the current date. Such as fixed_date=yyyymm-dd hh34:mi:ss

The default is none, such as fixed_date=none. These parameters can be set dynamically.

3. Parameters related to audit

(1) audit_trail

This parameter is used to turn on or off the audit function of the database. If not, 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 the audit record to an operating system file.

Db Oracle records the audit information in 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 audit information to be written to os files in the form of xml.

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

Such as audit_trail=db

There is no default value, this parameter is a static type.

(2) audit_file_dest

If audit_trail=os, or xml,xml,extended is set, this parameter is used to set the directory location of the audit information.

Such as: audit_file_dest=$ORACLE_BASE/admin/orcl/audit

Default value: $ORACLE_HOME/rdbms/audit

Parameter type: dynamic, which can be modified with alter system. Parameters.

(3) audit_sys_operations

Setting this parameter to true,Oracle will audit all activities of sys users or users with sysdba and sysoper roles and write audit information 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

Db_name is used to set the name of the database. This parameter is mandatory. The values of db_name and ORACLE_SID environment variables are the same. The maximum length of db_name is 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 or domain name of the database, which is generally the same as the name of the organization that owns the database.

Such as: db_domain=world

Default value: null

Parameter type: static

(3) instance_name

This parameter is used to specify the name of the instance, in a single instance, instance_name and db_name have the same value; in RAC, you can assign multiple instances to a single database service.

Such as: instance_name=orcl

Default value: instance SID

Parameter type: static

(3) service_name

This parameter provides a name for the database service, which is usually 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

This parameter is used 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 database 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 hh34:mi:ss

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

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

5. Parameters related to the file

(1) ifile

You can use ifile to embed additional 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 recommends that there are 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 Oracle retains records in the control file, and Oracle recommends that you set a higher value for this parameter to retain online disk backup information in all control files.

Such as: control_file_record_wkkp_time=31

Default value: 7

Parameter type: dynamic, which can be modified using alter system

(4) utl_file_dir

When using the utl_file package to read and write operating system files, you can use the utl_file_dir parameter to specify the IO directory that Oracle uses to process.

Such as: utl_file_dir=$ORACLE_BASE/utl_dir

Default value: none means not to use

Parameter type: static

6. Parameters related to managed files

If you decide to use the OMF (Oracle Managed File Oracle Management Files) feature, you need to define its format with two parameters, one of which is as follows:

(1) db_create_file_dest

This parameter specifies the default directory for administrative files.

Such as: db_create_file_dest=$ORACLE_BASE/admin/orcl/dbfile

Default value: none

Parameter type: dynamic, its format can be modified with alter system or session

(2) db_create_online_log_dest_n

This parameter specifies the default location of 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, which 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 write processes for the instance database

Such as: db_writer_processes=8

Default value: 1

Parameter type: static

(3) open_cursor

This parameter sets the limit on the number of cursors that can be opened for a single session at a given time.

Such as: open_cursor=300

Default value: 50

Parameter type: dynamic, which can be modified with alter system

8. Memory configuration parameters

(1) memory_target

Use this parameter memory_target to specify the memory allocated to the Oracle when using automatic memory management to allocate intrinsic to the 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 the Oracle instance.

Such as: memory_max_target=2G

Default value: 0

Parameter type: static

If memory_target is specified and memory_max_target is ignored, the value of memoery_max_target is the same as that of memoery_target.

(3) db_cache_size

This parameter sets the default cache pool size for caches that have a base block size, which is defined by db_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 by alter system

(4) db_kepp_cache_size

The buffer pool usually does the same for the objects placed in it, and any object will remain in the pool as long as the buffer cache has available space. When the recording pool is full, the oldest object will be deleted to make room for the new object.

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

This parameter is used to set the size of the save pool

Such as: db_keep_cache_size=500M

Default value: 0, which means that this parameter is not configured

Parameter type: dynamic, can be modified by alter system

(5) db_recycle_cache_size

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

Such as: db_recycle_cache_size=200M

Default value: 0, which means that this parameter is not configured

Parameter type: dynamic

(6) db_nk_cache_size

This parameter is used to specify a buffer cache of a non-standard size. N can use 2pr 4pr 8pr 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 for the OCI client query to make another round trip. In 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 memory allocated to the client by adjusting the buffer for each process result set.

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 hypervisor packages for the server.

Diagnostic packages, such as awr,addm, etc.

Tuning packages: e. G. SQL Tuning Advisor,SQl Access Advisor, etc.

Such as: control_management_pack_access=AWR

Default value: diagnostic+tuning

Parameter type: dynamic, which can be modified with alter system

(10) large_pool_size

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

Such as: large_pool_size=1G

Default value: 0

Parameter type: dynamic, which can be modified using alter system

9. Archive log parameters

(1) log_archive_dest_n

Among them, there are at most 10 positions of nasty 1, 2 and 3. This parameter is used to specify the location of the archive log. The database uses this parameter only 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

This parameter is used to specify the default format of the archive redo log name. The format parameters are as follows:

T represents thread number, s represents log sequence number r represents redo log ID

Such as: log_archive_format='log%t_%s_%r.arc'

Default value: depending on the operating system

Parameter type: static

10. Selling space parameters

(1) undo_management

If undo_management is set to auto, the release space is used to store the lock record. Oracle will automatically manage the unlocking segment.

Such as: undo_management=auot

Default value: auto

Parameter type: static

(2) undo_tablespace

This parameter specifies the default tablespace for selling records, and if there is no selling tablespace, Oracle will use the system fallback segment for selling storage. If you do not specify this parameter when you create the database, and you select AUM (automatic unod management), Oracle creates a default tablespace called undotbs, which has a data file for 10MB, which automatically extends without maximum limits.

Such as: undo_tablespace=undotbs

L default: an available resale tablespace

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

(3) undo_retention

This parameter specifies that the important amount of information in the selling tablespace can be retained before it is overwritten, in seconds.

If the sale data of newer transactions is entered, if there is not enough space in the sale table space, the old sale data may be overwritten.

For long-running queries, older sales data need to be retained for consistency purposes. If the data it needs is overwritten by newer transactions

So, when you make such a query at this time, the database may have an error message (the ORA-01555 snapshot to old snapshot is too old).

Such as: undo_retention=14400 (4 hours)

Default value: 900 seconds

Parameter type: dynamic, which can be modified using alter system

Alter system set undo_retention = 14400

SQL > show parameters undo_retention

NAME TYPE VALUE

-

Undo_retention integer 14400

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.