ORA-00604： 和ORA-01548: 编译存储过程

刚才启动测试环境下虚拟机上的oracle，启动过程没有报错，在我编译存储过程的时候报错如下：

Thu Jul  4 14:05:09 2013Errors in file /u01/app/oracle/admin/ora10g/bdump/ora10g_smon_3319.trc:ORA-00604: error occurred at recursive SQL level 1ORA-00376: file 2 cannot be read at this timeORA-01110: data file 2: '/u01/oracle/oradata/ora10g/undotbs01.dbf'Opening with internal Resource Manager planwhere NUMA PG = 1, CPUs = 2replication_dependency_tracking turned off (no async multimaster replication found)Starting background process QMNCQMNC started with pid=16, OS id=3337

没有搞明白这是为什么？？

看到上面提示undo文件/u01/oracle/oradata/ora10g/undotbs01.dbf，就查看了一下发现这个文件不存在。

SQL> select segment_name,status,tablespace_name from dba_rollback_segs;SEGMENT_NAME                   STATUS           TABLESPACE_NAME------------------------------ ---------------- ------------------------------SYSTEM                         ONLINE           SYSTEM_SYSSMU4$                      NEEDS RECOVERY   UNDOTBS1_SYSSMU5$                      NEEDS RECOVERY   UNDOTBS1_SYSSMU6$                      NEEDS RECOVERY   UNDOTBS1_SYSSMU7$                      NEEDS RECOVERY   UNDOTBS1_SYSSMU8$                      NEEDS RECOVERY   UNDOTBS1_SYSSMU9$                      NEEDS RECOVERY   UNDOTBS1_SYSSMU10$                     NEEDS RECOVERY   UNDOTBS1_SYSSMU22$                     ONLINE           UNDOTBS02_SYSSMU23$                     ONLINE           UNDOTBS02_SYSSMU24$                     ONLINE           UNDOTBS02SEGMENT_NAME                   STATUS           TABLESPACE_NAME------------------------------ ---------------- ------------------------------_SYSSMU25$                     ONLINE           UNDOTBS02_SYSSMU26$                     ONLINE           UNDOTBS02_SYSSMU27$                     ONLINE           UNDOTBS02_SYSSMU28$                     ONLINE           UNDOTBS02_SYSSMU29$                     ONLINE           UNDOTBS02_SYSSMU30$                     ONLINE           UNDOTBS02_SYSSMU21$                     ONLINE           UNDOTBS02_SYSSMU11$                     OFFLINE          UNDOTBS01_SYSSMU12$                     OFFLINE          UNDOTBS01_SYSSMU13$                     OFFLINE          UNDOTBS01_SYSSMU14$                     OFFLINE          UNDOTBS01SEGMENT_NAME                   STATUS           TABLESPACE_NAME------------------------------ ---------------- ------------------------------_SYSSMU15$                     OFFLINE          UNDOTBS01_SYSSMU16$                     OFFLINE          UNDOTBS01_SYSSMU17$                     OFFLINE          UNDOTBS01_SYSSMU18$                     OFFLINE          UNDOTBS01_SYSSMU19$                     OFFLINE          UNDOTBS01_SYSSMU20$                     OFFLINE          UNDOTBS0128 rows selected.SQL> 

发现正在用的是不是这一个UNDOTBS1，而是UNDOTBS02；但是UNDOTBS1却需要恢复。

百度上贴一段。。。。。

回滚表空间有三种种情况：1、数据库已经完全关闭（使用shutdown immediate或shutdown命令）1) 确认数据库完全关闭2) 修改init.ora文件,注释"rollback-segment"3) svrmgrl> startup restrict mount4) svrmgrl> alter database datafile filename offline drop;5) svrmgrl> alter database open;基于出现的结果："statement PRocessed" 转(7)；"ORA-00604,ORA-00376,ORA-01110"转(6)6) svrmgrl> shutdown immediate修改init.ora文件,增加如下一行：_corrupted_rollback_segments = (<roll1>,...<rolln>)svrmgrl> startup restrict7) svrmgrl> drop tablespace tablespace_name including contents;8) 重建表空间及回滚段9) svrmgrl> alter system disable restricted session;10) 修改init.ora文件2、数据库未完全关闭（数据库崩溃或使用shutdown abort命令关闭数据库）1) 恢复备份2) svrmgrl> startup mount3) svrmgrl> select file#,name,status from v$datafile;svrmgrl> alter database datafile filename online;4) svrmgrl> select v1.group#,member,sequence#,first_change# from v$log v1,v$logfile v2 where v1.group#=v2.group#;5) svrmgrl> select file#,change# from v$recover_file; #参见方案2-46) svrmgrl> recover datafile filename;7) svrmgrl> alter database open;3、数据库处于打开状态1) 删除回滚段和表空间2) 重建表空间和回滚段

因为启动后当前库没有报错，所以就创建了pfile，然后修改pfile，添加参数写上损坏的回滚段，然后用它启动，

_CORRUPTED_ROLLBACK_SEGMENTS=(_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)

，启动完之后，再删除UNDOTBS1，同时也再创建一下。完了之后，再根据pfile创建spfile，重启数据库。再次编译的时候没有报错，日志里面也没有。

drop tablespace UNDOTBS1 including contents and datafiles ;

create undo tablespace UNDOTBS1 datafile '/u01/oracle/oradata/ora10g/undotbs01.dbf' size 200m;

SQL>  select segment_name,status,tablespace_name from dba_rollback_segs;SEGMENT_NAME                   STATUS           TABLESPACE_NAME------------------------------ ---------------- ------------------------------SYSTEM                         ONLINE           SYSTEM_SYSSMU2$                      ONLINE           UNDOTBS1_SYSSMU3$                      ONLINE           UNDOTBS1_SYSSMU4$                      ONLINE           UNDOTBS1_SYSSMU5$                      ONLINE           UNDOTBS1_SYSSMU6$                      ONLINE           UNDOTBS1_SYSSMU7$                      ONLINE           UNDOTBS1_SYSSMU8$                      ONLINE           UNDOTBS1_SYSSMU9$                      ONLINE           UNDOTBS1_SYSSMU10$                     ONLINE           UNDOTBS1_SYSSMU1$                      ONLINE           UNDOTBS1SEGMENT_NAME                   STATUS           TABLESPACE_NAME------------------------------ ---------------- ------------------------------_SYSSMU22$                     OFFLINE          UNDOTBS02_SYSSMU23$                     OFFLINE          UNDOTBS02_SYSSMU24$                     OFFLINE          UNDOTBS02_SYSSMU25$                     OFFLINE          UNDOTBS02_SYSSMU26$                     OFFLINE          UNDOTBS02_SYSSMU27$                     OFFLINE          UNDOTBS02_SYSSMU28$                     OFFLINE          UNDOTBS02_SYSSMU29$                     OFFLINE          UNDOTBS02_SYSSMU30$                     OFFLINE          UNDOTBS02_SYSSMU21$                     OFFLINE          UNDOTBS02_SYSSMU11$                     OFFLINE          UNDOTBS01SEGMENT_NAME                   STATUS           TABLESPACE_NAME------------------------------ ---------------- ------------------------------_SYSSMU12$                     OFFLINE          UNDOTBS01_SYSSMU13$                     OFFLINE          UNDOTBS01_SYSSMU14$                     OFFLINE          UNDOTBS01_SYSSMU15$                     OFFLINE          UNDOTBS01_SYSSMU16$                     OFFLINE          UNDOTBS01_SYSSMU17$                     OFFLINE          UNDOTBS01_SYSSMU18$                     OFFLINE          UNDOTBS01_SYSSMU19$                     OFFLINE          UNDOTBS01_SYSSMU20$                     OFFLINE          UNDOTBS0131 rows selected.SQL>