重做日志文件具有以下特征：
1.记录对数据所做的所有更改
2.提供恢复机制（数据写入数据文件前都被重做日志记录了）
3.可以划分成组
4.至少需要两个组
=====================================================================
最好一个重做日志文件组每个成员应该放在不同物理磁盘，提高冗余性，多路复用
重做日志文件如何工作？
? 重做日志文件是以循环方式使用的。
? 一旦某个重做日志文件被写满，LGWR 就会移动到
下一个日志组。
–这称为日志切换（log switch）
–同时还将执行检查点操作
–将信息写入控制文件
log swith:一组写满切换到另一组的过程，引发checkpoint
checkpoint:确定修改的信息写入重做日志后，真正把数据写入数据文件的操作过程（DBWn）
先写重做日志文件，再写入数据文件中
什么时候LGWR写重做日志文件
1.当一个修改commit时候
2.每3S
3.当redolog buffer满1/3的时候
4.当有超过1M要写入redolog buffer
5.一个checkpoint执行前
===============================================================================
手动进行重做日志切换：
SQL> select group#,thread#,sequence#,status from v$log;
GROUP# THREAD# SEQUENCE# STATUS
---------- ---------- ---------- ----------------
1 1 7 INACTIVE
2 1 8 INACTIVE
3 1 9 CURRENT
SQL> alter system switch logfile;
System altered.
SQL> select group#,thread#,sequence#,status from v$log;
GROUP# THREAD# SEQUENCE# STATUS
---------- ---------- ---------- ----------------
1 1 10 CURRENT
2 1 8 INACTIVE
3 1 9 ACTIVE
SQL> select group#,thread#,sequence#,status from v$log;
GROUP# THREAD# SEQUENCE# STATUS
---------- ---------- ---------- ----------------
1 1 10 CURRENT
2 1 8 INACTIVE
3 1 9 ACTIVE
SQL> alter system switch logfile;
System altered.
SQL> select group#,thread#,sequence#,status from v$log;
GROUP# THREAD# SEQUENCE# STATUS
---------- ---------- ---------- ----------------
1 1 10 ACTIVE
2 1 11 CURRENT
3 1 9 ACTIVE
================================================================================
强制执行checkpoint:
方法一：
SQL> show parameter fast
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_io_target integer 0
fast_start_mttr_target integer 0
fast_start_parallel_rollback string LOW
SQL> alter system set fast_start_mttr_target=600 scope=both;
System altered.
SQL> show parameter fast
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_io_target integer 0
fast_start_mttr_target integer 600
fast_start_parallel_rollback string LOW
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 633343360 bytes
Database Buffers 192937984 bytes
Redo Buffers 6590464 bytes
Database mounted.
Database opened.
SQL> show parameter fast
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_io_target integer 0
fast_start_mttr_target integer 600
fast_start_parallel_rollback string LOW
方法二：
SQL> alter system checkpoint;
System altered.
查看重做日志文件信息有三个重要视图
v$log:从控制文件中展示重做日志文件信息
v$logfile：可差可能重做日志组成员的状态信息
V$log_history
======================================================
新增重做日志文件
SQL> select group#,status,type,member from v$logfile; #查看当前总共有几组重做日志组及其成员
GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
1 ONLINE /database/msp/redo01a.rdo
1 ONLINE /database/msp/redo01b.rdo
2 ONLINE /database/msp/redo02a.rdo
2 ONLINE /database/msp/redo02b.rdo
3 ONLINE /database/msp/redo03a.rdo
3 ONLINE /database/msp/redo03b.rdo
6 rows selected.
SQL> select group#,thread#,sequence#,status from v$log; #查看当前重做日志组使用状态
GROUP# THREAD# SEQUENCE# STATUS
---------- ---------- ---------- ----------------
1 1 10 INACTIVE
2 1 11 CURRENT
3 1 9 INACTIVE
SQL> alter database add logfile group 4 #新增重做日志租
2 ('/database/msp/redo04a.rdo')
3 size 100M;
Database altered.
SQL> select group#,status,type,member from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
1 ONLINE /database/msp/redo01a.rdo
1 ONLINE /database/msp/redo01b.rdo
2 ONLINE /database/msp/redo02a.rdo
2 ONLINE /database/msp/redo02b.rdo
3 ONLINE /database/msp/redo03a.rdo
3 ONLINE /database/msp/redo03b.rdo
4 ONLINE /database/msp/redo04a.rdo
7 rows selected.
SQL> select group#,thread#,sequence#,status from v$log;
GROUP# THREAD# SEQUENCE# STATUS
---------- ---------- ---------- ----------------
1 1 10 INACTIVE
2 1 11 CURRENT
3 1 9 INACTIVE
4 1 0 UNUSED
SQL> !
[oracle@server01 ~]$ cd /database/msp/
[oracle@server01 msp]$ ll
total 1672132
-rw-r----- 1 oracle oinstall 16433152 Mar 24 23:29 control01.ctl
-rw-r----- 1 oracle oinstall 16433152 Mar 24 23:29 control03.ctl
-rw-r----- 1 oracle oinstall 104865792 Mar 24 23:08 mspmytbs.dbf
-rw-r----- 1 oracle oinstall 104858112 Mar 24 23:08 redo01a.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 24 23:08 redo01b.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 24 23:29 redo02a.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 24 23:29 redo02b.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 24 23:08 redo03a.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 24 23:08 redo03b.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 24 23:29 redo04a.rdo
-rw-r----- 1 oracle oinstall 314580992 Mar 24 23:23 sysaux01.dbf
-rw-r----- 1 oracle oinstall 314580992 Mar 24 23:28 system01.dbf
-rw-r----- 1 oracle oinstall 104865792 Mar 24 22:00 temp01.dbf
-rw-r----- 1 oracle oinstall 209723392 Mar 24 23:28 undotbs01.dbf
[oracle@server01 msp]$ exit
exit
SQL> alter database add logfile member #新增重做日志组成员
2 '/database/msp/redo04b.rdo' to group 4;
Database altered.
SQL> select group#,thread#,sequence#,status from v$log;
GROUP# THREAD# SEQUENCE# STATUS
---------- ---------- ---------- ----------------
1 1 10 INACTIVE
2 1 11 CURRENT
3 1 9 INACTIVE
4 1 0 UNUSED
SQL> select group#,status,type,member from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
1 ONLINE /database/msp/redo01a.rdo
1 ONLINE /database/msp/redo01b.rdo
2 ONLINE /database/msp/redo02a.rdo
2 ONLINE /database/msp/redo02b.rdo
3 ONLINE /database/msp/redo03a.rdo
3 ONLINE /database/msp/redo03b.rdo
4 ONLINE /database/msp/redo04a.rdo
4 INVALID ONLINE /database/msp/redo04b.rdo #INVALID状态是由于当前的的日志组并非active日志组，可以手工进行切换重做日志组，就不会是该状态了
8 rows selected.
SQL> !
[oracle@server01 ~]$ cd /database/msp
[oracle@server01 msp]$ ll
total 1774536
-rw-r----- 1 oracle oinstall 16433152 Mar 24 23:34 control01.ctl
-rw-r----- 1 oracle oinstall 16433152 Mar 24 23:34 control03.ctl
-rw-r----- 1 oracle oinstall 104865792 Mar 24 23:08 mspmytbs.dbf
-rw-r----- 1 oracle oinstall 104858112 Mar 24 23:08 redo01a.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 24 23:08 redo01b.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 24 23:34 redo02a.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 24 23:34 redo02b.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 24 23:08 redo03a.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 24 23:08 redo03b.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 24 23:29 redo04a.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 24 23:31 redo04b.rdo
-rw-r----- 1 oracle oinstall 314580992 Mar 24 23:33 sysaux01.dbf
-rw-r----- 1 oracle oinstall 314580992 Mar 24 23:28 system01.dbf
-rw-r----- 1 oracle oinstall 104865792 Mar 24 22:00 temp01.dbf
-rw-r----- 1 oracle oinstall 209723392 Mar 24 23:34 undotbs01.dbf
================================================================
删除组成员：
1.可以删除重做日志组成员，但是最好保持组的对称，成员只有在不被使用非active的情况下才能被删除掉，如果非要删除该成员，需要手工进行日志组切换后才能进行删除操作。
2.确保删除重做日志组成员前先进行归档
3.删除后操作系统的系统文件没被删除，只是控制文件中记录了该重做日志组成员被删除了
SQL> select group#,status,type,member from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
1 ONLINE /database/msp/redo01a.rdo
1 ONLINE /database/msp/redo01b.rdo
2 ONLINE /database/msp/redo02a.rdo
2 ONLINE /database/msp/redo02b.rdo
3 ONLINE /database/msp/redo03a.rdo
3 ONLINE /database/msp/redo03b.rdo
4 ONLINE /database/msp/redo04a.rdo
4 ONLINE /database/msp/redo04b.rdo
8 rows selected.
SQL> alter database drop logfile member
2 '/database/msp/redo04b.rdo';
alter database drop logfile member
*
ERROR at line 1:
ORA-01609: log 4 is the current log for thread 1 - cannot drop members
ORA-00312: online log 4 thread 1: '/database/msp/redo04a.rdo'
ORA-00312: online log 4 thread 1: '/database/msp/redo04b.rdo' #该成员所在重做日志组正在被使用，所以无法删除
SQL> desc v$log
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
THREAD# NUMBER
SEQUENCE# NUMBER
BYTES NUMBER
BLOCKSIZE NUMBER
MEMBERS NUMBER
ARCHIVED VARCHAR2(3)
STATUS VARCHAR2(16)
FIRST_CHANGE# NUMBER
FIRST_TIME DATE
NEXT_CHANGE# NUMBER
NEXT_TIME DATE
SQL> select group#,members,archived,status from v$log;
GROUP# MEMBERS ARC STATUS
---------- ---------- --- ----------------
1 2 YES INACTIVE
2 2 YES INACTIVE
3 2 YES INACTIVE
4 2 NO CURRENT
SQL> alter system switch logfile;
System altered.
SQL> select group#,members,archived,status from v$log;
GROUP# MEMBERS ARC STATUS
---------- ---------- --- ----------------
1 2 YES INACTIVE
2 2 YES INACTIVE
3 2 NO CURRENT
4 2 YES INACTIVE
SQL> select group#,sequence#,members,archived,status from v$log;
GROUP# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 10 2 YES INACTIVE
2 11 2 YES INACTIVE
3 13 2 NO CURRENT
4 12 2 YES INACTIVE
SQL>
SQL>
SQL>
SQL> alter database drop logfile member
2 '/database/msp/redo04b.rdo';
Database altered.
SQL>
SQL>
SQL>
SQL> select group#,status,type,member from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
1 ONLINE /database/msp/redo01a.rdo
1 ONLINE /database/msp/redo01b.rdo
2 ONLINE /database/msp/redo02a.rdo
2 ONLINE /database/msp/redo02b.rdo
3 ONLINE /database/msp/redo03a.rdo
3 ONLINE /database/msp/redo03b.rdo
4 ONLINE /database/msp/redo04a.rdo
7 rows selected.
SQL> !
[oracle@server01 ~]$ cd /database/msp/
[oracle@server01 msp]$ ll
total 1774536
-rw-r----- 1 oracle oinstall 16433152 Mar 25 18:27 control01.ctl
-rw-r----- 1 oracle oinstall 16433152 Mar 25 18:27 control03.ctl
-rw-r----- 1 oracle oinstall 104865792 Mar 25 18:23 mspmytbs.dbf
-rw-r----- 1 oracle oinstall 104858112 Mar 24 23:08 redo01a.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 24 23:08 redo01b.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 24 23:42 redo02a.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 24 23:42 redo02b.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 25 18:26 redo03a.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 25 18:26 redo03b.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 25 18:23 redo04a.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 25 18:23 redo04b.rdo
-rw-r----- 1 oracle oinstall 314580992 Mar 25 18:23 sysaux01.dbf
-rw-r----- 1 oracle oinstall 314580992 Mar 25 18:23 system01.dbf
-rw-r----- 1 oracle oinstall 104865792 Mar 24 22:00 temp01.dbf
-rw-r----- 1 oracle oinstall 209723392 Mar 25 18:23 undotbs01.dbf
[oracle@server01 msp]$ rm -rf redo04b.rdo
==========================================================
删除组：
1.一个实例至少要有两组重做日志组
2.处于active或者current组不能被删除，除非手动进行组切换
3.删除组后需要手动对操作系统进行系统文件删除
SQL> alter database drop logfile group 4;
Database altered.
SQL> select group#,status,type,member from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
1 ONLINE /database/msp/redo01a.rdo
1 ONLINE /database/msp/redo01b.rdo
2 ONLINE /database/msp/redo02a.rdo
2 ONLINE /database/msp/redo02b.rdo
3 ONLINE /database/msp/redo03a.rdo
3 ONLINE /database/msp/redo03b.rdo
6 rows selected.
SQL> !
[oracle@server01 ~]$ cd /database/msp
[oracle@server01 msp]$ ll
total 1672132
-rw-r----- 1 oracle oinstall 16433152 Mar 25 18:30 control01.ctl
-rw-r----- 1 oracle oinstall 16433152 Mar 25 18:30 control03.ctl
-rw-r----- 1 oracle oinstall 104865792 Mar 25 18:23 mspmytbs.dbf
-rw-r----- 1 oracle oinstall 104858112 Mar 24 23:08 redo01a.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 24 23:08 redo01b.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 24 23:42 redo02a.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 24 23:42 redo02b.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 25 18:30 redo03a.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 25 18:30 redo03b.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 25 18:23 redo04a.rdo
-rw-r----- 1 oracle oinstall 314580992 Mar 25 18:30 sysaux01.dbf
-rw-r----- 1 oracle oinstall 314580992 Mar 25 18:30 system01.dbf
-rw-r----- 1 oracle oinstall 104865792 Mar 24 22:00 temp01.dbf
-rw-r----- 1 oracle oinstall 209723392 Mar 25 18:30 undotbs01.dbf
[oracle@server01 msp]$ rm -rf redo04a.rdo
==================================
移动重做日志位置和重命名重做日志组成员
注意：移动重做日志组最好先备份
移动和重命名的方法：
方法1：增加一个新的成员再删除旧成员；
方法2：
SQL> select group#,status,type,member from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
1 ONLINE /database/msp/redo01a.rdo
1 ONLINE /database/msp/redo01b.rdo
2 ONLINE /database/msp/redo02a.rdo
2 ONLINE /database/msp/redo02b.rdo
3 ONLINE /database/msp/redo03a.rdo
3 ONLINE /database/msp/redo03b.rdo
6 rows selected.
SQL> shutdown immediate; #1.先关闭数据库
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !
[oracle@server01 msp]$ mv redo03b.rdo ../redo03c.rdo #2.手动移动和重命名重做日志文件
[oracle@server01 msp]$ cd ..
[oracle@server01 database]$ ll
total 102428
drwx------ 2 root root 16384 Mar 2 01:50 lost+found
drwxr-xr-x 2 oracle oinstall 4096 Mar 25 18:34 msp
drwxr-x--- 2 oracle oinstall 4096 Mar 2 02:07 ora1
-rw-r----- 1 oracle oinstall 104858112 Mar 25 18:32 redo03c.rdo
[oracle@server01 database]$ cd msp
[oracle@server01 msp]$ ll
total 1467324
-rw-r----- 1 oracle oinstall 16433152 Mar 25 18:32 control01.ctl
-rw-r----- 1 oracle oinstall 16433152 Mar 25 18:32 control03.ctl
-rw-r----- 1 oracle oinstall 104865792 Mar 25 18:32 mspmytbs.dbf
-rw-r----- 1 oracle oinstall 104858112 Mar 24 23:08 redo01a.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 24 23:08 redo01b.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 24 23:42 redo02a.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 24 23:42 redo02b.rdo
-rw-r----- 1 oracle oinstall 104858112 Mar 25 18:32 redo03a.rdo
-rw-r----- 1 oracle oinstall 314580992 Mar 25 18:32 sysaux01.dbf
-rw-r----- 1 oracle oinstall 314580992 Mar 25 18:32 system01.dbf
-rw-r----- 1 oracle oinstall 104865792 Mar 24 22:00 temp01.dbf
-rw-r----- 1 oracle oinstall 209723392 Mar 25 18:32 undotbs01.dbf
[oracle@server01 msp]$ exit
exit
SQL> startup mount; #3.将数据库启动到mount状态
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 633343360 bytes
Database Buffers 192937984 bytes
Redo Buffers 6590464 bytes
Database mounted.
SQL> alter database rename file #4.向数据库提交移动和重命名的语句
2 '/database/msp/redo03b.rdo'
3 to '/database/redo03c.rdo';
Database altered.
SQL>
SQL> alter database open #5.最后，将数据库置为open状态即可
2 ;
Database altered.
SQL> select group#,status,type,member from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
1 ONLINE /database/msp/redo01a.rdo
1 ONLINE /database/msp/redo01b.rdo
2 ONLINE /database/msp/redo02a.rdo
2 ONLINE /database/msp/redo02b.rdo
3 ONLINE /database/msp/redo03a.rdo
3 ONLINE /database/redo03c.rdo
6 rows selected.
=====================================================================
当重做日志组损坏的时候，需要对该组进行重新清空后使用，这样就会造成一定的文件数据丢失
alter database clear logfile group2
alter database clear unarchived logfile group2
注意：因为会造成数据丢失，所以尽量避免使用以上指令
============================================
最好把同一个组的成员分配在不同的磁盘上，做到冗余，当同一组某个成员坏了，因为还有成员在另外的盘上，重做日志文件仍旧可以正常工作。
最好让每个组的每个重做日志成员，归档日志文件，数据文件都分配在不同的盘上，这样可以避免后台进程（LGWR和DBWn）同时写一个磁盘造成冲突。
==================================
关于thread：
对于RAC环境下，每个实例都有自己的重做日志组，每个实例各自的重做日志组就是一个thread，在单实例下thread只有一个，所以值为1。
=======================================================================================
归档重做日志文件：
防止重写的时候日志文件被覆盖，而造成无法恢复数据
查看数据库是否开启归档模式：
SQL> select ARCHIVER from v$instance;
ARCHIVE
-------
STARTED
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
重做日志文件什么时候被重写覆盖？
1.checkpoint已经进行了
2.其文件已经被ARCn进程进行归档了
