Oracle 19c Sql Performance Analyzer (SPA) by example

Dear Readers,

SPA by example:

Step 1)
Get the STS name

select count(*), sqlset_name from dba_sqlset_statements group by sqlset_name order by 2;

Step 2)
Create an analysis task

Command:

VARIABLE t_name VARCHAR2(100);
EXEC :t_name := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => 'SQLT_WKLD_STS',task_name => 'my_spa_task');

Actual Execution:

SQL> VARIABLE t_name VARCHAR2(100);
EXEC :t_name := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => 'SQLT_WKLD_STS',task_name => 'my_spa_task');SQL>
PL/SQL procedure successfully completed.
SQL>
You have options to configure the analyze task.

Step 3)

Create pre change sql trail task

Command:

EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'my_spa_task', -
       execution_type => 'TEST EXECUTE', - 
       execution_name => 'my_exec_BEFORE_change');

Actual output:

SQL> EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'my_spa_task', -
       execution_type => 'TEST EXECUTE', -
       execution_name => 'my_exec_BEFORE_change');> >
PL/SQL procedure successfully completed.
SQL>

I wanted to update my init param filesystemio_options from none to setall, to see the effect. I am using cooked filesystem to store my datafile.

12:40:16 SQL> sho parameter filesyste
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options                 string      none

12:47:33 SQL> sho parameter filesys
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options                 string      SETALL
12:47:46 SQL>

Param changed and db bounced.

Step 4)

Create post change sql trail task

Command:

EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'my_spa_task', -
       execution_type => 'TEST EXECUTE', - 
       execution_name => 'my_exec_AFTER_change');

Actual output:

12:47:46 SQL> EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'my_spa_task', -
       execution_type => 'TEST EXECUTE', -
       execution_name => 'my_exec_AFTER_change');12:48:29 > 12:48:29 >
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.00

12:48:34 SQL>

Step 5)

Compare the pre/post analysis task result

Command:

EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'my_spa_task', - 
       execution_type => 'COMPARE PERFORMANCE', -
       execution_name => 'my_exec_compare', -
       execution_params => dbms_advisor.arglist(-
                             'comparison_metric', 'elapsed_time'));
overall arglist supported:
elapsed_time (default), cpu_time, buffer_gets, disk_reads, direct_writes, optimizer_cost, and io_interconnect_bytes

Actual output:

12:55:37 SQL> EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'my_spa_task', -
       execution_type => 'COMPARE PERFORMANCE', -
       execution_name => 'my_exec_compare', -
12:58:21 > 12:58:21 > 12:58:21 >        execution_params => dbms_advisor.arglist(-
                             'comparison_metric', 'elapsed_time'));12:58:21 >
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.26
12:58:22 SQL>

Step 6)
Report the result:
Active html format:

set trimspool on
set trim on
set pages 0
set linesize 1000
set long 1000000
set longchunksize 1000000
spool spa_active.html
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(task_name => 'my_spa_task',
          type => 'active', section => 'all') FROM dual;
spool off
Text form:
VAR rep   CLOB;
EXEC :rep := DBMS_SQLPA.REPORT_ANALYSIS_TASK('my_spa_task', -
                'text', 'typical', 'summary');
SET LONG 100000 LONGCHUNKSIZE 100000 LINESIZE 130
PRINT :rep

Actual output:

12:59:55 SQL> 12:59:55 SQL> VAR rep   CLOB;
EXEC :rep := DBMS_SQLPA.REPORT_ANALYSIS_TASK('my_spa_task', -
                'text', 'typical', 'summary');
SET LONG 100000 LONGCHUNKSIZE 100000 LINESIZE 130
PRINT :rep13:00:32 SQL> 13:00:32 >
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.08

13:00:32 SQL> 13:00:32 SQL>
General Information
---------------------------------------------------------------------------------------------
 Task Information:                              Workload Information:
 ---------------------------------------------  ---------------------------------------------
  Task Name    : my_spa_task                     SQL Tuning Set Name        : SQLT_WKLD_STS
  Task Owner   : SYS                             SQL Tuning Set Owner       : SYS
  Description  :                                 Total SQL Statement Count  : 27
Execution Information:
---------------------------------------------------------------------------------------------
  Execution Name             : my_exec_compare        Started             : 08/15/2022 12:58:22
  Execution Type             : COMPARE PERFORMANCE    Last Updated        : 08/15/2022 12:58:22
  Description                :                        Global Time Limit   : UNLIMITED
  Scope                      : COMPREHENSIVE          Per-SQL Time Limit  : UNUSED
  Status                     : COMPLETED              Number of Errors    : 0
  Number of Unsupported SQL  : 14
Analysis Information:
---------------------------------------------------------------------------------------------
 Before Change Execution:                       After Change Execution:
 ---------------------------------------------  ---------------------------------------------
  Execution Name      : my_exec_BEFORE_change    Execution Name      : my_exec_AFTER_change
  Execution Type      : TEST EXECUTE             Execution Type      : TEST EXECUTE
  Scope               : COMPREHENSIVE            Scope               : COMPREHENSIVE
  Status              : COMPLETED                Status              : COMPLETED
  Started             : 08/15/2022 12:40:04      Started             : 08/15/2022 12:48:30
  Last Updated        : 08/15/2022 12:40:07      Last Updated        : 08/15/2022 12:48:34
  Global Time Limit   : UNLIMITED                Global Time Limit   : UNLIMITED
  Per-SQL Time Limit  : UNUSED                   Per-SQL Time Limit  : UNUSED
  Number of Errors    : 0                        Number of Errors    : 0
 ---------------------------------------------
 Comparison Metric: ELAPSED_TIME
 ------------------
 Workload Impact Threshold: 1%
 --------------------------
 SQL Impact Threshold: 1%
 ----------------------
Report Summary
---------------------------------------------------------------------------------------------
Projected Workload Change Impact:
-------------------------------------------
 Overall Impact      :  -53.36%
 Improvement Impact  :  2.95%
 Regression Impact   :  -56.31%
SQL Statement Count
-------------------------------------------
 SQL Category  SQL Count  Plan Change Count
 Overall              27                  0
 Improved              2                  0
 Regressed             3                  0
 Unchanged             8                  0
 Unsupported          14                  0
Top 13 SQL Sorted by Absolute Value of Change Impact on the Workload
---------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
|           |               | Impact on | Execution | Metric | Metric | Impact    | Plan   |
| object_id | sql_id        | Workload  | Frequency | Before | After  | on SQL    | Change |
--------------------------------------------------------------------------------------------
|        65 | 3rnkw340dt3mj |   -51.07% |     12819 |      7 |    107 | -1428.57% | n      |
|        59 | 06852xf36pjwr |    -3.41% |         1 | 170068 | 255758 |   -50.39% | n      |
|        68 | 4krsqvh5cgrus |     1.94% |         1 | 482556 | 433917 |    10.08% | n      |
|        82 | fcyayutm5frcc |    -1.83% |      6576 |      9 |     16 |   -77.78% | n      |
|        60 | 0cwuxyv314wcg |     1.02% |      8508 |      9 |      6 |    33.33% | n      |
|        70 | 5tq075cva3dga |     -.62% |      7725 |     14 |     16 |   -14.29% | n      |
|        66 | 4g2g8zv8tr8vv |      .51% |     12819 |     13 |     12 |     7.69% | n      |
|        74 | a8ntu3081hfgw |      .51% |      1277 |     20 |     10 |       50% | n      |
|        69 | 5mz8u3b34u9gw |       .5% |     12579 |     17 |     16 |     5.88% | n      |
|        81 | f90zn75aphu4w |      .35% |      1261 |    722 |    715 |      .97% | n      |
|        67 | 4hbzjyh4p336s |      .15% |      1277 |     18 |     15 |    16.67% | n      |
|        63 | 29rsy84cajnjd |        0% |      5094 |     14 |     14 |        0% | n      |
|        77 | bswc46zum45tj |        0% |     12579 |      9 |      9 |        0% | n      |
--------------------------------------------------------------------------------------------
Note: time statistics are displayed in microseconds
---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------

13:00:33 SQL>

SPA has lot of limitation like it cant do DDL, PDML limitation etc... this made in my test nearly 14/27 sql unsupported. Which means nearly 50% of the sql execution arent supported.
I was hoping the filesystemio option brings in postive benefit, but it wasnt.
As such SPA and STS are very stright forward. Can use it as part of performance engineering.

Thanks

Sql Tuning Set (STS) creation in 19c

 Dear Readers,

In this blog, we will see how we can create a Sql Tuning Set in 19c Oracle database.

Procedure to create Sql Tuning Set

Step 1)
Ensure you have Tuning Pack licesnse.

Step 2)
We will use Pl/Sql API to create and load the sql tuning set. To create the STS...

Command:

BEGIN
  DBMS_SQLSET.CREATE_SQLSET (
    sqlset_name  => 'SQLT_WKLD_STS',description  => 'STS to store SQL from the private SQL area');
END;

Actual execution:

SQL> BEGIN
  DBMS_SQLSET.CREATE_SQLSET (
    sqlset_name  => 'SQLT_WKLD_STS',description  => 'STS to store SQL from the private SQL area');
END;  2    3    4
  5  /
PL/SQL procedure successfully completed.
SQL>

Step 3)
Select the sqls to be loaded into the STS.

Command:

DECLARE
  c_sqlarea_cursor DBMS_SQLSET.SQLSET_CURSOR;
BEGIN
 OPEN c_sqlarea_cursor FOR
   SELECT VALUE(p)
   FROM   TABLE( 
            DBMS_SQLSET.SELECT_WORKLOAD_REPOSITORY(
            begin_snap =>17
            ,end_snap=>18
            ,basic_filter => 
            ' module = ''wish8.6@10.0.2.15 (TNS V1-V3)'' ')
          ) p;
-- load the tuning set
  DBMS_SQLSET.LOAD_SQLSET (  
    sqlset_name     => 'SQLT_WKLD_STS'
,   populate_cursor =>  c_sqlarea_cursor 
);
END;
/

Actual execution:

SQL> DECLARE
  c_sqlarea_cursor DBMS_SQLSET.SQLSET_CURSOR;
BEGIN
 OPEN c_sqlarea_cursor FOR
   SELECT VALUE(p)
   FROM   TABLE(
            DBMS_SQLSET.SELECT_WORKLOAD_REPOSITORY(
            begin_snap =>17
            ,end_snap=>18
            ,basic_filter =>
            ' module = ''wish8.6@10.0.2.15 (TNS V1-V3)'' ')
          ) p;
-- load the tuning set
  DBMS_SQLSET.LOAD_SQLSET (
    sqlset_name     => 'SQLT_WKLD_STS'
,   populate_cursor =>  c_sqlarea_cursor
);
END;
/  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19
PL/SQL procedure successfully completed.
SQL>

--> less than 1 minute for the above step.

Step 4)
Verify the loaded sqls.

COLUMN SQL_TEXT FORMAT a30   
COLUMN SCH FORMAT a3
COLUMN ELAPSED FORMAT 999999999
SELECT SQL_ID, PARSING_SCHEMA_NAME AS "SCH", SQL_TEXT, 
       ELAPSED_TIME AS "ELAPSED", BUFFER_GETS
FROM   TABLE( DBMS_SQLTUNE.SELECT_SQLSET( 'SQLT_WKLD_STS' ) )
order by 4;

>> by default it sorts in asc.

Now the result:

SQL_ID        SCH SQL_TEXT                          ELAPSED BUFFER_GETS
------------- --- ------------------------------ ---------- -----------
ap249zmtsh0hc TPC INSERT INTO new_order (no_o_id      75898        1775
              C   , no_d_id, no_w_id) values (:o
                  _id, :o_d_id, :o_w_i
70khh6rkrtwgj TPC INSERT INTO history (h_c_id, h     184359        5337
              C   _c_d_id, h_c_w_id, h_w_id, h_d
                  _id, h_date, h_amoun
...
aw9ttz9acxbc3 TPC BEGIN payment(:p_w_id,:p_d_id,    7997748      158122
              C   :p_c_w_id,:p_c_d_id,:p_c_id,:b
                  yname,:p_h_amount,:p
16dhat4ta7xs9 TPC begin neword(:no_w_id,:no_max_   12292007     1170785
              C   w_id,:no_d_id,:no_c_id,:no_o_o
                  l_cnt,:no_c_discount

27 rows selected.

If you look at the last row... comparing this aginsts AWR report....

        Elapsed                  Elapsed Time
        Time (s)    Executions  per Exec (s)  %Total   %CPU    %IO    SQL Id
---------------- -------------- ------------- ------ ------ ------ -------------
            12.3         12,579          0.00    9.3   95.2     .3 16dhat4ta7xs9
Module: wish8.6@10.0.2.15 (TNS V1-V3)
begin neword(:no_w_id,:no_max_w_id,:no_d_id,:no_c_id,:no_o_ol_cnt,:no_c_discount
,:no_c_last,:no_c_credit,:no_d_tax,:no_w_tax,:no_d_next_o_id,TO_DATE(:timestamp,
'YYYYMMDDHH24MISS')); END;
             8.0         12,819          0.00    6.0   94.0     .0 aw9ttz9acxbc3
Module: wish8.6@10.0.2.15 (TNS V1-V3)
BEGIN payment(:p_w_id,:p_d_id,:p_c_w_id,:p_c_d_id,:p_c_id,:byname,:p_h_amount,:p
_c_last,:p_w_street_1,:p_w_street_2,:p_w_city,:p_w_state,:p_w_zip,:p_d_street_1,
:p_d_street_2,:p_d_city,:p_d_state,:p_d_zip,:p_c_first,:p_c_middle,:p_c_street_1
,:p_c_street_2,:p_c_city,:p_c_state,:p_c_zip,:p_c_phone,:p_c_since,:p_c_credit,:

You can notice the top 2 sqls by elapsed time shown in AWR are matching with our STS.

Thanks

Breakup of Oracle Foreground and Background wait class metrics history from AWR views

 Dear Readers,

In this blog, we are going to look at the possibilities of reporting the foreground and background wait class metrics. Oracle in the AWR doesnt report the foreground and background wait class metrics separately, it only reports the foreground metrics by wait class; but not the background.

So I wrote a sql analytic query based on dba_hist_system_event view to report the metrics...

Query:

with t as
(
select /*+ materialize */ snap_id
,wait_class
,sum(total_waits) totwts
,sum(time_waited_micro) tmwt
,sum(total_waits_fg) totwtsfg
,sum(time_Waited_micro_fg) tmwtfg
,(sum(total_waits)-sum(total_waits_fg)) totwtsbg
,(sum(time_waited_micro)-sum(time_waited_micro_fg)) tmwtbg
from dba_hist_system_event
group by snap_id
,wait_class
)
select t.wait_class
,(t.totwts-a.totwts) total_waits
,(t.tmwt-a.tmwt) total_time_waited_us
,(t.totwtsfg-a.totwtsfg) total_waits_fg
,(t.tmwtfg-a.tmwtfg) time_waited_fg_us
,(t.totwtsbg-a.totwtsbg) total_waits_bg
,(t.tmwtbg-a.tmwtbg) time_waited_bg_us
from
t ,t a
where t.snap_id=18
and a.snap_id=(t.snap_id-1)
and a.wait_Class=t.wait_class
order by 1,2;

Result:

WAIT_CLASS                    ,TOTAL_WAITS,TOTAL_TIME_WAITED_US,TOTAL_WAITS_FG,TIME_WAITED_FG_US,TOTAL_WAITS_BG,TIME_WAITED_BG_US

------------------------------,-----------,--------------------,--------------,-----------------,--------------,-----------------

Administrative                ,         32,                 189,            32,              189,             0,               0

Application                   ,         17,               24645,            17,            24645,             0,               0

Commit                        ,      26770,            84755489,         26759,         84639135,            11,          116354

Concurrency                   ,          2,               25996,             0,                0,             2,           25996

Configuration                 ,         13,              922471,            13,           922471,             0,               0

Idle                          ,     168764,          1.1192E+11,        105007,       5855698235,         63757,       1.0606E+11

Network                       ,     120130,             1099890,        120024,          1099821,           106,              69

Other                         ,       6035,            76421612,          4793,           696732,          1242,        75724880

Scheduler                     ,          0,                   0,             0,                0,             0,               0

System I/O                    ,      40147,           119983483,          1009,            93039,         39138,       119890444

User I/O                      ,       5086,             5884585,          4479,          2866115,           607,         3018470

11 rows selected.

So you can see above the breakup of the wait class information by foreground and background processes.

AWR report sample (Showing the total waits and time waited information in total - not classified by fg or bg process):

Wait Classes by Total Wait Time

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

                                                          Avg             Avg

                                        Total Wait       Wait   % DB   Active

Wait Class                  Waits       Time (sec)       Time   time Sessions

---------------- ---------------- ---------------- ---------- ------ --------

System I/O                 40,147              120     2.99ms   90.7      0.1

Commit                     26,770               85     3.17ms   64.1      0.0

Other                       6,035               76    12.66ms   57.8      0.0

DB CPU                                          42              31.7      0.0

User I/O                    5,086                6     1.16ms    4.4      0.0

Network                   120,130                1     9.16us     .8      0.0

Configuration                  13                1    70.96ms     .7      0.0

Concurrency                     2                0    13.00ms     .0      0.0

Application                    17                0     1.45ms     .0      0.0

Administrative                 32                0     5.91us     .0      0.0

AWR report sample (Showing the total waits and time waited information in total - only for fg):

Foreground Wait Class                 DB/Inst: ORA19C01/ORA19C01  Snaps: 17-18

-> s  - second, ms - millisecond, us - microsecond, ns - nanosecond

-> ordered by wait time desc, waits desc

-> %Timeouts: value of 0 indicates value was < .5%.  Value of null is truly 0

-> Captured Time accounts for        100.0%  of Total DB time         132.30 (s)

-> Total FG Wait Time:                90.34 (s)  DB CPU time:          42.00 (s)

                                     %Time      Total Wait

Wait Class                     Waits -outs        Time (s)   Avg wait  %DB time

-------------------- --------------- ----- --------------- ---------- ---------

Commit                        26,759     0              85     3.16ms      64.0

DB CPU                                                  42                 31.7

User I/O                       4,479     0               3   639.90us       2.2

Network                      120,024     0               1     9.16us       0.8

Configuration                     13     0               1    70.96ms       0.7

Other                          4,793     0               1   145.36us       0.5

System I/O                     1,009     0               0    92.21us       0.1

Application                       17     0               0     1.45ms       0.0

Administrative                    32     0               0     5.91us       0.0

Concurrency                        0                     0                  0.0

                          ------------------------------------------------------

If you read the line "-> Captured Time accounts for        100.0%  of Total DB time         132.30 (s)". Oracle AWR doesnt account for bg process times in the db time metrics, the same applies for DB CPU metric as well. Background process spends are measured separately.

Then why would some one be interested in the background process wait_class metric information.

In my DB during a hammerDB load test, the system was chocked with system I/O class of wait events aka "log file parallel write & Redo Transport Attach", since my machine had archivelog & standby enabled. Also @ the os end I saw a lot of system and IO waits, I didnt store vmstat result, but you can see below the sar report (averaged out!!!) over 10 mins time showing a significant increase in system cpu usage.

                                                Total

                                       %Time     Wait              Waits   % bg

Event                            Waits -outs Time (s)  Avg wait     /txn   time

-------------------------- ----------- ----- -------- --------- -------- ------

log file parallel write         28,790     0       90    3.13ms      1.1   39.1

Redo Transport Attach                6     0       72   12.03 s      0.0   31.3

02:20:01 AM     CPU     %user     %nice   %system   %iowait    %steal     %idle

02:30:01 AM     all      0.27      0.00      4.53      0.17      0.00     95.04  <<< this is the window the test ran

02:40:01 AM     all      0.04      0.00      0.66      0.13      0.00     99.17

Hope this helps!

Thank you!

User managed backup and redo size growth

 Dear Readers,

As per oracle document, when you perform user managed backup using 
BEGIN BACKUP at database or tablespace level, your expected to generate more redo (since we will include the image of the full block undergoing change, since the user managed backup tool doesnt understand the dbfile architecture - might create fractured block in backup) compared to using RMAN to perform the same backup.

So I wanted to test that...

Test platform details:

Oracle Version: 19.3.0
DB Type: Standalone x86_64
Os: Linux

In session 1:

  1  create table t1 as
  2  select level lvl,mod(level,3) mdlvl3,mod(level,4) mdlvl4
  3  from dual
  4* connect by level<10000
SQL> /
Table created.

In session 2:

SQL> alter system flush buffer_cache;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> select distinct(Status) from v$backup;

STATUS

------------------

NOT ACTIVE

In session 1 back:

SQL> set autotrace trace statistics

SQL> update t1 set mdlvl3=20;

9999 rows updated.

Statistics

----------------------------------------------------------

         95  recursive calls

        156  db block gets

        141  consistent gets

         38  physical reads

     949056  redo size

        195  bytes sent via SQL*Net to client

        374  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

         10  sorts (memory)

          0  sorts (disk)

       9999  rows processed

SQL> commit;

Commit complete.

So we see it is only 949K or so redo generated.

In session 2 again:

SQL> alter system flush buffer_cache;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> select distinct(Status) from v$backup;

STATUS

------------------

NOT ACTIVE

SQL> alter database begin backup;

Database altered.

SQL> select distinct(Status) from v$backup;

STATUS

------------------

ACTIVE

In session 1 again:

SQL> set autotrace trace statistics

SQL> update t1 set mdlvl3=30;

9999 rows updated.

Statistics

----------------------------------------------------------

         55  recursive calls

        158  db block gets

        139  consistent gets

         37  physical reads

    1134532  redo size

        195  bytes sent via SQL*Net to client

        374  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

         10  sorts (memory)

          0  sorts (disk)

       9999  rows processed

SQL> commit;

Commit complete.

This time 1.1M redo bytes generated, this ideally should be the full image of the block which are undergoing change.

In session 2 again:

SQL> alter database end backup;

Database altered.

SQL> select distinct(Status) from v$backup;

STATUS

------------------

NOT ACTIVE

SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush buffer_cache;

System altered.

In session 1 again:

SQL> set autotrace trace statistics

SQL> update t1 set mdlvl3=40;

9999 rows updated.

Statistics

----------------------------------------------------------

         89  recursive calls

        158  db block gets

        141  consistent gets

         37  physical reads

     949164  redo size

        195  bytes sent via SQL*Net to client

        374  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

         10  sorts (memory)

          0  sorts (disk)

       9999  rows processed

SQL> commit;

Commit complete.

SQL>

In session 2 again:

SQL> alter system flush buffer_cache;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> alter database begin backup;

Database altered.

SQL> select distinct(Status) from v$backup;

STATUS

------------------

ACTIVE

In session 1 again:

SQL> set autotrace trace statistics

SQL> update t1 set mdlvl3=30;

9999 rows updated.

Statistics

----------------------------------------------------------

         55  recursive calls

        156  db block gets

        139  consistent gets

         37  physical reads

    1134400  redo size

        195  bytes sent via SQL*Net to client

        374  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

         10  sorts (memory)

          0  sorts (disk)

       9999  rows processed

SQL> commit;

Commit complete.

SQL>

So we repeated the same update 4 times, 2 times in "BACKUP" mode and other 2 times normally.

In all the scenario when we had "BACKUP" mode active, the amount of redo size is 1.1M vs "NO ACTIVE BACKUP" mode, the size is 949K.

So this confirms oracle's document and expected behaviour.

Thanks

Standby setup in oracle 19c using active database duplication (no OMF or ASM)

Objective of this exercise:
Setup a standby database using active database duplication in oracle 19c. The database isnt using OMF or ASM.

oracle document used for reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/creating-oracle-data-guard-physical-standby.html#GUID-0DC30726-3471-4588-BFE0-9CA0736328E2

Create an example primary db:
dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname ORA19C01 -sid ORA19C01 -responseFile NO_VALUE -characterSet AL32UTF8 -sysPassword <syspass> -systemPassword <systempass> -databaseType MULTIPURPOSE -automaticMemoryManagement false -totalMemory 2000 -storageType FS -datafileDestination /oradata  -redoLogFileSize 50 -emConfiguration NONE -ignorePreReqs

Setting up the primary DB for dataguard setup:
1) Enable appropriate logging (there are other 2 modes available as well, which is an enhancement to support batch loads)

SQL> alter database force logging;
Database altered.

SQL> select name,open_mode,database_role,log_mode,force_logging from v$database;
NAME      OPEN_MODE            DATABASE_ROLE    LOG_MODE     FORCE_LOGG
--------- -------------------- ---------------- ------------ ----------
ORA19C01  READ WRITE           PRIMARY          NOARCHIVELOG YES
SQL>

2) Configure redo transport authentication:

setup the below params:
log_archive_dest_n
fal_server

3) Add necessary standby redologs
Always add g+1 number of standby logfile. Where g is the numbe of redologs found in primary.
alter database add standby logfile ('<path>') size 50M;

4) Setup initialization params on primary db (supports both primary and standby role)

primary: chicago
standby: boston

Primary role params:
DB_NAME=chicago
DB_UNIQUE_NAME=chicago
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
CONTROL_FILES='/arch1/chicago/control1.ctl', '/arch2/chicago/control2.ctl'
LOG_ARCHIVE_DEST_1=
 'LOCATION=USE_DB_RECOVERY_FILE_DEST 
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_2=
 'SERVICE=boston ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
  DB_UNIQUE_NAME=boston'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

Standby role params:
FAL_SERVER=boston
DB_FILE_NAME_CONVERT='/boston/','/chicago/'
LOG_FILE_NAME_CONVERT='/boston/','/chicago/' 
STANDBY_FILE_MANAGEMENT=AUTO

Actual:
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORA19C01,ORA19C01SB1)';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORA19C01';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=ORA19C01SB1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORA19C01SB1';
alter system set LOG_ARCHIVE_FORMAT='ORA19C01_%t_%s_%r.arc' scope=spfile;

alter system set FAL_SERVER=ORA19C01SB1;
alter system set DB_FILE_NAME_CONVERT='/ORA19C01SB1/','/ORA19C01/' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='/ORA19C01SB1/','/ORA19C01/' scope=spfile;
alter system set STANDBY_FILE_MANAGEMENT=AUTO;

5) Create a Backup Copy of the Primary Database Data Files
6) Create a Control File for the Standby Database
7) Create a Parameter File for the Standby Database
8) Copy Files from the Primary System to the Standby System

Note:The steps 5 through 8 will be done using active database cloning.

9) Set Up the Environment to Support the Standby Database

a) Add /etc/oratab entry

standby:
[oracle@localhost dbs]$ tail -1 /etc/oratab
ORA19C01SB1:/u01/app/oracle/product/19.0.0/db_1:N
[oracle@localhost dbs]$

b) Copy password file from primary to standby

Primary:
[oracle@localhost dbs]$ cp orapwORA19C01 orapwORA19C01SB1
[oracle@localhost dbs]$ scp orapwORA19C01SB1 oracle@192.168.50.5:/u01/app/oracle/product/19.0.0/db_1/dbs
oracle@192.168.50.5's password:
orapwORA19C01SB1                                                             100% 2048   236.6KB/s   00:00
[oracle@localhost dbs]$

c) Make static entry in listener & tns entry for both the primary and standby in both the servers

Standby listener:
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORA19C01SB1)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_1)
      (SID_NAME = ORA19C01SB1)
    )
  )

Primary listener:
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORA19C01)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_1)
      (SID_NAME = ORA19C01)
    )
  )

Both tnsnames.ora:
ORA19C01 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.4)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORA19C01)
    )
  )
ORA19C01SB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.5)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORA19C01SB1)
    )
  )

d) Add only db_name and db_unique_name parameter and startup nomount the standby database

[oracle@localhost dbs]$ cat initORA19C01SB1.ora
db_name=ORA19C01
db_unique_name=ORA19C01SB1
[oracle@localhost dbs]$

SQL> select instance_name,status from v$instance;
INSTANCE_NAME    STATUS
---------------- ------------
ORA19C01SB1      STARTED

e) start the local listener if not done already in both primary and standby
f) Create the dump destination and controlfile, datafile, redologfile directories manually. Since we arent using ASM or OMF

primary:
[oracle@localhost dbs]$ find /oradata|grep -Ev ".dbf|.log|.ctl|mf"
/oradata
/oradata/ORA19C01
/oradata/ORA19C01/archives
/oradata/ORA19C01/archives/ORA19C01
[oracle@localhost dbs]$

Standby:
[oracle@localhost oradata]$ find /oradata
/oradata
/oradata/ORA19C01SB1
/oradata/ORA19C01SB1/archives
/oradata/ORA19C01SB1/archives/ORA19C01SB1
[oracle@localhost oradata]$

Location of controlfile, datafile, redologfile: /oradata/ORA19C01SB1
Location of archives: /oradata/ORA19C01SB1/archives/ORA19C01SB1
adump: /u01/app/oracle/admin/ORA19C01SB1/adump

10) Frame the active database duplication command
reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/creating-data-guard-standby-database-using-RMAN.html#GUID-C713FD5C-3375-45E4-A78F-4063006DAC5B

ex:
DUPLICATE TARGET DATABASE
  FOR STANDBY
  FROM ACTIVE DATABASE
  DORECOVER
  SPFILE
    SET "db_unique_name"="foou" COMMENT "Is a duplicate"
    SET LOG_ARCHIVE_DEST_2="service=inst3 ASYNC REGISTER
     VALID_FOR=(online_logfile,primary_role)"
    SET FAL_SERVER="inst1" COMMENT "Is primary"
  NOFILENAMECHECK;

Actuals:

File: sbysetup.cmd
Contents:
DUPLICATE TARGET DATABASE
  FOR STANDBY
  FROM ACTIVE DATABASE
  DORECOVER
  SPFILE
    PARAMETER_VALUE_CONVERT='ORA19C01','ORA19C01SB1'
    SET "db_name"="ORA19C01"
    SET "db_unique_name"="ORA19C01SB1"
    SET DB_FILE_NAME_CONVERT='/ORA19C01/','/ORA19C01SB1/'
    SET LOG_FILE_NAME_CONVERT='/ORA19C01/','/ORA19C01SB1/'
    SET LOG_ARCHIVE_DEST_2="service=ORA19C01 ASYNC REGISTER
     VALID_FOR=(online_logfile,primary_role)"
    SET FAL_SERVER="ORA19C01"
  NOFILENAMECHECK;

Notes:
db_file_name_convert & log_file_name_convert arent required in case you use OMF. Ensure to unset this params in source as well, otherwise you will recieve an RMAN- error.
db_name param is needed in the duplicate command, since your using PARAMETER_VALUE_CONVERT keyword in the duplicate command.

11) Make service based test connection to both primary and standby, if required stop the firewall on the host or make necessary exceptions.

12) Frame a shell script to call the command we framed above....
File: sbysetup.sh
Contents:
cd /home/oracle/dba/SbySetup;
export ORACLE_SID=ORA19C01SB1;export ORAENV_ASK=NO;. oraenv >/dev/null 2>&1;
rman target sys/sys@ORA19C01 auxiliary sys/sys@ORA19C01SB1 cmdfile=sbysetup.cmd log=sbysetup_duplicate.log

13) Invoke the shell script:
nohup sh sbysetup.sh > nh_sbysetup.out &

14) Wait for the duplicate command to finish

15) Verify the output:

Output:
[oracle@localhost SbySetup]$ head -5 sbysetup_duplicate.log
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Feb 9 23:25:59 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
[oracle@localhost SbySetup]$ head -10 sbysetup_duplicate.log
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Feb 9 23:25:59 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORA19C01 (DBID=3909934395)
connected to auxiliary database: ORA19C01 (not mounted)
RMAN> DUPLICATE TARGET DATABASE
[oracle@localhost SbySetup]$ tail -4 sbysetup_duplicate.log
Finished Duplicate Db at 09-FEB-22
Recovery Manager complete.
---

[oracle@localhost SbySetup]$ grep -iE "RMAN-|ORA-" sbysetup_duplicate.log
[oracle@localhost SbySetup]$

16) Verify file locations:
SQL> select name from v$datafile;
NAME
----------------------------------
/oradata/ORA19C01SB1/system01.dbf
/oradata/ORA19C01SB1/sysaux01.dbf
/oradata/ORA19C01SB1/undotbs01.dbf
/oradata/ORA19C01SB1/users01.dbf
SQL> select member from v$logfile;
MEMBER
----------------------------------
/oradata/ORA19C01SB1/redo03.log
/oradata/ORA19C01SB1/redo02.log
/oradata/ORA19C01SB1/redo01.log
SQL> select name from v$controlfile;
NAME
------------------------------------
/oradata/ORA19C01SB1/control01.ctl
/oradata/ORA19C01SB1/control02.ctl
SQL> select name from v$tempfile;
NAME
--------------------------------
/oradata/ORA19C01SB1/temp01.dbf
SQL>

[oracle@localhost SbySetup]$

17) There is no automatic recovery initated by the duplication command, hence we manually kick off the recovery..

command: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.

SQL> select process,pid,status,client_process from v$managed_standby;
PROCESS   PID                      STATUS       CLIENT_P
--------- ------------------------ ------------ --------
DGRD      14968                    ALLOCATED    N/A
ARCH      14970                    CONNECTED    ARCH
DGRD      14972                    ALLOCATED    N/A
ARCH      14974                    CONNECTED    ARCH
ARCH      14976                    CONNECTED    ARCH
ARCH      14978                    CONNECTED    ARCH
RFS       15576                    IDLE         UNKNOWN
RFS       15578                    IDLE         LGWR
RFS       15581                    IDLE         Archival
MRP0      15790                    WAIT_FOR_LOG N/A
10 rows selected.

SQL> /
PROCESS   PID                      STATUS       CLIENT_P  SEQUENCE#
--------- ------------------------ ------------ -------- ----------
DGRD      14968                    ALLOCATED    N/A               0
ARCH      14970                    CONNECTED    ARCH              0
DGRD      14972                    ALLOCATED    N/A               0
ARCH      14974                    CONNECTED    ARCH              0
ARCH      14976                    CONNECTED    ARCH              0
ARCH      14978                    CONNECTED    ARCH              0
RFS       15576                    IDLE         UNKNOWN           0
RFS       15578                    IDLE         LGWR             32
RFS       15581                    IDLE         Archival          0
MRP0      15790                    WAIT_FOR_LOG N/A              32
10 rows selected.

cmd: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL>

The recovery is cancelled.

Thanks

Different Types of Protection Modes in Oracle Dataguard

 Dear Readers,

A quick blog on comparing different protection modes available in oracle dataguard....

Thank you!