(文档 ID 466363.1)
In this Document
Applies to:
Oracle OLAP - Version 9.2.0.8 to 11.2.0.4 [Release 9.2 to 11.2]
Information in this document applies to any platform.
Symptoms
After adding OLAP, the OLAP API is INVALID in dba_registry. Re-ran the utlrp.sql script to re-compile all the database objects and the OLAP API still shows up as INVALID.
XOQ Oracle OLAP API 11.2.0.2.0 INVALID
Cause
For this one particular case, upon running the modified xoq_validate procedure (see included script), which includes additional output statements not in the original xoq_validate procedure, it was identified that there was a missing library from the operating system, in this case:
ORA-37111: Unable to load the OLAP API sharable library: (libstdc++.so.5: cannot open shared
object file: No such file or directory)
This missing library or any other requirement for validation can now be identified and corrected to allow this validation procedure to complete successfully.
Solution
Use the script below to create the xoq_validate_verbose procedure.
Note:
When attempting to use this code in 11g the following change must be made:
Original line for 9.2 and 10g:
dummy_num := OlapiBootstrap(dummy_str);
New Line for 11g:
dummy_num := OlapiBootstrap2(dummy_str, dummy_str, dummy_str);
CREATE OR REPLACE PROCEDURE xoq_validate_verbose IS
compat VARCHAR2(30);
dummy_num NUMBER;
dummy_str VARCHAR2(100);
name VARCHAR2(50);
versn VARCHAR2(10);
status VARCHAR2(15);
BEGIN
SELECT value INTO compat FROM v$parameter WHERE name='compatible';
DBMS_OUTPUT.PUT_LINE('compat -> '||compat);
IF NOT (substr(compat,1,3) >= '9.2' OR substr(compat,1,2) >= '10')
THEN
dbms_registry.invalid('XOQ');
DBMS_OUTPUT.PUT_LINE('AFTER COMPAT: REGISTRY.INVALID');
ELSE
BEGIN
DBMS_OUTPUT.PUT_LINE('BEFORE BOOTSTRAP');
dummy_num := OlapiBootstrap(dummy_str);
DBMS_OUTPUT.PUT_LINE('AFTER BOOTSTRAP');
DBMS_OUTPUT.PUT_LINE('dummy_num: '||to_char(dummy_num));
dbms_registry.valid('XOQ');
DBMS_OUTPUT.PUT_LINE('AFTER BOOTSTRAP: REGISTRY.VALID');
SELECT comp_name INTO name FROM dba_registry where comp_name like '%OLAP API%';
SELECT version INTO versn FROM dba_registry where comp_name like '%OLAP API%';
SELECT status INTO status FROM dba_registry where comp_name like '%OLAP API%';
DBMS_OUTPUT.PUT_LINE('comp_name: '||name);
DBMS_OUTPUT.PUT_LINE('version: '||versn);
DBMS_OUTPUT.PUT_LINE('status: '||status);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(sqlerrm);
dbms_registry.invalid('XOQ');
DBMS_OUTPUT.PUT_LINE('AFTER BOOTSTRAP: REGISTRY.INVALID');
SELECT comp_name INTO name FROM dba_registry where comp_name like '%OLAP API%';
SELECT version INTO versn FROM dba_registry where comp_name like '%OLAP API%';
SELECT status INTO status FROM dba_registry where comp_name like '%OLAP API%';
DBMS_OUTPUT.PUT_LINE('comp_name: '||name);
DBMS_OUTPUT.PUT_LINE('version: '||versn);
DBMS_OUTPUT.PUT_LINE('status: '||status);
END; END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Select '||sqlerrm); END;
/
In 11.2.0.4 the xoq_validate has been expanded with couple of more checks.
Please use the following code to create the xoq_validate_verbose in 11.2.0.4 (no modifications needed below with the 11.2.0.4 or greater release).
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;
/
Once you have created the xoq_validate_verbose procedure, run it as follows when connected as '/ as sysdba':
SQL> set serveroutput on size 10000
SQL> exec xoq_validate_verbose
The output, in this case, indicated that the 'libstd++.so.5' library file was not found and therefore the validation code could not run to completion.
To implement the solution, execute the following steps:
1. Correct the problem identified by running this modified script
2. Re-execute the xoq_validate procedure
3. Verify that there are no errors returned
4. Verify that the OLAP API is now VALID by executing the SQL commands when connected '/ as sysdba':
SQL> col comp_name format a30
SQL> SELECT comp_name, status, substr(version,1,10) as version from dba_registry;
MORE INFORMATION:
Errors similar to the following means you are loading the wrong version of OLAP API sharable library, libolapapi11.so
SQL> exec xoq_validate_verbose
compat -> 11.2.0
BEFORE BOOTSTRAP
ORA-37111: Unable to load the OLAP API sharable library: (Function not
implemented (xsoqGetSubObjectCpp))
AFTER BOOTSTRAP: REGISTRY.INVALID
comp_name: Oracle OLAP API
version: 11.2.0.3.0
status: INVALID
Make sure you have set the library path environment variable correctly to point to your new ORACLE_HOME
LINUX/ SOLARIS: LD_LIBRARY_PATH
AIX: LIBPATH
HP-UX: SHLIB_PATH
If you hame more than one ORACLE_HOME, you may be loading the wrong version of the OLAP API sharable library, libolapapi11.so
Shut down the other database instance or rename any copies of libolapapi11.so
阅读(2225) | 评论(0) | 转发(0) |
