oracle数据库新环境常见配置

jiaxp

**************************　　
1.开启归档
　　
**************************
　　
archive log list;
　　
alter system set log_archive_dest_1='location=/u01/app/oracle/arch' scope=spfile;
　　
shutdown immediate
　　
startup mount
　　
alter database  archivelog ;
　　
或关闭自动归档
　　
alter database noarchivelog;
　　
alter database open;
　　
**************************
　　
2.redo
　　
**************************
　　
--2.1 查看需要增加日志大小
　　
/**
　　
如果平均时间 SYSDATE - 1;
　　
--2.2 列出Oracle每小时的redo重做日志产生量
　　
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI:SS';
　　
WITH times AS
　　(SELECT /*+ MATERIALIZE */
　　hour_end_time
　　FROM (SELECT (TRUNC(SYSDATE, 'HH') + (2 / 24)) - (ROWNUM / 24) hour_end_time
　　FROM DUAL
　　CONNECT BY ROWNUM 　　FROM(

　　
SELECT hour_end_time, CASE WHEN(hour_end_time - (1 / 24)) > lag_next_time THEN(next_time + (1 / 24) - hour_end_time) * (size_mb / (next_time - lag_next_time)) ELSE 0 END + CASE WHEN hour_end_time < lead_next_time THEN(hour_end_time - next_time) * (lead_size_mb / (lead_next_time - next_time)) ELSE 0 END + CASE WHEN lag_next_time > (hour_end_time - (1 / 24)) THEN>
　　ORDER BY hour_end_time DESC, next_time DESC) ELSE 0 END>　　FROM(
　　
SELECT t.hour_end_time, arc.next_time, arc.lag_next_time, LEAD(arc.next_time) OVER(
　　ORDER BY arc.next_time ASC) lead_next_time, arc.size_mb, LEAD(arc.size_mb) OVER(
　　ORDER BY arc.next_time ASC) lead_size_mb
　　FROM times t,(

　　
SELECT next_time,>　　ORDER BY next_time) lag_next_time
　　FROM(

　　
SELECT next_time, SUM(size_mb)>　　FROM(

　　
SELECT DISTINCT a.sequence#, a.next_time, ROUND(a.blocks * a.block_size / 1024 / 1024)>　　FROM v$archived_log a,(
　　
SELECT /*+ no_merge */
　　
CASE WHEN TO_NUMBER(pt.VALUE) = 0 THEN 1 ELSE TO_NUMBER(pt.VALUE) END VALUE
　　FROM v$parameter pt
　　WHERE pt.name = 'thread') pt
　　WHERE a.next_time > SYSDATE - 3 AND a.thread# = pt.VALUE AND ROUND(a.blocks * a.block_size / 1024 / 1024) > 0)
　　GROUP BY next_time)) arc
　　WHERE t.hour_end_time = (TRUNC(arc.next_time(+), 'HH') + (1 / 24)))
　　WHERE hour_end_time > TRUNC(SYSDATE, 'HH') - 1 - (1 / 24)), v$instance i
　　WHERE hour_end_time 　　
alter database add logfile group 5

　　
('/u01/app/oracle/oradata/orcl/redo05.log')>　　
--查看日志使用情况
　　
SQL> select group#,status,archived from v$log;
　　GROUP# STATUS           ARC
　　
---------- ---------------- ---
　　1 CURRENT          NO
　　2 INACTIVE         NO
　　3 INACTIVE         NO
　　4 UNUSED           YES
　　5 UNUSED           YES
　　
2.4 切换当前日志到新的日志组,切换current日志到添加的日志组
　　
alter system switch logfile;
　　
alter system switch logfile;
　　
alter system checkpoint;
　　
SQL> select group#,status,archived from v$log;
　　GROUP# STATUS           ARC
　　
---------- ---------------- ---
　　1 INACTIVE         YES
　　2 INACTIVE         YES
　　3 INACTIVE         YES
　　4 INACTIVE         YES
　　5 CURRENT          NO
　　
####如果你想删除哪个日志就要把他的状态切换为：inactive
　　
2.5 删除旧的日志组
　　
alter database drop logfile group 1;
　　
alter database drop logfile group 2;
　　
alter database drop logfile group 3;
　　
2.6 操作系统下删除原日志组1、2、3中的文件
　　
rm -f /u01/app/oracle/oradata/orcl/redo01.log
　　
rm  -f /u01/app/oracle/oradata/orcl/redo02.log
　　
rm -f  /u01/app/oracle/oradata/orcl/redo03.log
　　
2.7 重建日志组1、2、3
　　
alter database add logfile group 1
　　
('/u01/app/oracle/oradata/orcl/redo01.log',
　　
'/u01/app/oracle/oradata/orcl/redo001.log')
　　
size 1000m;
　　
alter database add logfile group 2
　　
('/u01/app/oracle/oradata/orcl/redo02.log',
　　
'/u01/app/oracle/oradata/orcl/redo002.log')
　　
size 1000m;
　　
alter database add logfile group 3
　　
('/u01/app/oracle/oradata/orcl/redo03.log',
　　
'/u01/app/oracle/oradata/orcl/redo003.log')
　　
size 1000m;
　　
2.8 切换日志组
　　
####你想删除哪个日志就要把他的状态切换为：inactive
　　
select group#,status,archived from v$log;
　　
SQL> select group#,status,archived from v$log;
　　GROUP# STATUS           ARC
　　
---------- ---------------- ---
　　1 UNUSED           YES
　　2 UNUSED           YES
　　3 UNUSED           YES
　　4 INACTIVE         YES
　　5 CURRENT          NO
　　
--切换日志,
　　
alter system switch logfile;
　　
--切换到1-3日志组中任意一个current，其它都是active执行checkpoint即可
　　
alter system checkpoint;
　　
SQL> select group#,status,archived from v$log;
　　GROUP# STATUS           ARC
　　
---------- ---------------- ---
　　1 CURRENT          NO
　　2 INACTIVE         YES
　　3 INACTIVE         YES
　　4 INACTIVE         YES
　　5 INACTIVE         YES
　　
2.9 删除中间过渡用的日志组4、5
　　
alter database drop logfile group 4;
　　
alter database drop logfile group 5;
　　
2.10 到操作系统下删除原日志组4、5中的文件
　　
rm -f /u01/app/oracle/oradata/orcl/redo04.log
　　
rm -f  /u01/app/oracle/oradata/orcl/redo05.log
　　
**************************
　　
3.temp表空间组
　　
**************************
　　
create temporary tablespace temp01
　　
tempfile '/u01/app/oracle/oradata/orcl/TEMP001.DBF'
　　
size 2000M;
　　
create temporary tablespace temp02
　　
tempfile '/u01/app/oracle/oradata/orcl/TEMP002.DBF'
　　
size 2000M;
　　
create temporary tablespace temp03
　　
tempfile '/u01/app/oracle/oradata/orcl/TEMP003.DBF'
　　
size 2000M;
　　
create temporary tablespace tempex
　　
tempfile '/u01/app/oracle/oradata/orcl/tempex.DBF'
　　
size 2000M;
　　
select tablespace_name from dba_tablespaces where contents='TEMPORARY';
　　
TABLESPACE_NAME
　　
------------------------------
　　
TEMP
　　
TEMP01
　　
TEMP02
　　
TEMP03
　　
TEMPEX
　　
---设置过度临时表空间
　　
alter database default temporary tablespace tempex;
　　
--删除临时表空间
　　
drop tablespace temp including contents and datafiles;
　　
select tablespace_name from dba_tablespaces where contents='TEMPORARY';
　　
TABLESPACE_NAME
　　
------------------------------
　　
TEMP01
　　
TEMP02
　　
TEMP03
　　
TEMPEX
　　
----将不同的表空间加入到相同的文件组
　　
alter tablespace temp01 tablespace group temp;
　　
alter tablespace temp02 tablespace group temp;
　　
alter tablespace temp03 tablespace group temp;
　　
SQL> select * from dba_tablespace_groups;
　　
GROUP_NAME             TABLESPACE_NAME
　　
------------------------------ ------------------------------
　　
TEMP                   TEMP01
　　
TEMP                   TEMP02
　　
TEMP                   TEMP03
　　
---设置默认表空间
　　
alter database default temporary tablespace temp;
　　
删除临时表空间组
　　
alter tablespace tmp01 tablespace group '';
　　
--删除过度临时表空间
　　
drop tablespace tempex including contents and datafiles;
　　
**************************
　　
4.UNDO表空间
　　
**************************
　　
show parameters undo;
　　
NAME                                 TYPE        VALUE
　　
------------------------------------ ----------- ----------------
　　
undo_management                      string      AUTO
　　
undo_retention                       integer     1800
　　
undo_tablespace                      string      UNDOTBS
　　
select tablespace_name from dba_tablespaceswhere contents='UNDO';
　　
---1.确定UNDO表空间大小
　　
/****
　　
1.计算业务高峰期每秒产生undo数据块的个数：
　　
SQL> select max(undoblks / ((end_time - begin_time)*24*3600))  from v$undostat;
　　
2.得到undo数据块在undo表空间中可以保留的最长时间
　　
show parameter undo_retention
　　
3.得到数据块大小
　　
show parameter db_block;
　　
4.将以上三者的数据相乘就是所需undo表空间的大小数。
　　
select begin_time,end_time,undoblks
　　
from v$undostat;
　　
********/
　　
----2.确定最优的撤销保留时间
　　
/****
　　
如果需要使用undo_retention的前提：
　　
1.撤销表空间设置AUTOEXTEND选项
　　
2.如果得到ORA-0155快照太久的错误，说明oracle闪回操作，意味数据库没有能有效的
　　
动态的调整撤销保留时间.这种情况下可以增大undo_retention的时间值.
　　
alter system set undo_retention=1800 scope=both;
　　
通过下面的公式计算：

　　
undo_retention=undo>　　
第1步：计算数据库中实际产生的撤销数据：
　　
select sum(a.bytes) "UNDO"
　　
from v$datafile a,
　　
v$tablespace b,
　　
dba_tablespaces c
　　
where c.contents='UNDO'
　　
and c.status='ONLINE'
　　
and b.name=c.tablespace_name
　　
and a.ts#=b.ts#;
　　
--
　　
335544320
　　
第2步：计算undo_block_per_sec
　　
select ceil(max(undoblks/((end_time-begin_time)*3600*24))) "UNDO_BLOCK_PER_SEC"
　　
from v$undostat;
　　
--
　　
8
　　
第3步：查看db_block_size
　　
show parameters db_block_size;
　　
--8192
　　
第4步：计算出undo_retention
　　
undo_retention=335544320/(8*8192)=5120
　　结果为目前undo的大小可以保留5120秒.我们设置的undo_retention=1800秒，其实可以设置的时间更长.
　　
---自动优化撤销保留时间
　　
select to_char(begin_time,'hh24:mi:ss') begin_time,
　　
to_char(end_time,'hh24:mi:ss') end_time,
　　
maxquerylen,nospaceerrcnt,tuned_undoretention  --maxquerylen最长的查询
　　
from v$undostat;
　　
---查询撤销数据块的使用率，以及每十分钟间隔内的事务计数
　　
select to_char(begin_time,'hh24:mi:ss'),to_char(end_time,'hh24:mi"ss'),
　　
maxquerylen,ssolderrcnt,nospaceerrcnt,undoblks,txncount from v$undostat
　　
order by undoblks
　　
;
　　
***/
　　
---3.设置undo
　　
select tablespace_name from dba_tablespaces where contents='UNDO';
　　
create undo tablespace undotbs2

　　
datafile '/u01/app/oracle/oradata/orclUNDOTBS02.DBF'>　　
reuse autoextend on next 100M MAXSIZE UNLIMITED;
　　
Alter system set undo_tablespace=undotbs2;
　　
drop tablespace undotbs1 including contents and datafiles;
　　
create undo tablespace undotbs1

　　
datafile '/u01/app/oracle/oradata/orclUNDOTBS01.DBF'>　　
reuse autoextend on next 100M MAXSIZE UNLIMITED;
　　
Alter system set undo_tablespace=undotbs1;
　　
Alter tablespace undotbs1 retention guarantee;
　　
drop tablespace undotbs2 including contents and datafiles;
　　
**************************
　　
5.修改多路控制文件
　　
**************************
　　
select name from v$controlfile;
　　
create pfile='/u01/app/oracle/initorcl.ora' from spfile;
　　
shutdown immediate
　　
修改/u01/app/oracle/initorcl.ora:
　　
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl',
　　
'/u01/app/oracle/oradata/orcl/control02.ctl'',
　　
'/u01/app/oracle/oradata/orcl/control03.ctl''
　　
cp /u01/app/oracle/oradata/orcl/control01.ctl   /u01/app/oracle/oradata/orcl/control02.ctl
　　
cp /u01/app/oracle/oradata/orcl/control01.ctl   /u01/app/oracle/oradata/orcl/control03.ctl
　　
根据需要，复制控制文件名到相应路径并修改名
　　
create spfile from pfile='/u01/app/oracle/initorcl.ora';
　　
startup
　　
select name from v$controlfile;
　　
**************************
　　
6.全局配置文件的设置
　　
**************************
　　
vi   /u01/app/oracle/product/11.2.0/db_1/sqlplus/admin/glogin.sql
　　
或E:\app\Administrator\product\11.2.0\dbhome_1\sqlplus\admin\glogin.sql
　　
--dba add for oracle
　　
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
　　
define _editor=vi

　　
set serveroutput on>　　
set trimspool on
　　
set long 5000
　　
set linesize 100
　　
set pagesize 9999
　　
column plan_plus_exp format a80
　　
column global_name new_value gname
　　
set termout off
　　
define gname=idle
　　
column global_name new_value gname
　　
select lower(user) || '@' || substr( global_name, 1,
　　
decode( dot, 0, length(global_name), dot-1) ) global_name
　　
from (select global_name, instr(global_name,'.') dot from global_name );
　　
set time on
　　
set sqlprompt '&gname>'
　　
**************************
　　
7.防止密码过期
　　
**************************
　　
SELECT * FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
　　
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED ;
　　
**************************
　　
8.rman修改控制文件保存时间
　　
**************************
　　
select name,value from v$parameter where name='control_file_record_keep_time';
　　
alter system set control_file_record_keep_time=31;
　　
show parameter control_file_record_keep_time
　　
**************************
　　
9.process设置
　　
**************************
　　
show parameter  dispatchers
　　
show parameter processes
　　
alter system set DISPATCHERS = '(protocol=tcp)(dispatchers=3)';
　　
alter system set processes=500 scope=spfile;