重做日志文件丢失导致无法打开数据库

来源：互联网发布：天融信网络管理系统编辑：程序博客网时间：2024/06/13 23:53

1.模拟重做日志文件丢失:

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/home/oracle/data/oradata/zxin/redo01.log
/home/oracle/data/oradata/zxin/redo02.log
/home/oracle/data/oradata/zxin/redo03.log

SQL>

删除重做日志文件:

SQL> ho rm /home/oracle/data/oradata/zxin/redo0*

SQL> host
oracle@linux:~/data/oradata/zxin> ls *.log

ls: cannot access *.log: No such file or director

重做日志文件已经删除了，我们尝试重启数据库:

如图: 找不到redo02.log重做日志文件，我们如何恢复呢:

打开系统隐藏参数:

SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;

System altered.

SQL>

重启数据库并强制到mount状态:

SQL> startup force mount;
ORACLE instance started.

Total System Global Area 1375731712 bytes
Fixed Size                              2083848 bytes
Variable Size                      402654200 bytes
Database Buffers                956301312 bytes
Redo Buffers                         14692352 bytes
Database mounted.

SQL> recover database using backup controlfile;//直接回车
ORA-00279: change 1999797 generated at 11/03/2016 10:03:03 needed for thread 1
ORA-00289: suggestion :
/home/oracle/product/10.2.0/db_1/dbs/arch1_2_926891233.dbf
ORA-00280: change 1999797 for thread 1 is in sequence #2

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

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

SQL> alter database open resetlogs;

System altered.

到此，已经解决了日志丢失的问题，我们还需要把隐藏参数给关掉:

SQL> show parameter "_allow_resetlogs_corruption";

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_allow_resetlogs_corruption boolean TRUE
SQL>

SQL> alter system reset "_allow_resetlogs_corruption" scope=spfile sid='*';

System altered.

SQL>

重启一下数据库:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 1375731712 bytes
Fixed Size                               2083848 bytes
Variable Size                       402654200 bytes
Database Buffers                956301312 bytes
Redo Buffers                         14692352 bytes
Database mounted.
Database opened.
SQL>

再看一下目录下的重做日志文件:

oracle@linux:~/data/oradata/zxin> ll *.log
-rw-r----- 1 oracle dba 52429312 2016-11-03 10:42 redo01.log
-rw-r----- 1 oracle dba 52429312 2016-11-03 10:42 redo02.log
-rw-r----- 1 oracle dba 52429312 2016-11-03 10:42 redo03.log
oracle@linux:~/data/oradata/zxin>

0 0