Querying dba_hist_system_event to summarize the class based summarization of system events from awr data

Lab : Querying dba_hist_system_event to summarize the class based summarization of system events from awr data

Query:

with t as
(select INSTANCE_NUMBER
,snap_id
,WAIT_CLASS
,EVENT_NAME
,(total_waits - lag(total_waits,1,0) over (partition by INSTANCE_NUMBER,WAIT_CLASS,EVENT_NAME order by snap_id)) db_tot_waits
,(TOTAL_TIMEOUTS - lag(TOTAL_TIMEOUTS,1,0) over (partition by INSTANCE_NUMBER,WAIT_CLASS,EVENT_NAME order by snap_id)) db_tot_timeouts
,(TIME_WAITED_MICRO - lag(TIME_WAITED_MICRO,1,0) over (partition by INSTANCE_NUMBER,WAIT_CLASS,EVENT_NAME order by snap_id)) db_tot_timemic
,(TOTAL_WAITS_FG - lag(TOTAL_WAITS_FG,1,0) over (partition by INSTANCE_NUMBER,WAIT_CLASS,EVENT_NAME order by snap_id)) fg_tot_waits
,(TOTAL_TIMEOUTS_FG - lag(TOTAL_TIMEOUTS_FG,1,0) over (partition by INSTANCE_NUMBER,WAIT_CLASS,EVENT_NAME order by snap_id)) fg_tot_timeouts
,(TIME_WAITED_MICRO_FG - lag(TIME_WAITED_MICRO_FG,1,0) over (partition by INSTANCE_NUMBER,WAIT_CLASS,EVENT_NAME order by snap_id)) fg_tot_timemic
from DBA_HIST_SYSTEM_EVENT)
select a.END_INTERVAL_TIME tstmp
,a.instance_number in_nu
,t.wait_class
,sum(t.db_tot_timemic) class_agg_timemic
,sum(t.db_tot_timemic)/decode(sum(t.db_tot_waits),0,1,sum(t.db_tot_waits)) class_res_timemic
,round(sum(t.db_tot_timemic/decode(b.agg_db_tot_time,0,1,b.agg_db_tot_time))*100,2) pct_wt_class_time
,sum(t.db_tot_waits) class_agg_waits
,sum(db_tot_timeouts) class_agg_timeo
,round(sum(fg_tot_timemic)/decode(sum(t.db_tot_timemic),0,1,sum(t.db_tot_timemic))*100,2) pct_fg_class
from dba_hist_snapshot a
,t
,(select instance_number
,snap_id
,sum(db_tot_waits) agg_db_tot_wts
,sum(db_tot_timeouts) agg_db_tot_timout
,sum(db_tot_timemic) agg_db_tot_time
,sum(fg_tot_waits) agg_fg_tot_wts
,sum(fg_tot_timeouts) agg_fg_tot_timeo
,sum(fg_tot_timemic) agg_fg_tot_time
from t
group by instance_number
,snap_id) b
where a.snap_id=t.snap_id
and a.snap_id=b.snap_id
and a.INSTANCE_NUMBER=t.INSTANCE_NUMBER
and a.instance_number=b.instance_number
and a.BEGIN_INTERVAL_TIME > sysdate-2
group by a.END_INTERVAL_TIME
,a.instance_number
,t.wait_class
order by a.END_INTERVAL_TIME
,a.instance_number
,t.wait_class;

Result example:

TSTMP                         ,     IN_NU,WAIT_CLASS     ,CLASS_AGG_TIMEMIC,CLASS_RES_TIMEMIC,PCT_WT_CLASS_TIME,CLASS_AGG_WAITS,CLASS_AGG_TIMEO,PCT_FG_CLASS
------------------------------,----------,---------------,-----------------,-----------------,-----------------,---------------,---------------,------------
20-APR-19 04.27.03.450 PM     ,         1,Administrative ,            16321,            16321,                0,              1,        0,         100
20-APR-19 04.27.03.450 PM     ,         1,Application    ,          5658890,         -2829445,                0,             -2,        0,      102.78
20-APR-19 04.27.03.450 PM     ,         1,Commit         ,          2433284,       -110603.82,                0,            -22,        0,      130.45
20-APR-19 04.27.03.450 PM     ,         1,Concurrency    ,          7756959,       -13080.875,                0,           -593,        0,        81.8
20-APR-19 04.27.03.450 PM     ,         1,Configuration  ,         15554245,       5184748.33,                0,              3,        2,         100
20-APR-19 04.27.03.450 PM     ,         1,Idle           ,       -6.258E+11,        1929801.8,            99.98,        -324287,  -126260,         .05
20-APR-19 04.27.03.450 PM     ,         1,Network        ,             3885,       13.7765957,                0,            282,        0,       82.57
20-APR-19 04.27.03.450 PM     ,         1,Other          ,          9562378,       -133.03623,                0,         -71878,    -7152,      143.58
20-APR-19 04.27.03.450 PM     ,         1,Scheduler      ,         14416183,        -30672.73,                0,           -470,        0,       98.34
20-APR-19 04.27.03.450 PM     ,         1,System I/O     ,       -332436358,       5973.91385,              .05,         -55648,        0,       -6.51
20-APR-19 04.27.03.450 PM     ,         1,User I/O       ,        171322583,       -264795.34,             -.03,           -647,        0,       67.25
20-APR-19 05.00.15.137 PM     ,         1,Administrative ,           184875,          9243.75,                0,             20,        0,       -8.83
20-APR-19 05.00.15.137 PM     ,         1,Application    ,            11573,       136.152941,                0,             85,        0,       90.27
20-APR-19 05.00.15.137 PM     ,         1,Commit         ,           213992,          42798.4,                0,              5,        0,           0
20-APR-19 05.00.15.137 PM     ,         1,Concurrency    ,           257055,       3338.37662,                0,             77,        0,       97.82
20-APR-19 05.00.15.137 PM     ,         1,Configuration  ,                0,                0,                0,              0,        0,           0
20-APR-19 05.00.15.137 PM     ,         1,Idle           ,       8.7817E+10,        1917102.4,             99.9,          45807,    15792,        1.59
20-APR-19 05.00.15.137 PM     ,         1,Network        ,             9689,       15.8316993,                0,            612,        0,        79.7
20-APR-19 05.00.15.137 PM     ,         1,Other          ,         12605424,       1320.76949,              .01,           9544,      631,       32.46
20-APR-19 05.00.15.137 PM     ,         1,Scheduler      ,           135399,       1289.51429,                0,            105,        0,         100
20-APR-19 05.00.15.137 PM     ,         1,System I/O     ,         68315547,       5247.37284,              .08,          13019,        0,        1.77
20-APR-19 05.00.15.137 PM     ,         1,User I/O       ,          8369085,        4197.1339,              .01,           1994,        0,        1.97
20-APR-19 06.00.40.902 PM     ,         1,Administrative ,                0,                0,                0,              0,        0,           0
20-APR-19 06.00.40.902 PM     ,         1,Application    ,                0,                0,                0,              0,        0,           0
20-APR-19 06.00.40.902 PM     ,         1,Commit         ,          1673828,       139485.667,                0,             12,        0,       29.57
20-APR-19 06.00.40.902 PM     ,         1,Concurrency    ,            87558,       587.637584,                0,            149,        0,       85.15
20-APR-19 06.00.40.902 PM     ,         1,Configuration  ,                0,                0,                0,              0,        0,           0
...
21-APR-19 04.00.59.614 PM     ,         1,Idle           ,       1.5709E+11,       1906963.95,            99.91,          82376,    29052,        2.75
21-APR-19 04.00.59.614 PM     ,         1,Network        ,             2272,       23.9157895,                0,             95,        0,         100
21-APR-19 04.00.59.614 PM     ,         1,Other          ,         13535952,       756.113954,              .01,          17902,     2075,       29.28
21-APR-19 04.00.59.614 PM     ,         1,Scheduler      ,           213924,       1258.37647,                0,            170,        0,         100
21-APR-19 04.00.59.614 PM     ,         1,System I/O     ,        129506987,       9125.99443,              .08,          14191,        0,           0
21-APR-19 04.00.59.614 PM     ,         1,User I/O       ,          2095721,       1806.65603,                0,           1160,        0,        5.24
21-APR-19 05.00.20.950 PM     ,         1,Application    ,                0,                0,                0,              0,        0,           0
21-APR-19 05.00.20.950 PM     ,         1,Commit         ,          3888804,         388880.4,                0,             10,        0,       92.64
21-APR-19 05.00.20.950 PM     ,         1,Concurrency    ,           122897,       967.692913,                0,            127,        0,       91.07
21-APR-19 05.00.20.950 PM     ,         1,Configuration  ,                0,                0,                0,              0,        0,           0
21-APR-19 05.00.20.950 PM     ,         1,Idle           ,       1.5227E+11,        1915807.4,            99.87,          79481,    28541,         .13
21-APR-19 05.00.20.950 PM     ,         1,Network        ,                0,                0,                0,              0,        0,           0
21-APR-19 05.00.20.950 PM     ,         1,Other          ,         17030382,       1002.90807,              .01,          16981,     1822,       23.76
21-APR-19 05.00.20.950 PM     ,         1,Scheduler      ,           295912,       2026.79452,                0,            146,        0,       77.19
21-APR-19 05.00.20.950 PM     ,         1,System I/O     ,        177589588,       12890.2945,              .12,          13777,        0,           0
21-APR-19 05.00.20.950 PM     ,         1,User I/O       ,          1434472,       1941.09878,                0,            739,        0,           0

160 rows selected.

Test oracle backup of backupset (both backup/restore)

Lab Exercise: Test oracle backup of backupset (both backup/restore)

For the lab exercise if at all we needed to use sbt library, we will use oracle.disksbt library which is a dummy library provided by oracle.

[read about this here: http://practical-tech.blogspot.com/2012/12/simulating-oracle-tape-backups.html & oracle 9.2 rman guide]

allocate channel t1 device type 'sbt_tape' parms 'SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/tmp)';


setup:
1) create a tablespace for the testing purpose...

create tablespace backupofbackup01 datafile size 10m autoextend on next 10m;
select file_name from dba_data_files where lower(tablespace_name)='backupofbackup01';
alter user scott quota unlimited on backupofbackup01;

2) create a test table...

create table scott.test1 tablespace backupofbackup01 as
select mod(level,2) mdlvl,level lvl
from dual
connect by level < 1000;

3) Try access the table

select mdlvl,count(1)
from scott.test1
group by mdlvl;

4) Put the tbspc in Ro mode and take a backup of the tablespace as backupset to local disk

alter tablespace BACKUPOFBACKUP01 read only;
select tablespace_name,status from dba_tablespaces where tablespace_name='BACKUPOFBACKUP01';

backup disk location: /oradata_2/oratest_20apr19

run
{
allocate channel d1 device type disk;
backup as backupset tablespace BACKUPOFBACKUP01 format '/oradata_2/oratest_20apr19/backup_tbspc_%s_%t_%T';
}

5) Try backing up the backupset of the tbspc to disk and sbt.

to backup backupset...
BACKUP BACKUPSET command

to backup copy backups...
backup copy of
BACKUP DATAFILECOPY FROM TAG

option disk:

get the backupset key value from the last list command for the tablespace backup and use it in the command.

list backupset summary;

run
{
allocate channel d1 device type disk;
backup backupset 90 format '/oradata_2/oratest_dkch/backupdk_tbspc_%d_%s_%t_%T';
}

option tape:

run
{
allocate channel t1 device type 'sbt_tape' parms 'SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/oradata_2/oratest_sbtch)';
backup backupset 90 format 'backuptp_tbspc_%d_%s_%t_%T';
}

6) Try to restore the tablespace from each of this backupset copies and try fetch the records from the test table.

option tape backupset (copy#3):

i) list backup of tablespace BACKUPOFBACKUP01 summary;

ii) manually delete the file from disk /oradata_1/ORATEST/datafile/o1_mf_backupof_gcotgctf_.dbf

rm /oradata_1/ORATEST/datafile/o1_mf_backupof_gcotgctf_.dbf

Try data selection to ensure the accidental damage is done... [if this retrives the rows, try disc from current session, flush the buffer cache and retry]

select mdlvl,count(1)
from scott.test1
group by mdlvl;

select error from v$datafile_header where file#=1014;

>> verify your getting "CANNOT OPEN FILE" message.

iii) To facilitate a tablespace restore on a nonarchivelog db, please keep the tablespace in offline state.Otherwise you may hit ORA-19573 & skipping datafile 1014; already restored to file errors.

alter tablespace BACKUPOFBACKUP01 offline;

iv) restore from the tape backupset copy (#3)
run
{
allocate channel t1 device type 'sbt_tape' parms 'SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/oradata_2/oratest_sbtch)';
restore tablespace BACKUPOFBACKUP01;
}

---below isnt needed, keeping it for reference purpose only---
run
{
allocate channel t1 device type 'sbt_tape' parms 'SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/oradata_2/oratest_sbtch)';
restore tablespace BACKUPOFBACKUP01 force;
}
------------------------

v) Bring the tablespace to online
alter tablespace BACKUPOFBACKUP01 online;
vi) Try data selection

select mdlvl,count(1)
from scott.test1
group by mdlvl;
option disk backup (copy#1) or disk backupset (copy#2):

i) Archive the previous results.

ii) manually delete the file from disk /oradata_1/ORATEST/datafile/o1_mf_backupof_gcotgctf_.dbf  [the name of the file may change based on the previous restore since we are using OMF for this test]

rm /oradata_1/ORATEST/datafile/o1_mf_backupof_gcotgctf_.dbf

Try data selection to ensure the accidental damage is done... [if this retrives the rows, try disc from current session, flush the buffer cache and retry]

select mdlvl,count(1)
from scott.test1
group by mdlvl;

select error from v$datafile_header where file#=1014;

>> verify your getting "CANNOT OPEN FILE" message.

iii) To facilitate a tablespace restore on a nonarchivelog db, please keep the tablespace in offline state.Otherwise you may hit ORA-19573 & skipping datafile 1014; already restored to file errors.

alter tablespace BACKUPOFBACKUP01 offline;

iv) restore from the backupset copy on disk (#2) -- let us try a default restore using disk channel and observe what happens, then let us force it to a specific copy by renaming the other copy.
run
{
allocate channel d1 device type disk;
restore tablespace BACKUPOFBACKUP01;
}

--- it properly picks the copy#1 first and incase the copy#1 isnt available it fails over to the next one which is copy#2 [you will notice messages like "failover to piece handle=" in rman restore when copy#1 is inaccessible].

v) Bring the tablespace to online
alter tablespace BACKUPOFBACKUP01 online;

vi) Try data selection

select mdlvl,count(1)
from scott.test1
group by mdlvl;
vii) Query and store the v$rman_status


select START_TIME,END_TIME,OPERATION,status,MBYTES_PROCESSED,INPUT_BYTES,OUTPUT_BYTES,OPTIMIZED,OUTPUT_DEVICE_TYPE
from v$rman_status
where END_TIME > sysdate -1/24 and operation!='LIST'
order by start_time;

########Execution of setup:

########Step 1)
SQL> create tablespace backupofbackup01 datafile size 10m autoextend on next 10m;

Tablespace created.

SQL> select file_name from dba_data_files where lower(tablespace_name)='backupofbackup01';

FILE_NAME
--------------------------------------------------------------------------------
/oradata_1/ORATEST/datafile/o1_mf_backupof_gcotgctf_.dbf

SQL> alter user scott quota unlimited on backupofbackup01;

User altered.

########Step 2)

SQL> create table scott.test1 tablespace backupofbackup01 as
select mod(level,2) mdlvl,level lvl
from dual
connect by level < 1000;  2    3    4

Table created.

SQL> select table_name,owner,tablespace_name from dba_tables where table_name='TEST1' and owner='SCOTT';

TABLE_NAME OWNER      TABLESPACE_NAME
---------- ---------- ------------------------------
TEST1      SCOTT      BACKUPOFBACKUP01

########Step 3)

SQL> select mdlvl,count(1)
from scott.test1
group by mdlvl;  2    3

     MDLVL   COUNT(1)
---------- ----------
         1        500
         0        499

########Step 4)

SQL> alter tablespace BACKUPOFBACKUP01 read only;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='BACKUPOFBACKUP01';

TABLESPACE_NAME                STATUS
------------------------------ ---------
BACKUPOFBACKUP01               READ ONLY

SQL> select tablespace_name,status from dba_tablespaces where status='READ ONLY';

TABLESPACE_NAME                STATUS
------------------------------ ---------
TBSPC_IMGCPY_COMP_TEST         READ ONLY
BACKUPOFBACKUP01               READ ONLY

RMAN> list backup of tablespace BACKUPOFBACKUP01;

specification does not match any backup in the repository

RMAN> run
{
allocate channel d1 device type disk;
backup as backupset tablespace BACKUPOFBACKUP01 format '/oradata_2/oratest_20apr19/backup_tbspc_%s_%t_%T';
}2> 3> 4> 5>

allocated channel: d1
channel d1: SID=275 device type=DISK

Starting backup at 20-APR-19
channel d1: starting full datafile backup set
channel d1: specifying datafile(s) in backup set
input datafile file number=01014 name=/oradata_1/ORATEST/datafile/o1_mf_backupof_gcotgctf_.dbf
channel d1: starting piece 1 at 20-APR-19
channel d1: finished piece 1 at 20-APR-19
piece handle=/oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420 tag=TAG20190420T175642 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:07
Finished backup at 20-APR-19

Starting Control File and SPFILE Autobackup at 20-APR-19
piece handle=/oradata_1/fast_recovery_area/RMN01TST/autobackup/2019_04_20/o1_mf_s_1006106210_gcov7hkt_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 20-APR-19
released channel: d1

RMAN> list backup of tablespace BACKUPOFBACKUP01;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
90      Full    1.08M      DISK        00:00:01     20-APR-19
        BP Key: 90   Status: AVAILABLE  Compressed: NO  Tag: TAG20190420T175642
        Piece Name: /oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420
  List of Datafiles in backup set 90
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1014    Full 5667749    20-APR-19 /oradata_1/ORATEST/datafile/o1_mf_backupof_gcotgctf_.dbf

RMAN>

RMAN> list backupset summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
47      B  F  A DISK        12-AUG-18       1       1       NO         TAG20180812T180622
..
89      B  F  A DISK        18-NOV-18       1       1       NO         TAG20181118T135357
90      B  F  A DISK        20-APR-19       1       1       NO         TAG20190420T175642    << backupset of interest
91      B  F  A DISK        20-APR-19       1       1       NO         TAG20190420T175650

########Step 5)

Option 1) backup backupset to disk:
RMAN> run
{
allocate channel d1 device type disk;
backup backupset 90 format '/oradata_2/oratest_dkch/backupdk_tbspc_%d_%s_%t_%T';
}2> 3> 4> 5>

allocated channel: d1
channel d1: SID=275 device type=DISK

Starting backup at 20-APR-19
channel d1: input backup set: count=98, stamp=1006106203, piece=1
channel d1: starting piece 1 at 20-APR-19
channel d1: backup piece /oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420
piece handle=/oradata_2/oratest_dkch/backupdk_tbspc_RMN01TST_98_1006106203_20190420 comment=NONE
channel d1: finished piece 1 at 20-APR-19
channel d1: backup piece complete, elapsed time: 00:00:01
Finished backup at 20-APR-19

Starting Control File and SPFILE Autobackup at 20-APR-19
piece handle=/oradata_1/fast_recovery_area/RMN01TST/autobackup/2019_04_20/o1_mf_s_1006107048_gcow1bcm_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 20-APR-19
released channel: d1

RMAN> list backupset summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
47      B  F  A DISK        12-AUG-18       1       1       NO         TAG20180812T180622
80      B  F  A DISK        08-SEP-18       1       1       NO         TAG20180908T114452
82      B  F  A DISK        08-SEP-18       1       1       NO         TAG20180908T121002
84      B  F  A DISK        08-SEP-18       1       1       NO         TAG20180908T135002
86      B  F  A DISK        08-SEP-18       1       1       NO         TAG20180908T140833
87      B  F  A DISK        18-NOV-18       1       1       NO         TAG20181118T135118
89      B  F  A DISK        18-NOV-18       1       1       NO         TAG20181118T135357
90      B  F  A DISK        20-APR-19       1       2       NO         TAG20190420T175642
91      B  F  A DISK        20-APR-19       1       1       NO         TAG20190420T175650
92      B  F  A DISK        20-APR-19       1       1       NO         TAG20190420T181048

RMAN> list backup of tablespace BACKUPOFBACKUP01;


List of Backup Sets
===================


BS Key  Type LV Size
------- ---- -- ----------
90      Full    1.08M
  List of Datafiles in backup set 90
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1014    Full 5667749    20-APR-19 /oradata_1/ORATEST/datafile/o1_mf_backupof_gcotgctf_.dbf

  Backup Set Copy #1 of backup set 90
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:00:01     20-APR-19       NO         TAG20190420T175642

    List of Backup Pieces for backup set 90 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    90      1   AVAILABLE   /oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420

  Backup Set Copy #2 of backup set 90
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:00:01     20-APR-19       NO         TAG20190420T175642

    List of Backup Pieces for backup set 90 Copy #2            <<<<<<<<<<< 2nd copy information, we retained here the tag as well.
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    92      1   AVAILABLE   /oradata_2/oratest_dkch/backupdk_tbspc_RMN01TST_98_1006106203_20190420

RMAN> list backup of spfile;


List of Backup Sets
===================


...

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
89      Full    20.58M     DISK        00:00:02     18-NOV-18
        BP Key: 89   Status: AVAILABLE  Compressed: NO  Tag: TAG20181118T135357
        Piece Name: /oradata_1/fast_recovery_area/RMN01TST/autobackup/2018_11_18/o1_mf_s_992526837_fz1zmprx_.bkp
  SPFILE Included: Modification time: 18-NOV-18
  SPFILE db_unique_name: RMN01TST

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
91      Full    20.58M     DISK        00:00:14     20-APR-19
        BP Key: 91   Status: AVAILABLE  Compressed: NO  Tag: TAG20190420T175650
        Piece Name: /oradata_1/fast_recovery_area/RMN01TST/autobackup/2019_04_20/o1_mf_s_1006106210_gcov7hkt_.bkp
  SPFILE Included: Modification time: 20-APR-19
  SPFILE db_unique_name: RMN01TST

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
92      Full    20.58M     DISK        00:00:03     20-APR-19
        BP Key: 93   Status: AVAILABLE  Compressed: NO  Tag: TAG20190420T181048
        Piece Name: /oradata_1/fast_recovery_area/RMN01TST/autobackup/2019_04_20/o1_mf_s_1006107048_gcow1bcm_.bkp
  SPFILE Included: Modification time: 20-APR-19
  SPFILE db_unique_name: RMN01TST

RMAN> list backup of tablespace BACKUPOFBACKUP01 summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
90      B  F  A DISK        20-APR-19       1       2       NO         TAG20190420T175642

RMAN>


Observation: See we now 2 copies vs earlier 1 copies as reported by "list backupset summary" against BS key = 90. Important to note the BP key value is 92 still vs 90 for the first copy, which I think is unique key.

Option 2) backup backupset to tape:

RMAN> run
{
allocate channel t1 device type 'sbt_tape' parms 'SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/oradata_2/oratest_sbtch)';
backup backupset 90 format 'backuptp_tbspc_%d_%s_%t_%T';
}2> 3> 4> 5>

allocated channel: t1
channel t1: SID=275 device type=SBT_TAPE
channel t1: WARNING: Oracle Test Disk API

Starting backup at 20-APR-19
channel t1: input backup set: count=98, stamp=1006106203, piece=1
channel t1: starting piece 1 at 20-APR-19
channel t1: backup piece /oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420
piece handle=backuptp_tbspc_RMN01TST_98_1006106203_20190420 comment=API Version 2.0,MMS Version 8.1.3.0
channel t1: finished piece 1 at 20-APR-19
channel t1: backup piece complete, elapsed time: 00:00:01
Finished backup at 20-APR-19

Starting Control File and SPFILE Autobackup at 20-APR-19
piece handle=c-2907708373-20190420-02 comment=API Version 2.0,MMS Version 8.1.3.0
Finished Control File and SPFILE Autobackup at 20-APR-19
released channel: t1

RMAN>

RMAN> list backup of tablespace BACKUPOFBACKUP01 summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
90      B  F  A *           20-APR-19       1       3       NO         TAG20190420T175642

RMAN> list backup of tablespace BACKUPOFBACKUP01;


List of Backup Sets
===================


BS Key  Type LV Size
------- ---- -- ----------
90      Full    1.08M
  List of Datafiles in backup set 90
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1014    Full 5667749    20-APR-19 /oradata_1/ORATEST/datafile/o1_mf_backupof_gcotgctf_.dbf

  Backup Set Copy #2 of backup set 90
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:00:01     20-APR-19       NO         TAG20190420T175642

    List of Backup Pieces for backup set 90 Copy #2
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    92      1   AVAILABLE   /oradata_2/oratest_dkch/backupdk_tbspc_RMN01TST_98_1006106203_20190420

  Backup Set Copy #1 of backup set 90
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:00:01     20-APR-19       NO         TAG20190420T175642

    List of Backup Pieces for backup set 90 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    90      1   AVAILABLE   /oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420

  Backup Set Copy #3 of backup set 90
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  SBT_TAPE    00:00:01     20-APR-19       NO         TAG20190420T175642

    List of Backup Pieces for backup set 90 Copy #3                <<< 3rd copy of the backupset 90, this time it is written to tape.
    BP Key  Pc# Status      Media                   Piece Name
    ------- --- ----------- ----------------------- ----------
    94      1   AVAILABLE   /oratest_sbtch,backuptp_tbspc_RMN0 backuptp_tbspc_RMN01TST_98_1006106203_20190420

RMAN> list backup of spfile completed after 'sysdate-1';


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
91      Full    20.58M     DISK        00:00:14     20-APR-19
        BP Key: 91   Status: AVAILABLE  Compressed: NO  Tag: TAG20190420T175650
        Piece Name: /oradata_1/fast_recovery_area/RMN01TST/autobackup/2019_04_20/o1_mf_s_1006106210_gcov7hkt_.bkp
  SPFILE Included: Modification time: 20-APR-19
  SPFILE db_unique_name: RMN01TST

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
92      Full    20.58M     DISK        00:00:03     20-APR-19
        BP Key: 93   Status: AVAILABLE  Compressed: NO  Tag: TAG20190420T181048
        Piece Name: /oradata_1/fast_recovery_area/RMN01TST/autobackup/2019_04_20/o1_mf_s_1006107048_gcow1bcm_.bkp
  SPFILE Included: Modification time: 20-APR-19
  SPFILE db_unique_name: RMN01TST

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
93      Full    24.00M     SBT_TAPE    00:00:01     20-APR-19
        BP Key: 95   Status: AVAILABLE  Compressed: NO  Tag: TAG20190420T182221
        Handle: c-2907708373-20190420-02   Media: /oratest_sbtch,c-2907708373-201904
  SPFILE Included: Modification time: 20-APR-19
  SPFILE db_unique_name: RMN01TST

RMAN>

Observation: Note this time the media became '*' since we now backups present in both disk and tape. The copy# increased by 1, so the # of backup copies count is 3 for tablespace BACKUPOFBACKUP01.

Allset with first round of tests with backupsets. Now let us try deleting the file and perform restoration from each of this backups to see if it works fine.
########step 6) Restore test.

>>>>>>>>>>>>>>>option tape restore (copy#3)

RMAN> list backup of tablespace backupofbackup01 summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
90      B  F  A *           20-APR-19       1       3       NO         TAG20190420T175642

RMAN>

SQL> !rm /oradata_1/ORATEST/datafile/o1_mf_backupof_gcpkd96o_.dbf

SQL> !ls -altr /oradata_1/ORATEST/datafile/o1_mf_backupof_gcpkd96o_.dbf
ls: cannot access /oradata_1/ORATEST/datafile/o1_mf_backupof_gcpkd96o_.dbf: No such file or directory


SQL> alter system flush buffer_cache;

System altered.

SQL> select mdlvl,count(1)
from scott.test1
group by mdlvl;  2    3
select mdlvl,count(1)
*
ERROR at line 1:
ORA-01116: error in opening database file 1014
ORA-01110: data file 1014:
'/oradata_1/ORATEST/datafile/o1_mf_backupof_gcpkd96o_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


SQL> select error from v$datafile_header where file#=1014;

ERROR
-----------------------------------------------------------------
CANNOT OPEN FILE


SQL> alter tablespace BACKUPOFBACKUP01 offline;

Tablespace altered.

SQL>

RMAN> run
{
allocate channel t1 device type 'sbt_tape' parms 'SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/oradata_2/oratest_sbtch)';
restore tablespace BACKUPOFBACKUP01;
}2> 3> 4> 5>

released channel: ORA_DISK_1
allocated channel: t1
channel t1: SID=29 device type=SBT_TAPE
channel t1: WARNING: Oracle Test Disk API

Starting restore at 21-APR-19

channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 01014 to /oradata_1/ORATEST/datafile/o1_mf_backupof_gcpkd96o_.dbf
channel t1: reading from backup piece backuptp_tbspc_RMN01TST_98_1006106203_20190420
channel t1: piece handle=backuptp_tbspc_RMN01TST_98_1006106203_20190420 tag=TAG20190420T175642
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:01
Finished restore at 21-APR-19
released channel: t1

RMAN>

SQL> alter tablespace BACKUPOFBACKUP01 online;

Tablespace altered.

SQL> select tablespace_name,status from dba_Tablespaces where tablespace_name='BACKUPOFBACKUP01';

TABLESPACE_NAME                STATUS
------------------------------ ---------
BACKUPOFBACKUP01               READ ONLY

SQL>

SQL> select mdlvl,count(1)
from scott.test1
group by mdlvl;  2    3

     MDLVL   COUNT(1)
---------- ----------
         1        500
         0        499

SQL>

=> so reading the backup piece sent to tape works fine, which is the copy#3 (of backupset)

>>>>>>>>>>>>>>>option disk restore (copy#1  - which is the default)

Now let us test backupset copy#1 or actual backupset as preferred by oracle...

SQL> !rm /oradata_1/ORATEST/datafile/o1_mf_backupof_gcpl7ovz_.dbf

SQL> !ls -altr /oradata_1/ORATEST/datafile/o1_mf_backupof_gcpl7ovz_.dbf
ls: cannot access /oradata_1/ORATEST/datafile/o1_mf_backupof_gcpl7ovz_.dbf: No such file or directory


SQL> alter system flush buffer_cache;

System altered.

SQL> select mdlvl,count(1)
from scott.test1
group by mdlvl;  2    3
select mdlvl,count(1)
*
ERROR at line 1:
ORA-01116: error in opening database file 1014
ORA-01110: data file 1014:
'/oradata_1/ORATEST/datafile/o1_mf_backupof_gcpl7ovz_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


SQL> select error from v$datafile_header where file#=1014;

ERROR
-----------------------------------------------------------------
CANNOT OPEN FILE

SQL>

SQL> alter tablespace BACKUPOFBACKUP01 offline;

Tablespace altered.

SQL>


RMAN> run
{
allocate channel d1 device type disk;
restore tablespace BACKUPOFBACKUP01;
}2> 3> 4> 5>

allocated channel: d1
channel d1: SID=29 device type=DISK

Starting restore at 21-APR-19

channel d1: starting datafile backup set restore
channel d1: specifying datafile(s) to restore from backup set
channel d1: restoring datafile 01014 to /oradata_1/ORATEST/datafile/o1_mf_backupof_gcpl7ovz_.dbf
channel d1: reading from backup piece /oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420            <<<<<<<< this is the actual backupset
channel d1: piece handle=/oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420 tag=TAG20190420T175642
channel d1: restored backup piece 1
channel d1: restore complete, elapsed time: 00:00:01
Finished restore at 21-APR-19
released channel: d1

RMAN>

SQL> alter tablespace BACKUPOFBACKUP01 online;

Tablespace altered.

SQL> select mdlvl,count(1)
from scott.test1
group by mdlvl  2    3  ;

     MDLVL   COUNT(1)
---------- ----------
         1        500
         0        499

>>>>>>>>>>>>>>>option disk restore (copy#2  - which is the failover)

Let us now rename this actual backupset and see if this restore can fallback to copy#2 (the one in disk)

SQL> !mv /oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420 /oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420_KEEPSAFE

SQL> !ls -altr /oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420
ls: cannot access /oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420: No such file or directory

SQL>

SQL> alter system flush buffer_cache;

System altered.

SQL> select mdlvl,count(1)
from scott.test1
group by mdlvl  2    3  ;
select mdlvl,count(1)
*
ERROR at line 1:
ORA-01116: error in opening database file 1014
ORA-01110: data file 1014:
'/oradata_1/ORATEST/datafile/o1_mf_backupof_gcplkdns_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


SQL> select error from v$datafile_header where file#=1014;

ERROR
-----------------------------------------------------------------
CANNOT OPEN FILE

SQL> alter tablespace BACKUPOFBACKUP01 offline;

Tablespace altered.

SQL>

RMAN> run
{
allocate channel d1 device type disk;
restore tablespace BACKUPOFBACKUP01;
}2> 3> 4> 5>

allocated channel: d1
channel d1: SID=29 device type=DISK

Starting restore at 21-APR-19

channel d1: starting datafile backup set restore
channel d1: specifying datafile(s) to restore from backup set
channel d1: restoring datafile 01014 to /oradata_1/ORATEST/datafile/o1_mf_backupof_gcplkdns_.dbf
channel d1: reading from backup piece /oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420
channel d1: errors found reading piece handle=/oradata_2/oratest_20apr19/backup_tbspc_98_1006106203_20190420
channel d1: failover to piece handle=/oradata_2/oratest_dkch/backupdk_tbspc_RMN01TST_98_1006106203_20190420 tag=TAG20190420T175642
channel d1: restored backup piece 1
channel d1: restore complete, elapsed time: 00:00:01
Finished restore at 21-APR-19
released channel: d1

RMAN>

SQL> select error from v$datafile_header where file#=1014;

ERROR
-----------------------------------------------------------------
OFFLINE NORMAL

SQL> alter tablespace BACKUPOFBACKUP01 online;

Tablespace altered.

SQL> select error from v$datafile_header where file#=1014;

ERROR
-----------------------------------------------------------------


SQL> select mdlvl,count(1)
from scott.test1
group by mdlvl  2    3  ;

     MDLVL   COUNT(1)
---------- ----------
         1        500
         0        499

SQL> select file_name from dba_data_Files where tablespace_name='BACKUPOFBACKUP01';

FILE_NAME
--------------------------------------------------------------------------------
/oradata_1/ORATEST/datafile/o1_mf_backupof_gcpltsnk_.dbf

SQL>

SQL> select START_TIME,END_TIME,OPERATION,status,MBYTES_PROCESSED,INPUT_BYTES,OUTPUT_BYTES,OPTIMIZED,OUTPUT_DEVICE_TYPE
from v$rman_status
where END_TIME > sysdate -1/24 and operation!='LIST'
order by start_time;  2    3    4

START_TIME          END_TIME            OPERATION                         STATUS                  MBYTES_PROCESSED INPUT_BYTES OUTPUT_BYTES OPT OUTPUT_DEVICE_TYP
------------------- ------------------- --------------------------------- ----------------------- ---------------- ----------- ------------ --- -----------------
20/04/2019 23:47:55 21/04/2019 00:54:14 RMAN                              RUNNING WITH ERRORS                    0           0              0 NO
21/04/2019 00:15:03 21/04/2019 00:15:06 RESTORE                           COMPLETED                             10           0       10485760 NO
21/04/2019 00:26:25 21/04/2019 00:26:26 VALIDATE                          FAILED                                 0           0              0 NO
21/04/2019 00:29:40 21/04/2019 00:29:42 RESTORE                           COMPLETED                             10           0       10485760 NO
21/04/2019 00:34:24 21/04/2019 00:34:27 RESTORE                           FAILED                                 0     2080768              0 NO
21/04/2019 00:34:51 21/04/2019 00:34:53 RESTORE                           COMPLETED                             10     1130496       10485760 NO  DISK
21/04/2019 00:39:52 21/04/2019 00:39:54 RESTORE                           COMPLETED                             10     1130496       10485760 NO  DISK

7 rows selected.



===> Summary: So oracle's backup of backupset works fine with both disk and tape copies of it. We just need to be careful when trying this restore operation on a nonarchivelog mode db to avoid ORA-19753 and skip already restored file RMAN errors.
Thanks for the visit, your valuable comments are welcome :)

Aggregate dba_hist_active_sess_history to find out the total # of sessions logged in overall, the aggregated elapsed time, aggregated dbtime,aggregated pga max size and aggregated temp max size in the window we specify

Obective: let us aggregate dba_hist_active_sess_history to find out the total # of sessions logged in overall, the aggregated elapsed time, aggregated dbtime,aggregated pga max size and aggregated temp max size in the window we specify.

Query:

set lines 300
set pages 3000
set colsep ,

variable d1 varchar2(20);
variable d2 varchar2(20);
prompt enter date in format "DD/MM/YYYY HH24:MI:SS" d1:
exec :d1:=&d1
prompt enter date in format "DD/MM/YYYY HH24:MI:SS" d2:

exec :d2:=&d2

with dhash as
(
select a.instance_number,a.session_id,a.session_serial#,b.username,a.program,a.machine,min(cast(a.sample_time as date)) firstsample,(max(cast(a.sample_time as date))-min(cast(a.sample_time as date)))*86400 sess_ela_sec_insamp,sum(tm_delta_time) sess_dbtime_insamp,max(a.PGA_ALLOCATED) max_pga,max(TEMP_SPACE_ALLOCATED) max_tmps
from dba_hist_active_sess_history a,dba_users b
where a.sample_time between to_date(:d1,'DD/MM/YYYY HH24:MI:SS') and to_date(:d2,'DD/MM/YYYY HH24:MI:SS')
and b.user_id=a.user_id
group by a.instance_number,a.session_id,a.session_serial#,b.username,a.program,a.machine
order by a.instance_number,a.session_id,a.session_serial#,b.username,a.program,a.machine)
select count(1) session_cnt,sum(sess_ela_sec_insamp) sum_ela,sum(sess_dbtime_insamp)/1000/1000 sum_dbt_sec,sum(max_pga) sum_mxpga,sum(max_tmps) sum_mxtmp
from dhash;

SESSION_CNT,   SUM_ELA,   SUM_DBT, SUM_MXPGA, SUM_MXTMP
-----------,----------,----------,----------,----------
        177,  19009211,4.2618E+11,1071654912,  28311552

Report by hour:

with dhash as
(
select a.instance_number,a.session_id,a.session_serial#,b.username,a.program,a.machine,min(cast(a.sample_time as date)) firstsample,(max(cast(a.sample_time as date))-min(cast(a.sample_time as date)))*86400 sess_ela_sec_insamp,sum(tm_delta_time) sess_dbtime_insamp,max(a.PGA_ALLOCATED) max_pga,max(TEMP_SPACE_ALLOCATED) max_tmps
from dba_hist_active_sess_history a,dba_users b
where a.sample_time between to_date(:d1,'DD/MM/YYYY HH24:MI:SS') and to_date(:d2,'DD/MM/YYYY HH24:MI:SS')
and b.user_id=a.user_id
group by a.instance_number,a.session_id,a.session_serial#,b.username,a.program,a.machine
order by a.instance_number,a.session_id,a.session_serial#,b.username,a.program,a.machine)
select trunc(firstsample,'HH') firstsample_HH,count(1) session_cnt,sum(sess_ela_sec_insamp) sum_ela,sum(sess_dbtime_insamp)/1000/1000 sum_dbt_sec,sum(max_pga) sum_mxpga,sum(max_tmps) sum_mxtmp
from dhash
group by trunc(firstsample,'HH')

order by 1;

SQL> with dhash as
(
select a.instance_number,a.session_id,a.session_serial#,b.username,a.program,a.machine,min(cast(a.sample_time as date)) firstsample,(max(cast(a.sample_time as date))-min(cast(a.sample_time as date)))*86400 sess_ela_sec_insamp,sum(tm_delta_time) sess_dbtime_insamp,max(a.PGA_ALLOCATED) max_pga,max(TEMP_SPACE_ALLOCATED) max_tmps
from dba_hist_active_sess_history a,dba_users b
where a.sample_time between to_date(:d1,'DD/MM/YYYY HH24:MI:SS') and to_date(:d2,'DD/MM/YYYY HH24:MI:SS')
and b.user_id=a.user_id
group by a.instance_number,a.session_id,a.session_serial#,b.username,a.program,a.machine
order by a.instance_number,a.session_id,a.session_serial#,b.username,a.program,a.machine)
select trunc(firstsample,'HH') firstsample_HH,count(1) session_cnt,sum(sess_ela_sec_insamp) sum_ela,sum(sess_dbtime_insamp)/1000/1000 sum_dbt_sec,sum(max_pga) sum_mxpga,sum(max_tmps) sum_mxtmp
from dhash
group by trunc(firstsample,'HH')
order by 1;  2    3    4    5    6    7    8    9   10   11   12

FIRSTSAMPLE_HH     ,SESSION_CNT,   SUM_ELA,SUM_DBT_SEC, SUM_MXPGA, SUM_MXTMP
-------------------,-----------,----------,-----------,----------,----------
10/03/2019 13:00:00,         33,  14649921, 208786.223, 189300736,   1048576
..
07/04/2019 15:00:00,          4,        10,  12.442203,  21295104,   2097152

17 rows selected.

Thank you for paying the visit.

version 3 - Write a query to find out how many new connections or session were connected in db from dba_hist_active_sess_history - aggregated by day/hr/mi

Objective: In continuation to the previous 2 blogs or versions on determining sessions which are connected on db from dba_hist_active_Sess_history. In this blog we will see how we can aggregate the metrics to report it based on day/hr/mi.

Query:

--- version 3 of new session identification by sample as we like (more of aggregation and averaging).

variable d1 varchar2(20);
variable d2 varchar2(20);
prompt enter date in format "DD/MM/YYYY HH24:MI:SS" d1:
exec :d1:=&d1
prompt enter date in format "DD/MM/YYYY HH24:MI:SS" d2:
exec :d2:=&d2

SQL> exec :d1:=&d1
Enter value for d1: '07/03/2019 00:00:00'

PL/SQL procedure successfully completed.

SQL> exec :d2:=&d2
Enter value for d2: '07/04/2019 23:59:59'

PL/SQL procedure successfully completed.


set lines 500
set pages 49999
set colsep ,
col program for a45 wrap
col username for a15
col machine for a30
set trim on
set trims on
alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
-- remember to ignore the very first sample output on this query.
--daywise query first
with dhash as
(
select a.instance_number,a.session_id,a.session_serial#,b.username,a.program,a.machine,min(cast(a.sample_time as date)) firstsample,(max(cast(a.sample_time as date))-min(cast(a.sample_time as date)))*86400 sess_ela_sec_insamp,sum(tm_delta_time) sess_dbtime_insamp,max(a.PGA_ALLOCATED) max_pga,max(TEMP_SPACE_ALLOCATED) max_tmps
from dba_hist_active_sess_history a,dba_users b
where a.sample_time between to_date(:d1,'DD/MM/YYYY HH24:MI:SS') and to_date(:d2,'DD/MM/YYYY HH24:MI:SS')
and b.user_id=a.user_id
group by a.instance_number,a.session_id,a.session_serial#,b.username,a.program,a.machine
order by a.instance_number,a.session_id,a.session_serial#,b.username,a.program,a.machine)
select trunc(firstsample),count(1)
from dhash
group by trunc(firstsample)
order by trunc(firstsample);

TRUNC(FIRSTSAMPLE) ,  COUNT(1)
-------------------,----------
10/03/2019 00:00:00,        61
..
07/04/2019 00:00:00,        34

--hrly query
with dhash as
(
select a.instance_number,a.session_id,a.session_serial#,b.username,a.program,a.machine,min(cast(a.sample_time as date)) firstsample,(max(cast(a.sample_time as date))-min(cast(a.sample_time as date)))*86400 sess_ela_sec_insamp,sum(tm_delta_time) sess_dbtime_insamp,max(a.PGA_ALLOCATED) max_pga,max(TEMP_SPACE_ALLOCATED) max_tmps
from dba_hist_active_sess_history a,dba_users b
where a.sample_time between to_date(:d1,'DD/MM/YYYY HH24:MI:SS') and to_date(:d2,'DD/MM/YYYY HH24:MI:SS')
and b.user_id=a.user_id
group by a.instance_number,a.session_id,a.session_serial#,b.username,a.program,a.machine
order by a.instance_number,a.session_id,a.session_serial#,b.username,a.program,a.machine)
select trunc(firstsample,'HH'),count(1)
from dhash
group by trunc(firstsample,'HH')
order by trunc(firstsample,'HH');

TRUNC(FIRSTSAMPLE,',  COUNT(1)
-------------------,----------
10/03/2019 13:00:00,        33
10/03/2019 14:00:00,         6
10/03/2019 15:00:00,         5
...
07/04/2019 14:00:00,        30
07/04/2019 15:00:00,         4

-- minute precision

with dhash as
(
select a.instance_number,a.session_id,a.session_serial#,b.username,a.program,a.machine,min(cast(a.sample_time as date)) firstsample,(max(cast(a.sample_time as date))-min(cast(a.sample_time as date)))*86400 sess_ela_sec_insamp,sum(tm_delta_time) sess_dbtime_insamp,max(a.PGA_ALLOCATED) max_pga,max(TEMP_SPACE_ALLOCATED) max_tmps
from dba_hist_active_sess_history a,dba_users b
where a.sample_time between to_date(:d1,'DD/MM/YYYY HH24:MI:SS') and to_date(:d2,'DD/MM/YYYY HH24:MI:SS')
and b.user_id=a.user_id
group by a.instance_number,a.session_id,a.session_serial#,b.username,a.program,a.machine
order by a.instance_number,a.session_id,a.session_serial#,b.username,a.program,a.machine)
select trunc(firstsample,'MI'),count(1)
from dhash
group by trunc(firstsample,'MI')
order by trunc(firstsample,'MI');

TRUNC(FIRSTSAMPLE,',  COUNT(1)
-------------------,----------
10/03/2019 13:48:00,         2
10/03/2019 13:49:00,         4
10/03/2019 13:50:00,        25
10/03/2019 13:51:00,         1
...
07/04/2019 15:16:00,         2
07/04/2019 15:30:00,         1
07/04/2019 15:43:00,         1

58 rows selected.

Thanks for paying a visit.

Evaluate the shared server vs dedicated server execution differences on a concurrent job situation

Lab objective: Evaluate the shared server vs dedicated server execution differences on a concurrent job situation

This time, we have to spawn 10 connections concurrently in dedicated & in shared mode.

Queries to be used for this excercise:

To monitor the pga/uga per group of connection:

---monitor_pgauga.sql

set lines 300
set pages 3000
set feedback off
select c.server,a.name,count(c.sid),sum(b.value)
from v$statname a, v$sesstat b,v$session c
where c.username='SCOTT'
and b.sid=c.sid
and a.statistic#=b.statistic#
and lower(a.name) in ('session uga memory','session uga memory max','session pga memory','session pga memory max')
group by c.server,a.name
order by 1,2;
exit;

---ugaexamination_lab.sql

set term off
select a.owner,a.object_id,b.object_name
from dba_objects a,dba_objects b
where a.object_id=b.object_id
and a.object_id > 10000;
dbms_lock.sleep(1);
select a.owner,a.object_id,b.object_name
from dba_objects a,dba_objects b
where a.object_id=b.object_id
and a.object_id > 10000;
dbms_lock.sleep(1);
select a.owner,a.object_id,b.object_name
from dba_objects a,dba_objects b
where a.object_id=b.object_id
and a.object_id > 10000;
exit;

---- shell script to generate the workload of dedicated connection

##wrapper_launch_dedicated_conn.sh
#!/bin/ksh
export ORAENV_ASK=NO
. oraenv testinst
sqlplus -s '/as sysdba' @create_snap.sql
i=1
while [[ $i -le $1 ]];
do
dt=`date +%d%m%y_%H%M%S`
sqlplus -s scott/<PWD>@oraded @ugaexamination_lab.sql &
(( i++ ))
done
statprcnt=`ps -ef|grep -i sqlplus|grep -i ugaexamination_lab.sql|wc -l`
while [[ ${statprcnt} -gt 0 ]];
do
sleep 3
statprcnt=`ps -ef|grep -i sqlplus|grep -i ugaexamination_lab.sql|wc -l`
done
sqlplus -s '/as sysdba' @create_snap.sql
exit

---- shell script to monitor the pga/uga utilisation

###monitor_pgauga.sh
#!/bin.ksh
export ORAENV_ASK=NO
. oraenv testinst
while :
do
echo "###################"
dt=`date +%d%m%y_%H%M%S`
echo "zzzzzz $dt"
sqlplus -s '/as sysdba' @monitor_pgauga.sql
dt1=`date +%d%m%y_%H%M%S`
echo "$dt1: going to sleep rountine 1 for 1 secs.. press ctrl+c here if you intend to kill the script"
sleep 1
dt2=`date +%d%m%y_%H%M%S`
echo "$dt2: going to sleep rountine 1 for 1 secs.."
sleep 1
done


---- shell script to generate the workload of shared connection

##wrapper_launch_shared_conn.sh
#!/bin/ksh
export ORAENV_ASK=NO
. oraenv testinst
sqlplus -s '/as sysdba' @create_snap.sql
i=1
while [[ $i -le $1 ]];
do
dt=`date +%d%m%y_%H%M%S`
sqlplus -s scott/<PWD>@orashared @ugaexamination_lab.sql &
(( i++ ))
done
statprcnt=`ps -ef|grep -i sqlplus|grep -i ugaexamination_lab.sql|wc -l`
while [[ ${statprcnt} -gt 0 ]];
do
sleep 3
statprcnt=`ps -ef|grep -i sqlplus|grep -i ugaexamination_lab.sql|wc -l`
done
sqlplus -s '/as sysdba' @create_snap.sql
exit

===> Round 1, launch the below shell scripts for dedicated connection test.

screen 1:
./monitor_pgauga.sh

example output:
###################
zzzzzz 170319_205801
170319_205801: going to sleep rountine 1 for 1 secs.. press ctrl+c here if you intend to kill the script
170319_205802: going to sleep rountine 1 for 1 secs..
###################
zzzzzz 170319_205804

SERVER    NAME                                                             COUNT(C.SID) SUM(B.VALUE)
--------- ---------------------------------------------------------------- ------------ ------------
DEDICATED session pga memory                                                         10     13941264
DEDICATED session pga memory max                                                     10     14400016
DEDICATED session uga memory                                                         10      3375896
DEDICATED session uga memory max                                                     10      4015872
170319_205804: going to sleep rountine 1 for 1 secs.. press ctrl+c here if you intend to kill the script
170319_205805: going to sleep rountine 1 for 1 secs..
###################


screen 2:
./wrapper_launch_dedicated_conn.sh 10

example output:
##################
The Oracle base has been set to /u01/app/oracle

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

[oracle@host testdir]$

===> Bounce the DB

===> Round 2, lauch the below shell scripts for shared server connection test.
screen 1:

./monitor_pgauga.sh

170319_212839: going to sleep rountine 1 for 1 secs..
###################
zzzzzz 170319_212840

SERVER    NAME                                                             COUNT(C.SID) SUM(B.VALUE)
--------- ---------------------------------------------------------------- ------------ ------------
SHARED    session pga memory                                                          7     47667512
SHARED    session pga memory max                                                      7     52927448
SHARED    session uga memory                                                          7      8654272
SHARED    session uga memory max                                                      7      8760544
170319_212841: going to sleep rountine 1 for 1 secs.. press ctrl+c here if you intend to kill the script
170319_212842: going to sleep rountine 1 for 1 secs..
###################


screen 2:

./wrapper_launch_shared_conn.sh 10

example output:
The Oracle base has been set to /u01/app/oracle

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

[oracle@host testdir]$

Differences:

Fact 1 ) Examine session response time:

Dedicated conn lasted for : 170319_205804 to 170319_205954 [until the last connection exited] => 110 seconds
Shared conn lasted for : 170319_212624 to 170319_212921  [until the last connection exited] => 177 seconds

So there is about 67 seconds latency on shared server.

Fact 2) Examine memory utilisation:
Dedicated: [last but 1 snap view - when all 10 connections were active] - here the uga resides in pga

SERVER    NAME                                                             COUNT(C.SID) SUM(B.VALUE)
--------- ---------------------------------------------------------------- ------------ ------------
DEDICATED session pga memory                                                         10    111852048
DEDICATED session pga memory max                                                     10    116111888
DEDICATED session uga memory                                                         10    101117584
DEDICATED session uga memory max                                                     10    101264424

So overall PGA use is 110.73 MB.

Shared: [last snap with 10 conn view]

SERVER    NAME                                                             COUNT(C.SID) SUM(B.VALUE)
--------- ---------------------------------------------------------------- ------------ ------------
SHARED    session pga memory                                                         10     69367664
SHARED    session pga memory max                                                     10     78215024
SHARED    session uga memory                                                         10     12713760
SHARED    session uga memory max                                                     10     12814800

If we observe above
pga max: 78215024
uga max: 12814800
total: 86.81 MB

So we save about 23.9 MB in shared server vs dedicated server. Not sure if we increase the connection, we will have this difference increase as well. Though I took awr snaps, I noticed the current shell's environment variables ruined my snapshots (though the test is valid), hence I am not able to figure out which subpool in shared pool catered to shared server connection.

Thanks