日志管理

最新推荐文章于 2026-06-20 11:16:21 发布

转载最新推荐文章于 2026-06-20 11:16:21 发布 · 163 阅读

标签

#数据库

本文介绍了Oracle数据库中重做日志的管理操作，包括删除、增加重做日志组及成员，展示了具体SQL命令及其执行效果。

-- 查看相关信息
SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ----------
1 1 0 104857600 1 YES UNUSED 0
2 1 0 104857600 1 YES UNUSED 0
3 1 1 104857600 1 NO CURRENT 190578 2007-04-22

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
3 ONLINE D:ORACLEORADATAHUZCREDO03.LOG
2 ONLINE D:ORACLEORADATAHUZCREDO02.LOG
1 ONLINE D:ORACLEORADATAHUZCREDO01.LOG

-- 删除重做日志组
-- 当前日志组不能被删除！
SQL> -- drop online redo log groups
SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for thread 1 - cannot drop
ORA-00312: online log 3 thread 1: 'D:ORACLEORADATAHUZCREDO03.LOG'

SQL> alter database drop logfile group 1;

Database altered.

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
3 ONLINE D:ORACLEORADATAHUZCREDO03.LOG
2 ONLINE D:ORACLEORADATAHUZCREDO02.LOG

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ----------
2 1 0 104857600 1 YES UNUSED 0
3 1 1 104857600 1 NO CURRENT 190578 2007-04-22

-- 增加重做日志组
SQL> alter database
2 add logfile group 1
3 ('D:oracleoradatahuzcredo01.log') size 10m;

Database altered.

-- 增加重做日志组成员
SQL> alter database
2 add logfile member
3 'D:oracleoradatahuzcredo04.log' to group 1;

Database altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ----------
1 1 0 10485760 2 YES UNUSED 0
2 1 0 104857600 1 YES UNUSED 0
3 1 1 104857600 1 NO CURRENT 190578 2007-04-22

SQL> select * from v$logfile;

-- 删除重做日志组成员
SQL> alter database
2 drop logfile member
3 'D:oracleoradatahuzcredo04.log';

Database altered.

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17826/viewspace-912027/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17826/viewspace-912027/