oracle 未归档redo丢失或损坏(active状态)

原创

wx63159e54cdd9a 2023-02-24 09:30:14 博主文章分类：特殊恢复 ©著作权

文章标签 oracle redo损坏 oracle未归档redo损坏 SQL Database oracle 文章分类 Python 后端开发

©著作权归作者所有：来自51CTO博客作者wx63159e54cdd9a的原创作品，请联系作者获取转载授权，否则将追究法律责任

2. 非归档模式下，非current redo丢失或损坏(active状态)
如果immediate关闭，可直接clear;
如果abort,需要不完全恢复
注意：归档模式方法一样

----Session 1

SQL> select * from v$Log;

GROUP#    THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---- ---------
   1       1         97   52428800    512    1     NO   ACTIVE    1628603    29-DEC-15    1628880 29-DEC-15
   2       1       95   20971520    512    1      NO   INACTIVE 1628590    29-DEC-15   1628594 29-DEC-15
   3       1       98   52428800    512     1      NO   CURRENT   1628880    29-DEC-15 2.8147E+14

---Session 2

root@zw_test_26_75 u01]# dd if=/dev/null of=/u01/app/oracle/oradata/orcl/redo01.log bs=512 count=10
0+0 records in
0+0 records out
0 bytes (0 B) copied, 0.000136094 s, 0.0 kB/s

SQL> shutdown abort; 注意是abort,如果是immediate可直接clear
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 952020992 bytes
Fixed Size      2258960 bytes
Variable Size    314574832 bytes
Database Buffers   629145600 bytes
Redo Buffers      6041600 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log'
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1

怎么情况怎么搞呢？

SQL> alter database clear logfile group 1;
alter database clear logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance orcl (thread 1)
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log'

SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance orcl (thread 1)
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log'

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log'
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1

这种情况能不能做完全恢复？

SQL> recover database until cancel;
ORA-00279: change 1628785 generated at 12/29/2015 14:57:25 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/product/11.2.0.4/db_1/dbs/arch1_97_896791384.dbf
ORA-00280: change 1628785 for thread 1 is in sequence #97

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/u01/app/oracle/product/11.2.0.4/db_1/dbs/arch1_97_896791384.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

ORA-00308: cannot open archived log '/u01/app/oracle/product/11.2.0.4/db_1/dbs/arch1_97_896791384.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'

关闭数据库
SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

重新重启
SQL> startup
ORACLE instance started.

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'

打开失败

创建一个pfile，添加隐含参数强制打开数据库
SQL> create pfile='/tmp/pfile.ora' from spfile;

File created.

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

pfile文件中添加如下隐含参数：

_allow_resetlogs_corruption: 强制启动数据库，设置此参数之后，在数据库Open过程中，Oracle会跳过某些一致性检查，从而使数据库可能跳过不一致状态；
_allow_error_simulation： 10g版本需要

*._allow_resetlogs_corruption=true
*._allow_error_simulation=true

SQL> startup mount pfile='/tmp/pfile.ora';
ORACLE instance started.

Database altered.

把pfile的隐含参数去掉，重新启动数据库创建spfile

SQL> create spfile from pfile='/tmp/pfile.ora';

File created.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> set linesize 200 pagesize 2000
SQL> select * from v$Log;

GROUP#   THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -----------------
1       1        4    52428800    512     1    NO CURRENT     1628892    29-DEC-15   2.8147E+14
2       1       2     20971520    512     1    NO INACTIVE 1628886    29-DEC-15   1628889     29-DEC-15
3      1       3      52428800    512     1    NO INACTIVE 1628889    29-DEC-15   1628892     29-DEC-15

可以看到redo的seq被重置了。