分类目录归档：数据库

数据库从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                                                                         
------------------------------                                                  
COMP_NAME                                                                       
--------------------------------------------------------------------------------
VERSION                        STATUS                                           
------------------------------ ----------------------                           
Oracle Real Application Clusters                                                
12.1.0.2.0                     VALID                                            
                                                                                

13 rows selected.

到这一步已经超过了我的能力和大连gcs的能力范围,sr升级到美国olap研发团队,配合他们进行分析

CREATE OR REPLACE PROCEDURE xoq_validate_verbose IS 
compat VARCHAR2(30); 
dummy_num NUMBER; 
dummy_out_1_str VARCHAR2(100); 
dummy_out_2_str VARCHAR2(100); 
ok BOOLEAN := TRUE; 
BEGIN 
-- check compatible 
SELECT value INTO compat FROM v$parameter WHERE name='compatible'; 
IF NOT (substr(compat,1,3) >= '9.2' OR substr(compat,1,2) >= '10') THEN 
ok := FALSE; 
END IF; 
dbms_output.put_line('compatible:'||compat||' ok:'|| case when ok then 'True' else 'False' end); 
IF ok THEN 
--check for errors during installation/upgrade 
BEGIN 
SELECT 0 INTO dummy_num from sys.registry$error 
WHERE identifier='XOQ'AND rownum <=1; 
-- at least one install error was found so component is invalid 
ok := FALSE; 
dbms_output.put_line('xoq errors during installation/upgrade. Query sys.registry$error WHERE identifier euqal to XOQ. ok:'|| case when ok then 'True' else 'False' end); 
EXCEPTION 
WHEN NO_DATA_FOUND THEN 
dbms_output.put_line('No install errors were found so component remains valid. ok:'|| case when ok then 'True' else 'False' end); 
NULL; 
END; 
END IF; 

IF ok THEN 
-- check that dependent component XDB is valid 
IF dbms_registry.is_valid('XDB', dbms_registry.release_version) != 1 THEN 
ok := FALSE; 
dbms_output.put_line('dependent component XDB is invalid. ok:'|| case when ok then 'True' else 'False' end); 
END IF; 
END IF; 

IF ok THEN 
-- check that expected XDB resources are there 
IF NOT (dbms_xdb.existsresource('/OLAP_XDS/dsclass.xml') AND 
dbms_xdb.existsresource('/olap_data_security/public/acls') AND 
dbms_xdb.existsresource('/xds/dsd')) THEN 
ok := FALSE; 
dbms_output.put_line('expected XDB resources are not there. ok:'|| case when ok then 'True' else 'False' end); 
END IF; 
END IF; 

IF ok THEN 
-- check that installed library is valid 
BEGIN 
SELECT 0 INTO dummy_num FROM DBA_LIBRARIES 
WHERE STATUS = 'INVALID' AND rownum <=1 AND 
OWNER='SYS' AND LIBRARY_NAME = 'DBMS_OLAPI_LIB'; 
-- at least one object is invalid so component is invalid 
ok := FALSE; 
dbms_output.put_line('installed library is invalid. ok:'|| case when ok then 'True' else 'False' end); 
EXCEPTION 
WHEN NO_DATA_FOUND THEN 
-- no invalid objects were found so component remains valid 
NULL; 
END; 
END IF; 

IF ok THEN 
-- check very basic OLAP API function (including load of shared library) 
BEGIN 
dummy_num := OlapiBootstrap2(compat, dummy_out_1_str, dummy_out_2_str); 
EXCEPTION 
WHEN OTHERS THEN 
ok := FALSE; 
DBMS_OUTPUT.PUT_LINE(sqlerrm); 
dbms_output.put_line('Error during OlapiBootstrap2. ok:'|| case when ok then 'True' else 'False' end); 
END; 
END IF; 

IF ok THEN 
-- check that Java classes are loaded successfully 
BEGIN 
SELECT 0 INTO dummy_num FROM dba_objects 
WHERE owner = 'SYS' AND 
status = 'INVALID' AND 
object_type = 'JAVA CLASS' AND 
object_name LIKE 'oracle/AWXML/%'; 
-- at least one class is invalid so component is invalid 
ok := FALSE; 
dbms_output.put_line('oracle/AWXML/.. Java class invalid. ok:'|| case when ok then 'True' else 'False' end); 
EXCEPTION 
WHEN NO_DATA_FOUND THEN 
-- no invalid components were found so component remains valid 
NULL; 
END; 
END IF; 

IF ok THEN 
-- check that installed types, packages, and procedures are valid 
BEGIN 
SELECT 0 INTO dummy_num FROM DBA_OBJECTS 
WHERE STATUS = 'INVALID' AND rownum <=1 AND 
OWNER='SYS' AND OBJECT_NAME IN 
('DBMS_CUBE_ADVISE','DBMS_CUBE_ADVISE_SEC','DBMS_CUBE', 
'DBMS_CUBE_EXP','GENDATABASEINTERFACE','GENCONNECTIONINTERFACE', 
'GENSERVERINTERACE','GENMDMPROPERTYIDCONSTANTS', 
'GENMDMCLASSCONSTANTS','GENMDMOBJECTIDCONSTANTS', 
'GENMETADATAPROVIDERINTERFACE','GENCURSORMANAGERINTERFACE', 
'GENDATATYPEIDCONSTANTS','GENDEFINITIONMANAGERINTERFACE', 
'GENDATAPROVIDERINTERFACE','DBMS_AW_XML','DBMS_CUBE_UTIL', 
'COAD_ADVICE_T','COAD_ADVICE_REC','GENOLAPIEXCEPTION', 
'GENINTERFACESTUB', 'GENINTERFACESTUBSEQUENCE', 
'GENRAWSEQUENCE','GENWSTRINGSEQUENCE', 
'DBMS_CUBE_UTIL_EXT_MD_T','DBMS_CUBE_UTIL_EXT_MD_R', 
'OLAPIHANDSHAKE2','OLAPIBOOTSTRAP2'); 
-- at least one object is invalid so component is invalid 
ok := FALSE; 
dbms_output.put_line('Olap type, packages or procedure is invalid. ok:'|| case when ok then 'True' else 'False' end); 
EXCEPTION 
WHEN NO_DATA_FOUND THEN 
-- no invalid objects were found so component remains valid 
NULL; 
END; 
END IF; 

IF ok THEN 
-- check for expected role 
BEGIN 
SELECT 0 INTO dummy_num FROM DBA_ROLES 
WHERE ROLE = 'OLAP_XS_ADMIN'; 
EXCEPTION 
WHEN NO_DATA_FOUND THEN 
ok := FALSE; 
dbms_output.put_line('OLAP_XS_ADMIN role is missing. ok:'|| case when ok then 'True' else 'False' end); 
END; 
END IF; 

IF ok THEN 
SELECT COUNT(*) INTO dummy_num FROM DBA_TAB_PRIVS WHERE GRANTEE='OLAP_XS_ADMIN' AND ( 
(PRIVILEGE='EXECUTE' AND OWNER='SYS' AND TABLE_NAME='AWM_CREATEXDSFOLDER') OR 
(PRIVILEGE='SELECT' AND OWNER='SYS' AND TABLE_NAME='DBA_ROLES') OR 
(PRIVILEGE='SELECT' AND OWNER='SYS' AND TABLE_NAME='DBA_XDS_INSTANCE_SETS') OR 
(PRIVILEGE='EXECUTE' AND OWNER='SYS' AND TABLE_NAME='DBMS_XDS') OR 
(PRIVILEGE='EXECUTE' AND OWNER='SYS' AND TABLE_NAME='DBMS_XS_DATA_SECURITY_EVENTS') OR 
(PRIVILEGE='EXECUTE' AND OWNER='SYS' AND TABLE_NAME='DBMS_XS_PRIVID_LIST') OR 
(PRIVILEGE='EXECUTE' AND OWNER='SYS' AND TABLE_NAME='DBMS_XS_ROLESET_EVENTS_INT') OR 
(PRIVILEGE='EXECUTE' AND OWNER='SYS' AND TABLE_NAME='DBMS_XS_SECCLASS_EVENTS') OR 
(PRIVILEGE='EXECUTE' AND OWNER='SYS' AND TABLE_NAME='DBMS_XS_SECCLASS_INT') OR 
(PRIVILEGE='EXECUTE' AND OWNER='XDB' AND TABLE_NAME='DBMS_XDB') OR 
(PRIVILEGE='UPDATE' AND OWNER='XDB' AND TABLE_NAME='XDB$ACL') OR 
(PRIVILEGE='DELETE' AND OWNER='XDB' AND TABLE_NAME='XDB$ACL') OR 
(PRIVILEGE='INSERT' AND OWNER='XDB' AND TABLE_NAME='XDB$ACL') OR 
(PRIVILEGE='SELECT' AND OWNER='XDB' AND TABLE_NAME='XDB$ACL') OR 
(PRIVILEGE='DELETE' AND OWNER='XDB' AND TABLE_NAME='XS$DATA_SECURITY') OR 
(PRIVILEGE='UPDATE' AND OWNER='XDB' AND TABLE_NAME='XS$DATA_SECURITY') OR 
(PRIVILEGE='INSERT' AND OWNER='XDB' AND TABLE_NAME='XS$DATA_SECURITY') OR 
(PRIVILEGE='SELECT' AND OWNER='XDB' AND TABLE_NAME='XS$DATA_SECURITY') OR 
(PRIVILEGE='DELETE' AND OWNER='XDB' AND TABLE_NAME='XS$PRINCIPALS') OR 
(PRIVILEGE='UPDATE' AND OWNER='XDB' AND TABLE_NAME='XS$PRINCIPALS') OR 
(PRIVILEGE='SELECT' AND OWNER='XDB' AND TABLE_NAME='XS$PRINCIPALS') OR 
(PRIVILEGE='INSERT' AND OWNER='XDB' AND TABLE_NAME='XS$PRINCIPALS') OR 
(PRIVILEGE='DELETE' AND OWNER='XDB' AND TABLE_NAME='XS$SECURITYCLASS') OR 
(PRIVILEGE='INSERT' AND OWNER='XDB' AND TABLE_NAME='XS$SECURITYCLASS') OR 
(PRIVILEGE='UPDATE' AND OWNER='XDB' AND TABLE_NAME='XS$SECURITYCLASS') OR 
(PRIVILEGE='SELECT' AND OWNER='XDB' AND TABLE_NAME='XS$SECURITYCLASS')); 
IF dummy_num != 26 THEN 
ok := FALSE; 
dbms_output.put_line('OLAP_XS_ADMIN does necessary privs. ok:'|| case when ok then 'True' else 'False' end); 
END IF; 
END IF; 

END; 
/ 

SQL> set serveroutput on size 10000 
SQL> exec xoq_validate_verbose; 
compatible:12.1.0.2.0 ok:True
xoq errors during installation/upgrade. Query sys.registry$error WHERE
identifier euqal to XOQ. ok:False

PL/SQL procedure successfully completed.


SQL> set long 12000 
SQL> set lines 1200 
SQL> set pages 1200 
select * from sys.registry$error WHERE identifier ='XOQ'; 
SQL> 
USERNAME                                                                                                                                                                                                                                                         TIMESTAMP
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------
SCRIPT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IDENTIFIER                                                                                                                                                                                                                                                       MESSAGE                                                                          STATEMENT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
SYS                                                                                                                                                                                                                                                              21-APR-15 11.28.35.000000 AM
/u04/oracle/app/oracle/product/12.1/olap/admin/xoqroles.sql
XOQ                                                                                                                                                                                                                                                              ORA-00600: internal error code, arguments: [qkaQknLTPruneKaf:1], [], [], [], [], begin
                                                                                                                                                                                                                                                                  [], [], [], [], [], [], []                                                       xoq_validate;
                                                                                                                                                                                                                                                                                                                                                  exception
                                                                                                                                                                                                                                                                                                                                                  when others then
                                                                                                                                                                                                                                                                                                                                                   sys.dbms_registry.invalid('XOQ');
                                                                                                                                                                                                                                                                                                                                                  end;

SYS                                                                                                                                                                                                                                                              21-APR-15 11.28.35.000000 AM
/u04/oracle/app/oracle/product/12.1/olap/admin/xoqroles.sql
XOQ                                                                                                                                                                                                                                                              [], [], [], [], [], [], [], []                                                   as above

继续处理

SQL> delete from sys.registry$error where identifier='XOQ'; 

2 rows deleted.

SQL> commit;

Commit complete.

SQL> exec xoq_validate_verbose ;
compatible:12.1.0.2.0 ok:True
No install errors were found so component remains valid. ok:True
expected XDB resources are not there. ok:False

PL/SQL procedure successfully completed.

SQL> select * from sys.registry$error WHERE identifier ='XOQ';

no rows selected


@?/olap/admin/xoqrelod.sql; 
@?/rdbms/admin/utlrp.sql

  SQL> select comp_name, status, substr(version,1,10) as version from dba_registry; 

COMP_NAME                                           STATUS                 VERSION
--------------------------------------------------- ---------------------- --------------------
Spatial                                             VALID                  12.1.0.2.0
Oracle Multimedia                                   VALID                  12.1.0.2.0
Oracle XML Database                                 VALID                  12.1.0.2.0
Oracle Text                                         VALID                  12.1.0.2.0
Oracle Workspace Manager                            VALID                  12.1.0.2.0
Oracle Database Catalog Views                       VALID                  12.1.0.2.0
Oracle Database Packages and Types                  VALID                  12.1.0.2.0
JServer JAVA Virtual Machine                        VALID                  12.1.0.2.0
Oracle XDK                                          VALID                  12.1.0.2.0
Oracle Database Java Packages                       VALID                  12.1.0.2.0
OLAP Analytic Workspace                             VALID                  12.1.0.2.0
Oracle OLAP API                                     VALID                  12.1.0.2.0
Oracle Real Application Clusters                    VALID                  12.1.0.2.0

13 rows selected.

至此处理完成OLAP API组件终于变成VALID状态.出现此类问题,是否由于设置了default profile文件的密码验证函数导致,还是oracle 12.1.0.2.3的bug导致不能确定.这里到这里让我知道,在数据库升级过程中,最好检查default profile中密码验证配置,免得导致用户无法创建的错误(在升级中有些组件需要重建用户)