安装完数据库软件的下一步就是创建数据库,如果是手工创建数据库或对数据库所占用的内存进行一些合理化配置,就需要理解必备的建库初始化参数。
今天在手工建库过程中,发现$ORACLE_HOME/dbs/下的init.ora和initdw.ora两个文件内容也是值得读一遍。因此产生了Oracle建库必备的核心初始化参数有哪些的想法,总结如下:
1、Oracle 9i 环境
以1G物理内存为例,给OS留50%,其余用于Oracle。在Oracle9i提出了pga_agregate_target参数。
db_name = MY_DB_NAME
control_files = (ora_control1, ora_control2)
shared_pool_size = 50M
pga_aggregate_target= 200M
db_cache_size = 200M
undo_management = auto
undo_tablespace =undotbs1[@more@]
当然还有其他的参数,initdw.ora文件中介绍的也是非常详细,在此只是列出必备的。
# Memory is managed
globally. The DBA should first determine how much
# memory is
available for Oracle to use. Then, the DBA should choose
# memory parameters
so that pga_aggregate_size + db_cache_size +
# shared_pool_size +
large_pool_size is roughly equal to the amount
# of memory
available for the Oracle database.
#
# For example,
suppose that a DBA is managing a small data mart. The
# data mart server
has 1GB of physical memory. The DBA has determined
# that 500M of
memory will be used by the operating system and other
# applications, so
that 500M is available for Oracle.
#
# The DBA may choose
the following settings:
#shared_pool_size = 50M
#pga_aggregate_size = 200M
#db_cache_size = 200M
#large_pool_size =
#
# The total memory
utilization is 450M plus a system-determined value
# for the large
pool.
# pga_aggregate_target should, in general, be
equal to 20-80% of the
# available memory,
depending on the workload.
# db_cache_size
should, in general, be equal to 20-80% of the
# available memory,
depending on the workload.
# Shared pool size
should be, in general, equal to 5-10% of the
# available memory.
2、Oracle
10g 环境
在Oracle10g,提出sga_max_size和sga_target参数
db_name = MY_DB_NAME
control_files =
(ora_control1, ora_control2)
pga_aggregate_target= 200M
sga_target=
250M
undo_management
= auto
undo_tablespace
=undotbs1
3、Oracle 11g
环境
类似SGA_MAX_SIZE与SGA_TARGET一样,MEMORY_MAX_TARGET与MEMORY_TARGET成对出现,这一特性的出现使得PGA+SGA作为整体内存使用被一致的纳入自动管理范畴。。
在Oracle10g,提出sga_max_size和sga_target参数
db_name = MY_DB_NAME
control_files =
(ora_control1, ora_control2)
memory_targe=450M
MEMORY_MAX_TARGET=450M
undo_management
= auto
undo_tablespace
=undotbs1
与Oracle10g类似,设置了自动内存管理之后,PGA_AGGREGATE_TARGET参数不再需要设置,取而代之发挥作用的参数是带有2个下划线的__PGA_AGGREGATE_TARGET参数