ORA-20000故障排除手记
1、发现故障。
在Oracle10.2.0.1的alert.log文件中发现如下错误。
Wed Jul 8 22:00:08 2009
Errors in file /export/home/oracle/admin/ora10g/bdump/ora10g_j000_1472.trc:
ORA-12012: error on auto execute of job 8888
ORA-20000: ORA-20000: Content of the tablespace specified is not permanent or tablespace name is invalid
ORA-06512: at "SYS.PRVT_ADVISOR", line 1624
ORA-06512: at "SYS.DBMS_ADVISOR", line 186
ORA-06512: at "SYS.DBMS_SPACE", line 1338
ORA-06512: at "SYS.DBMS_SPACE", line 1554
仔细检查,几乎每天晚上10点都报这个错。显然是某个job出错了。
2、查看trc文件,发现如下错
*** ACTION NAME:(AUTO_SPACE_ADVISOR_JOB) 2009-07-07 22:00:08.968
*** MODULE NAME:(DBMS_SCHEDULER) 2009-07-07 22:00:08.968
*** SERVICE NAME:(SYS$USERS) 2009-07-07 22:00:08.968
*** SESSION ID:(1005.30306) 2009-07-07 22:00:08.968
是Oracle AUTO_SPACE_ADVISOR_JOB任务。
3、再现并验证错误
SQL> exec dbms_space.auto_space_advisor_job_proc;
BEGIN dbms_space.auto_space_advisor_job_proc; END;
*
ERROR at line 1:
ORA-20000: Content of the tablespace specified is not permanent or tablespace
name is invalid
ORA-06512: at "SYS.PRVT_ADVISOR", line 1624
ORA-06512: at "SYS.DBMS_ADVISOR", line 186
ORA-06512: at "SYS.DBMS_SPACE", line 1338
ORA-06512: at "SYS.DBMS_SPACE", line 1554
ORA-06512: at line 1
果然出现同样的错误。
4、找出问题表空间名:参见( otn的一篇帖子 )
SQL> select distinct tablespace_name from DBA_AUTO_SEGADV_CTL;
TABLESPACE_NAME
------------------------------
TBS_DNINMSV30
...
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
...
TBS_DNINMSV3
果然,存在不一致情况(这是Oracle的一个Bug所致,Oracle10.2.0.1没有自动的更新字典表)。
继续
SQL> select count(*) from DBA_AUTO_SEGADV_CTL where tablespace_name = ' TBS_DNINMSV3 ';
COUNT(*)
1
SQL> select segment_owner, segment_name, status from DBA_AUTO_SEGADV_CTL where tablespace_name=' TBS_DNINMSV3 ';
SEGMENT_OWNER SEGMENT_NAME STATUS
BEING_PROCESSED
5、三步搞定,解决问题 ( 参考文档)
创建DBA_AUTO_SEGADV_CTL中存在,但实际却不存在的表空间(100K就够了,目的也就是借用它的“名字”):
create tablespace TBS_DNINMSV30 datafile '/export/home/oracle/tmp/dninsmv30temp.dbf' size 100k;
运行
exec dbms_space.auto_space_advisor_job_proc; ----果然不报错了
删除表空间
drop tablespace TBS_DNINMSV30
6、再次验证
运行
exec dbms_space.auto_space_advisor_job_proc; ----依然不报错,说明问题解决。
另注:
直接删除DBA_AUTO_SEGADV_CTL中的条目是是不可以的。
delete DBA_AUTO_SEGADV_CTL where tablespace_name='TBS_DNINMSV30';
一旦运行exec dbms_space.auto_space_advisor_job_proc; TBS_DNINMSV30又会出现
Link URL: http://blog.sina.com.cn/s/blog_592060b50100e2l2.html
1、发现故障。
在Oracle10.2.0.1的alert.log文件中发现如下错误。
Wed Jul 8 22:00:08 2009
Errors in file /export/home/oracle/admin/ora10g/bdump/ora10g_j000_1472.trc:
ORA-12012: error on auto execute of job 8888
ORA-20000: ORA-20000: Content of the tablespace specified is not permanent or tablespace name is invalid
ORA-06512: at "SYS.PRVT_ADVISOR", line 1624
ORA-06512: at "SYS.DBMS_ADVISOR", line 186
ORA-06512: at "SYS.DBMS_SPACE", line 1338
ORA-06512: at "SYS.DBMS_SPACE", line 1554
仔细检查,几乎每天晚上10点都报这个错。显然是某个job出错了。
2、查看trc文件,发现如下错
*** ACTION NAME:(AUTO_SPACE_ADVISOR_JOB) 2009-07-07 22:00:08.968
*** MODULE NAME:(DBMS_SCHEDULER) 2009-07-07 22:00:08.968
*** SERVICE NAME:(SYS$USERS) 2009-07-07 22:00:08.968
*** SESSION ID:(1005.30306) 2009-07-07 22:00:08.968
是Oracle AUTO_SPACE_ADVISOR_JOB任务。
3、再现并验证错误
SQL> exec dbms_space.auto_space_advisor_job_proc;
BEGIN dbms_space.auto_space_advisor_job_proc; END;
*
ERROR at line 1:
ORA-20000: Content of the tablespace specified is not permanent or tablespace
name is invalid
ORA-06512: at "SYS.PRVT_ADVISOR", line 1624
ORA-06512: at "SYS.DBMS_ADVISOR", line 186
ORA-06512: at "SYS.DBMS_SPACE", line 1338
ORA-06512: at "SYS.DBMS_SPACE", line 1554
ORA-06512: at line 1
果然出现同样的错误。
4、找出问题表空间名:参见( otn的一篇帖子 )
SQL> select distinct tablespace_name from DBA_AUTO_SEGADV_CTL;
TABLESPACE_NAME
------------------------------
TBS_DNINMSV30
...
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
...
TBS_DNINMSV3
果然,存在不一致情况(这是Oracle的一个Bug所致,Oracle10.2.0.1没有自动的更新字典表)。
继续
SQL> select count(*) from DBA_AUTO_SEGADV_CTL where tablespace_name = ' TBS_DNINMSV3 ';
COUNT(*)
1
SQL> select segment_owner, segment_name, status from DBA_AUTO_SEGADV_CTL where tablespace_name=' TBS_DNINMSV3 ';
SEGMENT_OWNER SEGMENT_NAME STATUS
BEING_PROCESSED
5、三步搞定,解决问题 ( 参考文档)
创建DBA_AUTO_SEGADV_CTL中存在,但实际却不存在的表空间(100K就够了,目的也就是借用它的“名字”):
create tablespace TBS_DNINMSV30 datafile '/export/home/oracle/tmp/dninsmv30temp.dbf' size 100k;
运行
exec dbms_space.auto_space_advisor_job_proc; ----果然不报错了
删除表空间
drop tablespace TBS_DNINMSV30
6、再次验证
运行
exec dbms_space.auto_space_advisor_job_proc; ----依然不报错,说明问题解决。
另注:
直接删除DBA_AUTO_SEGADV_CTL中的条目是是不可以的。
delete DBA_AUTO_SEGADV_CTL where tablespace_name='TBS_DNINMSV30';
一旦运行exec dbms_space.auto_space_advisor_job_proc; TBS_DNINMSV30又会出现
Link URL: http://blog.sina.com.cn/s/blog_592060b50100e2l2.html
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/91771/viewspace-609550/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/91771/viewspace-609550/