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

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'

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

和归档都是没有的。

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

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;

Database altered.

过往文章目录

祖仙教

理科精华

有问题请留言--->