ORACLE 11G沒有備份檔案參數檔案在異機通過rman備份恢複找回被誤刪的資料，11grman

ORACLE 11G沒有備份檔案參數檔案在異機通過rman備份恢複找回被誤刪的資料，11grman

背景：

         同事誤刪除線上資料，所以需要從備份中找回資料恢複。真實屋漏偏逢連夜雨、船遲又遇打頭風，前兩天備份的磁碟壞塊，現在只有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)
----------------------------------------------------------------------------------------------------------------