3 Replies Latest reply: Mar 4, 2013 3:18 AM by 953987 RSS

    OLAP and version on EBS database upgrade and migration

    953987
      Hi all,

      Recently I have upgraded EBS database to 11.2.0.3. When I querey " select COMP_ID,COMP_NAME,VERSION,STATUS
      from dba_registry; " getting below output
      SQL> select COMP_ID,COMP_NAME,VERSION,STATUS,MODIFIED from dba_registry;
      
      COMP_ID                        COMP_NAME                                    VERSION                        STATUS      
      ------------------------------ ----------------------------------------                  ------------------------------ ----------- --------------------
      ODM                            Oracle Data Mining                            11.2.0.3.0                     VALID       
      CONTEXT                        Oracle Text                                  11.2.0.3.0                     VALID       
      AMD                            OLAP Catalog                                  11.2.0.3.0                     VALID     
      SDO                            Spatial                                            11.2.0.3.0                     VALID       
      ORDIM                          Oracle Multimedia                            11.2.0.3.0                     VALID       
      XDB                            Oracle XML Database                         11.2.0.3.0                     VALID       
      EXF                            Oracle Expression Filter                       11.2.0.3.0                     VALID       
      CATALOG                        Oracle Database Catalog Views            11.2.0.3.0                     VALID       
      CATPROC                        Oracle Database Packages and Types       11.2.0.3.0                     VALID       
      RAC                            Oracle Real Application Clusters                 11.2.0.3.0                     INVALID     
      JAVAVM                         JServer JAVA Virtual Machine                11.2.0.3.0                     VALID       
      XML                            Oracle XDK                                           11.2.0.3.0                     VALID       
      CATJAVA                        Oracle Database Java Packages            11.2.0.3.0                     VALID       
      APS                            OLAP Analytic Workspace                      11.2.0.3.0                     VALID       
      XOQ                            Oracle OLAP API                                 11.2.0.3.0                     VALID       
      
      
      15 rows selected.
      I thought everything goes well.


      Now I am migrating EBS from 32 bit linux to 64 bit and when I follow the note for OLAP AW Note: 579234.1 "verify OLAP was in use ". When I qurey " select DBID,NAME,VERSION,DETECTED_USAGES,CURRENTLY_USED from dba_feature_usage_statistics "
      3874995160 Virtual Private Database (VPD)                                   10.2.0.5.0                      1 TRUE
      3874995160 XDB                                                              10.2.0.5.0                      1 TRUE
      3874995160 LOB                                                              10.2.0.5.0                      1 TRUE
      3874995160 Object                                                           10.2.0.5.0                      1 TRUE
      3874995160 Extensibility                                                    10.2.0.5.0                      1 TRUE
      3874995160 ASO native encryption and checksumming                           11.2.0.3.0                      0 FALSE
       103678515 Advanced Replication                                             10.2.0.3.0                      0 FALSE
       103678515 Advanced Security                                                10.2.0.3.0                      0 FALSE
       103678515 Audit Options                                                    10.2.0.3.0                      0 FALSE
       103678515 Automatic Database Diagnostic Monitor                            10.2.0.3.0                      0 FALSE
       103678515 Automatic Segment Space Management (system)                      10.2.0.3.0                    144 TRUE
       103678515 Automatic Segment Space Management (user)                        10.2.0.3.0                    144 TRUE
       103678515 Messaging Gateway                                                10.2.0.3.0                      0 FALSE
       103678515 MTTR Advisor                                                     10.2.0.3.0                      0 FALSE
       103678515 Multiple Block Sizes                                             10.2.0.3.0                      0 FALSE
       103678515 OLAP - Analytic Workspaces                                       10.2.0.3.0                    143 TRUE
       103678515 OLAP - Cubes                                                     10.2.0.3.0                      0 FALSE
       103678515 Oracle Managed Files                                             10.2.0.3.0                      0 FALSE
       103678515 Parallel SQL DDL Execution                                       10.2.0.3.0                     71 FALSE
       103678515 Parallel SQL DML Execution                                       10.2.0.3.0                     68 FALSE
        103678515 Virtual Private Database (VPD)                                   10.2.0.3.0                    144 TRUE
       103678515 Advanced Replication                                             10.2.0.5.0                      0 FALSE
       103678515 ASO native encryption and checksumming                           10.2.0.5.0                      0 FALSE
       103678515 Audit Options                                                    10.2.0.5.0                      0 FALSE
       103678515 Automatic Database Diagnostic Monitor                            10.2.0.5.0                      0 FALSE
       103678515 Automatic Segment Space Management (system)                      10.2.0.5.0                     25 TRUE
       103678515 Automatic Segment Space Management (user)                        10.2.0.5.0                     25 TRUE
       103678515 Automatic SQL Execution Memory                                   10.2.0.5.0                     25 TRUE
       103678515 Automatic Storage Manager                                        10.2.0.5.0                      0 FALSE
       103678515 Automatic Undo Management                                        10.2.0.5.0                     25 TRUE
       103678515 Automatic Workload Repository                                    10.2.0.5.0                      0 FALSE
       103678515 AWR Report                                                       10.2.0.5.0                      0 FALSE
       103678515 Change-Aware Incremental Backup                                  10.2.0.5.0                      0 FALSE
       103678515 CSSCAN                                                           10.2.0.5.0                      0 FALSE
       103678515 Character Semantics                                              10.2.0.5.0                      0 FALSE
       103678515 Character Set                                                    10.2.0.5.0                     25 TRUE
       103678515 Data Guard                                                       10.2.0.5.0                      0 FALSE
       103678515 Data Guard Broker                                                10.2.0.5.0                      0 FALSE
       103678515 Data Mining                                                      10.2.0.5.0                      0 FALSE
       103678515 Dynamic SGA                                                      10.2.0.5.0                      0 FALSE
        103678515 MTTR Advisor                                                     10.2.0.5.0                      0 FALSE
       103678515 Multiple Block Sizes                                             10.2.0.5.0                      0 FALSE
       103678515 OLAP - Analytic Workspaces                                       10.2.0.5.0                     25 TRUE
       103678515 OLAP - Cubes                                                     10.2.0.5.0                      0 FALSE
       103678515 Oracle Managed Files                                             10.2.0.5.0                      0 FALSE
       103678515 Parallel SQL DDL Execution                                       10.2.0.5.0                      1 FALSE
       103678515 Parallel SQL DML Execution                                       10.2.0.5.0                      0 FALSE
       103678515 Parallel SQL Query Execution                                     10.2.0.5.0                     22 TRUE
        103678515 Recovery Manager (RMAN)                                          10.2.0.5.0                     25 TRUE
       103678515 RMAN - Disk Backup                                               10.2.0.5.0                     24 TRUE
       103678515 RMAN - Tape Backup                                               10.2.0.5.0                     25 TRUE
       103678515 Resource Manager                                                 10.2.0.5.0                      0 FALSE
       103678515 SQL Tuning Set                                                   10.2.0.5.0                      0 FALSE
        103678515 Streams (user)                                                   10.2.0.5.0                     25 TRUE
       103678515 Transparent Gateway                                              10.2.0.5.0                      0 FALSE
       103678515 Undo Advisor                                                     10.2.0.5.0                      0 FALSE
       103678515 Virtual Private Database (VPD)                                   10.2.0.5.0                     25 TRUE
       103678515 XDB                                                              10.2.0.5.0                     25 TRUE
       103678515 LOB                                                              10.2.0.5.0                     25 TRUE
       103678515 Object                                                           10.2.0.5.0                     25 TRUE
       103678515 Extensibility                                                    10.2.0.5.0                     25 TRUE
       103678515 Change Data Capture                                              10.2.0.5.0                      0 FALSE
       103678515 Materialized Views (User)                                        10.2.0.5.0                     25 TRUE
      3874453699 Advanced Replication                                             10.2.0.5.0                      0 FALSE
      3874453699 ASO native encryption and checksumming                           10.2.0.5.0                      0 FALSE
      3874453699 Audit Options                                                    10.2.0.5.0                      0 FALSE
      3874453699 Data Mining                                                      10.2.0.5.0                      0 FALSE
      3874453699 Dynamic SGA                                                      10.2.0.5.0                      0 FALSE
      3874453699 EM Database Control                                              10.2.0.5.0                      0 FALSE
      3874453699 EM Grid Control                                                  10.2.0.5.0                      0 FALSE
      3874453699 MTTR Advisor                                                     10.2.0.5.0                      0 FALSE
      3874453699 Multiple Block Sizes                                             10.2.0.5.0                      0 FALSE
      3874453699 OLAP - Analytic Workspaces                                       10.2.0.5.0                      1 TRUE
      3874453699 OLAP - Cubes                                                     10.2.0.5.0                      0 FALSE
      3874453699 Oracle Managed Files                                             10.2.0.5.0                      0 FALSE
      3874453699 Parallel SQL DDL Execution                                       10.2.0.5.0                      0 FALSE
      3874453699 Parallel SQL DML Execution                                       10.2.0.5.0                      0 FALSE
      3874453699 Parallel SQL Query Execution                                     10.2.0.5.0                      0 FALSE
      3874453699 Resource Manager                                                 10.2.0.5.0                      0 FALSE
      3874453699 Server Parameter File                                            10.2.0.5.0                      1 TRUE
      3874453699 Shared Server                                                    10.2.0.5.0                      0 FALSE
      3874453699 Spatial                                                          10.2.0.5.0                      1 TRUE
      3874453699 Locator                                                          10.2.0.5.0                      0 FALSE
      3874995160 Partitioning (user)                                              11.2.0.3.0                      1 TRUE
      3874995160 Services                                                         11.2.0.3.0                      1 TRUE
      3874995160 Oracle Java Virtual Machine (system)                             11.2.0.3.0                      1 TRUE
      
      446 rows selected.
      This is cloned instanse from from production.

      1 - I can see different DBID for the same and for some DBID it is showing different version. any problem during upgrade ( 10.2.0.3 -> 10.2.0.5 - one year back) and (10.2.0.5 to 11.2.0.3) ?

      If any problem, please let me know what I can do to fix.


      2 - For OLAP details :

      SQL> select DBID,NAME,VERSION,DETECTED_USAGES,CURRENTLY_USED from dba_feature_usage_statistics where NAME like 'OLAP%';

      DBID NAME VERSION DETECTED_USAGES CURRE
      ---------- ---------------------------------------------------------------- ----------------- --------------- -----
      103678515 OLAP - Analytic Workspaces 10.2.0.3.0 143 TRUE
      103678515 OLAP - Analytic Workspaces 10.2.0.5.0 25 TRUE
      3874453699 OLAP - Analytic Workspaces 10.2.0.5.0 1 TRUE
      3874995160 OLAP - Analytic Workspaces 10.2.0.5.0 1 TRUE
      3874995160 OLAP - Analytic Workspaces 11.2.0.3.0 1 TRUE
      103678515 OLAP - Cubes 10.2.0.3.0 0 FALSE
      103678515 OLAP - Cubes 10.2.0.5.0 0 FALSE
      3874453699 OLAP - Cubes 10.2.0.5.0 0 FALSE
      3874995160 OLAP - Cubes 10.2.0.5.0 0 FALSE
      3874995160 OLAP - Cubes 11.2.0.3.0 0 FALSE

      Do I need to do anything for export olap AW ?


      3 - Remobe OLAP from 32 bit. After following the steps mentioned in note: 352306.1 am getting invalid objects with object name start with OLAP.

      SQL> select owner, object_name, object_type from dba_objects where status <> 'VALID';

      OWNER OBJECT_NAME OBJECT_TYPE
      ------------------------------ ---------------------------------------- -------------------
      SYS OLAPIBOOTSTRAP FUNCTION
      SYS OLAPIHANDSHAKE FUNCTION
      PUBLIC OLAPIBOOTSTRAP SYNONYM
      PUBLIC OLAPIHANDSHAKE SYNONYM

      Do I need to remove these objects manually?


      Thanks,
        • 1. Re: OLAP and version on EBS database upgrade and migration
          Hussein Sawwan-Oracle
          1 - I can see different DBID for the same and for some DBID it is showing different version. any problem during upgrade ( 10.2.0.3 -> 10.2.0.5 - one year back) and (10.2.0.5 to 11.2.0.3) ?

          If any problem, please let me know what I can do to fix.
          What do you mean by "any problem during upgrade"?
          2 - For OLAP details :

          SQL> select DBID,NAME,VERSION,DETECTED_USAGES,CURRENTLY_USED from dba_feature_usage_statistics where NAME like 'OLAP%';

          DBID NAME VERSION DETECTED_USAGES CURRE
          ---------- ---------------------------------------------------------------- ----------------- --------------- -----
          103678515 OLAP - Analytic Workspaces 10.2.0.3.0 143 TRUE
          103678515 OLAP - Analytic Workspaces 10.2.0.5.0 25 TRUE
          3874453699 OLAP - Analytic Workspaces 10.2.0.5.0 1 TRUE
          3874995160 OLAP - Analytic Workspaces 10.2.0.5.0 1 TRUE
          3874995160 OLAP - Analytic Workspaces 11.2.0.3.0 1 TRUE
          103678515 OLAP - Cubes 10.2.0.3.0 0 FALSE
          103678515 OLAP - Cubes 10.2.0.5.0 0 FALSE
          3874453699 OLAP - Cubes 10.2.0.5.0 0 FALSE
          3874995160 OLAP - Cubes 10.2.0.5.0 0 FALSE
          3874995160 OLAP - Cubes 11.2.0.3.0 0 FALSE

          Do I need to do anything for export olap AW ?
          Why to export OLAP AW? And is this output from prod or dev?
          3 - Remobe OLAP from 32 bit. After following the steps mentioned in note: 352306.1 am getting invalid objects with object name start with OLAP.

          SQL> select owner, object_name, object_type from dba_objects where status <> 'VALID';

          OWNER OBJECT_NAME OBJECT_TYPE
          ------------------------------ ---------------------------------------- -------------------
          SYS OLAPIBOOTSTRAP FUNCTION
          SYS OLAPIHANDSHAKE FUNCTION
          PUBLIC OLAPIBOOTSTRAP SYNONYM
          PUBLIC OLAPIHANDSHAKE SYNONYM

          Do I need to remove these objects manually?
          Yes. Please see (Invalid OLAPIBOOTSTRAP, OLAPIHANDSHAKE, GENINTERRUPTABLEINTERFACE Objects After Removing Olap from 11g [ID 1377048.1]) for details.

          Thanks,
          Hussein
          • 2. Re: OLAP and version on EBS database upgrade and migration
            953987
            Thanks Hussein,


            1 - What do you mean by "any problem during upgrade"?

            By seeing the out put, I really got confused. The out is an expected one for that query ?
            In production it is fine. So during the clone new controlfile created and I hv done 2 times.

            So this fine for me and thanks to open my eyes.



            2 - Why to export OLAP AW ?And is this output from prod or dev?

            This is from DEV and am igrating EBS from 32 bit to 64 bit and follwoing the note:471566.1
            Step 2 - Export OLAP AW (456347.1) is directing to a note : 352306.1 to perform the export OLAP AW steps.

            To clarify whether do I need to perform the same based on the output for the query - select DBID,NAME,VERSION,DETECTED_USAGES,CURRENTLY_USED from dba_feature_usage_statistics where NAME like 'OLAP%';



            Thanks,