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>