dbms_backup_restore恢复数据库
环境：oracle 11.2.0.1 测试数据库，由于磁盘坏了，数据文件和控制文件都丢失了
只有全备的备份集，而且控制文件的备份开启了autobackup 功能，但是由于磁盘的损坏也丢失了
因为还有之前备份时的log，可以找到数据文件的file_id 和file_name
用dbms_backup_restore恢复需要的数据
启动到nomount 状态,然后恢复数据文件
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=>00015 ,toname=>'/oradata/mdstest/mierp_data01.dbf');
sys.dbms_backup_restore.restoredatafileto(dfnumber=>00046 ,toname=>'/oradata/mdstest/tongteng01.dbf');
sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'/backup/rman_bak/back_7so0j3j2_1276_1.bak',params=>null);
sys.dbms_backup_restore.devicedeallocate;
end;
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=>00001,toname=>'/oradata/mdstest/system01.dbf');
sys.dbms_backup_restore.restoredatafileto(dfnumber=>00002,toname=>'/oradata/mdstest/sysaux01.dbf');
sys.dbms_backup_restore.restoredatafileto(dfnumber=>00042,toname=>'/oradata/mdstest/system02.dbf');
sys.dbms_backup_restore.restoredatafileto(dfnumber=>00005,toname=>'/oradata/mdstest/users01.dbf');
sys.dbms_backup_restore.restoredatafileto(dfnumber=>00047,toname=>'/oradata/mdstest/takyundata01.dbf');
sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'/backup/rman_bak/back_7to0j3j3_1277_1.bak',params=>null);
sys.dbms_backup_restore.devicedeallocate;
end;
数据文件恢复之后，创建controlfile
SQL>
CREATE CONTROLFILE REUSE DATABASE "mdstest" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 1168
LOGFILE
GROUP 1 '/oradata/mdstest/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/oradata/mdstest/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/oradata/mdstest/redo03.log' SIZE 50M BLOCKSIZE 512
DATAFILE
'/oradata/mdstest/system01.dbf',
'/oradata/mdstest/system02.dbf',
'/oradata/mdstest/sysaux01.dbf',
'/oradata/mdstest/users01.dbf',
'/oradata/mdstest/takyundata01.dbf',
'/oradata/mdstest/mierp_data01.dbf',
'/oradata/mdstest/tongteng01.dbf'
CHARACTER SET AL32UTF8 ;
创建完毕，open
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01173: data dictionary indicates missing data file from system tablespace
Process ID: 30553
Session ID: 125 Serial number: 3
不知道是不是还是少了system文件，可是查找之前的备份记录信息，system表空间就只有2个文件啊
现在该怎么处理啊
开启了autobackup controlfile的会自动备份到flash_recovery_area中，全备的备份集里面没有
开启了autobackup controlfile真的不会将控制文件备份到全备集中
mdslinuxoracle:/home/oracle>rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Mar 13 08:32:35 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11 (DBID=736537108)
RMAN> list backup of controlfile;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 9.67M DISK 00:00:01 06-FEB-13
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20130206T091106
Piece Name: /backup/back_database_02o19d9q_1_1.bak
Control File Included: Ckp SCN: 4520875 Ckp time: 06-FEB-13
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 9.64M DISK 00:00:01 06-FEB-13
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20130206T091125
Piece Name: /backup/ctl_03o19d9t_1_1.bak
Control File Included: Ckp SCN: 4520897 Ckp time: 06-FEB-13
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 9.67M DISK 00:00:01 11-MAR-13
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20130311T153017
Piece Name: /backup/database_05o49115_1_1.bak
Control File Included: Ckp SCN: 11724950364612 Ckp time: 11-MAR-13
RMAN> exit
Recovery Manager complete.
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORA11 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/orahome/11.2/db_1/dbs/snapcf_ora11.f'; # default
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP on;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN> backup database format '/backup/database_%U.bak';
Starting backup at 13-MAR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=55 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/oradata/ora11/undotbs01.dbf
input datafile file number=00006 name=/oradata/ora11/lerry02.dbf
input datafile file number=00001 name=/oradata/ora11/system01.dbf
input datafile file number=00002 name=/oradata/ora11/sysaux01.dbf
input datafile file number=00005 name=/oradata/ora11/lerry01.dbf
input datafile file number=00004 name=/oradata/ora11/users01.dbf
channel ORA_DISK_1: starting piece 1 at 13-MAR-13
channel ORA_DISK_1: finished piece 1 at 13-MAR-13
piece handle=/backup/database_06o4dhbe_1_1.bak tag=TAG20130313T083350 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
Finished backup at 13-MAR-13
Starting Control File and SPFILE Autobackup at 13-MAR-13
piece handle=/orahome/11.2/flash_recovery_area/ORA11/autobackup/2013_03_13/o1_mf_s_809944456_8mzld8hc_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 13-MAR-13
RMAN> list backup of controlfile;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 9.67M DISK 00:00:01 06-FEB-13
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20130206T091106
Piece Name: /backup/back_database_02o19d9q_1_1.bak
Control File Included: Ckp SCN: 4520875 Ckp time: 06-FEB-13
BS Key Type LV Size Device Type Elapsed Time Completion Time
是的，就是open的时候出现下面出错，不知道是不是system表空间少了数据文件
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01173: data dictionary indicates missing data file from system tablespace
Process ID: 30553
Session ID: 125 Serial number: 3
页:
[1]