只有数据文件情况下恢复数据库

只有数据文件情况下恢复数据库

 

1,创建几个表，进行 log switch 

SQL> create table t9 as select * from dba_tables;

Table created.

SQL> alter system switch logfile;

System altered.

SQL> create table t10 as select * from v$session;

Table created.

SQL>

2,杀 oracle 进程，模拟意外关闭

[oracle@g1 gg1]$ kill ‐9 3358

Alert.log

Sun Feb 03 17:26:09 2013

Errors in file /u01/app/oracle/diag/rdbms/gg1/gg1/trace/gg1_pmon_3342.trc:

ORA‐00471: DBWR process terminated with error

PMON (ospid: 3342): terminating the instance due to error 471

Instance terminated by PMON, pid = 3342

 

3,删除所有控制文件和日志文件

[oracle@g1 gg1]$ rm ‐fr *.ctl

[oracle@g1 gg1]$ rm ‐fr *.log

[oracle@g1 frank]$ rm ‐fr /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilefrank.ora 

[oracle@g1 frank]$ rm ‐fr /u01/app/oracle/product/11.2.0/dbhome_1/dbs/init.ora

试着启动一下

SQL> conn / as sysdba;

Connected to an idle instance.

SQL> startup;

ORA‐01078: failure in processing system parameters

LRM‐00109:  could  not  open  parameter  file

'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initfrank.ora'

找不到参数文件了

4,手动建一个参数文件，只要有 db_name 和 control_files 就可以了

db_name=frank

control_files='/home/oracle/control01.ctl','/home/oracle/control02.ctl'

 

SQL> startup nomount pfile=/home/oracle/tmp.ora;

ORACLE instance started.

 

Total System Global Area   217157632 bytes

Fixed Size                   2158264 bytes

Variable Size              159383880 bytes

Database Buffers            50331648 bytes ORACLE东东堂                                                     QQ:327356330 

Redo Buffers                 5283840 bytes

SQL>

 

可以 nomount 了，现在开始创建控制文件

 

5,如果 redo log也没有一定要改成 resetlogs

SQL> create controlfile reuse database 'frank' resetlogs noarchivelog

   2   maxdatafiles 100

   3   maxlogfiles 20

   4   maxlogmembers 5

   5   maxloghistory 100

   6   logfile

   7   group 1 '/home/oracle/redo01.log' size 100M reuse,

   8   group 2 '/home/oracle/redo02.log' size 100M reuse,

   9   group 3 '/home/oracle/redo03.log' size 100M reuse

  10   datafile

  11   '/u01/app/oracle/oradata/frank/sysaux01.dbf',

  12   '/u01/app/oracle/oradata/frank/system01.dbf',

  13   '/u01/app/oracle/oradata/frank/undotbs01.dbf',

  14   '/u01/app/oracle/oradata/frank/users01.dbf'

  15   character set AL32UTF8;

 

Control file created.

SQL> select status from v$instance;

 

STATUS

‐‐‐‐‐‐‐‐‐‐‐‐

MOUNTED

 

6,尝试打开数据库看看

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA‐01589: must use RESETLOGS or NORESETLOGS option for database open

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/gg1/system01.dbf'

 

需要做恢复，因为我是一手动杀进程关闭，这时候数据文件是不一致的，而且我的 redologORACLE东东堂                                                     QQ:327356330 

和归档都是没有的。

 

恢复肯定会报错的，试一下看

SQL> recover database using backup controlfile;

ORA‐00279: change 992964 generated at 02/03/2013 19:42:31 needed for thread 1

ORA‐00289: suggestion :

/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_5_806441953.dbf

ORA‐00280: change 992964 for thread 1 is in sequence #5

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

这个归档日志我根本就没有。

现在就采取手动推进 scn 的方式，让数据库以为可以使用当时的 redolog 就可以恢复，其实

我现在的 redolog也是控制的，只是这样骗过数据库

SQL> alter system set events '100015 trace name adjust_scn level 1';

System altered.

再恢复一下数据库试试看看

SQL> recover database using backup controlfile;

ORA‐00279: change 1155670 generated at 11/25/2012 00:41:09 needed for thread 1

ORA‐00289: suggestion :

/u01/app/oracle/product/11.1.7/db_1/dbs/arch1_23_791968117.dbf

ORA‐00280: change 1155670 for thread 1 is in sequence #23

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

cancel;

 

7,结果还是一样，在 tmp.ora 中增加隐含参数

_allow_resetlogs_corruption=true
 
SQL> startup mount pfile=/home/oracle/tmp.ora;

ORACLE instance started.

 
Total System Global Area   217157632 bytes

Fixed Size                   2158264 bytes

Variable Size              159383880 bytes

Database Buffers            50331648 bytes

Redo Buffers                 5283840 bytes

Database mounted.

SQL> alter database open resetlogs;

 ORACLE东东堂                                                     QQ:327356330 

Database altered.

注:其他的版本可能还有报 ora‐01555错误,那就再推进一次

alter system set events '100015 trace name adjust_scn level 1';

其他的版本可能还有报 ora‐600 4194错误

这个就比较好办了，去掉 AUM.改成手动,

查看 alert.log中有报,

ORA‐00704: bootstrap process failure

ORA‐00704: bootstrap process failure

ORA‐00604: error occurred at recursive SQL level 1

ORA‐01555: snapshot too old: rollback segment number 7 with name "_SYSSMU7_1221203537$"

too small

Error 704 happened during db open, shutting down database

USER (ospid: 3724): terminating the instance due to error 704

Instance terminated by USER, pid = 3724

ORA‐1092 signalled during: alter database open resetlogs...

ORA‐1092 : opiodr aborting process unknown ospid (3724_47703483198656)

Sun Feb 03 17:56:34 2013

ORA‐1092 : opitsk aborting process
alter system set events '100015 trace name adjust_scn level 1';

SQL> recover database;

ORA‐00283: recovery session canceled due to errors

ORA‐16433: The database has not been opened in read‐write mode

碰到了一个 11.1.0.7  的bug ,悲剧.不能恢复了.