数据库从11.2.0.3.x升级到12.1.0.2.3之时出现如下错误,Oracle OLAP API组件无效
Oracle Database 12.1 Post-Upgrade Status Tool 04-21-2015 10:58:50
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
Oracle Server UPGRADED 12.1.0.2.0 00:36:20
JServer JAVA Virtual Machine VALID 12.1.0.2.0 00:08:19
Oracle Real Application Clusters VALID 12.1.0.2.0 00:00:02
Oracle Workspace Manager VALID 12.1.0.2.0 00:01:25
OLAP Analytic Workspace VALID 12.1.0.2.0 00:00:24
OLAP Catalog OPTION OFF 11.2.0.3.0 00:00:00
Oracle OLAP API
ORA-00600: internal error code, arguments: [qkaQknLTPruneKaf:1], [], [], []
Oracle OLAP API INVALID 12.1.0.2.0 00:01:37
Oracle XDK VALID 12.1.0.2.0 00:00:56
Oracle Text VALID 12.1.0.2.0 00:01:14
Oracle XML Database VALID 12.1.0.2.0 00:03:13
Oracle Database Java Packages VALID 12.1.0.2.0 00:00:18
Oracle Multimedia VALID 12.1.0.2.0 00:03:03
Spatial UPGRADED 12.1.0.2.0 00:06:41
Final Actions 00:10:10
Total Upgrade Time: 01:24:48
这里主要是OLAP API组件无效,OLAP Catalog组件官方已经有明确说明,12c里面已经不支持,可以升级之后把其卸载。分析alert日志，发现ora-600[qkaQknLTPruneKaf:1]错误
Tue Apr 21 10:15:55 2015
SERVER COMPONENT id=CATPROC: timestamp=2015-04-21 10:15:54
Tue Apr 21 10:16:06 2015
SERVER COMPONENT id=RDBMS: status=UPGRADED, version=12.1.0.2.0, timestamp=2015-04-21 10:16:06
Tue Apr 21 10:16:53 2015
Thread 1 advanced to log sequence 15 (LGWR switch)
Current log# 9 seq# 15 mem# 0: /oradata/redo/zjrpt/redo09.dbf
Tue Apr 21 10:24:27 2015
SERVER COMPONENT id=JAVAVM: status=VALID, version=12.1.0.2.0, timestamp=2015-04-21 10:24:27
Tue Apr 21 10:24:44 2015
### queuing purge of JIT compilation due to creation of 700010c40827818 oracle/xml/util/XMLUtil
### queuing purge of JIT compilation due to creation of 700010c508b7910 oracle/xdb/XMLType
Tue Apr 21 10:25:10 2015
### jox_purge_jit pid 41746460 pdb 0
Tue Apr 21 10:25:27 2015
SERVER COMPONENT id=XML: status=VALID, version=12.1.0.2.0, timestamp=2015-04-21 10:25:27
Tue Apr 21 10:25:33 2015
Shared IO Pool defaulting to 512MB. Trying to get it from Buffer Cache for process 42139652.
Tue Apr 21 10:25:52 2015
SERVER COMPONENT id=APS: status=VALID, version=12.1.0.2.0, timestamp=2015-04-21 10:25:52
SERVER COMPONENT id=AMD: status=OPTION OFF, version=11.2.0.3.0, timestamp=2015-04-21 10:25:53
Tue Apr 21 10:27:08 2015
SERVER COMPONENT id=CONTEXT: status=VALID, version=12.1.0.2.0, timestamp=2015-04-21 10:27:08
Tue Apr 21 10:27:55 2015
XDB installed.
XDB initialized.
Tue Apr 21 10:30:22 2015
SERVER COMPONENT id=XDB: status=VALID, version=12.1.0.2.0, timestamp=2015-04-21 10:30:22
Tue Apr 21 10:30:42 2015
SERVER COMPONENT id=CATJAVA: status=VALID, version=12.1.0.2.0, timestamp=2015-04-21 10:30:42
Tue Apr 21 10:32:07 2015
SERVER COMPONENT id=OWM: status=VALID, version=12.1.0.2.0, timestamp=2015-04-21 10:32:07
SERVER COMPONENT id=RAC: status=VALID, version=12.1.0.2.0, timestamp=2015-04-21 10:32:10
Tue Apr 21 10:35:15 2015
SERVER COMPONENT id=ORDIM: status=VALID, version=12.1.0.2.0, timestamp=2015-04-21 10:35:15
Tue Apr 21 10:37:59 2015
Thread 1 cannot allocate new log, sequence 16
Private strand flush not complete
Current log# 9 seq# 15 mem# 0: /oradata/redo/zjrpt/redo09.dbf
Tue Apr 21 10:38:02 2015
Thread 1 advanced to log sequence 16 (LGWR switch)
Current log# 10 seq# 16 mem# 0: /oradata/redo/zjrpt/redo10.dbf
Tue Apr 21 10:41:58 2015
SERVER COMPONENT id=SDO: status=UPGRADED, version=12.1.0.2.0, timestamp=2015-04-21 10:41:58
Tue Apr 21 10:42:58 2015
Errors in file /u04/oracle/app/oracle/diag/rdbms/zjrpt/zjrpt1/trace/zjrpt1_ora_39125322.trc (incident=48369):
ORA-00600: internal error code, arguments: [qkaQknLTPruneKaf:1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u04/oracle/app/oracle/diag/rdbms/zjrpt/zjrpt1/incident/incdir_48369/zjrpt1_ora_39125322_i48369.trc
Tue Apr 21 10:44:09 2015
Errors in file /u04/oracle/app/oracle/diag/rdbms/zjrpt/zjrpt1/incident/incdir_48369/zjrpt1_ora_39125322_i48369.trc:
ORA-00339: archived log does not contain any redo
ORA-00334: archived log: "/oradata/redo/zjrpt/redo14.dbf"
ORA-00600: internal error code, arguments: [qkaQknLTPruneKaf:1], [], [], [], [], [], [], [], [], [], [], []
Tue Apr 21 10:44:09 2015
Dumping diagnostic data in directory=[cdmp_20150421104409], requested by (instance=1, osid=14811583), summary=[incident=48369].
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Tue Apr 21 10:44:09 2015
SERVER COMPONENT id=XOQ: status=INVALID, version=12.1.0.2.0, timestamp=2015-04-21 10:44:11
Tue Apr 21 10:44:12 2015
SERVER COMPONENT id=ACTIONS_BGN: timestamp=2015-04-21 10:44:12
Tue Apr 21 10:52:06 2015
SERVER COMPONENT id=CATREQ_BGN: timestamp=2015-04-21 10:52:06
Tue Apr 21 10:54:06 2015
SERVER COMPONENT id=CATREQ_END: timestamp=2015-04-21 10:54:06
Tue Apr 21 10:54:23 2015
SERVER ACTION=UPGRADE id=: Upgraded from 11.2.0.3.0
SERVER COMPONENT id=ACTIONS_END: timestamp=2015-04-21 10:54:23
SERVER COMPONENT id=UPGRD_END: timestamp=2015-04-21 10:54:23
这里提示比较明显,XOQ组件由于ORA-600错误,未升级成功
分析trace文件
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics
and Real Application Testing options
ORACLE_HOME = /u04/oracle/app/oracle/product/12.1
System name: AIX
Node name: zjddrpt5
Release: 1
Version: 7
Machine: 00CB9D064C00
Instance name: zjrpt1
Redo thread mounted by this instance: 1
Oracle process number: 40
Unix process pid: 36765856, image: oracle@zjddrpt5 (TNS V1-V3)
*** 2015-04-21 11:27:29.567
*** SESSION ID:(2258.29003) 2015-04-21 11:27:29.567
*** CLIENT ID:() 2015-04-21 11:27:29.567
*** SERVICE NAME:(SYS$USERS) 2015-04-21 11:27:29.567
*** MODULE NAME:(catcon(pid=3473480)) 2015-04-21 11:27:29.567
*** CLIENT DRIVER:(SQL*PLUS) 2015-04-21 11:27:29.567
*** ACTION NAME:(non-CDB::@cmpupmsc.sql) 2015-04-21 11:27:29.567
[TOC00000]
Jump to table of contents
Dump continued from file: /u04/oracle/app/oracle/diag/rdbms/zjrpt/zjrpt1/trace/zjrpt1_ora_36765856.trc
[TOC00001]
ORA-00600: internal error code, arguments: [qkaQknLTPruneKaf:1], [], [], [], [], [], [], [], [], [], [], []
[TOC00001-END]
[TOC00002]
========= Dump for incident 192322 (ORA 600 [qkaQknLTPruneKaf:1]) ========
*** 2015-04-21 11:27:29.615
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
[TOC00003]
----- Current SQL Statement for this session (sql_id=5s1pbm9ykxmjx) -----
SELECT COUNT(*) FROM DBA_TAB_PRIVS WHERE GRANTEE="OLAP_XS_ADMIN" AND ((PRIVILEGE="SELECT" AND OWNER="SYS" AND
TABLE_NAME="XS$OLAP_POLICY" AND COMMON="YES") OR (PRIVILEGE="SELECT" AND OWNER="SYS" AND
TABLE_NAME="DBA_ROLES" AND COMMON="YES") OR (PRIVILEGE="EXECUTE" AND OWNER="SYS" AND TABLE_NAME="DBMS_XDS"
AND COMMON="YES"))
[TOC00004]
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
700010c76ef4290 171 procedure SYS.XOQ_VALIDATE
700010c67f9aa90 2 anonymous block
[TOC00004-END]
[TOC00003-END]
[TOC00005]
----- Call Stack Trace -----
skdstdst <- ksedst1 <- ksedst <- dbkedDefDump <- ksedmp
<- ksfdmp <- dbgexPhaseII <- dbgexProcessError <- dbgeExecuteForError <- dbgePostErrorKGE
<- 1244 <- dbkePostKGE_kgsf <- kgeadse <- kgerinv_internal <- kgerinv
<- kgeasnmierr <- qkaQknLTPruneKaf <- 780 <- qkaQknPruneKaf <- qknProjPushNode_Int
<- qknProjPushNode <- qkeWalkAllQueryNode <- qknProjPushStmt <- qkeProjPrune <- qkadrv2
<- opitca <- kksFullTypeCheck <- rpiswu2 <- kksLoadChild <- kxsGetRuntimeLock
<- kksfbc <- kkspbd0 <- kksParseCursor <- opiosq0 <- opipls
<- opiodr <- rpidrus <- skgmstack <- rpidru <- rpiswu2
<- rpidrv <- psddr0 <- psdnal <- pevm_EXECC <- pfrinstr_EXECC
<- pfrrun_no_tool <- pfrrun <- plsql_run <- peicnt <- kkxexe
<- opiexe <- kpoal8 <- opiodr <- ttcpip <- opitsk
<- opiino <- opiodr <- opidrv <- sou2o <- opimai_real
<- ssthrdmain <- main <- start <-
这里可以大概看出来，出现该问题，可能和SYS.XOQ_VALIDATE有关系.既然OLAP组件无效,那么我们是否可以尝试删除组件,然后重装OLAP组件
卸载OLAP组件
sqlplus /nolog
SQL> conn / as sysdba
SQL> spool remove_olap.log
----> Remove OLAP Catalog
SQL> @?/olap/admin/catnoamd.sql
----> Remove OLAP API
SQL> @?/olap/admin/olapidrp.plb
SQL> @?/olap/admin/catnoxoq.sql
----> Deinstall APS - OLAP AW component
SQL> @?/olap/admin/catnoaps.sql
----> Recompile invalids
SQL> @?/rdbms/admin/utlrp.sql
SQL> spool off
安装OLAP组件
SQL> @?/olap/admin/olap.sql SYSAUX TEMP
分析安装日志发现创建用户错误
old 3: execute immediate "select tablespace_name from dba_tablespaces where tablespace_name = upper(""&1"")
and contents = ""PERMANENT""" into :default_ts;
new 3: execute immediate "select tablespace_name from dba_tablespaces where tablespace_name =
upper(""SYSAUX"") and contents = ""PERMANENT""" into :default_ts;
old 10: execute immediate "select tablespace_name from dba_tablespaces where tablespace_name = upper(""&2"")
and contents = ""TEMPORARY""" into :temp_ts;
new 10: execute immediate "select tablespace_name from dba_tablespaces where tablespace_name =
upper(""TEMP"") and contents = ""TEMPORARY""" into :temp_ts;
PL/SQL procedure successfully completed.
Session altered.
old 10: || " default tablespace " || "&1"
new 10: || " default tablespace " || "SYSAUX"
old 11: || " temporary tablespace " || "&2"
new 11: || " temporary tablespace " || "TEMP"
old 12: || " quota unlimited on " || "&1";
new 12: || " quota unlimited on " || "SYSAUX";
DECLARE
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20008: Password must contain at least one digit, one character
ORA-06512: at line 8
…………………………
ERROR at line 1:
ORA-01435: user does not exist
通过分析olap对应的创建语句,发现会在olap安装过程中,如果没有olapsys用户,数据库会自己创建该用户
DECLARE
isthere NUMBER;
BEGIN
select count(*) into isthere from dba_users where username ="OLAPSYS";
if isthere = 0 then
execute immediate "create user olapsys identified by no_password"
|| " password expire account lock"
|| " default tablespace " || "&1"
|| " temporary tablespace " || "&2"
|| " quota unlimited on " || "&1";
end if;
END;
/
这里比较明显的显示了创建用户olapsys 状态为Lock，密码为no_password,根据错误提示,应该是该密码不符合要求,对其进行测试
zjddrpt5:/u04/oracle/app/oracle/product/12.1/olap/admin$sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Apr 22 16:40:25 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics
and Real Application Testing options
SQL> create user xifenfei identified by no_password;
create user xifenfei identified by no_password
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20008: Password must contain at least one digit, one character
SQL> create user xifenfei identified by "1qsx!qaz";
User created.
分析密码验证原因
SQL> select * from dba_profiles where profile="DEFAULT";
PROFILE RESOURCE_NAME RESOURCE LIMIT COM
------------------------------ -------------------------------- -------- -------------------------------------------------- ---
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED NO
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED NO
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED NO
DEFAULT CPU_PER_CALL KERNEL UNLIMITED NO
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED NO
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED NO
DEFAULT IDLE_TIME KERNEL UNLIMITED NO
DEFAULT CONNECT_TIME KERNEL UNLIMITED NO
DEFAULT PRIVATE_SGA KERNEL UNLIMITED NO
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED NO
DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED NO
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED NO
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED NO
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD VERIFY_FUNCTION_11G NO
DEFAULT PASSWORD_LOCK_TIME PASSWORD UNLIMITED NO
DEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITED NO
16 rows selected.
因为数据库在创建用户之时未指定profile,而默认的profile密码设置了验证,因此olap安装脚本创建olapsys用户的密码为no_password无法成功,因此该用户无法创建.基于此，那我尝试人工创建olapsys用户,然后再执行olap.sql 试试看
SQL> create user olapsys identified by "1qsx!qaz" password
2 expire account lock default tablespace SYSAUX temporary tablespace TEMP quota unlimited on SYSAUX;
User created.
SQL> @?/olap/admin/olap.sql SYSAUX TEMP
分析执行日志文件,发现均为对象not exist情况的错误,类似
ORA-04043: object COAD_ADVICE_T does not exist
ORA-04043: object COAD_ADVICE_REC does not exist
ORA-01432: public synonym to be dropped does not exist
ORA-00942: table or view does not exist
ORA-01432: public synonym to be dropped does not exist
ORA-00942: table or view does not exist
这里证明安装过程未出错,查询无效对象
select owner,object_name,object_type from dba_objects where status=’INVALID’
[/sql]
也未发现任何sys/system/olapsys中发现任何无效对象,但是OLAP API依然无效,陷入了误解中
SQL> select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;
COMP_ID
------------------------------
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ ----------------------
SDO
Spatial
12.1.0.2.0 VALID
ORDIM
Oracle Multimedia
12.1.0.2.0 VALID
COMP_ID
------------------------------
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ ----------------------
XDB
Oracle XML Database
12.1.0.2.0 VALID
CONTEXT
Oracle Text
COMP_ID
------------------------------
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ ----------------------
12.1.0.2.0 VALID
OWM
Oracle Workspace Manager
12.1.0.2.0 VALID
CATALOG
COMP_ID
------------------------------
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ ----------------------
Oracle Database Catalog Views
12.1.0.2.0 VALID
CATPROC
Oracle Database Packages and Types
12.1.0.2.0 VALID
COMP_ID
------------------------------
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ ----------------------
JAVAVM
JServer JAVA Virtual Machine
12.1.0.2.0 VALID
XML
Oracle XDK
12.1.0.2.0 VALID
COMP_ID
------------------------------
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ ----------------------
CATJAVA
Oracle Database Java Packages
12.1.0.2.0 VALID
APS
OLAP Analytic Workspace
COMP_ID
------------------------------
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ ----------------------
12.1.0.2.0 VALID
XOQ
Oracle OLAP API
12.1.0.2.0 INVALID
RAC
COMP_ID &nbs
解决方案|解决Oracle 12.1.0.2 的OLAP API组件无效问题
http://m.bbyears.com/shujuku/75309.html
推荐访问:解决问题 解决方案模板 解决形式主义突出问题为基层减负 解决问题的能力 解决方案工程师 解决的英文