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',
2 3 4 5 6 7 8 9 10 '/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; 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
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: {=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: {=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;
no rows selected
----------------------------------------------------------------------------------------------------------------
有，文章允許轉載，但必須以連結方式註明源地址，否則追究法律責任!>
原部落格地址： http://blog.itpub.net/26230597/viewspace-1426675/
原作者：黃杉 (mchdba)
----------------------------------------------------------------------------------------------------------------
SQL>
沒有一條表記錄，所以還是需要找找原因，為什麼使用者下的表看不見了，~_~。
前期準備工作：
事先查詢好先查詢下原來的資料檔案路徑
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',
2 3 4 5 6 7 8 9 10 '/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; 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
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: {
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: {
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;
no rows selected
----------------------------------------------------------------------------------------------------------------
有，文章允許轉載，但必須以連結方式註明源地址，否則追究法律責任!>
原部落格地址： http://blog.itpub.net/26230597/viewspace-1426675/
原作者：黃杉 (mchdba)
----------------------------------------------------------------------------------------------------------------
SQL>
沒有一條表記錄，所以還是需要找找原因，為什麼使用者下的表看不見了，~_~。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26230597/viewspace-1426675/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 請教windows下有所有的資料檔案，控制檔案，但沒有引數檔案，如何恢復？Windows
- 引數檔案控制檔案和資料檔案丟失的恢復
- ORACLE 11G沒有備份檔案引數檔案在異機通過rman備份恢復找回被誤刪的資料Oracle
- Oracle 11g 通過 RMAN 檢查資料檔案、控制檔案、引數檔案的狀態和是否存在壞塊Oracle
- RMAN恢復控制檔案
- rman恢復資料庫--用備份的控制檔案資料庫
- RMAN恢復案例:無恢復目錄，丟失全部資料檔案、控制檔案、日誌檔案恢復
- 通過檔案控制程式碼恢復刪除的資料檔案
- 使用rman恢復控制檔案
- 【RMAN】如果控制檔案損壞那麼如何恢復？恢復控制檔案的方式有哪幾種？
- rman備份-（1） 利用備份級恢復資料檔案和控制檔案
- RMAN恢復表空間,資料檔案,歸檔檔案,控制檔案等介紹
- 誤刪除資料檔案、控制檔案的非RMAN恢復方法
- 控制檔案快照和rman利用快照恢復
- 控制檔案恢復—從trace檔案中恢復
- rman恢復時跳過資料檔案，進行恢復
- Oracle資料庫控制檔案在備份恢復中的作用Oracle資料庫
- 所有除引數檔案以外的檔案都丟失，但是隻有資料檔案的 RMAN 備份的恢復
- 引數檔案恢復
- 歸檔模式有備份丟失控制檔案和資料檔案後恢復模式
- RMAN資料庫恢復 之歸檔模式有(無)備份-丟失資料檔案的恢復資料庫模式
- Oracle11g資料庫引數檔案誤刪除恢復Oracle資料庫
- rman恢復--丟失控制檔案的恢復
- 資料恢復新姿勢——通過ibd和frm檔案恢復資料資料恢復
- 所有除引數檔案以外的檔案都丟失，但是隻有資料檔案的 RMAN 備份的恢復2
- rman恢復--歸檔模式有備份，丟失資料檔案的恢復模式
- rman備份恢復-rman恢復資料檔案測試
- 歸檔模式，恢復沒有備份的資料檔案模式
- rman恢復資料檔案 恢復表空間
- RMAN - "丟失控制檔案的恢復"
- rman恢復控制檔案測試--log
- 控制檔案丟失的RMAN恢復
- 沒有備份的資料檔案恢復（五）
- oracle資料庫移動資料檔案、日誌檔案和控制檔案Oracle資料庫
- 使用RMAN備份集和過時的控制檔案進行資料庫的恢復(2/2)資料庫
- 使用RMAN備份集和過時的控制檔案進行資料庫的恢復(1/2)資料庫
- oracle RMAN 非歸檔資料庫恢復Oracle資料庫
- 【/proc/檔案淺析】另類辦法恢復資料檔案和控制檔案