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（需要補丁）

參考文件：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

 

方法三、打補丁