Wednesday, February 25, 2015

DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled After Upgrading to 11.2.0.4 Standard Edition

"DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled" message could be seen while using export data dump and import data pump after upgrading a 11.2.0.3 standard edition database to 11.2.0.4 standard edition.
expdp system/testupg directory=DATA_PUMP_DIR dumpfile=sct.dmp logfile=sct.log schemas=scott

Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** directory=DATA_PUMP_DIR dumpfile=sct.dmp logfile=sct.log schemas=scott
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled
>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled
. . exported "SCOTT"."DEPT"                              5.929 KB       4 rows
. . exported "SCOTT"."EMP"                               8.562 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /opt/app/oracle/admin/testupg/dpdump/sct.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Jan 28 13:00:10 2015 elapsed 0 00:00:09
The main reason for this is the OLAP components are not enabled for SE but could get installed if a database template is used (eg. online transaction processing template) and becomes invalid after the upgrade. A similar problem was observed earlier with regard to dbms_cube_exp.schema_info_imp_beg error.
MOS note 1638799.1 these messages are intentionally output for 11.2.0.4 SE1 and SE and could be safely ignored. But if it's desired not to have the message output then the package could be deleted with (take a full database backup before deleting)
delete from sys.exppkgact$ where package = 'DBMS_AW_EXP' and schema= 'SYS';
commit;
@?/rdbms/admin/utlrp
This is mentioned in other MOS notes (1921158.1,726637.1,1675617.1) though not directly related to this warning message. After the delete the expdp and impdp doesn't result in any warning messages
expdp system/testupg directory=DATA_PUMP_DIR dumpfile=sct.dmp logfile=sct.log schemas=scott

Export: Release 11.2.0.4.0 - Production on Wed Jan 28 13:02:55 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** directory=DATA_PUMP_DIR dumpfile=sct.dmp logfile=sct.log schemas=scott
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."DEPT"                              5.929 KB       4 rows
. . exported "SCOTT"."EMP"                               8.562 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /opt/app/oracle/admin/testupg/dpdump/sct.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Jan 28 13:03:01 2015 elapsed 0 00:00:06

impdp system/testupg directory=DATA_PUMP_DIR dumpfile=sct.dmp logfile=sct.log schemas=scott

Import: Release 11.2.0.4.0 - Production on Wed Jan 28 13:05:31 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** directory=DATA_PUMP_DIR dumpfile=sct.dmp logfile=sct.log schemas=scott
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SCOTT" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."DEPT"                              5.929 KB       4 rows
. . imported "SCOTT"."EMP"                               8.562 KB      14 rows
. . imported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . imported "SCOTT"."BONUS"                                 0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Wed Jan 28 13:05:33 2015 elapsed 0 00:00:02
At the same time it would also be useful in clearing all OLAP related invalid objects. Invalid objects could be identified with
select owner, object_name, object_type from dba_objects where status <> 'VALID' order by owner, object_type;
Since OLAP components are not installed in a SE home running the component removal scripts will fail
SQL> @?/olap/admin/catnoamd.sql
SP2-0310: unable to open file "/opt/app/oracle/product/11.2.0/dbhome_4/olap/admin/catnoamd.sql"
SQL> @?/olap/admin/olapidrp.plb
SP2-0310: unable to open file "/opt/app/oracle/product/11.2.0/dbhome_4/olap/admin/olapidrp.plb"
SQL> @?/olap/admin/catnoxoq.sql
SP2-0310: unable to open file "/opt/app/oracle/product/11.2.0/dbhome_4/olap/admin/catnoxoq.sql"
SQL> @?/olap/admin/catnoaps.sql
SP2-0310: unable to open file "/opt/app/oracle/product/11.2.0/dbhome_4/olap/admin/catnoaps.sql"
SQL> @?/olap/admin/cwm2drop.sql
SP2-0310: unable to open file "/opt/app/oracle/product/11.2.0/dbhome_4/olap/admin/cwm2drop.sql"
It is possible to copy in these files from a EE home and run to remove the OLAP components or manually drop the packages/synonyms and finally drop the OLAP user (refer 1593666.1 and 1900113.1 before dropping OLAP user).

Useful metalink notes
Datapump Export (expdp) Raises Warnings Like "DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled" [ID 1638799.1]
12.1 Export Gives EXP-8 ORA-29280 EXP-85 ORA-06512 "SYS.UTL_FILE" "SYS.DBMS_AW_EXP" [ID 1921158.1]
Traditional Export (EXP) and DataPump (expdp) Fail With ORA-4063: Package Body SYS.DBMS_AW_EXP Has Errors [ID 726637.1]
DBMS_AW_EXP: Ignoring APPS.ODPCODE During Schema Data Pump Export [ID 1675617.1]
How To Find Out If OLAP Is Being Used [ID 739032.1]
How To Remove or De-activate OLAP After Migrating From 9i To 10g or 11g [ID 467643.1]
How To Remove The OLAP Option In 10g And 11g [ID 332351.1]
CATNOAMD.SQL DOES NOT DROP OLAPSYS USER IN 11.2.0.4 [ID 1900113.1]
Invalid OLAPSYS Objects After Upgrading TO 12C [ID 1593666.1]
Removing Oracle OLAP from the Database does not Remove All OLAP Objects [ID 1060023.1]
Remove Invalid OLAP Objects From SYS And OLAPSYS Schemas [ID 565773.1]
OWB Repository Upgrade To 11.2.0.4 Does Not Upgrade The Locations [ID 1637271.1]
Upgrading Oracle Warehouse Builder 11.2 - How To Upgrade From OWB 11.2.0.x To OWB 11.2.0.y [ID 1225254.1]

Related Posts
ORA-39127: unexpected error from call to export_string :=SYS.DBMS_CUBE_EXP.SCHEMA_INFO_EXP while Exporting
dbms_cube_exp.schema_info_imp_beg error while importing with impdp