[20171225]沒有備份資料檔案的恢復.txt
[20171225]沒有備份資料檔案的恢復.txt
--//別人問的問題,增加了資料檔案沒有備份,如何恢復,實際上很簡單,因為當前控制檔案有記錄建立時間只要從建立資料檔案開始的
--//歸檔日誌都存在恢復是沒有任何問題的.我僅僅介紹rman的恢復:
1.環境:
SCOTT@book> @&r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
CREATE TABLESPACE SUGAR DATAFILE
'/mnt/ramdisk/book/sugar01.dbf' SIZE 6M AUTOEXTEND OFF
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
SCOTT@book> create table tx tablespace sugar as select * from dba_objects where rownum<=1000;
Table created.
SCOTT@book> delete from tx where rownum<=10 ;
10 rows deleted.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> select count(*) from tx ;
COUNT(*)
----------
990
SCOTT@book> alter system archive log current ;
System altered.
SCOTT@book> alter system archive log current ;
System altered.
SCOTT@book> alter system archive log current ;
System altered.
SCOTT@book> alter system archive log current ;
System altered.
2.測試:
--//人為刪除資料檔案:
$ rm /mnt/ramdisk/book/sugar01.dbf
/bin/rm: remove regular file `/mnt/ramdisk/book/sugar01.dbf'? y
SCOTT@book> select count(*) from tx ;
COUNT(*)
----------
990
SCOTT@book> alter system flush buffer_cache;
System altered.
SCOTT@book> select count(*) from tx ;
select count(*) from tx
*
ERROR at line 1:
ORA-01116: error in opening database file 7
ORA-01110: data file 7: '/mnt/ramdisk/book/sugar01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SCOTT@book> SELECT file#, CREATION_TIME,CHECKPOINT_CHANGE#,CHECKPOINT_TIME,UNRECOVERABLE_CHANGE#,LAST_CHANGE#,LAST_TIME, OFFLINE_CHANGE#, ONLINE_CHANGE#,FIRST_NONLOGGED_SCN,FIRST_NONLOGGED_TIME,status,name FROM v$datafile where file#=7;
FILE# CREATION_TIME CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TIM STATUS NAME
----- ------------------- ------------------ ------------------- --------------------- ------------ ------------------- --------------- -------------- ------------------- ------------------- ------- --------------------------------------------------
7 2017-12-25 15:53:23 13277924712 2017-12-25 15:54:52 0 13277925346 2017-12-25 16:00:21 0 0 0 RECOVER /mnt/ramdisk/book/sugar01.dbf
--//在控制檔案裡面記錄了建立資料檔案的時間(2017-12-25 15:53:23).只要這些歸檔在問題就不大.
RMAN> report schema ;
Report of database schema for database with db_unique_name BOOK
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 760 SYSTEM *** /mnt/ramdisk/book/system01.dbf
2 940 SYSAUX *** /mnt/ramdisk/book/sysaux01.dbf
3 865 UNDOTBS1 *** /mnt/ramdisk/book/undotbs01.dbf
4 128 USERS *** /mnt/ramdisk/book/users01.dbf
5 346 EXAMPLE *** /mnt/ramdisk/book/example01.dbf
6 6 TEA *** /mnt/ramdisk/book/tea01.dbf
7 0 SUGAR *** /mnt/ramdisk/book/sugar01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 414 TEMP 32767 /mnt/ramdisk/book/temp01.dbf
RMAN> list failure ;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------------- -------
71648 HIGH OPEN 2017-12-25 15:56:41 One or more non-system datafiles are missing
RMAN> list failure 71648 detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------------- -------
71648 HIGH OPEN 2017-12-25 15:56:41 One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 71648
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------------- -------
86741 HIGH OPEN 2017-12-25 15:56:41 Datafile 7: '/mnt/ramdisk/book/sugar01.dbf' is missing
Impact: Some objects in tablespace SUGAR might be unavailable
RMAN> advise failure ;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------------- -------
71648 HIGH OPEN 2017-12-25 15:56:41 One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 71648
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------------- -------
86741 HIGH OPEN 2017-12-25 15:56:41 Datafile 7: '/mnt/ramdisk/book/sugar01.dbf' is missing
Impact: Some objects in tablespace SUGAR might be unavailable
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=119 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=132 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=161 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file /mnt/ramdisk/book/sugar01.dbf was unintentionally renamed or moved, restore it
2. Shut down, mount the database and try flush redo using ALTER SYSTEM FLUSH REDO TO 'standby name' command. Then
perform a Data Guard role change (failover). Available standbys: bookdg.
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 7
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/book/book/hm/reco_1003154902.hm
$ cat /u01/app/oracle/diag/rdbms/book/book/hm/reco_1003154902.hm
# restore and recover datafile
sql 'alter database datafile 7 offline';
restore datafile 7;
recover datafile 7;
sql 'alter database datafile 7 online';
--//按照命令文字介紹執行如下:
RMAN> sql 'alter database datafile 7 offline';
sql statement: alter database datafile 7 offline
RMAN> restore datafile 7;
Starting restore at 2017-12-25 16:00:26
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
creating datafile file number=7 name=/mnt/ramdisk/book/sugar01.dbf
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
restore not done; all files read only, offline, or already restored
Finished restore at 2017-12-25 16:00:26
--//注意看下劃線內容,資料檔案建立.
RMAN> recover datafile 7;
Starting recover at 2017-12-25 16:05:20
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
starting media recovery
archived log for thread 1 with sequence 797 is already on disk as file /u01/app/oracle/archivelog/book/1_797_896605872.dbf
archived log for thread 1 with sequence 798 is already on disk as file /u01/app/oracle/archivelog/book/1_798_896605872.dbf
archived log for thread 1 with sequence 799 is already on disk as file /u01/app/oracle/archivelog/book/1_799_896605872.dbf
archived log for thread 1 with sequence 800 is already on disk as file /u01/app/oracle/archivelog/book/1_800_896605872.dbf
archived log file name=/u01/app/oracle/archivelog/book/1_797_896605872.dbf thread=1 sequence=797
archived log file name=/u01/app/oracle/archivelog/book/1_798_896605872.dbf thread=1 sequence=798
media recovery complete, elapsed time: 00:00:00
Finished recover at 2017-12-25 16:05:21
RMAN> sql 'alter database datafile 7 online';
sql statement: alter database datafile 7 online
SCOTT@book> select count(*) from tx ;
COUNT(*)
------------
990
--//OK,資料沒有丟失.
3.再介紹sqlplus來恢復:
--//重複操作:
$ rm /mnt/ramdisk/book/sugar01.dbf
/bin/rm: remove regular file `/mnt/ramdisk/book/sugar01.dbf'? y
SCOTT@book> alter system flush buffer_cache;
System altered.
SCOTT@book> select count(*) from tx ;
select count(*) from tx
*
ERROR at line 1:
ORA-01116: error in opening database file 7
ORA-01110: data file 7: '/mnt/ramdisk/book/sugar01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
--//在sqlplus也可以執行,建立資料檔案:
SCOTT@book> alter database create datafile 7 as '/mnt/ramdisk/book/sugar01.dbf';
alter database create datafile 7 as '/mnt/ramdisk/book/sugar01.dbf'
*
ERROR at line 1:
ORA-01182: cannot create database file 7 - file is in use or recovery
ORA-01110: data file 7: '/mnt/ramdisk/book/sugar01.dbf'
SCOTT@book> alter database datafile 7 offline ;
Database altered.
SCOTT@book> alter database create datafile 7 as '/mnt/ramdisk/book/sugar01.dbf';
Database altered.
SCOTT@book> recover datafile 7;
ORA-00279: change 13277924104 generated at 12/25/2017 15:53:23 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_797_896605872.dbf
ORA-00280: change 13277924104 for thread 1 is in sequence #797
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 13277924687 generated at 12/25/2017 15:54:45 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_798_896605872.dbf
ORA-00280: change 13277924687 for thread 1 is in sequence #798
Log applied.
Media recovery complete.
SCOTT@book> alter database datafile 7 online ;
Database altered.
SCOTT@book> select count(*) from tx ;
COUNT(*)
----------
990
--//只要概念清晰,這樣的恢復很容易完成.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2149231/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 沒有備份的資料檔案恢復（五）
- 歸檔模式，恢復沒有備份的資料檔案模式
- rman恢復--歸檔模式有備份，丟失資料檔案的恢復模式
- 資料庫在沒有備份的情況下的資料檔案損壞的恢復資料庫
- [20170213]刪除資料沒有備份可以恢復嗎.txt
- 【備份恢復】無備份線上恢復非關鍵資料檔案
- 沒備份，歸檔日誌存在，丟失資料檔案的恢復
- Oracle 之利用BBED修改資料塊SCN----沒有備份資料檔案的資料恢復Oracle資料恢復
- RMAN資料庫恢復 之歸檔模式有(無)備份-丟失資料檔案的恢復資料庫模式
- 歸檔模式有備份丟失資料檔案後恢復模式
- Oracle備份與恢復【丟失資料檔案的恢復】Oracle
- rman備份恢復-rman恢復資料檔案測試
- REDO檔案丟失的恢復__沒有任何備份的情況
- 備份與恢復--利用備份的控制檔案恢復
- 備份與恢復--重建控制檔案後資料檔案損壞的恢復
- 使用備份的控制檔案恢復資料庫資料庫
- 備份恢復之資料檔案丟失
- rman備份-（1） 利用備份級恢復資料檔案和控制檔案
- rman恢復--歸檔模式無備份，丟失資料檔案的恢復模式
- 歸檔模式有備份丟失控制檔案和資料檔案後恢復模式
- oracle實驗記錄 （恢復-恢復未備份的資料檔案）Oracle
- 備份與恢復--從備份的歸檔日誌中恢復資料
- ORACLE 11G沒有備份檔案引數檔案在異機通過rman備份恢復找回被誤刪的資料Oracle
- rman恢復資料庫--用備份的控制檔案資料庫
- ORACLE 只讀資料檔案備份與恢復Oracle
- 備份與恢復系列 十一 控制檔案的備份與恢復
- 【備份恢復】從備份恢復資料庫資料庫
- 【備份與恢復】archivelog模式中資料檔案的恢復Hive模式
- 【備份與恢復】noarchivelog模式中資料檔案的恢復Hive模式
- 【備份恢復】歸檔模式下丟失系統關鍵資料檔案 利用RMAN備份恢復模式
- 【備份恢復】利用 備份控制檔案到指定目錄下的控制檔案 恢復控制檔案
- 備份與恢復（Parameter 檔案恢復篇）
- 【備份恢復】非歸檔模式下丟失任意資料檔案 恢復操作模式
- 沒有自動備份的情況下控制檔案全部丟失的恢復
- 利用備份的控制檔案恢復
- 控制檔案的備份和恢復
- 【備份與恢復】恢復受損的口令檔案
- RMAN備份恢復之控制檔案的恢復（三）