Wednesday, November 12, 2008

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', .......);
for all_user in cur1 loop
end loop;

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