Oracle学习（八）之基于OFM机制的日志组管理

最新推荐文章于 2021-04-14 09:43:44 发布

木子果果

最新推荐文章于 2021-04-14 09:43:44 发布

阅读量1.3k

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle数据库学习文章标签： Oracle数据库数据库OFNM机制 OFM日志组管理

本文链接：https://blog.youkuaiyun.com/lzg1169350237/article/details/46272669

Oracle数据库学习专栏收录该内容

21 篇文章

订阅专栏

Oracle 11gR2数据库共有5参数，可用来快速建立日志组时增加日志文件，设置好后，在增加日志组时能简化语句。

这个机制的好处为：
1）当我们删除某一个采用OFM机制的日志组时，可以将本地文件一起删除，手动增加的日志组则没有此功能。
2）可以简化我们的操作。

1.查看和设置参数

1）查看参数

  SQL> show parameter create;

       NAME                                 TYPE        VALUE
      ------------------------------------ ----------- -----------------   
       create_bitmap_area_size              integer     8388608
       create_stored_outlines               string
       db_create_file_dest                  string
       db_create_online_log_dest_1          string
       db_create_online_log_dest_2          string
       db_create_online_log_dest_3          string
       db_create_online_log_dest_4          string
       db_create_online_log_dest_5          string

2）设置参数

   SQL> alter system set 
     2  db_create_online_log_dest_1='/u03/ora11g/student';

    System altered.

   SQL> alter system set 
     2  db_create_online_log_dest_2='/u04/ora11g/student';

    System altered.

   SQL> alter system set 
     2  db_create_online_log_dest_3='/u05/ora11g/student';

    System altered.

2.增加日志组

1）增加日志组

    SQL> alter database add logfile;

     Database altered.

2）查看日志组

    SQL> select GROUP#,MEMBER from v$logfile order by 1,2;

         GROUP# MEMBER
     ---------- ----------------------------------------------------
              1 /u03/ora11g/student/redo0101.rdo
              1 /u03/ora11g/student/redo0201.rdo
              1 /u03/ora11g/student/redo0301.rdo
              2 /u04/ora11g/student/redo0202.rdo
              2 /u04/ora11g/student/redo0302.rdo
              2 /u04/ora11g/student/redo0402.rdo
              3 /u05/ora11g/student/redo0103.rdo
              3 /u05/ora11g/student/redo0203.rdo
              3 /u05/ora11g/student/redo0303.rdo
              4 /u03/ora11g/student/ORCL/onlinelog/o1_mf_4_bpl65wg7_.log
              4 /u04/ora11g/student/ORCL/onlinelog/o1_mf_4_bpl65wn7_.log

         GROUP# MEMBER
     ---------- ----------------------------------------------------
              4 /u05/ora11g/student/ORCL/onlinelog/o1_mf_4_bpl66c73_.log
              8 /u03/ora11g/student/redo0801.rdo
              8 /u04/ora11g/student/redo0802.rdo
              8 /u05/ora11g/student/redo0803.rdo

      15 rows selected

其中数据库在默认的参数下建立“数据库名/onlinelog/带组号等的日志文件”的目录，并且增加了一个日志组4。

3.删除日志组

1）删除日志组

  SQL> alter database drop logfile group 4;

    Database altered.

2）查看日志组

   SQL> select GROUP#,status from v$log;

         GROUP# STATUS
     ---------- ----------------
              1 INACTIVE
              2 INACTIVE
              3 CURRENT
              8 UNUSED

3）查看日志组文件

   SQL> select GROUP#,MEMBER from v$logfile order by 1,2;

       GROUP# MEMBER   
   ---------- -----------------------------------------
            1 /u03/ora11g/student/redo0101.rdo
            1 /u03/ora11g/student/redo0201.rdo
            1 /u03/ora11g/student/redo0301.rdo
            2 /u04/ora11g/student/redo0202.rdo
            2 /u04/ora11g/student/redo0302.rdo
            2 /u04/ora11g/student/redo0402.rdo
            3 /u05/ora11g/student/redo0103.rdo
            3 /u05/ora11g/student/redo0203.rdo
            3 /u05/ora11g/student/redo0303.rdo
            8 /u03/ora11g/student/redo0801.rdo
            8 /u04/ora11g/student/redo0802.rdo
       ROUP# MEMBER
   ---------- ----------------------------------------
            8 /u05/ora11g/student/redo0803.rdo

    12 rows selected.

4.修改相应参数

1）设置例程恢复所用最长时间设置为15分钟(参数默认单位秒)

SYS(D647001)>show parameter fast;

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- --------
fast_start_io_target                 integer                0
fast_start_mttr_target               integer                0
fast_start_parallel_rollback         string                 LOW

SYS(D647001)>alter system set fast_start_mttr_target=900;

2）设置SMON进程在修复时唯一使用的日志组设置为6

切换日志文件，是第六组处于CURRENT状态

SYS(D647001)>alter system switch logfile;

系统已更改。

SYS(D647001)>select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARCHIV STATUS
---------- ---------- ---------- ---------- ---------- ---------- ------ ---------
     1          1         23   52428800        512          3 YES    ACTIVE
     5          1         24  204472320        512          3 YES    ACTIVE
     6          1         26   52428800        512          3 NO     CURRENT
     7          1         25  152043520        512          3 YES    ACTIVE

切换检查点

SYS(D647001)>alter system checkpoint;

系统已更改。