程序師世界是廣大編程愛好者互助、分享、學習的平台，程序師世界有你更精彩！
設為首頁 加入收藏
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> ORACLE中沒有參數文件和控制文件如何通過rman恢復數據庫

ORACLE中沒有參數文件和控制文件如何通過rman恢復數據庫

編輯：Oracle教程

ORACLE中沒有參數文件和控制文件如何通過rman恢復數據庫


場景： 一個DEV告訴我生產環境下某個用戶的表都看不到了，需要恢復，而此時生產庫上存儲自動備份的參數文件控制文件的磁盤目錄文件壞塊，所以導致rman備份的只有數據文件和歸檔日志文件，這種情況下，如何在測試服務器上利用rman恢復數據呢？google了很多資料，咨詢了朋友，恢復過程如下：         前期准備工作： 事先查詢好先查詢下原來的數據文件路徑 
SQL> select name from v$datafile;


NAME
--------------------------------------------------------------------------------
/home/oradata/stuorcl/system01.dbf
/home/oradata/stuorcl/sysaux01.dbf
/home/oradata/stuorcl/undotbs01.dbf
/home/oradata/stuorcl/users01.dbf
/home/oradata/stuorcl/stuorclk01.dbf
/home/oradata/stuorcl/plas01.dbf
/home/oradata/stuorcl/pl01.dbf
/home/oradata/stuorcl/help01.dbf
/home/oradata/stuorcl/adobelc01.dbf
/home/oradata/stuorcl/sms01.dbf


10 rows selected.


SQL>

 

  事先獲得redo日志文件路徑 
SQL> select member from v$logfile;


MEMBER
--------------------------------------------------------------------------------
/home/oradata/stuorcl/redo03.log
/home/oradata/stuorcl/redo02.log
/home/oradata/stuorcl/redo01.log
/home/oradata/stuorcl/redo_dg_01.log
/home/oradata/stuorcl/redo_dg_02.log
/home/oradata/stuorcl/redo_dg_03.log


6 rows selected.


SQL>

 

    1，nomount下創建控制文件； 
[root@xuelong4 ~]# su - oracle
[oracle@xuelong4 ~]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 4 13:48:23 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> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

 

    2，啟動到nomount狀態 
SQL> startup nomount
ORACLE instance started.


Total System Global Area 1.1358E+10 bytes
Fixed Size    2216744 bytes
Variable Size 8589937880 bytes
Database Buffers 2751463424 bytes
Redo Buffers   13946880 bytes
SQL>

 

    3，創建控制文件 

CREATE CONTROLFILE REUSE set DATABASE "stuorcl" RESETLOGS ARCHIVELOG
   MAXLOGFILES 16
   MAXLOGMEMBERS 3
   MAXDATAFILES 100
   MAXINSTANCES 8
   MAXLOGHISTORY 454
DATAFILE
'/home/oradata/stuorcl/system01.dbf',
'/home/oradata/stuorcl/sysaux01.dbf',
'/home/oradata/stuorcl/undotbs01.dbf',
'/home/oradata/stuorcl/users01.dbf',
'/home/oradata/stuorcl/stuorclk01.dbf',
'/home/oradata/stuorcl/plas01.dbf',
'/home/oradata/stuorcl/pl01.dbf',
'/home/oradata/stuorcl/help01.dbf',
'/home/oradata/stuorcl/adobelc01.dbf',
'/home/oradata/stuorcl/sms01.dbf'
LOGFILE
GROUP 1 ('/home/oradata/stuorcl/redo03.log')  SIZE 10M,
GROUP 2 ('/home/oradata/stuorcl/redo02.log')  SIZE 10M,
GROUP 3 ('/home/oradata/stuorcl/redo01.log')  SIZE 10M,
GROUP 4 ('/home/oradata/stuorcl/redo_dg_01.log')  SIZE 10M,
GROUP 5 ('/home/oradata/stuorcl/redo_dg_02.log')  SIZE 10M,
GROUP 6 ('/home/oradata/stuorcl/redo_dg_03.log')  SIZE 10M
CHARACTER SET ZHS16GBK;

 

    執行過程： 
SQL> CREATE CONTROLFILE REUSE set DATABASE "stuorcl" RESETLOGS ARCHIVELOG
   MAXLOGFILES 16
   MAXLOGMEMBERS 3
   MAXDATAFILES 100
   MAXINSTANCES 8
   MAXLOGHISTORY 454
DATAFILE
'/home/oradata/stuorcl/system01.dbf',
'/home/oradata/stuorcl/sysaux01.dbf',
 '/home/oradata/stuorcl/undotbs01.dbf',
'/home/oradata/stuorcl/users01.dbf',
'/home/oradata/stuorcl/stuorclk01.dbf',
'/home/oradata/stuorcl/plas01.dbf',
'/home/oradata/stuorcl/pl01.dbf',
'/home/oradata/stuorcl/help01.dbf',
'/home/oradata/stuorcl/adobelc01.dbf',
'/home/oradata/stuorcl/sms01.dbf'
LOGFILE
GROUP 1 ('/home/oradata/stuorcl/redo03.log')  SIZE 10M,
GROUP 2 ('/home/oradata/stuorcl/redo02.log')  SIZE 10M,
GROUP 3 ('/home/oradata/stuorcl/redo01.log')  SIZE 10M,
GROUP 4 ('/home/oradata/stuorcl/redo_dg_01.log')  SIZE 10M,
GROUP 5 ('/home/oradata/stuorcl/redo_dg_02.log')  SIZE 10M,
GROUP 6 ('/home/oradata/stuorcl/redo_dg_03.log')  SIZE 10M
CHARACTER SET ZHS16GBK; 


Control file created.


SQL>

 

    4，在新控制文件中注冊數據文件備份和歸檔備份   catalog start with '/oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-23/'; 在mount狀態下執行 
RMAN> catalog start with '/oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-23/';


released channel: ORA_DISK_1
searching for all files that match the pattern /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-23/


List of Files Unknown to the Database
=====================================
File Name: /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-23/full_stuorcl_20150123_3051.bak
File Name: /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-23/rman_backup.log
File Name: /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-23/arch_stuorcl_20150123_3050.bak
File Name: /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-23/arch_stuorcl_20150123_3052.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-23/full_stuorcl_20150123_3051.bak
File Name: /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-23/arch_stuorcl_20150123_3050.bak
File Name: /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-23/arch_stuorcl_20150123_3052.bak


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


RMAN>

 

    5，進行修復操作 
RMAN>  restore database;

Starting restore at 04-FEB-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=767 device type=DISK


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-23/full_stuorcl_20150123_3051.bak
channel ORA_DISK_1: piece handle=/oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-23/full_stuorcl_20150123_3051.bak tag=TAG20150123T030053
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:45:45
Finished restore at 04-FEB-15


RMAN>

 

  此時去看oracle後台的alert日志，正在一個個修復數據文件，信息如下： 
[root@xuelong4 trace]# tail -f /oracle/app/oracle/diag/rdbms/stuunq/stuorcl/trace/alert_stuorcl.log 
GROUP 5 ('/home/oradata/stuorcl/redo_dg_02.log')  SIZE 10M,
GROUP 6 ('/home/oradata/stuorcl/redo_dg_03.log')  SIZE 10M
CHARACTER SET ZHS16GBK
Wed Feb 04 17:05:40 2015
alter database mount
ORA-1100 signalled during: alter database mount
...
Wed Feb 04 17:05:55 2015
alter database mount
ORA-1100 signalled during: alter database mount...
Wed Feb 04 17:08:38 2015
Full restore complete of datafile 8 /home/oradata/stuorcl/help01.dbf.  Elapsed time: 0:00:02 
  checkpoint is 10903299975
  last deallocation scn is 9881798870
Full restore complete of datafile 9 /home/oradata/stuorcl/adobelc01.dbf.  Elapsed time: 0:00:00 
  checkpoint is 10903299975
Wed Feb 04 17:08:53 2015
Full restore complete of datafile 10 /home/oradata/stuorcl/sms01.dbf.  Elapsed time: 0:00:07 
  checkpoint is 10903299975
Wed Feb 04 17:09:52 2015
Full restore complete of datafile 7 /home/oradata/stuorcl/pl01.dbf.  Elapsed time: 0:00:46 
  checkpoint is 10903299975
  last deallocation scn is 10891613103
Wed Feb 04 17:12:41 2015
Full restore complete of datafile 3 /home/oradata/stuorcl/undotbs01.dbf.  Elapsed time: 0:03:28 
  checkpoint is 10903299975
  last deallocation scn is 10903242336
  Undo Optimization current scn is 10903252140
Wed Feb 04 17:14:00 2015
Full restore complete of datafile 4 /home/oradata/stuorcl/users01.dbf.  Elapsed time: 0:05:26 
  checkpoint is 10903299975
  last deallocation scn is 10893349080
Wed Feb 04 17:17:52 2015
Full restore complete of datafile 1 /home/oradata/stuorcl/system01.dbf.  Elapsed time: 0:08:11 
  checkpoint is 10903299975
  last deallocation scn is 10825889348
  Undo Optimization current scn is 10903252140
Wed Feb 04 17:20:27 2015
db_recovery_file_dest_size of 15360 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Wed Feb 04 17:20:40 2015
Full restore complete of datafile 2 /home/oradata/stuorcl/sysaux01.dbf.  Elapsed time: 0:12:02 
  checkpoint is 10903299975
  last deallocation scn is 10901674267
  ........

 

    6，開始恢復操作 
RMAN>  recover database;


Starting recover at 04-FEB-15
using channel ORA_DISK_1


starting media recovery


channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=29053
channel ORA_DISK_1: reading from backup piece /oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-23/arch_stuorcl_20150123_3052.bak
channel ORA_DISK_1: piece handle=/oracle/app/oracle/flash_recovery_area/stuorcl/backupset/2015-01-23/arch_stuorcl_20150123_3052.bak tag=TAG20150123T032709
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
archived log file name=/oracle/app/oracle/flash_recovery_area/archivelog/1_29053_821708334.dbf thread=1 sequence=29053
unable to find archived log
archived log thread=1 sequence=29054
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/04/2015 16:19:14
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 29054 and starting SCN of 10903302108


RMAN>

 

    在11g官方文檔error messages中對錯誤描述如下： 
RMAN-06054: media recovery requesting unknown archived log for thread string with sequence string and starting SCN of string
Cause: Media recovery is requesting a log whose existence is not recorded in the recovery catalog or target database control file.
Action: If a copy of the log is available, then add it to the recovery catalog and/or control file via a CATALOG command and then retry the RECOVER command. If not, then a point-in-time recovery up to the missing log is the only alternative and database can be opened using ALTER DATABASE OPEN RESETLOGS command.

 

  可見，出先此錯誤的原因是恢復需要的日志記錄在控制文件或恢復目錄中找不到。解決方法分兩種情況： 1.如果相關的日志存在且可用的話，就將此日志記錄添加到控制文件或恢復目錄中。 2.如果相關的日志已經被刪除了或不可用了，那麼就按照錯誤的提示scn將數據庫恢復到此scn，本案例是10903302108。也就是說此時數據庫只能進行不完全恢復了，在打開數據庫時得使用resetlogs打開。 

recover database until scn 10903302108;
RMAN> recover database until scn 10903302108;


Starting recover at 04-FEB-15
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/04/2015 16:30:30
RMAN-20208: UNTIL CHANGE is before RESETLOGS change


RMAN> 


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
2       2       stuorcl 3391761643       PARENT  945184     25-JUL-13
1       1       stuorcl 3391761643       CURRENT 10909913627 02-FEB-15


RMAN>

 

google下，看到http://blog.itpub.net/26442936/viewspace-767439/上面有說明 
RMAN> reset database to incarnation 2;
RMAN> restore database until scn 1045382;
RMAN> recover database until scn 1045382;
SQL> alter database open resetlogs;

 

的方式，不過我的incarnation 2已經是2013年的太早了，這種方式不適合。       嘗試rman的不完全恢復記錄，切換到SQL窗口去： 
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 10903299975 generated at 01/23/2015 03:00:53 needed for
thread 1
ORA-00289: suggestion :
/oracle/app/oracle/flash_recovery_area/archivelog/1_29053_821708334.dbf
ORA-00280: change 10903299975 for thread 1 is in sequence #29053


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}


ORA-00279: change 10903302108 generated at 01/23/2015 03:27:09 needed for
thread 1
ORA-00289: suggestion :
/oracle/app/oracle/flash_recovery_area/archivelog/1_29054_821708334.dbf
ORA-00280: change 10903302108 for thread 1 is in sequence #29054
ORA-00278: log file
'/oracle/app/oracle/flash_recovery_area/archivelog/1_29053_821708334.dbf' no
longer needed for this recovery




Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/oracle/app/oracle/flash_recovery_area/archivelog/1_29054_821708334.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3




ORA-00308: cannot open archived log
'/oracle/app/oracle/flash_recovery_area/archivelog/1_29054_821708334.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3




SQL> 


SQL> alter database open resetlogs;


Database altered.


SQL>

 

    7，去後台驗證下數據，找一張一直有日期變化的表數據： 
SQL> select  to_char(a.last_login_date,'yyyy-mm-dd hh24:mi:ss') from stu.pst_ac a where to_char(a.last_login_date,'yyyy-mm-dd hh24:mi:ss')>'2015-01-23 00:00:00:00' order by to_char(a.last_login_date,'yyyy-mm-dd hh24:mi:ss')  desc;


TO_CHAR(A.LAST_LOGI
-------------------
2015-01-23 03:10:38
2015-01-23 02:36:13
2015-01-23 01:47:36
2015-01-23 01:29:14
2015-01-23 01:27:41
2015-01-23 01:09:21
2015-01-23 00:52:14
2015-01-23 00:44:52
2015-01-23 00:43:46
2015-01-23 00:32:37
2015-01-23 00:31:59


TO_CHAR(A.LAST_LOGI
-------------------
2015-01-23 00:30:44
2015-01-23 00:27:27
2015-01-23 00:23:20
2015-01-23 00:18:53
2015-01-23 00:17:46
2015-01-23 00:13:25
2015-01-23 00:00:15


18 rows selected.


SQL>

 

看到最新為1月23日的數據，表明恢復的數據已經是1月23日的。 

去[oracle@xuelong4 admin]$ sqlplus elearning/plel0328@SC_SID;


SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 4 19:03:56 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> select ut.TABLE_NAME from user_tables ut where rownum<3;

 


  1. 上一頁:
  2. 下一頁:
Oracle教程
Oracle SQL*plus常用的命令和函數，oracleplus

Oracle SQL*plus常用的命令和函數，oracle
ORACLE10G官方文檔演示：查找手工建庫語句

ORACLE10G官方文檔演示：查找手工建庫語句

使用geotools導入shp文件到Oracle數據庫時表名帶下劃線問題的解決方法，

使用geotools導入shp文件到Oracle數據庫時表名
TOAD和PLSQL 默認日期顯示、rowid顯示、TNSNAME的修改，plsqlrowid

TOAD和PLSQL 默認日期顯示、rowid顯示、TNSN
oracle中的游標的原理和使用詳解

oracle中的游標的原理和使用詳解 游標 游標的
ORACLE問題集

ORACLE問題集 這篇博客記載我在用數據庫時
相關文章
閱讀排行榜
【Oracle】數據庫運行狀態下物理刪除所有redo日志恢復方法 oracleloopif循環 oracle的內存管理（之一） ORA-16401:archivelogrejectedbyRFS oracle外部表如何忽略第一行的標題 oracle數據庫連接串相關問題 JDBC-Oracle，jdbc Oracle實現行列轉換的方法分析，oracle實現行列轉換 linux下Oracle11gRAC搭建（一） ORA-01336: specified dictionary file cannot be opened，ora-01336specified oracle性能優化操作十八：決定使用全表掃描還是使用索引
熱門圖文
MySQL的一些基本操作 解決MySQL中文亂碼的方法歸納 php入門mysql分頁調用程序 關於Mysql權限表的使用小結 MySQL之表結構修改 特殊的存儲過程-觸發器概述 分享：在存儲過程中使用另一個存儲過程返回的查詢結果集的方法 SQL教程:聯接JOIN使用大全
欄目導航
關於Oracle數據庫Oracle數據庫基礎Oracle教程
Copyright © 程式師世界 All Rights Reserved