控制文件和重做日志文件

1 重做日志文件

日志文件的数据文件类型：在线日志文件（又叫联机日志）与归档日志文件（在线日志文件的历史备份）

日志运行的模式：归档模式、非归档模式（是否自动归档）

日志的运行流程：写文件是依靠LGWR后台进程

日志按照有序循环的方式被使用，即当一组日志文件被填满后，循环覆盖下一组日志文件，不断循环。

重做日志文件的目的：1. 记录数据的改变 2. 提供数据恢复 3. 维护数据库完整性。

1.1 日志分析或挖掘（logmnr）

1、set directory

修改D:/ORACLE/ADMIN/FOXCONN/PFILE/INIT.ORA

URL_FILE_DIR=''

2、restartup

3、create directory file

desc dbms_logmnr

start d:/oracle/ora92/rdbms/admin/catproc.sql // 可以做一些数据的更新后找到当前日志组

exec dbms_logmnr_d.build('foxdict.ora','d:/oracle/oradata/foxconn/cdump');

4、add/remove log file

exec dbms_logmnr.add_logfile/remove_logfile('d:/oracle/oradata/foxconn/redo02.log',dbms_logmnr.new);

exec dbms_logmnr.add_logfile/remove_logfile('d:/oracle/oradata/foxconn/redo03.log',dbms_logmnr.new);

5、start logmnr

exec dbms_logmnr.start_logmnr(dictionaryname='d:/oracle/admin/fox/cdump/foxdict.ora');

6、v$logmnr_context(sqlredo,sqlundo)

2 在线日志的常规操作

2.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> sele ct * 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.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

2.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.

2.4 重命名日志成员

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

2.4.1 关闭数据库

sys@ora11g> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

2.4.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

2.4.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.

2.4.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.

2.4.5 最后，不要忘记备份控制文件

sys@ora11g>alter database backup controlfile to trace;

Database altered.

2.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.

2.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.

2.7 强制切换日志

sys@ora11g>alter system switch logfile;

System altered.

sys@ora11g>alter system archive log current;

System altered.

2.8 小结

1).日志文件非常重要，当多路复用重做日志文件时，应该把一个组的成员保存在不同的磁盘上。

2).在完成日志文件维护后一定要记得备份最新的控制文件！

3).以上试验是在11g环境下完成的，在10g环境中一样适用。

3 常用SQL说明

3.1 select * from v$log

查看日志组。

3.2 select * from v$logfile

查看日志文件。

3.3 archive log list

查询当前的日志文件状态。

3.4 alter system archive log all

手工归档所有的日志文件组。

3.5 alter system archive log current

手工归档活动的日志文件组。

Note1：会对数据库中的所有实例执行日志切换。

Note2：是归档当前的重做日志文件，不管自动归档有没有打开都归档。

3.6 alter system archive log start

改为自动存档。

3.7 alter system switch logfile

The SWITCH LOGFILE clause lets you explicitly force Oracle to begin writing to a new redo log file group, regardless of whether the files in the current redo log file group are full. When you force a log switch, Oracle begins to perform a checkpoint but returns control to you immediately rather than when the checkpoint is complete. To use this clause, your instance must have the database open.

开始写新的日志文件组。不管当前日志文件组是否满了，强制执行日志切换。

Note1：不一定就归档当前的重做日志文件（若自动归档打开，就归档前的重做日志，若自动归档没有打开，就不归档当前重做日志。）

Note2：对单实例数据库或RAC中的当前实例执行日志切换。

3.8 alter system checkpoint

Specify CHECKPOINT to explicitly force Oracle to perform a checkpoint, ensuring that all changes made by committed transactions are written to datafiles on disk. You can specify this clause only when your instance has the database open. Oracle does not return control to you until the checkpoint is complete.

强迫oracle进行以次检查点，确保所有提交的事务的改变都被写到磁盘数据文件上。

3.9 select * from v$archived_log

查看已经归档的日志文件。

4 alter system switch logfile 和 alter system archive log current 的区别

ALTER SYSTEM SWITCH LOGFILE 对单实例数据库或RAC中的当前实例执行日志切换。(也可以是非归档情况下日志切换)

ALTER SYSTEM ARCHIVE LOG CURRENT 会对数据库中的所有实例执行日志切换。

详细解释：

-------------------------

我们都知道在自动归档的数据库中，这两个命令几乎产生的结果是一样的。来看看oracle的解释：

ALTER SYSTEM SWITCH LOGFILE ;

SWITCH LOGFILE Clause

The SWITCH LOGFILE clause lets you explicitly force Oracle to begin writing to a new redo log file group, regardless of whether the files in the current redo log file group are full. When you force a log switch, Oracle begins to perform a checkpoint but returns control to you immediately rather than when the checkpoint is complete. To use this clause, your instance must have the database open.ALTER SYSTEM ARCHIVE LOG CURRENT ;

CURRENT Clause

Specify CURRENT to manually archive the current redo log file group of the specified thread, forcing a log switch. If you omit the THREAD parameter, then Oracle archives all redo log file groups from all enabled threads, including logs previous to current logs. You can specify CURRENT only when the database is open.ALTER SYSTEM ARCHIVE LOG CURRENT NOSWITCH;

NOSWITCH

Specify NOSWITCH if you want to manually archive the current redo log file group without forcing a log switch. This setting is used primarily with standby databases to prevent data divergence when the primary database shuts down. Divergence implies the possibility of data loss in case of primary database failure.You can use the NOSWITCH clause only when your instance has the database mounted but not open. If the database is open, then this operation closes the database automatically. You must then manually shut down the database before you can reopen it

我们首先要明白两个事实：

一个就是在logfile switch的时候会触发arch进程，即通知归档进程说那个日志可以归档了。

另一个是logfile switch的时候会触发ckpt进程。而ckpt进程又会触发dbwr进程。dbwr进程会把上一个redo中保护的储藏在buffer cache中的dirty buffer全部写回磁盘。然后，ckpt进程纪录scn到controlfile中，这时会把redo log的状态由active 变成inactive。由此我们可以看出处于inactive状态的redo log对于instance recovery是没有用处了。但是它可能对于meda recovery还有用处，因为处于inactive状态的redo log可能已经归档，也可能没有归档。出现 checkpoint not complete,unable to allocate new redo log file的错误，是因为lgwr要覆盖一个其上检查点过程没有结束的日志文件，lgwr会等待dbwr完成，同时出现此错误。出现这个错误的原因，大概有几点，一个是dbwr写出速度太慢，一个是buffer cache中的脏数据太多。即事务太频繁，另一个也可能是日志太小，或日志组太少导致的。备份恢复中用到的几个操作：

alter system checkpoint;

alter system archive log all;

alter system archive log current

alter system switch logfile;

1：alter system checkpoint;

Specify CHECKPOINT to explicitly force Oracle to perform a checkpoint, ensuring that all changes made by committed transactions are written to datafiles on disk. You can specify this clause only when your instance has the database open. Oracle does not return control to you until the checkpoint is complete.

强迫oracle进行以次检查点，确保所有提交的事务的改变都被写到磁盘数据文件上。

2： alter system archive log all;

手工归档所有的日志文件组

3：alter system archive log current

手工归档活动的日志文件组。

4： alter system switch logfile;

The SWITCH LOGFILE clause lets you explicitly force Oracle to begin writing to a new redo log file group, regardless of whether the files in the current redo log file group are full. When you force a log switch, Oracle begins to perform a checkpoint but returns control to you immediately rather than when the checkpoint is complete. To use this clause, your instance must have the database open.

开始写新的日志文件组。不管当前日志文件组是否满了。

5 问题

5.1 oracle在哪些情况下会做归档？

1. 当自动归档设置打开时，会定时做归档。

如何定时？

如何打开自动归档？

2. 备份后。

3. shutdown