ORA-01157&ORA-01110故障解決

Hoegh發表於2015-07-08
        今天在啟動虛擬機器測試庫時，資料庫報了ORA-01157和ORA-01110錯誤，提示找不到一個資料檔案。我一看檔名就知道問題在哪兒了，是另一臺虛擬機器沒有啟動導致的，因為這個資料庫透過dnfs建立了一個“遠端”表空間。由於暫時不想啟動那臺虛擬機器（節省記憶體），乾脆就把這個表空間刪除，熟悉一下trouble-shooting的過程。

1.環境準備

我們在Oracle11g中進行測試。

點選(此處)摺疊或開啟

  1. SQL> 
  2. SQL> select * from v$version;

  4. BANNER
  5. --------------------------------------------------------------------------------
  6. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
  7. PL/SQL Release 11.2.0.3.0 - Production
  8. CORE 11.2.0.3.0 Production
  9. TNS for Linux: Version 11.2.0.3.0 - Production
  10. NLSRTL Version 11.2.0.3.0 - Production

  12. SQL>

2.啟動資料庫報錯

在啟動資料庫過程中，報了ORA-01157和ORA-01110錯誤，提示找不到資料檔案。

點選(此處)摺疊或開啟

  1. SYS@HOEGH>startup
  2. ORACLE instance started.

  4. Total System Global Area 941600768 bytes
  5. Fixed Size 1348860 bytes
  6. Variable Size 524290820 bytes
  7. Database Buffers 411041792 bytes
  8. Redo Buffers 4919296 bytes
  9. Database mounted.
  10. ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
  11. ORA-01110: data file 9: \'/u02/oradata/HOEGH/test_tbs01.dbf\'


  14. SYS@HOEGH>select status from v$instance;

  16. STATUS
  17. ------------
  18. MOUNTED

  20. SYS@HOEGH>
由於另外一臺虛擬機器沒有啟動，資料庫在啟動過程中後臺程式不能找到相應的資料檔案或者不能鎖定相應的資料檔案，資料庫將禁止訪問這些資料檔案而其他的資料檔案則沒有影響。ORA-01157錯誤一般和ORA-01110錯誤一起出現。

3.重啟資料庫到mount狀態，刪除資料檔案

啟動資料庫時，nomount狀態讀取引數檔案，mount狀態讀取控制檔案，在mount狀態下可以刪除資料檔案。

點選(此處)摺疊或開啟

  1. SYS@HOEGH>
  2. SYS@HOEGH>startup nomount
  3. ORACLE instance started.

  5. Total System Global Area 941600768 bytes
  6. Fixed Size 1348860 bytes
  7. Variable Size 524290820 bytes
  8. Database Buffers 411041792 bytes
  9. Redo Buffers 4919296 bytes
  10. SYS@HOEGH>
  11. SYS@HOEGH>
  12. SYS@HOEGH>alter database mount;

  14. Database altered.

  16. SYS@HOEGH>alter database datafile \'/u02/oradata/HOEGH/test_tbs01.dbf\' offline drop;

  18. Database altered.

4.開啟資料庫，刪除表空間

啟動資料庫到open狀態，查詢資料檔案對應的表空間名稱；然後，刪除表空間。

點選(此處)摺疊或開啟

  1. SYS@HOEGH>alter database open;

  3. Database altered.

  5. SYS@HOEGH>
  6. SYS@HOEGH>select file_id,tablespace_name from dba_data_files;

  8.    FILE_ID TABLESPACE_NAME
  9. ---------- ------------------------------
  10.          4 USERS
  11.          3 UNDOTBS1
  12.          2 SYSAUX
  13.          1 SYSTEM
  14.          5 TEST1
  15.          6 TEST2
  16.          7 TEST3
  17.          8 TEST
  18.          9 TEST_TBS

  20. 9 rows selected.
  21. SYS@HOEGH>col file_name for a50
  22. SYS@HOEGH>col file_id for 99
  23. SYS@HOEGH>col tablespace_name for a10
  24. SYS@HOEGH>col status for a10
  25. SYS@HOEGH>col online_status for a20
  26. SYS@HOEGH>select file_name,file_id,TABLESPACE_NAME,STATUS,ONLINE_STATUS from dba_data_files;
  27. FILE_NAME FILE_ID TABLESPACE STATUS ONLINE_STATUS
  28. -------------------------------------------------- ------- ---------- ---------- --------------------
  29. /u01/app/oracle/oradata/HOEGH/users01.dbf 4 USERS AVAILABLE ONLINE
  30. /u01/app/oracle/oradata/HOEGH/undotbs01.dbf 3 UNDOTBS1 AVAILABLE ONLINE
  31. /u01/app/oracle/oradata/HOEGH/sysaux01.dbf 2 SYSAUX AVAILABLE ONLINE
  32. /u01/app/oracle/oradata/HOEGH/system01.dbf 1 SYSTEM AVAILABLE SYSTEM
  33. /u01/app/oracle/oradata/HOEGH/TEST101.dbf 5 TEST1 AVAILABLE ONLINE
  34. /u01/app/oracle/oradata/HOEGH/TEST201.dbf 6 TEST2 AVAILABLE ONLINE
  35. /u01/app/oracle/oradata/HOEGH/TEST301.dbf 7 TEST3 AVAILABLE ONLINE
  36. /u01/app/oracle/oradata/HOEGH/test.dbf 8 TEST AVAILABLE ONLINE
  37. /u02/oradata/HOEGH/test_tbs01.dbf 9 TEST_TBS AVAILABLE RECOVER

  39. 9 rows selected.

  41. SYS@HOEGH>drop tablespace TEST_TBS including contents;

  43. Tablespace dropped.

  45. SYS@HOEGH>
  46. SYS@HOEGH>select file_name,file_id,TABLESPACE_NAME,STATUS,ONLINE_STATUS from dba_data_files;

  48. FILE_NAME FILE_ID TABLESPACE STATUS ONLINE_STATUS
  49. -------------------------------------------------- ------- ---------- ---------- --------------------
  50. /u01/app/oracle/oradata/HOEGH/users01.dbf 4 USERS AVAILABLE ONLINE
  51. /u01/app/oracle/oradata/HOEGH/undotbs01.dbf 3 UNDOTBS1 AVAILABLE ONLINE
  52. /u01/app/oracle/oradata/HOEGH/sysaux01.dbf 2 SYSAUX AVAILABLE ONLINE
  53. /u01/app/oracle/oradata/HOEGH/system01.dbf 1 SYSTEM AVAILABLE SYSTEM
  54. /u01/app/oracle/oradata/HOEGH/TEST101.dbf 5 TEST1 AVAILABLE ONLINE
  55. /u01/app/oracle/oradata/HOEGH/TEST201.dbf 6 TEST2 AVAILABLE ONLINE
  56. /u01/app/oracle/oradata/HOEGH/TEST301.dbf 7 TEST3 AVAILABLE ONLINE
  57. /u01/app/oracle/oradata/HOEGH/test.dbf 8 TEST AVAILABLE ONLINE

  59. 8 rows selected.

  61. SYS@HOEGH>

5.重啟資料庫

在進行上述操作後，重啟資料庫，確保資料庫能夠正常開啟。

點選(此處)摺疊或開啟

  1. SYS@HOEGH>
  2. SYS@HOEGH>shut immediate
  3. Database closed.
  4. Database dismounted.
  5. ORACLE instance shut down.
  6. SYS@HOEGH>
  7. SYS@HOEGH>
  8. SYS@HOEGH>startup
  9. ORACLE instance started.

  11. Total System Global Area 941600768 bytes
  12. Fixed Size 1348860 bytes
  13. Variable Size 511707908 bytes
  14. Database Buffers 423624704 bytes
  15. Redo Buffers 4919296 bytes
  16. Database mounted.
  17. Database opened.
  18. SYS@HOEGH>


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30162081/viewspace-1726797/，如需轉載，請註明出處，否則將追究法律責任。

相關文章