[TD-ORACLE] 12cR2 설치시 후 운영시 ORA-06512 에러 발생

alert 로그에 다음과 같은 에러가 발생된다. 

12시r2 설치시 패키지가 정상적으로 설치가 되지 않아 발생되며 버그라고 한다. 

해서 패키지만 다시 생성해주면 해당 에러는 발생되지 않는다. 

Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_j000_10213.trc:

ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_383"

ORA-20001: Statistics Advisor: Invalid task name for the current user

ORA-06512: at "SYS.DBMS_STATS", line 47207

ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882

ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059

ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201

ORA-06512: at "SYS.DBMS_STATS", line 47197

Trace file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_j000_10379.trc

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Build label:    RDBMS_12.2.0.1.0_LINUX.X64_170125

ORACLE_HOME:    /u01/app/oracle/product/12.2.0/db_1

System name:    Linux

Node name:      yoridb2

Release:        3.10.0-514.26.2.el7.x86_64

Version:        #1 SMP Tue Jul 4 15:04:05 UTC 2017

Machine:        x86_64

Instance name: xifenfei

Redo thread mounted by this instance: 1

Oracle process number: 148

Unix process pid: 10379, image: oracle@yoridb2 (J000)

 

 

*** 2017-08-04T14:41:26.486692+08:00

*** SESSION ID:(508.52539) 2017-08-04T14:41:26.486716+08:00

*** CLIENT ID:() 2017-08-04T14:41:26.486722+08:00

*** SERVICE NAME:(SYS$USERS) 2017-08-04T14:41:26.486727+08:00

*** MODULE NAME:(DBMS_SCHEDULER) 2017-08-04T14:41:26.486732+08:00

*** ACTION NAME:(ORA$AT_OS_OPT_SY_384) 2017-08-04T14:41:26.486738+08:00

*** CLIENT DRIVER:() 2017-08-04T14:41:26.486743+08:00

 

ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_384"

ORA-20001: Statistics Advisor: Invalid task name for the current user

ORA-06512: at "SYS.DBMS_STATS", line 47207

ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882

ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059

ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201

ORA-06512: at "SYS.DBMS_STATS", line 47197

[oracle@yoridb2 trace]$ ss

 

SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 4 23:40:33 2017

 

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

 

1) 오토 테스트가 정상적으로 생성 되었는지 확인  

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

 

SQL> select name, ctime, how_created

  2    from sys.wri$_adv_tasks

  3   where owner_name = 'SYS'

   and name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');  4 

 

no rows selected

2) 패키지 생성하기

 

SQL> EXEC dbms_stats.init_package();

 

PL/SQL procedure successfully completed.

3) 다시 조회시에는 생성되었음을 확인 할 수 있다.

 

SQL> select name, ctime, how_created

  2    from sys.wri$_adv_tasks

  3   where owner_name = 'SYS'

   and name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');  4 

 

NAME

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

CTIME     HOW_CREATED

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

AUTO_STATS_ADVISOR_TASK

04-AUG-17 CMD

 

INDIVIDUAL_STATS_ADVISOR_TASK

04-AUG-17 CMD