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