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

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

Thu Jul  4 14:05:09 2013
Errors in file /u01/app/oracle/admin/ora10g/bdump/ora10g_smon_3319.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u01/oracle/oradata/ora10g/undotbs01.dbf'
Opening with internal Resource Manager plan
where NUMA PG = 1, CPUs = 2
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC 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           UNDOTBS02

SEGMENT_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          UNDOTBS01

SEGMENT_NAME                   STATUS           TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
_SYSSMU15$                     OFFLINE          UNDOTBS01
_SYSSMU16$                     OFFLINE          UNDOTBS01
_SYSSMU17$                     OFFLINE          UNDOTBS01
_SYSSMU18$                     OFFLINE          UNDOTBS01
_SYSSMU19$                     OFFLINE          UNDOTBS01
_SYSSMU20$                     OFFLINE          UNDOTBS01

28 rows selected.

SQL> 

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

百度上贴一段。。。。。

回滚表空间
有三种种情况：
1、数据库已经完全关闭（使用shutdown immediate或shutdown命令）
1) 确认数据库完全关闭
2) 修改init.ora文件,注释"rollback-segment"
3) svrmgrl> startup restrict mount
4) 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 restrict
7) 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 mount
3) 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-4
6) 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           UNDOTBS1

SEGMENT_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          UNDOTBS01

SEGMENT_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          UNDOTBS01

31 rows selected.

SQL>