Oracle redo log

-- 查看
select group#,sequence#,status,archived,bytes/1024/1024 as sizes_mb from v$log;

-- Creating Redo Log Groups and Members
ALTER DATABASE ADD LOGFILE ('/oracle/dbs/log1a.rdo', '/oracle/dbs/log1b.rdo') SIZE 500K;
ALTER DATABASE  ADD LOGFILE GROUP 10 ('/oracle/dbs/log1a.rdo', '/oracle/dbs/log1b.rdo')  SIZE 500K;

ALTER DATABASE ADD LOGFILE MEMBER '/oracle/dbs/log2b.rdo' TO GROUP 2;
ALTER DATABASE ADD LOGFILE group 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 100M reuse; 

-- Renaming Redo Log Members,

SHUTDOWN

mv /oracle/dbs/log1a.rdo /oracle/dbs/log1c.rdo
mv /oracle/dbs/log1b.rdo /oracle/dbs/log1d.rdo

CONNECT / as SYSDBA
STARTUP MOUNT

ALTER DATABASE  RENAME FILE '/oracle/dbs/log1a.rdo', '/oracle/dbs/log1b.rdo' 
           TO '/oracle/dbs/log1c.rdo', '/oracle/dbs/log2d.rdo';

ALTER DATABASE OPEN;


-- Dropping Redo Log Groups and Members

ALTER DATABASE DROP LOGFILE GROUP 3;

ALTER DATABASE DROP LOGFILE MEMBER '/oracle/dbs/log1c.rdo';


-- Forcing Log Switches,
/*
You can force a log switch to make the currently active group inactive and available for redo log maintenance operations. 
For example, you want to drop the currently active group, but are not able to do so until the group is inactive. 
You may also wish to force a log switch if the currently active group needs to be archived at a specific time 
before the members of the group are completely filled. This option is useful in configurations with large redo log 
files that take a long time to fill.
*/
ALTER SYSTEM SWITCH LOGFILE;
-- 对单实例数据库或RAC中的当前实例执行强制日志切换，归档当前重做日志，并且只是用switch前的current日志的first_change#,
-- 更新了v$datafile 的小于它的检查点checkpoint_change#，不更新v$datafile_header的checkpoint_change#

-- 区别于
ALTER SYSTEM ARCHIVE LOG ALL;
-- 对数据库中的非当前未归档日志进行归档，不负责归档current日志

ALTER SYSTEM ARCHIVE LOG CURRENT;
-- 对数据库中的所有实例执行日志切换(只归档当前日志)。

ALTER SYSTEM CHECKPOINT;
<pre name="code" class="sql">-- 将data buffer中的脏块(已经写在redo里记录但是没有写到datafile里的)的内容写入到data file里并释放占用的空间,
-- 由dbw后台进程完成,并修改controlfile和datafile的scn，它做了完全检查点，v$datafile_header，控制文件，数据文件

-- Clearing a Redo Log FileALTER DATABASE CLEAR LOGFILE GROUP 3;ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;-- Viewing Redo Log Information V$LOG,V$LOGFILE,V$LOG_HISTORY;

-- 查看切换时间

-- 查看日志切换时间
select b.SEQUENCE#,b.FIRST_TIME,a.SEQUENCE#,round(((a.FIRST_TIME - b.FIRST_TIME) * 24) * 60, 2)
from v$log_history a, v$log_history b
where a.SEQUENCE# = b.SEQUENCE# + 1
and b.THREAD# = 1
order by a.SEQUENCE# desc, b.FIRST_TIME desc;

select sequence#,
       first_time,
       nexttime,
       round(((first_time - nexttime) * 24) * 60, 2) diff
  from (select sequence#,
               first_time,
               lag(first_time) over(order by sequence#) nexttime
          from v$log_history
         where thread# = 1)
 order by sequence# desc, first_time desc;

-- 归档时间，大小
select
       t.SEQUENCE#,
       t.BLOCKS * t.BLOCK_SIZE / 1024 / 1024 as s,
       t.FIRST_TIME,
       round((t.NEXT_TIME - t.FIRST_TIME) * 24 * 60, 2) diff
  from v$archived_log t
 where t.DELETED = 'NO'
 order by t.FIRST_TIME desc;

-- 查看闪回使用情况

select * From v$flash_recovery_area_usage;
select t.name, round(t.SPACE_USED / t.SPACE_LIMIT, 2) as space_used
  from v$recovery_file_dest t;

参考：http://docs.oracle.com/cd/B19306_01/server.102/b14231/onlineredo.htm