Oracle OLAP Component Reconstruction

 

1. Oracle OLAP Component Description

For a description of all Oracle components, refer:

Oracle8i/9i/10g/11g component (components) Description

Http://blog.csdn.net/tianlesoftware/article/details/5937382

OLAP (on-line analytical processing), also known as DSS decision support system. OLAP is the main application of the data warehouse system. It supports complex analysis operations, focuses on decision support, and provides intuitive and easy-to-understand query results.

OLAP is a multidimensional analysis engine for Oracle Database Enterprise Edition. It uses Oracle databases to store OLAP data and metadata.

In OLAP, the execution of a statement is not an assessment standard, because the execution time of a statement may be very long, and a large amount of data is read. Therefore, in such a system, the evaluation standard is usually the throughput (bandwidth) of the disk subsystem, such as the amount of Mbit/s of traffic.

 

For more information, see:

Introduction to oracleolap and OLTP

Http://blog.csdn.net/tianlesoftware/article/details/5794844

 

When the OLAP component is added, if we perform a 32-64-bit dB migration, we will need to recreate the OLAP component. Otherwise, a large number of invalid objects will exist. This reconstruction takes nearly 40 minutes, so it is a very time-consuming process.

 

Oracle OLAP is an optional component of Oracle Enterprise Edition. Since the OLAP engine is fully integrated into the Oracle database, all data and metadata are stored and managed within the Oracle database, to provide a highly scalable, robust management environment and industrial availability and security.

 

1.1 OLAP Components

You can use the following SQL statement to view the status and version of the OLAP component:

SQL> column comp_name format A35

SQL> Col version for A15

SQL> set wrap off

-- Oracle11g:

SQL> select comp_name, version, statusfrom dba_registry where comp_name like '% OLAP % ';

 

Comp_name version status

------------------------------------------------------------------------

OLAP analytic workspace 11.2.0.3.0 invalid

Oracle olap api 11.2.0.3.0 valid

OLAP catalog 11.2.0.3.0 valid

 

-- Oracle 10g:

Sys @ wangou> select comp_name, version, status from dba_registry where comp_name like '% OLAP % ';

 

Comp_name version status

------------------------------------------------------------------------

OLAP analytic workspace 10.2.0.5.0 valid

Oracle olap api 10.2.0.5.0 valid

OLAP catalog 10.2.0.5.0 valid

 

 

Oracle OLAP mainly includes the following components:

(1) OLAP analytic Engine

Oracle's OLAP analysis engine is a multi-dimensional model-based molap engine running in the Oracle kernel, so it has good performance.

(2) analytic Workspace

Analyze the data that actually stores the multi-dimensional model in the workspace. An Analytic workspace is stored as a relational table, and different objects in the analytic workspace are stored as a row in the table (in the lob format ). The analytic workspace can even be stored in a partitioned table to provide better concurrent performance.

(3) OLAP DML

Olap dml is the original operating language of analytic workspace, including the Data Definition Language (DDL) and data operation language (DML) about analytic workspace ). All the operations on analytic workspace, such as GUI tools, Java and SQL, must be converted to the Oracle DML language.

(4) SQL interface to OLAP

Provides an interface for using SQL to operate analyticworkspace, which is implemented using PL/SQL.

(5) analytic workspace Java API

Provides an interface for using Java to operate analyticworkspace. This interface is used in the analytic Workspace Manager GUI tool.

(6) OLAP API

An Oracle OLAP Java programming interface that supports oraclebi beans.

 

1.2 Two Oracle OLAP client tools:

(1) analytic Workspace Manager

This is a graphical tool for Oracle to operate analyticworkspace. This tool can be used to quickly complete tasks such as defining the logical multi-dimensional model of data, creating ing from multi-dimensional data to relational data, loading and aggregating data.

(2) OLAP Worksheet

OLAP worksheet provides an interactive environment for operating analytic workspace. It is similar to the sqlplus tool for Oracle databases.

 

1.3 active catalog view (Active Directory view)

Active catalogview is a series of dynamic views provided by Oracle for OLAP users. It is used to provide object information in the analytic workspace. Analytic workspace is an interface required for user session interaction with the Oracle OLAP engine. It is also a container for dimension and cube objects.

 

In Oracle OLAP, the analytic workspace concept is similar to the schema in the database and is also a set of related objects. However, analytic workspace uses a table named aw $ name to actually store objects (dimension, cube, level, attribute, etc.) in analyticworkspace ). Sometimes, we may need to migrate an analytic workspace from one system to another, or migrate the content of an analytic workspace to another analytic workspace, you can use analytic Workspace Manager to complete the task in the graphic interface. First, export an AW as an EIF file, and then import the EIF file to another aw.

 

Some main objects in the OLAP multi-dimensional model, such as dimension, measure, cube, hierarchy, level, and attribute) there is a corresponding Active Directory view. The content in the view can be determined based on the view name. All the Oracle10g Active Directory views start with all_olap2_aw:

(1) all_olap2_aws

(2) all_olap2_aw_attributes

(3) all_olap2_aw_catalogs

(4) all_olap2_aw_catalog_measures

(5) all_olap2_aw_cubes

(6) all_olap2_aw_cube_agg_lvl

(7) all_olap2_aw_cube_agg_meas

(8) all_olap2_aw_agg_op

(9) all_olap2_aw_cube_agg_specs

(10) all_olap2_aw_cube_dim_uses

(11) all_olap2_aw_cube_measures

(12) all_olap2_aw_dimensions

(13) all_olap2_aw_dim_hier_lvl_ord

(14) all_olap2_aw_dim_levels

(15) all_olap2_aw_phys_obj

(16) all_olap2_aw_phys_obj_prop

 

 

In addition to the Active Directory view, Oracle also provides several dynamic performance views to display some statistics of OLAP objects. These dynamicperformance views start with V $ AW:

(1) V $ aw_aggregate_op

(2) V $ aw_allocate_op

(3) V $ aw_calc

(4) V $ aw_longops

(5) V $ aw_olap

(6) V $ aw_session_info

 

The above theoretical knowledge is transferred from ningoo's blog:

Http://www.ningoo.net/html/2007/introduction_of_oracle_olap_component.html

 

2. add and delete OLAP Components

Mos: how to remove or to reinstall theolapoption to 10g and 11g [ID 332351.1]

 

Two solutions are provided here:

 

2.1 At ORACLE_HOME level reconstruction 2.1.1 Delete OLAP

Back up DB and orainventory, disable all instances on ORACLE_HOME, and use the universal Installer (Oui) to remove the OLAP options of ORACLE_HOME. In the oui, select an existing database to upgrade. In the selectoptions option, you can control whether to enable OLAP. For example:

 

On UNIX and Linux platforms, you can also directly relink the olap_off method to delete it, thus avoiding the use of OUI.

CD $ ORACLE_HOME/rdbms/lib

Make-F ins_rdbms.mk olap_off
Make-F ins_rdbms.mk ioracle

 

Non-registered OLAP instances are not restricted by DB banner, and V $ option is displayed as false.

 

2.1.2 reinstall OLAP

1) Add OLAP components in oui. For more information, see.

2) execute the following script after adding the database.

Sqlplus/nolog
SQL> Conn/As sysdba
SQL> spool add_olap.log
SQL> @? /OLAP/admin/OLAP. SQL sysaux temp;
SQL> @? /Rdbms/admin/utlrp. SQL
SQL> spool off

 

2.2 database-level reconstruction 2.2.1 Delete OLAP

-- Open a session on dbserver, SET related variables, and run the following script with SYS:

ORACLE_HOME = your_oracle_home
Oracle_sid = your_db_sid
Path = $ ORACLE_HOME/bin: $ path
Sqlplus/nolog
SQL> Conn/As sysdba

----> 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
SQL> @? /OLAP/admin/cwm2drop. SQL

----> Recompile invalids
SQL> @? /Rdbms/admin/utlrp. SQL

 

Catnoamd. sqlusedabove will drop the olapsys Schema (which is completely OLAP specific ).

-- Catnoamd. SQL script is used to drop olapsys users.

 

Cwm2drop. sqlneedsto be run only in 11g. In 10g, catnoamd. SQL already callit.

-- Cwm2drop. SQL only needs to be executed in 11 GB. In 10 Gb, The catnoamd. SQL script already contains this script, so you do not need to execute it.

 

If you connect to the database in sqlplus, the banner still shows the olapoption, this happensbecause the oracle executable is still aware of OLAP, it islinked with OLAP, andalso the OLAP files are still present in the ORACLE_HOME.

 

Once runningthedeinstall scripts, the utlrp. SQL cocould Report invalid objects with sysandpublic owners. If so, the following SQL can be used to report more detailsonthat:

-- Some invalid objects may be deleted. You can use the following SQL statement to view the specific information:

 

SQL> select owner, object_name, object_type, status from dba_objects where status = 'invalid ';

 

You cocould getsome invalid objects under sys and public owner, and they are the oldduplicate olapsys objects copied under these schemas when OLAP has beeninstalledpreviusly.

Generally, the OLAP objects are named with context like % OLAP %, % AWM %, or other OLAP wordkeys intheir "object_name" field, however, if it needs a help torecognizethem, then please contact Oracle Support and create a servicerequest toget assurance for this question.

 

Notethat catnoadm. SQL cocould fail from 10.1.0.5 to 11.1.0.7 release.

 

Due to the fact that it refers to three scripts which don't get shipped until 11.2 this scriptwill fail.

Besides that it will error on 7 non-existing synonyms to drop.

 

Prior 11.2, execute thesethree drop synonym statements:

-- Run the following script before 11.2:

SQL> drop public synonym olapfactview;
SQL> drop public synonym olapdimview;
SQL> drop public synonym dbms_odm;

 

If therearefurther invalid OLAP specific objects after the above removal steps, pleaseconsult note1060023.1, note565773.1

If you do notwant to install OLAP into a newly created database, then in thedatabaseconfiguration Assistant (dbca), select a custom database template anduncheckoracle OLAP in the database components wizard step.

 

2.2.2 add OLAP

Assuming that youcreated your database manually or via dbca, add the OLAP option to anexistingenterprise edition database.

-- Assume that we have added OLAP at the ORACLE_HOME level.

 

Then openaterminal Session on the database server machine, set ORACLE_HOME, oracle_sidenvironment variables, and execute some script as user SYS as sysdba, detailsbelow:

-- Run the following script on dbserver to create OLAP.

 

ORACLE_HOME = your_oracle_home
Oracle_sid = your_db_sid
Path = $ ORACLE_HOME/bin: $ path
Sqlplus/nolog
SQL> Conn/As sysdba
SQL> @? /OLAP/admin/OLAP. SQL sysaux temp;

-- This script takes over 30 minutes.

 

3. Verify invalid objects and component Status

 

SQL> select count (*) from dba_invalid_objects;

 

Count (*)

----------

67

 

Use the following script to compile an invalid object:

SQL> @? /Rdbms/admin/utlrp. SQL

 

SQL> Col comp_id for A15

SQL> Col version for A15

SQL> Col comp_name for A30

SQL> select comp_id, comp_name, version, status from dba_registry;

 

Comp_id comp_name version status

-----------------------------------------------------------------------------

Em Oracle Enterprise Manager 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 invalid

XDB Oracle XML database 11.2.0.3.0 invalid

Context Oracle Text 11.2.0.3.0 valid

ODM Oracle Data Mining 11.2.0.3.0 valid

Exf Oracle Expression Filter 11.2.0.3.0 valid

RUL Oracle Rules Manager 11.2.0.3.0 valid

Owm Oracle Workspace Manager 11.2.0.3.0 valid

Catalog Oracle Database catalog views 11.2.0.3.0 valid

 

Comp_id comp_name version status

-----------------------------------------------------------------------------

Catproc Oracle database packages and t11.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 invalid

Xoq Oracle olap api 11.2.0.3.0 valid

 

17 rows selected.

 

 

Related documents:

Dba_registry shows Oracle OLAP componentsin status removed [ID 1064322.1]

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!

Skype: tianlesoftware

Email: tianlesoftware@gmail.com

Blog: http://www.tianlesoftware.com

WEAVER: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

LinkedIn: http://cn.linkedin.com/in/tianlesoftware

 

 

------- Add a group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, reject the application ----

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 dba5 group: 142216823

Dba6 group: 158654907 dba7 group: 172855474 DBA group: 104207940