Scn to Timestamp and Timestamp to Scn - ORACLEAGENT BLOG

Feb, 2021

Oracle database Health check scripts

PERFOMANCE TUNING SCRIPTS

MONITOR SESSION
SQL PROFILE & BASELINE
ALL IN ONE SCRIPT
HISTORY OF EXECUTED QUERIES
SQL HISTORY
WAIT EVENTS, AWR ,ASH , TIME MODEL SCRIPTS
MONITOR SQL’S

MAIL SCRIPTS

Get DDL ‘s For All objects in oracle database

TEMP TABLESPACE scripts

UNDO TABLESPACE scripts

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

TUNING SCRIPTS

Root cause, Problems and Solutions

MONITOR SESSION

SQL PROFILE & BASELINE

ALL IN ONE SCRIPT

HISTORY OF EXECUTED QUERIES

SQL HISTORY

WAIT EVENTS, AWR ,ASH , TIME MODEL SCRIPTS

MONITOR SQL’S

Session monitoring scripts

Total user count on database:

set lines 750 pages 9999 compute SUM of tot on report compute SUM of active on report compute SUM of inactive on report col username for a50 select DECODE(username,NULL,'INTERNAL',USERNAME) Username, count(*) TOT, COUNT(DECODE(status,'ACTIVE',STATUS)) ACTIVE, COUNT(DECODE(status,'INACTIVE',STATUS)) INACTIVE from gv$session where status in ('ACTIVE','INACTIVE') group by username;

Users Details Session:

For Cluster:

set linesize 750 pages 9999 column box format a30 column spid format a10 column username format a30 column program format a30 column os_user format a20 col LOGON_TIME for a20 select b.inst_id,b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,to_char (b.logon_time, 'dd-mon-yyyy hh24:mi:ss') logon_time,substr(b.username,1,30) username, substr(b.osuser,1,20) os_user,substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id from gv$session b,gv$process a where b.paddr = a.addr and a.inst_id = b.inst_id and type='USER' order by logon_time;

For Standalone:

set pages 500 set linesize 750 column box format a30 column spid format a10 column username format a30 column program format a30 column os_user format a20 select b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,b.logon_time logon_date , to_char (b.logon_time, 'hh24:mi:ss') logon_time, substr(b.username,1,30) username, substr(b.osuser,1,20) os_user, substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id from v$session b,v$process a where b.paddr = a.addr and type='USER' order by b.sid;

(or)

set head off set verify off set echo off set pages 1500 set linesize 100 set lines 120 prompt prompt Details of SID / SPID / Client PID prompt ================================== select /+ CHOOSE/ 'Session Id.............................................: '||s.sid, 'Serial Num..............................................: '||s.serial#, 'User Name ..............................................: '||s.username, 'Session Status .........................................: '||s.status, 'Client Process Id on Client Machine ....................: '||''||s.process||'' Client, 'Server Process ID ......................................: '||p.spid Server, 'Sql_Address ............................................: '||s.sql_address, 'Sql_hash_value .........................................: '||s.sql_hash_value, 'Schema Name ..... ......................................: '||s.SCHEMANAME, 'Program ...............................................: '||s.program, 'Module .................................................: '|| s.module, 'Action .................................................: '||s.action, 'Terminal ...............................................: '||s.terminal, 'Client Machine .........................................: '||s.machine, 'LAST_CALL_ET ...........................................: '||s.last_call_et, 'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600 from v$session s, v$process p where p.addr=s.paddr and s.sid=nvl('&sid',s.sid) and p.spid=nvl('&spid',p.spid) and nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));

To Find Session Information Details based on SID or SPID or CLIENTPID :

col program for a15 col machine for a15 col terminal for a15 set lines 152 select s.sid,s.serial#,''||s.process||'' Client,p.spid Server,s.sql_address,s.sql_hash_value,s.username,s.action, s.program || s.module,s.terminal,s.machine, s.status,s.last_call_et,s.last_call_et/3600 from gv$session s, gv$process p where p.addr=s.paddr and s.sid=nvl('&sid',s.sid) and p.spid=nvl('&spid',p.spid) and nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));

Timing details, Client PID of associated oracle SID:

undefine spid set pagesize 40 col INST_ID for 99 col spid for a10 set linesize 150 col action format a10 col logon_time format a16 col module format a13 col cli_process format a7 col cli_mach for a15 col status format a10 col username format a10 col last_call_et for 9999.99 col sql_hash_value for 9999999999999 select p.INST_ID,p.spid,s.sid, s.serial#, s.status, s.username, s.action, to_char(s.logon_time, 'DD-MON-YY, HH24:MI') logon_time, s.module,s.program,s.last_call_et/3600 last_call_et ,s.process cli_process,s.machine cli_mach,s.sql_hash_value from gv$session s, gv$process p where p.addr=s.paddr and p.spid in(&SPID);

Checking Timing Details of SID and waiting event:

select a.sid, a.serial#, a.status, a.program, b.event,to_char(a.logon_time, 'dd-mon-yy hh24:mi') LOGON_TIME, to_char(Sysdate, 'dd-mon-yy--hh24:mi') CURRENT_TIME, (a.last_call_et/3600) "Hrs connected" from v$session a, v$session_wait b where a.sid in(&SIDs) and a.sid=b.sid;

Checking for active transactions SID

select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where t.addr=s.taddr and s.sid in(&SIDs);

Checking what is the Last SQL

undefine sid col "Last SQL" for a70 select s.username, s.sid, s.serial#,t.sql_text "Last SQL" from gv$session s, gv$sqlarea t where s.sql_address =t.address and s.sql_hash_value =t.hash_value and s.sid in (&SIDs);

All Active and Inactive connections

col program for a15F col machine for a15 col terminal for a15 set lines 152 select s.sid,s.serial#,''||s.process||'' Client,p.spid Server,s.sql_address,s.sql_hash_value,s.username,s.action, s.program || s.module,s.terminal,s.machine, s.status,s.last_call_et,s.last_call_et/3600 from gv$session s, gv$process p where p.addr=s.paddr and s.type != 'BACKGROUND';

Active sessions

select p.spid "Thread", s.sid "SID-Top Sessions", substr(s.osuser,1,15) "OS User", substr(s.program,1,25) "Program Running" from v$process p, v$session s where p.addr=s.paddr order by substr(s.osuser,1,15);

Session details from Session long ops

select SID,SERIAL#,OPNAME,SOFAR,TOTALWORK,START_TIME,LAST_UPDATE_TIME,username from v$session_longops where sid=&SID and serial#=&SERIAL;

To list the nodes

set head off set verify off set echo off set pages 1500 set linesize 70 prompt prompt Environment sketch prompt ================================== select /+ CHOOSE/ 'NODE_NAME.................: '||NODE_NAME, 'CREATION_DATE.............: '||CREATION_DATE, 'CREATED_BY ...............: '||CREATED_BY, 'SUPPORT_CP ...............: '||SUPPORT_CP, 'SUPPORT_FORMS ............: '||SUPPORT_FORMS, 'SUPPORT_WEB ..............: '||SUPPORT_WEB, 'SUPPORT_ADMIN ............: '||SUPPORT_ADMIN, 'STATUS ...................: '||STATUS, 'HOST.DOMAIN ..... ........: '||HOST||'.'||DOMAIN, 'SUPPORT_DB ..............: '||SUPPORT_DB from apps.fnd_nodes;

Session details through SPID:

select sid, serial#, USERNAME, STATUS, OSUSER, PROCESS, MACHINE, MODULE, ACTION, to_char(LOGON_TIME,'yyyy-mm-dd hh24:mi:ss') from v$session where paddr in (select addr from v$process where spid = '18924');

To list count of connections from other machines:

select count(1),machine from gv$session where inst_id=3 group by machine;

To get total count of sessions and processes:

select count(*) from v$session;

select count(*) from v$process;

select (select count(*) from v$session) sessions, (select count(*) from v$process) processes from dual;

To find sqltext through sqladdress:

select sql_address from v$session where sid=8794;

select sql_text from v$sqltext where ADDRESS='A00000089A00FA0' order by PIECE;

To find sqltext for different sql hashvalue:

select hash_value,sql_text from v$sql where hash_value in (193756789,156412346, 456812142,8971248686,2358702945);

To list long running forms user sessions:

select s.sid,s.process,p.spid,s.status ,s.action,s.module, (s.last_call_et/3600) from v$session s, v$process p where round(last_call_et/3600) >4 and action like '%FRM%' and p.addr=s.paddr ;

To list inactive Sessions respective username:

SELECT username,count(*) num_inv_sess FROM v$session where last_call_et > 3600 and username is not null AND STATUS='INACTIVE' group by username order by num_inv_sess DESC;

SELECT count() FROM v$session where last_call_et > 43200 and username is not null AND STATUS='INACTIVE';
SELECT count() FROM v$session where last_call_et > 3600 and username is not null AND STATUS='INACTIVE';

To find session id with set of SPIDs:

select sid from v$session, v$process where addr=paddr and spid in ('11214','12948','90088');

To find Sql Text given SQLHASH & SQLADDR:

select piece,sql_text from v$sqltext where HASH_VALUE = &hash and ADDRESS ='&addr' order by piece;
select piece,sql_text from v$sqltext where ADDRESS ='&addr' order by piece;

To find Undo Generated For a given session:

select username, t.used_ublk ,t.used_urec from gv$transaction t,gv$session s where t.addr=s.taddr and s.sid='&sessionid';

TEMP scripts

Sort space usage by session:

col sid_serial for a20 SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module, S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace, COUNT(*) sort_ops FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P WHERE T.session_addr = S.saddr AND S.paddr = P.addr AND T.tablespace = TBS.tablespace_name GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module, S.program, TBS.block_size, T.tablespace ORDER BY sid_serial; (or) set lines 1500 pages 9999 column sid format 9999 column username format a15 column SQL_EXEC_START for a21 column sql_text format a50 column module format a35 column sql_text format a50 SELECT a.inst_id,a.username, a.sid, a.serial#, a.osuser, (b.blocks*d.block_size)/1048576 MB_USED,a.sql_id,a.sql_child_number child,c.plan_hash_value,to_char (a.sql_exec_start, 'dd-Mon-yyyy hh24:mi:ss') sql_exec_start,c.rows_processed,a.status, -- c.sql_text substr(c.sql_text,1,50) sql_text FROM gv$session a, gv$tempseg_usage b, gv$sqlarea c ,(select block_size from dba_tablespaces where tablespace_name='TEMP') d WHERE b.tablespace = 'TEMP' and a.saddr = b.session_addr AND c.address= a.sql_address -- AND c.hash_value = a.sql_hash_value AND a.inst_id=b.inst_id ORDER BY 6 desc; (or) set lines 1500 pages 9999 column sid format 9999 column username format a15 column SQL_EXEC_START for a21 column sql_text format a50 column module format a35 column sql_text format a50 break on report compute SUM of MB_USED on report SELECT a.username, a.sid, a.serial#, a.osuser, (b.blocks*d.block_size)/1048576 MB_USED,a.sql_id,a.sql_child_number child,c.plan_hash_value,to_char (a.sql_exec_start, 'dd-Mon-yyyy hh24:mi:ss') sql_exec_start,c.rows_processed,a.status, -- c.sql_text substr(c.sql_text,1,50) sql_text FROM v$session a, v$tempseg_usage b, v$sqlarea c, (select block_size from dba_tablespaces where tablespace_name='TEMP') d WHERE b.tablespace = 'TEMP' and a.saddr = b.session_addr AND c.address= a.sql_address AND c.hash_value = a.sql_hash_value ORDER BY b.tablespace, 5 desc; (or) select ROUND(SUM(tempseg_size)/1048576) temp_mb from gv$sql_workarea_active WHERE sid=&sid;

History of Temp tablespace usage:

select sql_id,SQL_PLAN_HASH_VALUE,max(TEMP_SPACE_ALLOCATED)/(1024*1024*1024) gig from DBA_HIST_ACTIVE_SESS_HISTORY where sample_time > sysdate-10 and TEMP_SPACE_ALLOCATED > (10*1024*1024*1024) group by sql_id,SQL_PLAN_HASH_VALUE order by sql_id;

Shrink Temp file:

ALTER TABLESPACE temp SHRINK TEMPFILE '+ORA_DATA/JAPAN/tempfile/temp.313.868020865' KEEP 500M; Thank you for giving your valuable time to read the above information. If you want to be updated with all our articles send us the Invitation or Follow us: Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/ LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/ Facebook Page: https://www.facebook.com/Oracleagent-344577549964301 Ramkumar’s Twitter : https://twitter.com/ramkuma02877110 Ramkumar’s Telegram: https://t.me/oracleageant Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

UNDO scripts

v$parameter for undo:

select inst_id,name,value from gv$parameter where name like '%undo%';

UNDO tablespaces Free As per expired segments:

SELECT d.tablespace_name, round(((NVL(f.bytes,0) + (a.maxbytes - a.bytes))/1048576+ u.exp_space),2) as max_free_mb, round(((a.bytes - (NVL(f.bytes,0)+ (10241024u.exp_space)))100/a.maxbytes),2) used_pct FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes, sum(greatest(maxbytes,bytes)) maxbytes from sys.dba_data_files group by tablespace_name) a, (select tablespace_name, sum(bytes) bytes from sys.dba_free_space group by tablespace_name) f , (select tablespace_name , sum(blocks)8/(1024) exp_space from dba_undo_extents where status NOT IN ('ACTIVE','UNEXPIRED') group by tablespace_name) u WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND d.tablespace_name=u.tablespace_name AND d.contents = 'UNDO' AND u.tablespace_name = (select UPPER(value) from v$parameter where name = 'undo_tablespace');

does undo have expired segments?

select status, count(1) from dba_undo_extents group by status;

User Generated UNDO

col sql_text format a40 set lines 130 select sq.sql_text sql_text, t.USED_UREC Records, t.USED_UBLK Blocks, (t.USED_UBLK*8192/1024) KBytes from v$transaction t, v$session s, v$sql sq where t.addr = s.taddr and s.sql_id = sq.sql_id and s.username = '<user>' /

UNDO tablespace usage by USER

SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial, NVL(s.username, 'None') orauser, s.program, r.name undoseg, t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo" FROM sys.v_$rollname r, sys.v_$session s, sys.v_$transaction t, sys.v_$parameter x WHERE s.taddr = t.addr AND r.usn = t.xidusn(+) AND x.name = 'db_block_size'; (or) select username, t.used_ublk ,t.used_urec from gv$transaction t,gv$session s where t.addr=s.taddr and s.sid='&sid';

Recreating the undo tablespace or resolving to undo issue

select a.name,b.status , d.username , d.sid , d.serial# from v$rollname a,v$rollstat b, v$transaction c , v$session d where a.name IN ( select segment_name from dba_segments where tablespace_name = 'UNDOTBS1') and a.usn = b.usn and a.usn = c.xidusn and c.ses_addr = d.saddr;

Ref: https://community.oracle.com/tech/developers/discussion/1098943/create-new-undo-tablespace-and-remove-the-old-one

ORA-30019: Illegal rollback Segment operation in Automatic Undo mode

ALTER SYSTEM SET UNDO_SUPPRESS_ERRORS=TRUE scope=both;

If TRUE means it will not show any undo errors, So some times job may complete successfully.

Find active, expired, unexpired undo

select status, round(sum_bytes / (1024*1024), 0) as MB, round((sum_bytes / undo_size) * 100, 0) as PERC from ( select status, sum(bytes) sum_bytes from dba_undo_extents group by status ), ( select sum(a.bytes) undo_size from dba_tablespaces c join v$tablespace b on b.name = c.tablespace_name join v$datafile a on a.ts# = b.ts# where c.contents = 'UNDO' and c.status = 'ONLINE' ); select count(status) from dba_undo_extents where status = 'EXPIRED'; select count(status) from dba_undo_extents where status = 'UNEXPIRED'; select count(status) from dba_undo_extents where status = 'ACTIVE';

Find active, expired, and unexpired undo by Size in MB

select status,TABLESPACE_NAME,sum(bytes) / 1024 / 1024 || 'MB' from DBA_UNDO_EXTENTS group by status,TABLESPACE_NAME; select status, sum(bytes) / 1024 / 1024 || 'MB' from DBA_UNDO_EXTENTS group by status;

Undo advisor:

SELECT d.undo_size/(10241024) "ACTUAL UNDO SIZE [MByte]", SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]", ROUND((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]" FROM ( SELECT SUM(a.bytes) undo_size FROM v$datafile a, v$tablespace b, dba_tablespaces c WHERE c.contents = 'UNDO' AND c.status = 'ONLINE' AND b.name = c.tablespace_name AND a.ts# = b.ts# ) d, v$parameter e, v$parameter f, ( SELECT MAX(undoblks/((end_time-begin_time)3600*24)) undo_block_per_sec FROM v$undostat ) g WHERE e.name = 'undo_retention' AND f.name = 'db_block_size'; (or) SELECT dbms_undo_adv.required_undo_size(1800, SYSDATE-30, SYSDATE) FROM dual;

Thank you for giving your valuable time to read the above information. If you want to be updated with all our articles send us the Invitation or Follow us: Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/ LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/ Facebook Page: https://www.facebook.com/Oracleagent-344577549964301 Ramkumar’s Twitter : https://twitter.com/ramkuma02877110 Ramkumar’s Telegram: https://t.me/oracleageant Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

SQL Profiles & Baselines

To check SQL Profile :

select NAME,SIGNATURE ,STATUS,FORCE_MATCHING from dba_sql_profiles;

ENABLE/DISABLE/DROP

EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('coe_5273fz2cqkk80_3455548535','STATUS','DISABLED');

exec dbms_sqltune.drop_sql_profile('coe_5273fz2cqkk80_3455548535');

SQL Profiles for a sql_id

set lines 1000 pages 9999 col name for a30 col task_exec_name for a16 col category for a10 col created for a30 col sql_text for a150 col signature for 9999999999999999999999999 select sql.sql_id,sql.child_number as child , prof.name, prof.category, prof.created, prof.task_exec_name,prof.FORCE_MATCHING, prof.status, prof.SIGNATURE,prof.SQL_TEXT from dba_sql_profiles prof, gv$sql sql where sql.sql_id in ('&sql_id') order by created;

SQL Baselines:

Check the current baseline:

select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED ,sql_text from dba_sql_plan_baselines; select * from table(dbms_xplan.display_sql_plan_baseline(plan_name=>'SQL_PLAN_b7pnq6yp8da6a29d0d9b7'));

Drop the baseline:

set serveroutput on DECLARE i NATURAL; BEGIN i := dbms_spm.drop_sql_plan_baseline('SQL_b3d69637aa86a8ca'); dbms_output.put_line(i); END; /

Load the cursor:

variable sqlid number; execute :sqlid :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'5qbbnv0abm2vx', PLAN_HASH_VALUE=> 4197102931, SQL_HANDLE => 'SQL_d3318f33dfac7bc2');

Enable/Disable:

SET SERVEROUTPUT ON DECLARE l_plans_altered PLS_INTEGER; BEGIN l_plans_altered := DBMS_SPM.alter_sql_plan_baseline( sql_handle => 'SYS_SQL_1447ba3a1d83920f', plan_name => 'SYS_SQL_PLAN_1d83920fae82cf72', attribute_name => 'ENABLED', attribute_value => 'YES'); DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered); END; / SET SERVEROUTPUT ON DECLARE l_plans_altered PLS_INTEGER; BEGIN l_plans_altered := DBMS_SPM.alter_sql_plan_baseline( sql_handle => 'SYS_SQL_1447ba3a1d83920f', plan_name => 'SYS_SQL_PLAN_1d83920fae82cf72', attribute_name => 'ACCEPTED', attribute_value => 'YES'); DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered); END; / SET SERVEROUTPUT ON DECLARE l_plans_altered PLS_INTEGER; BEGIN l_plans_altered := DBMS_SPM.alter_sql_plan_baseline( sql_handle => 'SQL_b3d69637aa86a8ca', plan_name => 'SQL_PLAN_b7pnq6yp8da6a29d0d9b7', attribute_name => 'FIXED', attribute_value => 'YES'); DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered); END; /

Fix baseline of one sql_id to another

variable sqlid number; execute :sqlid :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'31pux6bymf1d4'); SQL> select sql_handle, plan_name, enabled from dba_sql_plan_baselines;

SQL_HANDLE PLAN_NAME ENA ——————————————————————————————————————————– ——————————————————————————————————————————– — SQL_d3318f33dfac7bc2 SQL_PLAN_d6ccg6ggusyy2ee7f0c12 YES variable sqlid number; exec :sqlid :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE ( SQL_HANDLE=> 'SQL_d3318f33dfac7bc2', PLAN_NAME => 'SQL_PLAN_d6ccg6ggusyy2ee7f0c12', ATTRIBUTE_NAME=> 'enabled', ATTRIBUTE_VALUE => 'NO'); Now fix the plan of 31pux6bymf1d4 to 5qbbnv0abm2vx 5qbbnv0abm2vx variable sqlid number; execute :sqlid :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>’5qbbnv0abm2vx’, PLAN_HASH_VALUE=> 4197102931, SQL_HANDLE => ‘SQL_d3318f33dfac7bc2’); SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('5qbbnv0abm2vx',&childnumber,'ALLSTATS LAST +PEEKED_BINDS +PROJECTION +ALIAS +OUTLINE +PREDICATE +COST +BYTES')); 4197102931

Extract hint from all profile/baseline/patch :

All in one script:

set pagesize 60 set linesize 180 set trimspool on column plan_name format a32 column signature format 999,999,999,999,999,999,999 column category format a10 column hint format a70 wrap word break on plan_name skip 1 on signature skip 1 on opt_type skip 1 on category skip 1 on plan_id skip 1 spool sql_profile_baseline_11g select prf.plan_name,prf.signature,decode(obj_type,1,'Profile',2,'Baseline',3,'Patch', 'Other') opt_type,prf.category,prf.plan_id,extractvalue(value(hnt),'.') hint from ( select /*+ no_eliminate_oby */ so.name plan_name, so.signature,so.category, so.obj_type,so.plan_id,sod.comp_data from sqlobj$ so,sqlobj$data sod where so.name = '&m_plan_name' and sod.signature = so.signature and sod.category = so.category and sod.obj_type = so.obj_type and sod.plan_id = so.plan_id order by signature, obj_type, plan_id ) prf, table ( select xmlsequence( extract(xmltype(prf.comp_data),'/outline_data/hint') )from dual ) hnt;

Taking SQL HINTS from memory:

SELECT chr(9)||chr(9)||''''||regexp_replace(extractvalue(value(d), '/hint'),'''','''''')||''',' from xmltable('/*/outline_data/hint' passing ( select xmltype(other_xml) as xmlval from v$sql_plan where sql_id like nvl('&sql_id',sql_id) and child_number = &child_no and other_xml is not null ) ) d;

Taking SQL HINTS from AWR:

SELECT chr(9)||chr(9)||''''||regexp_replace(extractvalue(value(d), '/hint'),'''','''''')||''',' from xmltable('/*/outline_data/hint' passing ( select xmltype(other_xml) as xmlval from -- v$sql_plan dba_hist_sql_plan where sql_id like nvl('&sql_id',sql_id) and other_xml is not null )) d;

Taking SQL HINTS from PROFILES

select hint as outline_hints from (select p.name, p.signature, p.category, row_number() over (partition by sd.signature, sd.category order by sd.signature) row_num, extractValue(value(t), '/hint') hint from sqlobj$data sd, dba_sql_profiles p, table(xmlsequence(extract(xmltype(sd.comp_data), '/outline_data/hint'))) t where sd.obj_type = 1 and p.signature = sd.signature and p.category = sd.category and p.name like ('&profile_name')) order by row_num;

Taking SQL HINTS from PLAN_TABLE:

SELECT regexp_replace(extractvalue(value(d), '/hint'),'''','''''') plan_hint from xmltable('/*/outline_data/hint' passing ( select xmltype(other_xml) as xmlval from plan_table where other_xml is not null and plan_id = (select max(plan_id) from plan_table) and rownum=1 ) ) d;

Fixing the PLAN HASH VALUE:

Resolution normally is to fix the execution plan in 11g by running

variable x number begin :x := dbms_spm.load_plans_from_cursor_cache( sql_id=>'&sql_id', plan_hash_value=>&plan_hash, fixed=>'YES'); end; /

To see the hints from sql_profile

select hint from ( select s.sql_id, sd.obj_type, row_number() over (partition by sd.signature, sd.category order by sd.signature) row_num, extractValue(value(t), '/hint') hint from sqlobj$data sd, v$sql s, table(xmlsequence(extract(xmltype(sd.comp_data), '/outline_data/hint'))) t where sd.obj_type = 1 and s.exact_matching_signature = sd.signature and s.sql_id = '&sql_id') order by row_num /

Purge the old plan hash value from memory:

BEGIN FOR i IN (SELECT address, hash_value FROM gv$sqlarea WHERE sql_id = '&sql_id.') LOOP SYS.DBMS_SHARED_POOL.PURGE(i.address||','||i.hash_value, 'C'); END LOOP; END; /

All in one script

Thank you for giving your valuable time to read the above information. If you want to be updated with all our articles send us the Invitation or Follow us: Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/ LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/ Facebook Page: https://www.facebook.com/Oracleagent-344577549964301 Ramkumar’s Twitter: https://twitter.com/ramkuma02877110 Ramkumar’s Telegram: https://t.me/oracleageant Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

All reports in one script

Its like a doctor script. My suggestion is to don’t directly run all the scripts in prod it will make some performance issue. Use the specified query and execute step by step. Its easy to figure out your issues.

"
------------------------------------------------------------------------------------------------------------------------------------------------------
---
--- one script to Identify all performace issue related to sql query
------
--
--
------------------------------------------------------------------------------------------------------------------------------------------------------

Set echo off
set trimspool on
set define on

column filename new_val filename
select to_char(sysdate, 'yyyymmdd-hh-mi-ss' ) filename from dual;
column dbname new_value dbname noprint
select name dbname from v$pdbs;
spool &dbname-&filename..txt

PROMPT ==================
PROMPT DB INFO
PROMPT ==================

set lines 750 pages 9999
select name CDB_NAME,(select name from v$pdbs) PDB_NAME,database_role from v$database;
select INSTANCE_NAME,HOST_NAME,logins,VERSION from v$instance;

PROMPT ==================
PROMPT TOTAL Connections
PROMPT ==================

set lines 750 pages 9999
break on report
compute SUM of tot on report
compute SUM of active on report
compute SUM of inactive on report
col username for a50
select DECODE(username,NULL,'INTERNAL',USERNAME) Username,
count(*) TOT,
COUNT(DECODE(status,'ACTIVE',STATUS)) ACTIVE,
COUNT(DECODE(status,'INACTIVE',STATUS)) INACTIVE
from gv$session
where status in ('ACTIVE','INACTIVE')
group by username;

PROMPT ================
PROMPT Session Details
PROMPT ================

set linesize 750 pages 9999
column box format a30
col serial# for 999999
column spid format a10
column username format a30
column program format a30
column os_user format a20
col LOGON_TIME for a20

select b.inst_id,b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,to_char (b.logon_time, 'dd-mon-yyyy hh24:mi:ss') logon_time,
substr(b.username,1,30) username,
substr(b.osuser,1,20) os_user,
substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id
from gv$session b,gv$process a
where b.paddr = a.addr
and a.inst_id = b.inst_id
and type='USER'
order by b.inst_id,b.sid;

PROMPT ================
PROMPT Sql Details
PROMPT ================

column sid format 9999
column username format a15
column PARSING_SCHEMA_NAME format a15
column sql_text format a50
column module format a35
select a.inst_id,a.sid,a.username,b.PARSING_SCHEMA_NAME,a.module,a.sql_id,a.sql_child_number child,b.hash_value,to_char (a.sql_exec_start, 'dd-Mon-yyyy hh24:mi:ss') sql_exec_start,(sysdate-sql_exec_start)*24*60*60 SECS,b.rows_processed,a.status,substr(b.sql_text,1,50) sql_text
from gv$session a,gv$sqlarea b
where a.sql_hash_value = b.hash_value
and a.sql_address = b.address
and a.module not like '%emagent%'
and a.module not like '%oraagent.bin%'
and a.username is not null
order by a.status;

PROMPT =========================
PROMPT Sql Monitor - REPORT
PROMPT =========================

column text_line format a1000
set lines 750 pages 9999
set long 20000 longchunksize 20000
select dbms_sqltune.report_sql_monitor_list() text_line from dual;

PROMPT =========================
PROMPT Sql Monitor - Executing
PROMPT =========================

set lines 1000 pages 9999
column sid format 9999
column serial for 999999
column status format a15
column username format a10
column sql_text format a80
column module format a30
col program for a30
col SQL_EXEC_START for a20

SELECT * FROM
(SELECT status,inst_id,sid,SESSION_SERIAL# as Serial,username,sql_id,SQL_PLAN_HASH_VALUE,
program,
TO_CHAR(sql_exec_start,'dd-mon-yyyy hh24:mi:ss') AS sql_exec_start,
ROUND(elapsed_time/1000000) AS "Elapsed (s)",
ROUND(cpu_time /1000000) AS "CPU (s)",
substr(sql_text,1,30) sql_text
FROM gv$sql_monitor where status='EXECUTING' and module not like '%emagent%'
ORDER BY sql_exec_start desc
);

PROMPT ================
PROMPT Blocking Session
PROMPT ================

set lines 750 pages 9999
col blocking_status for a100
select s1.inst_id,s2.inst_id,s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid and s1.inst_id=l1.inst_id and s2.inst_id=l2.inst_id
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2
order by s1.inst_id;

PROMPT ==============================================================================
PROMPT More Details
PROMPT ==============================================================================

accept sid default '' -
prompt 'Please provide the sid: '

accept inst_id default '' -
prompt 'Please provide the inst_id: '

PROMPT ================
PROMPT SID Details
PROMPT ================

column box format a30
column spid format a10
column username format a20
column program format a30
column os_user format a20
col LOGON_TIME for a20

select b.inst_id,b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,to_char (b.logon_time, 'dd-mon-yyyy hh24:mi:ss') logon_time,
substr(b.username,1,20) username,
substr(b.osuser,1,20) os_user,
substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id
from gv$session b,gv$process a
where b.paddr = a.addr
and a.inst_id = b.inst_id
and type='USER' and b.sid=&sid
and b.inst_id=&inst_id
order by logon_time;




column sid format 9999
column username format a15
column PARSING_SCHEMA_NAME format a15
column sql_text format a50
column module format a35
select a.inst_id,a.sid,a.username,b.PARSING_SCHEMA_NAME,a.module,a.sql_id,a.sql_child_number child,b.hash_value,to_char (a.sql_exec_start, 'dd-Mon-yyyy hh24:mi:ss') sql_exec_start,(sysdate-sql_exec_start)*24*60*60 SECS,b.rows_processed,a.status,substr(b.sql_text,1,50) sql_text
from gv$session a,gv$sqlarea b
where a.sql_hash_value = b.hash_value
and a.sql_address = b.address
and a.sid=&sid
and a.inst_id=&inst_id
and a.module not like '%emagent%'
and a.module not like '%oraagent.bin%'
and a.username is not null
order by a.status;

accept sql_id default '' -
prompt 'Please provide the sql_id: '

PROMPT ================
PROMPT SID Waiting on
PROMPT ================

COLUMN username FORMAT A20
COLUMN sid FORMAT 9999
COLUMN serial# FORMAT 999999
COLUMN event FORMAT A40

SELECT NVL(s.username, '(oracle)') AS username,
s.sid,
s.serial#,
se.event,
se.total_waits,
se.total_timeouts,
se.time_waited,
se.average_wait,
se.max_wait,
se.time_waited_micro
FROM gv$session_event se,
gv$session s
WHERE s.sid = se.sid
AND s.sid = &sid
and s.inst_id=se.inst_id
and s.inst_id=&inst_id
ORDER BY se.time_waited DESC
/

col WAIT_CLASS for a10
SELECT sw.inst_id,NVL(s.username, '(oracle)') AS username,
s.sid,
s.serial#,
sw.event,
sw.wait_class,
sw.wait_time,
sw.seconds_in_wait,
sw.state
FROM gv$session_wait sw,
gv$session s
WHERE s.sid = sw.sid and s.inst_id=sw.inst_id and s.sid=&sid
and s.inst_id=&inst_id
ORDER BY sw.seconds_in_wait DESC;

PROMPT ================
PROMPT Session LongOps
PROMPT ================

SET VERIFY OFF

SELECT a.sid,RPAD(a.opname,30),a.sofar,a.totalwork,a.ELAPSED_SECONDS,ROUND(((a.sofar)*100)/a.totalwork,3) "%_COMPLETED",time_remaining,
RPAD(a.username,10) username,a.SQL_HASH_VALUE,B.STATUS
FROM GV$SESSION_LONGOPS a, gv$session b
WHERE a.sid=&sid
and b.inst_id=&inst_id
AND a.sofar<> a.totalwork
/

PROMPT ==============================
PROMPT RealTime Monitoring For Sid
PROMPT ===============================

SELECT *
FROM
(SELECT status,
--username,
sql_id,
sql_exec_id,
TO_CHAR(sql_exec_start,'dd-mon-yyyy hh24:mi:ss') AS sql_exec_start,
ROUND(elapsed_time/1000000) AS "Elapsed (s)",
ROUND(cpu_time /1000000) AS "CPU (s)",
buffer_gets,
ROUND(physical_read_bytes /(1024*1024)) AS "Phys reads (MB)",
ROUND(physical_write_bytes/(1024*1024)) AS "Phys writes (MB)"
FROM gv$sql_monitor where sid=&sid and inst_id=&inst_id
ORDER BY elapsed_time DESC
)
WHERE rownum<=20;

SELECT ROUND(elapsed_time /1000000) AS "Elapsed (s)",
ROUND(cpu_time /1000000,3) AS "CPU (s)",
ROUND(queuing_time /1000000,3) AS "Queuing (s)",
ROUND(user_io_wait_time /1000000,3) AS "I/O wait (s)",
ROUND(application_wait_time/1000000,3) AS "Appli wait (s)",
ROUND(concurrency_wait_time/1000000,3) AS "Concurrency wait (s)",
ROUND(cluster_wait_time /1000000,3) AS "Cluster wait (s)",
ROUND(physical_read_bytes /(1024*1024)) AS "Phys reads (MB)",
ROUND(physical_write_bytes /(1024*1024)) AS "Phys writes (MB)",
buffer_gets AS "Buffer gets",
ROUND(plsql_exec_time/1000000,3) AS "Plsql exec (s)",
ROUND(java_exec_time /1000000,3) AS "Java exec (s)"
FROM gv$sql_monitor
WHERE sid=&sid and inst_id=&inst_id;

PROMPT ================================
PROMPT SQL_MONITOR REPORT
PROMPT ================================

set pagesize 0 echo off timing off linesize 1000 trimspool on trim on long 2000000 longchunksize 2000000
select
DBMS_SQLTUNE.REPORT_SQL_MONITOR(
sql_id=>'&sql_id',
report_level=>'ALL',
type=>'TEXT')
from dual;

PROMPT ================================
PROMPT Full sql statement
PROMPT ================================

set lines 1000 pages 9999
set long 20000
col sql_text for a500
select sql_text from dba_hist_sqltext where sql_id = '&sql_id';

PROMPT ================
PROMPT Bind Variables
PROMPT ================

col VALUE_STRING for a50
SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING FROM gv$sql_bind_capture WHERE sql_id='&sql_id' and inst_id=&inst_id;

PROMPT ================
PROMPT SQL History
PROMPT ================

set lines 1000 pages 9999
COL instance_number FOR 9999 HEA 'Inst';
COL end_time HEA 'End Time';
COL plan_hash_value HEA 'Plan|Hash Value';
COL executions_total FOR 999,999 HEA 'Execs|Total';
COL rows_per_exec HEA 'Rows Per Exec';
COL et_secs_per_exec HEA 'Elap Secs|Per Exec';
COL cpu_secs_per_exec HEA 'CPU Secs|Per Exec';
COL io_secs_per_exec HEA 'IO Secs|Per Exec';
COL cl_secs_per_exec HEA 'Clus Secs|Per Exec';
COL ap_secs_per_exec HEA 'App Secs|Per Exec';
COL cc_secs_per_exec HEA 'Conc Secs|Per Exec';
COL pl_secs_per_exec HEA 'PLSQL Secs|Per Exec';
COL ja_secs_per_exec HEA 'Java Secs|Per Exec';
SELECT 'gv$dba_hist_sqlstat' source,h.instance_number,
TO_CHAR(CAST(s.begin_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') snap_time,
TO_CHAR(CAST(s.end_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') end_time,
h.sql_id,
h.plan_hash_value,
h.executions_total,
TO_CHAR(ROUND(h.rows_processed_total / h.executions_total), '999,999,999,999') rows_per_exec,
TO_CHAR(ROUND(h.elapsed_time_total / h.executions_total / 1e6, 3), '999,990.000') et_secs_per_exec,
TO_CHAR(ROUND(h.cpu_time_total / h.executions_total / 1e6, 3), '999,990.000') cpu_secs_per_exec,
TO_CHAR(ROUND(h.iowait_total / h.executions_total / 1e6, 3), '999,990.000') io_secs_per_exec,
TO_CHAR(ROUND(h.clwait_total / h.executions_total / 1e6, 3), '999,990.000') cl_secs_per_exec,
TO_CHAR(ROUND(h.apwait_total / h.executions_total / 1e6, 3), '999,990.000') ap_secs_per_exec,
TO_CHAR(ROUND(h.ccwait_total / h.executions_total / 1e6, 3), '999,990.000') cc_secs_per_exec,
TO_CHAR(ROUND(h.plsexec_time_total / h.executions_total / 1e6, 3), '999,990.000') pl_secs_per_exec,
TO_CHAR(ROUND(h.javexec_time_total / h.executions_total / 1e6, 3), '999,990.000') ja_secs_per_exec
FROM dba_hist_sqlstat h,
dba_hist_snapshot s
WHERE h.sql_id = '&sql_id'
AND h.executions_total > 0
AND s.snap_id = h.snap_id
AND s.dbid = h.dbid
AND s.instance_number = h.instance_number
UNION ALL
SELECT 'gv$sqlarea_plan_hash' source,h.inst_id,
TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') snap_time,
TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') end_time,
h.sql_id,
h.plan_hash_value,
h.executions,
TO_CHAR(ROUND(h.rows_processed / h.executions), '999,999,999,999') rows_per_exec,
TO_CHAR(ROUND(h.elapsed_time / h.executions / 1e6, 3), '999,990.000') et_secs_per_exec,
TO_CHAR(ROUND(h.cpu_time / h.executions / 1e6, 3), '999,990.000') cpu_secs_per_exec,
TO_CHAR(ROUND(h.USER_IO_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') io_secs_per_exec,
TO_CHAR(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') cl_secs_per_exec,
TO_CHAR(ROUND(h.APPLICATION_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') ap_secs_per_exec,
TO_CHAR(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') cc_secs_per_exec,
TO_CHAR(ROUND(h.PLSQL_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') pl_secs_per_exec,
TO_CHAR(ROUND(h.JAVA_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') ja_secs_per_exec
FROM gv$sqlarea_plan_hash h
WHERE h.sql_id = '&sql_id'
and h.inst_id=&inst_id
AND h.executions > 0
order by source ;

PROMPT ================================
PROMPT Sql_id waiting on ?
PROMPT ================================

select
sql_id,event,
time_waited "time_waited(s)",
case when time_waited = 0 then
0
else
round(time_waited*100 / sum(time_waited) Over(), 2)
end "percentage"
from
(
select sql_id,event, sum(time_waited) time_waited
from gv$active_session_history
where sql_id = '&sql_id' and
inst_id=&inst_id
group by sql_id,event
)
order by
time_waited desc;

PROMPT ================================
PROMPT Object Statistics
PROMPT ================================

col table_name for a40
col owner for a30
select distinct owner, table_name, STALE_STATS, last_analyzed, stattype_locked
from dba_tab_statistics
where (owner, table_name) in
(select distinct owner, table_name
from dba_tables
where ( table_name)
in ( select object_name
from gv$sql_plan
where upper(sql_id) = upper('&sql_id') and inst_id=&inst_id and object_name is not null))
--and STALE_STATS='YES'
/

col index_name for a50
SELECT owner, index_name, table_name,last_analyzed, sample_size, num_rows, partitioned, global_stats
FROM dba_indexes
WHERE index_name IN (
select distinct rtrim(substr(plan_table_output, instr(plan_table_output, '|', 1, 3)+2, (instr(plan_table_output, '|', 1, 4)-instr(plan_table_output, '|', 1, 3)-2)), ' ')
from (
SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', null, 'BASIC'))
UNION ALL
SELECT * FROM TABLE(dbms_xplan.display_awr('&sql_id', null, null, 'ALL'))
)
where plan_table_output like '%INDEX%'
)
ORDER BY owner, table_name, index_name
/

PROMPT ================================
PROMPT Explain Plan from Memory
PROMPT ================================

col PLAN FOR a150
SELECT
RPAD('(' || p.plan_line_ID || ' ' || NVL(p.plan_parent_id,'0') || ')',8) || '|' ||
RPAD(LPAD (' ', 2*p.plan_DEPTH) || p.plan_operation || ' ' || p.plan_options,60,'.') ||
NVL2(p.plan_object_owner||p.plan_object_name, '(' || p.plan_object_owner|| '.' || p.plan_object_name || ') ', '') ||
NVL2(p.plan_COST,'Cost:' || p.plan_COST,'') || ' ' ||
NVL2(p.plan_bytes||p.plan_CARDINALITY,'(' || p.plan_bytes || ' bytes, ' || p.plan_CARDINALITY || ' rows)','') || ' ' ||
NVL2(p.plan_partition_start || p.plan_partition_stop,' PStart:' || p.plan_partition_start || ' PStop:' || p.plan_partition_stop,'') ||
NVL2(p.plan_time, p.plan_time || '(s)','') AS PLAN
FROM gv$sql_plan_monitor p
WHERE sid=&sid
and p.inst_id=&inst_id
ORDER BY p.plan_line_id, p.plan_parent_id;

select * from table(dbms_xplan.display_cursor('&sql_id', NULL, 'ALLSTATS LAST'));

PROMPT ================================
PROMPT Explain Plan from AWR
PROMPT ================================

select * from table(dbms_xplan.display_awr('&sql_id', NULL, null, 'ALLSTATS LAST'));

PROMPT ================================
PROMPT Sql Profiles
PROMPT ================================

set lines 1000 pages 9999
col name for a30
col task_exec_name for a16
col category for a10
col created for a30
col sql_text for a150
col signature for 9999999999999999999999999

select sql.sql_id,sql.child_number as child , prof.name, prof.category, prof.created,prof.task_exec_name,prof.FORCE_MATCHING,prof.status,prof.SIGNATURE
from
dba_sql_profiles prof,
gv$sql sql
where sql.sql_id in ('&sql_id')
-- and sql.child_number=child_number
-- and sql.force_matching_signature=prof.SIGNATURE
order by
created;

PROMPT ================
PROMPT Sql Baselines
PROMPT ================

col SQL_HANDLE for a30
col origin for a16
col last_modified for a30
col last_verified for a30

select sql_handle, plan_name, origin, created, last_modified, last_verified,ENABLED,ACCEPTED,FIXED,REPRODUCED
from dba_sql_plan_baselines
where signature in (select force_matching_signature
from gv$sql
where sql_id='&sql_id'
and inst_id=&inst_id);

undef sid
undef sql_id
undef inst_id
spool off;

"

SRVCTL command in RAC in 19c Database

Oracle RAC Administration and Monitoring Scripts -2

There are lots of options used with SRVCTL command, i mentioned them below.

-d Database Name
-i Instance Name
-s Service Name
-n Node Name
-r Preferred list
-a Available list
-p TAF (Transparent application failover policy)
-v Verbose

Status and Config Database

We can check all instance status of database as follows.

srvctl status database -d DB_NAME

[oracle@RAMdbrac01 ~]$ srvctl status database -d RAM
Instance RAM1 is running on node RAMdbrac01
Instance RAM2 is running on node RAMdbadm02
Instance RAM3 is running on node RAMdbadm03
Instance RAM4 is running on node RAMdbadm04
[oracle@RAMdbrac01 ~]$

We can display the configurations (instances name, nodes and oracle home) of any RAC Database as follows.

srvctl config database -d DB_NAME

srvctl config database -d RAM

Shutdown RAC Database

We can shutdown all instances of any database as follows.

srvctl stop database -d db_name [-o stop_options]
srvctl stop database -d DB_NAME
srvctl stop database -d DB_NAME -o normal
srvctl stop database -d DB_NAME -o immediate
srvctl stop database -d DB_NAME -o transactional
srvctl stop database -d DB_NAME -o abort

[oracle@RAMdbrac01 ~]$ srvctl stop database -d RAM

Startup RAC Database

We can start all instances of any database as follows.

srvctl start database -d db_name [-o start_options]

srvctl start database -d DB_NAME

srvctl start database -d DB_NAME -o nomount
srvctl start database -d DB_NAME -o mount
srvctl start database -d DB_NAME -o open

[oracle@RAMdbrac01 ~]$ srvctl start database -d RAM

Shutdown An Instance

We can shutdown only specific Instance of RAC database as follows.

srvctl stop instance -d db_unique_name [-i "instance_name_list"]} [-o stop_options] [-f]

srvctl stop instance -d DB_NAME -i INSTANCE_NAME

[oracle@RAMdbrac01 ~]$ srvctl stop instance -d RAM -i RAM1

Startup and Status An Instance

We can startup only specific Instance(s) of RAC database as follows.

srvctl start instance -d db_unique_name [-i "instance_name_list"} [-o start_options]

srvctl start instance -d DB_NAME -i INSTANCE_NAME

[oracle@RAMdbrac01 ~]$ srvctl start instance -d RAM -i RAM1

We can display any instance of RAC database as follows.

srvctl status instance -d DB_NAME -i INSTANCE_NAME

[oracle@RAMdbrac01 ~]$ srvctl status instance -d RAM -i RAM1

Add and Remove Database from CRS

We can add new DB to CRS as follows.

srvctl add database -d db_unique_name -o ORACLE_HOME [-p spfile]

srvctl add database -d RAM -o /u01/app/product/12.1.0.2/dbhome_1 -p +DATAC1/RAM/parameterfile/spfileRAM.ora

We can remove existing DB from CRS as follows.

srvctl remove database -d db_unique_name [-f] [-y] [-v]

srvctl remove database -d DB_NAME -f -y

Stop, Start and Check ASM via SRVCTL

We can shutdown an ASM instance as follows.

srvctl stop asm -n NODE_NAME -o immediate

srvctl stop asm -n RAMdbrac01 -o immediate

We can startup an ASM instance as follows.

srvctl start asm -n NODE_NAME

srvctl start asm -n RAMdbrac01

We can display configuration and status an ASM instance as follows.

srvctl status asm -n NODE_NAME
srvctl config asm -n NODE_NAME

[oracle@RAMdbrac01 ~]$ srvctl status asm -n RAMdbrac01
ASM is running on RAMdbrac01
[oracle@RAMdbrac01 ~]$
[oracle@RAMdbrac01 ~]$
[oracle@RAMdbrac01 ~]$ srvctl config asm -n RAMdbrac01
PRKO-2207 : Warning:-node option has been deprecated and will be ignored.
ASM home: <CRS home>
Password file: +DBFS_DG/orapwASM
ASM listener: LISTENER
[oracle@RAMdbrac01 ~]$

Stop, Start and Check Service via SRVCTL

We can start, stop and check any service on Database as follows.

srvctl status service -d db_name
srvctl config service -d db_name
srvctl start service -d dbname -s service_name
srvctl stop service -d dbname -s service_name

srvctl add service -d {DB_NAME} -s {SERVICE_NAME} -r {"preferred_list"} -a {"available_list"} [-P {BASIC | NONE | PRECONNECT}]

srvctl relocate service -d db_name -s service_name -i instance_name -t new_instance_name [-f]

We can remove any service as follows.

srvctl remove service -d {DB_NAME} -s {SERVICE_NAME}

srvctl remove service -d RAM -s RAM_SALES

Enable/disable Autorestart of Instance and Database

We can enable and disable auto restart of the database as follows.

srvctl enable database -d DB_UNIQUE_NAME
srvctl disable database -d DB_UNIQUE_NAME

We can enable and disable auto restart of the instance

srvctl enable instance -d DB_UNIQUE_NAME-i INSTANCE_NAME
srvctl disable instance -d DB_UNIQUE_NAME-i INSTANCE_NAME

Start, Stop and Check status,configurations of NodeApps

srvctl status nodeapps -n nodename
srvctl config nodeapps -n nodename
srvctl start nodeapps -n nodename
srvctl stop nodeapps -n nodename

History of executed queries

The view v$sql contains almost of queries which are executed in your Oracle Database. I attached few sql queries which will be useful to check history of SQL.

1. Get latest query

select sql_txt from v$sql where first_load_time=(select max(1st_load_time) from v$sql);

2. Sort executed queries by load time

select sql_text, first_load_time from v$sql order by first_load_time desc;

3. Get executed queries in a schema which have special text and sort by load time

select * from v$sql where parsing_schema_name like 'YOUR_SCHEMA_DETAILS' and 
sql_text like '%YOUR_SQLTEXT%' order by first_load_time desc;

4. Get 50 last executed queries

select sql_fulltext from (select * from v$sql where parsing_schema_name 
like 'ORAAGENT' order by first_load_time desc) where rownum < 51;

5. Get 50 executed UPDATE or DELETE queries in a specific time period and sort by load time

select sql_text,sql_fulltext, first_load_time, parsing_schema_name from
(
  select * from v$sql   where parsing_schema_name like 'YOUR_SCHEMA'
    and (sql_text like '%UPDATE %' or sql_text like '%INSERT %') 
and to_timestamp(first_load_time, 'YYYY-MM-DD/HH24:MI:SS') > 
to_timestamp('2021-01-24/09:06:00', 'YYYY-MM-DD/HH24:MI:SS')
  order by first_load_time desc
)
where rownum < 51

You can create your own queries to find out what queries you need to check. Remember this view v$sql doesn’t store prepared statements.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

History of executed queries

The view v$sql contains almost of queries which are executed in your Oracle Database. I attached few sql queries which will be useful to check history of SQL.

1. Get latest query

select sql_txt from v$sql where first_load_time=(select max(1st_load_time) from v$sql);

2. Sort executed queries by load time

select sql_text, first_load_time from v$sql order by first_load_time desc;

3. Get executed queries in a schema which have special text and sort by load time

select * from v$sql where parsing_schema_name like 'YOUR_SCHEMA_DETAILS' and 
sql_text like '%YOUR_SQLTEXT%' order by first_load_time desc;

4. Get 50 last executed queries

select sql_fulltext from (select * from v$sql where parsing_schema_name 
like 'ORAAGENT' order by first_load_time desc) where rownum < 51;

5. Get 50 executed UPDATE or DELETE queries in a specific time period and sort by load time

select sql_text,sql_fulltext, first_load_time, parsing_schema_name from
(
  select * from v$sql   where parsing_schema_name like 'YOUR_SCHEMA'
    and (sql_text like '%UPDATE %' or sql_text like '%INSERT %') 
and to_timestamp(first_load_time, 'YYYY-MM-DD/HH24:MI:SS') > 
to_timestamp('2021-01-24/09:06:00', 'YYYY-MM-DD/HH24:MI:SS')
  order by first_load_time desc
)
where rownum < 51

You can create your own queries to find out what queries you need to check. Remember this view v$sql doesn’t store prepared statements.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Tag Archives: Scn to Timestamp and Timestamp to Scn

Feb, 2021

Total user count on database:

Users Details Session:

To Find Session Information Details based on SID or SPID or CLIENTPID :

Sort space usage by session:

History of Temp tablespace usage:

Shrink Temp file:

v$parameter for undo:

UNDO tablespaces Free As per expired segments:

does undo have expired segments?

User Generated UNDO

UNDO tablespace usage by USER

Recreating the undo tablespace or resolving to undo issue

ORA-30019: Illegal rollback Segment operation in Automatic Undo mode

Find active, expired, unexpired undo

Find active, expired, and unexpired undo by Size in MB

Undo advisor:

To check SQL Profile :

SQL Baselines:

Extract hint from all profile/baseline/patch :

Fixing the PLAN HASH VALUE:

Purge the old plan hash value from memory:

Oracle RAC Administration and Monitoring Scripts -2

Status and Config Database

Shutdown RAC Database

Startup RAC Database

Shutdown An Instance

Startup and Status An Instance

Add and Remove Database from CRS

Stop, Start and Check ASM via SRVCTL

Stop, Start and Check Service via SRVCTL

Enable/disable Autorestart of Instance and Database

Start, Stop and Check status,configurations of NodeApps

History of executed queries

History of executed queries