ORA-01110: data file 7: '/u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00007'
本次試驗分為以下步驟
1 刪除表空間
2 檢查恢復
3 還原資料檔案
4 不完全恢復資料庫
5 以重製日誌選項開啟資料庫
6 確認資料庫恢復
step1 刪除表空間
插入時間戳資料作為參考
insert into snow.date_log values(sysdate,'snow1'); commit;
select * from snow.date_log order by create_time;
CREATE_TIME NAME
------------------- ------------------------------
2014-08-01 10:26:35 --
2014-08-01 10:27:35 --
2014-08-01 10:28:16 snow1 <==資料插入的時間
切換3次以上日誌,強制產生歸檔
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
drop tablespace indx including contents and datafilee;
出錯資訊為表空間中有主鍵約束,無法刪除
ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1ORA-02429: cannot drop index used for enforcement of unique/primary key
執行SQL語句產生一條刪除約束的命令
select 'alter table ' ||owner||'.'||table_name|| ' drop constraint ' ||constraint_name||' ;'
from dba_constraints
where constraint_type in ('U' , 'P')
and (index_owner, index_name) in
( select owner, segment_name
from dba_segments
where tablespace_name = 'INDX' );
alter table SNOW.DATE_LOG drop constraint CREATE_DATE_PK ;
drop tablespace indx including contents and datafilee;
此時alert日誌會記錄刪除表空間的資訊
Fri Aug 01 10:29:06 2014
Archived Log entry 24 added for thread 1 sequence 4 ID 0xb581bdfe dest 1:
Fri Aug 01 10:29:18 2014
Thread 1 advanced to log sequence 6 (LGWR switch)
Current log# 3 seq# 6 mem# 0: /oradata/PRACTICE/redo03.log
Fri Aug 01 10:29:18 2014 <==刪除表空間前最後一次日誌切換時間,不完全恢復時將要引用該時間
Archived Log entry 25 added for thread 1 sequence 5 ID 0xb581bdfe dest 1: <==最後一次切換日誌序列號
step2 檢查恢復
alter system switch logfile;
找出在重做日誌序列號為3的第一個變動序號,需要使用v$log_history
select sequence#,first_change# from v$log_history where sequence#=5;
SEQUENCE# FIRST_CHANGE#
---------- -------------
5 1077795
5 1299130
step3 還原資料檔案
還原所有的資料檔案,但是保留現在正使用的控制檔案,本實驗的關鍵點在此
shutdown immediate;
cp /backup/closed_backup/*.dbf /oradata/PRACTICE/
step4 不完全恢復資料庫
startup mount;
基於時間的恢復,時間點引用 “Fri Aug 01 10:29:18 2014”
alter database recover automatic until time '2014-08-01 10:29:18';
基於SCN的恢復
RECOVER DATABASE UNTIL CHANGE 1300637;
基於取消的恢復
alter database recover until cancel using backup controlfile;
step5 以重製日誌選項開啟資料庫
alter database open resetlogs;
step6 確認資料庫恢復
檢視INDX表空間是否被恢復
select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------------------------------------------------------------------ ----------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TOOLS ONLINE
INDX ONLINE
檢視日誌檔案是否有報錯
Fri Aug 01 10:42:35 2014
Errors in file /u01/app/oracle/diag/rdbms/practice/PRACTICE/trace/PRACTICE_j001_8206.trc:
ORA-12012: error on auto execute of job 24
ORA-00376: file 7 cannot be read at this time
ORA-01111: name for data file 7 is unknown - rename to correct file
ORA-01110: data file 7: '/u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00007’ <==7號資料檔案 有錯
ORA-06512: at "SNOW.CREATE_DATE_LOG_ROW", line 4
ORA-06512: at line 1
col tablespace_name for a10
col file_name for a60
檢視資料庫檔案資料字典
select file_id,tablespace_name,file_name,online_status from dba_data_files;
FILE_ID TABLESPACE FILE_NAME ONLINE_STATUS
---------- ---------- ------------------------------------------------------------ ---------------------
4 USERS /oradata/PRACTICE/users01.dbf ONLINE
3 UNDOTBS1 /oradata/PRACTICE/undotbs01.dbf ONLINE
2 SYSAUX /oradata/PRACTICE/sysaux01.dbf ONLINE
1 SYSTEM /oradata/PRACTICE/system01.dbf SYSTEM
5 EXAMPLE /oradata/PRACTICE/example01.dbf ONLINE
6 TOOLS /oradata/PRACTICE/tools01.dbf ONLINE
7 INDX /u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00007 RECOVER
8 USERS /oradata/PRACTICE/users02.dbf ONLINE
由於我們使用的是當前的控制檔案,已經“不認識”被刪除的表空間INDX和所包含的資料檔案datafile7。 從冷備複製回來的資料檔案datafile7在恢復過程中無法識別,就被建立到預設的/u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00007,為了可以正常啟動資料庫。
手工重置資料檔案的路徑,讓控制檔案重新識別
ALTER TABLESPACE indx RENAME DATAFILE '/u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00007' to '/oradata/PRACTICE/indx.dbf';
恢復資料檔案
recover datafile 7;
ORA-00279: change 1298436 generated at 08/01/2014 10:21:00 needed for thread 1
ORA-00289: suggestion : /archive/1_1_854446453.arc
ORA-00280: change 1298436 for thread 1 is in sequence #1
Specify log: {
ORA-00279: change 1299046 generated at 08/01/2014 10:26:58 needed for thread 1
ORA-00289: suggestion : /archive/1_2_854446453.arc
ORA-00280: change 1299046 for thread 1 is in sequence #2
Specify log: {
ORA-00279: change 1299121 generated at 08/01/2014 10:28:54 needed for thread 1
ORA-00289: suggestion : /archive/1_3_854446453.arc
ORA-00280: change 1299121 for thread 1 is in sequence #3
Specify log: {
ORA-00279: change 1299127 generated at 08/01/2014 10:29:03 needed for thread 1
ORA-00289: suggestion : /archive/1_4_854446453.arc
ORA-00280: change 1299127 for thread 1 is in sequence #4
Specify log: {
ORA-00279: change 1299130 generated at 08/01/2014 10:29:06 needed for thread 1
ORA-00289: suggestion : /archive/1_5_854446453.arc
ORA-00280: change 1299130 for thread 1 is in sequence #5
Specify log: {
Log applied.
Media recovery complete.
恢復後將資料檔案置為online狀態
alter database datafile 7 online;
select file_id,tablespace_name,file_name,online_status from dba_data_files;
FILE_ID TABLESPACE FILE_NAME ONLINE_STATUS
---------- ---------- ------------------------------------------------------------ ---------------------
4 USERS /oradata/PRACTICE/users01.dbf ONLINE
3 UNDOTBS1 /oradata/PRACTICE/undotbs01.dbf ONLINE
2 SYSAUX /oradata/PRACTICE/sysaux01.dbf ONLINE
1 SYSTEM /oradata/PRACTICE/system01.dbf SYSTEM
5 EXAMPLE /oradata/PRACTICE/example01.dbf ONLINE
6 TOOLS /oradata/PRACTICE/tools01.dbf ONLINE
7 INDX /oradata/PRACTICE/indx.dbf ONLINE
8 USERS /oradata/PRACTICE/users02.dbf ONLINE
檢視資料恢復是否成功
select * from snow.date_log order by create_time;
CREATE_TIME NAME
------------------- --------------------
2014-08-01 10:20:20 --
2014-08-01 10:21:35 --
2014-08-01 10:22:35 --
2014-08-01 10:23:35 --
2014-08-01 10:24:35 --
2014-08-01 10:25:35 --
2014-08-01 10:26:35 --
2014-08-01 10:27:35 --
2014-08-01 10:28:16 snow1
檢視被刪除的約束是否恢復成功
col INDEX_NAME for a20
col TABLE_NAME for a20
elect index_name,table_name from user_indexes;
INDEX_NAME TABLE_NAME
-------------------- --------------------
CREATE_DATE_PK DATE_LOG
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29047826/viewspace-1243775/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- u01/app/oracle/product/10.2.0/db_1/bin/dbca has failedAPPOracleAI
- 安裝12pdb遇到 Error in Process:/u01/app/oracle/product/12.1.0/db_1/perl/bin/perlErrorAPPOracle
- LRM-00109: could not open parameter file '/u01/.../dbs/initshdb.ora'
- What is the $ORACLE_HOME/dbs/hc_<ORACLE_SID>.dat fileOracle
- Unload data to a flat file from Oracle databaseOracleDatabase
- [轉載]Oracle等待事件Data file init writeOracle事件
- oracle 匯入報錯:field in data file exceeds maximum lengthOracle
- 【蓋國強】Oracle Wait Event:Data file init writeOracleAI
- oracle10g 資料檔案頭data file header(file header)OracleHeader
- $ORACLE_HOME/dbs下的lk檔案Oracle
- 打補丁報錯 File in patch is not a known Oracle Applications fileOracleAPP
- Clone Madison 12R TEST application with customized data file locationAPPZed
- HP -Data Protector Restore file systemREST
- Oracle 12C Database File Mapping for Oracle ASM FilesOracleDatabaseAPPASM
- oracle ORA-01157: cannot identify/lock data file 64OracleIDE
- sqlldr Field in data file exceeds maximum lengthSQL
- oracle active data guard real-time apply特性OracleAPP
- oracle 10g data guard log apply servicesOracle 10gAPP
- Default Data Type Mappings Used by Oracle SQL DeveloperAPPOracleSQLDeveloper
- data file int write和db file sequential read個人想法
- Oracle Appliactions 11i concepts(二) - Application File System(1)OracleAPP
- PG data wrapper 連線oracle 資料庫oracle_fdw 安裝APPOracle資料庫
- Oracle X7一體機儲存節點重啟導致u01使用率不斷增大Oracle
- ELF file data encoding not little-endianEncoding
- HP -Data Protector Restore file system【Blog 搬家】REST
- oracle is not in the sudoers fileOracle
- Rich Internet Applications and AJAX - Selecting the best productAPP
- Oracle Data BufferOracle
- oracle data guard!!Oracle
- oracle data pumpOracle
- Product Quantization
- GoldenGate replication using a data definition file and DEFGEN utilityGo
- SAP CRM Product hierarchy,Category和Application三個問題GoAPP
- 不Root也可以app的/data/data/目錄APP
- Oracle ASM File DirectoryOracleASM
- oracle utl_fileOracle
- Oracle EBS ATTACHMENT with fileOracle
- OMF (Oracle Managed File)Oracle