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
--------------------------------------------------------------------------------
/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
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.
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.
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
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
'/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>
------------------------------------ ----------- ------------------------------
_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.
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>
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>
-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>