************************** 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;
转载于:https://blog.51cto.com/ocpyang/1219487