RMAN session / rman longops

RMAN backup status, RMAN jobs, rman back list, Is rman running, rman session, rman process -- With time (RAC version) col "Sid Serial#" for a15 col "Logon time" for a20 col spid for a6 select b.sid||','||b.serial# as "Sid Serial#", a.inst_id, b.status, a.spid, to_char(b.logon_time, 'dd Mon yyyy, hh24:mi') as "Logon time", b.client_info from gv$process a, gv$session b where a.addr=b.paddr and a.inst_id = b.inst_id and lower(client_info) like '%rman%'; -- Basic and (Non-RAC) select b.sid, b.serial#, a.spid, b.client_info from v$process a, v$session b where a.addr=b.paddr and client_info like '%rman%';

Is database standby

Quick check to see if the instance is standby or primary? SQL> select database_role from v$database; DATABASE_ROLE ---------------- PHYSICAL STANDBY SQL> select controlfile_type from V$database; CONTROL ------- STANDBY Useful sql select to_char(max(next_time),'dd Mon yyyy hh24:mi:ss') from v$archived_log where applied = 'YES'; TO_CHAR(MAX(NEXT_TIME),'DDMON ----------------------------- 05 Mar 2019 07:24:02 alter session set nls_date_format='dd Mon yyyy hh24:mi:ss'; select * from ( SELECT FIRST_TIME, NEXT_TIME, SEQUENCE# FROM V$ARCHIVED_LOG ORDER BY SEQUENCE# desc) where rownum < 6 order by SEQUENCE#; FIRST_TIME NEXT_TIME SEQUENCE# ----------------------------- ----------------------------- ---------- 05 Feb 2019 06:39:02 05 Feb 2019 06:54:02 213003 05 Feb 2019 06:54:02 05 Feb 2019 07:09:02 213004 05 Feb 2019 07:09:02 05 Feb 2019 07:24:02 213005 05 Feb 2019 07:24:02 05 Feb 2019 07:39:02 213006 05 Feb 2019 07:39:02 05 Feb 2019 07:54:02 213007

Kill session

-- Current session alter system kill session '213,98643'; alter system kill session '777,56244,@1'; -- Kill session from another instance alter system kill session '66,64486,@2'; alter system kill session '66,64486' IMMEDIATE;

Schema Last analysed | Stale Stats

-- Summary of stale select stale_stats, count(*) from user_tab_statistics group by stale_stats; STA COUNT(*) --- ---------- 414 NO 2582 YES 199 select TABLE_NAME, STALE_STATS from user_tab_statistics where stale_stats = 'YES';

SQL for RAC db

RAC sql RAC Instances, DB instances, instance status, List of all instances, with startup time, up till, host load. col host_name for a16 select i.instance_number, i.host_name, i.instance_name, to_char(startup_time, 'DD Mon YYYY HH24:Mi') startup_time, floor(sysdate - startup_time) uptill, i.status, round(o.value, 3) value from gv$instance i, gv$osstat o where i.instance_number = o.inst_id and o.stat_name = 'LOAD' order by instance_number; -- e.g. INSTANCE_NUMBER HOST_NAME INSTANCE_NAME STARTUP_TIME UPTILL STATUS VALUE --------------- ---------------- ---------------- -------------------------- ---------- ------------ ---------- 1 proddbx00101 dex011 12 Feb 2018 02:52 247 OPEN 15.979 2 proddbx00102 dex012 12 Feb 2018 05:25 247 OPEN 18 3 proddbx00103 dex013 12 Feb 2018 07:23 247 OPEN 21.59 4 proddbx00104 dex014 12 Feb 2018 09:37 247 OPEN 19.079

Current Transaction for RAC set line 250 col "username_sid_inst" for a35 select s.username||'('||s.sid||', '||s.inst_id||')' username_sid_inst,s.sid,t.start_time, t.used_ublk as "Block Count", round(t.used_ublk*8192/1024/1024,2) Size_MB from gv$transaction t,gv$session s where s.saddr=t.ses_Addr and t.inst_id=s.inst_id order by start_time;

Session Wait for RAC set line 250 col username for a24 col event for a45 col 'FileId# Obj#' for a16 select s.inst_id, s.username, s.row_wait_file# ||', '|| s.ROW_WAIT_OBJ# as "FileId# Obj#", s.sql_hash_value, sw.event, sw.wait_time, sw.seconds_in_wait, sw.state from gv$session s, gv$session_wait sw where s.sid = sw.sid and s.inst_id = sw.inst_id and sw.wait_time != 0 order by s.inst_id, s.username;

Sessions connected for RAC -- Group by User (Summary) select s.username, count(*) from gv$session s group by s.username order by s.username; -- Group by Instance, User select s.inst_id, s.username, count(*) from gv$session s group by s.inst_id, s.username order by s.inst_id, s.username;

Locks (Holder/Waiter) for RAC column sess format A20 select substr(DECODE(request,0,'Holder: ','Waiter: ')||sid||' ('||inst_id||')',1,18) sess, id1, id2, lmode, request, type, to_char(to_date(ctime,'sssss'),' hh24:mi:ss') time_h_m_s from gv$lock where (id1, id2, type) IN (select id1, id2, type from gv$lock where request>0) order by id1, request, ctime desc;

PDB DBA user

create user c##dbareadonly identified by urdbapa55 container=all; grant create session to c##dbateam container=all; grant set container to c##dbateam container=all; grant select_catalog_role to c##dbateam container=all; alter user c##dbateam set container_data=all container = current; Above (last line is important, otherwise dba user can't select * from v$pdbs, can't select from pdb views)

Favorite SQL for session waits

Session waits col "FileId# Obj#" for a18 col event for a42 select s.inst_id, s.username, s.row_wait_file# ||', '|| s.ROW_WAIT_OBJ# as "FileId# Obj#", s.sql_hash_value, sw.event, sw.wait_time, sw.seconds_in_wait, sw.state from gv$session s, gv$session_wait sw where s.sid = sw.sid and s.inst_id = sw.inst_id and sw.wait_time != 0 order by s.inst_id, s.username Other related, or common sql -- Current Transaction col "username_sid" for a35 select s.username||'('||s.sid||')' username_sid,s.sid,t.start_time,t.used_ublk as "Block Count", round(t.used_ublk*8192/1024/1024,2) Size_MB from v$transaction t,v$session s where s.saddr=t.ses_Addr order by start_time; -- Long OPS select sid, serial#, context, sofar, totalwork, target, sql_hash_value, round(sofar/totalwork*100,2) "%_COMPLETE", opname, to_char(start_time, 'DD Mon YYYY - HH24:MI') start_time, round(time_remaining/60) tr, to_char(last_update_time, 'DD Mon YYYY - HH24:MI') last_update_time from v$session_longops where totalwork != 0 and sofar <> totalwork order by start_time; SQL from hash value, SQL hash set line 200 select * from v$sql where hash_value = 9999999; select sq.sql_text from v$sqltext_with_newlines sq where sq.hash_value = 9999999 order by sq.piece SELECT s.username, sl.sid, sq.executions, sl.last_update_time, sl.sql_id, sl.sql_hash_value, opname, target, elapsed_seconds, time_remaining, sq.sql_fulltext FROM v$session_longops sl INNER JOIN v$sql sq ON sq.sql_id = sl.sql_id INNER JOIN v$session s ON sl.SID = s.SID AND sl.serial# = s.serial# WHERE time_remaining > 0 SQL from pid / session from Process Id / Session Id set line 200 col schemaname for a20 col host_name for a20 col program for a16 col pprogram for a20 col machine for a20 col com for a10 -- PID version select p.spid, s.schemaname, s.sid, s.serial#, s.status, s.machine, s.program, p.program pprogram, s.sql_hash_value, s.sql_id, s.prev_sql_id, decode(command, 2, 'INSERT', 3, 'SELECT', 6, 'UPDATE', 7, 'DELETE', 47, 'PL/SQL EXE', command) com , i.host_name, i.instance_name from v$session s, v$process p, v$instance i where s.paddr = p.addr and p.spid = 22844; -- SID version col "Sid_Ser#_SqlHash"for a24 select p.spid, s.schemaname, s.sid||','||s.serial#||' ['||s.sql_hash_value||']' as "Sid_Ser#_SqlHash", s.status, s.machine, s.program, p.program pprogram, s.sql_id, s.prev_sql_id, decode(command, 2, 'INSERT', 3, 'SELECT', 6, 'UPDATE', 7, 'DELETE', 47, 'PL/SQL EXE', command) com , i.host_name, i.instance_name from v$session s, v$process p, v$instance i where s.paddr = p.addr and s.sid = 2790;