oracle8i誤刪除臨時表空間後的恢復
事故原因如下:
今天早上檢視錶空間利用率時發現一oracle8i資料庫當機了操作完後過段時間導致資料庫當機。查詢警告日誌發現大量的關於資料檔案的臨時讀取錯誤。
原來一同事因磁碟空間達到95%，去清理臨時資料和log時，錯誤的執行了如下命令，清空了臨時資料檔案。
cat /dev/null > /opt/oracle/oradata/openview/OPC_TEMP_1.dbf
cat /dev/null > /opt/oracle/oradata/openview/OPC_TEMP_1.dbf
cat /dev/null > /opt/oracle/oradata/openview/OPC_TEMP_1.dbf
之後我在啟動資料庫的時候報錯如下：
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
LICENSE_MAX_USERS = 0
Starting up ORACLE RDBMS Version: 8.1.7.0.0.
System parameters with non-default values:
processes = 50
shared_pool_size = 120000000
control_files = /opt/oracle/oradata/openview/control01.ctl, /opt/oracle/oradata/openview/control02.ctl, /opt/oracle/oradata/openview/control03.ctl
db_block_buffers = 550
db_block_size = 8192
compatible = 8.1.7.0.0
log_archive_start = FALSE
log_archive_dest = /opt/oracle/admin/openview/arch
log_archive_format = T%TS%S.ARC
log_buffer = 65536
log_checkpoint_interval = 99999
db_files = 50
db_file_multiblock_read_count= 8
dml_locks = 100
rollback_segments = r01, r02, r03, r04
remote_os_authent = TRUE
db_name = openview
open_cursors = 500
os_authent_prefix =
background_dump_dest = /opt/oracle/admin/openview/bdump
user_dump_dest = /opt/oracle/admin/openview/udump
max_dump_file_size = 10240
core_dump_dest = /opt/oracle/admin/openview/cdump
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
Fri Jun 21 08:18:51 2013
ALTER DATABASE MOUNT
Fri Jun 21 08:18:55 2013
Successful mount of redo thread 1, with mount id 2788845359.
Fri Jun 21 08:18:55 2013
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE MOUNT
Fri Jun 21 08:18:55 2013
ALTER DATABASE OPEN
Fri Jun 21 08:18:55 2013
Errors in file /opt/oracle/admin/openview/udump/ora_10395_openview.trc:
ORA-01110: data file 5: '/opt/oracle/oradata/openview/OPC_TEMP_1.dbf'
ORA-01115: IO error reading block from file 5 (block # 1)
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
Additional information: 1
Additional information: 1
ORA-01110: data file 2: '/opt/oracle/oradata/openview/temp_1.dbf'
ORA-01115: IO error reading block from file 2 (block # 1)
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
Additional information: 1
Additional information: 1
Fri Jun 21 08:18:55 2013
Errors in file /opt/oracle/admin/openview/udump/ora_10395_openview.trc:
ORA-01110: data file 21: '/opt/oracle/oradata/openview/OPC_TEMP_2.dbf'
ORA-01115: IO error reading block from file 21 (block # 1)
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
Additional information: 1
Additional information: 1
ORA-01110: data file 2: '/opt/oracle/oradata/openview/temp_1.dbf'
ORA-01115: IO error reading block from file 2 (block # 1)
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
Additional information: 1
Additional information: 1
ORA-1110 signalled during: ALTER DATABASE OPEN...
Fri Jun 21 08:20:09 2013
重新執行：
alter database open
Fri Jun 21 08:20:31 2013
Errors in file /opt/oracle/admin/openview/udump/ora_10401_openview.trc:
ORA-01110: data file 5: '/opt/oracle/oradata/openview/OPC_TEMP_1.dbf'
ORA-01115: IO error reading block from file 5 (block # 1)
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
Additional information: 1
Additional information: 1
ORA-01110: data file 2: '/opt/oracle/oradata/openview/temp_1.dbf'
ORA-01115: IO error reading block from file 2 (block # 1)
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
Additional information: 1
Additional information: 1
Fri Jun 21 08:20:31 2013
Errors in file /opt/oracle/admin/openview/udump/ora_10401_openview.trc:
ORA-01110: data file 21: '/opt/oracle/oradata/openview/OPC_TEMP_2.dbf'
ORA-01115: IO error reading block from file 21 (block # 1)
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
Additional information: 1
Additional information: 1
ORA-01110: data file 2: '/opt/oracle/oradata/openview/temp_1.dbf'
ORA-01115: IO error reading block from file 2 (block # 1)
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
Additional information: 1
Additional information: 1
ORA-1110 signalled during: alter database open
...
select status,instance_name from v$instance;
發現資料庫在mounted狀態下，仍然無法正常啟動。
recover database;也不行。
最後確定清空的資料檔案及所在表空間後做如下操作：
alter database datafile '/opt/oracle/oradata/openview/temp_1.dbf' offline drop
Fri Jun 21 08:56:45 2013
Completed: alter database datafile '/opt/oracle/oradata/openv
Fri Jun 21 08:57:02 2013
alter database open
Fri Jun 21 08:57:02 2013
Errors in file /opt/oracle/admin/openview/udump/ora_19584_openview.trc:
ORA-01110: data file 21: '/opt/oracle/oradata/openview/OPC_TEMP_2.dbf'
ORA-01115: IO error reading block from file 21 (block # 1)
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
Additional information: 1
Additional information: 1
ORA-01110: data file 5: '/opt/oracle/oradata/openview/OPC_TEMP_1.dbf'
ORA-01115: IO error reading block from file 5 (block # 1)
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
Additional information: 1
Additional information: 1
Fri Jun 21 08:57:02 2013
ORA-1110 signalled during: alter database open
...
Fri Jun 21 08:57:50 2013
alter database datafile '/opt/oracle/oradata/openview/OPC_TEMP_1.dbf' offline drop
Fri Jun 21 08:57:50 2013
Completed: alter database datafile '/opt/oracle/oradata/openv
Fri Jun 21 08:58:00 2013
alter database open
ORA-1110 signalled during: alter database open
...
Fri Jun 21 08:58:24 2013
alter database datafile '/opt/oracle/oradata/openview/OPC_TEMP_2.dbf' offline drop
Fri Jun 21 08:58:24 2013
Completed: alter database datafile '/opt/oracle/oradata/openv
Fri Jun 21 08:58:33 2013
alter database open
Beginning crash recovery of 1 threads
Fri Jun 21 08:58:33 2013
Thread recovery: start rolling forward thread 1
Recovery of Online Redo Log: Thread 1 Group 1 Seq 93032 Reading mem 0
Mem# 0 errs 0: /opt/oracle/oradata/openview/redo01.log
Recovery of Online Redo Log: Thread 1 Group 2 Seq 93033 Reading mem 0
Mem# 0 errs 0: /opt/oracle/oradata/openview/redo02.log
Fri Jun 21 08:58:34 2013
Thread recovery: finish rolling forward thread 1
Thread recovery: 551 data blocks read, 249 data blocks written, 1508 redo blocks read
Crash recovery completed successfully
Fri Jun 21 08:58:34 2013
Thread 1 advanced to log sequence 93034
Thread 1 opened at log sequence 93034
Current log# 3 seq# 93034 mem# 0: /opt/oracle/oradata/openview/redo03.log
Successful open of redo thread 1.
Fri Jun 21 08:58:34 2013
SMON: enabling cache recovery
SMON: enabling tx recovery
Fri Jun 21 08:58:35 2013
Completed: alter database open
最終資料庫開啟ok；接著開始重建臨時資料檔案及表空：
alter tablespace opc_temp add tempfile '/opt/oracle/oradata/openview/OPC_TEMP_3.dbf' SIZE 100M
Fri Jun 21 09:13:42 2013
ORA-3217 signalled during: alter tablespace opc_temp add tempfile '/opt/oracl...
Fri Jun 21 09:15:16 2013
DROP TABLESPACE TEMP
Fri Jun 21 09:15:16 2013
Completed: DROP TABLESPACE TEMP
Fri Jun 21 09:26:12 2013
create temporary tablespace temp tempfile '/opt/oracle/oradata/openview/temp_1.dbf[D[D[D[2[C[C[C[C' size 100M
Fri Jun 21 09:26:40 2013
Completed: create temporary tablespace temp tempfile '/opt/or
alter tablespace opc_temp add tempfile '/opt/oracle/oradata/openview/OPC_TEMP_1.dbf' size 200M
Fri Jun 21 09:32:46 2013
ORA-3217 signalled during: alter tablespace opc_temp add tempfile '/opt/oracl...
drop tablespace opc_temp
Fri Jun 21 09:38:57 2013
Completed: drop tablespace opc_temp
Fri Jun 21 09:41:21 2013
create temporary tablespace opc_temp tempfile '/opt/oracle/oradata/openview/OPC_TEMP_1.dbf' size 200M
Fri Jun 21 09:41:21 2013
Completed: create temporary tablespace opc_temp tempfile '/op
總結測試：此問題後在oracle10g，11g中測試如上操作清空臨時資料檔案後，然而並不會導致資料庫當機問題。只需要重建臨時表空間及資料檔案即可。此檔案在oracle管理機制中不同於其他資料檔案的管理。
臨時資料檔案簡介：
本地管理的（locally managed）臨時表空間（temporary tablespace）使用臨時的資料檔案（datafile）(臨時檔案)，這樣的檔案與普通資料檔案類似，用於磁碟上的分類和臨時的儲存。
但有以下區別：
● 臨時檔案總是被設定為 NOLOGGING 模式。
● 使用者不能將臨時檔案設為之讀
● 使用者不能使用 ALTER DATABASE 語句建立臨時檔案
● 介質恢復（media recovery）不能識別臨時檔案：
● BACKUP CONTROLFILE 不會產生與臨時檔案有關的資訊
● CREATE CONTROLFILE 不能設定與臨時檔案有關的資訊
● 當使用者建立臨時檔案或改變其容量時，Oracle並不保證按照使用者指定的檔案容量為其分配磁碟空間。在某些檔案系統（file systems）中（例如UNIX）磁碟塊（disk block）並不會在檔案建立或改變容量時分配，而是在其被使用之前 才分配。
值得注意的是：
臨時檔案（tempfile）建立與改變容量操作執行的更快。但是隨著臨時檔案的使用可能會導致磁碟空間不足。
● 臨時檔案（tempfile）資訊可以從 DBA_TEMP_FILES 資料字典表及 V$TEMPFILE 動態效能檢視（dynamic performance view）中查詢，但是不存在於 DBA_DATA_FILES 或 V$DATAFILE 檢視中。
參考資料：
擴充套件內容，臨時資料檔案及表空間的修改要用如下語句：
alter database tempfile 'file_path/file_name' resize;即可同時也可透過alter tablespace temp_tbs add tempfile 新增資料檔案。
undo表空間及資料檔案的修改：
undo 資料檔案可直接alter database datafile修改，同時undo表空間也可透過alter tablespace undo_tbs add datafie 新增資料檔案。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28612416/viewspace-764491/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 刪除臨時表空間組
- oracle建立臨時表空間和資料表空間以及刪除Oracle
- 【北亞資料恢復】誤刪除oracle表和誤刪除oracle表資料的資料恢復方法資料恢復Oracle
- 刪除表空間時，遇到了ORA-14404錯誤
- oracle 臨時表空間的增刪改查Oracle
- hbase 恢復 誤刪除
- MySQL資料庫表誤刪除恢復(一)MySql資料庫
- Tablespace表空間刪除
- Oracle臨時表空間檢視、新增臨時表空間資料檔案、修改預設臨時表空間 方法！Oracle
- mysql臨時表，臨時表空間，ibtmp1表空間暴增原因初探MySql
- 恢復誤刪除表黑科技之relay log大法（續）
- Oracle RMAN 表空間恢復Oracle
- 刪除表空間出現ORA-22868錯誤（一）
- 表空間TSPITR恢復-實驗
- 【Oracle 恢復表空間】 實驗Oracle
- MySQL 5.7的表刪除資料後的磁碟空間釋放MySql
- Mongodb資料庫誤刪後的恢復MongoDB資料庫
- oracle臨時表空間相關Oracle
- 4.2.1.8規劃臨時表空間
- MySQL InnoDB臨時表空間配置MySql
- oracle級聯刪除使用者，刪除表空間Oracle
- mysql 誤刪除表內資料，透過binlog日誌恢復MySql
- 消除臨時表空間暴漲的方法
- 【NetApp資料恢復案例】針對NetApp誤刪除資料的恢復APP資料恢復
- 2.5.7 建立預設臨時表空間
- 誤刪除資料了怎麼辦？小編交易誤刪除資料的恢復方法
- 14、MySQL Case-線上表誤刪除恢復MySql
- MySQL 5.7 新特性 共享臨時表空間及臨時表改進MySql
- 臨時表空間和回滾表空間使用率查詢
- 【儲存資料恢復案例】Netapp誤操作刪除lun的資料恢復資料恢復APP
- 【儲存資料恢復】NetApp儲存誤刪除的資料恢復案例資料恢復APP
- 【Oracle】表空間誤刪除導致startup啟動時提示ORA-01110和ORA-01157錯誤Oracle
- linux下恢復誤刪除oracle的資料檔案LinuxOracle
- oracle11g 查詢臨時表空間的使用率和正在使用臨時表空間的使用者Oracle
- SYSTEM 表空間管理及備份恢復
- QQ恢復解散後的群聊或刪除後的好友的方法
- (Les16 執行資料庫恢復)-表空間恢復資料庫
- 【伺服器資料恢復】LINUX誤刪除、格式化的資料恢復伺服器資料恢復Linux
- 【伺服器資料恢復】NetApp儲存誤刪除的資料恢復案例伺服器資料恢復APP