ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【REDO】重做日志文件(redo log files)管理(增,删,改,查,切)

【REDO】重做日志文件(redo log files)管理(增,删,改,查,切)

原创 Linux操作系统 作者:secooler 时间:2009-03-09 17:39:30 0 删除 编辑
汇总整理一下有关重做日志文件(redo log files)管理相关的操作(增,删,改,查,切)。供参考。

1.当前日志相关信息

sys@ora11g> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS    FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- --------- ------------- ---------
         1          1         10  209715200          1 YES INACTIVE         461938 09-MAR-09
         2          1         11  209715200          1 NO  CURRENT          485885 09-MAR-09
         3          1          9  209715200          1 YES INACTIVE         432636 04-MAR-09

sys@ora11g> select * from v$logfile;

    GROUP# STATUS  TYPE                 MEMBER                                   IS_
---------- ------- -------------------- ---------------------------------------- ---
         1         ONLINE               /oracle/u02/oradata/ora11g/redo01.log    NO
         2         ONLINE               /oracle/u02/oradata/ora11g/redo02.log    NO
         3         ONLINE               /oracle/u02/oradata/ora11g/redo03.log    NO

2.添加重做日志组
sys@ora11g> alter database add logfile group 4 ('/oracle/u02/oradata/ora11g/redo04_01.log','/oracle/u02/oradata/ora11g/redo04_02.log') size 50m;

Database altered.

sys@ora11g> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS    FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- --------- ------------- ---------
         1          1         10  209715200          1 YES INACTIVE         461938 09-MAR-09
         2          1         11  209715200          1 NO  CURRENT          485885 09-MAR-09
         3          1          9  209715200          1 YES INACTIVE         432636 04-MAR-09
         4          1          0   52428800          2 YES UNUSED                0

sys@ora11g> select * from v$logfile;

    GROUP# STATUS  TYPE                 MEMBER                                   IS_
---------- ------- -------------------- ---------------------------------------- ---
         1         ONLINE               /oracle/u02/oradata/ora11g/redo01.log    NO
         2         ONLINE               /oracle/u02/oradata/ora11g/redo02.log    NO
         3         ONLINE               /oracle/u02/oradata/ora11g/redo03.log    NO
         4         ONLINE               /oracle/u02/oradata/ora11g/redo04_01.log NO
         4         ONLINE               /oracle/u02/oradata/ora11g/redo04_02.log NO


3.添加日志文件
sys@ora11g> alter database add logfile member
             '/oracle/u02/oradata/ora11g/redo01_02.log' to group 1,
             '/oracle/u02/oradata/ora11g/redo02_02.log' to group 2,
             '/oracle/u02/oradata/ora11g/redo03_02.log' to group 3;

Database altered.

sys@ora11g> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS    FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- --------- ------------- ---------
         1          1         10  209715200          2 YES INACTIVE         461938 09-MAR-09
         2          1         11  209715200          2 NO  CURRENT          485885 09-MAR-09
         3          1          9  209715200          2 YES INACTIVE         432636 04-MAR-09
         4          1          0   52428800          2 YES UNUSED                0

sys@ora11g>  select * from v$logfile order by 1;

    GROUP# STATUS  TYPE                 MEMBER                                   IS_
---------- ------- -------------------- ---------------------------------------- ---
         1         ONLINE               /oracle/u02/oradata/ora11g/redo01.log    NO
         1 INVALID ONLINE               /oracle/u02/oradata/ora11g/redo01_02.log NO
         2 INVALID ONLINE               /oracle/u02/oradata/ora11g/redo02_02.log NO
         2         ONLINE               /oracle/u02/oradata/ora11g/redo02.log    NO
         3         ONLINE               /oracle/u02/oradata/ora11g/redo03.log    NO
         3 INVALID ONLINE               /oracle/u02/oradata/ora11g/redo03_02.log NO
         4         ONLINE               /oracle/u02/oradata/ora11g/redo04_02.log NO
         4         ONLINE               /oracle/u02/oradata/ora11g/redo04_01.log NO

8 rows selected.

4.重命名日志成员
在重命名日志组成员之前新的目标必须已经存在。Oracle的sql命令只是把控制文件中的内部指针指向新的日志文件。
1)关闭数据库

sys@ora11g> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

2)使用操作系统命令重命名或移动日志文件
ora11g@RHEL53 /oracle/u02/oradata/ora11g$ mv redo01.log redo01_01.log
ora11g@RHEL53 /oracle/u02/oradata/ora11g$ mv redo02.log redo02_01.log
ora11g@RHEL53 /oracle/u02/oradata/ora11g$ mv redo03.log redo03_01.log

3)启动数据库实例到mount状态,重命名控制文件中的日志文件成员。
NotConnected@> select * from v$logfile order by 1,4;

    GROUP# STATUS  TYPE                 MEMBER                                     IS_
---------- ------- -------------------- ------------------------------------------ ---
         1         ONLINE               /oracle/u02/oradata/ora11g/redo01.log      NO
         2         ONLINE               /oracle/u02/oradata/ora11g/redo02.log      NO
         3         ONLINE               /oracle/u02/oradata/ora11g/redo03.log      NO
         4         ONLINE               /oracle/u02/oradata/ora11g/redo04_01.log   NO
         4         ONLINE               /oracle/u02/oradata/ora11g/redo04_02.log   NO
         1         ONLINE               /oracle/u02/oradata/ora11g/redo01_02.log   NO
         2         ONLINE               /oracle/u02/oradata/ora11g/redo02_02.log   NO
         3         ONLINE               /oracle/u02/oradata/ora11g/redo03_02.log   NO

8 rows selected.

NotConnected@> alter database rename file '/oracle/u02/oradata/ora11g/redo01.log' to '/oracle/u02/oradata/ora11g/redo01_01.log';

Database altered.

NotConnected@> alter database rename file '/oracle/u02/oradata/ora11g/redo02.log' to '/oracle/u02/oradata/ora11g/redo02_01.log';

Database altered.

NotConnected@> alter database rename file '/oracle/u02/oradata/ora11g/redo03.log' to '/oracle/u02/oradata/ora11g/redo03_01.log';

Database altered.

4)open数据库,验证结果
NotConnected@> alter database open;

Database altered.

sys@ora11g> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS   FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- -------- ------------- ---------
         1          1         18  209715200          2 YES INACTIVE        486960 09-MAR-09
         2          1         19  209715200          2 YES INACTIVE        486964 09-MAR-09
         3          1         21  209715200          2 NO  CURRENT         486973 09-MAR-09
         4          1         20   52428800          2 YES INACTIVE        486968 09-MAR-09

sys@ora11g> select * from v$logfile order by 1,4;

    GROUP# STATUS  TYPE                 MEMBER                                     IS_
---------- ------- -------------------- ------------------------------------------ ---
         1         ONLINE               /oracle/u02/oradata/ora11g/redo01_01.log   NO
         1         ONLINE               /oracle/u02/oradata/ora11g/redo01_02.log   NO
         2         ONLINE               /oracle/u02/oradata/ora11g/redo02_01.log   NO
         2         ONLINE               /oracle/u02/oradata/ora11g/redo02_02.log   NO
         3         ONLINE               /oracle/u02/oradata/ora11g/redo03_01.log   NO
         3         ONLINE               /oracle/u02/oradata/ora11g/redo03_02.log   NO
         4         ONLINE               /oracle/u02/oradata/ora11g/redo04_01.log   NO
         4         ONLINE               /oracle/u02/oradata/ora11g/redo04_02.log   NO

8 rows selected.

5)最后,不要忘记备份控制文件
sys@ora11g> alter database backup controlfile to trace;

Database altered.

5.删除一个非活动的重做日志组的成员
sys@ora11g> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS   FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- -------- ------------- ---------
         1          1         18  209715200          2 YES INACTIVE        486960 09-MAR-09
         2          1         19  209715200          2 YES INACTIVE        486964 09-MAR-09
         3          1         21  209715200          2 NO  CURRENT         486973 09-MAR-09
         4          1         20   52428800          2 YES INACTIVE        486968 09-MAR-09

sys@ora11g> alter database drop logfile member '/oracle/u02/oradata/ora11g/redo04_02.log';

Database altered.

sys@ora11g> !ls -l /oracle/u02/oradata/ora11g/redo04_02.log
-rw-r----- 1 oracle oinstall 52429312 Mar  9 16:28 /oracle/u02/oradata/ora11g/redo04_02.log

sys@ora11g> !rm -f /oracle/u02/oradata/ora11g/redo04_02.log

sys@ora11g> select * from v$logfile order by 1,4;

    GROUP# STATUS  TYPE                 MEMBER                                     IS_
---------- ------- -------------------- ------------------------------------------ ---
         1         ONLINE               /oracle/u02/oradata/ora11g/redo01_01.log   NO
         1         ONLINE               /oracle/u02/oradata/ora11g/redo01_02.log   NO
         2         ONLINE               /oracle/u02/oradata/ora11g/redo02_01.log   NO
         2         ONLINE               /oracle/u02/oradata/ora11g/redo02_02.log   NO
         3         ONLINE               /oracle/u02/oradata/ora11g/redo03_01.log   NO
         3         ONLINE               /oracle/u02/oradata/ora11g/redo03_02.log   NO
         4         ONLINE               /oracle/u02/oradata/ora11g/redo04_01.log   NO

7 rows selected.

6.删除一个非活动的重做日志组
sys@ora11g> alter database drop logfile group 4;

Database altered.

sys@ora11g> !rm -f /oracle/u02/oradata/ora11g/redo04_01.log

sys@ora11g> select * from v$logfile;

    GROUP# STATUS  TYPE                 MEMBER                                     IS_
---------- ------- -------------------- ------------------------------------------ ---
         1         ONLINE               /oracle/u02/oradata/ora11g/redo01_01.log   NO
         2         ONLINE               /oracle/u02/oradata/ora11g/redo02_01.log   NO
         3         ONLINE               /oracle/u02/oradata/ora11g/redo03_01.log   NO
         1         ONLINE               /oracle/u02/oradata/ora11g/redo01_02.log   NO
         2         ONLINE               /oracle/u02/oradata/ora11g/redo02_02.log   NO
         3         ONLINE               /oracle/u02/oradata/ora11g/redo03_02.log   NO

6 rows selected.

7.强制切换日志
sys@ora11g> alter system switch logfile;

System altered.

sys@ora11g> alter system archive log current;

System altered.

8.小结
1)日志文件非常重要,当多路复用重做日志文件时,应该把一个组的成员保存在不同的磁盘上。
2
在完成日志文件维护后一定要记得备份最新的控制文件!
3
以上试验是在11g环境下完成的,在10g环境中一样适用。

Good luck.

secooler
09.03.09

-- The End --

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

请登录后发表评论 登录
全部评论
Oracle ACE 总监,阿里云MVP,北京大学理学硕士,恩墨学院创始人,教育专家,中国区 Cloudera 首位官方授权大数据讲师,金牌培训专家,BDA大数据联盟创始人,OCM联盟创始人,ACCUG创始人、ACOUG核心专家,Blogger。

注册时间:2008-03-16

  • 博文量
    797
  • 访问量
    8012885