一聚教程网:一个值得你收藏的教程网站

热门教程

解决Oracle 12.1.0.2 的OLAP API组件无效问题

时间:2022-06-29 09:50:22 编辑:袖梨 来源:一聚教程网

数据库从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

热门栏目