重做日志有以下几种状态(主要看前三种),如下:
CURRENT:此状态表示正在被LGWR使用的日志组;
ACTIVE:重做日志组中仍含有实例恢复所需的重做数据;
INACTIVE:表示实例恢复不再需要此日志组,可以覆盖;
UNUSED:未使用;
CLEARING:对已存在的日志组执行clear操作的后的状态;
CLEARING_CURRENT:日志正在清空。当清空出错时,该日志组被置于这种状态。
一般情况下,我们只能查询到如下三种状态:
SYS@PROD1>select group#,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 2 CURRENT
2 2 INACTIVE
3 2 ACTIVE
一.数据库联机的情况下,删除INACTIVE日志组进行恢复
1.查看当前日志组状态
SYS@PROD1>select group#,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 2 INACTIVE
2 2 CURRENT
3 2 UNUSED
SYS@PROD1>col member for a50;
SYS@PROD1>select group#,member from v$logfile order by 1;
GROUP# MEMBER
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/PROD1/redo01.log
1 /u01/app/oracle/oradata/PROD1/redo01_a.log
2 /u01/app/oracle/oradata/PROD1/redo02_a.log
2 /u01/app/oracle/oradata/PROD1/redo02.log
3 /u01/app/oracle/oradata/PROD1/redo03_a.log
3 /u01/app/oracle/oradata/PROD1/redo03.log
6 rows selected.
2.操作系统层删除第一组日志组(第一组为inactive)
SYS@PROD1>! rm /u01/app/oracle/oradata/PROD1/redo01.log
SYS@PROD1>! rm /u01/app/oracle/oradata/PROD1/redo01_a.log
SYS@PROD1>! ls /u01/app/oracle/oradata/PROD1/redo*
/u01/app/oracle/oradata/PROD1/redo02_a.log /u01/app/oracle/oradata/PROD1/redo03_a.log
/u01/app/oracle/oradata/PROD1/redo02.log /u01/app/oracle/oradata/PROD1/redo03.log
3. 发现丢失后,没等数据库反应过来呢,就立即恢复
SYS@PROD1>alter database drop logfile group 1;
Database altered.
SYS@PROD1>select group#,members,status,bytes/1024/1024 m from v$log;
GROUP# MEMBERS STATUS M
---------- ---------- ---------------- ----------
2 2 CURRENT 50
3 2 UNUSED 50
SYS@PROD1>alter database add logfile group 1 ('/u01/app/oracle/oradata/PROD1/redo01.log','/u01/app/oracle/oradata/PROD1/redo01_a.log') size 50m;
Database altered.
SYS@PROD1>select group#,members,status,bytes/1024/1024 m from v$log;
GROUP# MEMBERS STATUS M
---------- ---------- ---------------- ----------
1 2 UNUSED 50
2 2 CURRENT 50
3 2 UNUSED 50
4.恢复完成
SYS@PROD1>select group#,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 2 CURRENT
2 2 ACTIVE
3 2 UNUSED
SYS@PROD1>alter system switch logfile;
SYS@PROD1>select group#,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 2 ACTIVE
2 2 ACTIVE
3 2 CURRENT
自测:数据库处于归档模式,数据库联机的情况下,丢失了一个INACTIVE状态的日志文件,此时数据库还在使用,
读者可以模拟建表、插入数据,切换日志,当然也可以切换到丢失的日志组,多切换几次,此时数据库会hang住,原因是日志无法归档,
二.删除INACTIVE日志组,重启数据库后恢复
1.查看当前日志组状态
SYS@PROD1>alter system checkpoint;
System altered.
SYS@PROD1>select group#,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 2 INACTIVE
2 2 CURRENT
3 2 INACTIVE
2.删除第3组日志
SYS@PROD1>! rm /u01/app/oracle/oradata/PROD1/redo03*
SYS@PROD1>! ls /u01/app/oracle/oradata/PROD1/redo03*
ls: /u01/app/oracle/oradata/PROD1/redo03*: No such file or directory
3.重启数据库
SYS@PROD1>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
一致性关闭没问题,启动的时候,只能启动到mount状态(其实只是看着应该是mount状态,其实数据库目前的状态shutdown状态),
open时报错,并没有明确的指明是因为丢失了日志文件导致的数据库open不了(oracle10g中会明确的指明)
SYS@PROD1>startup;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 520096848 bytes
Database Buffers 310378496 bytes
Redo Buffers 2371584 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 25417
Session ID: 1 Serial number: 5
4.查看告警日志
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/PROD1/redo03.log'
ORA-27037: unable to obtain file status
5.对3号日志组执行clear操作,open数据库
[oracle@host01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Dec 17 10:36:19 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SYS@PROD1>startup mount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 520096848 bytes
Database Buffers 310378496 bytes
Redo Buffers 2371584 bytes
Database mounted.
SYS@PROD1>alter database clear logfile group 3;
Database altered.
(clear的动作相当于将在操作系统层重建了第三组日记,且里面没有记录,也就是没有使用过unused)
SYS@PROD1>alter database open;
Database altered.
6.查看日志组状态
SYS@PROD1>select group#,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 2 INACTIVE
2 2 CURRENT
3 2 UNUSED
SYS@PROD1>! ls /u01/app/oracle/oradata/PROD1/redo*
/u01/app/oracle/oradata/PROD1/redo01_a.log /u01/app/oracle/oradata/PROD1/redo02.log
/u01/app/oracle/oradata/PROD1/redo01.log /u01/app/oracle/oradata/PROD1/redo03_a.log
/u01/app/oracle/oradata/PROD1/redo02_a.log /u01/app/oracle/oradata/PROD1/redo03.log
CURRENT:此状态表示正在被LGWR使用的日志组;
ACTIVE:重做日志组中仍含有实例恢复所需的重做数据;
INACTIVE:表示实例恢复不再需要此日志组,可以覆盖;
UNUSED:未使用;
CLEARING:对已存在的日志组执行clear操作的后的状态;
CLEARING_CURRENT:日志正在清空。当清空出错时,该日志组被置于这种状态。
一般情况下,我们只能查询到如下三种状态:
SYS@PROD1>select group#,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 2 CURRENT
2 2 INACTIVE
3 2 ACTIVE
一.数据库联机的情况下,删除INACTIVE日志组进行恢复
1.查看当前日志组状态
SYS@PROD1>select group#,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 2 INACTIVE
2 2 CURRENT
3 2 UNUSED
SYS@PROD1>col member for a50;
SYS@PROD1>select group#,member from v$logfile order by 1;
GROUP# MEMBER
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/PROD1/redo01.log
1 /u01/app/oracle/oradata/PROD1/redo01_a.log
2 /u01/app/oracle/oradata/PROD1/redo02_a.log
2 /u01/app/oracle/oradata/PROD1/redo02.log
3 /u01/app/oracle/oradata/PROD1/redo03_a.log
3 /u01/app/oracle/oradata/PROD1/redo03.log
6 rows selected.
2.操作系统层删除第一组日志组(第一组为inactive)
SYS@PROD1>! rm /u01/app/oracle/oradata/PROD1/redo01.log
SYS@PROD1>! rm /u01/app/oracle/oradata/PROD1/redo01_a.log
SYS@PROD1>! ls /u01/app/oracle/oradata/PROD1/redo*
/u01/app/oracle/oradata/PROD1/redo02_a.log /u01/app/oracle/oradata/PROD1/redo03_a.log
/u01/app/oracle/oradata/PROD1/redo02.log /u01/app/oracle/oradata/PROD1/redo03.log
3. 发现丢失后,没等数据库反应过来呢,就立即恢复
SYS@PROD1>alter database drop logfile group 1;
Database altered.
SYS@PROD1>select group#,members,status,bytes/1024/1024 m from v$log;
GROUP# MEMBERS STATUS M
---------- ---------- ---------------- ----------
2 2 CURRENT 50
3 2 UNUSED 50
SYS@PROD1>alter database add logfile group 1 ('/u01/app/oracle/oradata/PROD1/redo01.log','/u01/app/oracle/oradata/PROD1/redo01_a.log') size 50m;
Database altered.
SYS@PROD1>select group#,members,status,bytes/1024/1024 m from v$log;
GROUP# MEMBERS STATUS M
---------- ---------- ---------------- ----------
1 2 UNUSED 50
2 2 CURRENT 50
3 2 UNUSED 50
4.恢复完成
SYS@PROD1>select group#,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 2 CURRENT
2 2 ACTIVE
3 2 UNUSED
SYS@PROD1>alter system switch logfile;
SYS@PROD1>select group#,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 2 ACTIVE
2 2 ACTIVE
3 2 CURRENT
自测:数据库处于归档模式,数据库联机的情况下,丢失了一个INACTIVE状态的日志文件,此时数据库还在使用,
读者可以模拟建表、插入数据,切换日志,当然也可以切换到丢失的日志组,多切换几次,此时数据库会hang住,原因是日志无法归档,
二.删除INACTIVE日志组,重启数据库后恢复
1.查看当前日志组状态
SYS@PROD1>alter system checkpoint;
System altered.
SYS@PROD1>select group#,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 2 INACTIVE
2 2 CURRENT
3 2 INACTIVE
2.删除第3组日志
SYS@PROD1>! rm /u01/app/oracle/oradata/PROD1/redo03*
SYS@PROD1>! ls /u01/app/oracle/oradata/PROD1/redo03*
ls: /u01/app/oracle/oradata/PROD1/redo03*: No such file or directory
3.重启数据库
SYS@PROD1>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
一致性关闭没问题,启动的时候,只能启动到mount状态(其实只是看着应该是mount状态,其实数据库目前的状态shutdown状态),
open时报错,并没有明确的指明是因为丢失了日志文件导致的数据库open不了(oracle10g中会明确的指明)
SYS@PROD1>startup;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 520096848 bytes
Database Buffers 310378496 bytes
Redo Buffers 2371584 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 25417
Session ID: 1 Serial number: 5
4.查看告警日志
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/PROD1/redo03.log'
ORA-27037: unable to obtain file status
5.对3号日志组执行clear操作,open数据库
[oracle@host01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Dec 17 10:36:19 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SYS@PROD1>startup mount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 520096848 bytes
Database Buffers 310378496 bytes
Redo Buffers 2371584 bytes
Database mounted.
SYS@PROD1>alter database clear logfile group 3;
Database altered.
(clear的动作相当于将在操作系统层重建了第三组日记,且里面没有记录,也就是没有使用过unused)
SYS@PROD1>alter database open;
Database altered.
6.查看日志组状态
SYS@PROD1>select group#,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 2 INACTIVE
2 2 CURRENT
3 2 UNUSED
SYS@PROD1>! ls /u01/app/oracle/oradata/PROD1/redo*
/u01/app/oracle/oradata/PROD1/redo01_a.log /u01/app/oracle/oradata/PROD1/redo02.log
/u01/app/oracle/oradata/PROD1/redo01.log /u01/app/oracle/oradata/PROD1/redo03_a.log
/u01/app/oracle/oradata/PROD1/redo02_a.log /u01/app/oracle/oradata/PROD1/redo03.log
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31400681/viewspace-2130844/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31400681/viewspace-2130844/