I got one request to export entire R12 (12.1.1) database. I thought it going to be easiest task for me, but it turn to be most challenging for me. This is how proceed:
1. Create Directory in database:
create directory SUNEXP as '/sundeep/exp';
2. Grant read, write on SUNEXP to DBA role, as I want to start expdp as system.
grant read,write on directory EXPDIR to "DBA";
3. Ran expdp full export:
expdp system/password DIRECTORY=SUNEXP FULL=Y COMPRESSION=ALL DUMPFILE=exp_sundeep.dmp LOGFILE=exp_sundeep.log
I thought I am done and it will gona take its own sweet time. But it failed at very first step:
----------------------------------------------------------------------------------
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS []
ORA-31642: the following SQL statement fails:
BEGIN "SYS"."DBMS_AW_EXP".SCHEMA_CALLOUT(:1,0,1,'11.01.00.00.00'); END;
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 923
ORA-33272: Analytic workspace ZPB.ZPBANNOT cannot be opened.
ORA-00600: internal error code, arguments: [xschkmk:!dict], [], [], [], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.KUPW$WORKER", line 7834
---------------------------------------------------------------------------------------
Now the issue is from where to start troubleshooting it. Then I thought of running this as normal exp command:
exp system/password FULL=Y FILE=exp_sundeep.dmp LOG=exp.log statistics=none feedback=1000000 buffer=2000000 compress=Y FILESIZE=4294967296 resumable=N
It started gracefully and end was not that graceful and I got the same error:
----------------------------------------------------------------------------------------
. exporting post-schema procedural objects and actions
EXP-00008: ORACLE error 33272 encountered
ORA-33272: Analytic workspace ZPB.ZPBANNOT 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 933
ORA-06512: at "SYS.DBMS_AW", line 1295
ORA-06512: at "SYS.DBMS_AW_EXP", line 575
ORA-06512: at "SYS.DBMS_AW_EXP", line 697
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_AW_EXP.schema_info_exp
. exporting user history table
. exporting default and system auditing options
. exporting statistics
Export terminated successfully with warnings.
---------------------------------------------------------------------------------------
Yup now there seems to be some issue. I focused on "ORA-33272: Analytic workspace ZPB.ZPBANNOT cannot be opened" which is my key words to start working on this issue.
SQL> select aw_name, aw_version from all_aws where owner='ZPB';
AW_NAME AW_V
------------------------------ ----
ZPBANNOT 9.1
ZPBCODE 10.2
ZPBDATA 9.1
See here was the issue, ZPBANNOT was 9.1 version and my db was 11.1. I thought of export is alone and delete it so that I can proceed and once done I can import it back. Following are the command do the same:
set serveroutput on
exec dbms_aw.execute('aw attach ZPB.ZPBANNOT');
exec dbms_aw.execute('limit name to all');
exec dbms_aw.execute('allstat');
exec dbms_aw.execute('export all to eif file ''SUNEXP/ZPB.ZPBANNOT_old.eif'' ');
When I ran first command it failed:
set serverout on
SQL> exec dbms_aw.execute('aw attach ZPB.ZPBANNOT RW');
exec dbms_aw.execute('aw attach ZPB.ZPBANNOT RW')
*
ERROR at line 1:
ORA-33272: Analytic workspace ZPB.ZPBANNOT cannot be opened.
ORA-00600: internal error code, arguments: [xschkmk:!dict], [], [], [], [], [],[], [], [], [], [], []ORA-06512: at "SYS.DBMS_AW", line 93
ORA-06512: at "SYS.DBMS_AW", line 122
----------------------------------------------------------------------------------------
One of the note says ZPBDATA or ZPBANNOT are obsolete objects and we can delete them.
I did this to delete them
set serveroutput on
exec dbms_aw.execute('aw delete zpb.zpbdata')
exec dbms_aw.execute('aw delete zpb.zpbannot')
Then started the same expdp command which I used earlier and this time it worked. It looks really simple but it ate my 1.5 days to resolve this issue.
Happy Troubleshooting !!!
