Chinaunix首页 | 论坛 | 博客
  • 博客访问: 643578
  • 博文数量: 128
  • 博客积分: 265
  • 博客等级: 二等列兵
  • 技术积分: 1464
  • 用 户 组: 普通用户
  • 注册时间: 2011-09-27 20:44
个人简介

just do it

文章分类

全部博文(128)

文章存档

2023年(1)

2020年(1)

2019年(1)

2018年(3)

2017年(6)

2016年(17)

2015年(16)

2014年(39)

2013年(34)

2012年(10)

分类: Oracle

2014-08-07 10:17:53

(文档 ID 466363.1)

In this Document

Symptoms
Cause
Solution



 


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

阅读(2014) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~