只有DBF的恢复

只有dbf的恢复
灾难场景：
1. ctl，redo，spfile丢失，dbf未丢失，无备份
2. ctl，redo，spfile，dbf均丢失，有备份但无ctl备份

实验一：ctl，redo，spfile丢失，dbf未丢失，无备份，数据库正常关闭

SQL> shutdown immediate

[oracle@baobao ~]$ cd $ORACLE_HOME/dbs/

[oracle@baobao dbs]$ cat initdong.ora

*.control_files=‘/u01/app/oracle/oradata/dong/control01.ctl‘,‘/u01/app/oracle/flash_recovery_area/dong/control02.ctl‘#Restore 
Controlfile<BR>*.db_name=‘dong‘<BR>*._allow_resetlogs_corruption=TRUE

[oracle@baobao dbs]$ sqlplus / as sysdba

SQL>startup nomount pfile=‘$ORACLE_HOME/dbs/initdong.ora‘;

 

SQL>CREATE CONTROLFILE REUSE DATABASE "DONG" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
      MAXDATAFILES 100
        MAXINSTANCES 8
       MAXLOGHISTORY 292
    LOGFILE
     GROUP 1 ‘/u01/app/oracle/oradata/dong/redo01.log‘  SIZE 50M BLOCKSIZE 512,
        GROUP 2 ‘/u01/app/oracle/oradata/dong/redo02.log‘  SIZE 50M BLOCKSIZE 512,
     GROUP 3 ‘/u01/app/oracle/oradata/dong/redo03.log‘  SIZE 50M BLOCKSIZE 512
   -- STANDBY LOGFILE
    DATAFILE
      ‘/u01/app/oracle/oradata/dong/system01.dbf‘,
      ‘/u01/app/oracle/oradata/dong/sysaux01.dbf‘,
     ‘/u01/app/oracle/oradata/dong/undotbs01.dbf‘,
    ‘/u01/app/oracle/oradata/dong/users01.dbf‘
      CHARACTER SET WE8MSWIN1252 ;
Control file created.

SQL> alter database open;<BR>

实验二：ctl，redo，spfile丢失，dbf未丢失，无备份，数据库非正常关闭

SQL> create table t2149 as select * from dba_objects;
Table created.
SQL> shutdown abort

[oracle@baobao ~]$ cd $ORACLE_HOME/dbs/
[oracle@baobao dbs]$ vi initdong.ora 
*.control_files=‘/u01/app/oracle/oradata/dong/control01.ctl‘,‘/u01/app/oracle/flash_recovery_area/dong/control02.ctl‘#Restore Controlfile
*.db_name=‘dong‘
[oracle@baobao dbs]$ sqlplus / as sysdba
SQL> startup nomount pfile=‘$ORACLE_HOME/dbs/initdong.ora‘;
SQL> CREATE CONTROLFILE REUSE DATABASE "DONG" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
   MAXLOGHISTORY 292
LOGFILE
 GROUP 1 ‘/u01/app/oracle/oradata/dong/redo01.log‘  SIZE 50M BLOCKSIZE 512,
    GROUP 2 ‘/u01/app/oracle/oradata/dong/redo02.log‘  SIZE 50M BLOCKSIZE 512,
  GROUP 3 ‘/u01/app/oracle/oradata/dong/redo03.log‘  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
 DATAFILE
   ‘/u01/app/oracle/oradata/dong/system01.dbf‘,
   ‘/u01/app/oracle/oradata/dong/sysaux01.dbf‘,
  ‘/u01/app/oracle/oradata/dong/undotbs01.dbf‘,
 ‘/u01/app/oracle/oradata/dong/users01.dbf‘
   CHARACTER SET WE8MSWIN1252 ;
Control file created.
SQL> select name,status from v$datafile;
NAME                                               STATUS
-------------------------------------------------- -------
/u01/app/oracle/oradata/dong/system01.dbf          SYSTEM
/u01/app/oracle/oradata/dong/sysaux01.dbf          RECOVER
/u01/app/oracle/oradata/dong/undotbs01.dbf         RECOVER
/u01/app/oracle/oradata/dong/users01.dbf           RECOVER

SQL> recover database until cancel;
ORA-00279: change 1382526 generated at 11/26/2013 20:04:55 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_3_832524557.dbf
ORA-00280: change 1382526 for thread 1 is in sequence # 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-10879: error signaled in parallel recovery slave
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/u01/app/oracle/oradata/dong/system01.dbf‘
ORA-10878: parallel recovery slave died unexpectedly 

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [1382534], [0], [1382569], [4194432], [], [], [], [], [], []
Process ID: 3170
Session ID: 87 Serial number: 3

重新构造环境，然后重新执行recover，发现成功。。。：

SQL> recover database until cancel;
ORA-00279: change 1381057 generated at 11/26/2013 21:49:09 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_832542546.dbf
ORA-00280: change 1381057 for thread 1 is in sequence #1 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-10879: error signaled in parallel recovery slave
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/u01/app/oracle/oradata/dong/system01.dbf‘
ORA-10878: parallel recovery slave died unexpectedly
SQL> alter database open resetlogs;
Database altered.
SQL> select count(*) from t2149 ;
select count(*) from t2149
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

数据会有丢失，但应该只是丢失已经提交但还没有写到dbf里的。

实验三：ctl，redo，spfile，dbf均丢失，有备份但无ctl备份
对于紧紧有dbf的备份，但没有ctl备份的情况，肯定是需要重建ctl了，那如果ctl重建的话，就肯定查不到dbf的备份信息，在这种情况下，恢复如下：

[oracle@baobao dong]$ ll
总用量 1850616
-rw-r-----. 1 oracle oinstall  10076160 11月 26 21:58 control01.ctl
-rw-r-----. 1 oracle oinstall  10559488 11月 25 21:09 ff.dbf
-rw-r-----. 1 oracle oinstall  52429312 11月 26 21:58 redo01.log
-rw-r-----. 1 oracle oinstall  52429312 11月 26 21:51 redo02.log
-rw-r-----. 1 oracle oinstall  52429312 11月 26 21:51 redo03.log
-rw-r-----. 1 oracle oinstall 492838912 11月 26 21:58 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 996155392 11月 26 21:58 system01.dbf
-rw-r-----. 1 oracle oinstall  20979712 10月 25 12:23 temp011.dbf
-rw-r-----. 1 oracle oinstall  20979712 10月 22 13:54 temp01.dbf
-rw-r-----. 1 oracle oinstall 214966272 11月 26 21:58 undotbs01.dbf
-rw-r-----. 1 oracle oinstall  10559488 11月 26 21:58 users01.dbf
[oracle@baobao dong]$ rm -rf *
[oracle@baobao ~]$ cd $ORACLE_HOME/dbs/
[oracle@baobao dbs]$ vi initdong.ora 
*.control_files=‘/u01/app/oracle/oradata/dong/control01.ctl‘,‘/u01/app/oracle/flash_recovery_area/dong/control02.ctl‘#Restore Controlfile
*.db_name=‘dong‘
[oracle@baobao dbs]$ sqlplus / as sysdba
SQL> startup nomount pfile=‘$ORACLE_HOME/dbs/initdong.ora‘;
SQL> CREATE CONTROLFILE REUSE DATABASE "DONG" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6     MAXLOGHISTORY 292
  7  LOGFILE
  8   GROUP 1 ‘/u01/app/oracle/oradata/dong/redo01.log‘  SIZE 50M BLOCKSIZE 512,
  9      GROUP 2 ‘/u01/app/oracle/oradata/dong/redo02.log‘  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 ‘/u01/app/oracle/oradata/dong/redo03.log‘  SIZE 50M BLOCKSIZE 512
 11  -- STANDBY LOGFILE
 12   DATAFILE
 13     ‘/u01/app/oracle/oradata/dong/system01.dbf‘,
 14     ‘/u01/app/oracle/oradata/dong/sysaux01.dbf‘,
 15    ‘/u01/app/oracle/oradata/dong/undotbs01.dbf‘,
 16   ‘/u01/app/oracle/oradata/dong/users01.dbf‘
 17     CHARACTER SET WE8MSWIN1252 ;
 CREATE CONTROLFILE REUSE DATABASE "DONG" NORESETLOGS  ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file
‘/u01/app/oracle/oradata/dong/system01.dbf‘--这里很是不解啊？为啥创建ctl的时候会去检查各个dbf是否存在呢，而且还会读取文件的内容
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

因此尝试将ctl和dbf文件从备份文件中恢复。
恢复控制文件：

SQL> declare
  2  devtype varchar2(256);
  3  done  boolean;
  4  begin
  5  devtype:=sys.dbms_backup_restore.deviceAllocate(type=>‘‘,ident=>‘T1‘);
  6  sys.dbms_backup_restore.restoresetdatafile;
  7  sys.dbms_backup_restore.restorecontrolfileto(cfname=>‘/u01/app/oracle/oradata/dong/control01.ctl‘);
sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>‘/u01/app/oracle/flash_recovery_area/DONG/autobackup/2013_11_27/o1_mf_s_832590070_99br9o1s_.bkp‘,params=>null);
  9  sys.dbms_backup_restore.devicedeallocate;
 10  end;
 11  / 

PL/SQL procedure successfully completed. 

SQL> !ls -l 
-rw-r-----. 1 oracle oinstall 10141696 11月 29 16:21 control01.ctl
SQL> alter database mount; 

Database altered.
[oracle@baobao dong]$ rman target / 

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Nov 29 16:22:42 2013 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved. 

connected to target database: DONG (DBID=2075447482, not open) 

RMAN> list backup; 

using target database control file instead of recovery catalog 

List of Backup Sets
=================== 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
69      Full    1.21G      DISK        00:01:04     27-NOV-13      
        BP Key: 69   Status: AVAILABLE  Compressed: NO  Tag: TAG20131127T110314
        Piece Name: /home/oracle/11271103full832590194
  List of Datafiles in backup set 69
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1384847    27-NOV-13 /u01/app/oracle/oradata/dong/system01.dbf
  2       Full 1384847    27-NOV-13 /u01/app/oracle/oradata/dong/sysaux01.dbf
  3       Full 1384847    27-NOV-13 /u01/app/oracle/oradata/dong/undotbs01.dbf
  4       Full 1384847    27-NOV-13 /u01/app/oracle/oradata/dong/users01.dbf
  5       Full 1297899    25-NOV-13 /u01/app/oracle/oradata/dong/ff.dbf

再直接restore即可，不过这里我们还是使用包来将dbf恢复：

SQL> select status from v$instance; 

STATUS
------------
MOUNTED 

SQL> declare
    devtype varchar2(256);
    done boolean;
    begin
    devtype:=sys.dbms_backup_restore.deviceallocate(type=>‘‘,ident=>‘t1‘);
    sys.dbms_backup_restore.restoresetdatafile;
    sys.dbms_backup_restore.restoredatafileto(dfnumber=>01,toname=>‘/u01/app/oracle/oradata/dong/system01.dbf‘);
     sys.dbms_backup_restore.restoredatafileto(dfnumber=>02,toname=>‘/u01/app/oracle/oradata/dong/sysaux01.dbf‘);
     sys.dbms_backup_restore.restoredatafileto(dfnumber=>03,toname=>‘/u01/app/oracle/oradata/dong/undotbs01.dbf‘);
     sys.dbms_backup_restore.restoredatafileto(dfnumber=>04,toname=>‘/u01/app/oracle/oradata/dong/users01.dbf‘);
     sys.dbms_backup_restore.restoredatafileto(dfnumber=>05,toname=>‘/u01/app/oracle/oradata/dong/ff.dbf‘);
     sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>‘/home/oracle/11271103full832590194‘,params=>null);
     sys.dbms_backup_restore.devicedeallocate;
     end;
    / 

PL/SQL procedure successfully completed.
[oracle@baobao dong]$ ll
总用量 1704472
-rw-r-----. 1 oracle oinstall  10141696 11月 29 16:20 control01.ct
-rw-r-----. 1 oracle oinstall  10141696 11月 29 16:27 control01.ctl
-rw-r-----. 1 oracle oinstall  10559488 11月 29 16:26 ff.dbf
-rw-r-----. 1 oracle oinstall 492838912 11月 29 16:27 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 996155392 11月 29 16:27 system01.dbf
-rw-r-----. 1 oracle oinstall 214966272 11月 29 16:26 undotbs01.dbf
-rw-r-----. 1 oracle oinstall  10559488 11月 29 16:26 users01.dbf

接下来就可以recover了：

SQL> alter system set log_archive_dest_1=‘LOCATION=/u01/app/oracle/diag/rdbms/dong‘; 

System altered. 

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@baobao 2013_11_27]$ rman target / 

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Nov 29 16:55:57 2013 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved. 

connected to target database: DONG (DBID=2075447482, not open) 

RMAN> recover database; 

Starting recover at 29-NOV-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=99 device type=DISK
datafile 5 not processed because file is read-only 

starting media recovery 

archived log file name=/u01/app/oracle/diag/rdbms/dong/1_7_832589996.dbf thread=1 sequence=7
media recovery complete, elapsed time: 00:00:01
Finished recover at 29-NOV-13 

SQL> alter database open resetlogs; 

Database altered.

小插曲：
恢复归档文件:

declare
devtype varchar2(256);
done boolean;
begin
devtype:=sys.dbms_backup_restore.deviceallocate(type=>‘‘,ident=>‘t1‘);
sys.dbms_backup_restore.restoresetarchivedlog(destination=>‘归档目录‘);
sys.dbms_backup_restore.restorearchivedlog(thread=>1,seqnce=>1);
sys.dbms_backup_restore.restorearchivedlog(thread=>1,seqnce=>2);
sys.dbms_backup_restore.restorearchivedlog(thread=>1,seqnce=>3);
sys.dbms_backup_restore.restorearchivedlog(thread=>1,seqnce=>4);
sys.dbms_backup_restore.restorearchivedlog(thread=>1,seqnce=>5);
sys.dbms_backup_restore.restorearchivedlog(thread=>1,seqnce=>6);
sys.dbms_backup_restore.restorearchivedlog(thread=>1,seqnce=>7);
sys.dbms_backup_restore.restorearchivedlog(thread=>1,seqnce=>8);
sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>‘归档备份集‘,params=>null);
sys.dbms_backup_restore.devicedeallocate;
end;