CREATE A PHYSCIAL STANDBY DATABASE

 1). finding the sid with request id == enter 10102224

select f.request_id,p.spid B_Process,s.sid,s.serial#,s.process F_Process,s.program, u.description

 from v$session s, v$process p, apps.fnd_concurrent_requests f, apps.fnd_user u

 where f.oracle_process_id = p.spid

 and p.addr=s.paddr

 and f.request_id=&request_id

 and f.requested_by = u.user_id;

 2: Find sid status.

select username,osuser,status,sid,serial#,last_call_et/3600,machine,to_char(logon_time,'DD-MM-yyyy HH24:MI:ss') from v$session where sid=59

select phase_code, status_code from apps.fnd_concurrent_requests where request_id=10102224;

select phase_code, status_code from apps.fnd_concurrent_requests where request_id=10102226;

select username,osuser,status,sid,serial#,last_call_et/3600,machine,to_char(logon_time,'DD-MM-yyyy HH24:MI:ss') from v$session where sid in ('4472','17811','1172','6722','7809','6715');

select phase_code, status_code from apps.fnd_concurrent_requests where request_id=10253752;

3). Who launched this sid=Find the conc-request id====  Input require sid..?

select f.request_id,p.spid B_Process,s.sid,s.serial#,s.process F_Process,s.program, u.description

 from v$session s, v$process p, apps.fnd_concurrent_requests f, apps.fnd_user u

 where f.oracle_process_id = p.spid

 and p.addr=s.paddr

 and s.sid=&sid

 and f.requested_by = u.user_id

Step 4:  What sql statement is running 

select s.sid, s.serial#, s.program, a.sql_text,a.rows_processed

 from v$session s, v$sqlarea a

where s.sid = &1

  and a.address = s.sql_address

Step 5: for kill the session id

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#'

ex:  ALTER SYSTEM KILL SESSION '754,42933';

 6: Manullay updated the phase_code

SQL> update apps.fnd_concurrent_requests set phase_code='C',status_code='X' where request_id=87189956;

7). Oracle Apps -> Concurrent manager phase code status details

select request_id,phase_code,status_code from fnd_concurrent_requests where request_id='10083487'

PHASE CODE:

I = Inactive

P = Pending

R = Running

C = Completed

STATUS CODE:

U = Disabled

W = Paused

X = Terminated

Z = Waiting

M = No Manager

Q = Standby

R = Normal

S = Suspended

T = Terminating

D = Cancelled

E = Error

F = Scheduled

G = Warning

H = On Hold

I =  Normal

A = Waiting

B = Resuming

C = Normal

8). Script to recompile invalid objects 

Select decode( object_type, 'PACKAGE BODY', 'ALTER PACKAGE ' || OBJECT_NAME || ' COMPILE BODY;','ALTER ' || OBJECT_TYPE || ' ' || OBJECT_NAME || ' compile;' )from user_objects where status = 'INVALID' order by object_type

9). tablespace space status

set pages 50000 lines 32767

 col tablespace_name format a30

 col TABLESPACE_NAME heading "Tablespace|Name"

 col Allocated_size heading "Allocated|Size(GB)" form 99999999.99

 col Current_size heading "Current|Size(GB)" form 99999999.99

 col Used_size heading "Used|Size(GB)" form 99999999.99

 col Available_size heading "Available|Size(GB)" form 99999999.99

 col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99

 select a.tablespace_name

         ,a.alloc_size/1024/1024/1024 Allocated_size

         ,a.cur_size/1024/1024/1024 Current_Size

         ,(u.used+a.file_count*65536)/1024/1024/1024 Used_size

         ,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024/1024 Available_size

         ,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used

 from     dba_tablespaces t

         ,(select t1.tablespace_name

         ,nvl(sum(s.bytes),0) used

         from  dba_segments s

         ,dba_tablespaces t1

          where t1.tablespace_name=s.tablespace_name(+)

          group by t1.tablespace_name) u

         ,(select d.tablespace_name

         ,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size

         ,sum(d.bytes) cur_size

         ,count(*) file_count

         from dba_data_files d

         group by d.tablespace_name) a

 where t.tablespace_name=u.tablespace_name

 and t.tablespace_name=a.tablespace_name

 order by t.tablespace_name

 /

10).  particular table space size/disk location

col TABLESPACE_NAME for a30

col file_name for a95

set lines 350

select df.file_name,df.tablespace_name,df.bytes/1024/1024,file_id,vs.creation_time,df.autoextensible from dba_data_files df, v$datafile vs where df.file_id=vs.file# and df.tablespace_name in('UNDOTBS1') order by vs.creation_time desc;

11). add the space

alter tablespace APPS_TS_TX_DATA add datafile '+DG_DATA1' SIZE 10890M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

alter tablespace USERS add datafile '+SZ0682_D1557P_D1' SIZE 300M AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED; 

ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/RSBCOTME/TEMPFILE/temp.298.1030534075' resize 5000m;

alter database tempfile '+DATA/RSBCOTME/TEMPFILE/temp.298.1030534075' resize 5g;

ALTER TABLESPACE TEMP ADD TEMPFILE '+DATAC1' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 32767M;