Oracle基础参数配置和调整

说明：

开头带有“SQL>”标识的，表示是一个sql语句。
简单介绍SGA参数的调整、表空间的调整。

调整sga_max_size、processes、sessions、transactions大小。

下面显示oracle默认安装完成后的部分参数。

SQL> select * from v$parameter t where t.name in ('sga_max_size','sga_target','memory_max_target','memory_target','pga_aggregate_target', 'db_block_buffers','db_block_size','shared_pool_size','log_buffer','_pool_size','large_pool_size','processes','sessions','transactions','workarea_size_policy');

以上参数，value=0的为oracle自动调整，无需修改；实际需要调整的就是sga_max_size、memory_target、processes、sessions、transactions。

a) sga_max_size：调整成的40%-50%；memory_target值= sga_max_size值（命令行执行）

SQL> alter system set sga_max_size=900M scope=spfile;

b) processes默认150，可以先调整成1000个，后续可以根据业务请求量调整。

SQL> alter system set processes=1000 scope=spfile;

c) sessions值根据sessions=(1.5*processes)+22调整。

SQL> alter system set sessions=1522 scope=spfile;

d) transactions 值根据transactions＝1.1*sessions调整。

SQL> alter system set transactions=1675 scope=spfile;

修改完后，重启数据库，检查参数。

打开归档模式。

检查，修改为归档模式，并设置参数。

SQL>archive log list

SQL>shutdown immediate

SQL>startup mount

SQL>alter database archivelog

SQL> alter system set log_archive_dest_1='location=/data/oradata/arch';

SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;

SQL> shutdown immediate;

SQL>startup;

测试

打开闪回。

确保数据库运行在归档模式下

参数设置

SQL>alter system set db_recovery_file_dest_size=2G;

SQL> alter system set db_recovery_file_dest='/opt//oracle/fast_recovery_area';

SQL>alter system set db_flashback_retention_target=10080;

SQL>alter system set undo_retention=10800;

SQL>shutdown immediate;

SQL>startup mount

SQL>alter database flashback on;

SQL>alter database open;

注意：归档日志目录，默认是放在闪回区的；建议换到另外的目录（log_archive_dest_1）。否则，有可能因为归档日志清理不及时，闪回区的大小（db_recovery_file_dest_size）用光，导致归档日志无法写入，从而数据库hang住。

新建业务用户表空间、业务用户，调整表空间。

新建业务表空间（建2个：分别用于存业务表和表索引；生成环境文件大小30G，本文仅为演示）

SQL> create tablespace BUSIDATA datafile '/data/oradata/orcl/BUSIDATA01.dbf' size 1G autoextend on;

SQL> create tablespace BUSIIDX datafile '/data/oradata/orcl/BUSIIDX01.dbf' size 200M autoextend on;

对于并发量大，SQL里排序、分组又多的情况，可以建临时表空间组(非必须）

SQL>create temporary tablespace tmp01 tempfile '/data/oradata/orcl/tmp01.dbf' size 50M autoextend on;

SQL>create temporary tablespace tmp02 tempfile '/data/oradata/orcl/tmp02.dbf' size 50M autoextend on;

SQL>create temporary tablespace tmp03 tempfile '/data/oradata/orcl/tmp03.dbf' size 50M autoextend on;

SQL>alter tablespace tmp01 tablespace group tempgrp;

SQL>alter tablespace tmp02 tablespace group tempgrp;

SQL>alter tablespace tmp03 tablespace group tempgrp;

SQL>alter database default temporary tablespace tempgrp;

新建业务用户BUSI，赋权限。

SQL>create user busi identified by "busi123" default tablespace BUSIDATA temporary tablespace tempgrp;

注意：建索引的时候，需要明确指定tablespace为BUSIIDX。
更换用户表的表空间：由于建表语句不规范，经常会存在这样的情况：用户所属表的存储表空间不一样，有的表存储在users甚至是system表空间下。这时需要调整表的存储表空间。需要注意的是，移动表空间时会产生锁，而且表上的索引有可能失效，因此操作时务必避开业务高峰。

以下sql中带“<>”根据实际情况改写：

SQL>alter table <tablename> move tablespace <newtbs>;

SQL>select * from user_indexes t where t.table_name=' <tablename> ' and t.status<>'VALID';

SQL>alter index <idx_name> rebuild tablespace <new_tab_space_name>;