To Know Running Concurrent Requests

**************************************************ManjunathaReddy
set lines 180
set pages 1000
set verify off
undef spid
column req_id format 99999999999
column OPID format a10
column PPID format a8
column SPID format a8
column ST_CD format a1
column ph_cd format a1
column CNAME format a30
column event format a15
column user_name format a10
column program format a8
column serial# format 999999
column sid format 9999
column username format a8
select a.request_id "REQ_ID",a.oracle_process_id "OPID",a.os_process_id "PPID",
e.user_concurrent_program_name "CNAME",
f.user_name,a.status_code "ST_CD",a.phase_code "PH_CD", b.username,b.sid,
b.serial#,b.program,g.event,
to_char(a.ACTUAL_START_DATE,'MON-DD-HH-MI-SS') START_DATE,
to_char(a.ACTUAL_COMPLETION_DATE,'MON-DD-HH-MI-SS') COMPL_DATE
from apps.fnd_concurrent_requests a,(select c.username,c.sid,c.serial#,
                        c.program,d.spid from v$session c, v$process d
                        where c.paddr=d.addr) b,
                        apps.fnd_concurrent_programs_tl e,
                        apps.fnd_user f,
                        v$session_wait g
                        where a.oracle_process_id=b.spid
                        and a.concurrent_program_id=e.concurrent_program_id
                        and e.language='US'
                        and a.requested_by=f.user_id         
                        and b.sid=g.sid
                                                and a.status_code='R'
                                                and a.phase_code ='R';
********************************************************ManjunathaReddy

To Change Schemas password & Change Site Name

sqlplus apps/apps
    set pages 0
    set lines 80
    spool schema_pwd.ksh
select 'FNDCPASS apps/<appspwd> 0 Y system/<systempwd> ORACLE ' ||
           replace(ORACLE_USERNAME,'$','\$') || ' ' || oracle_username
"CMD"
      from APPLSYS.FND_ORACLE_USERID
    where  READ_ONLY_FLAG = 'A'
      and  ORACLE_USERNAME in (select USERNAME from SYS.DBA_USERS)
/
    spool off
    exit
***********************************************************
Change Site Name:
a).To find the SiteName:
select V.PROFILE_OPTION_VALUE from FND_PROFILE_OPTION_VALUES V
WHERE V.PROFILE_OPTION_ID = (SELECT O.PROFILE_OPTION_ID FROM
FND_PROFILE_OPTIONS_VL O WHERE O.PROFILE_OPTION_ID=V.PROFILE_OPTION_ID
AND V.APPLICATION_ID=O.APPLICATION_ID AND
O.PROFILE_OPTION_NAME='SITENAME' )

b).To Update the SiteName:

UPDATE FND_PROFILE_OPTION_VALUES V
set V.PROFILE_OPTION_VALUE='SUPT - Support Instance - Cloned from
&backup_date Production Backup'
WHERE V.PROFILE_OPTION_ID = (SELECT O.PROFILE_OPTION_ID FROM
FND_PROFILE_OPTIONS_VL O WHERE O.PROFILE_OPTION_ID=V.PROFILE_OPTION_ID
AND V.APPLICATION_ID=O.APPLICATION_ID AND
O.PROFILE_OPTION_NAME='SITENAME' )
/
commit;
/
********************************************************************

which sql is running on background

SELECT a.username, b.sql_text FROM v$session a, v$sqlarea b, v$process c
WHERE c.spid = '&PID' AND a.paddr = c.addr AND a.sql_address = b.address;

To Find Top List Segments Usage...

Total list
========
select SEGMENT_NAME "Table Name",sum(BLOCKS)  "Total blocks" ,
sum(bytes/1024/1024) "Size in MB" from dba_segments
where segment_name like 'MLOG$%'
group by segment_name
 order by 2 desc;

Top-20 list
=========
select * from (
select SEGMENT_NAME "Table Name",sum(BLOCKS)  "Total blocks" ,
sum(bytes/1024/1024) "Size in MB" from dba_segments
where segment_name like 'MLOG$%'
group by segment_name
 order by 2 desc)
where rownum < 20

TOP SQLs & TableSpace Free

TOP sqls :

set linesize 1000

set pagesize 1000

col sql for a50

SELECT sql,buffer_gets, executions

FROM (SELECT hash_value,address,substr(sql_text,1,40) sql,

            buffer_gets, executions, buffer_gets/executions "Gets/Exec"

      FROM V$SQLAREA

      WHERE buffer_gets > 100000 AND executions > 10

      ORDER BY buffer_gets DESC)

      WHERE rownum <= 5

/

Tablespace Free and Fragmentation [ID 1019709.6]

select
  total.tablespace_name                       tsname,
  count(free.bytes)                           nfrags,
  nvl(max(free.bytes)/1024,0)                 mxfrag,
  total.bytes/1024                            totsiz,
  nvl(sum(free.bytes)/1024,0)                 avasiz,
  (1-nvl(sum(free.bytes),0)/total.bytes)*100  pctusd
from
  dba_data_files  total,
  dba_free_space  free
where
  total.tablespace_name = free.tablespace_name(+)
  and total.file_id=free.file_id(+)
group by
  total.tablespace_name,
  total.bytes
/

set linesize 1000

set pagesize 1000

OPatch apply & rollback

1.Stop all services...on both APPS & DB node.......

2.export PATH=$ORACLE_HOME/OPatch

3.Above is mandatory...Before applying patch check PATH should points to opatch directory location.....

4.opatch lsinventory -invPtrLoc /u01/app/oracle/db/tech_st/11.1.0/oraInst.loc |grep 8940108(patch number)

Above command shows whether the patch is applied or not.

5.unzip the patch...

6.shutdown the database...

7.goto patch directory location.....

8.fire the below command to apply the patch....(go to patch directory)

opatch apply -invPtrLoc /u01/app/oracle/db/tech_st/11.1.0/oraInst.loc.

Once the patch is applied successfully on database...

Startup the database....

Start the services

Patch is already … Download the new version of patch rollback patch and apply new version patch:

Oracle_home/inventory/oneoffs/<patch_no>/  opatch rollback  –id <pch_no> -invPtrLoc /u01/app/oracle/db/tech_st/11.1.0/oraInst.loc

---------------------------------------------------------------------------> ManjunathaReddy

To create inventory

./runInstaller -silent -attachHome -invPtrLoc /etc/oraInst.loc ORACLE_HOME="/u01/app/oracle/db/tech_st/11.1.0" ORACLE_HOME_NAME="oracleHome" CLUSTER_NODES="{}"