{"id":741,"date":"2020-06-15T19:04:05","date_gmt":"2020-06-15T22:04:05","guid":{"rendered":"https:\/\/www.fernandosimon.com\/blog\/?p=741"},"modified":"2020-07-19T19:07:50","modified_gmt":"2020-07-19T22:07:50","slug":"zdlra-ordering_wait-task-state","status":"publish","type":"post","link":"https:\/\/www.fernandosimon.com\/blog\/zdlra-ordering_wait-task-state\/","title":{"rendered":"ZDLRA, ORDERING_WAIT task state"},"content":{"rendered":"

Tasks for ZDLRA are the pillar of how the backups are processed, everything is one task. So, when you ingest incremental backup one task is created but can occur that it get a freeze at ORDERING_WAIT state. These tasks are hard to identify and can create a big problem for your virtual full backup and backup strategy. Below I will show how they occur and how to solve the problem.<\/p>\n

<\/p>\n

Incremental backups<\/h2>\n

To understand how they appear I need to show a little how the incremental backups work. Basically, look at the example below and one datafile with some blocks. When you do backup level 0, all the blocks are copied, and if I do backup incremental level 1 just the \u201cnew blocks\u201d are copied (just blocks that changed).<\/p>\n

\"\"<\/a><\/p>\n

As you can see above, the first backup of datafile (level 0), have SCN 3333. So, the consequent backup level 1 will copy everything that changed from that (and in this example have SCN 4444). And, the next incremental backup will pick up everything since the last backup, in this example, every block change since SCN 4444. So, it will generate a new backup with\/until SCN 5555.<\/p>\n

As you know, this is the definition of incremental backup. As you can see in the definition at \u201cAbout RMAN Incremental Backups<\/a>\u201d from docs it is:<\/p>\n

\u201cAn incremental backup copies only those data blocks that have changed since a previous backup. You can use RMAN to create incremental backups of data files, tablespaces, or the whole database.\u201d<\/em><\/p>\n

But the point it is that database\/rman knows the scn from the last backup, and when does incremental backup it copy everything since from the last scn. Each incremental backup \u201cknows\u201d (internally, with the database blocks that are inside) the start scn and endpoint scn. So, to \u201creconstruct\u201d the datafile, the database\/rman uses the full backup and all subsequent incremental backups.<\/p>\n

Incremental backup and ZDLRA<\/h2>\n

As I already wrote about incremental backups and ZDLRA<\/a>, they are used to construct the \u201cVirtual Full Backup\u201d. In a very resumed way, ZDLRA merges the stored backups and creates the virtual full backup (as I explained here too<\/a>).<\/p>\n

\"\"<\/a><\/p>\n

But even with this virtual backup, the way how the incremental backups work not change. The procedure is the same, check the scn from the last backup and copy all block change since that. As you can see in the image above, the full backups (blue in the image) are created merging the previous full with the ingested backup and are used as the base for the subsequent incremental.<\/p>\n

ORDERING_WAIT<\/h2>\n

The ORDERING_WAIT occurs when the task INDEX_BACKUP that creates the index (and the virtual full backup) can\u2019t finish because it doesn\u2019t have all the required data. And this occurs because (by some reason) one backup is created and not stored at ZDLRA. And can be even a duplicate to create the standby (remember that basically the duplicate is rman backup copied from one side to another).<\/p>\n

\"\"<\/a><\/p>\n

Look the image above, when after the SCN 4444 (that was the last backup stored at ZDLRA), another backup was taken and it is not inside of ZDLRA. So, when the new incremental backup is taken, it will copy all blocks changed from the last backup, but this last backup was the one that is not at ZDLRA (for rman side, it don\u2019t care where it is the backup. By definition, incremental backup it is from the last backup, whatever or wherever it is).<\/p>\n

And when this incremental backup it is ingested at ZDLRA, it will try to create the virtual full. But since the last stored backup have the SCN 4444, and the new incremental pickup blocks changed since SCN 5555 and go until the SCN 6666, ZDLRA knows that it is a gap when opens this ingested backup. ZDLRA doesn\u2019t have the blocks that are between SCN 4444 and 5555 (look the yellow block, backup exists just outside of ZDLRA).<\/strong><\/p>\n

So, it is impossible to create the virtual full backup for SCN 6666, and the INDEX_BACKUP task will be at hold in state ORDERING_WAIT. To solve, there is two option, you can take a new level 0 backup or use BACKUP [CUMULATIVE] INCREMENTAL LEVEL 1 \u2026 FOR RECOVER OF TAG ‘<TAG>’<\/strong> command. I will show you below how to do that.<\/p>\n

How this occurs and how to solve<\/h2>\n

Bellow, I will show how you can identify and solve the problem. I will use the solution wrote in the last paragraph. But you can check the internal details of how occurs, to identify, and how to solve the issue.<\/p>\n

In this scenario, I will use one database (number 12 \u2013 tablespace users for one PDB). So, first, check the backups for datafile 12:<\/p>\n

RMAN> list backup of datafile 12 completed after \"sysdate - 20\/1440\";\r\n\r\n\r\nList of Backup Sets\r\n===================\r\n\r\n\r\nBS Key  Type LV Size       Device Type Elapsed Time Completion Time\r\n------- ---- -- ---------- ----------- ------------ -------------------\r\n24283   Incr 1  40.00K     SBT_TAPE    00:00:04     20\/04\/2020 23:49:26\r\n        BP Key: 24284   Status: AVAILABLE  Compressed: YES  Tag: TAG20200420T234921\r\n        Handle: VB$_1891149551_24282I   Media:\r\n  List of Datafiles in backup set 24283\r\n  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name\r\n  ---- -- ---- ---------- ------------------- ----------- ------ ----\r\n  12   1  Incr 2013573    20\/04\/2020 23:49:22              NO    \/u01\/app\/oracle\/oradata\/ORCL18C\/ORCL18P\/users01.dbf\r\n\r\nBS Key  Type LV Size       Device Type Elapsed Time Completion Time\r\n------- ---- -- ---------- ----------- ------------ -------------------\r\n24287   Incr 0  40.00K     SBT_TAPE    00:00:04     20\/04\/2020 23:49:26\r\n        BP Key: 24288   Status: AVAILABLE  Compressed: YES  Tag: TAG20200420T234921\r\n        Handle: VB$_1891149551_24282_12   Media:\r\n  List of Datafiles in backup set 24287\r\n  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name\r\n  ---- -- ---- ---------- ------------------- ----------- ------ ----\r\n  12   0  Incr 2013573    20\/04\/2020 23:49:22              NO    \/u01\/app\/oracle\/oradata\/ORCL18C\/ORCL18P\/users01.dbf\r\n\r\nRMAN>\r\n<\/pre>\n
As you can see, we have Incr 1 and Incr 0 levels with the same scn. And the name of the handle starts with VB$. This means that virtual full backup it is OK and created by ZDLRA.<\/p>\n
And if I do a new incremental backup, ZDLRA generates a new virtual full backup:<\/p>\n
RMAN> BACKUP INCREMENTAL LEVEL 1 DEVICE TYPE SBT FILESPERSET 1 DATAFILE 12;\r\n\r\nStarting backup at 20\/04\/2020 23:51:58\r\nusing channel ORA_SBT_TAPE_1\r\nchannel ORA_SBT_TAPE_1: starting incremental level 1 datafile backup set\r\nchannel ORA_SBT_TAPE_1: specifying datafile(s) in backup set\r\ninput datafile file number=00012 name=\/u01\/app\/oracle\/oradata\/ORCL18C\/ORCL18P\/users01.dbf\r\nchannel ORA_SBT_TAPE_1: starting piece 1 at 20\/04\/2020 23:51:59\r\nchannel ORA_SBT_TAPE_1: finished piece 1 at 20\/04\/2020 23:52:02\r\npiece handle=ORCL18C_a1uu5dsv_1_1 tag=TAG20200420T235158 comment=API Version 2.0,MMS Version 12.2.0.2\r\nchannel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:03\r\nFinished backup at 20\/04\/2020 23:52:02\r\n\r\nStarting Control File and SPFILE Autobackup at 20\/04\/2020 23:52:02\r\npiece handle=c-558466555-20200420-0b comment=API Version 2.0,MMS Version 12.2.0.2\r\nFinished Control File and SPFILE Autobackup at 20\/04\/2020 23:52:10\r\n\r\nRMAN> list backup of datafile 12 completed after \"sysdate - 20\/1440\";\r\n\r\n\r\nList of Backup Sets\r\n===================\r\n\r\n\r\nBS Key  Type LV Size       Device Type Elapsed Time Completion Time\r\n------- ---- -- ---------- ----------- ------------ -------------------\r\n24283   Incr 1  40.00K     SBT_TAPE    00:00:04     20\/04\/2020 23:49:26\r\n        BP Key: 24284   Status: AVAILABLE  Compressed: YES  Tag: TAG20200420T234921\r\n        Handle: VB$_1891149551_24282I   Media:\r\n  List of Datafiles in backup set 24283\r\n  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name\r\n  ---- -- ---- ---------- ------------------- ----------- ------ ----\r\n  12   1  Incr 2013573    20\/04\/2020 23:49:22              NO    \/u01\/app\/oracle\/oradata\/ORCL18C\/ORCL18P\/users01.dbf\r\n\r\nBS Key  Type LV Size       Device Type Elapsed Time Completion Time\r\n------- ---- -- ---------- ----------- ------------ -------------------\r\n24287   Incr 0  40.00K     SBT_TAPE    00:00:04     20\/04\/2020 23:49:26\r\n        BP Key: 24288   Status: AVAILABLE  Compressed: YES  Tag: TAG20200420T234921\r\n        Handle: VB$_1891149551_24282_12   Media:\r\n  List of Datafiles in backup set 24287\r\n  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name\r\n  ---- -- ---- ---------- ------------------- ----------- ------ ----\r\n  12   0  Incr 2013573    20\/04\/2020 23:49:22              NO    \/u01\/app\/oracle\/oradata\/ORCL18C\/ORCL18P\/users01.dbf\r\n\r\nBS Key  Type LV Size       Device Type Elapsed Time Completion Time\r\n------- ---- -- ---------- ----------- ------------ -------------------\r\n24355   Incr 1  40.00K     SBT_TAPE    00:00:03     20\/04\/2020 23:52:02\r\n        BP Key: 24356   Status: AVAILABLE  Compressed: YES  Tag: TAG20200420T235158\r\n        Handle: VB$_1891149551_24354I   Media:\r\n  List of Datafiles in backup set 24355\r\n  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name\r\n  ---- -- ---- ---------- ------------------- ----------- ------ ----\r\n  12   1  Incr 2013810    20\/04\/2020 23:51:59              NO    \/u01\/app\/oracle\/oradata\/ORCL18C\/ORCL18P\/users01.dbf\r\n\r\nBS Key  Type LV Size       Device Type Elapsed Time Completion Time\r\n------- ---- -- ---------- ----------- ------------ -------------------\r\n24359   Incr 0  40.00K     SBT_TAPE    00:00:03     20\/04\/2020 23:52:02\r\n        BP Key: 24360   Status: AVAILABLE  Compressed: YES  Tag: TAG20200420T235158\r\n        Handle: VB$_1891149551_24354_12   Media:\r\n  List of Datafiles in backup set 24359\r\n  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name\r\n  ---- -- ---- ---------- ------------------- ----------- ------ ----\r\n  12   0  Incr 2013810    20\/04\/2020 23:51:59              NO    \/u01\/app\/oracle\/oradata\/ORCL18C\/ORCL18P\/users01.dbf\r\n\r\nRMAN><\/pre>\n
Simulating the error<\/h2>\n
Just to remember that this new incremental have all scn from 2013573 until 2013810.<\/p>\n
But by some reason one full backup it takes to another place (like disk, or duplicate for standby). Look that the channel type it is not ZDLRA:<\/p>\n
RMAN> BACKUP INCREMENTAL LEVEL 0 DEVICE TYPE disk format '\/tmp\/%U' DATAFILE 12 TAG 'BKP-DBF-TO-DISK';\r\n\r\nStarting backup at 20\/04\/2020 23:54:01\r\nreleased channel: ORA_SBT_TAPE_1\r\nallocated channel: ORA_DISK_1\r\nchannel ORA_DISK_1: SID=65 device type=DISK\r\nchannel ORA_DISK_1: starting incremental level 0 datafile backup set\r\nchannel ORA_DISK_1: specifying datafile(s) in backup set\r\ninput datafile file number=00012 name=\/u01\/app\/oracle\/oradata\/ORCL18C\/ORCL18P\/users01.dbf\r\nchannel ORA_DISK_1: starting piece 1 at 20\/04\/2020 23:54:01\r\nchannel ORA_DISK_1: finished piece 1 at 20\/04\/2020 23:54:02\r\npiece handle=\/tmp\/a3uu5e0p_1_1 tag=BKP-DBF-TO-DISK comment=NONE\r\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01\r\nFinished backup at 20\/04\/2020 23:54:02\r\n\r\nStarting Control File and SPFILE Autobackup at 20\/04\/2020 23:54:02\r\npiece handle=\/u01\/app\/oracle\/oradata\/ORCL18C\/autobackup\/2020_04_20\/o1_mf_s_1038268443_h9w6hwht_.bkp comment=NONE\r\nFinished Control File and SPFILE Autobackup at 20\/04\/2020 23:54:10\r\n\r\nRMAN> list backup tag = 'BKP-DBF-TO-DISK';\r\n\r\n\r\nList of Backup Sets\r\n===================\r\n\r\n\r\nBS Key  Type LV Size       Device Type Elapsed Time Completion Time\r\n------- ---- -- ---------- ----------- ------------ -------------------\r\n24391   Incr 0  1.18M      DISK        00:00:00     20\/04\/2020 23:54:01\r\n        BP Key: 24394   Status: AVAILABLE  Compressed: NO  Tag: BKP-DBF-TO-DISK\r\n        Piece Name: \/tmp\/a3uu5e0p_1_1\r\n  List of Datafiles in backup set 24391\r\n  Container ID: 3, PDB Name: ORCL18P\r\n  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name\r\n  ---- -- ---- ---------- ------------------- ----------- ------ ----\r\n  12   0  Incr 2013972    20\/04\/2020 23:54:01              NO    \/u01\/app\/oracle\/oradata\/ORCL18C\/ORCL18P\/users01.dbf\r\n\r\nRMAN><\/pre>\n
Now, if I try to execute the incremental level 1, the ingested backup does not generate a new virtual backup:<\/p>\n
RMAN> BACKUP INCREMENTAL LEVEL 1 DEVICE TYPE SBT FILESPERSET 1 DATAFILE 12;\r\n\r\nStarting backup at 20\/04\/2020 23:55:23\r\nreleased channel: ORA_DISK_1\r\nallocated channel: ORA_SBT_TAPE_1\r\nchannel ORA_SBT_TAPE_1: SID=65 device type=SBT_TAPE\r\nchannel ORA_SBT_TAPE_1: RA Library (ZDLRAS1) SID=A3C0F4C16DAA11FAE053010310ACC1C4\r\nchannel ORA_SBT_TAPE_1: starting incremental level 1 datafile backup set\r\nchannel ORA_SBT_TAPE_1: specifying datafile(s) in backup set\r\ninput datafile file number=00012 name=\/u01\/app\/oracle\/oradata\/ORCL18C\/ORCL18P\/users01.dbf\r\nchannel ORA_SBT_TAPE_1: starting piece 1 at 20\/04\/2020 23:55:24\r\nchannel ORA_SBT_TAPE_1: finished piece 1 at 20\/04\/2020 23:55:27\r\npiece handle=ORCL18C_a5uu5e3c_1_1 tag=TAG20200420T235524 comment=API Version 2.0,MMS Version 12.2.0.2\r\nchannel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:03\r\nFinished backup at 20\/04\/2020 23:55:27\r\n\r\nStarting Control File and SPFILE Autobackup at 20\/04\/2020 23:55:27\r\npiece handle=c-558466555-20200420-0d comment=API Version 2.0,MMS Version 12.2.0.2\r\nFinished Control File and SPFILE Autobackup at 20\/04\/2020 23:55:35\r\n\r\nRMAN> list backup of datafile 12 completed after \"sysdate - 20\/1440\";\r\n\r\n\r\nList of Backup Sets\r\n===================\r\n\r\n\r\nBS Key  Type LV Size       Device Type Elapsed Time Completion Time\r\n------- ---- -- ---------- ----------- ------------ -------------------\r\n24283   Incr 1  40.00K     SBT_TAPE    00:00:04     20\/04\/2020 23:49:26\r\n        BP Key: 24284   Status: AVAILABLE  Compressed: YES  Tag: TAG20200420T234921\r\n        Handle: VB$_1891149551_24282I   Media:\r\n  List of Datafiles in backup set 24283\r\n  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name\r\n  ---- -- ---- ---------- ------------------- ----------- ------ ----\r\n  12   1  Incr 2013573    20\/04\/2020 23:49:22              NO    \/u01\/app\/oracle\/oradata\/ORCL18C\/ORCL18P\/users01.dbf\r\n\r\nBS Key  Type LV Size       Device Type Elapsed Time Completion Time\r\n------- ---- -- ---------- ----------- ------------ -------------------\r\n24287   Incr 0  40.00K     SBT_TAPE    00:00:04     20\/04\/2020 23:49:26\r\n        BP Key: 24288   Status: AVAILABLE  Compressed: YES  Tag: TAG20200420T234921\r\n        Handle: VB$_1891149551_24282_12   Media:\r\n  List of Datafiles in backup set 24287\r\n  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name\r\n  ---- -- ---- ---------- ------------------- ----------- ------ ----\r\n  12   0  Incr 2013573    20\/04\/2020 23:49:22              NO    \/u01\/app\/oracle\/oradata\/ORCL18C\/ORCL18P\/users01.dbf\r\n\r\nBS Key  Type LV Size       Device Type Elapsed Time Completion Time\r\n------- ---- -- ---------- ----------- ------------ -------------------\r\n24355   Incr 1  40.00K     SBT_TAPE    00:00:03     20\/04\/2020 23:52:02\r\n        BP Key: 24356   Status: AVAILABLE  Compressed: YES  Tag: TAG20200420T235158\r\n        Handle: VB$_1891149551_24354I   Media:\r\n  List of Datafiles in backup set 24355\r\n  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name\r\n  ---- -- ---- ---------- ------------------- ----------- ------ ----\r\n  12   1  Incr 2013810    20\/04\/2020 23:51:59              NO    \/u01\/app\/oracle\/oradata\/ORCL18C\/ORCL18P\/users01.dbf\r\n\r\nBS Key  Type LV Size       Device Type Elapsed Time Completion Time\r\n------- ---- -- ---------- ----------- ------------ -------------------\r\n24359   Incr 0  40.00K     SBT_TAPE    00:00:03     20\/04\/2020 23:52:02\r\n        BP Key: 24360   Status: AVAILABLE  Compressed: YES  Tag: TAG20200420T235158\r\n        Handle: VB$_1891149551_24354_12   Media:\r\n  List of Datafiles in backup set 24359\r\n  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name\r\n  ---- -- ---- ---------- ------------------- ----------- ------ ----\r\n  12   0  Incr 2013810    20\/04\/2020 23:51:59              NO    \/u01\/app\/oracle\/oradata\/ORCL18C\/ORCL18P\/users01.dbf\r\n\r\nBS Key  Type LV Size       Device Type Elapsed Time Completion Time\r\n------- ---- -- ---------- ----------- ------------ -------------------\r\n24391   Incr 0  1.18M      DISK        00:00:00     20\/04\/2020 23:54:01\r\n        BP Key: 24394   Status: AVAILABLE  Compressed: NO  Tag: BKP-DBF-TO-DISK\r\n        Piece Name: \/tmp\/a3uu5e0p_1_1\r\n  List of Datafiles in backup set 24391\r\n  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name\r\n  ---- -- ---- ---------- ------------------- ----------- ------ ----\r\n  12   0  Incr 2013972    20\/04\/2020 23:54:01              NO    \/u01\/app\/oracle\/oradata\/ORCL18C\/ORCL18P\/users01.dbf\r\n\r\nBS Key  Type LV Size       Device Type Elapsed Time Completion Time\r\n------- ---- -- ---------- ----------- ------------ -------------------\r\n24430   Incr 1  256.00K    SBT_TAPE    00:00:00     20\/04\/2020 23:55:24\r\n        BP Key: 24431   Status: AVAILABLE  Compressed: NO  Tag: TAG20200420T235524\r\n        Handle: ORCL18C_a5uu5e3c_1_1   Media: Recovery Appliance (ZDLRAS1)\r\n  List of Datafiles in backup set 24430\r\n  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name\r\n  ---- -- ---- ---------- ------------------- ----------- ------ ----\r\n  12   1  Incr 2014089    20\/04\/2020 23:55:24              NO    \/u01\/app\/oracle\/oradata\/ORCL18C\/ORCL18P\/users01.dbf\r\n\r\nRMAN><\/pre>\n
Look above that the last incremental backup not generated a new level 0, it is the backupset 24430 (and backuppiece 24431).<\/p>\n
Task inside ZDLRA<\/h3>\n
If we enter inside of ZDLRA, we can see that the task responsible to index the backupiece 23286 is in state ORDEING_WAIT. You can use query over ra_task<\/em> and check using the state<\/em> column.<\/p>\n
SQL> select TASK_ID, TASK_TYPE, STATE, WAITING_ON, DB_KEY, DB_UNIQUE_NAME, CREATION_TIME, ERROR_COUNT, INTERRUPT_COUNT, BP_KEY,BS_KEY,DF_KEY,VB_KEY from rasys.ra_task where db_unique_name = 'ORCL18C' and state = 'ORDERING_WAIT' order by 5,2,7,10,11,12,13;\r\n\r\n   TASK_ID TASK_TYPE       STATE                     WAITING_ON     DB_KEY DB_UNIQUE_NAME                 CREATION_TIME                       ERROR_COUNT INTERRUPT_COUNT     BP_KEY     BS_KEY     DF_KEY     VB_KEY\r\n---------- --------------- ------------------------- ---------- ---------- ------------------------------ ----------------------------------- ----------- --------------- ---------- ---------- ---------- ----------\r\n     40203 INDEX_BACKUP    ORDERING_WAIT                             15993 ORCL18C                        20-APR-20 11.55.26.779191 PM +02:00           0               1      24431\r\n\r\nSQL><\/pre>\n
And there is no incident for this error or state:<\/p>\n
SQL> select incident_id, error_text last_seen from ra_incident_log where db_unique_name = 'ORCL18C' and status not in ('FIXED', 'RESET') order by last_seen desc;\r\n\r\nno rows selected\r\n\r\nSQL><\/pre>\n
Unfortunately, this occurs for ZDLRA, even if there is a task in the ORDEING_WAIT state, it is not reported as an error. And if you think about, the virtual full backup it is not generated and the feature that it is the virtual full backup is not in place for these datafiles.<\/p>\n
But even with tasks in this state, you will not be unprotected, the backup is there and can be restored.<\/p>\n
ZDLRA Internals<\/h3>\n
If we check inside of ZDLRA we can check more details (this is one example how to navigate through internal tables of ZDLRA rman catalog and find the information \u2013 same that you find with list backupset<\/em> inside rman):<\/p>\n
SQL> select bs_key, db_key, pdb_key from bp where bp_key = 24431;\r\n\r\n    BS_KEY     DB_KEY    PDB_KEY\r\n---------- ---------- ----------\r\n     24430      15993      16000\r\n\r\nSQL> select * from rc_database where db_key = 15993;\r\n\r\n    DB_KEY  DBINC_KEY       DBID NAME     RESETLOGS_CHANGE# RESETLOGS FINAL_CHANGE#\r\n---------- ---------- ---------- -------- ----------------- --------- -------------\r\n     15993      15994  558466555 ORCL18C            1477662 11-AUG-19\r\n\r\nSQL> select df_key from df where dbinc_key = 15994 and  file# = 12;\r\n\r\n    DF_KEY\r\n----------\r\n     16026\r\n\r\nSQL> select bdf_key, ckp_scn from bdf where bs_key = 24430;\r\n\r\n   BDF_KEY    CKP_SCN\r\n---------- ----------\r\n     24432    2014089\r\n\r\nSQL>\r\n###################################################################\r\nRMAN> list backupset 24430;\r\n\r\n\r\nList of Backup Sets\r\n===================\r\n\r\n\r\nBS Key  Type LV Size       Device Type Elapsed Time Completion Time\r\n------- ---- -- ---------- ----------- ------------ -------------------\r\n24430   Incr 1  256.00K    SBT_TAPE    00:00:00     20\/04\/2020 23:55:24\r\n        BP Key: 24431   Status: AVAILABLE  Compressed: NO  Tag: TAG20200420T235524\r\n        Handle: ORCL18C_a5uu5e3c_1_1   Media: Recovery Appliance (ZDLRAS1)\r\n  List of Datafiles in backup set 24430\r\n  Container ID: 3, PDB Name: ORCL18P\r\n  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name\r\n  ---- -- ---- ---------- ------------------- ----------- ------ ----\r\n  12   1  Incr 2014089    20\/04\/2020 23:55:24              NO    \/u01\/app\/oracle\/oradata\/ORCL18C\/ORCL18P\/users01.dbf\r\n\r\nRMAN><\/pre>\n
Using the BP table we can find the DB_KEY, and with that, we can go to RC_DATABASE to get the incarnation key (DBINC_KEY). With that, at DF table we can discover the DF_KEY for the datafile 12 and for this database incarnation. And with the DBF table, we can find the CKP_SCN for this datafile.<\/p>\n
But if we go to the BLOCKS tables (ZDLRA table that stores the index \u2013 and \u201cmore or less\u201d the virtual full), there are no database blocks with the SCN 2014089 for the backupset 24430.  If you want to understand who it works, you can read my post<\/a> about this.<\/p>\n
SQL> select * from blocks where df_key = 16026 and scn >= 2014089;\r\n\r\nno rows selected\r\n\r\nSQL><\/pre>\n
And if we check for SCN 2013972 (that came from backup was put in disk), nothing too (as expected):<\/p>\n
SQL> select * from blocks where df_key = 16026 and scn >= 2013972;\r\n\r\nno rows selected\r\n\r\nSQL><\/pre>\n
As if we check with the last SCN 2013810 that are know by ZDLRA (last virtual full backup, backupset 24359), we can see which blocks are there:<\/p>\n
SQL> select * from blocks where df_key = 16026 and scn >= 2013810 order by scn, chunkno ;\r\n\r\n    DF_KEY    BLOCKNO        SCN     CKP_ID    CHUNKNO    COFFSET       USED  DBINC_KEY     ENDBLK\r\n---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------\r\n     16026          1    2013810    2013810      25601      33121        338      15994\r\n     16026          0    2013810    2013810      25601       8192      24576      15994\r\n\r\nSQL><\/pre>\n
And there are just PLANS for this virtual backup (nothing generated for the next existing backups):<\/p>\n
SQL> select VB_KEY, DF_KEY, CKP_SCN, SRCBP_KEY, VCBP_KEY from vbdf where CKP_SCN >= 2013810 and df_key = 16026;\r\n\r\n    VB_KEY     DF_KEY    CKP_SCN  SRCBP_KEY   VCBP_KEY\r\n---------- ---------- ---------- ---------- ----------\r\n     24354      16026    2013810      24293      24356\r\n\r\nSQL> select * from plans_details where VB_KEY = 24354;\r\n\r\n    DF_KEY       TYPE     VB_KEY    BLKRANK    BLOCKNO    CHUNKNO    NUMBLKS    COFFSET   NUMBYTES\r\n---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------\r\n     16026          1      24354          1          0      25601          1       8192      24576\r\n     16026          1      24354          1          2      16385          2      32788        256\r\n     16026          1      24354          1          4      14337        132      33045      14000\r\n     16026          1      24354          1        136      16385          3      33044        553\r\n     16026          1      24354          1        139      23553          1      32788        327\r\n     16026          1      24354          1        140      16385          2      33786        264\r\n     16026          1      24354          1        142      25601          1      32788        333\r\n     16026          1      24354          1        143      16385          1      34182        132\r\n     16026          1      24354          1        191      14337          3      47045        252\r\n     16026          1      24354          1 4294967295      25601          1      33121        338\r\n\r\n10 rows selected.\r\n\r\nSQL><\/pre>\n
As you can figure out, ZDLRA can\u2019t fill the gap to create the virtual full backup.<\/p>\n
Recurring error<\/h3>\n
If you not solve the problem, and continue to ingest backups, the task will remain in ORDERING_WAIT:<\/p>\n
RMAN> BACKUP INCREMENTAL LEVEL 1 DEVICE TYPE SBT FILESPERSET 1 DATAFILE 12;\r\n\r\nStarting backup at 21\/04\/2020 00:04:00\r\nusing channel ORA_SBT_TAPE_1\r\nchannel ORA_SBT_TAPE_1: starting incremental level 1 datafile backup set\r\nchannel ORA_SBT_TAPE_1: specifying datafile(s) in backup set\r\ninput datafile file number=00012 name=\/u01\/app\/oracle\/oradata\/ORCL18C\/ORCL18P\/users01.dbf\r\nchannel ORA_SBT_TAPE_1: starting piece 1 at 21\/04\/2020 00:04:01\r\nchannel ORA_SBT_TAPE_1: finished piece 1 at 21\/04\/2020 00:04:04\r\npiece handle=ORCL18C_a7uu5ejh_1_1 tag=TAG20200421T000400 comment=API Version 2.0,MMS Version 12.2.0.2\r\nchannel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:03\r\nFinished backup at 21\/04\/2020 00:04:04\r\n\r\nStarting Control File and SPFILE Autobackup at 21\/04\/2020 00:04:04\r\npiece handle=c-558466555-20200421-00 comment=API Version 2.0,MMS Version 12.2.0.2\r\nFinished Control File and SPFILE Autobackup at 21\/04\/2020 00:04:13\r\n\r\nRMAN> list backup of datafile 12 completed after \"sysdate - 5\/1440\";\r\n\r\n\r\nList of Backup Sets\r\n===================\r\n\r\n\r\nBS Key  Type LV Size       Device Type Elapsed Time Completion Time\r\n------- ---- -- ---------- ----------- ------------ -------------------\r\n24511   Incr 1  256.00K    SBT_TAPE    00:00:01     21\/04\/2020 00:04:02\r\n        BP Key: 24512   Status: AVAILABLE  Compressed: NO  Tag: TAG20200421T000400\r\n        Handle: ORCL18C_a7uu5ejh_1_1   Media: Recovery Appliance (ZDLRAS1)\r\n  List of Datafiles in backup set 24511\r\n  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name\r\n  ---- -- ---- ---------- ------------------- ----------- ------ ----\r\n  12   1  Incr 2021757    21\/04\/2020 00:04:01              NO    \/u01\/app\/oracle\/oradata\/ORCL18C\/ORCL18P\/users01.dbf\r\n\r\nRMAN>\r\n####################################\r\nSQL> select TASK_ID, TASK_TYPE, STATE, WAITING_ON, DB_KEY, DB_UNIQUE_NAME, CREATION_TIME, ERROR_COUNT, INTERRUPT_COUNT, BP_KEY,BS_KEY,DF_KEY,VB_KEY from rasys.ra_task where db_unique_name = 'ORCL18C' and state = 'ORDERING_WAIT' order by 5,2,7,10,11,12,13;\r\n\r\n   TASK_ID TASK_TYPE       STATE                     WAITING_ON     DB_KEY DB_UNIQUE_NAME                 CREATION_TIME                       ERROR_COUNT INTERRUPT_COUNT     BP_KEY     BS_KEY     DF_KEY     VB_KEY\r\n---------- --------------- ------------------------- ---------- ---------- ------------------------------ ----------------------------------- ----------- --------------- ---------- ---------- ---------- ----------\r\n     40203 INDEX_BACKUP    ORDERING_WAIT                             15993 ORCL18C                        20-APR-20 11.55.26.779191 PM +02:00           0               1      24431\r\n     40210 INDEX_BACKUP    ORDERING_WAIT                             15993 ORCL18C                        21-APR-20 12.04.05.207342 AM +02:00           0               1      24512\r\n\r\nSQL><\/pre>\n
Even if you try to do a cumulative incremental backup, the problem will be the same:<\/p>\n
RMAN> BACKUP CUMULATIVE INCREMENTAL LEVEL 1 DEVICE TYPE SBT FILESPERSET 1 DATAFILE 12;\r\n\r\nStarting backup at 21\/04\/2020 00:07:13\r\nusing channel ORA_SBT_TAPE_1\r\nchannel ORA_SBT_TAPE_1: starting incremental level 1 datafile backup set\r\nchannel ORA_SBT_TAPE_1: specifying datafile(s) in backup set\r\ninput datafile file number=00012 name=\/u01\/app\/oracle\/oradata\/ORCL18C\/ORCL18P\/users01.dbf\r\nchannel ORA_SBT_TAPE_1: starting piece 1 at 21\/04\/2020 00:07:13\r\nchannel ORA_SBT_TAPE_1: finished piece 1 at 21\/04\/2020 00:07:20\r\npiece handle=ORCL18C_a9uu5eph_1_1 tag=TAG20200421T000713 comment=API Version 2.0,MMS Version 12.2.0.2\r\nchannel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:07\r\nFinished backup at 21\/04\/2020 00:07:20\r\n\r\nStarting Control File and SPFILE Autobackup at 21\/04\/2020 00:07:20\r\npiece handle=c-558466555-20200421-01 comment=API Version 2.0,MMS Version 12.2.0.2\r\nFinished Control File and SPFILE Autobackup at 21\/04\/2020 00:07:28\r\n\r\nRMAN> list backup of datafile 12 completed after \"sysdate - 2\/1440\";\r\n\r\n\r\nList of Backup Sets\r\n===================\r\n\r\n\r\nBS Key  Type LV Size       Device Type Elapsed Time Completion Time\r\n------- ---- -- ---------- ----------- ------------ -------------------\r\n24604   Incr 1  256.00K    SBT_TAPE    00:00:02     21\/04\/2020 00:07:15\r\n        BP Key: 24605   Status: AVAILABLE  Compressed: NO  Tag: TAG20200421T000713\r\n        Handle: ORCL18C_a9uu5eph_1_1   Media: Recovery Appliance (ZDLRAS1)\r\n  List of Datafiles in backup set 24604\r\n  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name\r\n  ---- -- ---- ---------- ------------------- ----------- ------ ----\r\n  12   1  Incr 2021959    21\/04\/2020 00:07:13              NO    \/u01\/app\/oracle\/oradata\/ORCL18C\/ORCL18P\/users01.dbf\r\n\r\nRMAN>\r\n#############################################\r\nSQL> select TASK_ID, TASK_TYPE, STATE, WAITING_ON, DB_KEY, DB_UNIQUE_NAME, CREATION_TIME, ERROR_COUNT, INTERRUPT_COUNT, BP_KEY,BS_KEY,DF_KEY,VB_KEY from rasys.ra_task where db_unique_name = 'ORCL18C' and state = 'ORDERING_WAIT' order by 5,2,7,10,11,12,13;\r\n\r\n   TASK_ID TASK_TYPE       STATE                     WAITING_ON     DB_KEY DB_UNIQUE_NAME                 CREATION_TIME                       ERROR_COUNT INTERRUPT_COUNT     BP_KEY     BS_KEY     DF_KEY     VB_KEY\r\n---------- --------------- ------------------------- ---------- ---------- ------------------------------ ----------------------------------- ----------- --------------- ---------- ---------- ---------- ----------\r\n     40203 INDEX_BACKUP    ORDERING_WAIT                             15993 ORCL18C                        20-APR-20 11.55.26.779191 PM +02:00           0               1      24431\r\n     40210 INDEX_BACKUP    ORDERING_WAIT                             15993 ORCL18C                        21-APR-20 12.04.05.207342 AM +02:00           0               1      24512\r\n     40215 INDEX_BACKUP    ORDERING_WAIT                             15993 ORCL18C                        21-APR-20 12.07.18.140618 AM +02:00           0               1      24605\r\n\r\nSQL><\/pre>\n
Solving ORDERING_WAIT<\/h2>\n
There are two ways to solve the issue, for both the idea is the same: ingest the database blocks inside of ZDLRA. And we do this performing backup.<\/p>\n
FOR RECOVER OF TAG<\/h3>\n
The first is to use the command \u201cBACKUP [CUMULATIVE] INCREMENTAL LEVEL 1 \u2026 FOR RECOVER OF TAG ‘<TAG>’\u201d. The idea here is to create one incremental backup that recovers since one specific tag. You can check the documentation<\/a> for more details if you want, it exists since Oracle 10g.<\/p>\n
As you can imagine, the critical point here is to define the correct TAG do be used as a reference. And in this case, the tag is the last full backup (virtual or no) that it is inside of ZDLRA. Doing this, we ingest all changed blocks and fill the gap that is holding the task.<\/p>\n
In this case, I used normal incremental. Look that the tag is from the last virtual full backup that is inside of ZDLRA for this datafile:<\/p>\n
RMAN> BACKUP INCREMENTAL LEVEL 1 DEVICE TYPE SBT FOR RECOVER OF TAG 'TAG20200420T235158' DATAFILE 12;\r\n\r\nStarting backup at 21\/04\/2020 00:12:17\r\nusing channel ORA_SBT_TAPE_1\r\nchannel ORA_SBT_TAPE_1: starting incremental level 1 datafile backup set\r\nchannel ORA_SBT_TAPE_1: specifying datafile(s) in backup set\r\ninput datafile file number=00012 name=\/u01\/app\/oracle\/oradata\/ORCL18C\/ORCL18P\/users01.dbf\r\nchannel ORA_SBT_TAPE_1: starting piece 1 at 21\/04\/2020 00:12:17\r\nchannel ORA_SBT_TAPE_1: finished piece 1 at 21\/04\/2020 00:12:20\r\npiece handle=ORCL18C_abuu5f31_1_1 tag=TAG20200420T235158 comment=API Version 2.0,MMS Version 12.2.0.2\r\nchannel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:03\r\nFinished backup at 21\/04\/2020 00:12:20\r\n\r\nStarting Control File and SPFILE Autobackup at 21\/04\/2020 00:12:20\r\npiece handle=c-558466555-20200421-02 comment=API Version 2.0,MMS Version 12.2.0.2\r\nFinished Control File and SPFILE Autobackup at 21\/04\/2020 00:12:28\r\n\r\nRMAN><\/pre>\n
And we can see that a new virtual full backup was created with this incremental backup (look the last 5 backupsets):<\/p>\n
RMAN> list backup of datafile 12 completed after \"sysdate - 40\/1440\";\r\n\r\n\r\nList of Backup Sets\r\n===================\r\n\r\n\r\nBS Key  Type LV Size       Device Type Elapsed Time Completion Time\r\n------- ---- -- ---------- ----------- ------------ -------------------\r\n24283   Incr 1  40.00K     SBT_TAPE    00:00:04     20\/04\/2020 23:49:26\r\n        BP Key: 24284   Status: AVAILABLE  Compressed: YES  Tag: TAG20200420T234921\r\n        Handle: VB$_1891149551_24282I   Media:\r\n  List of Datafiles in backup set 24283\r\n  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name\r\n  ---- -- ---- ---------- ------------------- ----------- ------ ----\r\n  12   1  Incr 2013573    20\/04\/2020 23:49:22              NO    \/u01\/app\/oracle\/oradata\/ORCL18C\/ORCL18P\/users01.dbf\r\n\r\nBS Key  Type LV Size       Device Type Elapsed Time Completion Time\r\n------- ---- -- ---------- ----------- ------------ -------------------\r\n24287   Incr 0  40.00K     SBT_TAPE    00:00:04     20\/04\/2020 23:49:26\r\n        BP Key: 24288   Status: AVAILABLE  Compressed: YES  Tag: TAG20200420T234921\r\n        Handle: VB$_1891149551_24282_12   Media:\r\n  List of Datafiles in backup set 24287\r\n  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name\r\n  ---- -- ---- ---------- ------------------- ----------- ------ ----\r\n  12   0  Incr 2013573    20\/04\/2020 23:49:22              NO    \/u01\/app\/oracle\/oradata\/ORCL18C\/ORCL18P\/users01.dbf\r\n\r\nBS Key  Type LV Size       Device Type Elapsed Time Completion Time\r\n------- ---- -- ---------- ----------- ------------ -------------------\r\n24355   Incr 1  40.00K     SBT_TAPE    00:00:03     20\/04\/2020 23:52:02\r\n        BP Key: 24356   Status: AVAILABLE  Compressed: YES  Tag: TAG20200420T235158\r\n        Handle: VB$_1891149551_24354I   Media:\r\n  List of Datafiles in backup set 24355\r\n  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name\r\n  ---- -- ---- ---------- ------------------- ----------- ------ ----\r\n  12   1  Incr 2013810    20\/04\/2020 23:51:59              NO    \/u01\/app\/oracle\/oradata\/ORCL18C\/ORCL18P\/users01.dbf\r\n\r\nBS Key  Type LV Size       Device Type Elapsed Time Completion Time\r\n------- ---- -- ---------- ----------- ------------ -------------------\r\n24359   Incr 0  40.00K     SBT_TAPE    00:00:03     20\/04\/2020 23:52:02\r\n        BP Key: 24360   Status: AVAILABLE  Compressed: YES  Tag: TAG20200420T235158\r\n        Handle: VB$_1891149551_24354_12   Media:\r\n  List of Datafiles in backup set 24359\r\n  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name\r\n  ---- -- ---- ---------- ------------------- ----------- ------ ----\r\n  12   0  Incr 2013810    20\/04\/2020 23:51:59              NO    \/u01\/app\/oracle\/oradata\/ORCL18C\/ORCL18P\/users01.dbf\r\n\r\nBS Key  Type LV Size       Device Type Elapsed Time Completion Time\r\n------- ---- -- ---------- ----------- ------------ -------------------\r\n24391   Incr 0  1.18M      DISK        00:00:00     20\/04\/2020 23:54:01\r\n        BP Key: 24394   Status: AVAILABLE  Compressed: NO  Tag: BKP-DBF-TO-DISK\r\n        Piece Name: \/tmp\/a3uu5e0p_1_1\r\n  List of Datafiles in backup set 24391\r\n  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name\r\n  ---- -- ---- ---------- ------------------- ----------- ------ ----\r\n  12   0  Incr 2013972    20\/04\/2020 23:54:01              NO    \/u01\/app\/oracle\/oradata\/ORCL18C\/ORCL18P\/users01.dbf\r\n\r\nBS Key  Type LV Size       Device Type Elapsed Time Completion Time\r\n------- ---- -- ---------- ----------- ------------ -------------------\r\n24430   Incr 1  256.00K    SBT_TAPE    00:00:00     20\/04\/2020 23:55:24\r\n        BP Key: 24431   Status: AVAILABLE  Compressed: NO  Tag: TAG20200420T235524\r\n        Handle: ORCL18C_a5uu5e3c_1_1   Media: Recovery Appliance (ZDLRAS1)\r\n  List of Datafiles in backup set 24430\r\n  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name\r\n  ---- -- ---- ---------- ------------------- ----------- ------ ----\r\n  12   1  Incr 2014089    20\/04\/2020 23:55:24              NO    \/u01\/app\/oracle\/oradata\/ORCL18C\/ORCL18P\/users01.dbf\r\n\r\nBS Key  Type LV Size       Device Type Elapsed Time Completion Time\r\n------- ---- -- ---------- ----------- ------------ -------------------\r\n24511   Incr 1  256.00K    SBT_TAPE    00:00:01     21\/04\/2020 00:04:02\r\n        BP Key: 24512   Status: AVAILABLE  Compressed: NO  Tag: TAG20200421T000400\r\n        Handle: ORCL18C_a7uu5ejh_1_1   Media: Recovery Appliance (ZDLRAS1)\r\n  List of Datafiles in backup set 24511\r\n  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name\r\n  ---- -- ---- ---------- ------------------- ----------- ------ ----\r\n  12   1  Incr 2021757    21\/04\/2020 00:04:01              NO    \/u01\/app\/oracle\/oradata\/ORCL18C\/ORCL18P\/users01.dbf\r\n\r\nBS Key  Type LV Size       Device Type Elapsed Time Completion Time\r\n------- ---- -- ---------- ----------- ------------ -------------------\r\n24604   Incr 1  256.00K    SBT_TAPE    00:00:02     21\/04\/2020 00:07:15\r\n        BP Key: 24605   Status: AVAILABLE  Compressed: NO  Tag: TAG20200421T000713\r\n        Handle: ORCL18C_a9uu5eph_1_1   Media: Recovery Appliance (ZDLRAS1)\r\n  List of Datafiles in backup set 24604\r\n  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name\r\n  ---- -- ---- ---------- ------------------- ----------- ------ ----\r\n  12   1  Incr 2021959    21\/04\/2020 00:07:13              NO    \/u01\/app\/oracle\/oradata\/ORCL18C\/ORCL18P\/users01.dbf\r\n\r\nBS Key  Type LV Size       Device Type Elapsed Time Completion Time\r\n------- ---- -- ---------- ----------- ------------ -------------------\r\n24814   Incr 1  40.00K     SBT_TAPE    00:00:03     21\/04\/2020 00:12:20\r\n        BP Key: 24815   Status: AVAILABLE  Compressed: YES  Tag: TAG20200420T235158\r\n        Handle: VB$_1891149551_24813I   Media:\r\n  List of Datafiles in backup set 24814\r\n  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name\r\n  ---- -- ---- ---------- ------------------- ----------- ------ ----\r\n  12   1  Incr 2025613    21\/04\/2020 00:12:17              NO    \/u01\/app\/oracle\/oradata\/ORCL18C\/ORCL18P\/users01.dbf\r\n\r\nBS Key  Type LV Size       Device Type Elapsed Time Completion Time\r\n------- ---- -- ---------- ----------- ------------ -------------------\r\n24818   Incr 0  40.00K     SBT_TAPE    00:00:03     21\/04\/2020 00:12:20\r\n        BP Key: 24819   Status: AVAILABLE  Compressed: YES  Tag: TAG20200420T235158\r\n        Handle: VB$_1891149551_24813_12   Media:\r\n  List of Datafiles in backup set 24818\r\n  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name\r\n  ---- -- ---- ---------- ------------------- ----------- ------ ----\r\n  12   0  Incr 2025613    21\/04\/2020 00:12:17              NO    \/u01\/app\/oracle\/oradata\/ORCL18C\/ORCL18P\/users01.dbf\r\n\r\nRMAN><\/pre>\n
And inside of ZDLRA, we can see that task previously in ORDERING_WAIT finished (check the BP_KEY column):<\/p>\n
SQL> select TASK_ID, TASK_TYPE, STATE, WAITING_ON, DB_KEY, DB_UNIQUE_NAME, CREATION_TIME, COMPLETION_TIME, ERROR_COUNT, INTERRUPT_COUNT, BP_KEY,BS_KEY,DF_KEY,VB_KEY from rasys.ra_task where task_id IN (40203,40210,40215);\r\n\r\n   TASK_ID TASK_TYPE       STATE                     WAITING_ON     DB_KEY DB_UNIQUE_NAME                 CREATION_TIME                       COMPLETION_TIME                     ERROR_COUNT INTERRUPT_COUNT     BP_KEY     BS_KEY     DF_KEY     VB_KEY\r\n---------- --------------- ------------------------- ---------- ---------- ------------------------------ ----------------------------------- ----------------------------------- ----------- --------------- ---------- ---------- ---------- ----------\r\n     40203 INDEX_BACKUP    COMPLETED                                 15993 ORCL18C                        20-APR-20 11.55.26.779191 PM +02:00 21-APR-20 12.13.09.253916 AM +02:00           0               1      24431\r\n     40210 INDEX_BACKUP    COMPLETED                                 15993 ORCL18C                        21-APR-20 12.04.05.207342 AM +02:00 21-APR-20 12.13.21.663869 AM +02:00           0               1      24512\r\n     40215 INDEX_BACKUP    COMPLETED                                 15993 ORCL18C                        21-APR-20 12.07.18.140618 AM +02:00 21-APR-20 12.13.42.179087 AM +02:00           0               1      24605\r\n\r\nSQL><\/pre>\n
And if we check for the BLOCKS for this datafile, we can see that was registered new that are higher with the last full backup before the error, and they go until the last backup made:<\/p>\n
SQL> select * from blocks where df_key = 16026 and scn >= 2013810 order by scn, chunkno ;\r\n\r\n    DF_KEY    BLOCKNO        SCN     CKP_ID    CHUNKNO    COFFSET       USED  DBINC_KEY     ENDBLK\r\n---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------\r\n     16026          0    2013810    2013810      25601       8192      24576      15994\r\n     16026          1    2013810    2013810      25601      33121        338      15994\r\n     16026        142    2021942    2025613      26625      32788        414      15994\r\n     16026          1    2025613    2025613      26625      33202        336      15994\r\n     16026          0    2025613    2025613      26625       8192      24576      15994\r\n\r\nSQL><\/pre>\n
And we can see that now exists PLANS for the backupset of virtual full backup that exists before the error (SCN 2013810) and after we fix (SCN 2025613):<\/p>\n
SQL> select VB_KEY, DF_KEY, CKP_SCN, SRCBP_KEY, VCBP_KEY from vbdf where CKP_SCN >= 2013810 and DF_KEY = 16026;\r\n\r\n    VB_KEY     DF_KEY    CKP_SCN  SRCBP_KEY   VCBP_KEY\r\n---------- ---------- ---------- ---------- ----------\r\n     24354      16026    2013810      24293      24356\r\n     24813      16026    2025613      24706      24815\r\n\r\nSQL>\r\nSQL> select * from plans_details where VB_KEY IN (24354,24813) order by VB_KEY,BLOCKNO;\r\n\r\n    DF_KEY       TYPE     VB_KEY    BLKRANK    BLOCKNO    CHUNKNO    NUMBLKS    COFFSET   NUMBYTES\r\n---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------\r\n     16026          1      24354          1          0      25601          1       8192      24576\r\n     16026          1      24354          1          2      16385          2      32788        256\r\n     16026          1      24354          1          4      14337        132      33045      14000\r\n     16026          1      24354          1        136      16385          3      33044        553\r\n     16026          1      24354          1        139      23553          1      32788        327\r\n     16026          1      24354          1        140      16385          2      33786        264\r\n     16026          1      24354          1        142      25601          1      32788        333\r\n     16026          1      24354          1        143      16385          1      34182        132\r\n     16026          1      24354          1        191      14337          3      47045        252\r\n     16026          1      24354          1 4294967295      25601          1      33121        338\r\n     16026          1      24813          1          0      26625          1       8192      24576\r\n     16026          1      24813          1          2      16385          2      32788        256\r\n     16026          1      24813          1          4      14337        132      33045      14000\r\n     16026          1      24813          1        136      16385          3      33044        553\r\n     16026          1      24813          1        139      23553          1      32788        327\r\n     16026          1      24813          1        140      16385          2      33786        264\r\n     16026          1      24813          1        142      26625          1      32788        414\r\n     16026          1      24813          1        143      16385          1      34182        132\r\n     16026          1      24813          1        191      14337          3      47045        252\r\n     16026          1      24813          1 4294967295      26625          1      33202        336\r\n\r\n20 rows selected.\r\n\r\nSQL><\/pre>\n
So, this means that the incremental backup that we made with FOR RECOVERY OF TAG was ingested and used to fix the needed gap.<\/p>\n
And if we try to recover the datafile 12, we can do without a problem. Check that the used backup to recover was the last virtual full backup generated from the \u201cRECOVERY OF TAG\u201d command:<\/p>\n
RMAN> run{\r\n2> ALTER PLUGGABLE DATABASE ORCL18P CLOSE IMMEDIATE INSTANCES=ALL;\r\n3> RESTORE DATAFILE 12;\r\n4> RECOVER DATAFILE 12;\r\n5> ALTER PLUGGABLE DATABASE ORCL18P OPEN INSTANCES=ALL;\r\n6> }\r\n\r\nStatement processed\r\nstarting full resync of recovery catalog\r\nfull resync complete\r\n\r\nStarting restore at 21\/04\/2020 00:57:53\r\nallocated channel: ORA_DISK_1\r\nchannel ORA_DISK_1: SID=88 device type=DISK\r\nallocated channel: ORA_SBT_TAPE_1\r\nchannel ORA_SBT_TAPE_1: SID=70 device type=SBT_TAPE\r\nchannel ORA_SBT_TAPE_1: RA Library (ZDLRAS1) SID=A3C1D44670B428B0E053010310AC5DA9\r\n\r\nchannel ORA_SBT_TAPE_1: starting datafile backup set restore\r\nchannel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set\r\nchannel ORA_SBT_TAPE_1: restoring datafile 00012 to \/u01\/app\/oracle\/oradata\/ORCL18C\/ORCL18P\/users01.dbf\r\nchannel ORA_SBT_TAPE_1: reading from backup piece VB$_1891149551_24813_12\r\nchannel ORA_SBT_TAPE_1: piece handle=VB$_1891149551_24813_12 tag=TAG20200420T235158\r\nchannel ORA_SBT_TAPE_1: restored backup piece 1\r\nchannel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:25\r\nFinished restore at 21\/04\/2020 00:58:21\r\n\r\nStarting recover at 21\/04\/2020 00:58:21\r\nusing channel ORA_DISK_1\r\nusing channel ORA_SBT_TAPE_1\r\n\r\nstarting media recovery\r\nmedia recovery complete, elapsed time: 00:00:00\r\n\r\nFinished recover at 21\/04\/2020 00:58:22\r\n\r\nStatement processed\r\nstarting full resync of recovery catalog\r\nfull resync complete\r\n\r\nRMAN><\/pre>\n
Unfortunately, backup tags for ZDLRA can be tricky when you directly specify it during the backup phase. They can be the same and the usage \u201cFOR TAG\u201d can be more difficult to define. One option is to merge and execute the command BACKUP CUMULATIVE INCREMENTAL LEVEL 1 DEVICE TYPE SBT FOR RECOVER OF TAG \u2018<TAG>\u2019 DATAFILE XX.<\/p>\n
Doing this, the command will pick up all the blocks from the last full backup that have the tag that you defined. The result is the same:<\/p>\n
RMAN> BACKUP CUMULATIVE INCREMENTAL LEVEL 1 DEVICE TYPE SBT FOR RECOVER OF TAG 'TAG20200419T232006' DATAFILE 12;\r\n\r\nStarting backup at 19\/04\/2020 23:38:44\r\nusing channel ORA_SBT_TAPE_1\r\nchannel ORA_SBT_TAPE_1: starting incremental level 1 datafile backup set\r\nchannel ORA_SBT_TAPE_1: specifying datafile(s) in backup set\r\ninput datafile file number=00012 name=\/u01\/app\/oracle\/oradata\/ORCL18C\/ORCL18P\/users01.dbf\r\nchannel ORA_SBT_TAPE_1: starting piece 1 at 19\/04\/2020 23:38:44\r\nchannel ORA_SBT_TAPE_1: finished piece 1 at 19\/04\/2020 23:38:59\r\npiece handle=ORCL18C_97uu2oo4_1_1 tag=TAG20200419T232006 comment=API Version 2.0,MMS Version 12.2.0.2\r\nchannel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:15\r\nFinished backup at 19\/04\/2020 23:38:59\r\n\r\nStarting Control File and SPFILE Autobackup at 19\/04\/2020 23:39:00\r\npiece handle=c-558466555-20200419-04 comment=API Version 2.0,MMS Version 12.2.0.2\r\nFinished Control File and SPFILE Autobackup at 19\/04\/2020 23:39:16\r\n\r\nRMAN><\/pre>\n
BACKUP FULL<\/h3>\n
The other option to solve the ORDERING_WAIT is to do a full backup of the datafile. With this, all the blocks are read and ingested at ZDLRA.<\/p>\n
The procedure is the same as above and the result the same. The only point is that for huge files this can take a long time (since it is full) and the above incremental approach can be more suitable.<\/p>\n
Monitoring the Tasks<\/h2>\n
So, as you can see above, the ORDERING_WAIT state can have a lot of collateral effects for you ZDLRA. Unfortunately, this not generates incidents that are reported, you need to write a query to check this directly at ra_task<\/em> table.<\/p>\n
Whatever the method that you choose to solve the problem (RECOVERY OF TAG, or FULL BACKUP) always verify if the new virtual full backup is generated. It is a good practice to do this to avoid errors and do a double cross-check over the tasks.<\/p>\n
It is a simple query and a simple monitoring thing to do. But this will avoid a huge problem.<\/p>\n
Reference:<\/h2>\n