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:09The 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/utlrpThis 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:02At 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
