Oracle DBA课程系列笔记（7_1）

原创

客居天涯 2012-02-28 14:45:28 博主文章分类：数据库技术 ©著作权

文章标签 oracle 职场休闲 Oracle study 文章分类 Oracle 数据库

©著作权归作者所有：来自51CTO博客作者客居天涯的原创作品，请联系作者获取转载授权，否则将追究法律责任

第七章： REDO 日志(1)

1、redo （重做） log 的功能：数据recovery
2、redo log 特征：
     1）记录数据块的变化（DML、DDL)
     2) 用于数据块的recover
     3）以组的方式管理redo file ，最少两组redo ，循环使用
     4）和数据文件存放到不同的磁盘上，需读写速度快的磁盘(比如采用RAID10)

     日志切换：
        1）归档模式：将历史日志进行保存
        2）非归档：历史日志被覆盖
        3）并产生checkpoint，通知redo log 所对应的 dirty block 从data buffer写入到datafile，并且更新控制文件

3、redo 日志组
     1）最少两组，最好每组有两个成员，并存放到不同的磁盘上，大小形同，互相镜像
     2）日志在组写满时发生切换，或手工切换： alter system switch logfile ；
     3）在归档模式，日志进行归档，并把相关的信息写入controlfile
4、添加日志组
     04:33:02 SQL> startup
ORACLE instance started.

Total System Global Area 251658240 bytes
Fixed Size                  1218820 bytes
Variable Size             134219516 bytes
Database Buffers          113246208 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
04:33:24 SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         2          1          7   10485760          1 NO CURRENT                 381102 02-AUG-11
         1          1          6   10485760          1 NO INACTIVE                357157 01-AUG-11

04:34:30 SQL> col member for a50
04:34:38 SQL> select group#, member from v$logfile;

    GROUP# MEMBER
---------- --------------------------------------------------
         2 /u01/app/oracle/oradata/lx02/redo02a.log
         1 /u01/app/oracle/oradata/lx02/redo01a.log

04:34:39 SQL> alter database add logfile
04:34:53 2 '/u01/app/oracle/oradata/lx02/redo03a.log' size 10m;

Database altered.

04:35:18 SQL> select group#, member from v$logfile;

04:35:24 SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          6   10485760          1 NO INACTIVE                357157 01-AUG-11
         3          1          0   10485760          1 YES UNUSED                       0
         2          1          7   10485760          1 NO CURRENT                 381102 02-AUG-11

04:35:31 SQL>

5、添加日志组的成员

04:36:54 SQL> select group#, member from v$logfile;

    GROUP# MEMBER
---------- --------------------------------------------------
         2 /u01/app/oracle/oradata/lx02/redo02a.log
         1 /u01/app/oracle/oradata/lx02/redo01a.log
         3 /u01/app/oracle/oradata/lx02/redo03a.log

04:38:30 SQL> alter database add logfile member
2   '/disk1/lx02/oradata/redo01b.log' to group 1,
3 '/disk1/lx02/oradata/redo02b.log' to group 2,
4* '/disk1/lx02/oradata/redo03b.log' to group 3；
04:38:31 SQL>

Database altered.

04:38:36 SQL> select group#, member from v$logfile order by 1;

    GROUP# MEMBER
---------- --------------------------------------------------
         1 /u01/app/oracle/oradata/lx02/redo01a.log
         1 /disk1/lx02/oradata/redo01b.log
         2 /disk1/lx02/oradata/redo02b.log
         2 /u01/app/oracle/oradata/lx02/redo02a.log
         3 /disk1/lx02/oradata/redo03b.log
         3 /u01/app/oracle/oradata/lx02/redo03a.log

6 rows selected.

04:38:47 SQL>

6、查看日志信息

04:38:47 SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          6   10485760          2 NO INACTIVE                357157 01-AUG-11
         3          1          0   10485760          2 YES UNUSED                       0
         2          1          7   10485760          2 NO CURRENT                 381102 02-AUG-11

04:40:19 SQL> alter system switch logfile;

System altered.

04:40:42 SQL> /

System altered.

04:40:43 SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          9   10485760          2 NO CURRENT                 384007 02-AUG-11
         3          1          8   10485760          2 NO ACTIVE                  384005 02-AUG-11
         2          1          7   10485760          2 NO ACTIVE                  381102 02-AUG-11

04:40:45 SQL>

status： unused 新添加的日志组，还没有使用
            inactive 日志组对应的脏块已经从data buffer写入到data file ，可以删除
            active 日志组对应的脏块还没有从data buffer写入到data file，不能被删除
            current 当前日志组，日志组对应的脏块还没有从data buffer写入到data file，不能被删除
           在删除日志组之前，进行日志手工切换，将被删除的日志切换到inactive状态。
            thread ：线程（通过后台进程lgwr 启动），在单实例的环境下，thread# 永远是1
            sequence ：日志序列号。在日志切换时会递增。
            FIRST_CHANGE# ：在当前日志中记录的首个数据块的scn。（当事务完成的时候会在数据块上写入一个scn，代表数据块的变化）。

    ----------查看生成的日志量
    14:51:12 SQL> insert into scott.emp1 select * from scott.emp1;

28 rows created.

14:51:33 SQL> insert into scott.emp1 select * from scott.emp1;

56 rows created.

14:51:35 SQL> select a.name,b.value from v$statname a,v$mystat b
14:51:39 2 where b.statistic#=a.statistic# and a.name like '%redo size%';

NAME VALUE
---------------------------------------------------------------- ----------
redo size 4544

14:51:42 SQL>
7、redo 日志成员重命名或迁移
05:07:37 SQL> select group#,member from v$logfile order by 1;

6 rows selected.

05:07:43 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
05:08:01 SQL> !
[oracle@oracle ~]$ cp /u01/app/oracle/oradata/lx02/redo01a.log /disk2/lx02/oradata/
[oracle@oracle ~]$ cp /u01/app/oracle/oradata/lx02/redo02a.log /disk2/lx02/oradata
[oracle@oracle ~]$ cp /u01/app/oracle/oradata/lx02/redo03a.log /disk2/lx02/oradata
[oracle@oracle ~]$ !sql
sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 2 05:08:35 2011

Connected to an idle instance.

05:08:35 SQL> startup mount
ORACLE instance started.

Total System Global Area 251658240 bytes
Fixed Size                  1218820 bytes
Variable Size             125830908 bytes
Database Buffers          121634816 bytes
Redo Buffers                2973696 bytes
Database mounted.
05:08:43 SQL> col member for a50
05:08:49 SQL> select group# ,member from v$logfile order by 1;

6 rows selected.

05:08:58 SQL> alter database rename file
05:09:05 2 '/u01/app/oracle/oradata/lx02/redo01a.log' to '/disk2/lx02/oradata/redo01a.log';

Database altered.

05:09:22 SQL> alter database rename file
05:09:26 2 '/u01/app/oracle/oradata/lx02/redo02a.log' to '/disk2/lx02/oradata/redo02a.log';

Database altered.

05:09:34 SQL> alter database rename file
05:09:35 2 '/u01/app/oracle/oradata/lx02/redo03a.log' to '/disk2/lx02/oradata/redo03a.log';

Database altered.

05:09:42 SQL> select group# ,member from v$logfile order by 1;

    GROUP# MEMBER
---------- --------------------------------------------------
         1 /disk2/lx02/oradata/redo01a.log
         1 /disk1/lx02/oradata/redo01b.log
         2 /disk1/lx02/oradata/redo02b.log
         2 /disk2/lx02/oradata/redo02a.log
         3 /disk1/lx02/oradata/redo03b.log
         3 /disk2/lx02/oradata/redo03a.log

6 rows selected.
05:09:53 SQL> alter database open;

Database altered.

05:10:01 SQL> select group# ,member from v$logfile order by 1;

6 rows selected.

05:10:06 SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          9   10485760          2 NO INACTIVE                384007 02-AUG-11
         3          1          8   10485760          2 NO INACTIVE                384005 02-AUG-11
         2          1         10   10485760          2 NO CURRENT                 385481 02-AUG-11

05:10:12 SQL>