日志组的某个日志成员损坏
如果没有数据库的全备份,也可以使用_allow_resetlogs_corruption隐藏参数进行恢复(不推荐)。
SQL> select group#,status,member from v$logfile;
GROUP# STATUS MEMBER
---------- ------- ----------------------------------------------------------------------
3 F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG
2 F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG
1 F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG
1 F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01_ADD.LOG
2 F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02_ADD.LOG
3 F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03_ADD.LOG
已选择6行。
--使用360强力删除REDO01_ADD.LOG文件,以模拟日志成员损坏
--多次切换日志,以使刚删除的redo文件处于invalid状态
SQL> alter system switch logfile;
系统已更改。
SQL> alter system switch logfile;
系统已更改。
......
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 ACTIVE
3 ACTIVE
SQL> select group#,status,member from v$logfile;
GROUP# STATUS MEMBER
---------- ------- ----------------------------------------------------------------------
3 F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG
2 F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG
1 F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG
1 INVALID F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01_ADD.LOG
2 F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02_ADD.LOG
3 F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03_ADD.LOG
已选择6行。
--如果为为当前日志组成员,则不能被删除,这时需要切换日志,才能够删除成员
SQL> alter database drop logfile member 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01_ADD.LOG';
alter database drop logfile member 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01_ADD.LOG'
*
第 1 行出现错误:
ORA-01609: 日志 1 是线程 1 的当前日志 - 无法删除成员
ORA-00312: 联机日志 1 线程 1: 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG'
ORA-00312: 联机日志 1 线程 1: 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01_ADD.LOG'
SQL> alter system switch logfile;
系统已更改。
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 ACTIVE
2 CURRENT
3 ACTIVE
SQL> alter database drop logfile member 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01_ADD.LOG';
数据库已更改。
--添加日志组成员
SQL> alter database add logfile member 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01_ADD_NEW.LOG' to group 1;
数据库已更改。
SQL> select group#,status,member from v$logfile;
GROUP# STATUS MEMBER
---------- ------- ----------------------------------------------------------------------
3 F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG
2 F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG
1 F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG
1 INVALID F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01_ADD_NEW.LOG
2 F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02_ADD.LOG
3 F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03_ADD.LOG
已选择6行。
注意,新增加的日志组成员状态也为invalid。
inactive日志组的所有日志成员全部损坏
1)open状态下inactive日志组的所有日志成员全部损坏
2)关闭状态下inactive日志组的所有日志成员全部损坏
1)
SQL> set linesize 110
SQL> column member for a70
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 CURRENT
3 INACTIVE
SQL> select group#,status,member from v$logfile;
GROUP# STATUS MEMBER
---------- ------- ----------------------------------------------------------------------
1 /oracle/10g/oracle/product/10.2.0/oradata/oralife/redo01.log
3 /oracle/10g/oracle/product/10.2.0/oradata/oralife/redo03.log
2 /oracle/10g/oracle/product/10.2.0/oradata/oralife/redo02.log
1 /oracle/10g/oracle/product/10.2.0/oradata/oralife/redo01_add.log
2 /oracle/10g/oracle/product/10.2.0/oradata/oralife/redo02_add.log
3 /oracle/10g/oracle/product/10.2.0/oradata/oralife/redo03_add.log
6 rows selected.
--使用rm删除日志组1的所有成员(redo01.log,redo01_add.log)
SQL> rm ...
--切换日志组为current状态
SQL> alter system switch logfile;
System altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 ACTIVE
3 INACTIVE
SQL> select group#,status,member from v$logfile;
GROUP# STATUS MEMBER
---------- ------- ----------------------------------------------------------------------
1 /oracle/10g/oracle/product/10.2.0/oradata/oralife/redo01.log
3 /oracle/10g/oracle/product/10.2.0/oradata/oralife/redo03.log
2 /oracle/10g/oracle/product/10.2.0/oradata/oralife/redo02.log
1 /oracle/10g/oracle/product/10.2.0/oradata/oralife/redo01_add.log
2 /oracle/10g/oracle/product/10.2.0/oradata/oralife/redo02_add.log
3 /oracle/10g/oracle/product/10.2.0/oradata/oralife/redo03_add.log
6 rows selected.
--继续切换日志组,归档日志组1
SQL> alter system switch logfile;
System altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 ACTIVE
2 ACTIVE
3 CURRENT
SQL> select group#,status,member from v$logfile;
GROUP# STATUS MEMBER
---------- ------- ----------------------------------------------------------------------
1 /oracle/10g/oracle/product/10.2.0/oradata/oralife/redo01.log
3 /oracle/10g/oracle/product/10.2.0/oradata/oralife/redo03.log
2 /oracle/10g/oracle/product/10.2.0/oradata/oralife/redo02.log
1 /oracle/10g/oracle/product/10.2.0/oradata/oralife/redo01_add.log
2 /oracle/10g/oracle/product/10.2.0/oradata/oralife/redo02_add.log
3 /oracle/10g/oracle/product/10.2.0/oradata/oralife/redo03_add.log
6 rows selected.
--查看告警日志文件出现以下信息,意思是找不到重做日志文件,无法对其进行归档,后台进程lgwr处于等待状态
Wed Aug 3 21:02:30 2011
Thread 1 advanced to log sequence 35
Current log# 3 seq# 35 mem# 0: /oracle/10g/oracle/product/10.2.0/oradata/oralife/redo03.log
Current log# 3 seq# 35 mem# 1: /oracle/10g/oracle/product/10.2.0/oradata/oralife/redo03_add.log
Wed Aug 3 21:02:30 2011
Errors in file /oracle/10g/oracle/product/10.2.0/db_1/admin/oralife/bdump/oralife_arc1_3514.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/oracle/10g/oracle/product/10.2.0/oradata/oralife/redo01_add.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 1 thread 1: '/oracle/10g/oracle/product/10.2.0/oradata/oralife/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Wed Aug 3 21:02:30 2011
ARC1: Failed to archive thread 1 sequence 34 (0)
ARCH: Archival stopped, error occurred. Will continue retrying
Wed Aug 3 21:02:30 2011
ORACLE Instance oralife - Archival Error
Wed Aug 3 21:02:30 2011
ORA-16038: log 1 sequence# 34 cannot be archived
ORA-00312: online log 1 thread 1: '/oracle/10g/oracle/product/10.2.0/oradata/oralife/redo01.log'
ORA-00312: online log 1 thread 1: '/oracle/10g/oracle/product/10.2.0/oradata/oralife/redo01_add.log'
Wed Aug 3 21:02:30 2011
Errors in file /oracle/10g/oracle/product/10.2.0/db_1/admin/oralife/bdump/oralife_arc1_3514.trc:
--清除该日志组以使后台进程lgwr继续工作
SQL> alter database clear unarchived logfile group 1;
--以上命令会重建日志组1的所有成员,此时后台进程可以继续工作。
--因为日志组内容没有被归档,导致过去的数据文件备份不能使用,应该备份数据库。
--查看告警日志文件出现:
Wed Aug 3 22:04:44 2011
WARNING! CLEARING REDO LOG WHICH HAS NOT BEEN ARCHIVED. BACKUPS TAKEN
BEFORE 08/03/2011 22:02:45 (CHANGE 874003) CANNOT BE USED FOR RECOVERY.
--继续切换日志组,日志组1进入不了current状态,原因是日志组1无法归档,这里所有的操作都是在open状态下。
日志信息:
Wed Aug 3 22:24:55 2011
Errors in file /oracle/10g/oracle/product/10.2.0/db_1/admin/oralife/bdump/oralife_arc1_4803.trc:
ORA-00314: log 1 of thread 1, expected sequence# 56 doesn't match 0
ORA-00312: online log 1 thread 1: '/oracle/10g/oracle/product/10.2.0/oradata/oralife/redo01_add.log'
ORA-00314: log 1 of thread 1, expected sequence# 56 doesn't match 0
ORA-00312: online log 1 thread 1: '/oracle/10g/oracle/product/10.2.0/oradata/oralife/redo01.log'
Wed Aug 3 22:24:55 2011
ARC1: Failed to archive thread 1 sequence 56 (0)
--无奈shutdown数据库,然后startup
SQL> startup
ORACLE instance started.
Total System Global Area 528482304 bytes
Fixed Size 1220360 bytes
Variable Size 159383800 bytes
Database Buffers 360710144 bytes
Redo Buffers 7168000 bytes
Database mounted.
ORA-00314: log 1 of thread 1, expected sequence# doesn't match
ORA-00312: online log 1 thread 1: '/oracle/10g/oracle/product/10.2.0/oradata/oralife/redo01.log'
ORA-00312: online log 1 thread 1: '/oracle/10g/oracle/product/10.2.0/oradata/oralife/redo01_add.log'
SQL> alter database clear unarchived logfile group 1;
Database altered.
SQL> alter database open;
Database altered.
--此时可以任意切换日志组
SQL> alter system switch logfile;
注意:alter database clear unarchived logfile group n之后要备份数据库。
2)
--在数据库关闭状态下使用rm删除日志组1的所有日志成员(redo01.log,redo01_add.log),来模拟日志组1的所有日志成员全部损坏。
--启动数据库。
SQL> startup
ORACLE instance started.
Total System Global Area 528482304 bytes
Fixed Size 1220360 bytes
Variable Size 159383800 bytes
Database Buffers 360710144 bytes
Redo Buffers 7168000 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/oracle/10g/oracle/product/10.2.0/oradata/oralife/redo01.log'
ORA-00312: online log 1 thread 1: '/oracle/10g/oracle/product/10.2.0/oradata/oralife/redo01_add.log'
SQL> select group#,status,archived from v$log;
GROUP# STATUS ARC
---------- ---------------- ---
1 INACTIVE YES --inactive
3 INACTIVE YES
2 CURRENT NO
--添加新日志组,将原来的日志组1删除。
SQL> alter database add logfile
2 ('/oracle/10g/oracle/product/10.2.0/oradata/oralife/redo04.log','/oracle/10g/oracle/product/10.2.0/oradata/oralife/redo04_add.log')
3 size 50M;
Database altered.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database open;
Database altered.
current状态日志组的所有日志成员损坏
1)open状态下当前日志组成员全部出现介质失败,后台进程lgwr将事务写入该日志组时,oracle实例会终止。
2)关闭状态下当前日志组全部成员出现介质失败,oracle实例不能打开;因为数据库关闭后,数据文件与控制文件处于完全一致状态,只需使用recover database until cancel执行基于取消的不完全恢复,然后使用resetlogs打开数据库,这时oracle会自动重新建立日志组所有成员。使用resetlogs打开数据库后,应该备份数据文件与控制文件。
2)关闭状态
SQL> select group#,status from v$log;
GROUP# STATUS
---------- --------------------------------
1 INACTIVE
2 INACTIVE
3 CURRENT
SQL> select group#,status,member from v$logfile;
GROUP# STATUS MEMBER
---------- -------------- -------------------------------------------------------------------------------
3 F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG
2 F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG
1 F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG
1 F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01_ADD_NEW.LOG
2 F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02_ADD.LOG
3 F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03_ADD.LOG
已选择6行。
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
--手动删除日志组3的所有日志成员(REDO03.LOG,REDO03_ADD.LOG),启动数据库。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 293604228 bytes
Database Buffers 310378496 bytes
Redo Buffers 7135232 bytes
数据库装载完毕。
ORA-00313: 无法打开日志组 3 (用于线程 1) 的成员
ORA-00312: 联机日志 3 线程 1: 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG'
ORA-00312: 联机日志 3 线程 1: 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03_ADD.LOG'
--基于取消的恢复。
SQL> recover database until cancel;
完成介质恢复。
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01589: 要打开数据库则必须使用 RESETLOGS 或 NORESETLOGS 选项
--使用resetlogs打开数据库,应该备份数据库。
SQL> alter database open resetlogs;
数据库已更改。
1)open状态
SQL> select group#,status,archived from v$log;
GROUP# STATUS ARC
---------- ---------------- ---
1 CURRENT NO
2 INACTIVE YES
3 INACTIVE YES
SQL> create table t (text varchar(1000));
表已创建。
SQL> insert into t values('ajax');
已创建 1 行。
SQL> commit;
提交完成。
--切换日志,使其归档。
SQL> alter system switch logfile;
系统已更改。
SQL> select group#,status,archived from v$log;
GROUP# STATUS ARC
---------- ---------------- ---
1 ACTIVE YES
2 CURRENT NO
3 INACTIVE YES
SQL> insert into t values('java');
已创建 1 行。
SQL> commit;
提交完成。
--切换日志,使其归档。
SQL> alter system switch logfile;
系统已更改。
SQL> select group#,status,archived from v$log;
GROUP# STATUS ARC
---------- ---------------- ---
1 ACTIVE YES
2 ACTIVE YES
3 CURRENT NO
--删除日志组3的所有日志成员。
SQL> insert into t values('spring');
insert into t values('spring')
*
第 1 行出现错误:
ORA-03113: 通信通道的文件结束
--重新连接例程,启动到mount状态。
SQL> conn / as sysdba
已连接到空闲例程。
SQL> startup mount;
ORACLE 例程已经启动。
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 201329540 bytes
Database Buffers 402653184 bytes
Redo Buffers 7135232 bytes
数据库装载完毕。
--拷贝所有的数据文件备份到目标目录。
cp..
--基于取消的不完全恢复,先恢复归档日志,然后再cancel。
SQL> recover database until cancel
ORA-00279: 更改 %s (在 %s 生成) 对于线程 %s 是必需的
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: 更改 %s (在 %s 生成) 对于线程 %s 是必需的
ORA-00279: 更改 %s (在 %s 生成) 对于线程 %s 是必需的
ORA-00308: 无法打开归档日志 '%s'
SQL> recover database until cancel
ORA-00279: 更改 %s (在 %s 生成) 对于线程 %s 是必需的
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
介质恢复已取消。
SQL> desc t
ERROR:
ORA-04043: 对象 %s 不存在
SQL> select group#,status,archived from v$log;
GROUP# STATUS ARC
---------- ---------------- ---
1 ACTIVE YES
3 CURRENT NO
2 ACTIVE YES
--使用resetlogs打开数据库,备份数据库
SQL> alter database open resetlogs;
数据库已更改。
SQL> desc t;
名称 是否为空? 类型
----------------------------------------------------------- -------- ----------------
TEXT VARCHAR2(1000)
SQL> select * from t;
TEXT
--------------------------------------------------------------------------------------
ajax
java
数据还在,归档日志已经恢复。
如果没有数据库的全备份,也可以使用_allow_resetlogs_corruption隐藏参数进行恢复(不推荐)。