oracle日志管理

oracle 日志管理

一： Active (Current) and Inactive Redo Log File(当前活动和非活动的日志文件）
active（current）：Redo log files that are required for instance recovery are called active redo log files
实例恢复需要该日志就叫活动的，或者叫当前的重做日志文件
inactive：Redo log files that are no longer required for instance recovery are called inactive redo log files
实例恢复时不需要的日志就叫inactive日志

二：Log Sequence Numbers
当发生日志切换并且lgwr后台进程开始写该日志文件，oracle数据库便给该日志文件分配一个新的日志序列号。
当数据库归档了该日志文件，则归档日志将会保有该日志序列号。
在线日志，或者归档日志被日志序列号唯一标识，当实例crash或者需要介质恢复时，数据库使用日志序列号以升序方式应用重做日志文件和必须的归档日志文件。

三：Creating Redo Log Groups
采用默认组号
ALTER DATABASE
ADD LOGFILE ('/oracle/CRM2/CRM/redo4a.log', '/oracle/CRM2/CRM/redo4b.log') SIZE 200M;

采用指定组号 (注意为了好管理不要跳跃性的指定你的组号）
ALTER DATABASE 
ADD LOGFILE GROUP 10 ('/oracle/CRM2/CRM/redo10a.log', '/oracle/CRM2/CRM/redo10b.log')
SIZE 200M;

四：Creating Redo Log Members
添加一个日志成员到日志组号2 注意事项：
必须指定日志文件名。
文件大小不需要指定，文件大小取决于已存在日志组指定的大小。
新日志成员的状态会从新添加时候的invalid到第一次使用时变为active
语句：
ALTER DATABASE ADD LOGFILE MEMBER '/oracle/CRM2/CRM/redo2b.log' TO GROUP 2;

五：Relocating and Renaming Redo Log Members（迁移和重命名日志成员）
重命名日志成员步骤：
步骤1： Shutdown the database.
shutdown immediate
步骤2： Copy the redo log files to the new location.
The following example uses operating system commands (UNIX) to move the 
redo log members to a new location:
mv /source/log1a.rdo /ddestination/log1c.rdo
mv /source/log2a.rdo /destination/log2c.rdo
步骤3： Startup the database。
startup mount
步骤4： Rename the redo log members.
alter database
rename file  '/source/log1a.rdo', '/source/log2a.rdo' 
           to '/destination/log1c.rdo', '/destination/log2c.rdo';
步骤5：Open the database for normal operation.
The redo log alterations take effect when the database is opened.
alter database open

六：Dropping Redo Log Groups and Members
drop log group（restrictions）：删除重做日志组的限制条件
限制一：instance 要求至少两个组，而不管组内成员个数
限制二：你可以丢弃一个状态为inactive状态的重做日志组，如果要丢弃当前的日志组，必须手动切换日志组
限制三：如果是归档模式，确定丢弃重做日志组之前，成员已经被归档，通过视图v$log 进行检查
select group#,archived,status from v$log;
    GROUP# ARC STATUS
---------- --- ----------------
         1 YES INACTIVE
         2 NO  CURRENT
         3 YES INACTIVE
         4 YES UNUSED
        10 YES UNUSED
语句：ALTER DATABASE DROP LOGFILE GROUP xx; 注意，该命令实际上不删除日志文件，需要用操作系统命令删除。

Dropping Redo Log Members
限制一：drop的日志成员不能是当前组或者活动组的成员
限制二：drop之前确保日志成员已经归档，如果数据库是归档模式的话
限制三：drop的成员属于活动日志组，则必须 force a log switch
语句：ALTER DATABASE DROP LOGFILE MEMBER '/oracle/dbs/log3c.rdo';

七：Forcing Log Switches
语句：alter system switch logfile;

八： Clearing a Redo Log File
当数据库一直open时，可能会出现日志损坏，最终挂起数据库，alter database clear logfile 语句能够用于重新初始化这个日志文件。
ALTER DATABASE CLEAR LOGFILE GROUP 3;
如果该日志文件没有归档，则
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

九：oracle是如何切换日志的？

当前日志状态如下（ 注意红色部分，当前日志序列号最大是42，而日志组6中序列号最小为37）

SQL> select group#,sequence#,archived,status from v$log;

    GROUP#  SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
           1         38 YES INACTIVE
         2         40 YES INACTIVE
         3         41 YES INACTIVE
          4         42 NO  CURRENT
         5         39 YES INACTIVE
         6         37 YES INACTIVE
切换日志组：

SQL> alter system switch logfile;

System altered.

再查询日志组状态：
SQL> select group#,sequence#,archived,status from v$log;

    GROUP#  SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
         1         38 YES INACTIVE
         2         40 YES INACTIVE
         3         41 YES INACTIVE
         4         42 YES ACTIVE
         5         39 YES INACTIVE
         6         43 NO  CURRENT
总结：oracle总是找日志组中sequence#字段值为最小的日志组，做为下一个当前日志组。

十：重做日志恢复（见如下例1、例2、例3）

例1：数据库处于open状态，意外丢失inactive日志组所有成员

运行如下命令，不过该命令执行后，日志文件状态处于unuse状态，需要重启数据库 日志组才能真正生效。
ALTER DATABASE CLEAR LOGFILE GROUP 3;
或者重建日志文件
SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 3 '/oracle/CRM2/CRM/redo03.log' size 200m  reuse;

Database altered.

SQL> select group#,archived,status from v$log;

    GROUP# ARC STATUS
---------- --- ----------------
         1 YES INACTIVE
         2 NO  CURRENT
         3 YES UNUSED

例2：如果数据库关闭，再open时出现日志组所有成员丢失的情况有如下两种情景：
情景一：非活动日志组所有成员全部损毁
关闭前日志状态如下
SQL> select group#,archived,status from v$log;

    GROUP# ARC STATUS
---------- --- ----------------
         1 YES INACTIVE
         2 YES INACTIVE
          3 NO  CURRENT
当数据库关闭后，打开数据库提示
SQL> startup
ORACLE instance started.

Total System Global Area  322961408 bytes
Fixed Size                  2020480 bytes
Variable Size              92277632 bytes
Database Buffers          222298112 bytes
Redo Buffers                6365184 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/oracle/CRM2/CRM/redo01.log'
当前数据库状态
SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED
当前日志状态如下所示：
SQL> select group#,archived,status from v$log;

    GROUP# ARC STATUS
---------- --- ----------------
          1 YES INACTIVE
         3 NO  CURRENT
         2 YES INACTIVE
查询redo01.log所在的日志组如下所示：
SQL> select group#,member from v$logfile;

    GROUP# MEMBER
---------- ----------------------------------------
         3 /oracle/CRM2/CRM/redo03.log
         2 /oracle/CRM2/CRM/redo02.log
          1 /oracle/CRM2/CRM/redo01.log
         2 /oracle/CRM2/CRM/redo2b.log
运行alter database clear logfile group 1 重新初始化创建日志组1如下所示：
SQL> alter database clear logfile group 1;

Database altered.
重新打开数据库如下：
SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

情景二：数据库open时当前联机日志文件丢失

SQL> startup
ORACLE instance started.

Total System Global Area  322961408 bytes
Fixed Size                  2020480 bytes
Variable Size              92277632 bytes
Database Buffers          222298112 bytes
Redo Buffers                6365184 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/oracle/CRM2/CRM/redo01.log'

查询当前数据库日志状态发现当前日志组1所有成员丢失如下所示：
SQL> select group#,archived,status from v$log;

    GROUP# ARC STATUS
---------- --- ----------------
          1 NO  CURRENT
         3 YES INACTIVE
         2 YES INACTIVE

SQL> select group#,member from v$logfile;

    GROUP# MEMBER
---------- ----------------------------------------
         3 /oracle/CRM2/CRM/redo03.log
         2 /oracle/CRM2/CRM/redo02.log
          1 /oracle/CRM2/CRM/redo01.log
         2 /oracle/CRM2/CRM/redo2b.log
此时数据库启动状态如下：   
SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED
此时若执行 clear unarchived logfile会报如下错误：
SQL> alter database clear unarchived logfile group 1;
alter database clear unarchived logfile group 1
*
ERROR at line 1:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/oracle/CRM2/CRM/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

由于关闭数据库之前数据文件处于一致性状态所以执行基于取消的不完全恢复。
SQL>  recover database until cancel;
Media recovery complete.

用resetlogs打开数据库时自动创建丢失的日志文件
SQL>  alter database open resetlogs;

Database altered.

SQL> select group#,member from v$logfile;

    GROUP# MEMBER
---------- ----------------------------------------
         3 /oracle/CRM2/CRM/redo03.log
         2 /oracle/CRM2/CRM/redo02.log
         1 /oracle/CRM2/CRM/redo01.log
         2 /oracle/CRM2/CRM/redo2b.log
例3：数据库处于open状态意外丢失当前联机日志

当前数据库日志状态如下：
SQL> select group#,archived,status from v$log;

    GROUP# ARC STATUS
---------- --- ----------------
         1 NO  CURRENT
         2 YES INACTIVE
         3 YES INACTIVE
删除当前日志如下
[root@oracle CRM]# rm -rf redo01.log
切换当前日志组，触发报错，错误信息位于alert文件中如下：
SQL> alter system switch logfile;
System altered.
ORA-16038: log 1 sequence# 20 cannot be archived
ORA-00312: online log 1 thread 1: '/oracle/CRM2/CRM/redo01.log'
Wed Oct 17 01:50:35 2012

SQL> alter database clear unarchived logfile group 1;   （ 注意这里，如果是非当前日志组丢失则删除日志组后直接重建即可，不需要运行此命令处理）

Database altered.

SQL> select group#,archived,status from v$Log;

    GROUP# ARC STATUS
---------- --- ----------------
         1 YES UNUSED
         2 YES INACTIVE
         3 NO  CURRENT
   
此时需要注意：
1  如果直接drop当前日志组直接重建会报如下错误
SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-00350: log 1 of instance CRM (thread 1) needs to be archived
ORA-00312: online log 1 thread 1: '/oracle/CRM2/CRM/redo01.log'
2  clear unarchived logfile group 1 后，日志组1数据库还不能使用，需要重新启动数据库或者重建日志组1
重建步骤如下：
SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database  add logfile group 1 '/oracle/CRM2/CRM/redo01.log' size 200m reuse;

Database altered.