Posts Tagged ‘Datapump error’

EBS — Datapump expdp error ORA-33272 , ZPB, DBMS_AW, dba_aws

January 31st, 2015, posted in Oracle Queries

EBS -- Datapump expdp error,EBS ,Datapump expdp error,Datapump error,ORA-33272 ,Oracle,Oracle 9i,Oracle 10g,Oracle 11g,Ora Error,Oracle Error,Error In Oracle Export

While exporting an EBS 11i database using datapump , you can get ORA-33272 error on Analytical Workspaces..
This errors are mostly due to the versions of aw objects..
Suppose you have upgraded your Ebs database from 9i to 10g and the AW objects belong to 9i version of Oracle Database have remained in your database..  In such a situation, you can encounter ORA-33272 error on your future data pump exports..

An example for these kind of errors are below;

ORA-39127: unexpected error from call to export_string :=SYS.DBMS_AW_EXP.schema_info_exp(‘ZPB’,1,1,’′,newblock)
ORA-33272: Analytic workspace ZPB.ZPBCODE cannot be opened.
ORA-00600: internal error code, arguments: [xschkmk:!dict], [], [], [], [], [], [], []
ORA-06512: at “SYS.DBMS_AW”, line 67
ORA-06512: at “SYS.DBMS_AW”, line 131
ORA-06512: at “SYS.DBMS_AW”, line 792
ORA-06512: at “SYS.DBMS_AW”, line 1142
ORA-06512: at “SYS.DBMS_AW_EXP”, line 517
ORA-06512: at line 1
ORA-06512: at “SYS.DB.

For the solution, use following query to list the version for the object in question;
select * from all_aws where owner=’ZPB’;  (ZPB is the schema name of failing objects in the example above)SQL> select * from dba_aws;
Example output:
———- ———- ——————– —- ———- ———– ——
ZPB 1000 ZPBCODE 9.1 292 2
SYS 129 AWREPORT 10.2 9 2
SYS 128 AWXML 10.2 64 2
ZPB 1001 ZPBANNOT 9.1 7 1
ZPB 1002 ZPBDATA 9.1 7 1
SYS 125 AWMD 10.2 363 2
SYS 124 EXPRESS 10.2 46 2
SYS 126 AWCREATE 10.2 21 2
SYS 127 AWCREATE10G 10.2 9 2
Analyze the output of the query and if you have older versions, delete them..
For the example above: the ZPB Aws are 9i and they are need to be deleted.

You can use dbms_aw.execute to do that..

exec dbms_aw.execute(‘aw delete zpb.zpbcode’);
exec dbms_aw.execute(‘aw delete zpb.zpbannot’);
exec dbms_aw.execute(‘aw delete zpb.zpbdata’);

Log in AS ZPB
SQL> set serverout on
SQL> call dbms_aw.execute(‘AW DELETE ZPBCODE’);
SQL> call dbms_aw.execute(‘AW DELETE ZPBANNOT’);
SQL> call dbms_aw.execute(‘AW DELETE ZPBDATA’);
If you receive errors deleting the AWs, drop the associated AW$ tables :
SQL> drop table AW$ZPBCODE;
SQL> drop table AW$ZPBANNOT;
SQL> drop table AW$ZPBDATA;select * from dba_aws;SYS 125 AWREPORT 9.1 9 2
SYS 124 AWXML 9.1 50 2
SYS 123 AWCREATE10G 9.1 9 2
SYS 122 AWCREATE 9.1 18 2
SYS 121 AWMD 9.1 205 2
SYS 120 EXPRESS 9.1 38 2

Also if you have all 9i ‘s in awr$ table, then you may be need to -> Remove OLAP, Clean up Left Over Objects And Add It back (Doc ID 1560841.1)
Note : Before applying any of this. Do understand your problem and concern with some-else as well.