SQL> select instance_name,status,to_char(startup_time,'dd Mon, yyyy hh24:mi:ss') from gv$instance;
###########Script to get locks information########
column sess format a8 word_wrapped
column id1 format 999999999999
column id2 format 99999999
column req format 999
column type format a4
column "Module" format a30 word_Wrapped
SELECT lpad('-->',DECODE(request,0,0,5),' ')||sid sess
, id1
, id2
, lmode
, ctime
, request req, type --, sanj_sessf(sid) "Module"
FROM V$LOCK
WHERE id1 IN (SELECT id1 FROM gV$LOCK WHERE lmode = 0)
ORDER BY id1,request;
###########Blocking sessions######
column blocker format a25
column blockee format a25
column sid_serial format a10
select
(select username || ' - ' || osuser from v$session where sid=a.sid) blocker,
a.sid || ', ' ||
(select serial# from v$session where sid=a.sid) sid_serial,
' is blocking ',
(select username || ' - ' || osuser from v$session where sid=b.sid) blockee,
b.sid || ', ' ||
(select serial# from v$session where sid=b.sid) sid_serial
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;
###########Scripts to get locks in RAC env ######################
SELECT DECODE(request,0,'Holder: ','Waiter: ') ||
inst_id,sid sess, id1, id2, lmode, request, type,addr
FROM GV$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM gV$LOCK WHERE request > 0)
ORDER BY id1, request;
SQL> select sql_text from v$sqltext where HASH_VALUE = (select SQL_HASH_VALUE from v$session where sid = &n);
SQL> select sid,serial#,status,action,module,LAST_CALL_ET,WAIT_TIME,PROCESS from v$session where sid = &n;
###Steps to verify DR Synch###
select sequence# - 1
from v$log
where status = 'CURRENT';
select sequence#, applied,to_char(first_time, 'mm/dd/yy hh24:mi:ss') first from v$archived_log order by first_time;
SQL> select * from (select SEQUENCE#,FIRST_TIME,APPLIED from v$archived_log order by SEQUENCE# desc) where rownum <=150;
#####Script for Nodes Information#############
spool fnd_nodes
set pagesize 50
col node_name format a15
col server_id format a9
col server_address format a15
col platform_code format a4
col webhost format a12
col domain format a18
col virtual_ip format a12
set linesize 132
select
node_id,
platform_code,
support_db D,
support_cp C,
support_admin A,
support_forms F,
support_web W,
node_name,
server_id,
server_address,
domain,
webhost,
virtual_ip
from
fnd_nodes
order by node_id;
###############Invalids##############
select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type;
col object_name for a30
col owner for a10
select object_name,owner,object_type,status
from DBA_OBJECTS where status like 'INVALID%'
order by object_name;
select owner, object_name,object_type from dba_objects where status ='INVALID';
