Friday, 3 March 2023

Oracle AWR Report Script top sqls' waits

Oracle AWR Report Script top sqls' waits .. 


	
set numf 9999999999999999999999.99

VARIABLE SNAP_ID_MIN NUMBER
VARIABLE SNAP_ID_MAX NUMBER
VARIABLE DBID NUMBER
VARIABLE INSTANCE_NUMBER number

exec select max(snap_id) -1 into :SNAP_ID_MIN from dba_hist_snapshot ;
exec select max(snap_id) into :SNAP_ID_MAX  from dba_hist_snapshot ;
exec select DBID into :DBID from v$database;
exec select INSTANCE_NUMBER into :INSTANCE_NUMBER from v$instance ;


set linesize 300 pagesize 300	
	
	select dbid,
       db_name,
       instance_number,
       inst_name,
       begin_snap_id,
       end_snap_id,
       elapsed,
       (SELECT  sum(e.VALUE-b.value) as diff_value
           FROM DBA_HIST_SYSSTAT  B,
                DBA_HIST_SYSSTAT  E
          WHERE e.dbid = b.dbid
            and e.instance_number = b.instance_number
            and e.STAT_ID = b.STAT_ID
            and B.DBID            = base_info.dbid
            AND B.INSTANCE_NUMBER = base_info.instance_number
            AND B.SNAP_ID         = base_info.begin_snap_id
            AND E.SNAP_ID         = base_info.end_snap_id
            AND B.STAT_NAME       in( 'physical reads') ) as physical_reads,
       (SELECT  sum(e.VALUE-b.value) as diff_value
           FROM DBA_HIST_SYSSTAT  B,
                DBA_HIST_SYSSTAT  E
          WHERE e.dbid = b.dbid
            and e.instance_number = b.instance_number
            and e.STAT_ID = b.STAT_ID
            and B.DBID            = base_info.dbid
            AND B.INSTANCE_NUMBER = base_info.instance_number
            AND B.SNAP_ID         = base_info.begin_snap_id
            AND E.SNAP_ID         = base_info.end_snap_id
            AND B.STAT_NAME       in( 'session logical reads') ) as logical_reads,
       (SELECT SUM(E.TIME_WAITED_MICRO -      NVL(B.TIME_WAITED_MICRO, 0))
          FROM DBA_HIST_SYSTEM_EVENT B, 
               DBA_HIST_SYSTEM_EVENT E
         WHERE B.SNAP_ID(+) = base_info.begin_snap_id
           AND E.SNAP_ID = base_info.end_snap_id
           AND B.DBID(+) = E.DBID
                AND B.INSTANCE_NUMBER(+) = E.INSTANCE_NUMBER
           AND E.INSTANCE_NUMBER = base_info.instance_number
           AND B.EVENT_ID(+) = E.EVENT_ID
           AND E.WAIT_CLASS = 'User I/O') as UIOW_TIME,--STAT_UIOW_TIME
       (SELECT  e.VALUE-b.value as diff_value
          FROM DBA_HIST_SYS_TIME_MODEL B,
               DBA_HIST_SYS_TIME_MODEL E
          WHERE e.dbid = b.dbid
            and e.instance_number = b.instance_number
            and e.STAT_ID = b.STAT_ID
            and B.DBID            = base_info.dbid
            AND B.INSTANCE_NUMBER = base_info.instance_number
            AND B.SNAP_ID         = base_info.begin_snap_id
            AND E.SNAP_ID         = base_info.end_snap_id
            AND B.STAT_NAME       = 'DB time'
 ) as db_time,
       (SELECT sum(E.VALUE)-sum(B.VALUE) as STAT_TXN
            FROM DBA_HIST_SYSSTAT B,
                 DBA_HIST_SYSSTAT E
           WHERE b.dbid          = e.dbid 
           and b.instance_number = e.instance_number 
           and b.STAT_ID         = e.STAT_ID
           AND E.DBID            = base_info.dbid
           and e.instance_number = base_info.instance_number
           and b.snap_id         = base_info.begin_snap_id
           and e.snap_id          = base_info.end_snap_id 
           AND e.STAT_NAME in ('user rollbacks','user commits')
         ) as transaction_count
  from (with db_info as (select d.dbid            dbid,
                                d.name            db_name,
                                i.instance_number instance_number,
                                i.instance_name   inst_name
                           from v$database d, v$instance i), 
         snap_info as (select c.*,
                              EXTRACT(DAY FROM c.max_end_interval_time - c.min_end_interval_time) * 86400
                               + EXTRACT(HOUR FROM c.max_end_interval_time - c.min_end_interval_time) * 3600
                               + EXTRACT(MINUTE FROM c.max_end_interval_time - c.min_end_interval_time) * 60
                               + EXTRACT(SECOND FROM c.max_end_interval_time - c.min_end_interval_time) ELAPSED
                         from (select min(snap_id) begin_snap_id,
                                      max(snap_id) end_snap_id,
                                      min(END_INTERVAL_TIME) as  min_end_interval_time,
                                      max(END_INTERVAL_TIME) as max_end_interval_time
                                 from dba_hist_snapshot sn
                                where sn.begin_interval_time >= trunc(sysdate) - 1
                                  and sn.begin_interval_time < sysdate) c
                        )        
       select * from db_info, snap_info) base_info;
	   
	   
                      DBID DB_NAME              INSTANCE_NUMBER INST_NAME                     BEGIN_SNAP_ID                END_SNAP_ID                    ELAPSED             PHYSICAL_READS              LOGICAL_READS                  UIOW_TIME                    DB_TIME          TRANSACTION_COUNT
-------------------------- --------- -------------------------- ---------------- -------------------------- -------------------------- -------------------------- -------------------------- -------------------------- -------------------------- -------------------------- --------------------------
             1222414252.00 XXRAC                           1.00 xxrac1                             12280.00                   12312.00                  115204.08                41865968.00               392896011.00             14553390318.00             39638901871.00                    5463.00


	

AWR SQL  ordered by Reads 
	


set linesize 600 pagesize 300

col "SQL Text" for a70 wrap
define phyr='41865968.00'	
	   
       select *
     from (select sqt.dskr as "Physical Reads",
                  sqt.exec as "Executions",
                  round(decode(sqt.exec, 0, to_number(null),(sqt.dskr / sqt.exec)),2) as "Reads per Exec",
                  round(decode(&phyr, 0, to_number(null),(100 * sqt.dskr)/&phyr),2) as  "%Total",
                  round(nvl((sqt.elap/1000000), to_number(null)),2) as "Elapsed Time (s)",
                  round(decode(sqt.elap, 0, to_number(null),(100 * (sqt.cput / sqt.elap))),2) as "%CPU",
                  round(decode(sqt.elap, 0, to_number(null), (100 * (sqt.uiot / sqt.elap))),2) as "%IO",
                  sqt.sql_id as "SQL Id",
                  decode(sqt.module, null,null, 'Module: ' || sqt.module) as "SQL Module",
            	    nvl(dbms_lob.substr(st.sql_text,4000,1), to_clob('** SQL Text Not Available **')) as  "SQL Text"
           from (select sql_id, max(module) module,
                        sum(disk_reads_delta) dskr, sum(executions_delta) exec,
                        sum(cpu_time_delta) cput, sum(elapsed_time_delta) elap,
                        sum(iowait_delta) uiot
                  from dba_hist_sqlstat
                  where 1=1
                     and   instance_number = :INSTANCE_NUMBER
                    and :SNAP_ID_MIN < snap_id
                    and snap_id        <= :SNAP_ID_MAX
               group by sql_id) sqt,
               dba_hist_sqltext st
           where st.sql_id(+)             = sqt.sql_id
              and &phyr                    > 0
           order by nvl(sqt.dskr, -1) desc, sqt.sql_id)
    where rownum < 60 and  (rownum <= 10 or   "%Total" > 0.1)
	
	
	
            Physical Reads                 Executions             Reads per Exec                     %Total           Elapsed Time (s)                       %CPU                        %IO SQL Id        SQL Module                                                               SQL Text
-------------------------- -------------------------- -------------------------- -------------------------- -------------------------- -------------------------- -------------------------- ------------- ------------------------------------------------------------------------ ----------------------------------------------------------------------
                                                                                                                                                                                                                      bytes = :25,
                                                                                                                                                                                                                      other_tag = :26,
                                                                                                                                                                                                                      partition_start = :
                                                                                                                                                                                                                   27,                                                 partition_s
                                                                                                                                                                                                                   top = :28,                                                          par
                                                                                                                                                                                                                   tition_id = :29,
                                                                                                                                                                                                                other = :30,
                                                                                                                                                                                                                distribution = :31,
                                                                                                                                                                                                                cpu_cost = :32,
           





SQL statements  Top SQL

	
set linesize 500 pagesize 300
	
VARIABLE SNAP_ID_MIN NUMBER
VARIABLE SNAP_ID_MAX NUMBER
VARIABLE DBID NUMBER
VARIABLE INSTANCE_NUMBER number

exec select max(snap_id) -1 into :SNAP_ID_MIN from dba_hist_snapshot ;
exec select max(snap_id) into :SNAP_ID_MAX  from dba_hist_snapshot ;
exec select DBID into :DBID from v$database;
exec select INSTANCE_NUMBER into :INSTANCE_NUMBER from v$instance ;

	
Logical read TOP 10
	
	select *
     from (select sqt.dskr,
                  sqt.exec,
                  decode(sqt.exec, 0, to_number(null), (sqt.dskr / sqt.exec)),
                  (100 * sqt.dskr) /  (SELECT sum(e.VALUE) - sum(b.value)
                     FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
                    WHERE B.SNAP_ID = :SNAP_ID_MIN
                      AND E.SNAP_ID = :SNAP_ID_MAX
                      AND B.DBID = :DBID
                      AND E.DBID = :DBID
                      AND B.INSTANCE_NUMBER = :INSTANCE_NUMBER 
                      AND E.INSTANCE_NUMBER = :INSTANCE_NUMBER 
                      and e.STAT_NAME = 'physical reads'
                      and b.stat_name = 'physical reads') norm_val,
                  nvl((sqt.cput / 1000000), to_number(null)),
                  nvl((sqt.elap / 1000000), to_number(null)),
                  sqt.sql_id,
                  decode(sqt.module, null, null, 'Module: ' || sqt.module),
                  nvl(st.sql_text, to_clob('** SQL Text Not Available **'))
             from (select sql_id,
                          max(module) module,
                          sum(disk_reads_delta) dskr,
                          sum(executions_delta) exec,
                          sum(cpu_time_delta) cput,
                          sum(elapsed_time_delta) elap
                     from dba_hist_sqlstat
                    where dbid = :DBID
                      and instance_number = :INSTANCE_NUMBER 
                      and :SNAP_ID_MIN < snap_id
                      and snap_id <= :SNAP_ID_MAX
                    group by sql_id) sqt,
                  dba_hist_sqltext st
            where st.sql_id(+) = sqt.sql_id
              and st.dbid(+) = :DBID
              and (SELECT sum(e.VALUE) - sum(b.value)
                     FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
                    WHERE B.SNAP_ID = :SNAP_ID_MIN
                      AND E.SNAP_ID = :SNAP_ID_MAX
                      AND B.DBID = :DBID
                      AND E.DBID = :DBID
                      AND B.INSTANCE_NUMBER = :INSTANCE_NUMBER 
                      AND E.INSTANCE_NUMBER = :INSTANCE_NUMBER 
                      and e.STAT_NAME = 'physical reads'
                      and b.stat_name = 'physical reads') > 0
            order by nvl(sqt.dskr, -1) desc, sqt.sql_id)
    where rownum < 65
	and(rownum <= 10
    or norm_val > 1)

	
	

      DSKR       EXEC DECODE(SQT.EXEC,0,TO_NUMBER(NULL),(SQT.DSKR/SQT.EXEC))   NORM_VAL NVL((SQT.CPUT/1000000),TO_NUMBER(NULL)) NVL((SQT.ELAP/1000000),TO_NUMBER(NULL)) SQL_ID        DECODE(SQT.MODULE,NULL,NULL,'MODULE:'||SQT.MODULE)                       NVL(ST.SQL_TEXT,TO_CLOB('**SQLTEXTNOTAVAILABLE**'))
---------- ---------- ------------------------------------------------------ ---------- --------------------------------------- --------------------------------------- ------------- ------------------------------------------------------------------------ --------------------------------------------------------------------------------
   1303904        384                                             3395.58333 99.8915974                              256.457058                              715.109381 8cnh50qfgwg73 Module: DBMS_SCHEDULER          SELECT NVL(SUM(BYTES),0) FROM SYS.DBA_FREE_SPACE WHERE TABLESPACE_NAME = :B1
  



  
Physical reading TOP 10
	
select *
from (select sqt.dskr Physical_Reads,
sqt.exec Executions,
decode(sqt.exec, 0, to_number(null), (sqt.dskr / sqt.exec)) Reads_per_Exec ,
(100 * sqt.dskr) /
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = :SNAP_ID_MIN
AND E.SNAP_ID = :SNAP_ID_MAX
AND B.DBID = :DBID
AND E.DBID = :DBID
AND B.INSTANCE_NUMBER = 1
AND E.INSTANCE_NUMBER = 1
and e.STAT_NAME = 'physical reads'
and b.stat_name = 'physical reads') Total_rate,
nvl((sqt.cput / 1000000), to_number(null)) CPU_Time_s,
nvl((sqt.elap / 1000000), to_number(null)) Elapsed_Time_s,
sqt.sql_id,
decode(sqt.module, null, null, 'Module: ' || sqt.module) SQL_Module,
nvl(st.sql_text, to_clob('** SQL Text Not Available **')) SQL_Text
from (select sql_id,
max(module) module,
sum(disk_reads_delta) dskr,
sum(executions_delta) exec,
sum(cpu_time_delta) cput,
sum(elapsed_time_delta) elap
from dba_hist_sqlstat
where dbid = :DBID
and instance_number = 1
and :SNAP_ID_MIN < snap_id
and snap_id <= :SNAP_ID_MAX
group by sql_id) sqt,
dba_hist_sqltext st
where st.sql_id(+) = sqt.sql_id
and st.dbid(+) = :DBID
and (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = :SNAP_ID_MIN
AND E.SNAP_ID = :SNAP_ID_MAX
AND B.DBID = :DBID
AND E.DBID = :DBID
AND B.INSTANCE_NUMBER = 1
AND E.INSTANCE_NUMBER = 1
and e.STAT_NAME = 'physical reads'
and b.stat_name = 'physical reads') > 0
order by nvl(sqt.dskr, -1) desc, sqt.sql_id)
where rownum < 65 and(rownum <= 10
or Total_rate > 1);





Physical reading TOP 10

select *
from (select sqt.dskr Physical_Reads,
sqt.exec Executions,
decode(sqt.exec, 0, to_number(null), (sqt.dskr / sqt.exec)) Reads_per_Exec ,
(100 * sqt.dskr) /
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = :SNAP_ID_MIN
AND E.SNAP_ID = :SNAP_ID_MAX
AND B.DBID = :DBID
AND E.DBID = :DBID
AND B.INSTANCE_NUMBER = 1
AND E.INSTANCE_NUMBER = 1
and e.STAT_NAME = 'physical reads'
and b.stat_name = 'physical reads') Total_rate,
nvl((sqt.cput / 1000000), to_number(null)) CPU_Time_s,
nvl((sqt.elap / 1000000), to_number(null)) Elapsed_Time_s,
sqt.sql_id,
decode(sqt.module, null, null, 'Module: ' || sqt.module) SQL_Module,
nvl(st.sql_text, to_clob('** SQL Text Not Available **')) SQL_Text
from (select sql_id,
max(module) module,
sum(disk_reads_delta) dskr,
sum(executions_delta) exec,
sum(cpu_time_delta) cput,
sum(elapsed_time_delta) elap
from dba_hist_sqlstat
where dbid = :DBID
and instance_number = 1
and :SNAP_ID_MIN < snap_id
and snap_id <= :SNAP_ID_MAX
group by sql_id) sqt,
dba_hist_sqltext st
where st.sql_id(+) = sqt.sql_id
and st.dbid(+) = :DBID
and (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = :SNAP_ID_MIN
AND E.SNAP_ID = :SNAP_ID_MAX
AND B.DBID = :DBID
AND E.DBID = :DBID
AND B.INSTANCE_NUMBER = 1
AND E.INSTANCE_NUMBER = 1
and e.STAT_NAME = 'physical reads'
and b.stat_name = 'physical reads') > 0
order by nvl(sqt.dskr, -1) desc, sqt.sql_id)
where rownum < 65 and(rownum <= 10
or Total_rate > 1);




            PHYSICAL_READS                 EXECUTIONS             READS_PER_EXEC                 TOTAL_RATE                 CPU_TIME_S             ELAPSED_TIME_S SQL_ID        SQL_MODULE         SQL_TEXT
-------------------------- -------------------------- -------------------------- -------------------------- -------------------------- -------------------------- ------------- ------------------ ----------------------------------------------------------------------
                1303904.00                     384.00                    3395.58                      99.89                     256.46                     715.11 8cnh50qfgwg73 Module: DBMS_SCHED SELECT NVL(SUM(BYTES),0) FROM SYS.DBA_FREE_SPACE WHERE TABLESPACE_NAME
                                                                                                                                                                                ULER                = :B1






CPU consumption TOP 10


col SQL_MODULE  for a18
select *
from (select nvl((sqt.elap / 1000000), to_number(null)) Elapsed_Time_s,
nvl((sqt.cput / 1000000), to_number(null)) CPU_Time_s,
sqt.exec Executions,
decode(sqt.exec,
0,
to_number(null),
(sqt.elap / sqt.exec / 1000000)) Elap_per_Exec_s,
(100 *
(sqt.elap / (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = :SNAP_ID_MIN
AND E.SNAP_ID = :SNAP_ID_MAX
AND B.DBID = :DBID
AND E.DBID = :DBID
AND B.INSTANCE_NUMBER = 1
AND E.INSTANCE_NUMBER = 1
and e.STAT_NAME = 'DB time'
and b.stat_name = 'DB time')))/1000 Total_DB_Time_rate,
sqt.sql_id,
to_clob(decode(sqt.module,
null,
null,
'Module: ' || sqt.module)) SQL_Module,
nvl(st.sql_text, to_clob(' ** SQL Text Not Available ** ')) SQL_Text
from (select sql_id,
max(module) module,
sum(elapsed_time_delta) elap,
sum(cpu_time_delta) cput,
sum(executions_delta) exec
from dba_hist_sqlstat
where dbid = :DBID
and instance_number = 1
and :SNAP_ID_MIN < snap_id
and snap_id <= :SNAP_ID_MAX
group by sql_id) sqt,
dba_hist_sqltext st
where st.sql_id(+) = sqt.sql_id
and st.dbid(+) = :DBID
order by nvl(sqt.cput, -1) desc, sqt.sql_id)
where rownum < 65
and (rownum <= 10 or Total_DB_Time_rate > 1);


            ELAPSED_TIME_S                 CPU_TIME_S                 EXECUTIONS            ELAP_PER_EXEC_S         TOTAL_DB_TIME_RATE SQL_ID        SQL_MODULE         SQL_TEXT
-------------------------- -------------------------- -------------------------- -------------------------- -------------------------- ------------- ------------------ ----------------------------------------------------------------------
                    715.11                     256.46                     384.00                       1.86                     309.17 8cnh50qfgwg73 Module: DBMS_SCHED SELECT NVL(SUM(BYTES),0) FROM SYS.DBA_FREE_SPACE WHERE TABLESPACE_NAME
                                                                                                                                                     ULER                = :B1
 


Execution time TOP 10

select *
from (select nvl((sqt.elap / 1000000), to_number(null)) Elapsed_Time_s,
nvl((sqt.cput / 1000000), to_number(null)) CPU_Time_s,
sqt.exec Executions,
decode(sqt.exec,
0,
to_number(null),
(sqt.elap / sqt.exec / 1000000)) Elap_per_Exec_s,
(100 *
(sqt.elap / (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = :SNAP_ID_MIN
AND E.SNAP_ID = :SNAP_ID_MAX
AND B.DBID = :DBID
AND E.DBID = :DBID
AND B.INSTANCE_NUMBER = 1
AND E.INSTANCE_NUMBER = 1
and e.STAT_NAME = 'DB time'
and b.stat_name = 'DB time')))/1000 Total_DB_Time_rate,
sqt.sql_id,
to_clob(decode(sqt.module,
null,
null,
'Module: ' || sqt.module)) SQL_Module,
nvl(st.sql_text, to_clob(' ** SQL Text Not Available ** ')) SQL_Text
from (select sql_id,
max(module) module,
sum(elapsed_time_delta) elap,
sum(cpu_time_delta) cput,
sum(executions_delta) exec
from dba_hist_sqlstat
where dbid = :DBID
and instance_number = 1
and :SNAP_ID_MIN < snap_id
and snap_id <= :SNAP_ID_MAX
group by sql_id) sqt,
dba_hist_sqltext st
where st.sql_id(+) = sqt.sql_id
and st.dbid(+) = :DBID
order by nvl(sqt.elap, -1) desc, sqt.sql_id)
where rownum < 65
and (rownum <= 10 or Total_DB_Time_rate > 1);




          ELAPSED_TIME_S                 CPU_TIME_S                 EXECUTIONS            ELAP_PER_EXEC_S         TOTAL_DB_TIME_RATE SQL_ID        SQL_MODULE         SQL_TEXT
-------------------------- -------------------------- -------------------------- -------------------------- -------------------------- ------------- ------------------ ----------------------------------------------------------------------
                    715.11                     256.46                     384.00                       1.86                     309.17 8cnh50qfgwg73 Module: DBMS_SCHED SELECT NVL(SUM(BYTES),0) FROM SYS.DBA_FREE_SPACE WHERE TABLESPACE_NAME
                                                                                                                                                     ULER                = :B1





	 

select substr(sql_text,1,40), count(*)
from gv$sqlarea
group by substr(sql_text,1,40) having count(*) > 50;


col USERNAME for a20
col sql_text for a70 wrap
col BEGIN_INTERVAL_TIME for a29	
SELECT t.sql_id,
  dbms_lob.substr(q.SQL_TEXT,100,1) sql_text,
  t.PARSING_SCHEMA_NAME username,
  t.executions_delta exec_count,
  begin_interval_time,  -- 5
  ROUND(SUM(t.elapsed_time_delta/1000000)/SUM(t.executions_delta),4) time_exec -- 6
FROM dba_hist_sqlstat t,
  dba_hist_snapshot s,
  DBA_HIST_SQLTEXT q
WHERE t.snap_id           = s.snap_id
AND t.dbid                = s.dbid
AND q.sql_id              =t.sql_id
AND t.instance_number     = s.instance_number
AND t.executions_delta   IS NOT NULL
AND t.elapsed_time_delta IS NOT NULL
AND t.executions_delta    > 0
AND s.begin_interval_time BETWEEN TRUNC(sysdate)-1 AND TRUNC(sysdate)   ---- 1Day
AND t.PARSING_SCHEMA_NAME NOT IN ('SYS','SYSTEM','DBSNMP') -- yesterday's stats
GROUP BY t.sql_id,
  dbms_lob.substr(q.SQL_TEXT,100,1),
  PARSING_SCHEMA_NAME,
  t.executions_delta,
  s.begin_interval_time
ORDER BY 5,6 DESC;


SQL_ID        SQL_TEXT                                                               USERNAME                             EXEC_COUNT BEGIN_INTERVAL_TIME                            TIME_EXEC
------------- ---------------------------------------------------------------------- -------------------- -------------------------- ----------------------------- --------------------------
bcv9qynmu1nv9 select sys.dbms_standard.dictionary_obj_type from dual                 MDSYS                                    352.00 01-MAR-23 10.00.24.829 PM                            .00






Most important system statiscts/performance overview:



set linesize 400 pagesize 300

col MAXIMUM    for 999999999.999
col AVERAGE for 999999999.999


col METRIC_NAME for a30
SELECT begin_time,
  CASE METRIC_NAME
    WHEN 'SQL Service Response Time'
    THEN 'SQL Service Response
Time (secs)'
    WHEN 'Response Time Per Txn'
    THEN 'Response Time Per Txn
(secs)'
    ELSE METRIC_NAME
  END METRIC_NAME,
  CASE METRIC_NAME
    WHEN 'SQL Service Response Time'
    THEN ROUND((MINVAL / 100),2)
    WHEN 'Response Time Per Txn'
    THEN ROUND((MINVAL / 100),2)
    ELSE MINVAL
  END MININUM,
  CASE METRIC_NAME
    WHEN 'SQL Service Response Time'
    THEN ROUND((MAXVAL / 100),2)
    WHEN 'Response Time Per Txn'
    THEN ROUND((MAXVAL / 100),2)
    ELSE MAXVAL
  END MAXIMUM,
  CASE METRIC_NAME
    WHEN 'SQL Service Response Time'
    THEN ROUND((AVERAGE / 100),2)
    WHEN 'Response Time Per Txn'
    THEN ROUND((AVERAGE / 100),2)
    ELSE AVERAGE
  END AVERAGE
FROM SYS.DBA_HIST_SYSMETRIC_SUMMARY
WHERE METRIC_NAME IN ('CPU Usage Per Sec', 'CPU Usage Per Txn', 'Database CPU Time Ratio', 'Database Wait Time Ratio', 'Executions Per Sec', 'Executions Per Txn', 'Response Time Per Txn', 'SQL Service Response Time', 'User Transaction Per Sec','User Commits Per Sec')
--AND BEGIN_TIME BETWEEN sysdate -1 and sysdate
AND BEGIN_TIME > sysdate - interval '300' minute
ORDER BY 1;


BEGIN_TIM METRIC_NAME                       MININUM        MAXIMUM        AVERAGE
--------- ------------------------------ ---------- -------------- --------------
03-MAR-23 User Transaction Per Sec                0         34.106         17.592
03-MAR-23 Executions Per Txn                      0       1175.000        132.021
03-MAR-23 Response Time Per Txn                   0      51142.100       2929.060
          (secs)

Thursday, 2 March 2023

Oracle Mini Awr report ....






From !!!

https://github.com/abdulirfan3/Oracle_SQL_Scripts/blob/master/capture_awr_stats_mini_awr.sql


capture_awr_stats_mini_awr.sql



save in  1awr.sql

----- =============================================================================================================================================
set linesize 300 pagesize 300

VARIABLE SNAP_ID_MIN NUMBER
VARIABLE SNAP_ID_MAX NUMBER
VARIABLE DBID NUMBER
VARIABLE INSTANCE_NUMBER number

exec select max(snap_id) -1 into :SNAP_ID_MIN from dba_hist_snapshot ;
exec select max(snap_id) into :SNAP_ID_MAX  from dba_hist_snapshot ;
exec select DBID into :DBID from v$database;
exec select INSTANCE_NUMBER into :INSTANCE_NUMBER from v$instance ;



col MODULE for A20
col ACTION for  A25

col PARSING_SCHEMA_NAME for a20

set define '&'
set concat '~'
set colsep " "
set pagesize 50000
SET ARRAYSIZE 5000
REPHEADER OFF
REPFOOTER OFF


ALTER SESSION SET WORKAREA_SIZE_POLICY = manual;
ALTER SESSION SET SORT_AREA_SIZE = 268435456;


set timing off

set serveroutput on
set verify off
column cnt_dbid_1 new_value CNT_DBID noprint

define NUM_DAYS = 01
define SQL_TOP_N = 100
define AWR_MINER_VER = 4.0.10
define CAPTURE_HOST_NAMES = 'YES'

alter session set cursor_sharing = exact;





define DIAG_PACK_LICENSE='YES'


whenever sqlerror exit
set serveroutput on
begin
    if upper('&DIAG_PACK_LICENSE') = 'YES' then
                null;
        else
        dbms_output.put_line('This script will now exit.');
        execute immediate 'bogus statement to force exit';
    end if;
end;
/

whenever sqlerror continue

SELECT count(DISTINCT dbid) cnt_dbid_1
FROM dba_hist_database_instance;
 --where rownum = 1


define DBID = ' '
column :DBID_1 new_value DBID noprint
variable DBID_1 varchar2(30)

define DB_VERSION = 0
column :DB_VERSION_1 new_value DB_VERSION noprint
variable DB_VERSION_1 number



set feedback off
declare
        version_gte_11_2        varchar2(30);
        l_sql                           varchar2(32767);
        l_variables             varchar2(1000) := ' ';
        l_block_size            number;
begin
        :DB_VERSION_1 :=  dbms_db_version.version + (dbms_db_version.release / 10);
        dbms_output.put_line('Database IDs in this Repository:');



        for c1 in (select distinct dbid,db_name FROM dba_hist_database_instance order by db_name)
        loop
                dbms_output.put_line(rpad(c1.dbid,35)||c1.db_name);
        end loop; --c1

        if to_number(&CNT_DBID) > 1 then
                :DBID_1 := ' ';
        else

                SELECT DISTINCT dbid into :DBID_1
                                         FROM dba_hist_database_instance
                                        where rownum = 1;


        end if;

        --l_variables := l_variables||'ver_gte_11_2:TRUE';

        if :DB_VERSION_1  >= 11.2 then
                l_variables := l_variables||'ver_gte_11_2:TRUE';
        else
                l_variables := l_variables||'ver_gte_11_2:FALSE';
        end if;

        if :DB_VERSION_1  >= 11.1 then
                l_variables := l_variables||',ver_gte_11_1:TRUE';
        else
                l_variables := l_variables||',ver_gte_11_1:FALSE';
        end if;

        --alter session set plsql_ccflags = 'debug_flag:true';
        l_sql := q'[alter session set plsql_ccflags =']'||l_variables||q'[']';



        execute immediate l_sql;
end;
/






select :DBID_1 from dual;
select :DB_VERSION_1 from dual;





whenever sqlerror exit
set serveroutput on
begin
    if length(:DBID) > 4 then
                null;
        else
        dbms_output.put_line('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
        dbms_output.put_line('You must choose a database ID.');
        dbms_output.put_line('This script will now exit.');
                dbms_output.put_line('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
        execute immediate 'bogus statement to force exit';
    end if;
end;
/

whenever sqlerror continue

REM set heading off

select :DBID a from dual;

column db_name1 new_value DBNAME
--prompt Will export AWR data for the following Database:

SELECT dbid,db_name db_name1
FROM dba_hist_database_instance
where dbid = :DBID
and rownum = 1
;


define T_WAITED_MICRO_COL = 'TIME_WAITED_MICRO'
column :T_WAITED_MICRO_COL_1 new_value T_WAITED_MICRO_COL noprint
variable T_WAITED_MICRO_COL_1 varchar2(30)

begin
        if :DB_VERSION_1  >= 11.1 then
                :T_WAITED_MICRO_COL_1 := 'TIME_WAITED_MICRO_FG';
        else
                :T_WAITED_MICRO_COL_1 := 'TIME_WAITED_MICRO';
        end if;

end;
/

select :T_WAITED_MICRO_COL_1 from dual;

column DB_BLOCK_SIZE_1 new_value DB_BLOCK_SIZE noprint
with inst as (
select min(instance_number) inst_num
  from dba_hist_snapshot
  where dbid = :DBID
        )
SELECT VALUE DB_BLOCK_SIZE_1
        FROM DBA_HIST_PARAMETER
        WHERE dbid = :DBID
        and PARAMETER_NAME = 'db_block_size'
        AND snap_id = (SELECT MAX(snap_id) FROM dba_hist_osstat WHERE dbid = :DBID AND instance_number = (select inst_num from inst))
   AND instance_number = (select inst_num from inst);


--accept SNAP_ID_MIN prompt "Enter Min SnapID: "
--accept SNAP_ID_MAX prompt "Enter Max SnapID: "





column FILE_NAME new_value SPOOL_FILE_NAME noprint
select 'C:\oracle\sql\spool\'||'awr-hist-'||:DBID||'-'||'&DBNAME'||'-'||ltrim(':SNAP_ID_MIN')||'-'||ltrim(':SNAP_ID_MAX')||'.out' FILE_NAME from dual;
spool &SPOOL_FILE_NAME





Prompt
prompt ~~SNAP_ID Info~~

select * from(
select snap_id, begin_interval_time, end_interval_time, FLUSH_ELAPSED,snap_level, startup_time
from dba_hist_snapshot order by 2 desc
) where snap_id between ':SNAP_ID_MIN' and ':SNAP_ID_MAX';

prompt
-- ##############################################################################################
REPHEADER ON
REPFOOTER ON

set linesize 1000
set numwidth 10
set wrap off
set heading on
set trimspool on
set feedback off




set serveroutput on
DECLARE
    l_pad_length number :=60;
        l_hosts varchar2(4000);
        l_dbid  number;
BEGIN


    dbms_output.put_line('~~BEGIN-OS-INFORMATION~~');
    dbms_output.put_line(rpad('STAT_NAME',l_pad_length)||' '||'STAT_VALUE');
    dbms_output.put_line(rpad('-',l_pad_length,'-')||' '||rpad('-',l_pad_length,'-'));

    FOR c1 IN (
                        with inst as (
                select min(instance_number) inst_num
                  from dba_hist_snapshot
                  where dbid = :DBID
                        and snap_id BETWEEN to_number(:SNAP_ID_MIN) and to_number(:SNAP_ID_MAX))
        SELECT
                      CASE WHEN stat_name = 'PHYSICAL_MEMORY_BYTES' THEN 'PHYSICAL_MEMORY_GB' ELSE stat_name END stat_name,
                      CASE WHEN stat_name IN ('PHYSICAL_MEMORY_BYTES') THEN round(VALUE/1024/1024/1024,2) ELSE VALUE END stat_value
                  FROM dba_hist_osstat
                 WHERE dbid = :DBID
                   AND snap_id = (SELECT MAX(snap_id) FROM dba_hist_osstat WHERE dbid = :DBID AND instance_number = (select inst_num from inst))
                                   AND instance_number = (select inst_num from inst)
                   AND (stat_name LIKE 'NUM_CPU%'
                   OR stat_name IN ('PHYSICAL_MEMORY_BYTES')))
    loop
        dbms_output.put_line(rpad(c1.stat_name,l_pad_length)||' '||c1.stat_value);
    end loop; --c1

        for c1 in (SELECT CPU_COUNT,CPU_CORE_COUNT,CPU_SOCKET_COUNT
                                 FROM DBA_CPU_USAGE_STATISTICS
                                where dbid = :DBID
                                  and TIMESTAMP = (select max(TIMESTAMP) from DBA_CPU_USAGE_STATISTICS where dbid = :DBID )
                                  AND ROWNUM = 1)
        loop
                dbms_output.put_line(rpad('!CPU_COUNT',l_pad_length)||' '||c1.CPU_COUNT);
                dbms_output.put_line(rpad('!CPU_CORE_COUNT',l_pad_length)||' '||c1.CPU_CORE_COUNT);
                dbms_output.put_line(rpad('!CPU_SOCKET_COUNT',l_pad_length)||' '||c1.CPU_SOCKET_COUNT);
        end loop;

        for c1 in (SELECT distinct platform_name FROM sys.GV_$DATABASE
                                where dbid = :DBID
                                and rownum = 1)
        loop
                dbms_output.put_line(rpad('!PLATFORM_NAME',l_pad_length)||' '||c1.platform_name);
        end loop;



        FOR c2 IN (SELECT
                                                $IF $$VER_GTE_11_2 $THEN
                                                        REPLACE(platform_name,' ','_') platform_name,
                                                $ELSE
                                                        'None' platform_name,
                                                $END
                                                VERSION,db_name,DBID FROM dba_hist_database_instance
                                                WHERE dbid = :DBID
                                                and startup_time = (select max(startup_time) from dba_hist_database_instance WHERE dbid = :DBID )
                                                AND ROWNUM = 1)
    loop
        dbms_output.put_line(rpad('PLATFORM_NAME',l_pad_length)||' '||c2.platform_name);
        dbms_output.put_line(rpad('VERSION',l_pad_length)||' '||c2.VERSION);
        dbms_output.put_line(rpad('DB_NAME',l_pad_length)||' '||c2.db_name);
        dbms_output.put_line(rpad('DBID',l_pad_length)||' '||c2.DBID);
    end loop; --c2

    FOR c3 IN (SELECT count(distinct s.instance_number) instances
                             FROM dba_hist_database_instance i,dba_hist_snapshot s
                                WHERE i.dbid = s.dbid
                                  and i.dbid = :DBID
                                  AND s.snap_id BETWEEN :SNAP_ID_MIN AND :SNAP_ID_MAX)
    loop
        dbms_output.put_line(rpad('INSTANCES',l_pad_length)||' '||c3.instances);
    end loop; --c3


        FOR c4 IN (SELECT distinct regexp_replace(host_name,'^([[:alnum:]]+)\..*$','\1')  host_name
                             FROM dba_hist_database_instance i,dba_hist_snapshot s
                                WHERE i.dbid = s.dbid
                                  and i.dbid = :DBID
                  and s.startup_time = i.startup_time
                                  AND s.snap_id BETWEEN :SNAP_ID_MIN AND :SNAP_ID_MAX
                            order by 1)
    loop
                if '&CAPTURE_HOST_NAMES' = 'YES' then
                        l_hosts := l_hosts || c4.host_name ||',';
                end if;
        end loop; --c4
        l_hosts := rtrim(l_hosts,',');
        dbms_output.put_line(rpad('HOSTS',l_pad_length)||' '||l_hosts);

        FOR c5 IN (SELECT sys_context('USERENV', 'MODULE') module FROM DUAL)
    loop
        dbms_output.put_line(rpad('MODULE',l_pad_length)||' '||c5.module);
    end loop; --c5



        dbms_output.put_line(rpad('AWR_MINER_VER',l_pad_length)||' &AWR_MINER_VER');
        dbms_output.put_line('~~END-OS-INFORMATION~~');
END;
/

prompt
prompt


-- ##############################################################################################

REPHEADER PAGE LEFT '~~BEGIN-MEMORY~~'
REPFOOTER PAGE LEFT '~~END-MEMORY~~'

SELECT snap_id,
    instance_number,
    MAX (DECODE (stat_name, 'SGA', stat_value, NULL)) "SGA",
    MAX (DECODE (stat_name, 'PGA', stat_value, NULL)) "PGA",
    MAX (DECODE (stat_name, 'SGA', stat_value, NULL)) + MAX (DECODE (stat_name, 'PGA', stat_value,
    NULL)) "TOTAL"
   FROM
    (SELECT snap_id,
        instance_number,
        ROUND (SUM (bytes) / 1024 / 1024 / 1024, 1) stat_value,
        MAX ('SGA') stat_name
       FROM dba_hist_sgastat
      WHERE dbid = :DBID
        AND snap_id BETWEEN :SNAP_ID_MIN AND :SNAP_ID_MAX
   GROUP BY snap_id,
        instance_number
  UNION ALL
     SELECT snap_id,
        instance_number,
        ROUND (value / 1024 / 1024 / 1024, 1) stat_value,
        'PGA' stat_name
       FROM dba_hist_pgastat
      WHERE dbid = :DBID
        AND snap_id BETWEEN :SNAP_ID_MIN AND :SNAP_ID_MAX
        AND NAME = 'total PGA allocated'
    )
GROUP BY snap_id,
    instance_number
ORDER BY snap_id,
    instance_number;

prompt
prompt

-- ##############################################################################################


REPHEADER PAGE LEFT '~~BEGIN-MEMORY-SGA-ADVICE~~'
REPFOOTER PAGE LEFT '~~END-MEMORY-SGA-ADVICE~~'

select snap_id,instance_number,sga_target_gb,size_factor,ESTD_PHYSICAL_READS,lead_read_diff
from(
with top_n_dbtime as(
select snap_id from(
select snap_id, sum(average) dbtime_p_s,
  dense_rank() over (order by sum(average) desc nulls last) rnk
 from dba_hist_sysmetric_summary
where dbid = :DBID
 and snap_id between :SNAP_ID_MIN and :SNAP_ID_MAX
 and metric_name = 'Database Time Per Sec'
 group by snap_id)
 where rnk <= 5)
SELECT a.SNAP_ID,
  INSTANCE_NUMBER,
  ROUND(sga_size/1024,1) sga_target_gb,
  sga_size_FACTOR size_factor,
  ESTD_PHYSICAL_READS,
  round((ESTD_PHYSICAL_READS - lead(ESTD_PHYSICAL_READS,1,ESTD_PHYSICAL_READS) over (partition by a.snap_id,instance_number order by sga_size_FACTOR asc nulls last)),1) lead_read_diff,
  min(sga_size_FACTOR) over (partition by a.snap_id,instance_number) min_factor,
  max(sga_size_FACTOR) over (partition by a.snap_id,instance_number) max_factor
FROM DBA_HIST_SGA_TARGET_ADVICE a,top_n_dbtime tn
WHERE dbid          = :DBID
AND a.snap_id         = tn.snap_id)
where (size_factor = 1
or size_factor = min_factor
or size_factor = max_factor
or lead_read_diff > 1)
order by snap_id asc,instance_number, size_factor asc nulls last;


prompt
prompt

-- ##############################################################################################


REPHEADER PAGE LEFT '~~BEGIN-MEMORY-PGA-ADVICE~~'
REPFOOTER PAGE LEFT '~~END-MEMORY-PGA-ADVICE~~'


SELECT SNAP_ID,
  INSTANCE_NUMBER,
  PGA_TARGET_GB,
  SIZE_FACTOR,
  ESTD_EXTRA_MB_RW,
  LEAD_SIZE_DIFF_MB,
  ESTD_PGA_CACHE_HIT_PERCENTAGE
FROM
  ( WITH top_n_dbtime AS
  (SELECT snap_id
  FROM
    (SELECT snap_id,
      SUM(average) dbtime_p_s,
      dense_rank() over (order by SUM(average) DESC nulls last) rnk
    FROM dba_hist_sysmetric_summary
      where dbid = :DBID
      and snap_id between :SNAP_ID_MIN and :SNAP_ID_MAX
    AND metric_name = 'Database Time Per Sec'
    GROUP BY snap_id
    )
  WHERE rnk <= 5
  )
SELECT a.SNAP_ID,
  INSTANCE_NUMBER,
  ROUND(PGA_TARGET_FOR_ESTIMATE/1024/1024/1024,1) pga_target_gb,
  PGA_TARGET_FACTOR size_factor,
  ROUND(ESTD_EXTRA_BYTES_RW  /1024/1024,1) ESTD_EXTRA_MB_RW,
  ROUND((ESTD_EXTRA_BYTES_RW - lead(ESTD_EXTRA_BYTES_RW,1,ESTD_EXTRA_BYTES_RW) over (partition BY a.snap_id,instance_number order by PGA_TARGET_FACTOR ASC nulls last))/1024/1024,1) lead_size_diff_mb,
  ESTD_PGA_CACHE_HIT_PERCENTAGE,
  MIN(PGA_TARGET_FACTOR) over (partition BY a.snap_id,instance_number) min_factor,
  MAX(PGA_TARGET_FACTOR) over (partition BY a.snap_id,instance_number) max_factor
FROM DBA_HIST_PGA_TARGET_ADVICE a,
  top_n_dbtime tn
WHERE dbid = :DBID
AND a.snap_id = tn.snap_id
  )
WHERE (size_factor   = 1
OR size_factor       = min_factor
OR size_factor       = max_factor
OR lead_size_diff_mb > 1)
ORDER BY snap_id ASC,
  instance_number,
  size_factor ASC nulls last;


prompt
prompt

-- ##############################################################################################



REPHEADER PAGE LEFT '~~BEGIN-SIZE-ON-DISK~~'
REPFOOTER PAGE LEFT '~~END-SIZE-ON-DISK~~'
 WITH ts_info as (
select dbid, ts#, tsname, max(block_size) block_size
from dba_hist_datafile
where dbid = :DBID
group by dbid, ts#, tsname),
-- Get the maximum snaphsot id for each day from dba_hist_snapshot
snap_info as (
select dbid,to_char(trunc(end_interval_time,'DD'),'MM/DD/YY') dd, max(s.snap_id) snap_id
FROM dba_hist_snapshot s
where s.snap_id between :SNAP_ID_MIN and :SNAP_ID_MAX
and dbid = :DBID
--where s.end_interval_time > to_date(:start_time,'MMDDYYYY')
--and s.end_interval_time < to_date(:end_time,'MMDDYYYY')
group by dbid,trunc(end_interval_time,'DD'))
-- Sum up the sizes of all the tablespaces for the last snapshot of each day
select s.snap_id, round(sum(tablespace_size*f.block_size)/1024/1024/1024,2) size_gb
from dba_hist_tbspc_space_usage sp,
ts_info f,
snap_info s
WHERE s.dbid = sp.dbid
AND s.dbid = :DBID
 and s.snap_id between :SNAP_ID_MIN and :SNAP_ID_MAX
and s.snap_id = sp.snap_id
and sp.dbid = f.dbid
AND sp.tablespace_id = f.ts#
GROUP BY  s.snap_id,s.dd, s.dbid
order by  s.snap_id;

prompt
prompt
-- ##############################################################################################


REPHEADER PAGE LEFT '~~BEGIN-MAIN-METRICS~~'
REPFOOTER PAGE LEFT '~~END-MAIN-METRICS~~'

 select snap_id "snap",num_interval "dur_m", end_time "end",inst "inst",
  max(decode(metric_name,'Host CPU Utilization (%)',                                    average,null)) "os_cpu",
  max(decode(metric_name,'Host CPU Utilization (%)',                                    maxval,null)) "os_cpu_max",
  max(decode(metric_name,'Host CPU Utilization (%)',                                    STANDARD_DEVIATION,null)) "os_cpu_sd",
  max(decode(metric_name,'Database Wait Time Ratio',                   round(average,1),null)) "db_wait_ratio",
max(decode(metric_name,'Database CPU Time Ratio',                   round(average,1),null)) "db_cpu_ratio",
max(decode(metric_name,'CPU Usage Per Sec',                   round(average/100,3),null)) "cpu_per_s",
max(decode(metric_name,'CPU Usage Per Sec',                   round(STANDARD_DEVIATION/100,3),null)) "cpu_per_s_sd",
max(decode(metric_name,'Host CPU Usage Per Sec',                   round(average/100,3),null)) "h_cpu_per_s",
max(decode(metric_name,'Host CPU Usage Per Sec',                   round(STANDARD_DEVIATION/100,3),null)) "h_cpu_per_s_sd",
max(decode(metric_name,'Average Active Sessions',                   average,null)) "aas",
max(decode(metric_name,'Average Active Sessions',                   STANDARD_DEVIATION,null)) "aas_sd",
max(decode(metric_name,'Average Active Sessions',                   maxval,null)) "aas_max",
max(decode(metric_name,'Database Time Per Sec',                                 average,null)) "db_time",
max(decode(metric_name,'Database Time Per Sec',                                 STANDARD_DEVIATION,null)) "db_time_sd",
max(decode(metric_name,'SQL Service Response Time',                   average,null)) "sql_res_t_cs",
max(decode(metric_name,'Background Time Per Sec',                   average,null)) "bkgd_t_per_s",
max(decode(metric_name,'Logons Per Sec',                            average,null)) "logons_s",
max(decode(metric_name,'Current Logons Count',                      average,null)) "logons_total",
max(decode(metric_name,'Executions Per Sec',                        average,null)) "exec_s",
max(decode(metric_name,'Hard Parse Count Per Sec',                  average,null)) "hard_p_s",
max(decode(metric_name,'Logical Reads Per Sec',                     average,null)) "l_reads_s",
max(decode(metric_name,'User Commits Per Sec',                      average,null)) "commits_s",
max(decode(metric_name,'Physical Read Total Bytes Per Sec',         round((average)/1024/1024,1),null)) "read_mb_s",
max(decode(metric_name,'Physical Read Total Bytes Per Sec',         round((maxval)/1024/1024,1),null)) "read_mb_s_max",
max(decode(metric_name,'Physical Read Total IO Requests Per Sec',   average,null)) "read_iops",
max(decode(metric_name,'Physical Read Total IO Requests Per Sec',   maxval,null)) "read_iops_max",
max(decode(metric_name,'Physical Reads Per Sec',                        average,null)) "read_bks",
max(decode(metric_name,'Physical Reads Direct Per Sec',                         average,null)) "read_bks_direct",
max(decode(metric_name,'Physical Write Total Bytes Per Sec',        round((average)/1024/1024,1),null)) "write_mb_s",
max(decode(metric_name,'Physical Write Total Bytes Per Sec',        round((maxval)/1024/1024,1),null)) "write_mb_s_max",
max(decode(metric_name,'Physical Write Total IO Requests Per Sec',  average,null)) "write_iops",
max(decode(metric_name,'Physical Write Total IO Requests Per Sec',  maxval,null)) "write_iops_max",
max(decode(metric_name,'Physical Writes Per Sec',                       average,null)) "write_bks",
max(decode(metric_name,'Physical Writes Direct Per Sec',                        average,null)) "write_bks_direct",
max(decode(metric_name,'Redo Generated Per Sec',                    round((average)/1024/1024,1),null)) "redo_mb_s",
max(decode(metric_name,'DB Block Gets Per Sec',                     average,null)) "db_block_gets_s",
max(decode(metric_name,'DB Block Changes Per Sec',                   average,null)) "db_block_changes_s",
max(decode(metric_name,'GC CR Block Received Per Second',            average,null)) "gc_cr_rec_s",
max(decode(metric_name,'GC Current Block Received Per Second',       average,null)) "gc_cu_rec_s",
max(decode(metric_name,'Global Cache Average CR Get Time',           average,null)) "gc_cr_get_cs",
max(decode(metric_name,'Global Cache Average Current Get Time',      average,null)) "gc_cu_get_cs",
max(decode(metric_name,'Global Cache Blocks Corrupted',              average,null)) "gc_bk_corrupted",
max(decode(metric_name,'Global Cache Blocks Lost',                   average,null)) "gc_bk_lost",
max(decode(metric_name,'Active Parallel Sessions',                   average,null)) "px_sess",
max(decode(metric_name,'Active Serial Sessions',                     average,null)) "se_sess",
max(decode(metric_name,'Average Synchronous Single-Block Read Latency', average,null)) "s_blk_r_lat",
max(decode(metric_name,'Cell Physical IO Interconnect Bytes',         round((average)/1024/1024,1),null)) "cell_io_int_mb",
max(decode(metric_name,'Cell Physical IO Interconnect Bytes',         round((maxval)/1024/1024,1),null)) "cell_io_int_mb_max"
  from(
  select  snap_id,num_interval,to_char(end_time,'YY/MM/DD HH24:MI') end_time,instance_number inst,metric_name,round(average,1) average,
  round(maxval,1) maxval,round(standard_deviation,1) standard_deviation
 from dba_hist_sysmetric_summary
where dbid = :DBID
 and snap_id between :SNAP_ID_MIN and :SNAP_ID_MAX
 --and snap_id = 920
 --and instance_number = 4
 and metric_name in ('Host CPU Utilization (%)','CPU Usage Per Sec','Host CPU Usage Per Sec','Average Active Sessions','Database Time Per Sec',
 'Executions Per Sec','Hard Parse Count Per Sec','Logical Reads Per Sec','Logons Per Sec',
 'Physical Read Total Bytes Per Sec','Physical Read Total IO Requests Per Sec','Physical Reads Per Sec','Physical Write Total Bytes Per Sec',
 'Redo Generated Per Sec','User Commits Per Sec','Current Logons Count','DB Block Gets Per Sec','DB Block Changes Per Sec',
 'Database Wait Time Ratio','Database CPU Time Ratio','SQL Service Response Time','Background Time Per Sec',
 'Physical Write Total IO Requests Per Sec','Physical Writes Per Sec','Physical Writes Direct Per Sec','Physical Writes Direct Lobs Per Sec',
 'Physical Reads Direct Per Sec','Physical Reads Direct Lobs Per Sec',
 'GC CR Block Received Per Second','GC Current Block Received Per Second','Global Cache Average CR Get Time','Global Cache Average Current Get Time',
 'Global Cache Blocks Corrupted','Global Cache Blocks Lost',
 'Active Parallel Sessions','Active Serial Sessions','Average Synchronous Single-Block Read Latency','Cell Physical IO Interconnect Bytes'
    )
 )
 group by snap_id,num_interval, end_time,inst
 order by snap_id, end_time,inst;



prompt
prompt
-- ##############################################################################################

column display_value format a50
set wrap off
REPHEADER PAGE LEFT '~~BEGIN-DATABASE-PARAMETERS~~'
REPFOOTER PAGE LEFT '~~END-DATABASE-PARAMETERS~~'
with inst as (
select min(instance_number) inst_num
  from dba_hist_snapshot
  where dbid = :DBID
        and snap_id BETWEEN to_number(:SNAP_ID_MIN) and to_number(:SNAP_ID_MAX))
SELECT PARAMETER_NAME,VALUE
FROM DBA_HIST_PARAMETER
WHERE dbid = :DBID
AND snap_id = (SELECT MAX(snap_id) FROM dba_hist_osstat WHERE dbid = :DBID AND instance_number = (select inst_num from inst))
   AND instance_number = (select inst_num from inst)
  and PARAMETER_NAME not in ('local_listener','service_names','remote_listener','db_domain','cluster_interconnects')
ORDER BY 1;


prompt
prompt
-- ##############################################################################################



REPHEADER PAGE LEFT '~~BEGIN-AVERAGE-ACTIVE-SESSIONS~~'
REPFOOTER PAGE LEFT '~~END-AVERAGE-ACTIVE-SESSIONS~~'
column wait_class format a20

 SELECT snap_id,
    wait_class,
    ROUND (SUM (pSec), 2) avg_sess
   FROM
    (SELECT snap_id,
        wait_class,
        p_tmfg / 1000000 / ela pSec
       FROM
        (SELECT (CAST (s.end_interval_time AS DATE) - CAST (s.begin_interval_time AS DATE)) * 24 *
            3600 ela,
            s.snap_id,
            wait_class,
            e.event_name,
            CASE WHEN s.begin_interval_time = s.startup_time
                        -- compare to e.time_waited_micro_fg for 10.2?
                THEN e.&T_WAITED_MICRO_COL
                ELSE e.&T_WAITED_MICRO_COL - lag (e.&T_WAITED_MICRO_COL) over (partition BY
                    event_id, e.dbid, e.instance_number, s.startup_time order by e.snap_id)
            END p_tmfg
           FROM dba_hist_snapshot s,
            dba_hist_system_event e
          WHERE s.dbid = e.dbid
            AND s.dbid = to_number(:DBID)
            AND e.dbid = to_number(:DBID)
            AND s.instance_number = e.instance_number
            AND s.snap_id = e.snap_id
            AND s.snap_id BETWEEN to_number(:SNAP_ID_MIN) and to_number(:SNAP_ID_MAX)
            AND e.snap_id BETWEEN to_number(:SNAP_ID_MIN) and to_number(:SNAP_ID_MAX)
            AND e.wait_class != 'Idle'
      UNION ALL
         SELECT (CAST (s.end_interval_time AS DATE) - CAST (s.begin_interval_time AS DATE)) * 24 *
            3600 ela,
            s.snap_id,
            t.stat_name wait_class,
            t.stat_name event_name,
            CASE WHEN s.begin_interval_time = s.startup_time
                THEN t.value
                ELSE t.value - lag (value) over (partition BY stat_id, t.dbid, t.instance_number,
                    s.startup_time order by t.snap_id)
            END p_tmfg
           FROM dba_hist_snapshot s,
            dba_hist_sys_time_model t
          WHERE s.dbid = t.dbid
            AND s.dbid = to_number(:DBID)
            AND s.instance_number = t.instance_number
            AND s.snap_id = t.snap_id
            AND s.snap_id BETWEEN to_number(:SNAP_ID_MIN) and to_number(:SNAP_ID_MAX)
                        AND t.snap_id BETWEEN to_number(:SNAP_ID_MIN) and to_number(:SNAP_ID_MAX)
            AND t.stat_name = 'DB CPU'
        )
                where p_tmfg is not null
    )
GROUP BY snap_id,
    wait_class
ORDER BY snap_id,
    wait_class;



prompt
prompt
-- ##############################################################################################


REPHEADER OFF
REPFOOTER OFF

define HISTOGRAM_QUERY = ' '
column :HISTOGRAM_QUERY_1 new_value HISTOGRAM_QUERY noprint
variable HISTOGRAM_QUERY_1 varchar2(4000)


begin
        if :DB_VERSION_1  >= 11.1 then
                :HISTOGRAM_QUERY_1 := q'!   snap_id,wait_class,event_name,wait_time_milli,sum(wait_count) wait_count
                                                from(
                                                SELECT       s.snap_id,
                                                                        wait_class,
                                                                        h.event_name,
                                                                        wait_time_milli,
                                                                        CASE WHEN s.begin_interval_time = s.startup_time
                                                                                THEN h.wait_count
                                                                                ELSE h.wait_count - lag (h.wait_count) over (partition BY
                                                                                        event_id,wait_time_milli, h.dbid, h.instance_number, s.startup_time order by h.snap_id)
                                                                        END wait_count
                                                                   FROM dba_hist_snapshot s,
                                                                        DBA_HIST_event_histogram h
                                                                  WHERE s.dbid = h.dbid
                                                                        AND s.dbid = :DBID
                                                                        AND s.instance_number = h.instance_number
                                                                        AND s.snap_id = h.snap_id
                                                                        AND s.snap_id BETWEEN :SNAP_ID_MIN and :SNAP_ID_MAX
                                                                        and event_name in ('cell single block physical read','cell list of blocks physical read','cell multiblock physical read',
                                                                                                           'db file sequential read','db file scattered read',
                                                                                                           'log file parallel write','log file sync','free buffer wait')
                                                                                  )
                                                          where wait_count > 0
                                                group by snap_id,wait_class,event_name,wait_time_milli
                                                          order by snap_id,event_name,wait_time_milli !';
        else
                :HISTOGRAM_QUERY_1 := q'!  'table not in this version' from dual !';
        end if;

end;
/

select :HISTOGRAM_QUERY_1 from dual;

REPHEADER PAGE LEFT '~~BEGIN-IO-WAIT-HISTOGRAM~~'
REPFOOTER PAGE LEFT '~~END-IO-WAIT-HISTOGRAM~~'
COLUMN EVENT_NAME FORMAT A37
select &HISTOGRAM_QUERY ;



prompt
prompt
-- ##############################################################################################


REPHEADER PAGE LEFT '~~BEGIN-IO-OBJECT-TYPE~~'
REPFOOTER PAGE LEFT '~~END-IO-OBJECT-TYPE~~'
COLUMN OBJECT_TYPE FORMAT A15


SELECT s.snap_id,regexp_replace(o.OBJECT_TYPE,'^(TABLE|INDEX).*','\1') OBJECT_TYPE,
       ROUND((sum(s.LOGICAL_READS_DELTA)* &DB_BLOCK_SIZE)/1024/1024/1024,1) logical_read_gb,
           ROUND((sum(s.PHYSICAL_READS_DELTA)* &DB_BLOCK_SIZE)/1024/1024/1024,1) physical_read_gb,
           ROUND((sum(s.PHYSICAL_WRITES_DELTA)* &DB_BLOCK_SIZE)/1024/1024/1024,1) physical_write_gb,
           ROUND((sum(s.SPACE_ALLOCATED_DELTA)/1024/1024/1024),1) GB_ADDED
FROM
  DBA_HIST_SEG_STAT_OBJ o,
  DBA_HIST_SEG_STAT s
where o.dbid = s.dbid
  and o.ts# = s.ts#
  and o.obj# = s.obj#
  and o.dataobj# = s.dataobj#
  and o.dbid = :DBID
                                  AND s.snap_id BETWEEN :SNAP_ID_MIN and :SNAP_ID_MAX
          AND OBJECT_TYPE != 'UNDEFINED'
  group by s.snap_id,regexp_replace(o.OBJECT_TYPE,'^(TABLE|INDEX).*','\1')
  order by snap_id,object_type;



prompt
prompt
-- ##############################################################################################



REPHEADER OFF
REPFOOTER OFF

define IOSTAT_FN_QUERY = ' '
column :IOSTAT_FN_QUERY_1 new_value IOSTAT_FN_QUERY noprint
variable IOSTAT_FN_QUERY_1 varchar2(4000)


begin
        if :DB_VERSION_1  >= 11.1 then
                :IOSTAT_FN_QUERY_1 := q'!  snap_id,
              function_name,
              SUM(sm_r_reqs) sm_r_reqs,
              SUM(sm_w_reqs) sm_w_reqs,
              SUM(lg_r_reqs) lg_r_reqs,
              SUM(lg_w_reqs) lg_w_reqs
            FROM
              (SELECT s.snap_id ,
                s.instance_number ,
                s.dbid ,
                FUNCTION_NAME,
                CASE
                  WHEN s.begin_interval_time = s.startup_time
                  THEN NVL(fn.SMALL_READ_REQS,0)
                  ELSE NVL(fn.SMALL_READ_REQS,0) - lag(NVL(fn.SMALL_READ_REQS,0),1) over (partition BY fn.FUNCTION_NAME , fn.instance_number , fn.dbid , s.startup_time order by fn.snap_id)
                END sm_r_reqs,
                CASE
                  WHEN s.begin_interval_time = s.startup_time
                  THEN NVL(fn.SMALL_WRITE_REQS,0)
                  ELSE NVL(fn.SMALL_WRITE_REQS,0) - lag(NVL(fn.SMALL_WRITE_REQS,0),1) over (partition BY fn.FUNCTION_NAME , fn.instance_number , fn.dbid , s.startup_time order by fn.snap_id)
                END sm_w_reqs,
                CASE
                  WHEN s.begin_interval_time = s.startup_time
                  THEN NVL(fn.LARGE_READ_REQS,0)
                  ELSE NVL(fn.LARGE_READ_REQS,0) - lag(NVL(fn.LARGE_READ_REQS,0),1) over (partition BY fn.FUNCTION_NAME , fn.instance_number , fn.dbid , s.startup_time order by fn.snap_id)
                END lg_r_reqs,
                CASE
                  WHEN s.begin_interval_time = s.startup_time
                  THEN NVL(fn.LARGE_WRITE_REQS,0)
                  ELSE NVL(fn.LARGE_WRITE_REQS,0) - lag(NVL(fn.LARGE_WRITE_REQS,0),1) over (partition BY fn.FUNCTION_NAME , fn.instance_number , fn.dbid , s.startup_time order by fn.snap_id)
                END lg_w_reqs
              FROM dba_hist_snapshot s ,
                DBA_HIST_IOSTAT_FUNCTION fn
              WHERE s.dbid = fn.dbid
              AND s.dbid   = :DBID
              AND s.snap_id BETWEEN :SNAP_ID_MIN and :SNAP_ID_MAX
              AND s.instance_number = fn.instance_number
              AND s.snap_id     = fn.snap_id
              )
            GROUP BY snap_id,
              function_name
              having SUM(sm_r_reqs) is not null
            order by snap_id !';
else
                :IOSTAT_FN_QUERY_1 := q'!  'table not in this version' from dual !';
        end if;

end;
/



select :IOSTAT_FN_QUERY_1 from dual;

REPHEADER PAGE LEFT '~~BEGIN-IOSTAT-BY-FUNCTION~~'
REPFOOTER PAGE LEFT '~~END-IOSTAT-BY-FUNCTION~~'
COLUMN FUNCTION_NAME FORMAT A22
select &IOSTAT_FN_QUERY ;



prompt
prompt
-- ##############################################################################################


column EVENT_NAME format a60
REPHEADER PAGE LEFT '~~BEGIN-TOP-N-TIMED-EVENTS~~'
REPFOOTER PAGE LEFT '~~END-TOP-N-TIMED-EVENTS~~'


SELECT snap_id,
  wait_class,
  event_name,
  pctdbt,
  total_time_s
FROM
  (SELECT a.snap_id,
    wait_class,
    event_name,
    b.dbt,
    ROUND(SUM(a.ttm) /b.dbt*100,2) pctdbt,
    SUM(a.ttm) total_time_s,
    dense_rank() over (partition BY a.snap_id order by SUM(a.ttm)/b.dbt*100 DESC nulls last) rnk
  FROM
    (SELECT snap_id,
      wait_class,
      event_name,
      ttm
    FROM
      (SELECT
        /*+ qb_name(systemevents) */
        (CAST (s.end_interval_time AS DATE) - CAST (s.begin_interval_time AS DATE)) * 24 * 3600 ela,
        s.snap_id,
        wait_class,
        e.event_name,
        CASE
          WHEN s.begin_interval_time = s.startup_time
          THEN e.time_waited_micro
          ELSE e.time_waited_micro - lag (e.time_waited_micro ) over (partition BY e.instance_number,e.event_name order by e.snap_id)
        END ttm
      FROM dba_hist_snapshot s,
        dba_hist_system_event e
      WHERE s.dbid          = e.dbid
      AND s.dbid            = :DBID
      AND s.instance_number = e.instance_number
      AND s.snap_id         = e.snap_id
      AND s.snap_id BETWEEN :SNAP_ID_MIN and :SNAP_ID_MAX
      AND e.wait_class != 'Idle'
      UNION ALL
      SELECT
        /*+ qb_name(dbcpu) */
        (CAST (s.end_interval_time AS DATE) - CAST (s.begin_interval_time AS DATE)) * 24 * 3600 ela,
        s.snap_id,
        t.stat_name wait_class,
        t.stat_name event_name,
        CASE
          WHEN s.begin_interval_time = s.startup_time
          THEN t.value
          ELSE t.value - lag (t.value ) over (partition BY s.instance_number order by s.snap_id)
        END ttm
      FROM dba_hist_snapshot s,
        dba_hist_sys_time_model t
      WHERE s.dbid          = t.dbid
      AND s.dbid            = :DBID
      AND s.instance_number = t.instance_number
      AND s.snap_id         = t.snap_id
      AND s.snap_id BETWEEN :SNAP_ID_MIN and :SNAP_ID_MAX
      AND t.stat_name = 'DB CPU'
      )
    ) a,
    (SELECT snap_id,
      SUM(dbt) dbt
    FROM
      (SELECT
        /*+ qb_name(dbtime) */
        s.snap_id,
        t.instance_number,
        t.stat_name nm,
        CASE
          WHEN s.begin_interval_time = s.startup_time
          THEN t.value
          ELSE t.value - lag (t.value ) over (partition BY s.instance_number order by s.snap_id)
        END dbt
      FROM dba_hist_snapshot s,
        dba_hist_sys_time_model t
      WHERE s.dbid          = t.dbid
      AND s.dbid            = :DBID
      AND s.instance_number = t.instance_number
      AND s.snap_id         = t.snap_id
      AND s.snap_id BETWEEN :SNAP_ID_MIN and :SNAP_ID_MAX
      AND t.stat_name = 'DB time'
      ORDER BY s.snap_id,
        s.instance_number
      )
    GROUP BY snap_id
    HAVING SUM(dbt) > 0
    ) b
  WHERE a.snap_id = b.snap_id
  GROUP BY a.snap_id,
    a.wait_class,
    a.event_name,
    b.dbt
  )
WHERE pctdbt > 0
AND rnk     <= 5
ORDER BY snap_id,
  pctdbt DESC;



REPHEADER OFF
REPFOOTER OFF





prompt
prompt
-- ##############################################################################################



REPHEADER PAGE LEFT '~~BEGIN-SYSSTAT~~'
REPFOOTER PAGE LEFT '~~END-SYSSTAT~~'
SELECT SNAP_ID,
  MAX(DECODE(event_name,'cell flash cache read hits', event_val_diff,NULL)) "cell_flash_hits",
  MAX(DECODE(event_name,'physical read total IO requests', event_val_diff,NULL)) "read_iops",
  ROUND(MAX(DECODE(event_name,'physical read total bytes', event_val_diff,NULL))                                 /1024/1024,1) "read_mb",
  ROUND(MAX(DECODE(event_name,'physical read total bytes optimized', event_val_diff,NULL))                       /1024/1024,1) "read_mb_opt",
  ROUND(MAX(DECODE(event_name,'cell physical IO interconnect bytes', event_val_diff,NULL))                       /1024/1024,1) "cell_int_mb",
  ROUND(MAX(DECODE(event_name,'cell physical IO interconnect bytes returned by smart scan', event_val_diff,NULL))/1024/1024,1) "cell_int_ss_mb",
  MAX(DECODE(event_name,'EHCC Conventional DMLs', event_val_diff,NULL)) "ehcc_con_dmls"
FROM
  (SELECT snap_id,
    event_name,
    ROUND(SUM(val_per_s),1) event_val_diff
  FROM
    (SELECT snap_id,
      instance_number,
      event_name,
      event_val_diff,
      (event_val_diff/ela) val_per_s
    FROM
      (SELECT (CAST (s.end_interval_time AS DATE) - CAST (s.begin_interval_time AS DATE)) * 24 * 3600 ela,
        s.snap_id,
        s.instance_number,
        t.stat_name wait_class,
        t.stat_name event_name,
        CASE
          WHEN s.begin_interval_time = s.startup_time
          THEN t.value
          ELSE t.value - lag (value) over (partition BY stat_id, t.dbid, t.instance_number, s.startup_time order by t.snap_id)
        END event_val_diff
      FROM dba_hist_snapshot s,
        dba_hist_sysstat t
      WHERE s.dbid = t.dbid
      AND s.dbid   = :DBID
      AND s.instance_number = t.instance_number
      AND s.snap_id         = t.snap_id
      AND s.snap_id BETWEEN :SNAP_ID_MIN AND :SNAP_ID_MAX
      AND t.snap_id BETWEEN :SNAP_ID_MIN AND :SNAP_ID_MAX
      AND t.stat_name IN ('cell flash cache read hits','physical read total IO requests', 'cell physical IO bytes saved by storage index',
      'EHCC Conventional DMLs', 'cell physical IO interconnect bytes','cell physical IO interconnect bytes returned by smart scan',
      'physical read total bytes','physical read total bytes optimized' )
      )
    WHERE event_val_diff IS NOT NULL
    )
  GROUP BY snap_id,
    event_name
  )
GROUP BY snap_id
ORDER BY SNAP_ID ASC;



prompt
prompt

-- ##############################################################################################




REPHEADER PAGE LEFT '~~BEGIN-TOP-SQL-SUMMARY~~'
REPFOOTER PAGE LEFT '~~END-TOP-SQL-SUMMARY~~'

SELECT * FROM(
SELECT s.module,s.action,s.sql_id,avg(s.optimizer_cost) optimizer_cost,
decode(t.command_type,11,'ALTERINDEX',15,'ALTERTABLE',170,'CALLMETHOD',9,'CREATEINDEX',1,'CREATETABLE',
7,'DELETE',50,'EXPLAIN',2,'INSERT',26,'LOCKTABLE',47,'PL/SQLEXECUTE',
3,'SELECT',6,'UPDATE',189,'UPSERT') command_name,
PARSING_SCHEMA_NAME,
DENSE_RANK() OVER
      (ORDER BY sum(EXECUTIONS_DELTA) DESC ) exec_rank,
DENSE_RANK() OVER
      (ORDER BY sum(ELAPSED_TIME_DELTA) DESC ) elap_rank,
DENSE_RANK() OVER
      (ORDER BY sum(BUFFER_GETS_DELTA) DESC ) log_reads_rank,
DENSE_RANK() OVER
      (ORDER BY sum(disk_reads_delta) DESC ) phys_reads_rank,
          sum(EXECUTIONS_DELTA) execs,
sum(ELAPSED_TIME_DELTA) elap,
sum(BUFFER_GETS_DELTA) log_reads,
round(sum(disk_reads_delta * &DB_BLOCK_SIZE)/1024/1024/1024) phy_read_gb,
      count(distinct plan_hash_value) plan_count,
          sum(px_servers_execs_delta) px_servers_execs
 FROM dba_hist_sqlstat s,dba_hist_sqltext t
 WHERE s.dbid = :DBID
  AND s.snap_id BETWEEN :SNAP_ID_MIN and :SNAP_ID_MAX
   AND s.dbid = t.dbid
  AND s.sql_id = t.sql_id
  AND PARSING_SCHEMA_NAME NOT IN ('SYS','DBSNMP','SYSMAN')
  GROUP BY s.module,s.action,s.sql_id,t.command_type,PARSING_SCHEMA_NAME)
WHERE elap_rank <= &SQL_TOP_N
 OR phys_reads_rank <= &SQL_TOP_N
 or log_reads_rank <= &SQL_TOP_N
 or exec_rank <= &SQL_TOP_N
 order by elap_rank asc nulls last;





column PARSING_SCHEMA_NAME format a32
REPHEADER PAGE LEFT '~~BEGIN-TOP-SQL-BY-SNAPID~~'
REPFOOTER PAGE LEFT '~~END-TOP-SQL-BY-SNAPID~~'
column module format a33
column action format a33

select * from(
SELECT s.snap_id,PARSING_SCHEMA_NAME,PLAN_HASH_VALUE plan_hash,substr(regexp_replace(s.module,'([[:alnum:]\.\-])@.+\(TNS.+','\1'),1,30) module,
substr(s.action,1,30) action,
s.sql_id,
avg(s.optimizer_cost) optimizer_cost,
decode(t.command_type,11,'ALTERINDEX',15,'ALTERTABLE',170,'CALLMETHOD',9,'CREATEINDEX',1,'CREATETABLE',
7,'DELETE',50,'EXPLAIN',2,'INSERT',26,'LOCKTABLE',47,'PL/SQLEXECUTE',
3,'SELECT',6,'UPDATE',189,'UPSERT') command_name,sum(EXECUTIONS_DELTA) execs,sum(BUFFER_GETS_DELTA) buffer_gets,sum(ROWS_PROCESSED_DELTA) rows_proc,
round(sum(CPU_TIME_DELTA)/1000000,1) cpu_t_s,round(sum(ELAPSED_TIME_DELTA)/1000000,1) elap_s,
round(sum(disk_reads_delta * &DB_BLOCK_SIZE)/1024/1024,1) read_mb,round(sum(IOWAIT_DELTA)/1000000,1) io_wait,
DENSE_RANK() OVER (PARTITION BY s.snap_id ORDER BY sum(ELAPSED_TIME_DELTA) DESC ) elap_rank,
      CASE WHEN MAX(PLAN_HASH_VALUE) = LAG(MAX(PLAN_HASH_VALUE), 1, 0) OVER (PARTITION BY s.sql_id ORDER BY s.snap_id ASC)
      OR LAG(MAX(PLAN_HASH_VALUE), 1, 0) OVER (PARTITION BY s.sql_id ORDER BY s.snap_id ASC) = 0 THEN 0
      when count(distinct PLAN_HASH_VALUE) > 1 then 1 else 1 end plan_change,
      count(distinct PLAN_HASH_VALUE) OVER       (PARTITION BY s.snap_id,s.sql_id ) plans,
      round(sum(disk_reads_delta * &DB_BLOCK_SIZE)/1024/1024/1024) phy_read_gb,
      sum(s.px_servers_execs_delta) px_servers_execs,
      round(sum(DIRECT_WRITES_DELTA * &DB_BLOCK_SIZE)/1024/1024/1024) direct_w_gb,
      sum(IOWAIT_DELTA) as iowait_time,
      sum(DISK_READS_DELTA) as PIO
  FROM dba_hist_sqlstat s,dba_hist_sqltext t
  WHERE s.dbid = :DBID
  AND s.dbid = t.dbid
  AND s.sql_id = t.sql_id
  AND s.snap_id BETWEEN :SNAP_ID_MIN and :SNAP_ID_MAX
  AND PARSING_SCHEMA_NAME NOT IN ('SYS','DBSNMP','SYSMAN')
  GROUP BY s.snap_id, PLAN_HASH_VALUE,t.command_type,PARSING_SCHEMA_NAME,s.module,s.action, s.sql_id)
  WHERE elap_rank <= &SQL_TOP_N --#
  --and sql_id = '2a22s56r25y6d'
  order by snap_id,elap_rank asc nulls last;




REPHEADER OFF
REPFOOTER OFF

spool off

PRO &SPOOL_FILE_NAME is the text report

ALTER SESSION SET WORKAREA_SIZE_POLICY = AUTO;

Tuesday, 7 February 2023

ORA-20020: Database/Instance/Snapshot mismatch

ORA-20020: Database/Instance/Snapshot mismatch ...

SQL> SQL> set linesize 300 pagesize 300
SQL> select * from table(dbms_workload_repository.awr_sql_report_text((SELECT DBID FROM V$DATABASE), (SELECT INSTANCE_NUMBER FROM V$INSTANCE), :BgnSnap,:EndSnap, '&sql_id'));
old 1: select * from table(dbms_workload_repository.awr_sql_report_text((SELECT DBID FROM V$DATABASE), (SELECT INSTANCE_NUMBER FROM V$INSTANCE), :BgnSnap,:EndSnap, '&sql_id'))
new 1: select * from table(dbms_workload_repository.awr_sql_report_text((SELECT DBID FROM V$DATABASE), (SELECT INSTANCE_NUMBER FROM V$INSTANCE), :BgnSnap,:EndSnap, '36psp2j8kf741'))
select * from table(dbms_workload_repository.awr_sql_report_text((SELECT DBID FROM V$DATABASE), (SELECT INSTANCE_NUMBER FROM V$INSTANCE), :BgnSnap,:EndSnap, '36psp2j8kf741'))
*
ERROR at line 1:
ORA-20020: Database/Instance/Snapshot mismatch <<<<<<<<<<<<<<<<<<<<<<<<<<<-----
ORA-06512: at "SYS.DBMS_SWRF_REPORT_INTERNAL", line 16546
ORA-06512: at "SYS.DBMS_SWRF_REPORT_INTERNAL", line 25068
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 1365
ORA-06512: at line 1

select address, hash_value, executions, loads, version_count, invalidations, parse_calls
from v$sqlarea
where sql_text like ('select * from table(dbms_workload_repository%');

ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS
---------------- ---------- ---------- ---------- ------------- ------------- -----------
00000005F6F0E058 1266709388 1 1 1 0 1
00000005780680E8 433367347 1 1 1 0 1

exec dbms_shared_pool.purge ('00000005F6F0E058,1266709388 ','C');

PL/SQL procedure successfully completed.
SQL> SQL>
exec dbms_shared_pool.purge ('00000005780680E8,433367347','C');SQL> SQL>
PL/SQL procedure successfully completed.
SQL> exec dbms_shared_pool.purge ('00000005F6F0E058,1266709388','C');
PL/SQL procedure successfully completed.

===============

run again ...

Friday, 20 January 2023

Sequence Info

 



define sequence_name='SEQ'

set feed off
set pagesize 10000
set wrap off
set linesize 200
set heading on
set tab on
set scan on
set verify off

set numf 999999999999999999999999
column 		datum new_value datum noprint
column 		sequence_owner 	format A20 					heading 'Sequence|Owner'
column 		sequence_name 	noprint new_value sequence
column 		min_value 	format 99						heading 'Minimal|Value  '
column 		max_value 	format 99999999999999999999999999999999 heading 'Maximal    |Value      '
column 		increment_by 	format 99 					heading 'Incr|By  '
column 		last_number 	format 999999999999 		heading 'Last    |Number  '
column 		cache_size 		format 9999999999999 		heading 'Cache|Size '
column 		order_flag 		format a7 					heading 'Order ?'
column 		cycle_flag 		format a7 					heading 'Cycle ?'

ttitle left 'Sequence Info 'sequence skip 2

break on sequence_name skip page -
on sequence_owner skip 2 -

select  to_char(sysdate, 'DD-MM-YY') datum,
    substr(sequence_owner,1,12) sequence_owner,
    substr(sequence_name,1,27) sequence_name,
    min_value,
    max_value,
    increment_by,
    last_number,
    cache_size,
                decode(order_flag,
                  'Y','Yes',
                  'N','No') order_flag,
                decode(cycle_flag,
                  'Y','Yes',
                  'N','No') cycle_flag
from dba_sequences
where 1=1
--and sequence_owner LIKE UPPER('user')
and     sequence_name like upper('&sequence_name')
order by 1,2,3,4;


define sequence_name='SEQ'
set feed off
set pagesize 10000
set wrap off
set linesize 200
set heading on
set tab on
set scan on
set verify off
--


ttitle left 'Shows next sequence number without incrementing it' skip 2


col sequence_owner format a20 					heading 'Sequence|Owner'
col sequence_name  format a25 					heading 'Sequence|Name'
col next_seq       format 9999999999999999999 	heading 'Next|Value'
col cache          format a25 					heading 'Cache'

select sequence_owner, sequence_name, next_seq, cache
from (select sequence_owner, sequence_name,
       nextvalue next_seq, 'in cache' cache
      from v$_sequences
      where nextvalue is not null
       union
      select sequence_owner, sequence_name,
       highwater next_seq, 'created nocache' cache
      from v$_sequences
      where nextvalue is null
       union
      select sequence_owner, sequence_name,
       last_number next_seq, 'not in cache' cache
      from dba_sequences s
      where not exists (select  sequence_owner, sequence_name
                        from v$_sequences v
                        where v.sequence_name = s.sequence_name
                        and   v.sequence_owner = s.sequence_owner))
where 1=1
--and  sequence_owner like upper('sequence_owner')
and     sequence_name like upper('&sequence_name')
order by sequence_owner, sequence_name
/

Saturday, 31 December 2022

Oracle How to Restore Of Single PDB via RMAN

		 
Oracle How to Restore Of Single PDB via RMAN 
Rman to Restore Of Single PDB in Multitenant	



Aim to restore only ORCL database ONLY via rman !!!!
		
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCL                           READ WRITE NO   <<<< only this database !!!
         4 ORCLD                          READ WRITE NO

 
		 
=========		 

		 
RMAN> restore controlfile from '/home/oracle/RmanBackup/ORCLCDB_T20221230_cf15_p1_t1124819364';

Starting restore at 31-12-2022 02:43:09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=424 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/ORCLCDB/control01.ctl
Finished restore at 31-12-2022 02:43:12

RMAN> alter database mount ;


released channel: ORA_DISK_1
Statement processed



from another session !!!

set linesize 300 pagesize 100
col inst_id                      for 9999999                 heading 'Instance #'
col file_nr                      for 9999999                 heading 'File #'
col file_name                    for A70                     heading 'File name'
col checkpoint_change_nr         for 99999999999999          heading 'Checkpoint #'
col checkpoint_change_time       for A20                     heading 'Checkpoint time'
col last_change_nr               for 99999999999999          heading 'Last change #'
col SCNStatus for a15
SELECT
 fe.inst_id,
fe.CON_ID,  ---- for >12c
 fe.fenum file_nr,
 fn.fnnam file_name,
 TO_NUMBER (fe.fecps) checkpoint_change_nr,
 fe.fecpt checkpoint_change_time,
fe.fests last_change_nr,
NVL2(fe.fecps, '<-Good', 'Recover to this scn') SCNStatus,  ---- Recover to max 'scn'
 DECODE (
 fe.fetsn,
 0, DECODE (BITAND (fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
 DECODE (BITAND (fe.festa, 18),
         0, 'OFFLINE',
         2, 'ONLINE',
            'RECOVER')
 ) status
FROM x$kccfe fe, x$kccfn fn
WHERE ( (fe.fepax != 65535 AND fe.fepax != 0 )  OR (fe.fepax = 65535 OR fe.fepax = 0) )
 AND fn.fnfno = fe.fenum
 AND fe.fefnh = fn.fnnum
 AND fe.fedup != 0
 AND fn.fntyp = 4
 AND fn.fnnam IS NOT NULL
 AND BITAND (fn.fnflg, 4) != 4
ORDER BY fe.fenum
;
 
		 
		 Instance #     CON_ID   File # File name                                                                 Checkpoint # Checkpoint time      Last change # SCNSTATUS       STATUS
---------- ---------- -------- ---------------------------------------------------------------------- --------------- -------------------- -------------------- --------------- -------
         1          1        1 /u01/app/oracle/oradata/ORCLCDB/system01.dbf                                   4079968 12/30/2022 17:49:19<-Good          SYSTEM
         1          1        3 /u01/app/oracle/oradata/ORCLCDB/sysaux01.dbf                                   4079968 12/30/2022 17:49:19<-Good          ONLINE
         1          2        5 /u01/app/oracle/oradata/ORCLCDB/pdbseed/system01.dbf                           2163739 05/31/2019 15:32:31<-Good          SYSOFF
         1          2        6 /u01/app/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf                           2163739 05/31/2019 15:32:31<-Good          OFFLINE
         1          1        7 /u01/app/oracle/oradata/ORCLCDB/users01.dbf                                    4079968 12/30/2022 17:49:19<-Good          ONLINE
         1          2        8 /u01/app/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf                          2163739 05/31/2019 15:32:31<-Good          OFFLINE
         1          3        9 /u01/app/oracle/oradata/ORCLCDB/orcl/system01.dbf                              4079968 12/30/2022 17:49:19<-Good          SYSTEM
         1          3       10 /u01/app/oracle/oradata/ORCLCDB/orcl/sysaux01.dbf                              4079968 12/30/2022 17:49:19<-Good          ONLINE
         1          3       12 /u01/app/oracle/oradata/ORCLCDB/orcl/users01.dbf                               4079968 12/30/2022 17:49:19<-Good          ONLINE
         1          3       13 /u01/app/oracle/oradata/ORCLCDB/orcl/APEX_1291597703607401.dbf                 4079968 12/30/2022 17:49:19<-Good          ONLINE
         1          1       14 /u01/app/oracle/oradata/ORCLCDB/undotbs2.dbf                                   4079968 12/30/2022 17:49:19<-Good          ONLINE
         1          3       15 /u01/app/oracle/oradata/ORCLCDB/orcl/undotbs2.dbf                              4079968 12/30/2022 17:49:19<-Good          ONLINE
         1          4       16 /u01/app/oracle/oradata/ORCLCDB/orcld/system01.dbf                             4078427 12/30/2022 17:42:42<-Good          SYSOFF
         1          4       17 /u01/app/oracle/oradata/ORCLCDB/orcld/sysaux01.dbf                             4078427 12/30/2022 17:42:42<-Good          OFFLINE
         1          4       18 /u01/app/oracle/oradata/ORCLCDB/orcld/undotbs01.dbf                            4078427 12/30/2022 17:42:42<-Good          OFFLINE
         1          4       19 /u01/app/oracle/oradata/ORCLCDB/orcld/orcld.dbf                                4078427 12/30/2022 17:42:42<-Good          OFFLINE

16 rows selected.


Find out the scn from above SQL



run { set until scn 4079968;
            restore database root ;
            restore database "PDB$SEED";
            restore database ORCL;
                        }


Starting restore at 31-12-2022 02:50:50
Starting implicit crosscheck backup at 31-12-2022 02:50:50
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=414 device type=DISK
Crosschecked 14 objects
Finished implicit crosscheck backup at 31-12-2022 02:50:51

Starting implicit crosscheck copy at 31-12-2022 02:50:51
using channel ORA_DISK_1
Crosschecked 4 objects
Finished implicit crosscheck copy at 31-12-2022 02:50:51

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ORCLCDB/system01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ORCLCDB/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/RmanBackup/ORCLCDB_T20221230_db_s3_p1_t1124819247
channel ORA_DISK_1: piece handle=/home/oracle/RmanBackup/ORCLCDB_T20221230_db_s3_p1_t1124819247 tag=RMANFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORCLCDB/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00014 to /u01/app/oracle/oradata/ORCLCDB/undotbs2.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/RmanBackup/ORCLCDB_T20221230_db_s5_p1_t1124819293
channel ORA_DISK_1: piece handle=/home/oracle/RmanBackup/ORCLCDB_T20221230_db_s5_p1_t1124819293 tag=RMANFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 31-12-2022 02:51:33

Starting restore at 31-12-2022 02:51:33
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/ORCLCDB/pdbseed/system01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/RmanBackup/ORCLCDB_T20221230_db_s7_p1_t1124819319
channel ORA_DISK_1: piece handle=/home/oracle/RmanBackup/ORCLCDB_T20221230_db_s7_p1_t1124819319 tag=RMANFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/RmanBackup/ORCLCDB_T20221230_db_s9_p1_t1124819336
channel ORA_DISK_1: piece handle=/home/oracle/RmanBackup/ORCLCDB_T20221230_db_s9_p1_t1124819336 tag=RMANFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 31-12-2022 02:52:03

Starting restore at 31-12-2022 02:52:03
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/ORCLCDB/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00013 to /u01/app/oracle/oradata/ORCLCDB/orcl/APEX_1291597703607401.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/RmanBackup/ORCLCDB_T20221230_db_s2_p1_t1124819247
channel ORA_DISK_1: piece handle=/home/oracle/RmanBackup/ORCLCDB_T20221230_db_s2_p1_t1124819247 tag=RMANFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/ORCLCDB/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/ORCLCDB/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00015 to /u01/app/oracle/oradata/ORCLCDB/orcl/undotbs2.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/RmanBackup/ORCLCDB_T20221230_db_s4_p1_t1124819293
channel ORA_DISK_1: piece handle=/home/oracle/RmanBackup/ORCLCDB_T20221230_db_s4_p1_t1124819293 tag=RMANFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 31-12-2022 02:53:04






RMAN> recover database ;

Starting recover at 31-12-2022 03:27:16
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/31/2022 03:27:16
RMAN-07551: data file 16 must be restored or preplugin recovery must be completed


******************************************************************************************************************
How to use Rman to Restore Of Single PDB in Multitenant to Alternate Server (Doc ID 2142675.1)
Skip the PDB's database not required during the recovery stage by using 'Skip forever tablespace >'

*******************************************************************************************************************




RMAN> report schema ;

RMAN-06139: warning: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name ORCLCDB

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    910      SYSTEM               ***     /u01/app/oracle/oradata/ORCLCDB/system01.dbf
3    570      SYSAUX               ***     /u01/app/oracle/oradata/ORCLCDB/sysaux01.dbf
5    270      PDB$SEED:SYSTEM      ***     /u01/app/oracle/oradata/ORCLCDB/pdbseed/system01.dbf
6    330      PDB$SEED:SYSAUX      ***     /u01/app/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf
7    5        USERS                ***     /u01/app/oracle/oradata/ORCLCDB/users01.dbf
8    100      PDB$SEED:UNDOTBS1    ***     /u01/app/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf
9    410      ORCL:SYSTEM          ***     /u01/app/oracle/oradata/ORCLCDB/orcl/system01.dbf
10   1200     ORCL:SYSAUX          ***     /u01/app/oracle/oradata/ORCLCDB/orcl/sysaux01.dbf
12   401      ORCL:USERS           ***     /u01/app/oracle/oradata/ORCLCDB/orcl/users01.dbf
13   2        ORCL:APEX_1291597703607401 ***     /u01/app/oracle/oradata/ORCLCDB/orcl/APEX_1291597703607401.dbf
14   50       UNDOTBS2             ***     /u01/app/oracle/oradata/ORCLCDB/undotbs2.dbf
15   50       ORCL:UNDOTBS2        ***     /u01/app/oracle/oradata/ORCLCDB/orcl/undotbs2.dbf
16   0        ORCLD:SYSTEM         ***     /u01/app/oracle/oradata/ORCLCDB/orcld/system01.dbf
17   0        ORCLD:SYSAUX         ***     /u01/app/oracle/oradata/ORCLCDB/orcld/sysaux01.dbf
18   0        ORCLD:UNDOTBS1       ***     /u01/app/oracle/oradata/ORCLCDB/orcld/undotbs01.dbf
19   0        ORCLD:ORCL1_USERS    ***     /u01/app/oracle/oradata/ORCLCDB/orcld/orcld.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /u01/app/oracle/oradata/ORCLCDB/temp01.dbf
2    36       PDB$SEED:TEMP        32767       /u01/app/oracle/oradata/ORCLCDB/pdbseed/temp012019-05-31_15-03-14-330-PM.dbf
3    36       ORCL:TEMP            32767       /u01/app/oracle/oradata/ORCLCDB/orcl/temp01.dbf
4    36       ORCLD:TEMP           32767       /u01/app/oracle/oradata/ORCLCDB/orcld/cl_ixantemp012019-05-31_15-03-14-330-PM.dbf




Now skip ORCLD pluggable and datafiles!!!


RMAN> recover database skip forever tablespace ORCLD:SYSTEM,ORCLD:ORCL1_USERS,ORCLD:SYSAUX,ORCLD:UNDOTBS1 ;


Starting recover at 31-12-2022 03:39:38
using channel ORA_DISK_1

Executing: alter database datafile 16 offline drop
Executing: alter database datafile 19 offline drop
Executing: alter database datafile 17 offline drop
Executing: alter database datafile 18 offline drop
starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=33
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=34
channel ORA_DISK_1: reading from backup piece /home/oracle/RmanBackup/ORCLCDB_T20221230_arch_s13_p1_t1124819360
channel ORA_DISK_1: piece handle=/home/oracle/RmanBackup/ORCLCDB_T20221230_arch_s13_p1_t1124819360 tag=RMANFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2022_12_31/o1_mf_1_33_ktzxbcob_.arc thread=1 sequence=33
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2022_12_31/o1_mf_1_33_ktzxbcob_.arc RECID=6 STAMP=1124854779
archived log file name=/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2022_12_31/o1_mf_1_34_ktzxbcoy_.arc thread=1 sequence=34
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2022_12_31/o1_mf_1_34_ktzxbcoy_.arc RECID=5 STAMP=1124854779
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=35
channel ORA_DISK_1: reading from backup piece /home/oracle/RmanBackup/ORCLCDB_T20221230_arch_s14_p1_t1124819361
channel ORA_DISK_1: piece handle=/home/oracle/RmanBackup/ORCLCDB_T20221230_arch_s14_p1_t1124819361 tag=RMANFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2022_12_31/o1_mf_1_35_ktzxbf4z_.arc thread=1 sequence=35
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2022_12_31/o1_mf_1_35_ktzxbf4z_.arc RECID=7 STAMP=1124854781
unable to find archived log
archived log thread=1 sequence=36
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/31/2022 03:39:42
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 36 and starting SCN of 4079968

RMAN>

RMAN> alter database open resetlogs;
Statement processed

Recovey completed ....




SQL> select open_mode,current_scn,checkpoint_change#,archive_change#,controlfile_change# from v$database;

OPEN_MODE            CURRENT_SCN CHECKPOINT_CHANGE# ARCHIVE_CHANGE# CONTROLFILE_CHANGE#
-------------------- ----------- ------------------ --------------- -------------------
READ WRITE               4092115            4079972               0             4092072




set linesize 300
col NAME for a70
col ERROR for a15
 select * from (select file#,con_id,tablespace_name,status,error ,recover ,fuzzy ,checkpoint_time checkpoint from v$datafile_header)
natural join (select hxfil file#, fhsta, fhscn, fhrba_seq, fhafs from x$kcvfhall);





    FILE#     CON_ID TABLESPACE_NAME                STATUS  ERROR           REC FUZ CHECKPOIN      FHSTA FHSCN                 FHRBA_SEQ FHAFS
---------- ---------- ------------------------------ ------- --------------- --- --- --------- ---------- -------------------- ---------- --------------------
         1          1 SYSTEM                         ONLINE                  NO  YES 31-DEC-22       8196 4079972                       1 0
         3          1 SYSAUX                         ONLINE                  NO  YES 31-DEC-22          4 4079972                       1 0
         5          2 SYSTEM                         ONLINE                      NO  31-MAY-19       8192 2163739                       7 0
         6          2 SYSAUX                         ONLINE                      NO  31-MAY-19          0 2163739                       7 0
         7          1 USERS                          ONLINE                  NO  YES 31-DEC-22          4 4079972                       1 0
         8          2 UNDOTBS1                       ONLINE                      NO  31-MAY-19          0 2163739                       7 0
         9          3 SYSTEM                         ONLINE                  NO  YES 31-DEC-22       8196 4080979                       1 0
        10          3 SYSAUX                         ONLINE                  NO  YES 31-DEC-22          4 4080979                       1 0
        12          3 USERS                          ONLINE                  NO  YES 31-DEC-22          4 4080979                       1 0
        13          3 APEX_1291597703607401          ONLINE                  NO  YES 31-DEC-22          4 4080979                       1 0
        14          1 UNDOTBS2                       ONLINE                  NO  YES 31-DEC-22          4 4079972                       1 0
        15          3 UNDOTBS2                       ONLINE                  NO  YES 31-DEC-22          4 4080979                       1 0
        16          4                                OFFLINE FILE NOT FOUND                             0 0                             0 0
        17          4                                OFFLINE FILE NOT FOUND                             0 0                             0 0
        18          4                                OFFLINE FILE NOT FOUND                             0 0                             0 0
        19          4                                OFFLINE FILE NOT FOUND                             0 0                             0 0

16 rows selected.

SQL>



SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCL                           READ WRITE NO
         4 ORCLD                          MOUNTED  <<<<<
SQL>

Friday, 30 December 2022

Oracle Recover from a loss of the SYSTEM tablespace datafile


recover system datafile  lost

Recover from a loss of the SYSTEM tablespace datafile 



Error on alert log
ORA-01110: data file 1: '/u01/app/oracle/Datafile/data_D-ORCL_TS-SYSTEM_FNO-1'
ORA-01565: error in identifying file '/u01/app/oracle/Datafile/data_D-ORCL_TS-SYSTEM_FNO-1'   ------<<<<<<<
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
 


We should have valid backup to recover system datafile  ...


export NLS_DATE_FORMAT='dd-mm-yyyy hh:mi:ss'
rman target / | tee rman.log
run {
  allocate channel ch1 device type DISK;
  allocate channel ch2 device type DISK;
  backup incremental level=0 tag "RMANFULL" format '/u01/app/oracle/RmanBackup/%d_T%T_db_s%s_p%p_t%t' database filesperset 4 plus archivelog tag "RMANFULL";
 sql "alter system archive log current";
  backup format '/u01/app/oracle/RmanBackup/%d_T%T_arch_s%s_p%p_t%t' archivelog all filesperset 4 tag "RMANFULL";
  backup format '/u01/app/oracle/RmanBackup/%d_T%T_cf%s_p%p_t%t' current controlfile tag "RMANFULL";
sql "alter system archive log current";
restore database preview;
}



Simulate the failure - by moving the system datafile:


[oracle@wcp12cr2 Datafile]$ mv data_D-ORCL_TS-SYSTEM_FNO-1 data_D-ORCL_TS-SYSTEM_FNO-1-ORIG



SQL> shutdown immediate ;
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/Datafile/data_D-ORCL_TS-SYSTEM_FNO-1'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL>






from alert log 

2022-12-29T13:34:41.592711-08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_10344.trc:
ORA-01110: data file 1: '/u01/app/oracle/Datafile/data_D-ORCL_TS-SYSTEM_FNO-1'
ORA-01565: error in identifying file '/u01/app/oracle/Datafile/data_D-ORCL_TS-SYSTEM_FNO-1'   ------<<<<<<<
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
Checker run found 1 new persistent data failures
2022-12-29T13:35:25.549197-08:00
OS process OFSD (ospid 3088) idle for 30 seconds, exiting



--Check the status of datafiles 

set pagesize 300 linesize 200
col NAME for a70
col recover for a12
col , error for a20
select file#,NAME,checkpoint_change#, status, recover, error from v$datafile_header;


SQL> SQL> SQL>
     FILE# NAME                                                                   CHECKPOINT_CHANGE# STATUS  REC
---------- ---------------------------------------------------------------------- ------------------ ------- ---
         1                                                                                         0 ONLINE        <<<<< lost file 
         2 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_OPSS_FNO-2                      3361521 ONLINE  NO
         3 /u01/app/oracle/Datafile/data_D-ORCL_TS-SYSAUX_FNO-3                              3361521 ONLINE  NO
         4 /u01/app/oracle/Datafile/data_D-ORCL_TS-UNDOTBS1_FNO-4                            3361521 ONLINE  NO
         5 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_PORTLET_FNO-5                   3361521 ONLINE  NO
         6 /u01/app/oracle/Datafile/data_D-ORCL_TS-USERS_FNO-6                               3361521 ONLINE  NO
         7 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_WEBCENTER_FNO-7                 3361521 ONLINE  NO
         8 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_DISCUSS_FNO-8                   3361521 ONLINE  NO
         9 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_STB_FNO-9                           3361521 ONLINE  NO
        10 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAU_FNO-10                          3361521 ONLINE  NO
        11 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_MDS_FNO-11                          3361521 ONLINE  NO
        12 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_OCS_FNO-12                          3361521 ONLINE  NO
        13 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_ACTIVITY_FNO-13                 3361521 ONLINE  NO
        14 /u01/app/oracle/Datafile/data_D-ORCL_TS-DBFS_TS_FNO-14                            3361521 ONLINE  NO

14 rows selected.



Restore and Recover the system datafile using backups


RMAN> restore datafile 1;

Starting restore at 29-12-2022 01:42:42
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/Datafile/data_D-ORCL_TS-SYSTEM_FNO-1
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/RmanBackup/ORCL_T20221229_db_s19_p1_t1124716704
channel ORA_DISK_1: piece handle=/u01/app/oracle/RmanBackup/ORCL_T20221229_db_s19_p1_t1124716704 tag=RMANFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 29-12-2022 01:43:08


SQL> SQL> /

     FILE# NAME                                                                   CHECKPOINT_CHANGE# STATUS  REC
---------- ---------------------------------------------------------------------- ------------------ ------- ---
         1 /u01/app/oracle/Datafile/data_D-ORCL_TS-SYSTEM_FNO-1                                    0 ONLINE
         2 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_OPSS_FNO-2                      3361521 ONLINE  NO
         3 /u01/app/oracle/Datafile/data_D-ORCL_TS-SYSAUX_FNO-3                              3361521 ONLINE  NO
         4 /u01/app/oracle/Datafile/data_D-ORCL_TS-UNDOTBS1_FNO-4                            3361521 ONLINE  NO
         5 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_PORTLET_FNO-5                   3361521 ONLINE  NO
         6 /u01/app/oracle/Datafile/data_D-ORCL_TS-USERS_FNO-6                               3361521 ONLINE  NO
         7 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_WEBCENTER_FNO-7                 3361521 ONLINE  NO
         8 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_DISCUSS_FNO-8                   3361521 ONLINE  NO
         9 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_STB_FNO-9                           3361521 ONLINE  NO
        10 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAU_FNO-10                          3361521 ONLINE  NO
        11 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_MDS_FNO-11                          3361521 ONLINE  NO
        12 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_OCS_FNO-12                          3361521 ONLINE  NO
        13 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_ACTIVITY_FNO-13                 3361521 ONLINE  NO
        14 /u01/app/oracle/Datafile/data_D-ORCL_TS-DBFS_TS_FNO-14                            3361521 ONLINE  NO

14 rows selected.




RMAN> recover datafile 1;

Starting recover at 29-12-2022 01:44:29
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/Archive/ORCL/archivelog/2022_12_29/o1_mf_1_3_ktw1221o_.arc
archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/Archive/ORCL/archivelog/2022_12_29/o1_mf_1_4_ktw124o0_.arc
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/Archive/ORCL/archivelog/2022_12_29/o1_mf_1_5_ktw124sc_.arc
archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/Archive/ORCL/archivelog/2022_12_29/o1_mf_1_6_ktw12fc8_.arc
archived log file name=/u01/app/oracle/Archive/ORCL/archivelog/2022_12_29/o1_mf_1_3_ktw1221o_.arc thread=1 sequence=3
archived log file name=/u01/app/oracle/Archive/ORCL/archivelog/2022_12_29/o1_mf_1_4_ktw124o0_.arc thread=1 sequence=4
media recovery complete, elapsed time: 00:00:01
Finished recover at 29-12-2022 01:44:31

RMAN>



   FILE# NAME                                                                   CHECKPOINT_CHANGE# STATUS  REC
---------- ---------------------------------------------------------------------- ------------------ ------- ---
         1 /u01/app/oracle/Datafile/data_D-ORCL_TS-SYSTEM_FNO-1                              3382895 ONLINE  NO  <<<<<<<----- 10="" 11="" 12="" 13="" 14="" 2="" 3361521="" 3="" 4="" 5="" 6="" 7="" 8="" 9="" app="" atafile="" data_d-orcl_ts-dbfs_ts_fno-14="" data_d-orcl_ts-sysaux_fno-3="" data_d-orcl_ts-undotbs1_fno-4="" data_d-orcl_ts-users_fno-6="" data_d-orcl_ts-wcpvm_ias_activity_fno-13="" data_d-orcl_ts-wcpvm_ias_discuss_fno-8="" data_d-orcl_ts-wcpvm_ias_opss_fno-2="" data_d-orcl_ts-wcpvm_ias_portlet_fno-5="" data_d-orcl_ts-wcpvm_ias_webcenter_fno-7="" data_d-orcl_ts-wcpvm_iau_fno-10="" data_d-orcl_ts-wcpvm_mds_fno-11="" data_d-orcl_ts-wcpvm_ocs_fno-12="" data_d-orcl_ts-wcpvm_stb_fno-9="" no="" online="" oracle="" rman="" rows="" selected.="" u01=""> alter database open ;

Statement processed

RMAN>


====================

now all the scn no are same 


SQL> r
  1* select file#,NAME,checkpoint_change#, status, recover from v$datafile_header

    FILE# NAME                                                                   CHECKPOINT_CHANGE# STATUS  RECOVER
---------- ---------------------------------------------------------------------- ------------------ ------- ------------
         1 /u01/app/oracle/Datafile/data_D-ORCL_TS-SYSTEM_FNO-1                              3402898 ONLINE  NO
         2 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_OPSS_FNO-2                      3402898 ONLINE  NO
         3 /u01/app/oracle/Datafile/data_D-ORCL_TS-SYSAUX_FNO-3                              3402898 ONLINE  NO
         4 /u01/app/oracle/Datafile/data_D-ORCL_TS-UNDOTBS1_FNO-4                            3402898 ONLINE  NO
         5 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_PORTLET_FNO-5                   3402898 ONLINE  NO
         6 /u01/app/oracle/Datafile/data_D-ORCL_TS-USERS_FNO-6                               3402898 ONLINE  NO
         7 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_WEBCENTER_FNO-7                 3402898 ONLINE  NO
         8 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_DISCUSS_FNO-8                   3402898 ONLINE  NO
         9 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_STB_FNO-9                           3402898 ONLINE  NO
        10 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAU_FNO-10                          3402898 ONLINE  NO
        11 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_MDS_FNO-11                          3402898 ONLINE  NO
        12 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_OCS_FNO-12                          3402898 ONLINE  NO
        13 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_ACTIVITY_FNO-13                 3402898 ONLINE  NO
        14 /u01/app/oracle/Datafile/data_D-ORCL_TS-DBFS_TS_FNO-14                            3402898 ONLINE  NO

14 rows selected.



 set pages 200 lines 200
col FILE_NAME for a70
 select FILE_ID,TABLESPACE_NAME,FILE_NAME,STATUS from dba_data_files;

   FILE_ID TABLESPACE_NAME                FILE_NAME                                                              STATUS
---------- ------------------------------ ---------------------------------------------------------------------- ---------
         1 SYSTEM                         /u01/app/oracle/Datafile/data_D-ORCL_TS-SYSTEM_FNO-1                   AVAILABLE
         3 SYSAUX                         /u01/app/oracle/Datafile/data_D-ORCL_TS-SYSAUX_FNO-3                   AVAILABLE
         4 UNDOTBS1                       /u01/app/oracle/Datafile/data_D-ORCL_TS-UNDOTBS1_FNO-4                 AVAILABLE
         6 USERS                          /u01/app/oracle/Datafile/data_D-ORCL_TS-USERS_FNO-6                    AVAILABLE
         2 WCPVM_IAS_OPSS                 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_OPSS_FNO-2           AVAILABLE
         5 WCPVM_IAS_PORTLET              /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_PORTLET_FNO-5        AVAILABLE
         7 WCPVM_IAS_WEBCENTER            /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_WEBCENTER_FNO-7      AVAILABLE
         8 WCPVM_IAS_DISCUSS              /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_DISCUSS_FNO-8        AVAILABLE
         9 WCPVM_STB                      /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_STB_FNO-9                AVAILABLE
        10 WCPVM_IAU                      /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAU_FNO-10               AVAILABLE
        11 WCPVM_MDS                      /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_MDS_FNO-11               AVAILABLE
        12 WCPVM_OCS                      /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_OCS_FNO-12               AVAILABLE
        13 WCPVM_IAS_ACTIVITY             /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_ACTIVITY_FNO-13      AVAILABLE
        14 DBFS_TS                        /u01/app/oracle/Datafile/data_D-ORCL_TS-DBFS_TS_FNO-14                 AVAILABLE

14 rows selected.

=====


SET LINES 200 pagesize 300
COL name             FORM a80
COL status           FORM A12
COL file#            FORM 9999
COL control_file_SCN FORM 999999999999999
COL datafile_SCN     FORM 999999999999999
--
SELECT
 a.name
,a.status
,a.file#
,a.checkpoint_change# control_file_SCN
,b.checkpoint_change# datafile_SCN
,CASE
   WHEN ((a.checkpoint_change# - b.checkpoint_change#) = 0) THEN 'Startup Normal'
   WHEN ((b.checkpoint_change#) = 0)                        THEN 'File Missing?'
   WHEN ((a.checkpoint_change# - b.checkpoint_change#) > 0) THEN 'Media Rec. Req.'
   WHEN ((a.checkpoint_change# - b.checkpoint_change#) < 0) THEN 'Old Control File'
   ELSE 'what the ?'
 END datafile_status
FROM v$datafile        a -- control file SCN for datafile
    ,v$datafile_header b -- datafile header SCN
WHERE a.file# = b.file#
ORDER BY a.file#;


NAME                                                                             STATUS       FILE# CONTROL_FILE_SCN     DATAFILE_SCN DATAFILE_STATUS
-------------------------------------------------------------------------------- ------------ ----- ---------------- ---------------- ----------------
/u01/app/oracle/Datafile/data_D-ORCL_TS-SYSTEM_FNO-1                             SYSTEM           1          3405085          3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_OPSS_FNO-2                     ONLINE           2          3405085          3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-SYSAUX_FNO-3                             ONLINE           3          3405085          3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-UNDOTBS1_FNO-4                           ONLINE           4          3405085          3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_PORTLET_FNO-5                  ONLINE           5          3405085          3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-USERS_FNO-6                              ONLINE           6          3405085          3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_WEBCENTER_FNO-7                ONLINE           7          3405085          3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_DISCUSS_FNO-8                  ONLINE           8          3405085          3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_STB_FNO-9                          ONLINE           9          3405085          3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAU_FNO-10                         ONLINE          10          3405085          3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_MDS_FNO-11                         ONLINE          11          3405085          3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_OCS_FNO-12                         ONLINE          12          3405085          3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_ACTIVITY_FNO-13                ONLINE          13          3405085          3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-DBFS_TS_FNO-14                           ONLINE          14          3405085          3405085 Startup Normal

14 rows selected.

Tuesday, 20 December 2022

Oracle DBA unix useful command

Unix for the DBA How to kill all similar processes with single command (in this case opmn)

 

stty erase ^?
 stty erase ^H

ps -ef | grep opmn |grep -v grep | awk ‘{print $2}’ |xargs -i kill -9 {}
Locating Files under a particular directory

find . -print |grep -i test.sql

 Using AWK in UNIX

To remove a specific column of output from a UNIX command – for example to determine the UNIX process Ids for all Oracle processes on server (second column)

ps -ef |grep -i oracle |awk '{ print $2 }'

Changing the standard prompt for Oracle Users

Edit the .profile for the oracle user

PS1="`hostname`*$ORACLE_SID:$PWD>"

 Display top 10 CPU consumers using the ps command

/usr/ucb/ps auxgw | head -11

 Show number of active Oracle dedicated connection users for a particular ORACLE_SID

ps -ef | grep $ORACLE_SID|grep -v grep|grep -v ora_|wc -l

 Display the number of CPU’s in Solaris

psrinfo -v | grep "Status of processor"|wc -l

Display the number of CPU’s in AIX

lsdev -C | grep Process|wc -l

Display RAM Memory size on Solaris

prtconf |grep -i mem

Display RAM memory size on AIX

First determine name of memory device

lsdev -C |grep mem

then assuming the name of the memory device is ‘mem0’

lsattr -El mem0

Swap space allocation and usage

Solaris : swap -s or swap -l
Aix : lsps -a

 Total number of semaphores held by all instances on server

ipcs -as | awk '{sum += $9} END {print sum}'

View allocated RAM memory segments

ipcs -pmb

Manually deallocate shared memeory segments

ipcrm -m ''

 Show mount points for a disk in AIX

lspv -l hdisk13

 Display amount of occupied space (in KB) for a file or collection of files in a directory or sub-directory

du -ks * | sort -n| tail

Display total file space in a directory

du -ks .

 Cleanup any unwanted trace files more than seven days old

find . *.trc -mtime +7 -exec rm {} \;

 Locate Oracle files that contain certain strings

find . -print | xargs grep rollback

 Locate recently created UNIX files (in the past one day)

find . -mtime -1 -print

 Finding large files on the server (more than 100MB in size)

find . -size +102400 -print

Remove DOS CR/LFs (^M)
Remove DOS style CR/LF characters (^M) from UNIX files using:

    sed -e 's/^M$//' filename > tempfile


sar
$ sar -u 10 8
Reports CPU Utilization (10 seconds apart; 8 times):
Time 	%usr 	%sys 	%wio 	%idle
11:57:31 	72 	28 	0 	0
11:57:41 	70 	30 	0 	0
11:57:51 	70 	30 	0 	0
11:58:01 	68 	32 	0 	0
11:58:11 	67 	33 	0 	0
11:58:21 	65 	28 	0 	7
11:58:31 	73 	27 	0 	0
11:58:41 	69 	31 	0 	0
Average 	69 	30 	0 	1

%usr: Percent of CPU in user mode
%sys: Percent of CPU in system mode
%wio: Percent of CPU running idle with a process waiting for block I/O
%idle: Percent of CPU that is idle


mpstat
$ mpstat 10 2
Reports per-processor statistics on Sun Solaris (10 seconds apart; 8 times):
CPU 	minf 	mjf 	xcal 	intr 	ithr 	csw 	icsw 	migr 	smtx 	srw 	syscl 	usr 	sys 	wt 	idl
0 	6 	8 	0 	438 	237 	246 	85 	0 	0 	21 	8542 	23 	9 	9 	59
0 	0 	29 	0 	744 	544 	494 	206 	0 	0 	95 	110911 	65 	29 	6 	0
ps
$ ps -e -o pcpu -o pid -o user -o args | sort -k 1 | tail -21r
Displays the top 20 CPU users on the system.
%CPU 	PID 	USER 	COMMAND
78.1 	4789 	oracle 	ora_dbwr_DDDS2
8.5 	4793 	oracle 	ora_lgwr_DDDS2
2.4 	6206 	oracle 	oracleDDDS2 (LOCAL=NO)
0.1 	4797 	oracle 	ora_smon_DDDS2
0.1 	6207 	oracle 	oracleDDDS2 (LOCAL=NO)
etc. 	etc. 	etc. 	etc.

The PID column can then be matched with the SPID column on the V$PROCESS view to provide more information on the process:

    SELECT a.username, 
           a.osuser, 
           a.program, 
           spid, 
           sid, 
           a.serial#
    FROM   v$session a,
           v$process b
    WHERE  a.paddr = b.addr
    AND    spid = '&pid';


CRON
There are two methods of editing the crontab file. First you can use the "crontab -l > filename" option to list the contents and pipe this to a file. Once you've editied the file you can then apply it using the "crontab filename":

    * Login as root
    * crontab -l > newcron
    * Edit newcron file.
    * crontab newcron

Alternatively you can use the "crontab -e" option to edit the crontab file directly.

The entries have the following elements:

    field          allowed values
    -----          --------------
    minute         0-59
    hour           0-23
    day of month   1-31
    month          1-12
    day of week    0-7 (both 0 and 7 are Sunday)
    user           Valid OS user
    command        Valid command or script.

The first 5 fields can be specified using the following rules:

    *       - All available values or "first-last".
    3-4     - A single range representing each possible from the start to the end of the range inclusive.
    1,2,5,6 - A specific list of values.
    1-3,5-8 - A specific list of ranges.
    0-23/2  - Every other value in the specified range.

The following entry runs a cleanup script a 01:00 each Sunday. Any output or errors from the script are piped to /dev/null to prevent a buildup of mails to root:

    0 1 * * 0 /u01/app/oracle/dba/weekly_cleanup > /dev/null 2>&1


vmstat
$ vmstat 5 3
Displays system statistics (5 seconds apart; 3 times):
procs 	memory 	page 	disk 	faults 	cpu
r 	b 	w 	swap 	free 	re 	mf 	pi 	po 	fr 	de 	sr 	s0 	s1 	s2 	s3 	in 	sy 	cs 	us 	sy 	id
0 	0 	0 	28872 	8792 	8 	5 	172 	142 	210 	0 	24 	3 	11 	17 	2 	289 	1081 	201 	14 	6 	80
0 	0 	0 	102920 	1936 	1 	95 	193 	6 	302 	1264 	235 	12 	1 	0 	3 	240 	459 	211 	0 	2 	97
0 	0 	0 	102800 	1960 	0 	0 	0 	0 	0 	464 	0 	0 	0 	0 	0 	107 	146 	29 	0 	0 	100

Having any processes in the b or w columns is a sign of a problem system.
Having an id of 0 is a sign that the cpu is overburdoned.
Having high values in pi and po show excessive paging.

    * procs (Reports the number of processes in each of the following states)
          o r : in run queue
          o b : blocked for resources (I/O, paging etc.)
          o w : runnable but swapped
    * memory (Reports on usage of virtual and real memory)
          o swap : swap space currently available (Kbytes)
          o free : size of free list (Kbytes)
    * page (Reports information about page faults and paging activity (units per second)
          o re : page reclaims
          o mf : minor faults
          o pi : Kbytes paged in
          o po : Kbytes paged out
          o fr : Kbytes freed
          o de : anticipated short-term memory shortfall (Kbytes)
          o sr : pages scanned by clock algorith
    * disk (Reports the number of disk operations per second for up to 4 disks
    * faults (Reports the trap/interupt rates (per second)
          o in : (non clock) device interupts
          o si : system calls
          o cs : CPU context switches
    * cpu (Reports the breakdown of percentage usage of CPU time (averaged across all CPUs)
          o us : user time
          o si : system time
          o cs : idle time


File Exists Check
The Korn shell allows you to check for the presence of a file using the "test -s" command. In the following script a backup log is renamed and moved if it is present:

    #!/bin/ksh
    if test -s /backup/daily_backup.log
    then
      DATE_SUFFIX=`date +"%y""%m""%d""%H""%M"`
      mv /backup/daily_backup.log /backup/archive/daily_backup$DATE_SUFFIX.log
    fi

==


Automatic Startup Scripts on Linux
Create a file in the /etc/init.d/ directory, in this case the file is called myservice, containing the commands you wish to run at startup and/or shutdown.

Use the chmod command to set the privileges to 750:

    chmod 750 /etc/init.d/myservice

Link the file into the appropriate run-level script directories:

    ln -s /etc/init.d/myservice /etc/rc0.d/K10myservice
    ln -s /etc/init.d/myservice /etc/rc3.d/S99myservice

Associate the myservice service with the appropriate run levels:

    chkconfig --level 345 dbora on

The script should now be automatically run at startup and shutdown (with "start" or "stop" as a commandline parameter) like other service initialization scripts.

Anuj Singh Oracle DBA

Search This Blog

Total Pageviews

Friday, 3 March 2023

Oracle AWR Report Script top sqls' waits

Oracle AWR Report Script top sqls' waits ..

Thursday, 2 March 2023

Oracle Mini Awr report ....

Tuesday, 7 February 2023

ORA-20020: Database/Instance/Snapshot mismatch

ORA-20020: Database/Instance/Snapshot mismatch ...

Friday, 20 January 2023

Sequence Info

Saturday, 31 December 2022

Oracle How to Restore Of Single PDB via RMAN

Friday, 30 December 2022

Oracle Recover from a loss of the SYSTEM tablespace datafile

Tuesday, 20 December 2022

Oracle DBA unix useful command

Oracle DBA

anuj blog Archive