一個錯誤的資料檔案的恢復
環境:RHEL AS3 測試庫
12:03:07 SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 – Production
問題: 測試庫不能正常SHUTDOWN
SQL>shutdown immediate;
ORA-01116: error in opening database file 15
ORA-01110: data file 15: '/SERVER/ora9/oradata/ora9
'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
檢視資料檔案.
SQL>select file_id,file_name,tablespace_name from dba_data_files
1 /SERVER/ora9/oradata/ora9/system01.dbf SYSTEM
2 /SERVER/ora9/oradata/ora9/hyf.dbf HYF
3 /SERVER/ora9/oradata/ora9/cwmlite01.dbf CWMLITE
4 /SERVER/ora9/oradata/ora9/drsys01.dbf DRSYS
5 /SERVER/ora9/oradata/ora9/indx01.dbf INDX
6 /SERVER/ora9/oradata/ora9/odm01.dbf ODM
7 /SERVER/ora9/oradata/ora9/tools01.dbf TOOLS
8 /SERVER/ora9/oradata/ora9/users01.dbf USERS
9 /SERVER/ora9/oradata/ora9/xdb01.dbf XDB
10 /SERVER/ora9/oradata/ora9/t2007q1.dbf T2007Q1
11 /SERVER/ora9/oradata/ora9/t2007q2.dbf T2007Q2
12 /SERVER/ora9/oradata/ora9/t2007q3.dbf T2007Q3
13 /SERVER/ora9/oradata/ora9/t2007q4.dbf T2007Q4
14 /SERVER/ora9/oradata/ora9/undo.dbf UNDO
15 /SERVER/ora9/oradata/ora9 USERDATA
16 /SERVER/ora9/oradata/ora9/atu.dbf ATU
發現datafile 15 的檔名跟我的目錄名一樣.
想直接刪除表空間:
SQL>drop tablespace USERDATA ;
ORA-01116: error in opening database file 15
ORA-01110: data file 15: '/SERVER/ora9/oradata/ora9
'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
OFFLINE:
SQL>alter tablespace UESRDATA offline;
ORA-01116: error in opening database file 15
ORA-01110: data file 15: '/SERVER/ora9/oradata/ora9
'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
試著更名,可是不能OFFLINE也不能改名.
想到的辦法是:重建控制檔案了.
- 先SHUTDOWN ABORT;
- STARTUP MOUNT:
- Alter database backup controlfile to trace;
得到重建控制檔案指令碼;
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA9" RESETLOGS FORCE LOGGING ARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 '/SERVER/ora9/oradata/ora9/redo01.log' SIZE 20M,
GROUP 2 '/SERVER/ora9/oradata/ora9/redo02.log' SIZE 20M,
GROUP 3 '/SERVER/ora9/oradata/ora9/redo03.log' SIZE 20M
-- STANDBY LOGFILE
DATAFILE
'/SERVER/ora9/oradata/ora9/drsys01.dbf',
'/SERVER/ora9/oradata/ora9/indx01.dbf',
'/SERVER/ora9/oradata/ora9/odm01.dbf',
'/SERVER/ora9/oradata/ora9/tools01.dbf',
'/SERVER/ora9/oradata/ora9/users01.dbf',
'/SERVER/ora9/oradata/ora9/xdb01.dbf',
'/SERVER/ora9/oradata/ora9/t2007q1.dbf',
'/SERVER/ora9/oradata/ora9/t2007q2.dbf',
'/SERVER/ora9/oradata/ora9/t2007q3.dbf',
'/SERVER/ora9/oradata/ora9/t2007q4.dbf',
'/SERVER/ora9/oradata/ora9/undo.dbf',
'/SERVER/ora9/oradata/ora9',
'/SERVER/ora9/oradata/ora9/atu.dbf'
CHARACTER SET ZHS16GBK
並在指令碼中刪除錯誤的資料檔案: '/SERVER/ora9/oradata/ora9'
在重建控制檔案後,試著開啟資料庫:
11:37:28 SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/SERVER/ora9/oradata/ora9/system01.dbf'
//需要恢復
11:38:13 SQL> recover database using backup controlfile;
ORA-00279: change 10703011 generated at 12/19/2007 07:22:31 needed for thread
ORA-00289: suggestion : /SERVER/ora9/arc/1_108.dbf
ORA-00280: change 10703011 for thread 1 is in sequence #108
11:38:31 Specify log: {
/SERVER/ora9/oradata/ora9/redo02.log
ORA-00310: archived log contains sequence 107; sequence 108 required
ORA-00334: archived log: '/SERVER/ora9/oradata/ora9/redo02.log'
11:38:41 SQL> recover database using backup controlfile;
ORA-00279: change 10703011 generated at 12/19/2007 07:22:31 needed for thread
ORA-00289: suggestion : /SERVER/ora9/arc/1_108.dbf
ORA-00280: change 10703011 for thread 1 is in sequence #108
11:39:16 Specify log: {
/SERVER/ora9/oradata/ora9/redo03.log (使用的是當前日誌)
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 15: '/SERVER/ora9/oradata/ora9
'
ORA-01112: media recovery not started
11:42:07 SQL> recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 15 is unknown - rename to correct file
ORA-01110: data file 15: '/SERVER/ora9/product/9.2/dbs/UNNAMED00015'
ORA-01157: cannot identify/lock data file 15 - see DBWR trace file
ORA-01111: name for data file 15 is unknown - rename to correct file
ORA-01110: data file 15: '/SERVER/ora9/product/9.2/dbs/UNNAMED00015'
由於系統表空間中,在記錄這個資料檔案,而在控制檔案中沒有,所以系統報這個錯誤.
11:49:00 SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/SERVER/ora9/oradata/ora9/system01.dbf
/SERVER/ora9/oradata/ora9/hyf.dbf
/SERVER/ora9/oradata/ora9/cwmlite01.dbf
/SERVER/ora9/oradata/ora9/drsys01.dbf
/SERVER/ora9/oradata/ora9/indx01.dbf
/SERVER/ora9/oradata/ora9/odm01.dbf
/SERVER/ora9/oradata/ora9/tools01.dbf
/SERVER/ora9/oradata/ora9/users01.dbf
/SERVER/ora9/oradata/ora9/xdb01.dbf
/SERVER/ora9/oradata/ora9/t2007q1.dbf
/SERVER/ora9/oradata/ora9/t2007q2.dbf
NAME
--------------------------------------------------------------------------------
/SERVER/ora9/oradata/ora9/t2007q3.dbf
/SERVER/ora9/oradata/ora9/t2007q4.dbf
/SERVER/ora9/oradata/ora9/undo.dbf
/SERVER/ora9/product/9.2/dbs/UNNAMED00015
/SERVER/ora9/oradata/ora9/atu.dbf
16 rows selected.
11:49:07 SQL> alter database datafile '/SERVER/ora9/product/9.2/dbs/UNNAMED00015' offline;
Database altered.
11:53:43 SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/SERVER/ora9/oradata/ora9/system01.dbf'
11:54:34 SQL> recover database using backup controlfile;
ORA-00279: change 10712117 generated at 12/19/2007 10:12:24 needed for thread 1
ORA-00289: suggestion : /SERVER/ora9/arc/1_108.dbf
ORA-00280: change 10712117 for thread 1 is in sequence #108
11:54:45 Specify log: {
/SERVER/ora9/oradata/ora9/redo03.log (當前日誌)
Log applied.
Media recovery complete.
11:55:01 SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01245: offline file 15 will be lost if RESETLOGS is done
ORA-01111: name for data file 15 is unknown - rename to correct file
ORA-01110: data file 15: '/SERVER/ora9/product/9.2/dbs/UNNAMED00015'
11:55:09 SQL> alter database datafile '/SERVER/ora9/product/9.2/dbs/UNNAMED00015' offline;
Database altered.
11:55:32 SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01245: offline file 15 will be lost if RESETLOGS is done
ORA-01111: name for data file 15 is unknown - rename to correct file
ORA-01110: data file 15: '/SERVER/ora9/product/9.2/dbs/UNNAMED00015'
11:58:47 SQL> alter database datafile 15 offline drop;
Database altered.
12:01:55 SQL> alter database open resetlogs;
Database altered.
到此,資料庫正常開啟,恢復全部完成.
後緒工作:
1. 增加臨時檔案:
alter tablespace temp add tempfile
'/SERVER/ora9/oradata/ora9/temp01.dbf' size 200M reuse;
2 . 做一個RMAN全備.
後續思考的問題:
這個錯誤的資料檔案是怎麼被系統(LINUX,ORACLE)接受的??
當我在安全備份後,想重演一下錯誤的發生過程時:卻不成功. 13:50: SQL> create tablespace test1 datafile ' RVER/ora9/oradata/ora9' size 20M ;13:52:06 2 create tablespace test1 * ERROR at line 1: ORA-01119: error in creating database file ' RVER/ora9/oradata/ora9' ORA-27038: skgfrcre: file exists
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/703656/viewspace-995024/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- rman恢復控制檔案的一個小錯誤
- 資料檔案誤刪--但有資料檔案的copy恢復
- Oracle恢復誤刪除的資料檔案Oracle
- Oracle資料恢復 - Linux / Unix 誤刪除的檔案恢復(轉）Oracle資料恢復Linux
- 誤刪除資料檔案、控制檔案的非RMAN恢復方法
- 使用檔案描述符恢復誤刪除的資料檔案
- 【北亞伺服器資料恢復】LUN對映出錯導致檔案系統一致性錯誤的資料恢復案例伺服器資料恢復
- linux下恢復誤刪除的資料檔案Linux
- 【北亞資料恢復】zfs檔案系統的伺服器誤刪除的資料恢復資料恢復伺服器
- lsof恢復誤刪的檔案
- linux下恢復誤刪除oracle的資料檔案LinuxOracle
- 【資料庫資料恢復】Oracle資料庫檔案出現壞塊報錯的資料恢復案例資料庫資料恢復Oracle
- 資料庫資料恢復—MongoDB資料庫檔案丟失，啟動報錯的資料恢復案例資料庫資料恢復MongoDB
- 資料檔案丟失的恢復
- 僅僅丟失一個普通使用者資料檔案的恢復A(聯機恢復)
- 丟失一個控制檔案並恢復資料庫資料庫
- 【資料庫資料恢復】透過恢復NDF檔案修復資料庫的資料恢復過程資料庫資料恢復
- 資料庫資料恢復—附加資料庫錯誤823的SQL Server資料恢復案例資料庫資料恢復SQLServer
- linux中誤刪除oracle資料檔案的恢復操作LinuxOracle
- 一個恢復刪除檔案的工具
- 【分散式儲存資料恢復】hbase和hive資料庫底層檔案誤刪的資料恢復案例分散式資料恢復Hive資料庫
- 【北亞資料恢復】某公司網路共享檔案開啟報錯的資料恢復案例資料恢復
- 【資料庫資料恢復】磁碟空間不足導致sql server錯誤的資料恢復資料庫資料恢復SQLServer
- 恢復update,delete表資料錯誤的語句delete
- Oracle系統表空間剛新增的一個資料檔案誤刪除恢復處理Oracle
- 【資料庫資料恢復】MongoDB資料庫檔案損壞的資料恢復案例資料庫資料恢復MongoDB
- 【伺服器資料恢復】Ext4檔案系統執行fsck後檔案掛載報錯的資料恢復伺服器資料恢復
- Linux下誤刪資料檔案從檔案控制程式碼恢復資料檔案Linux
- 備份與恢復--重建控制檔案後資料檔案損壞的恢復
- 恢復案例：歸檔模式下丟失全部資料檔案的恢復模式
- 【虛擬機器資料恢復】誤刪除VMware虛擬機器vmdk檔案的資料恢復案例虛擬機資料恢復
- linux中誤刪除oracle資料檔案的恢復操作（轉）LinuxOracle
- Oracle備份與恢復【丟失資料檔案的恢復】Oracle
- 利用歸檔來做資料檔案的恢復
- 如何有效恢復誤刪的HDFS檔案
- 被誤刪的檔案快速恢復方法
- 使用lsof恢復誤刪除的檔案
- git恢復誤刪未提交的檔案Git