ZDLRA Internals, INDEX_BACKUP task in details

For ZDLRA, the task type INDEX_BACKUP it is important (if it is not the most) because it is responsible to create the virtual full backup. This task runs for every backup that you ingest at ZDLRA and here, I will show with more details what occurs at ZDLRA: internals steps, phases, and tables involved.

I recommend that you check my previous post about ZDLRA: ZDLRA Internals, Tables and Storage, ZDLRA, Virtual Full Backup and Incremental Forever, and Understanding ZDLRA. They provide a good base to understand some aspects of ZDLRA architecture and features.

Backup

As you saw in my previous post, ZDLRA opens every backup that you sent and read every block of it to generate one new virtual full backup. And this backup is validated block a block (physically and logically) against corruption. It differs from a snapshot because it is content-aware (in this case it is proprietary Oracle datafile blocks inside another proprietary Oracle rman block) and Oracle it is the only that can do this guaranteeing that result is valid.

This generation is done thought INDEX_BACKUP task and occurs for every backup (level 0 or 1) that enter in the system. For this post, I already have a full backup level 0 inside ZDLRA and I made one new incremental level 1:

RMAN> run{
2> backup device type sbt cumulative incremental level 1 filesperset 1 datafile 29;
3> }

Starting backup at 2019-08-16_15-01-11
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=406 instance=SIMON1 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: RA Library (ZDLRA) SID=903C95C93085DC14E0531E43B20AB30F
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=451 instance=SIMON1 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: RA Library (ZDLRA) SID=903C95D1C201DC19E0531E43B20A4C44
allocated channel: ORA_SBT_TAPE_3
channel ORA_SBT_TAPE_3: SID=502 instance=SIMON1 device type=SBT_TAPE
channel ORA_SBT_TAPE_3: RA Library (ZDLRA) SID=903C95E1BFD9DC38E0531E43B20A0038
allocated channel: ORA_SBT_TAPE_4
channel ORA_SBT_TAPE_4: SID=651 instance=SIMON1 device type=SBT_TAPE
channel ORA_SBT_TAPE_4: RA Library (ZDLRA) SID=903C95EAC1ACDC48E0531E43B20AC79D
channel ORA_SBT_TAPE_1: starting incremental level 1 datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
input datafile file number=00029 name=+DATAC1/simon_x6s/datafile/simontsthistoric1_tbs.399.938192975
channel ORA_SBT_TAPE_1: starting piece 1 at 2019-08-16_15-01-25
channel ORA_SBT_TAPE_1: finished piece 1 at 2019-08-16_15-03-10
piece handle=SIMON_s0u9c0a5_1_1 tag=TAG20190816T150116 comment=API Version 2.0,MMS Version 3.17.1.26
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:01:45
Finished backup at 2019-08-16_15-03-10

Starting Control File and SPFILE Autobackup at 2019-08-16_15-03-11
piece handle=c-4165931009-20190816-06 comment=API Version 2.0,MMS Version 3.17.1.26
Finished Control File and SPFILE Autobackup at 2019-08-16_15-03-18

RMAN>

As you can see, it is a normal backup. And the details for this backup:

RMAN> list backupset 52141830;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
52141830 Incr 1  30.75M     SBT_TAPE    00:01:44     2019-08-16_15-03-09
        BP Key: 52141831   Status: AVAILABLE  Compressed: NO  Tag: TAG20190816T150116
        Handle: SIMON_s0u9c0a5_1_1   Media: Recovery Appliance (ZDLRA)
  List of Datafiles in backup set 52141830
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  29   1  Incr 4932560891039 2019-08-16_15-01-25 +DATAC1/simon_x6s/datafile/simontsthistoric1_tbs.399.938192975

RMAN>

RMAN> list backup tag = 'TAG20190816T150116';


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
52141830 Incr 1  30.75M     SBT_TAPE    00:01:44     2019-08-16_15-03-09
        BP Key: 52141831   Status: AVAILABLE  Compressed: NO  Tag: TAG20190816T150116
        Handle: SIMON_s0u9c0a5_1_1   Media: Recovery Appliance (ZDLRA)
  List of Datafiles in backup set 52141830
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  29   1  Incr 4932560891039 2019-08-16_15-01-25 +DATAC1/simon_x6s/datafile/simontsthistoric1_tbs.399.938192975

RMAN>

Here it is important to remember the TAG, backupset(BS)/backup piece (BP) numbers, and the SCN for the backup made.

INDEX_BACKUP

The INDEX_BACKUP appears as a single task inside ZDLRA, but have two major phases: fixup_unordered and q_restore_fast. And each one impacts differently inside the rman catalog and in the internal tables.

So, after the backup finish, inside of ZDLRA we can see the index task running:

SQL> l
  1  SELECT s.ba_session_id, s.instance_id, s.sid, s.serial#, s.job_name
  2         , rt.task_id, rt.DB_KEY, rt.db_unique_name, rt.task_type, rt.state, rt.waiting_on
  3         , rt.elapsed_seconds
  4         , gs.module, gs.sql_id, gs.action
  5         , rt.BP_KEY, rt.bs_key, rt.df_key, rt.vb_key
  6  FROM sessions s
  7  JOIN ra_task rt
  8  ON rt.task_id = s.current_task
  9  AND rt.task_type = 'INDEX_BACKUP'
 10  JOIN gv$session gs
 11  ON gs.inst_id = s.instance_id
 12  AND gs.sid = s.sid
 13  AND gs.serial# = s.serial#
 14* ORDER BY rt.LAST_EXECUTE_TIME DESC
SQL> /

BA_SESSION_ID INSTANCE_ID    SID    SERIAL# JOB_NAME              TASK_ID     DB_KEY DB_UNIQUE_NAME  TASK_TYPE       STATE    WAITING_ON ELAPSED_SECONDS MODULE           SQL_ID        ACTION            BP_KEY     BS_KEY     DF_KEY     VB_KEY
------------- ----------- ------ ---------- ------------------ ---------- ---------- --------------- --------------- -------- ---------- --------------- ---------------- ------------- ------------- ---------- ---------- ---------- ----------
     56215535           2   4228      30075 RA$_EXEC_56216288    56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING                  345.305895 fixup_unordered  1q0zr86n25pvu scan 6% done    52141831

SQL>

With this SQL you can check all the INDEX_BACKUP tasks running at ZDLRA. And the SQL use the tables and report:

  • RA_TASK: All tasks running inside ZDLRA.
  • SESSIONS: Sessions running inside ZDLRA, used to get the database session running the task
  • GV$SESSION: Databases sessions, used to get the sql_id and others info
  • BP_KEY: Identify the backup piece key that it is indexing. It is the start point to identify everything here.

Above you can see that task 56242962 is running, processing the BP_KEY (backup piece key) 52141831, running the module fixup_unordered, processed 6% of this step, and running SQL_ID 1q0zr86n25pvu.

INDEX_BACKUP, fixup_unordered

As showed, the index task is running the fixup_unordered module, and thus represents the first phase where ZDLRA it is checking all the blocks that are necessary to build the virtual full backup. Based on the report from the query above we can see that BP_KEY processed it is 52141831 and we can get more info from backup piece rman table:

SQL> select bp_key, bs_key, tag, handle, bytes/1024/1024/1024 from bp where bp_key = 52141831;

    BP_KEY     BS_KEY TAG                              HANDLE               BYTES/1024/1024/1024
---------- ---------- -------------------------------- -------------------- --------------------
  52141831   52141830 TAG20190816T150116               SIMON_s0u9c0a5_1_1             .030029297

SQL>
SQL> select bs_key, INCREMENTAL_LEVEL, HANDLE from rc_backup_piece where bp_key = 52141831;

    BS_KEY INCREMENTAL_LEVEL HANDLE
---------- ----------------- --------------------
  52141830                 1 SIMON_s0u9c0a5_1_1

SQL>

This tells us that the task it is processing the backup that we made before (look the result of column BP_KEY from SQL in the first topic). The BP, and rc_backup_piece are the tables from rman catalog that store info about the backup pieces, check the handle and tag to compare from rman list output.

Going further we can check the VBDF (virtual backup data file table – store the virtual full backups information) table and verify more details:

SQL> SELECT vb_key, ckp_id, df_key, db_key, blocks, vcbp_key, srcbp_key, file#, ckp_scn FROM VBDF WHERE srcbp_key = 52141831;

    VB_KEY         CKP_ID     DF_KEY     DB_KEY     BLOCKS   VCBP_KEY  SRCBP_KEY      FILE#        CKP_SCN
---------- -------------- ---------- ---------- ---------- ---------- ---------- ---------- --------------
  52141864  4932560891039     752220     752186 1655177216              52141831         29  4932560891039

SQL>

At VBDF table we can discover:

  • VB_KEY: Virtual backup key.
  • CKP_ID: Checkpoint and SCN for this virtual backup.
  • BLOCKS: Number of the blocks for this virtual backup.
  • VCBP_KEY: Is null at fixup_unordered because the virtual backup piece (VCBP) was not yet generated (it is doing by fixup_unordered). This will change in the future and I will show more details.
  • SRCBP_KEY: The source/original backup piece used to create this virtual full backup.

So, we can check that the backu is in the middle of the index the process since the VCBP_KEY is not yet available. But besides that, we can validate/check more details about the backups.

Check that does not exist a backup piece associated with this virtual backup key:

SQL> select bp_key, bs_key, tag, handle, bytes/1024/1024/1024, vb_key from bp where vb_key = 52141864;

no rows selected

SQL>

And that exists just one backup of datafile for this SCN and FILE#:

SQL> SELECT bdf_key, bs_key, file#, ckp_scn, incr_level from bdf where CKP_SCN = 4932560891039 and file# = 29;

   BDF_KEY     BS_KEY      FILE#        CKP_SCN INCR_LEVEL
---------- ---------- ---------- -------------- ----------
  52141832   52141830         29  4932560891039          1

SQL>
SQL> SELECT db_key, bdf_key, file#, CHECKPOINT_CHANGE#, incremental_level from rc_backup_datafile where bs_key IN(52141830);

    DB_KEY    BDF_KEY      FILE# CHECKPOINT_CHANGE# INCREMENTAL_LEVEL
---------- ---------- ---------- ------------------ -----------------
    752186   52141832         29      4932560891039                 1

SQL> SELECT db_key, bdf_key, file#, CHECKPOINT_CHANGE#, incremental_level from rc_backup_datafile where CHECKPOINT_CHANGE# = 4932560891039;

    DB_KEY    BDF_KEY      FILE# CHECKPOINT_CHANGE# INCREMENTAL_LEVEL
---------- ---------- ---------- ------------------ -----------------
    752186   52141832         29      4932560891039                 1

SQL>

Digging more, we can see that are zero plans for this virtual backup (will be zero until fixup_unordered finish – more detail further):

SQL> select count(*) from plans where VB_KEY = 52141864;

  COUNT(*)
----------
         0

SQL>

During this phase of INDEX_BACKUP, the major SQL_ID is 1q0zr86n25pvu:

SQL> select sql_fulltext from gv$sqlarea where inst_id = 2 and sql_id = '1q0zr86n25pvu';

SQL_FULLTEXT
----------------------------------------------------------------------------------------------------
SELECT * FROM (SELECT /*+
                     QB_NAME(c)
                     INDEX_RS_ASC(@c b@c)
                     NO_INDEX_FFS(@c b@c)
                     NO_INDEX_SS(@c b@c)
                     OPT_PARAM('optimizer_dynamic_sampling' 0)
                     OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
                     OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
                     OPT_PARAM('_optimizer_use_feedback' 'false')
                    */ BLOCKNO, SCN, CKP_ID, ENDBLK, CHUNKNO, COFFSET FROM BLOCKS B WHERE DF_KEY = :
B4 AND BLOCKNO BETWEEN :B3 AND :B2 AND SCN < :B1 ORDER BY BLOCKNO, SCN, CKP_ID ) WHERE ROWNUM < :B5

SQL>

And the bind variables in this case are:

SQL> l
  1  SELECT s.ba_session_id, s.instance_id
  2  , rt.task_id, rt.DB_KEY, rt.db_unique_name, rt.task_type, rt.state
  3  , gs.module, gs.sql_id, gs.action
  4  , rt.BP_KEY, rt.bs_key, rt.df_key, rt.vb_key
  5  , sbc.name, sbc.value_string
  6  FROM sessions s
  7  JOIN ra_task rt
  8  ON rt.task_id = s.current_task
  9  AND RT.TASK_ID = 56242962
 10  JOIN gv$session gs
 11  ON gs.inst_id = s.instance_id
 12  AND gs.sid = s.sid
 13  AND gs.serial# = s.serial#
 14  join gv$sql_bind_capture sbc
 15  on sbc.inst_id = gs.inst_id
 16  and sbc.sql_id = gs.sql_id
 17* ORDER BY rt.LAST_EXECUTE_TIME DESC
SQL>
SQL> /

BA_SESSION_ID INSTANCE_ID    TASK_ID     DB_KEY DB_UNIQUE_NAME       TASK_TYPE       STATE           MODULE           SQL_ID        ACTION              BP_KEY     BS_KEY     DF_KEY     VB_KEY NAME  VALUE_STRING
------------- ----------- ---------- ---------- -------------------- --------------- --------------- ---------------- ------------- --------------- ---------- ---------- ---------- ---------- ----- ---------------
     56215535           2   56242962     752186 SIMON_X6S            INDEX_BACKUP    RUNNING         fixup_unordered  1q0zr86n25pvu scan 21% done     52141831                                  :B4   752220
     56215535           2   56242962     752186 SIMON_X6S            INDEX_BACKUP    RUNNING         fixup_unordered  1q0zr86n25pvu scan 21% done     52141831                                  :B3   302875642
     56215535           2   56242962     752186 SIMON_X6S            INDEX_BACKUP    RUNNING         fixup_unordered  1q0zr86n25pvu scan 21% done     52141831                                  :B2   1655177216
     56215535           2   56242962     752186 SIMON_X6S            INDEX_BACKUP    RUNNING         fixup_unordered  1q0zr86n25pvu scan 21% done     52141831                                  :B1   4932554641877
     56215535           2   56242962     752186 SIMON_X6S            INDEX_BACKUP    RUNNING         fixup_unordered  1q0zr86n25pvu scan 21% done     52141831                                  :B5   1048576

SQL>

Explaining a little. So, based on the SQL text and variables we can see that is retrieving from BLOCKS table all the blocks for the DF_KEY 752220 (you can get it in VBDF), that are between 302875642 (actual block of processing) and 1655177216 (max number of blocks for the ingested backup), and have SCN bellow 4932554641877 (from the ingested backup) and process this in package of 1048576 blocks. One information here it is that the number 302875642 will vary while it is running this phase.

So, the idea here for ZDLRA index task is processing get/check all the blocks that have SCN below of the SCN from ingested backup. And since the INDEX_BACKUP result creates one virtual full backup, all the blocks for this datafile need to be processed.

If you check this query time to time, you will see the increase of scan % increasing until the :B3 and :B4 are equal (compare with the previous execution):

SQL> /

BA_SESSION_ID INSTANCE_ID    TASK_ID     DB_KEY DB_UNIQUE_NAME       TASK_TYPE       STATE           MODULE           SQL_ID        ACTION             BP_KEY     BS_KEY     DF_KEY     VB_KEY NAME  VALUE_STRING
------------- ----------- ---------- ---------- -------------------- --------------- --------------- ---------------- ------------- -------------- ---------- ---------- ---------- ---------- ----- ---------------
     56215535           2   56242962     752186 SIMON_X6S            INDEX_BACKUP    RUNNING         fixup_unordered  1q0zr86n25pvu scan 99% done    52141831                                  :B4   752220
     56215535           2   56242962     752186 SIMON_X6S            INDEX_BACKUP    RUNNING         fixup_unordered  1q0zr86n25pvu scan 99% done    52141831                                  :B3   1641637658
     56215535           2   56242962     752186 SIMON_X6S            INDEX_BACKUP    RUNNING         fixup_unordered  1q0zr86n25pvu scan 99% done    52141831                                  :B2   1655177216
     56215535           2   56242962     752186 SIMON_X6S            INDEX_BACKUP    RUNNING         fixup_unordered  1q0zr86n25pvu scan 99% done    52141831                                  :B1   4932554641877
     56215535           2   56242962     752186 SIMON_X6S            INDEX_BACKUP    RUNNING         fixup_unordered  1q0zr86n25pvu scan 99% done    52141831                                  :B5   1048576

SQL> select rt.elapsed_seconds from ra_task rt where task_id = 56242962;

ELAPSED_SECONDS
---------------
     6618.53526

SQL>

After this hit 100% the next phase of the INDEX_BAKUP (q_restore_fast) will start. The phase fixup_unordered depends on the size of your datafile and not from the ingested backup. In this example, the backup has just 30MB, but the datafile has more than 16TB, so, the virtual full will have the same size and this takes a lot of time reading all the blocks. For you ZDLRA the DF_KEY, BS_KEY, and others will be different. Just remember that BP_KEY for the index task it is the start point for the analyses.

INDEX_BACKUP, q_restore_fast

After the fixup_unordered reach 100% of the scan, the phase q_restore_fast starts and this will create the virtual backup itself. But before let’s see what’s happened at rman catalog after the previous phase finished:

##########################################################
This is list took right after the backup finished:
RMAN> list backup tag = 'TAG20190816T150116';


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
52141830 Incr 1  30.75M     SBT_TAPE    00:01:44     2019-08-16_15-03-09
        BP Key: 52141831   Status: AVAILABLE  Compressed: NO  Tag: TAG20190816T150116
        Handle: SIMON_s0u9c0a5_1_1   Media: Recovery Appliance (ZDLRA)
  List of Datafiles in backup set 52141830
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  29   1  Incr 4932560891039 2019-08-16_15-01-25 +DATAC1/simon_x6s/datafile/simontsthistoric1_tbs.399.938192975

RMAN>
##########################################################
And now after the fixup_unordered finish:
RMAN> list backup tag = 'TAG20190816T150116';


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
52146235 Incr 1  28.00M     SBT_TAPE    00:01:44     2019-08-16_15-03-09
        BP Key: 52146236   Status: AVAILABLE  Compressed: YES  Tag: TAG20190816T150116
        Handle: VB$_90959062_52141864I   Media:
  List of Datafiles in backup set 52146235
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  29   1  Incr 4932560891039 2019-08-16_15-01-25 +DATAC1/simon_x6s/datafile/simontsthistoric1_tbs.399.938192975

RMAN>

As you can see, the backupset (52141830 before, and 52146235 now) and backup piece changed (the handle changed too). But look that TAG and SCN remains the same. This means that a new backup was created because ZDLRA scanned the original backup. But as you can see, the virtual full was not created yet (there is any level 0 available in the list). If you see this in your ZDLRA, that means that INDEX_BACKUP task is in process.

If we continue to check more details and use the same query than before, we can see that backup changed the BS_KEY and BP_KEY in the internal tables (look that old keys disappears):

SQL> select bs_key, INCREMENTAL_LEVEL, HANDLE from rc_backup_piece where bp_key = 52141831;

no rows selected

SQL> select bp_key, bs_key, tag, handle, bytes/1024/1024/1024, vb_key from bp where bp_key = 52141831;

no rows selected

SQL> select bp_key, bs_key, tag, handle, bytes/1024/1024/1024 from bp where tag = 'TAG20190816T150116';
                                         
    BP_KEY     BS_KEY TAG                 HANDLE                  BYTES/1024/1024/1024
---------- ---------- ------------------- ----------------------- --------------------
  52146236   52146235 TAG20190816T150116  VB$_90959062_52141864I             .02734375
                                                                 
SQL>

And now, checking in the VBDF table we can see that info at VCBP_KEY column appears:

SQL> SELECT vb_key, ckp_id, df_key, db_key, blocks, vcbp_key, srcbp_key, file#, ckp_scn FROM VBDF WHERE srcbp_key = 52141831;

    VB_KEY         CKP_ID     DF_KEY     DB_KEY     BLOCKS   VCBP_KEY  SRCBP_KEY      FILE#        CKP_SCN
---------- -------------- ---------- ---------- ---------- ---------- ---------- ---------- --------------
  52141864  4932560891039     752220     752186 1655177216   52146236   52141831         29  4932560891039

SQL>

And if you search now at backup piece table you can see that exists one listed for this virtual backup key (check in the topic before that this info does not exist):

SQL> select bp_key, bs_key, tag, handle, bytes/1024/1024/1024, vb_key from bp where vb_key = 52141864;

    BP_KEY     BS_KEY TAG                 HANDLE                  BYTES/1024/1024/1024     VB_KEY
---------- ---------- ------------------- ----------------------- -------------------- ----------
  52146236   52146235 TAG20190816T150116  VB$_90959062_52141864I             .02734375   52141864

SQL>

And check that exists just one backup of this datafile at this SCN:

SQL> SELECT bdf_key, bs_key, file#, ckp_scn, incr_level from bdf where CKP_SCN = 4932560891039 and file# = 29;

   BDF_KEY     BS_KEY      FILE#        CKP_SCN INCR_LEVEL
---------- ---------- ---------- -------------- ----------
  52146237   52146235         29  4932560891039          1

SQL>

In this phase, you can see that the details for index and virtual full backup starts to appear internally. If you check, now you have plans for this virtual full backup:

SQL> select count(*) from plans where VB_KEY = 52141864;

  COUNT(*)
----------
         1

SQL>
SQL> select count(*), to_char(sysdate, 'HH24:MI') as time from plans_details where VB_KEY = 52141864;

  COUNT(*) TIME
---------- -----
   2294010 18:25

SQL> select count(*), to_char(sysdate, 'HH24:MI') as time from plans_details where VB_KEY = 52141864;

  COUNT(*) TIME
---------- -----
   5773720 18:43

SQL>

As you can see above, the plans_details for this virtual backup is increasing from time to time. This means that the q_restore_fast is running and it is reading blocks to create the virtual full backup/index (and inserting at plan_details).

We can figure out this, while the INDEX_BACKUP task is running, checking the sql_id for the session. While the task is running, we have three major sql’s (I used the same query than before – where we checked the bind for the session/task):

SQL> /

BA_SESSION_ID INSTANCE_ID    TASK_ID     DB_KEY DB_UNIQUE_NAME  TASK_TYPE       STATE    MODULE          SQL_ID        ACTION                   BP_KEY     BS_KEY     DF_KEY     VB_KEY NAME  VALUE_STRING
------------- ----------- ---------- ---------- --------------- --------------- -------- --------------- ------------- -------------------- ---------- ---------- ---------- ---------- ----- ---------------
     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  9ma189ab8x9c7 plan 0% done           52141831                                  :B1
     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  9ma189ab8x9c7 plan 0% done           52141831                                  :B12
     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  9ma189ab8x9c7 plan 0% done           52141831                                  :B2
     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  9ma189ab8x9c7 plan 0% done           52141831                                  :B1
     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  9ma189ab8x9c7 plan 0% done           52141831                                  :B2
     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  9ma189ab8x9c7 plan 0% done           52141831                                  :B3
     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  9ma189ab8x9c7 plan 0% done           52141831                                  :B4
     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  9ma189ab8x9c7 plan 0% done           52141831                                  :B1   752220
     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  9ma189ab8x9c7 plan 0% done           52141831                                  :B3   1
     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  9ma189ab8x9c7 plan 0% done           52141831                                  :B4   1051071
     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  9ma189ab8x9c7 plan 0% done           52141831                                  :B11  0
     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  9ma189ab8x9c7 plan 0% done           52141831                                  :B10  4931849117847
     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  9ma189ab8x9c7 plan 0% done           52141831                                  :B9   4931848304742
     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  9ma189ab8x9c7 plan 0% done           52141831                                  :B8   4932554641877
     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  9ma189ab8x9c7 plan 0% done           52141831                                  :B7   752187
     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  9ma189ab8x9c7 plan 0% done           52141831                                  :B6   4932554641877
     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  9ma189ab8x9c7 plan 0% done           52141831                                  :B5   4932554641877
                                                                                                        
17 rows selected.                                                                                       
                                                                                                        
SQL>                                                                                                    
SQL> /                                                                                                  
                                                                                                        
BA_SESSION_ID INSTANCE_ID    TASK_ID     DB_KEY DB_UNIQUE_NAME  TASK_TYPE       STATE    MODULE          SQL_ID        ACTION                   BP_KEY     BS_KEY     DF_KEY     VB_KEY NAME  VALUE_STRING
------------- ----------- ---------- ---------- --------------- --------------- -------- --------------- ------------- -------------------- ---------- ---------- ---------- ---------- ----- ---------------
     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  brt6uuhzacdnu plan 54% done          52141831                                  :B1   752220
     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  brt6uuhzacdnu plan 54% done          52141831                                  :B3   403380104
     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  brt6uuhzacdnu plan 54% done          52141831                                  :B4   404440007
     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  brt6uuhzacdnu plan 54% done          52141831                                  :B11  0
     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  brt6uuhzacdnu plan 54% done          52141831                                  :B10  4931849117847
     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  brt6uuhzacdnu plan 54% done          52141831                                  :B9   4931848304742
     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  brt6uuhzacdnu plan 54% done          52141831                                  :B8   4932554641877
     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  brt6uuhzacdnu plan 54% done          52141831                                  :B7   752187
     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  brt6uuhzacdnu plan 54% done          52141831                                  :B6   4932554641877
     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  brt6uuhzacdnu plan 54% done          52141831                                  :B5   4932554641877

10 rows selected.

SQL>
SQL> /

BA_SESSION_ID INSTANCE_ID    TASK_ID     DB_KEY DB_UNIQUE_NAME  TASK_TYPE       STATE      MODULE          SQL_ID        ACTION             BP_KEY     BS_KEY     DF_KEY     VB_KEY NAME  VALUE_STRING
------------- ----------- ---------- ---------- --------------- --------------- ---------- --------------- ------------- -------------- ---------- ---------- ---------- ---------- ----- --------------
     56215535           2   56242962     477602 SIMON_X6S       INDEX_BACKUP    RUNNING    q_restore_fast  8t81c0j1w9jqu plan 89% done    41032043                                  :B1
     56215535           2   56242962     477602 SIMON_X6S       INDEX_BACKUP    RUNNING    q_restore_fast  8t81c0j1w9jqu plan 89% done    41032043                                  :B3   752220
     56215535           2   56242962     477602 SIMON_X6S       INDEX_BACKUP    RUNNING    q_restore_fast  8t81c0j1w9jqu plan 89% done    41032043                                  :B2   ‭4389973931270
     56215535           2   56242962     477602 SIMON_X6S       INDEX_BACKUP    RUNNING    q_restore_fast  8t81c0j1w9jqu plan 89% done    41032043                                  :B1   4932554641877
     56215535           2   56242962     477602 SIMON_X6S       INDEX_BACKUP    RUNNING    q_restore_fast  8t81c0j1w9jqu plan 89% done    41032043                                  :B4   1048576

SQL>

And the SQL text for them:

SQL> select sql_fulltext from gv$sqlarea where inst_id = 2 and sql_id = '9ma189ab8x9c7';

SQL_FULLTEXT
--------------------------------------------------------------------------------------------------------------------------------
INSERT /*+
             QB_NAME(q_restore_fast)
             OPT_PARAM('optimizer_dynamic_sampling' 0)
             OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
             OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
             OPT_PARAM('_optimizer_use_feedback' 'false')
           */ INTO PLANS_DETAILS (DF_KEY, TYPE, VB_KEY, BLKRANK, BLOCKNO, CHUNKNO, NUMBLKS, COFFSET, NUMBYTES) SELECT :B1 , :B12
, :B2 , 1 BLKRANK, BLOCKNO, CHUNKNO, NUMBLKS, COFFSET, NUMBYTES FROM TABLE( DBMS_RA_POOL.COLLAPSE(:B1 , :B2 , :B3 , :B4 ,
 CURSOR( SELECT /*+
                     QB_NAME(q_rfast_c)
                     INDEX_RS_ASC(@q_rfast_c b@q_rfast_c)
                     NO_INDEX_FFS(@q_rfast_c b@q_rfast_c)
                     NO_INDEX_SS(@q_rfast_c b@q_rfast_c)
                     OPT_PARAM('optimizer_dynamic_sampling' 0)
                     OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
                     OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
                     OPT_PARAM('_optimizer_use_feedback' 'false')
                   */ CKP_ID, BLOCKNO, SCN, CHUNKNO, USED, COFFSET, ENDBLK FROM BLOCKS B WHERE DF_KEY = :B1 AND BLOCKNO BETWEEN 
:B3 AND :B4 AND SCN BETWEEN :B11 AND :B10 AND CKP_ID >= :B9 AND (CKP_ID <= :B8 OR DBINC_KEY <> :B7 ) AND (SCN < :B6 OR CKP_ID = 
:B5 ) ORDER BY BLOCKNO, SCN, CKP_ID, CHUNKNO ) ) )

SQL>
SQL> select sql_fulltext from gv$sqlarea where inst_id = 2 and sql_id = 'brt6uuhzacdnu';

SQL_FULLTEXT
-------------------------------------------------------------------------------------------------------------------------------
SELECT /*+ QB_NAME ("Q_RFAST_C") NO_INDEX_SS ("B") NO_INDEX_FFS ("B") INDEX_RS_ASC ("B") */ "B"."CKP_ID" "CKP_ID","B"."BLOCKNO" 
"BLOCKNO","B"."SCN" "SCN","B"."CHUNKNO" "CHUNKNO","B"."USED" "USED","B"."COFFSET" "COFFSET","B"."ENDBLK" "ENDBLK" FROM "BLOCKS" 
"B" WHERE "B"."DF_KEY"=:B1 AND "B"."BLOCKNO">=:B3 AND "B"."BLOCKNO"<=:B4 AND "B"."SCN">=:B11 AND "B"."SCN"<=:B10 AND "B"."CKP_ID
">=:B9 AND ("B"."CKP_ID"<=:B8 OR "B"."DBINC_KEY"<>:B7) AND ("B"."SCN"<:B6 OR "B"."CKP_ID"=:B5) ORDER BY "B"."BLOCKNO","B"."SCN",
"B"."CKP_ID","B"."CHUNKNO"

SQL>
SQL> select sql_fulltext from gv$sqlarea where inst_id = 2 and sql_id = '8t81c0j1w9jqu';

SQL_FULLTEXT
--------------------------------------------------------------------------------------------------------------------------------
SELECT NVL(MAX(BLOCKNO), :B1 ), COUNT(*) FROM (SELECT /*+
                 QB_NAME(nbr)
                 INDEX_RS_ASC(@nbr b@nbr)
                 NO_INDEX_FFS(@nbr b@nbr)
                 NO_INDEX_SS(@nbr b@nbr)
                 OPT_PARAM('optimizer_dynamic_sampling' 0)
                 OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
                 OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
                 OPT_PARAM('_optimizer_use_feedback' 'false')
               */ BLOCKNO FROM BLOCKS B WHERE DF_KEY = :B3 AND BLOCKNO BETWEEN :B2 AND :B1 ORDER BY BLOCKNO) WHERE ROWNUM <= :B4

SQL>

As you can see, the sql_id 9ma189ab8x9c7 is responsible to insert into PLANS_DETAILS the blocks that are needed to create this virtual full backup. This is based in SCN, and means that ZDLRA create the index for all blocks that are below the SCN from the backup that was ingested originally. The sql_id brt6uuhzacdnu returns all the blocks that are needed (based on the SCN), and it is the same for sql_id 8t81c0j1w9jqu that verify if we have a block to index. These sql’s are the base for the q_restore_fast and they alternate itself until we reach 100%.

The idea for ZDLRA in this phase is to create the index that will be the base for the virtual full backup. And “create the index” means all the blocks that are needed to create the plan for this virtual full backup key.

INDEX_BACKUP, 100%

So, after the INDEX_BACKUP finish at ZDLRA, we have this at rman catalog:

RMAN> list backup of datafile 29 tag = TAG20190816T150116;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
52146235 Incr 1  28.00M     SBT_TAPE    00:01:44     2019-08-16_15-03-09
        BP Key: 52146236   Status: AVAILABLE  Compressed: YES  Tag: TAG20190816T150116
        Handle: VB$_90959062_52141864I   Media:
  List of Datafiles in backup set 52146235
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  29   1  Incr 4932560891039 2019-08-16_15-01-25 +DATAC1/simon_x6s/datafile/simontsthistoric1_tbs.399.938192975

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
52152458 Incr 0  12.67T     SBT_TAPE    00:01:44     2019-08-16_15-03-09
        BP Key: 52152459   Status: AVAILABLE  Compressed: YES  Tag: TAG20190816T150116
        Handle: VB$_90959062_52141864_29   Media:
  List of Datafiles in backup set 52152458
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  29   0  Incr 4932560891039 2019-08-16_15-01-25 +DATAC1/simon_x6s/datafile/simontsthistoric1_tbs.399.938192975

RMAN>

Look that for the same TAG we have now two backups, and one it is the virtual full backup. Check that SCN and TAG for both are the same. And if we check internally, at rman catalog view, about the backup pieces linked to the virtual backup key that was created by the index task we can see:

SQL> select bp_key, bs_key, tag, handle, bytes/1024/1024/1024, vb_key from bp where vb_key = 52141864;

    BP_KEY     BS_KEY TAG                 HANDLE                    BYTES/1024/1024/1024     VB_KEY
---------- ---------- ------------------- ------------------------- -------------------- ----------
  52152459   52152458 TAG20190816T150116  VB$_90959062_52141864_29            12975.3698   52141864
  52146236   52146235 TAG20190816T150116  VB$_90959062_52141864I               .02734375   52141864

SQL>

Look above that now we have two backups linked to the same virtual full backup (compare with the same SQL from the previous phases). The backup set key 52152458 is the virtual full backup and was generated during the phase q_restore_fast. The backup set key 52146235 was generated during the phase fixup_unordered.

And there is now, two backups for this datafile:

SQL> SELECT bdf_key, bs_key, file#, ckp_scn, incr_level from bdf where CKP_SCN = 4932560891039 and file# = 29;

   BDF_KEY     BS_KEY      FILE#         CKP_SCN INCR_LEVEL
---------- ---------- ---------- --------------- ----------
  52146237   52146235         29   4932560891039          1
  52152460   52152458         29   4932560891039          0

SQL>

Just to show that the task took a lot of time to finish:

SQL> select rt.elapsed_seconds from ra_task rt where task_id = 56242962;

ELAPSED_SECONDS
---------------
     14563.7986

SQL>
SQL> select count(*) from plans_details where VB_KEY = 52141864;

  COUNT(*)
----------
  74505579

SQL>

INDEX_BACKUP, The internals

As you can see above, the INDEX_BACKUP task it is responsible to generate the virtual full backup and “fix” the rman catalog views to represent the new backups. This is done in two major phases (fixup_unordered and q_restore_fast) to read the backup that was ingested at ZDLRA and index all the blocks. The other phases are process_allfiles and plan_blocks, but they are fast to execute.

About the blocks, it is important to hint that it is completely based in SCN/CKP number. The index creation will search for all blocks that are bellow of the SCN of ingested backup. If you check the details, the backup file does not exist, by the way, it is just a huge list of the blocks need to this SCN (information stored at plans_details table).

The procedures, SQL, steps, phases that I showed before will have different values in your case. The DF_KEY, BS_KEY, and others will be different. But I think that you can understand the logic of how ZDLRA index the backup and internally generate the virtual full backup. I know that it is not easy to read all the numbers and link between sql’s and tables, but the logic it is simple: verify all the blocks that belong to datafile and create one plan that points to every block needed by the virtual full backup for one scn.

 

Disclaimer: “The postings on this site are my own and don’t necessarily represent my actual employer positions, strategies or opinions. The information here was edited to be useful for general purpose, specific data and identifications were removed to allow reach the generic audience and to be useful for the community.”

Share it

1 thought on “ZDLRA Internals, INDEX_BACKUP task in details

  1. Pingback: ZDLRA Internals, Virtual Full Backup | Fernando Simon

Leave a Reply

Your email address will not be published. Required fields are marked *