oracle 11g 没有sql area?,Oracle 11g日志文件恢复

这次实验模拟一下，Oracle 11g在非归档模式下，且没有备份的条件，举行的日志文件的恢复

这里所有的redo日志都被我删除了，下面是报错和数据库模式：

SQL> startup

ORACLE instance started.

Total System Global Area  845348864 bytes

Fixed Size                  1339796 bytes

Variable Size            645926508 bytes

Database Buffers          192937984 bytes

Redo Buffers                5144576 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/myorcl/redo1.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: '/u01/app/oracle/oradata/myorcl/redo01.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

SQL> select log_mode,open_mode from v$database;

LOG_MODE    OPEN_MODE

------------ --------------------

NOARCHIVELOG MOUNTED

首先，我们要用resetlogs的方式实验打开数据库：

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01139: RESETLOGS option only valid after an incomplete database recovery

提醒resetlogs这个选项只有在一个不完全数据库恢复后才可以使用，既然这样，我们就给它做一个数据库恢复：

SQL> recover database using backup controlfile;

ORA-00279: change 1736992 generated at 01/18/2014 18:01:56 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/product/11.2.0/db_1/ora_log/1_1_837194464.dbf

ORA-00280: change 1736992 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log

'/u01/app/oracle/product/11.2.0/db_1/ora_log/1_1_837194464.dbf'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

下面，我们再来resetlogs启动数据库:

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/u01/app/oracle/oradata/myorcl/system01.dbf'

提醒要举行介质恢复，由于我们基本没有redo日志，基本没有办法恢复，只能通过添加隐藏参数，让数据库忽略数据一致性验证：

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

System altered.

SQL> startup force mount;

ORACLE instance started.

Total System Global Area  845348864 bytes

Fixed Size                  1339796 bytes

Variable Size            645926508 bytes

Database Buffers          192937984 bytes

Redo Buffers                5144576 bytes

Database mounted.

到这里，再来添加resetlogs选项，来打开数据库：

SQL> alter database open resetlogs;

Database altered.

数据库打开了，然则还没有竣事，我们要将修改的隐藏参数修改到默认值，并重新启动数据库：

SQL> alter system reset "_allow_resetlogs_corruption" scope=spfile;

System altered.

SQL> startup force;

ORACLE instance started.

Total System Global Area  845348864 bytes

Fixed Size                  1339796 bytes

Variable Size            645926508 bytes

Database Buffers          192937984 bytes

Redo Buffers                5144576 bytes

Database mounted.

Database opened.

SQL> select KSPPINM,KSPPSTVL from x$ksppi a,x$ksppcv b where a.indx=b.indx and ksppinm like '%resetlogs%';

KSPPINM

--------------------------------------------------------------------------------

KSPPSTVL

--------------------------------------------------------------------------------

_no_recovery_through_resetlogs

FALSE

_allow_resetlogs_corruption

FALSE