Oracle Apps 11i/R12: Check locked tables by fnd_user

SELECT c.owner
      ,c.object_name
      ,c.object_type
      ,fu.user_name locking_fnd_user_name
      ,fl.start_time locking_fnd_user_login_time
      ,vs.module
      ,vs.machine
      ,vs.osuser
      ,vlocked.oracle_username
      ,vs.sid
      ,vp.pid
      ,vp.spid AS os_process
      ,vs.serial#
      ,vs.status
      ,vs.saddr
      ,vs.audsid
      ,vs.process
FROM fnd_logins      fl
    ,fnd_user        fu
    ,v$locked_object vlocked
    ,v$process       vp
    ,v$session       vs
    ,dba_objects     c
WHERE vs.sid = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
--AND c.object_name LIKE '%' || upper('&tab_name_leaveblank4all') || '%'
AND nvl(vs.status
      ,'XX') != 'KILLED';

Enable Archive Log Mode in Oracle RAC 10g Database

The following will be the steps:
1- Login to one of the clustered database nodes and change parameter cluster_database to false:
   sqlplus "/ as sysdba"
   alter system set cluster_database=false scope=spfile sid='racdb1'
  
2- Stop all database instances:
    srvctl stop database -d racdb

3- On any node startup one instance in mount state and enable archivelog mode:

   sqlplus "/ as sysdba"
   startup mount;
   alter database archivelog;


4- Shutdown the previous running node:
   sqlplus "/ as sysdba"
   shutdown immediate;

5- Start all instance back:

     srvctl start database -d racdb

To check the mode:

   sqlplus "/ as sysdba"
   archive log list;

or

 select log_mode from v$database;

Oracle RAC: Find the Master node at cluster level

ocrconfig -showbackup: (Note the first column which represents the Node name)
$ ocrconfig -showbackup
gfmisdb1     2011/02/21 12:44:43     /u01/app/oracle/product/10.2.0/crs/cdata/GFMIS
gfmisdb1     2011/02/21 08:44:43     /u01/app/oracle/product/10.2.0/crs/cdata/GFMIS
gfmisdb1     2011/02/21 04:44:43     /u01/app/oracle/product/10.2.0/crs/cdata/GFMIS
gfmisdb1     2011/02/20 12:44:42     /u01/app/oracle/product/10.2.0/crs/cdata/GFMIS
gfmisdb1     2011/02/09 00:44:33     /u01/app/oracle/product/10.2.0/crs/cdata/GFMIS

Script to monitor RMAN progress

select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 done,
sysdate + TIME_REMAINING/3600/24 end_at
from v$session_longops
where totalwork > sofar
AND opname NOT LIKE '%aggregate%'
AND opname like 'RMAN%'

Terminate rman hang process at Unix OS leve

To do this you have to get the process id first:
SELECT p.SPID ,CLIENT_INFO
FROM V$SESSION_WAIT sw, V$SESSION s, V$PROCESS p
WHERE sw.EVENT LIKE 'sbt%'
       AND s.SID=sw.SID
       AND s.PADDR=p.ADDR;

SPID EVENT        SEC_WAIT STATE                CLIENT_INFO
---- ---------- ---------- -------------------- -------------
8642 sbtwrite2         600 WAITING              rman channel=ORA_SBT_TAPE_1
8374 sbtwrite2         600 WAITING              rman channel=ORA_SBT_TAPE_2

Now we will kill the processes by:

kill -9 8642 8374

Convert Single instance database to RAC Database methods 10g

Three methods used to convert Single database to RAC database:
1- DBCA.
2- Enterprise Manager.
3- rconfig tool.

Oracle Apps R12: Check if the patch applied from sqlplus

Two tables to check if the patch is applied or not:

This table includes the defined bugs on the system:
SELECT   bug_number
FROM     apps.ad_bugs
WHERE   bug_number LIKE '%' ||3594604 || '%';

This table includes patches applied on the system:
SELECT patch_name
FROM   apps.ad_applied_patches
WHERE patch_name LIKE '%' ||3594604 || '%'