ORA-01122,ORA-01110:ORA-01200錯誤處理

http://hi.baidu.com/dbconsole/blog/item/1f46d189832220bb0e244463.html

Sql代碼

1. SQL> startup   
2. ORACLE 例程已經啟動。   
3. Total System Global Area  588324464 bytes   
4. Fixed Size                   454256 bytes   
5. Variable Size             293601280 bytes   
6. Database Buffers          293601280 bytes   
7. Redo Buffers                 667648 bytes   
8. 資料庫裝載完畢。   
9. ORA-01122: 資料庫檔案 2 驗證失敗   
10. ORA-01110: 資料檔案 2: 'D:/ORACLE/ORADATA/JSYADC/UNDOTBS01.DBF'  
11. ORA-01200: 43520的實際檔案大小小于46720塊的正确大小  

SQL> startup
ORACLE 例程已經啟動。
Total System Global Area  588324464 bytes
Fixed Size                   454256 bytes
Variable Size             293601280 bytes
Database Buffers          293601280 bytes
Redo Buffers                 667648 bytes
資料庫裝載完畢。
ORA-01122: 資料庫檔案 2 驗證失敗
ORA-01110: 資料檔案 2: 'D:/ORACLE/ORADATA/JSYADC/UNDOTBS01.DBF'
ORA-01200: 43520的實際檔案大小小于46720塊的正确大小
      

主要是由于非正常關機或者磁盤損壞導緻undotbs01檔案損壞。

修改pfile中undo的相關資訊

*.undo_management='MANUAL'

*.undo_tablespace='SYSTEM'

以pfile啟動到mount狀态

Sql代碼

1. SQL> startup mount pfile='/oracle/p1.ora'  
2. ORACLE instance started.   
3. Total System Global Area 303531576 bytes   
4. Fixed Size                   742968 bytes   
5. Variable Size             285212672 bytes   
6. Database Buffers           16777216 bytes   
7. Redo Buffers                 798720 bytes   
8. Database mounted.   
9. SQL>  

SQL> startup mount pfile='/oracle/p1.ora'
ORACLE instance started.
Total System Global Area 303531576 bytes
Fixed Size                   742968 bytes
Variable Size             285212672 bytes
Database Buffers           16777216 bytes
Redo Buffers                 798720 bytes
Database mounted.
SQL>
      

檢視rollback segments資訊。

Sql代碼

1. SQL> select segment_name,tablespace_name,status from dba_rollback_segs;   
2. SEGMENT_NAME                   TABLESPACE_NAME                STATUS   
3. ------------------------------ ------------------------------ ----------------   
4. SYSTEM                         SYSTEM                         ONLINE   
5. _SYSSMU1$                      UNDOTBS0                       OFFLINE   
6. _SYSSMU2$                      UNDOTBS0                       OFFLINE   
7. _SYSSMU3$                      UNDOTBS0                       OFFLINE   
8. _SYSSMU4$                      UNDOTBS0                       OFFLINE   
9. _SYSSMU5$                      UNDOTBS0                       OFFLINE   
10. _SYSSMU6$                      UNDOTBS0                       OFFLINE   
11. _SYSSMU7$                      UNDOTBS0                       OFFLINE   
12. _SYSSMU8$                      UNDOTBS0                       OFFLINE   
13. _SYSSMU9$                      UNDOTBS0                       OFFLINE   
14. _SYSSMU10$                     UNDOTBS0                       OFFLINE   
15. SEGMENT_NAME                   TABLESPACE_NAME                STATUS   
16. ------------------------------ ------------------------------ ----------------   
17. _SYSSMU11$                     UNDOTBS0                       NEEDS RECOVERY   
18. _SYSSMU12$                     UNDOTBS0                       NEEDS RECOVERY  

SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
SEGMENT_NAME                   TABLESPACE_NAME                STATUS

------------------------------ ------------------------------ ----------------
SYSTEM                         SYSTEM                         ONLINE
_SYSSMU1$                      UNDOTBS0                       OFFLINE
_SYSSMU2$                      UNDOTBS0                       OFFLINE
_SYSSMU3$                      UNDOTBS0                       OFFLINE
_SYSSMU4$                      UNDOTBS0                       OFFLINE
_SYSSMU5$                      UNDOTBS0                       OFFLINE
_SYSSMU6$                      UNDOTBS0                       OFFLINE
_SYSSMU7$                      UNDOTBS0                       OFFLINE
_SYSSMU8$                      UNDOTBS0                       OFFLINE
_SYSSMU9$                      UNDOTBS0                       OFFLINE
_SYSSMU10$                     UNDOTBS0                       OFFLINE
SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
_SYSSMU11$                     UNDOTBS0                       NEEDS RECOVERY
_SYSSMU12$                     UNDOTBS0                       NEEDS RECOVERY
      

從這裡面可以看到，_SYSSMU11$,_SYSSMU12$這兩個資料段need recovery，我們需要通過隐患參數_corrupted_rollback_segments,丢棄這兩個段。

在之前的pfile中，增減下列資訊

_corrupted_rollback_segments=(_SYSSMU11$,_SYSSMU12$)

Sql代碼

1. SQL> startup restrict pfile='/oracle/p1.ora';   
2. ORACLE instance started.   
3. Total System Global Area 303531576 bytes   
4. Fixed Size                   742968 bytes   
5. Variable Size             285212672 bytes   
6. Database Buffers           16777216 bytes   
7. Redo Buffers                 798720 bytes   
8. Database mounted.   
9. Database opened.   
10. #Drop掉舊的undo表空間   
11. SQL> drop tablespace undotbs0 including contents;   
12. Tablespace dropped.   
13. #建立新的undo表空間   
14. SQL> create undo tablespace undotbs1 datafile '/oracle/oradata/oratest/undotbs1.dbf' size 150M autoextend on;   
15. Tablespace created.   
16. SQL> shutdown immediate;   
17. Database closed.   
18. Database dismounted.   
19. ORACLE instance shut down.   
20. SQL>  

SQL> startup restrict pfile='/oracle/p1.ora';
ORACLE instance started.
Total System Global Area 303531576 bytes
Fixed Size                   742968 bytes
Variable Size             285212672 bytes
Database Buffers           16777216 bytes
Redo Buffers                 798720 bytes
Database mounted.
Database opened.
#Drop掉舊的undo表空間
SQL> drop tablespace undotbs0 including contents;
Tablespace dropped.
#建立新的undo表空間
SQL> create undo tablespace undotbs1 datafile '/oracle/oradata/oratest/undotbs1.dbf' size 150M autoextend on;
Tablespace created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
      

修改pfile

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

并删除_corrupted_rollback_segments=(_SYSSMU11$,_SYSSMU12$)

Sql代碼

1. SQL> startup pfile='/oracle/p1.ora'  
2. ORACLE instance started.   
3. Total System Global Area 303531576 bytes   
4. Fixed Size                   742968 bytes   
5. Variable Size            285212672 bytes   
6. Database Buffers           16777216 bytes   
7. Redo Buffers                 798720 bytes   
8. Database mounted.   
9. Database opened.   
10. SQL> create spfile from pfile='/oracle/p1.ora';   
11. File created.   
12. SQL>