oracle数据库要怎样配置,搭建oracle数据库后需要调整的设置

**************************

1.开启归档

**************************

archive log list;

alter system set log_archive_dest_1='location=S:\arch' scope=spfile;

shutdown immediate

startup mount

alter database archivelog ;

或关闭自动归档

alter database noarchivelog;

alter database open;

**************************

2.redo

**************************

--2.1 查看需要增加日志大小

/**

如果平均时间<5分钟，则需要增加日志大小

**/

col min_minutes format 999.99

col max_minutes format 999.99

col avg_minutes format 999.99

set pagesize 1000

set lines 70

set echo on

WITH log_history AS

(SELECT thread#, first_time,

LAG(first_time) OVER (ORDER BY thread#, sequence#)

last_first_time,

(first_time

- LAG(first_time) OVER (ORDER BY thread#, sequence#))

* 24* 60 last_log_time_minutes,

LAG(thread#) OVER (ORDER BY thread#, sequence#)

last_thread#

FROM v$log_history)

SELECT ROUND(MIN(last_log_time_minutes), 2) min_minutes,

ROUND(MAX(last_log_time_minutes), 2) max_minutes,

ROUND(AVG(last_log_time_minutes), 2) avg_minutes

FROM log_history

WHERE last_first_time IS NOT NULL

AND last_thread# = thread#

AND first_time > 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 <= (1 * 24) + 3),

v$database

WHERE log_mode = 'ARCHIVELOG')

SELECT hour_end_time, NVL(ROUND(SUM(size_mb), 3), 0) size_mb, i.instance_name

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 size_mb ELSE 0 END + CASE WHEN next_time IS NULL THEN(1 / 24) * LAST_VALUE(CASE WHEN next_time IS NOT NULL AND lag_next_time IS NULL THEN 0 ELSE(size_mb / (next_time - lag_next_time)) END IGNORE NULLS) OVER(

ORDER BY hour_end_time DESC, next_time DESC) ELSE 0 END size_mb

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, size_mb, LAG(next_time) OVER(

ORDER BY next_time) lag_next_time

FROM(

SELECT next_time, SUM(size_mb) size_mb

FROM(

SELECT DISTINCT a.sequence#, a.next_time, ROUND(a.blocks * a.block_size / 1024 / 1024) size_mb

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 <= TRUNC(SYSDATE, 'HH')

GROUP BY hour_end_time, i.instance_name

ORDER BY hour_end_time

/

2.3创建2个新的中间临时日志组

alter database add logfile group 4

('E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO04.LOG') size 200M;

alter database add logfile group 5

('E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO05.LOG') size 200M;

--查看日志使用情况

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中的文件

host del E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG

host del E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO001.LOG

host del E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG

host del E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO002.LOG

host del E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG

host del E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO003.LOG

2.7 重建日志组1、2、3

alter database add logfile group 1

('E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG',

'E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO001.LOG')

size 200M;

alter database add logfile group 2

('E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG',

'E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO002.LOG')

size 200M;

alter database add logfile group 3

('E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG',

'E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO003.LOG')

size 200M;

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;

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中的文件

host del E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO04.LOG

host del E:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO05.LOG

**************************

3.temp表空间组

**************************

create temporary tablespace temp01

tempfile 'S:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP001.DBF'

size 2000M;

create temporary tablespace temp02

tempfile 'S:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP002.DBF'

size 2000M;

create temporary tablespace temp03

tempfile 'S:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP003.DBF'

size 2000M;

select tablespace_name from dba_tablespaces where contents='TEMPORARY';

TABLESPACE_NAME

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

TEMP01

TEMP02

TEMP03

alter tablespace temp01 tablespace group temp;

alter tablespace temp02 tablespace group temp;

alter tablespace temp03 tablespace group temp;

alter database default temporary tablespace temp;

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME

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

TEMP_GROUP TEMP01

TEMP_GROUP TEMP02

TEMP_GROUP TEMP03

删除临时表空间组

alter tablespace tmp01 tablespace group '';

**************************

4.UNDO表空间

**************************

show parameters undo;

NAME TYPE VALUE

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

undo_management string AUTO

undo_retention integer 1800

undo_tablespace string UNDOTBS

---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 size/(db_block_size*undo_block_per_sec)

第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

create undo tablespace undotbs1

datafile 'E:\app\Administrator\oradata\orcl\UNDOTBS01.DBF' size 100M

reuse autoextend on next 100M MAXSIZE UNLIMITED;

Alter system set undo_tablespace=undotbs1;

Alter tablespace undotbs1 retention guarantee;

**************************

5.备份最新的控制文件

**************************

SQL> alter database backup controlfile to trace resetlogs;