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.
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.
Backup
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).
