新建的 12.2.0.1 資料庫 alert 出現 ORA-12012

新建的 12.2.0.1 資料庫, 在 alert log 裡持續的出現 ORA-12012 , 如下所示

2018-06-04T01:30:45.124533+08:00
Errors in file /oracle/app/diag/rdbms/hisdb/HISDB1/trace/HISDB1_j000_24469.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_14438"
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

2018-06-04T01:40:45.589708+08:00
Errors in file /oracle/app/diag/rdbms/hisdb/HISDB1/trace/HISDB1_j000_2328.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_14440"
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

Metalink doc


ORA-12012 Error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_<NN> in 12.2 Database (Doc ID 2127675.1)

$ sqlplus / as sysdba
 EXEC dbms_stats.init_package();

column name format A35
set linesize 120
select name, ctime, how_created
  from sys.wri$_adv_tasks
 where owner_name = 'SYS'
   and name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');

Output of the query will look like this:
NAME                                 CTIME      HOW_CREATED
----------------------------------- ---------- ------------------------------
AUTO_STATS_ADVISOR_TASK              14-APR-16 CMD
INDIVIDUAL_STATS_ADVISOR_TASK        14-APR-16 CMD


Workaround

執行後執行上述的sql後, 應已生效


 NAME                                CTIME               HOW_CREATED
----------------------------------- ------------------- ------------------------------
AUTO_STATS_ADVISOR_TASK             2018-06-04 09:39:34 CMD
INDIVIDUAL_STATS_ADVISOR_TASK       2018-06-04 09:39:34 CMD



另外再查詢 alert log, 也沒再出現


$ awk '
/ORA-12012/ {
        for (i = (b + 1) % 3; i != b; i = (i + 1) % 3) {
                print before[i]
        }
        print (NR "-" ($0))
        a = 1
}

 a-- > 0 {
        print (" ")
}

 {
        before[b] = (NR ":" ($0))
        b = (b + 1) % 3
}' $ALERT  

169816:2018-06-04T00:40:36.705097+08:00
169817:Errors in file /oracle/app/diag/rdbms/hisdb/HISDB1/trace/HISDB1_j000_15352.trc:
169818-ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_14386"

169825:2018-06-04T00:50:35.956416+08:00
169826:Errors in file /oracle/app/diag/rdbms/hisdb/HISDB1/trace/HISDB1_j000_23110.trc:
169827-ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_14388"

169885:2018-06-04T01:00:41.287208+08:00
169886:Errors in file /oracle/app/diag/rdbms/hisdb/HISDB1/trace/HISDB1_j000_781.trc:
169887-ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_14398"

169909:2018-06-04T01:10:47.438371+08:00
169910:Errors in file /oracle/app/diag/rdbms/hisdb/HISDB1/trace/HISDB1_j000_9096.trc:
169911-ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_14400"

169921:2018-06-04T01:20:48.933576+08:00
169922:Errors in file /oracle/app/diag/rdbms/hisdb/HISDB1/trace/HISDB1_j000_16910.trc:
169923-ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_14402"

169930:2018-06-04T01:30:45.124533+08:00
169931:Errors in file /oracle/app/diag/rdbms/hisdb/HISDB1/trace/HISDB1_j000_24469.trc:
169932-ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_14438"

169950:2018-06-04T01:40:45.589708+08:00
169951:Errors in file /oracle/app/diag/rdbms/hisdb/HISDB1/trace/HISDB1_j000_2328.trc:
169952-ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_14440"




留言

張貼留言

這個網誌中的熱門文章

12c RAC, OS log 出現 WARNING: couldn't allocate FBT table for module oracleacfs

環境 Solaris 11 SPARC (LDOM) Oracle 12.2.0.1 EE RAC  異常   OS log 出現  WARNING: couldn't allocate FBT table for module oracleacfs 細節 root@HISRAC1:~# dmesg |grep acfs Apr 27 01:08:03 HISRAC1 fbt: [ID 795213 kern.warning] WARNING: couldn't allocate FBT table for module oracleacfs Apr 27 02:08:03 HISRAC1 fbt: [ID 795213 kern.warning] WARNING: couldn't allocate FBT table for module oracleacfs Apr 27 03:08:03 HISRAC1 fbt: [ID 795213 kern.warning] WARNING: couldn't allocate FBT table for module oracleacfs Apr 27 04:08:03 HISRAC1 fbt: [ID 795213 kern.warning] WARNING: couldn't allocate FBT table for module oracleacfs Apr 27 05:08:03 HISRAC1 fbt: [ID 795213 kern.warning] WARNING: couldn't allocate FBT table for module oracleacfs Apr 27 06:08:03 HISRAC1 fbt: [ID 795213 kern.warning] WARNING: couldn't allocate FBT table for module oracleacfs Apr 27 07:08:03 HISRAC1 fbt: [ID 795213 kern.warning] WARNING: couldn't allocate FBT table for module oracl...
閱讀完整內容

11g client 連上 12c server, 出現 ora-28040

圖片
環境 Solaris 11 Oracle 12.2.0.1 EE 異常   開發人員使用 11g tools 連至 12c DB 時出現 ora-28040 細節 User 轉寄以下圖片 Workaround  這個很好查, 馬上就找到  12c: ORA-28040 After Upgrade: No Matching Authentication Protocol (Doc ID 1957995.1)  在 sqlnet.ora 加入以下字串 SQLNET.ALLOWED_LOGON_VERSION_SERVER=10 SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10 有幾點要注意 1. 將數值設成低於 11, 也不代表一定支援那個版本的 client  2. 在RAC環境下, 有時必需重開 cluster service 3. 在 12cR1 預設值是 11, 在 12cR2 預設值是 12 4. 在 12c 已廢棄   SQLNET.ALLOWED_LOGON_VERSION
閱讀完整內容

12cRAC, 災演後, cluster 服務無法啟動

環境 Solaris 11 SPARC (LDOM) Oracle 12.2.0.1 EE RAC  異常   OS 重開後, GI 無法啟動,   osysmond.bin 也沒有啟動 細節 EMRRAC1 root@EMRRAC1:~# /oracle/app/grid/bin/crsctl start cluster CRS-2672: Attempting to start 'ora.crf' on 'emrrac1' CRS-2672: Attempting to start 'ora.cssd' on 'emrrac1' CRS-2672: Attempting to start 'ora.diskmon' on 'emrrac1' CRS-2676: Start of 'ora.diskmon' on 'emrrac1' succeeded CRS-2676: Start of 'ora.crf' on 'emrrac1' succeeded CRS-2674: Start of 'ora.cssd' on 'emrrac1' failed CRS-2679: Attempting to clean 'ora.cssd' on 'emrrac1' CRS-2681: Clean of 'ora.cssd' on 'emrrac1' succeeded CRS-2673: Attempting to stop 'ora.crf' on 'emrrac1' CRS-2677: Stop of 'ora.crf' on 'emrrac1' succeeded CRS-4000: Command Start failed, or completed with errors . EMRRAC2 root@EMRRAC2:~# /oracle/app/grid/bin/crsctl start ...
閱讀完整內容