**************************
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;