Thursday, December 31, 2009

Ten Great Tips For 2010

Ten Great Tips For 2010

1. Stay out of Trouble.

2. Aim for Greater Heights.

3. Stay Focused on your Job.

4. Exercise to Maintain Good Health.

5. Practice Team Work.

6. Rely on your Trusted Partner to Watch your Back. Take your Time Trusting Others.

7. Save for Rainy Days.

8. Rest and Relax.

9. Always Take Time to Smile.

AND

10. Realize That Nothing is Impossible.

Wishing You and Your Family A Very Happy, Prosperous and Healthy New Year 2010!

Tuesday, December 15, 2009

Export issue in 11g (11.1.0.7) with R12 (12.1.1)

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 !!!

Wednesday, December 9, 2009

OPP on Virtual Host

In one of my earlier post I have mentioned how to use Virtual host for Concurrent manager. In this post I am specifically talking about issue you might encounter while using report output as xml and it doesn't show the actual output rather it shows html tags. So just understand the concept how OPP is being used in xml reports and then look for solution.

An Application user submits an XML Publisher based report.
The Standard Concurrent Manager process the request.
The XML data file is generated by the standard concurrent manager.
This can be done by various methods:
Oracle Reports - Report Definition File (RDF)
XML Publisher Data Template - XML data template linked to the Data Definition
Any other process that produces XML output
A Post processing action defines that the output needs to be generated by the Output Post Processor hence it is triggered by the standard manager.
The Output Post Processor generates the final report and informs the standard Concurrent Manager whether that was successful.
The Standard Concurrent Manager finalizes the concurrent request.

Now the error in report:
----------------------------------
One or more post-processing actions failed. Consult the OPP service log for details.

Now the error in OPP log:
----------------------------------
oracle.apps.fnd.cp.util.RemoteFileException: An Error occured during file transmission in the FND File Server. The Applications File Server could not open the file 12345333 for read.

Now the resolution:
----------------------------------
Was worried why OPP is not able to read the file, we have Shared APPL_TOP too. Then though it might be due to virtual host. Then came across one note: 824887.1, it was like sure shot for me and it worked. There is a profile option "Concurrent: Force Local Output File Mode" it was set to No I made it Yes and checked the failed request. Vola it worked as expected and my team mates were jumping. For more detail about this profile look at metalink note mentioned earlier.

Happy Troubleshooting !!!