ORACLE 11G沒有備份檔案引數檔案在異機通過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
2 ;
Database altered.
SQL>
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
----------------------------------------------------------------------------------------------------------------
原部落格地址： http://blog.itpub.net/26230597/viewspace-1484099/
原作者：黃杉 (mchdba)
----------------------------------------------------------------------------------------------------------------
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26230597/viewspace-1484099/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 沒有備份的資料檔案恢復（五）
- 歸檔模式，恢復沒有備份的資料檔案模式
- rman備份-（1） 利用備份級恢復資料檔案和控制檔案
- rman備份恢復-rman恢復資料檔案測試
- rman恢復--歸檔模式有備份，丟失資料檔案的恢復模式
- oracle11g 資料檔案誤刪恢復(無備份)Oracle
- standby全庫rman備份檔案恢復到異機
- ORACLE中沒有引數檔案和控制檔案如何通過rman恢復資料庫Oracle資料庫
- 【備份恢復】不使用rman工具就能恢復被rm刪除的資料檔案案例
- 備份與恢復系列 十 引數檔案spfile的備份與恢復
- 所有除引數檔案以外的檔案都丟失，但是隻有資料檔案的 RMAN 備份的恢復
- rman恢復資料庫--用備份的控制檔案資料庫
- 所有除引數檔案以外的檔案都丟失，但是隻有資料檔案的 RMAN 備份的恢復2
- 控制檔案全部丟失，無備份，通過異機trace恢復
- Oracle RMAN 不完全恢復（只有資料檔案備份，丟失歸檔日誌備份）Oracle
- 11g rman備份恢復資料檔案datafile到新的位置
- rman恢復--歸檔模式無備份，丟失資料檔案的恢復模式
- 12 使用RMAN備份和恢復檔案
- rman備份丟失控制檔案恢復
- RMAN備份恢復之控制檔案的恢復（三）
- RMAN備份恢復之控制檔案的恢復（二）
- RMAN備份恢復之控制檔案的恢復（一）
- 使用logmnr，在RMAN備份檔案中恢復備份的歸檔日誌檔案進行分析
- 【備份恢復】歸檔模式下丟失系統關鍵資料檔案 利用RMAN備份恢復模式
- RMAN資料庫恢復 之歸檔模式有(無)備份-丟失資料檔案的恢復資料庫模式
- 備份與恢復--利用備份的控制檔案恢復
- 【備份恢復】無備份線上恢復非關鍵資料檔案
- Oracle備份與恢復【丟失資料檔案的恢復】Oracle
- Oracle資料庫控制檔案在備份恢復中的作用Oracle資料庫
- ORACLE 只讀資料檔案備份與恢復Oracle
- RMAN說，我能備份(4)--RMAN備份資料檔案和控制檔案
- 無備份恢復(歸檔模式)已刪除資料檔案模式
- Oracle 11g RAMN恢復-控制檔案的備份和恢復Oracle
- RMAN備份恢復典型案例——資料檔案存在壞快
- 非系統資料檔案損壞，rman備份恢復
- RMAN恢復（rman只備份資料庫，但不備份歸檔，歸檔透過簡單的rsync或scp來傳送到異地備份）資料庫
- [20171225]沒有備份資料檔案的恢復.txt
- 歸檔模式有備份丟失資料檔案後恢復模式