oracle redo log 操作和故障处理 1.ADD LOG GROUP: 只增加一个日志成员的写法,用此种方法以后一定要再至少创建一个日志成员: ALTER DATABASE [database] ADD LOGFILE GROUP integer filespec SIZE size [REUSE] [,GROUP integer filespec SIZE size [REUSE] ...] ; 同时增加多个日志成员的写法(注意多了个小括号): ALTER DATABASE [database] ADD LOGFILE GROUP integer ( filespec , filespec , filespec ...] ) SIZE size [REUSE]; 说明: --- filespec : 包括地址的全文件名 --- 注意可以同时创建多个文件 --- REUSE: 表示如果已经存在此文件就重用它! eg: alter database add logfile group 5 '/opt/oracle/oradata/dbtest/redo05_1.log' SIZE 10M; alter database add logfile group 5 '/opt/oracle/oradata/dbtest/redo05_1.log' size 10m reuse; alter database add logfile group 6 '/opt/oracle/oradata/dbtest/redo06_1.log' size 10m, group 7 '/opt/oracle/oradata/dbtest/redo07_1.log' size 10m; alter database add logfile group 8 ('/opt/oracle/oradata/dbtest/redo08_1.log','/opt/oracle/oradata/dbtest/redo08_2.log') size 10m; alter database add logfile group 5 ('/opt/oracle/oradata/dbtest/redo05_1.log','/opt/oracle/oradata/dbtest/redo05_2.log') size 10m reuse, group 6 '/opt/oracle/oradata/dbtest/redo06_1.log' size 10m;
--------------------------------------------------------------------- 2.ADD LOG MEMBER: ALTER DATABASE [database] ADD LOGFILE MEMBER 'filename' [REUSE] [, 'filename' [REUSE]...] TO GROUP integer | ( all_file_in_the_group_spec ); 说明: --- filespec : 包括地址的全文件名 --- REUSE: 表示如果已经存在此文件就重用它! eg: alter database add logfile member '/opt/oracle/oradata/dbtest/redo04_3.log' to group 4;
alter database add logfile member '/opt/oracle/oradata/dbtest/redo02_3.log','/opt/oracle/oradata/dbtest/redo02_4.log' to group 2;
alter database add logfile member '/opt/oracle/oradata/dbtest/redo04_4.log' to ('/opt/oracle/oradata/dbtest/redo04_1.log','/opt/oracle/oradata/dbtest/redo04_2.log','/opt/oracle/oradata/ dbtest/redo04_3.log');
3.DROP LOG GROUP: ALTER DATABASE [database] DROP LOGFILE GROUP integer | ( all_file_in_the_group_spec ) [,GROUP integer | ( all_file_in_the_group_spec ) ... ]; 说明: --- An instance requires at least two groups of online redo log files.(每个实例至少要有两个组) --- An active or current group cannot be dropped.(一个处于active或current状态的组不能被删除) --- When an online redo log group is dropped, the operating system files are not deleted. (所用的删除操作是指删除数据库中的信息,在操作系统中不删除。) --- all_file_in_the_group_spec : 此日志组包括的所有的成员的全文件名 --- 当删除用的是drop logfile group 5的时候,则group 5中的所有成员也同样在数据库中被删除 eg: alter database drop logfile group 5; alter database drop logfile ('/opt/oracle/oradata/dbtest/redo05_1.log','/opt/oracle/oradata/dbtest/redo05_2.log') alter database drop logfile group 6 , ('/opt/oracle/oradata/dbtest/redo05_1.log','/opt/oracle/oradata/dbtest/redo05_2.log');
---------------------------------------------------------- oracle redo日志恢复 如果数据库是正常shutdown,非当前日志都可以直接clear来重新生成,而且不丢失数据,因为正常关闭db,数据已经写入dbf文件了。唯独当前日志不可以,当前日志必须用其他方法恢复,不管是不是正常关闭, select * from v$log; alter database clear logfile group 1; 如果数据库是正常关闭的,用recover database until cancel可以轻松恢复或者说重新建立所有的redo,不再区分是否是当前日志,而且由于正常关闭,不会丢失任何数据,唯一可能丢失的情况就是如果日志还没有归档. recover database until cancel 继续用Resetlogs方法打开数据库,其实就是根据控制文件让系统自动重新生成redo,如果noresetlog的话,就不会重新生成redo,缺少了文件,db自然无法启动,这种恢复方法 由于要resetlogs,所以在恢复完成后,日志清零,以前的备份不再起作用,所以建议立即备份 alter database open resetlogs;
----------------------------------------------------------------- oracle redo日志故障处理 我们知道,当数据库发生日志切换时(Log Switch),Oracle会触发一个检查点(Checkpoint),检查点进程(Checkpoint Process,CKPT)会通知DBWR(Database?Writer)进程去执行写操作。在日志文件所保护的处于Buffer cache中的脏数据(dirty buffer)未写回磁盘之前,日志文件不能被覆盖或重用。如果数据库异常繁忙,或者DBWR的写出过慢,就可能出现检查点未完成,Oracle却已经用完所有日志文件的情况。在这种情况下,数据库的日志无法生成,整个数据库将处于停顿状态,此时日志文件中会记录类似如下信息:"Thread 1 cannot allocate new log",表明系统的checkpoint 没有来得及完成,也就是说 buffer cache 中的dirty data还没有完全写到数据文件,就已经有大量的日志需要写入到系统。而系统只能通知应用:checkpoint 还没有完成,你只能等待。这个时候,系统就基本处于hang 状态了 下面我们来看看日志的实时刷新信息 [root@radius ~]# tail -f $ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log Thu Jan 6 22:26:35 2011 Thread 1 advanced to log sequence 151078 (LGWR switch) Current log# 3 seq# 151078 mem# 0: /ora/oradata/radius/redo03.log Thu Jan 6 22:27:08 2011 Thread 1 cannot allocate new log, sequence 151079 Checkpoint not complete Current log# 3 seq# 151078 mem# 0: /ora/oradata/radius/redo03.log Thu Jan 6 22:27:12 2011 Thread 1 advanced to log sequence 151079 (LGWR switch) Current log# 1 seq# 151079 mem# 0: /ora/oradata/radius/redo01.log Thu Jan 6 22:27:45 2011 Thread 1 cannot allocate new log, sequence 151080 Checkpoint not complete Current log# 1 seq# 151079 mem# 0: /ora/oradata/radius/redo01.log 可以看出切换日志间隔不到一分钟,增加日志组容量和数据刻不容缓呐
1.查询下当前redo log 情况 SQL> select group#,status,archived,bytes/1024/1024 from v$log; GROUP# STATUS ARC BYTES/1024/1024 ---------- ---------------- --- --------------- 1 INACTIVE YES 50 2 CURRENT NO 50 3 UNUSED YES 50 可以看出有3组50M redo log
2.根据目前的切换日志的频率,决定将日志组增加至5组,每组200M. SQL> alter database add logfile group 4 '/ora/oradata/radius/redo04.log' size 200m; Database altered. SQL> alter database add logfile group 5 '/ora/oradata/radius/redo05.log' size 200m; Database altered. SQL> alter database add logfile group 6 '/ora/oradata/radius/redo06.log' size 200m; Database altered. SQL> alter database add logfile group 7 '/ora/oradata/radius/redo07.log' size 200m; Database altered. SQL> alter database add logfile group 8 '/ora/oradata/radius/redo08.log' size 200m; Database altered.
查询现在redo log状态 SQL> select group#,status,archived,bytes/1024/1024 from v$log; GROUP# STATUS ARC BYTES/1024/1024 ---------- ---------------- --- --------------- 1 INACTIVE YES 50 2 ACTIVE YES 50 3 CURRENT NO 50 4 UNUSED YES 200 5 UNUSED YES 200 6 UNUSED YES 200 7 UNUSED YES 200 8 UNUSED YES 200
3.删除之前的三个50M的redo log组 由于当前日志还在group# 3 redo log上,所以需要切换日志到其他的日志组中去. SQL> alter system switch logfile; System altered. SQL> select group#,status,archived,bytes/1024/1024 from v$log; GROUP# STATUS ARC BYTES/1024/1024 ---------- ---------------- --- --------------- 1 INACTIVE YES 50 2 ACTIVE YES 50 3 ACTIVE YES 50 4 CURRENT NO 200 5 UNUSED YES 200 6 UNUSED YES 200 7 UNUSED YES 200 8 UNUSED YES 200
从上面的status字段可以看出group# 2,3 redo log 的状态均是ACTIVE,也就是内存中的脏数据还没有写到数据文件中,这时oracle是不允许你删除的,如果你硬要删除会出现下面这个提示 SQL> alter database drop logfile group 2; alter database drop logfile group 2 * ERROR at line 1: ORA-01624: log 2 needed for crash recovery of instance radius (thread 1) ORA-00312: online log 2 thread 1: '/ora/oradata/radius/redo02.log'
如果你需要马上删除这个redo log ,你可以使用checkpoint来将脏数据写进数据文件(磁盘)中,之后再将group# 1,2,3的redo log一一删除。
SQL> alter system checkpoint; System altered. SQL> alter database drop logfile group 1; Database altered. SQL> alter database drop logfile group 2; Database altered. SQL> alter database drop logfile group 3; Database altered.
查看当前redo log状态 SQL> select group#,status,archived,bytes/1024/1024 from v$log; GROUP# STATUS ARC BYTES/1024/1024 ---------- ---------------- --- --------------- 4 CURRENT NO 200 5 INACTIVE YES 200 6 INACTIVE YES 200 7 INACTIVE YES 200 8 ACTIVE YES 200
4.删除不可用的redo log 上一步相当于是逻辑上从Oracle中删除了几个redo log,这些redo log的物理文件仍然还在硬盘上躺着,需要使用Linux命令删除之。
|