Oracle Full (not level 0) and incremental backup - how they support each other

 Dears,

This last week,  I was asked to perform a backup and restore exercise.

This is completely a new db, not in my support scope.

Still did study the DB setup thoroughly and gave feedbacks and brought as much as inputs as I needed.

There was a immense pressure on me to execute this adhoc backup/restore exercise for the db successfully due to some so and so reasons, which I cant discuss.

Anyway, challenge upfront - step into an unknown waters (the customizations), but it is still it is oracle DB.  The study I did , helped me to gain sufficient knowledge to carve out the plan for the restore/recovery exercise.

Objective: Refresh DEV with PROD data. The DB size was 14TB. This needs to be executed in a reasonable time frame. Last time the activity happened on Oct 2023, but no disclosure of the plan, but the only info given was restore took 7hrs.

I prepared the plan, and executed all prereqs, that night I was shared the credentials to login to the target DB where the restore is to be done.

On the 'D' day, I learnt the backups which are secured on the weekends "FULL" backup not incremental backup. Rest all days the backup admin is securing incremenal level 1.

So a FULL on sunday and all other days are an incremental level 1.

I was just blown away by this config, this is a managed backup, so no backup logs accessible before. My fault I didnt scrutinize the backup before. I kept hearing there is a incremental level 0 and level 1 but there is a mistake -- hrrrrrrrrr!

Ok, now the challenege ahead with 3TB worth of archive to recover from Full backup the PITR requested.

So I proceeded with the restore...

Attempt  1 - restore  failed due to the "convert" strings in init param not supporting non-omf db restore.

Attempt  2 - Set new name for the datafiles by dynamically generating string using regexp_replace(name,'source','tgt',1,1) from v$datafile view and set them up in the restore command.

run

{

set newname for datafile 1 to '/tgt/file1.dbf';

set until time to_date('');

restore database;

}

while the restore was ongoing I broke the archive restore command into 9 parts thinking I have to

>> restore archives

>> recover archives

>>purge archives

This is when my stuck an option where rman already automated this routine...

recover database delete archivelog XXX G;

This does all what was mentioned above.

Restore finished in 3hrs for 11TB.

Now comes the main discussion point of this blog, when I kicked this recovery on.

To my surprise, the incremental level 1 was used and it was used properly; which made me wonder what could have happened?

I didn't collect evidence, but I will when I find opportunity..

1. Full backup doesn't store the incremental scn value in the dictionary which is its nature.  So my assumption was they don't participate in incremental restore & recovery

This is where oracle turned smart, oracle after full backup was restored.

 The incremental level 1 backup it used was actually taken on top of the last incremental level 1.So ideally what happened is,

Full backup restored df1 (scn-111), df2 (scn-123) etc..

incremental level 1 had changes captured from 99 and hence this incremental level 1 was used during this recovery exercise successfully. The archives left over were used as required to roll forward the DB.

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