ORA-12012、ORA-20000错误解决过程

ORA-12012、ORA-20000错误解决过程

[testdb]#tail alert_testdb.log

Thu Feb 13 22:00:01 2014

Errors in file /u01/app/oracle/admin/testdb/bdump/testdb_j000_24348.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

Thu Feb 13 23:20:26 2014

[testdb]#cat /u01/app/oracle/admin/testdb/bdump/testdb_j000_24348.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining Scoring Engine options

ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1

System name:    Linux

Node name:      testdb

Release:        2.6.9-67.ELsmp

Version:        #1 SMP Wed Nov 7 13:56:44 EST 2007

Machine:        x86_64

Instance name: testdb

Redo thread mounted by this instance: 1

Oracle process number: 69

Unix process pid: 18648, image: oracle@testdb (J000)

 

*** ACTION NAME:(AUTO_SPACE_ADVISOR_JOB) 2014-02-12 22:00:04.285

*** MODULE NAME:(DBMS_SCHEDULER) 2014-02-12 22:00:04.285

*** SERVICE NAME:(SYS$USERS) 2014-02-12 22:00:04.285

*** SESSION ID:(1031.63832) 2014-02-12 22:00:04.285

*** 2014-02-12 22:00:04.285

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

 

?  怎么找出job name

参考文档：How to Find the Job Name if a Scheduled Job Fails with ORA-12012 (文档 ID 744645.1)

首先，ORA-12012: error on auto execute of job 8888中的8888可理解为job id，那么首先想到dba_jobs中的job，例如：

SQL>select job, what from dba_jobs ;

JOB WHAT
---------- ----------------------------------------------------------
1 EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();
21 BEGIN MGMT_BSLN.COMPUTE_ALL_STATISTICS;/*DB*/END;
22 BEGIN MGMT_BSLN.SET_ALL_THRESHOLDS;/*DB*/END;

 

其次，如果dba_jobs中无相关信息，则要找scheduler相关信息；如，DBA_SCHEDULER_JOBS，但是该视图中无job id相关字段，不好找对应关系；DBMS_SCHEDULER执行的job id是保存在sys.scheduler$_job下的obj#列的；则，

SQL>select obj# , object_name from sys.scheduler$_job , dba_objects where obj# = object_id;

 

另外，还可以在指定的trace file中找到job_name;例如文件中的action name；

[10.6.1.7:testdb]#cat /u01/app/oracle/admin/testdb/bdump/testdb_j000_24348.trc

*** ACTION NAME:(AUTO_SPACE_ADVISOR_JOB) 2014-02-12 22:00:04.285

 

?  ORA-12012、ORA-20000本例原因

       由于原有某个表空间不存在，导致在AUTO_SPACE_ADVISOR_JOB执行时报错，其根本原因是Bug 4707226（需要补丁Patch 4707226）

参考文档：AUTO_SPACE_ADVISOR_JOB May Fail With ORA-20000 if Tablespace No Longer Exists (文档 ID 551190.1)

 

?  解决方法

       方法一.创建缺失的表空间，再执行job，然后再删除表空间

    SQL> CREATE TABLESPACE DATAFILE '' SIZE 100K;

    Run the space advisor:

    SQL> EXEC dbms_space.auto_space_advisor_job_proc;

    Drop the tablespace again:

    SQL> DROP TABLESPACE ;

要找到哪个表空间缺失，则需要对比dba_auto_segadv_ctl与dba_tablespaces视图；

TABLESPACE_NAME(dba_auto_segadv_ctl)  TABLESPACE_NAME(dba_tablespaces)

------------------------------  -                -----------------------------

SYSAUX                          SYSTEM                       

MON813_INDEXES                  UNDOTBS1                      

USERS                           SYSAUX                       

MON813_TABLES                   TEMP                         

MIDDLETBS                       USERS                        

MON105_TABLES                   MON813_TABLES                 

                                MON813_INDEXES               

                                MIDDLETBS                 

方法二、手动remove相关视图中相关记录；（未验证）

原文：Workaround

      Manually remove the tablespace candidate from DBA_AUTO_SEGADV_CTL.

SQL> DELETE FROM dba_auto_segadv_ctl where tablespace_name not in (select tablespace_name from dba_tablespaces);

/

SQL>desc dbms_metadata

FUNCTION GET_DDL RETURNS CLOB

 Argument Name                  Type                    In/Out Default?

 ------------------------------ ----------------------- ------ --------

 OBJECT_TYPE                    VARCHAR2                IN

 NAME                           VARCHAR2                IN

 SCHEMA                         VARCHAR2                IN     DEFAULT

 VERSION                        VARCHAR2                IN     DEFAULT

 MODEL                          VARCHAR2                IN     DEFAULT

 TRANSFORM                      VARCHAR2                IN     DEFAULT

SQL> set long 5000

SQL> SELECT dbms_metadata.get_ddl('VIEW','DBA_AUTO_SEGADV_CTL') A FROM DUAL;

A

--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "SYS"."DBA_AUTO_SEGADV_CTL" ("AUTO_TASKID", "TABLESPACE_NAME", "SEGMENT_OWNER", "SEGMENT_NAME", "SEGMENT_TYPE", "PARTITION_NAME", "STATUS", "REASON", "REASON_VALUE", "CREATION_TIME", "PROCESSED_TASKID", "END_TIME") AS

  select stats.auto_taskid, stats.tablespace_name, stats.segment_owner,

       stats.segment_name, stats.segment_type, stats.partition_name,

       stats.status, stats.reason, stats.reason_value,

       stats.creation_time, stats.proc_taskid, stats.end_time

from   wri$_segadv_objlist stats

 

方法三、打补丁Patch 4707226

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16976507/viewspace-1266115/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16976507/viewspace-1266115/