apps dba interview questions

Few good links to find Oracle Apps DBA Interview Questions

http://onlineappsdba.com/index.php/category/interviewqs/

http://teachMeOracle.com/interview.html

Good Blogs about Apps DBA

Here are some good blog sites:

Very Good blog from Atul

http://www.teachmeoracle.com/
http://onlineappsdba.com/


Good Blog from Singapore by Madhu

http://appsdbablog.blogspot.com/

Printing in Oracle apps 11i from command line (ar60run):

Printing in Oracle apps 11i from command line (ar60run):

Here are the steps to be followed.
First of all you must be able to print form Linex using lp print command.


1.
chang to $APPL_TOP and source the evvironment by:

cd /d00/oracle/prodappl/

. ./SIIX_ebsiprd.evn
. ./APPSORA.env



2.

The PRINTER environment variable needs to be set. Check this by typing:

echo $PRINTER

If echo $PRINTER returns blank, you need to set a default printer,
by typing:

In Linux PRINTER=(valid printer name)

for example:

PRINTER=xerox-new-235


here xerox-new-235 is the OS name of my printer.
please check with your system administrator for the name of your printer


3.

cd /d00/oracle/siixora/8.0.6/reports60/admin/printer/
change permissions to

chmod 766 spoolcmd.sh
after change permissions

-rwxrw-rw- 1 oracle oracle 866 May 15 1999 spoolcmd.sh


4.
Now try Printing form Command line.

cd to the directory where your rdf file is located.
Complete path must be entered, if you are not in the directory where your rdf are located.

ar60run userid=apps/****** report=SEI-INVMOV8.rdf batch=yes destype=printer desname=xerox-new-235 desformat=wide180.prt errfile=test.log


in the above desname is the name of the printer as i mentioned above.

WORKFLOW PURGE PROGRAM DOES NOT REMOVE RECORDS FROM WF TABLES EVEN THOUGH SCHEDULED TO RUN DAILY

The data in workflow tables become huge. as explained below millions of records are accumulated. Due to this system become slow. users keep complaining.
Then i made a check on total number of records in each table related workflow.
Here i found WF tables like wf_item_attribute_values have more than 35 million records.

I run the below query to find out the number records item_type wise.

select item_type,count(*) from wf_item_attribute_values group by item_type;

ITEM_TYP COUNT(*)-------- ----------
AZNF005 18
CREATEPO 31536
MRPEXPWF 33636955
OECHGORD 21
OEOH 33969
OEOL 414430
P2P 14
POAPPRV 1956716
POERROR 217
POXML 396606
RCVDMEMO 3640
WFERROR 450223
WIPISHPW 31281

13 rows selected.

Frow the above queries it has been identified that the item_type "MRPEXPWF" is having 33.6 million records in wf_item_attribute_values table.
All these records are not purged by "Purge Absolete workflow Runtime Data" program even though we sheduled it run daily.
As explained above in my case item_type "MRPEXPWF" have many records which are not purge by purge program, but in your case may be differnt item_type.
You can know from above query which item_type have many records which are not purged by scheduled purge program.
please try this in Test Instance first.
Don't directly try below purge query in Production instance.
It will effect to running work flows if you try without proper analysis.


Now the question is how to clear these records from wf_item_attribute_values table.here my main concern is to purge data related to item_type "MRPEXPWF" which is causing the performance problem in the system.


I run the following query on wf_items table to find out item_type count with end_date is null.Purge Absolete workflow Runtime Data program purges the obsolete data based on the END_DATE column value.In this case the END_DATE is null in WF_ITEMS table for many records, so Purge Program can't find any records to purge even though we scheduled it to run daily. All these record need to be purged using sql.

SQL> select item_type,count(*) from WF_ITEMS where end_date is null group by item_type;

ITEM_TYP COUNT(*)-------- ----------
MRPEXPWF 847714
OEOH 512
OEOL 900
POAPPRV 181
POERROR 5
RCVDMEMO 288
WFERROR 17919
WIPISHPW 367

From the above query there are 847714 records related to item_type "MRPEXPWF" with END_DATE null in WF_ITEMS table.

At first i deleted records from WF tables related to item_type "MRPEXPWF" belong to last year 2006 and below.

This decision will help in two ways:
1. it will delete only old wf records belong to 2006 and below related to "MRPEXPWF" item_type, we will be out of risk as these are not useful.
2. we can overcome rollback segemnt out of space problem as i am deleting more than 33 million records, i my self tried to delete all records belong "MRPEXPWF" item_type in test instance at one, but failed due to rollback segment out of space issue(i already increased to 6GB, still can't). so spliting like this will help you two ways.


Run the the following to get the list of values to be run execute the Query below

select 'exec WF_PURGE.ITEMS (''MRPEXPWF'','''ITEM_KEY''',SYSDATE,FALSE,TRUE);' fromWF_ITEMS where item_type='MRPEXPWF' and (begin_date <= '31-DEC-06') and end_date is null;

This output gives some sql statments like below. And these are to be run again in sqlplus.
For example the output of above query generate about 847714 exec statements as below.

exec WF_PURGE.ITEMS('MRPEXPWF','2292547',SYSDATE,FALSE,TRUE);

All these exec statements spool to a file and run in sqlplus to purge records related to item_type "MRPEXPWF".


This will delete records from various WF tables related to item_type "MRPEXPWF".

Welcome to world of blogging.

Hai,
This is Anthony Reddy currently working for SIIX Group as Apps DBA.
I welcome all to the world of blogging.

i will be sharing my experience and knowledge about oracle apps through this blog.

you can reach me at: anthonyreddy.thumma@gmail.com

for any technical help.


Regards,
Anthony Reddy