任务1：增加日志组数至4组，且每组日志成员大小为50M，每组2个成员。

--查看日志成员路径及日志组大小、状态
SYS@ENMOEDU>select group#,member from v$logfile;

GROUP# MEMBER
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/ENMOEDU/redo01.log
2 /u01/app/oracle/oradata/ENMOEDU/redo02.log
3 /u01/app/oracle/oradata/ENMOEDU/redo03.log

SYS@ENMOEDU>select GROUP#,bytes/1024/1024,MEMBERS,STATUS from v$log;

GROUP# BYTES/1024/1024 MEMBERS STATUS
---------- --------------- ---------- ----------------
1 30 1 INACTIVE
2 30 1 ACTIVE
3 30 1 CURRENT

SYS@ENMOEDU>

--增加group 4,group 5,group 6,group 7四组日志

ALTER DATABASE
ADD LOGFILE GROUP 4
('/u01/app/oracle/oradata/ENMOEDU/redo04_a.log' ,
'/u01/app/oracle/oradata/ENMOEDU/redo04_b.log') SIZE 50M;
ALTER DATABASE
ADD LOGFILE GROUP 5
('/u01/app/oracle/oradata/ENMOEDU/redo05_a.log' ,
'/u01/app/oracle/oradata/ENMOEDU/redo05_b.log') SIZE 50M;
ALTER DATABASE
ADD LOGFILE GROUP 6
('/u01/app/oracle/oradata/ENMOEDU/redo06_a.log' ,
'/u01/app/oracle/oradata/ENMOEDU/redo06_b.log') SIZE 50M;
ALTER DATABASE
ADD LOGFILE GROUP 7
('/u01/app/oracle/oradata/ENMOEDU/redo07_a.log' ,
'/u01/app/oracle/oradata/ENMOEDU/redo07_b.log') SIZE 50M;

--查看日志组状态
SYS@ENMOEDU>select group#,member from v$logfile;

GROUP# MEMBER
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/ENMOEDU/redo01.log
2 /u01/app/oracle/oradata/ENMOEDU/redo02.log
3 /u01/app/oracle/oradata/ENMOEDU/redo03.log
4 /u01/app/oracle/oradata/ENMOEDU/redo04_a.log
4 /u01/app/oracle/oradata/ENMOEDU/redo04_b.log
5 /u01/app/oracle/oradata/ENMOEDU/redo05_a.log
5 /u01/app/oracle/oradata/ENMOEDU/redo05_b.log
6 /u01/app/oracle/oradata/ENMOEDU/redo06_a.log
6 /u01/app/oracle/oradata/ENMOEDU/redo06_b.log
7 /u01/app/oracle/oradata/ENMOEDU/redo07_a.log
7 /u01/app/oracle/oradata/ENMOEDU/redo07_b.log

11 rows selected.

SYS@ENMOEDU>select GROUP#,bytes/1024/1024,MEMBERS,STATUS from v$log;

GROUP# BYTES/1024/1024 MEMBERS STATUS
---------- --------------- ---------- ----------------
1 30 1 INACTIVE
2 30 1 INACTIVE
3 30 1 CURRENT
4 50 2 UNUSED
5 50 2 UNUSED
6 50 2 UNUSED
7 50 2 UNUSED

7 rows selected.

SYS@ENMOEDU>

--切换日志，查看日志组状态
SYS@ENMOEDU>alter system switch logfile;

System altered.

SYS@ENMOEDU>select GROUP#,bytes/1024/1024,MEMBERS,STATUS from v$log;

GROUP# BYTES/1024/1024 MEMBERS STATUS
---------- --------------- ---------- ----------------
1 30 1 INACTIVE
2 30 1 INACTIVE
3 30 1 ACTIVE
4 50 2 CURRENT
5 50 2 UNUSED
6 50 2 UNUSED
7 50 2 UNUSED

7 rows selected.

SYS@ENMOEDU>

--手工执行检查点事件
SYS@ENMOEDU>alter system checkpoint;

System altered.

SYS@ENMOEDU>
SYS@ENMOEDU>select GROUP#,bytes/1024/1024,MEMBERS,STATUS from v$log;

GROUP# BYTES/1024/1024 MEMBERS STATUS
---------- --------------- ---------- ----------------
1 30 1 INACTIVE
2 30 1 INACTIVE
3 30 1 INACTIVE
4 50 2 CURRENT
5 50 2 UNUSED
6 50 2 UNUSED
7 50 2 UNUSED

7 rows selected.

SYS@ENMOEDU>

--删除group 1,group 2,group 3
SYS@ENMOEDU>ALTER DATABASE DROP LOGFILE GROUP 1;

Database altered.

SYS@ENMOEDU>ALTER DATABASE DROP LOGFILE GROUP 2;

Database altered.

SYS@ENMOEDU>ALTER DATABASE DROP LOGFILE GROUP 3;

Database altered.

SYS@ENMOEDU>select GROUP#,bytes/1024/1024,MEMBERS,STATUS from v$log;

GROUP# BYTES/1024/1024 MEMBERS STATUS
---------- --------------- ---------- ----------------
4 50 2 CURRENT
5 50 2 UNUSED
6 50 2 UNUSED
7 50 2 UNUSED

SYS@ENMOEDU>

说明：
日志组的四种状态：
unused:表明该日志组从来没有被使用过，我们可以看到重建的日志组就是这个状态。
current:是oracle当前正在使用的日志组，即后台进程LGWR正在写的日志组。
active:当发生日志切换的时候，状态为current的日志组就会变成active状态，表明记录在这个日志组的事务提交所造成的数据块改变，
还没有完全从database buffer cache 写入到data file中。当数据库处于archivelog mode时，ARCn就会归档这个日志组。
所以处在这个状态的日志组是不允许被覆盖的。崩溃恢复需要该状态，可用于块恢复，可能归档也可能未归档。
inactive:处于这个状态的日志组，表明记录在这个日志组的事务提交所造成的数据块的改变，
已经完全从database buffer cache写入到data file中了，实例恢复不再需要这个状态的日志组了，所以这个日志组是可以被覆盖的。

ITPub博客

日志文件管理

最新文章