ORACLE11G没有备份文件参数文件在异机通过rman备份恢复找回被误删的数据
背景：
同事误删除线上数据，所以需要从备份中找回数据恢复。真实屋漏偏逢连夜雨、船迟又遇打头风，前两天备份的磁盘坏块，现在只有rman全备的.bak文件，没有控制文件和参数文件，所以现在需要考虑的是如何根据bak文件在备份数据库上恢复数据，从中找出被误删的数据。
1 通过catalog start with’’的方式来恢复
1.1手动创建控制文件
CREATE CONTROLFILE REUSE set DATABASE"powerdes" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 454
DATAFILE
'/home/oradata/powerdes/system01.dbf',
'/home/oradata/powerdes/sysaux01.dbf',
'/home/oradata/powerdes/undotbs01.dbf',
'/home/oradata/powerdes/users01.dbf',
'/home/oradata/powerdes/powerdesk01.dbf',
'/home/oradata/powerdes/plas01.dbf',
'/home/oradata/powerdes/pl01.dbf',
'/home/oradata/powerdes/help01.dbf',
'/home/oradata/powerdes/adobelc01.dbf',
'/home/oradata/powerdes/sms01.dbf',
'/home/oradata/powerdes/plcrm01.dbf'
LOGFILE
GROUP 1('/home/oradata/powerdes/redo03.log') SIZE 10M,
GROUP 2('/home/oradata/powerdes/redo02.log') SIZE 10M,
GROUP 3('/home/oradata/powerdes/redo01.log') SIZE10M,
GROUP 4('/home/oradata/powerdes/redo_dg_01.log') SIZE 10M,
GROUP 5('/home/oradata/powerdes/redo_dg_02.log') SIZE 10M,
GROUP 6('/home/oradata/powerdes/redo_dg_03.log') SIZE 10M
CHARACTER SET ZHS16GBK;
1.2，设置catalog start with 路径
searching for all files that match the pattern/tmp/2015-03-30/ List of Files Unknown to the Database ===================================== File Name:/tmp/2015-03-30/full_POWERDES_20150330_3395.bak File Name: /tmp/2015-03-30/rman_backup.log File Name:/tmp/2015-03-30/arch_POWERDES_20150330_3396.bak File Name:/tmp/2015-03-30/arch_POWERDES_20150330_3394.bak Do you really want to catalog the abovefiles (enter YES or NO)? yes cataloging files... cataloging done List of Cataloged Files ======================= File Name:/tmp/2015-03-30/full_POWERDES_20150330_3395.bak File Name:/tmp/2015-03-30/arch_POWERDES_20150330_3396.bak File Name:/tmp/2015-03-30/arch_POWERDES_20150330_3394.bak List of Files Which Where Not Cataloged ======================================= File Name: /tmp/2015-03-30/rman_backup.log RMAN-07517: Reason: The file header is corrupted
1.3 开始restore database恢复数据库
RMAN> restore database; Starting restore at 01-APR-15 using channel ORA_DISK_1 RMAN-00571:=========================================================== RMAN-00569: =============== ERROR MESSAGESTACK FOLLOWS =============== RMAN-00571:=========================================================== RMAN-03002: failure of restore command at04/01/2015 03:39:01 RMAN-06026: some targets not found -aborting restore RMAN-06023: no backup or copy of datafile12 found to restore RMAN-06023: no backup or copy of datafile11 found to restore RMAN-06023: no backup or copy of datafile10 found to restore RMAN-06023: no backup or copy of datafile 9found to restore RMAN-06023: no backup or copy of datafile 8found to restore RMAN-06023: no backup or copy of datafile 7found to restore RMAN-06023: no backup or copy of datafile 5found to restore RMAN-06023: no backup or copy of datafile 4found to restore RMAN-06023: no backup or copy of datafile 3found to restore RMAN-06023: no backup or copy of datafile 2found to restore RMAN-06023: no backup or copy of datafile 1found to restore RMAN>
PS：遗憾的是恢复失败，google了很久，发现只能通过sys.dbms_backup_restore.restoreDatafileTo的方式来做不完全一致恢复。
2，使用sys.dbms_backup_restore.restoreDatafileTo来做不完全一致数据恢复
2.1，先做restoreDatafileTo的数据抽取工作，也就是将数据文件从备份集中抽取出来，记得数据文件路径要与线上保持一致。
执行命令如下：
SQL> DECLARE devtype varchar2(256); doneboolean; BEGIN devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'t1'); sys.dbms_backup_restore.restoreSetDatafile; sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>01,toname=>'/home/oradata/powerdes/system01.dbf'); sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>02,toname=>'/home/oradata/powerdes/sysaux01.dbf'); sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>03,toname=>'/home/oradata/powerdes/undotbs01.dbf'); sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>04,toname=>'/home/oradata/powerdes/users01.dbf'); sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>05,toname=>'/home/oradata/powerdes/powerdesk01.dbf'); sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>06,toname=>'/home/oradata/powerdes/plas01.dbf'); sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>07,toname=>'/home/oradata/powerdes/pl01.dbf'); sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>08,toname=>'/home/oradata/powerdes/help01.dbf'); sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>09,toname=>'/home/oradata/powerdes/adobelc01.dbf'); sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>10,toname=>'/home/oradata/powerdes/sms01.dbf'); sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>11,toname=>'/home/oradata/powerdes/plcrm01.dbf'); sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/pddata2/oracle/backup/data/2015-03-30/full_POWERDES_20150330_3395.bak',params=>null); sys.dbms_backup_restore.deviceDeallocate; END; /
PS：handle是执行备份的bak文件；最后的/不能少。
SQL>
执行过程需要一段时间，慢慢等待，后台的alert日志会依次有如下信息显示出来提示抽取成功：
Full restore complete of datafile 7 to datafilecopy /home/oradata/powerdes/pl01.dbf. Elapsed time: 0:00:41
checkpoint is 11029786628
last deallocation scn is 11022714700
Full restore complete of datafile 8 todatafile copy /home/oradata/powerdes/help01.dbf. Elapsed time: 0:00:01
checkpoint is 11029786628
last deallocation scn is 9881798870
Wed Apr 01 05:01:54 2015
Full restore complete of datafile 9 todatafile copy /home/oradata/powerdes/adobelc01.dbf. Elapsed time: 0:00:00
checkpoint is 11029786628
Wed Apr 01 05:02:12 2015
Full restore complete of datafile 10 todatafile copy /home/oradata/powerdes/sms01.dbf. Elapsed time: 0:00:12
checkpoint is 11029786628
Wed Apr 01 05:05:15 2015
Full restore complete of datafile 3 todatafile copy /home/oradata/powerdes/undotbs01.dbf. Elapsed time: 0:04:45
checkpoint is 11029786628
last deallocation scn is 11029784977
Undo Optimization current scn is 11029779851
Wed Apr 01 05:06:04 2015
Full restore complete of datafile 11 todatafile copy /home/oradata/powerdes/plcrm01.dbf. Elapsed time: 0:06:01
checkpoint is 11029786628
last deallocation scn is 11007637870
Wed Apr 01 05:06:27 2015
Full restore complete of datafile 4 todatafile copy /home/oradata/powerdes/users01.dbf. Elapsed time: 0:06:24
checkpoint is 11029786628
last deallocation scn is 11029437543
Wed Apr 01 05:10:33 2015
Full restore complete of datafile 1 todatafile copy /home/oradata/powerdes/system01.dbf. Elapsed time: 0:09:27
checkpoint is 11029786628
last deallocation scn is 10910611499
Undo Optimization current scn is 11029779851
Wed Apr 01 05:13:39 2015
Full restore complete of datafile 2 todatafile copy /home/oradata/powerdes/sysaux01.dbf. Elapsed time: 0:13:29
checkpoint is 11029786628
last deallocation scn is 11028968586
Wed Apr 01 05:14:44 2015
Full restore complete of datafile 6 todatafile copy /home/oradata/powerdes/plas01.dbf. Elapsed time: 0:14:37
checkpoint is 11029786628
last deallocation scn is 11028348038
Wed Apr 01 05:39:30 2015
Full restore complete of datafile 5 todatafile copy /home/oradata/powerdes/powerdesk01.dbf. Elapsed time: 0:39:12
checkpoint is 11029786628
last deallocation scn is 11029438448
2.2 数据抽取成功后，要创新创建控制文件
如果不知道如何创建controlfile的命令，可以在线上生成trace文件
一般默认的控制文件是二进制的，打开来是乱码的 ，备份一个trace出来 可以打开看到语句了,$ORACLE_BASE/admin/$ORACLE_SID/udump目录下，生成的新的 trace 文件里，trace文件有生成控制文件的脚本，使用如下命令alter database backup controlfile to trace as'/oracle/app/oracle/admin/powerdes/pfile/control.sql';可以得到创建控制文件的sql命令。
整理出来创建控制文件命令如下：
CREATE CONTROLFILE REUSE SET DATABASE"POWERDES" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 2920 DATAFILE '/home/oradata/powerdes/system01.dbf', '/home/oradata/powerdes/sysaux01.dbf', '/home/oradata/powerdes/undotbs01.dbf', '/home/oradata/powerdes/users01.dbf', '/home/oradata/powerdes/powerdesk01.dbf', '/home/oradata/powerdes/plas01.dbf', '/home/oradata/powerdes/pl01.dbf', '/home/oradata/powerdes/help01.dbf', '/home/oradata/powerdes/adobelc01.dbf', '/home/oradata/powerdes/sms01.dbf', '/home/oradata/powerdes/plcrm01.dbf' LOGFILE GROUP 1 '/home/oradata/powerdes/redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '/home/oradata/powerdes/redo02.log' SIZE 50M BLOCKSIZE 512, GROUP 3 '/home/oradata/powerdes/redo03.log' SIZE 50M BLOCKSIZE 512 CHARACTER SET ZHS16GBK;
2.3 恢复数据并且打开数据库
RMAN> recover database; Starting recover at 02-APR-15 using target database control file insteadof recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=767 devicetype=DISK starting media recovery unable to find archived log archived log thread=1 sequence=31023 RMAN-00571:=========================================================== RMAN-00569: =============== ERROR MESSAGESTACK FOLLOWS =============== RMAN-00571:=========================================================== RMAN-03002: failure of recover command at04/02/2015 13:35:40 RMAN-06054: media recovery requestingunknown archived log for thread 1 with sequence 31023 and starting SCN of11029786628 RMAN> RMAN> restore database from'/pddata2/oracle/backup/data/2015-03-30/full_POWERDES_20150330_3395.bak'; Starting restore at 02-APR-15 using channel ORA_DISK_1 RMAN-00571:=========================================================== RMAN-00569: =============== ERROR MESSAGESTACK FOLLOWS =============== RMAN-00571:=========================================================== RMAN-03002: failure of restore command at04/02/2015 13:37:05 RMAN-06509: only SPFILE or control file canbe restored from AUTOBACKUP RMAN>
看到打开都报错了，所以试试直接open打开吧
SQL> SQL> alter database open resetlogs
3，然后exp导出表数据,imp导入到生产环境
导出：exp system/manager@myoracle file=/tmp/qd.dmp tables=(hr_resume_qingdao) system是用户名，manager 是密码，myoracle 是数据库名。
传输数据：scp /tmp/qd.dmp 192.168.120.217:/tmp/
导入：imp username/pwd[@sid] file=/tmp/ qd.dmp tables=( hr_resume_qingdao)
4，一些报错记录
找到数据文件
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01200: actual file size of 128679 issmaller than correct size of 340480
blocks
ORA-01110: data file 2:'/home/oradata/powerdes/sysaux01.dbf'
解决方法：
dd if=/dev/zeroof=/home/oradata/powerdes/sysaux01.dbf bs=8192 count=1 seek=340480