ORACLE中採用rman備份異機恢復資料庫詳細過程

mchdba發表於2015-02-03

場景:
        有一個生產庫的使用者下面所有的表都不見了,懷疑人為被刪除了,現在需要用備份去恢復下,找出原來的表,線上是oracle dataguard環境,有全庫備份檔案,準備去測試庫恢復一下。



1,從生產庫上copy好全備份檔案
恢復資料庫需要準備的檔案:rman完整備份(包括資料檔案、日誌檔案、控制檔案、引數檔案),記錄源資料庫的DBID

安裝的測試資料庫ORACLE資料庫軟體並建立跟源資料庫同名和資料庫SID並修改資料庫DBID跟源資料庫DBID一樣,建立跟源資料庫伺服器相同的資料檔案目錄、日誌檔案目錄、控制檔案目錄。

檢視環境
1.1,檢視引數檔案資訊
RMAN>  list backup of spfile;


using target database control file instead of recovery catalog


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




BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7       Full    1.05M      DISK        00:00:01     30-JAN-15      
        BP Key: 7   Status: AVAILABLE  Compressed: YES  Tag: TAG20150130T201758
        Piece Name: /data/oracle/backup/data/2015-01-30/full_stuorcl_20150130_7.bak
  SPFILE Included: Modification time: 30-JAN-15
  SPFILE db_unique_name: stuorcl


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
11      Full    9.39M      DISK        00:00:01     31-JAN-15      
        BP Key: 11   Status: AVAILABLE  Compressed: NO  Tag: TAG20150131T180139
        Piece Name: /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015_01_31/o1_mf_ncsnf_TAG20150131T180139_bdsb18jn_.bkp
  SPFILE Included: Modification time: 31-JAN-15
  SPFILE db_unique_name: stuorcl


1.2檢視控制檔案資訊:
RMAN>  list backup of controlfile;




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




BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7       Full    1.05M      DISK        00:00:01     30-JAN-15      
        BP Key: 7   Status: AVAILABLE  Compressed: YES  Tag: TAG20150130T201758
        Piece Name: /data/oracle/backup/data/2015-01-30/full_stuorcl_20150130_7.bak
  Control File Included: Ckp SCN: 1635989      Ckp time: 30-JAN-15


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
11      Full    9.39M      DISK        00:00:01     31-JAN-15      
        BP Key: 11   Status: AVAILABLE  Compressed: NO  Tag: TAG20150131T180139
        Piece Name: /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015_01_31/o1_mf_ncsnf_TAG20150131T180139_bdsb18jn_.bkp
  Control File Included: Ckp SCN: 1758247      Ckp time: 31-JAN-15


  
1.3 檢視資料庫資訊: 
RMAN>  list backup of database;




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




BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6       Full    270.16M    DISK        00:00:56     30-JAN-15      
        BP Key: 6   Status: AVAILABLE  Compressed: YES  Tag: TAG20150130T201758
        Piece Name: /data/oracle/backup/data/2015-01-30/full_stuorcl_20150130_6.bak
  List of Datafiles in backup set 6
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1635573    30-JAN-15 /oracle/data_ora/stuorcl/system01.dbf
  2       Full 1635573    30-JAN-15 /oracle/data_ora/stuorcl/sysaux01.dbf
  3       Full 1635573    30-JAN-15 /oracle/data_ora/stuorcl/undotbs01.dbf
  4       Full 1635573    30-JAN-15 /oracle/data_ora/stuorcl/users01.dbf
  5       Full 1635573    30-JAN-15 /oracle/data_ora/stuorcl/SBXTAX01.dbf
  6       Full 1635573    30-JAN-15 /oracle/data_ora/stuorcl/SMS01.dbf


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10      Full    1.11G      DISK        00:00:29     31-JAN-15      
        BP Key: 10   Status: AVAILABLE  Compressed: NO  Tag: TAG20150131T180139
        Piece Name: /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015_01_31/o1_mf_nnndf_TAG20150131T180139_bdsb04jz_.bkp
  List of Datafiles in backup set 10
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1758077    31-JAN-15 /oracle/data_ora/stuorcl/system01.dbf
  2       Full 1758077    31-JAN-15 /oracle/data_ora/stuorcl/sysaux01.dbf
  3       Full 1758077    31-JAN-15 /oracle/data_ora/stuorcl/undotbs01.dbf
  4       Full 1758077    31-JAN-15 /oracle/data_ora/stuorcl/users01.dbf
  5       Full 1758077    31-JAN-15 /oracle/data_ora/stuorcl/SBXTAX01.dbf
  6       Full 1758077    31-JAN-15 /oracle/data_ora/stuorcl/SMS01.dbf


  
1.4 檢視歸檔日誌資訊: 
RMAN> list backup of archivelog all;




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




BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
8       1.26M      DISK        00:00:00     30-JAN-15      
        BP Key: 8   Status: AVAILABLE  Compressed: YES  Tag: TAG20150130T201906
        Piece Name: /data/oracle/backup/data/2015-01-30/arch_stuorcl_20150130_8.bak


  List of Archived Logs in backup set 8
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    104     1635562    30-JAN-15 1635995    30-JAN-15


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
9       195.19M    DISK        00:00:04     31-JAN-15      
        BP Key: 9   Status: AVAILABLE  Compressed: NO  Tag: TAG20150131T180132
        Piece Name: /data/oracle/backup/data/ALL_0cpu493s_1_1.BKP


  List of Archived Logs in backup set 9
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    105     1635995    30-JAN-15 1636013    30-JAN-15
  1    106     1636013    30-JAN-15 1653304    30-JAN-15
  1    107     1653304    30-JAN-15 1679217    31-JAN-15
  1    108     1679217    31-JAN-15 1703168    31-JAN-15
  1    109     1703168    31-JAN-15 1731695    31-JAN-15
  1    110     1731695    31-JAN-15 1757189    31-JAN-15
  1    111     1757189    31-JAN-15 1758005    31-JAN-15
  1    112     1758005    31-JAN-15 1758039    31-JAN-15
  1    113     1758039    31-JAN-15 1758055    31-JAN-15


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
12      1.12M      DISK        00:00:00     31-JAN-15      
        BP Key: 12   Status: AVAILABLE  Compressed: NO  Tag: TAG20150131T180218
        Piece Name: /data/oracle/backup/data/ALL_0fpu495a_1_1.BKP


  List of Archived Logs in backup set 12
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    114     1758055    31-JAN-15 1758252    31-JAN-15


RMAN>






2、開始恢復引數檔案控制檔案:
SQL> select dbid from v$database;                                                                                                                                                              


      DBID
----------
3391761643


SQL>


2.1 設定DBID:
注意:在rman下即使沒有引數檔案,預設也會啟動一個DUMMY例項,以便能夠恢復引數檔案。
set dbid 3391761643


2.2 恢復spfile檔案
startup到open狀態,先檢視spfile檔案位置:
SQL> show parameter spfile;


NAME     TYPE
------------------------------------ ----------------------
VALUE
------------------------------
spfile     string
/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/spfilestuorcl.ora
SQL>

shutdown後,再startup 到 nomount狀態
startup nomount再恢復
restore spfile to '/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/spfilestuorcl.ora' from '/oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-27/full_stuorcl_20150127_3069.bak';


恢復報錯,去生產環境檢視備份資訊:
RMAN> show all;                                                                                                                                                                                


using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name stuunq are:                                                                                                                      
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 15 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/pddata2/oracle/backup/data/ctl_auto/%F';
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 '/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_stuorcl.f'; # default                                                                           
                                                                                                                                                                                               
RMAN> 
有CONFIGURE CONTROLFILE AUTOBACKUP ON; 表示引數檔案和控制檔案有備份


restore spfile to '/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/spfilestuorcl.ora' from  '/oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-27/c-3391761643-20150127-03';
RMAN> restore spfile to '/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/spfilestuorcl.ora' from  '/oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-27/c-3391761643-20150127-03';


Starting restore at 02-FEB-15
using channel ORA_DISK_1


channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/02/2015 11:36:30
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece
restore spfile to '/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/spfilestuorcl.ora' from  '/oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-27/c-3391761643-20150127-03' until time 'sysdate - 30'




RMAN> restore spfile to '/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/spfilestuorcl.ora' from  '/oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-27/c-3391761643-20150127-03' until time 'sysdate - 30';


Starting restore at 02-FEB-15
using channel ORA_DISK_1


channel ORA_DISK_1: restoring spfile from AUTOBACKUP /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-27/c-3391761643-20150127-03
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 02-FEB-15


RMAN> 
OK,成功了,引數檔案恢復成功。


CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 15 DAYS;
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
這是兩個庫不一樣的地方


2.3,恢復控制檔案位置
檢視控制檔案位置
SQL> show parameter control


NAME     TYPE
------------------------------------ ----------------------
VALUE
------------------------------
control_file_record_keep_time     integer
7
control_files     string
/oracle/data_ora/stuorcl/control01.ctl, /oracle/app/oracle/flash_recovery_area/stuorcl/control02.ctl
control_management_pack_access     string
DIAGNOSTIC+TUNING
SQL>




恢復控制檔案:
restore controlfile to '/oracle/data_ora/stuorcl/control01.ctl' from '/oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-27/c-3391761643-20150127-03';
RMAN> restore controlfile to '/oracle/data_ora/stuorcl/control01.ctl' from '/oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-27/c-3391761643-20150127-03';


Starting restore at 02-FEB-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=130 device type=DISK


channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 02-FEB-15


RMAN> 




2.4,  在新控制檔案中註冊資料檔案備份和歸檔備份
catalog start with '/oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-27/';
RMAN> catalog start with '/oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-27/';


searching for all files that match the pattern /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-27/


List of Files Unknown to the Database
=====================================
File Name: /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-27/arch_stuorcl_20150127_3068.bak
File Name: /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-27/rman_backup.log
File Name: /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-27/arch_stuorcl_20150127_3067.bak
File Name: /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-27/full_stuorcl_20150127_3069.bak
File Name: /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-27/c-3391761643-20150127-03
File Name: /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-27/arch_stuorcl_20150127_3070.bak


Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done


List of Cataloged Files
=======================
File Name: /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-27/arch_stuorcl_20150127_3068.bak
File Name: /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-27/arch_stuorcl_20150127_3067.bak
File Name: /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-27/full_stuorcl_20150127_3069.bak
File Name: /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-27/c-3391761643-20150127-03
File Name: /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-27/arch_stuorcl_20150127_3070.bak


List of Files Which Where Not Cataloged
=======================================
File Name: /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-27/rman_backup.log
  RMAN-07517: Reason: The file header is corrupted


RMAN> 


2.5,恢復整個庫
因為前面恢復了整個spfile已經controlfile,所以接下來恢復所有庫的話,就不用帶引數,直接恢復restore database就可以 ; 
RMAN> restore database;


Starting restore at 02-FEB-15
using channel ORA_DISK_1


channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /home/oradata/stuorcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /home/oradata/stuorcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /home/oradata/stuorcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /home/oradata/stuorcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /home/oradata/stuorcl/stuorclk01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /home/oradata/stuorcl/plas01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /home/oradata/stuorcl/pl01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /home/oradata/stuorcl/help01.dbf
channel ORA_DISK_1: restoring datafile 00009 to /home/oradata/stuorcl/adobelc01.dbf
channel ORA_DISK_1: restoring datafile 00010 to /home/oradata/stuorcl/sms01.dbf
channel ORA_DISK_1: reading from backup piece /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-27/full_stuorcl_20150127_3069.bak
^[
^[
channel ORA_DISK_1: piece handle=/oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-27/full_stuorcl_20150127_3069.bak tag=TAG20150127T030346
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 01:10:36
Finished restore at 02-FEB-15


RMAN> 
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01006: error signaled during parse
RMAN-02003: unrecognized character: 


MAN> 
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01006: error signaled during parse
RMAN-02003: unrecognized character: 


MAN> 


restore 結束,開始recover:


MAN> recover database;


Starting recover at 02-FEB-15
using channel ORA_DISK_1


starting media recovery


RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/02/2015 17:01:10
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 29186 and starting SCN of 10909658066 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 29185 and starting SCN of 10909532300 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 29184 and starting SCN of 10909335334 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 29183 and starting SCN of 10909087538 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 29182 and starting SCN of 10909083077 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 29181 and starting SCN of 10909082462 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 29180 and starting SCN of 10908905530 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 29179 and starting SCN of 10908836337 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 29178 and starting SCN of 10908811866 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 29177 and starting SCN of 10908758627 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 29176 and starting SCN of 10908700866 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 29175 and starting SCN of 10908695942 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 29174 and starting SCN of 10908693157 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 29173 and starting SCN of 10908683795 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 29172 and starting SCN of 10908674478 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 29171 and starting SCN of 10908665325 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 29170 and starting SCN of 10908660283 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 29169 and starting SCN of 10908655368 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 29168 and starting SCN of 10908650498 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 29167 and starting SCN of 10908638287 found to restore


RMAN> 
 
 
3,recover問題分析 
去原來備份庫上找到缺失的 29167到29186的dbf檔案,copy到測試庫(需要恢復的db伺服器)的歸檔日誌目錄下,如果不知道現在歸檔日誌,可以直接用sysdba登入檢視當前歸檔日誌目錄,如下所示:


3.1,檢視原來備份庫上的歸檔資訊
[oracle@xuexi4 ~]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 2 18:35:52 2015


Copyright (c) 1982, 2009, Oracle.  All rights reserved.




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL>
SQL> archive log list;
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination       /oracle/app/oracle/flash_recovery_area/archivelog
Oldest online log sequence     29185
Next log sequence to archive   29187
Current log sequence       29187
SQL> 
 
3.2,copy備份庫上面的  29167到29186的dbf歸檔日誌檔案到測試庫的歸檔日誌目錄下面
cp *29186* /tmp/rmanbak  
cp *29185*  /tmp/rmanbak
cp *29184*  /tmp/rmanbak
cp *29183*  /tmp/rmanbak
cp *29182*  /tmp/rmanbak
cp *29181*  /tmp/rmanbak
cp *29180*  /tmp/rmanbak
cp *29179*  /tmp/rmanbak
cp *29178*  /tmp/rmanbak
cp *29177*  /tmp/rmanbak
cp *29176*  /tmp/rmanbak
cp *29175*  /tmp/rmanbak
cp *29174*  /tmp/rmanbak
cp *29173*  /tmp/rmanbak
cp *29172*  /tmp/rmanbak
cp *29171*  /tmp/rmanbak
cp *29170*  /tmp/rmanbak
cp *29169*  /tmp/rmanbak
cp *29168*  /tmp/rmanbak
cp *29167*  /tmp/rmanbak
scp /tmp/rmanbak/* 192.168.121.217:/oracle/app/oracle/flash_recovery_area/archivelog/
 
3.3,檢視到原來的歸檔目錄和測試庫一樣,所以嘗試下繼續執行recover命令,然後繼續在測試庫的rman介面,進行recover操作:
RMAN> recover database;


Starting recover at 02-FEB-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=385 device type=DISK


starting media recovery


archived log for thread 1 with sequence 29167 is already on disk as file /oracle/app/oracle/flash_recovery_area/archivelog/1_29167_821708334.dbf
archived log for thread 1 with sequence 29168 is already on disk as file /oracle/app/oracle/flash_recovery_area/archivelog/1_29168_821708334.dbf
archived log for thread 1 with sequence 29169 is already on disk as file /oracle/app/oracle/flash_recovery_area/archivelog/1_29169_821708334.dbf
archived log for thread 1 with sequence 29170 is already on disk as file /oracle/app/oracle/flash_recovery_area/archivelog/1_29170_821708334.dbf
archived log for thread 1 with sequence 29171 is already on disk as file /oracle/app/oracle/flash_recovery_area/archivelog/1_29171_821708334.dbf
archived log for thread 1 with sequence 29172 is already on disk as file /oracle/app/oracle/flash_recovery_area/archivelog/1_29172_821708334.dbf
archived log for thread 1 with sequence 29173 is already on disk as file /oracle/app/oracle/flash_recovery_area/archivelog/1_29173_821708334.dbf
archived log for thread 1 with sequence 29174 is already on disk as file /oracle/app/oracle/flash_recovery_area/archivelog/1_29174_821708334.dbf
archived log for thread 1 with sequence 29175 is already on disk as file /oracle/app/oracle/flash_recovery_area/archivelog/1_29175_821708334.dbf
archived log for thread 1 with sequence 29176 is already on disk as file /oracle/app/oracle/flash_recovery_area/archivelog/1_29176_821708334.dbf
archived log for thread 1 with sequence 29177 is already on disk as file /oracle/app/oracle/flash_recovery_area/archivelog/1_29177_821708334.dbf
archived log for thread 1 with sequence 29178 is already on disk as file /oracle/app/oracle/flash_recovery_area/archivelog/1_29178_821708334.dbf
archived log for thread 1 with sequence 29179 is already on disk as file /oracle/app/oracle/flash_recovery_area/archivelog/1_29179_821708334.dbf
archived log for thread 1 with sequence 29180 is already on disk as file /oracle/app/oracle/flash_recovery_area/archivelog/1_29180_821708334.dbf
archived log for thread 1 with sequence 29181 is already on disk as file /oracle/app/oracle/flash_recovery_area/archivelog/1_29181_821708334.dbf
archived log for thread 1 with sequence 29182 is already on disk as file /oracle/app/oracle/flash_recovery_area/archivelog/1_29182_821708334.dbf
archived log for thread 1 with sequence 29183 is already on disk as file /oracle/app/oracle/flash_recovery_area/archivelog/1_29183_821708334.dbf
archived log for thread 1 with sequence 29184 is already on disk as file /oracle/app/oracle/flash_recovery_area/archivelog/1_29184_821708334.dbf
archived log for thread 1 with sequence 29185 is already on disk as file /oracle/app/oracle/flash_recovery_area/archivelog/1_29185_821708334.dbf
archived log for thread 1 with sequence 29186 is already on disk as file /oracle/app/oracle/flash_recovery_area/archivelog/1_29186_821708334.dbf
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=29166
channel ORA_DISK_1: reading from backup piece /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-27/arch_stuorcl_20150127_3070.bak
channel ORA_DISK_1: piece handle=/oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-27/arch_stuorcl_20150127_3070.bak tag=TAG20150127T033013
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/oracle/app/oracle/flash_recovery_area/archivelog/1_29166_821708334.dbf thread=1 sequence=29166
archived log file name=/oracle/app/oracle/flash_recovery_area/archivelog/1_29167_821708334.dbf thread=1 sequence=29167
archived log file name=/oracle/app/oracle/flash_recovery_area/archivelog/1_29168_821708334.dbf thread=1 sequence=29168
archived log file name=/oracle/app/oracle/flash_recovery_area/archivelog/1_29169_821708334.dbf thread=1 sequence=29169
archived log file name=/oracle/app/oracle/flash_recovery_area/archivelog/1_29170_821708334.dbf thread=1 sequence=29170
archived log file name=/oracle/app/oracle/flash_recovery_area/archivelog/1_29171_821708334.dbf thread=1 sequence=29171
archived log file name=/oracle/app/oracle/flash_recovery_area/archivelog/1_29172_821708334.dbf thread=1 sequence=29172
archived log file name=/oracle/app/oracle/flash_recovery_area/archivelog/1_29173_821708334.dbf thread=1 sequence=29173
archived log file name=/oracle/app/oracle/flash_recovery_area/archivelog/1_29174_821708334.dbf thread=1 sequence=29174
archived log file name=/oracle/app/oracle/flash_recovery_area/archivelog/1_29175_821708334.dbf thread=1 sequence=29175
archived log file name=/oracle/app/oracle/flash_recovery_area/archivelog/1_29176_821708334.dbf thread=1 sequence=29176
archived log file name=/oracle/app/oracle/flash_recovery_area/archivelog/1_29177_821708334.dbf thread=1 sequence=29177
archived log file name=/oracle/app/oracle/flash_recovery_area/archivelog/1_29178_821708334.dbf thread=1 sequence=29178
archived log file name=/oracle/app/oracle/flash_recovery_area/archivelog/1_29179_821708334.dbf thread=1 sequence=29179
archived log file name=/oracle/app/oracle/flash_recovery_area/archivelog/1_29180_821708334.dbf thread=1 sequence=29180
archived log file name=/oracle/app/oracle/flash_recovery_area/archivelog/1_29181_821708334.dbf thread=1 sequence=29181
archived log file name=/oracle/app/oracle/flash_recovery_area/archivelog/1_29182_821708334.dbf thread=1 sequence=29182
archived log file name=/oracle/app/oracle/flash_recovery_area/archivelog/1_29183_821708334.dbf thread=1 sequence=29183
archived log file name=/oracle/app/oracle/flash_recovery_area/archivelog/1_29184_821708334.dbf thread=1 sequence=29184
archived log file name=/oracle/app/oracle/flash_recovery_area/archivelog/1_29185_821708334.dbf thread=1 sequence=29185
archived log file name=/oracle/app/oracle/flash_recovery_area/archivelog/1_29186_821708334.dbf thread=1 sequence=29186
unable to find archived log
archived log thread=1 sequence=29187
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/02/2015 18:50:21
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 29187 and starting SCN of 10909913626


RMAN>  


3.3,看到有報錯,還是缺失歸檔日誌包,去現在測試庫/oracle/app/oracle/flash_recovery_area/archivelog/下面看了沒有新的29187歸檔日誌,所以從原來備份庫上copy過來的歸檔日誌都應該執行完了,直接試試開啟open。
RMAN> alter database open resetlogs;


database opened


RMAN> 
OK,到這裡rman恢復已經完全成功結束。




4,登入連線驗證,驗證連線報錯:
[oracle@xuexi4 admin]$ sqlplus "sys/sys@SC_PD";


SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 2 19:23:32 2015


Copyright (c) 1982, 2009, Oracle.  All rights reserved.


ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor




去檢查tnsnames.ora
SC_XDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.217)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = stuorcl)
    )
  )


登入去檢查當前庫的db_unique_name資訊
SQL> show parameter name;


NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert     string /home/oradata/stuorcl, /home/
oradata/pwerdes
db_name     string stuorcl
db_unique_name     string stuunq
global_names     boolean FALSE
instance_name     string stuorcl
lock_name_space     string
log_file_name_convert     string /home/oradata/stuorcl, /home/
oradata/stuorcl
service_names     string stuunq
SQL> 


發現db_unique_name是另外一個,所以將tnsnames.ora裡面的service_name從stuorcl換成了stuunq,就可以連線成功了。


5,找到原來的使用者下的表,匯出來恢復到線上。




總結步驟:
1,scp全備檔案到測試庫,在備份庫上查詢好引數檔案控制檔案歸檔路徑等資訊
2,關閉測試庫,啟動到nomount狀態,恢復引數檔案控制檔案
3,啟動到mount狀態,restore、recover操作
4,開啟資料庫alter database open resetlogs;

 ----------------------------------------------------------------------------------------------------------------
有,允許轉載,但必須以連結方式註明源地址,否則追究法律責任!>
原部落格地址:     http://blog.itpub.net/26230597/viewspace-1425212/
原作者:黃杉 (mchdba)
----------------------------------------------------------------------------------------------------------------

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26230597/viewspace-1425212/,如需轉載,請註明出處,否則將追究法律責任。

相關文章