Happy New Year

You might be wondering where I have been for so long, Officially we were busy in our Dev server movement to some other location internally and at personal front we blessed with a Baby Boy on Christmas Eve (24th Dec). So was busy or would say too busy to look at this site. Today got some time so thought of sharing good news with you all and Wish Happy New Year to all of you out there. On Funny side :
"May all your troubles last as long as your New Year's resolutions."

Happy Troubleshooting !!!

What is significance of 0 & Y while using ( FNDCPASS, FNDLOAD, WFLOAD .....) .

Another in a row for Basics:

-'0' refers to the ( Concurrent Program ) request ID , as Invocation isNOT from the concurrent program SRS where in each Concurrent Request is assigneda non-zero id.

- 'Y' is the value for a flag that indicates the method of invocation is directly from the command-line not from the SRS.

Note: SRS (Standard Request Submission)
Happy Troubleshooting !!!

Meaning of Codes in FND_CONCURRENT_REQUESTS table?

Back to Basics, we use this daily and forget what they mean internally. so Today I thought of going back to Apps DBA Basics and giving meaning of the codes in the status_code and phase_code columns of the FND_CONCURRENT_REQUESTS table?

STATUS_CODE Column:
A Waiting
B Resuming
C Normal
D Cancelled
E Error
F Scheduled
G Warning
H On Hold
I Normal
M No Manager
Q Standby
R Normal
S Suspended
T Terminating
U Disabled
W Paused
X Terminated
Z Waiting

PHASE_CODE column.
C Completed
I Inactive
P Pending
R Running

Happy Troubleshooting !!!

Adding Datafile in Standby Database

We are having our DR/Standby database getting sync with Production instance. Whenever we add any datafile to Production we have to manually add datafile in Standby. Yup that's the process and we have to do that, but would like to share with you all what needs to be done step by step.:

1. Add a datafile in Production "alter tablespace TBS add datafile 'Path/file.dbf' size 2000M;"
2. Wait for log switch or do it.
3. Move the arch file to standby/DR location.
4. Recover standby "Recover database using backup controlfile until cancel;"
5. Above step will fail and give you one unamed file#. You can query the tablespace name etc like "select TB.NAME,DF.NAME from v$tablespace TB , v$datafile DF where TB.TS#=DF.TS# and DF.File#=345;"
6. Create datafile in Standby/DR instance with unamed file like "Alter database create datafile '/OH/dbs/UNNAMED00527' as '/Path/file.dbf';"
7. Recover standby/DR again "Recover database using backup controlfile until cancel;"

Happy Troubleshooting !!!

Updating FND_USER

We had some requirement to disable some good amount of users. So it will not be a wise idea to get into Oracle and end date each and every user. So next option is to update from backend. But our worry is whether should we just update end_date column for all users and forget. That raised a question about what if we have to enable them again, what will happend to WF table sync. Then we used FND_USER_PKG details provided in Metalink Note : 364898.1 and we use it in cursor and we go the desired result. Here is what we did :

declare cursor cur1 is
select user_name from apps.fnd_user where LOWER(user_name) IN ('username','username', .......);
begin
for all_user in cur1 loop
apps.fnd_user_pkg.DisableUser(all_user.user_name);
commit;
end loop;
End;

Happy Troubleshooting !!!
Note: Many Thanks to "hsawwan" for this.