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