1. 数据字典视图v$log记录了当前数据库的日志组号，日志序列号和每个日志文件的大小（以字节为单位），每个日志组的成员数量，以及日志组的当前状态
SYS@orcl>select group#,sequence#,bytes/1024/1024,members,archived,status from v$log;
GROUP# SEQUENCE# BYTES/1024/1024 MEMBERS ARC STATUS
---------- ---------- --------------- ---------- --- ----------------
1 13 50 1 YES INACTIVE
2 14 50 1 NO CURRENT
3 12 50 1 YES INACTIVE

2. 17:25:59 SYS@orcl>select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------ --- status:stale:说明该文件内容是不完整的
3 ONLINE /u01/app/oracle/oradata/orcl/r NO 空白：说明该日志组正在使用
edo03.log invalid：表示该文件不能被访问
deleted：表示该文件已经不再使用
2 ONLINE /u01/app/oracle/oradata/orcl/r NO
edo02.log

1 ONLINE /u01/app/oracle/oradata/orcl/r NO
edo01.log

3. 添加重做日志组
SYS@orcl>alter database add logfile group 4 ('/u01/app/oracle/oradata/orcl/redoa.log','/u01/app/oracle/oradata/orcl/redob.log') size 100M;

SYS@orcl>alter database add logfile group 5 ('/u01/app/oracle/oradata/orcl/redo11.log','/u01/app/oracle/oradata/orcl/redo22.log',
'/u01/app/oracle/oradata/orcl/redo33.log') size 50M;

4. 验证日志组是否添加
SYS@orcl>col member for a30
SYS@orcl>select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------ ---
3 ONLINE /u01/app/oracle/oradata/orcl/r NO
edo03.log

2 ONLINE /u01/app/oracle/oradata/orcl/r NO
edo02.log

1 ONLINE /u01/app/oracle/oradata/orcl/r NO
edo01.log

4 ONLINE /u01/app/oracle/oradata/orcl/r NO
edoa.log

4 ONLINE /u01/app/oracle/oradata/orcl/r NO
edob.log

5 ONLINE /u01/app/oracle/oradata/orcl/r NO
edo11.log

5 ONLINE /u01/app/oracle/oradata/orcl/r NO
edo22.log

5 ONLINE /u01/app/oracle/oradata/orcl/r NO
edo33.log

5. 删除日志组
SYS@orcl>alter database drop logfile group 5;

6. 验证日志组是否删除
SYS@orcl>select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------ ---
3 ONLINE /u01/app/oracle/oradata/orcl/r NO
edo03.log

2 ONLINE /u01/app/oracle/oradata/orcl/r NO
edo02.log

1 ONLINE /u01/app/oracle/oradata/orcl/r NO
edo01.log

4 ONLINE /u01/app/oracle/oradata/orcl/r NO
edoa.log

4 ONLINE /u01/app/oracle/oradata/orcl/r NO
edob.log

7. 向日志组中添加成员
SYS@orcl>alter database add logfile member '/u01/app/oracle/oradata/orcl/redo01a.log' to group 1,
'/u01/app/oracle/oradata/orcl/redo02a.log' to group 2,
'/u01/app/oracle/oradata/orcl/redo03a.log' to group 3;
8. 验证日志组成员数量
SYS@orcl>select GROUP#,SEQUENCE#,BYTES/1024/1024,MEMBERS,STATUS from v$log;

GROUP# SEQUENCE# BYTES/1024/1024 MEMBERS STATUS
---------- ---------- --------------- ---------- ----------------
1 13 50 2 INACTIVE
2 14 50 2 CURRENT
3 12 50 2 INACTIVE
4 0 100 2 UNUSED

9. 删除日志组成员
alter database drop logfile member '/u01/app/oracle/oradata/orcl/redo02a.log';

10.重做日志组更改大小的话，只能添加新的日志组，设置大小，然后删除之前的日志组。。。

11.清除联机重做日志
数据库处于归档模式时候，如果正在使用的重做日志文件损坏，重做日志不能完成归档，这个时候，要清除重做日志文件并且重新初始化重做日志文件
SYS@orcl>alter database claer logfile group 1;

12.强制切换日志组，强制触发检查点事件
18:31:01 SYS@orcl>alter system switch logfile;

System altered.

18:31:18 SYS@orcl>alter system checkpoint;

System altered.

ITPub博客

重做日志

最新文章