联系：手机(17813235971) QQ(107644445)
链接：http://www.xifenfei.com/5859.html
标题：Oracle 12.1.0.2 的OLAP API组件无效的处理过程
作者：惜分飞©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]
数据库从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中密码验证配置,免得导致用户无法创建的错误(在升级中有些组件需要重建用户)
