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’t care where it is the backup. By definition, incremental backup it is from the last backup, whatever or wherever it is).
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’t have the blocks that are between SCN 4444 and 5555 (look the yellow block, backup exists just outside of ZDLRA).
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 … FOR RECOVER OF TAG ‘<TAG>’ command. I will show you below how to do that.
How this occurs and how to solve
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.
In this scenario, I will use one database (number 12 – tablespace users for one PDB). So, first, check the backups for datafile 12:
RMAN> list backup of datafile 12 completed after "sysdate - 20/1440";
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
24283 Incr 1 40.00K SBT_TAPE 00:00:04 20/04/2020 23:49:26
BP Key: 24284 Status: AVAILABLE Compressed: YES Tag: TAG20200420T234921
Handle: VB$_1891149551_24282I Media:
List of Datafiles in backup set 24283
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
12 1 Incr 2013573 20/04/2020 23:49:22 NO /u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
24287 Incr 0 40.00K SBT_TAPE 00:00:04 20/04/2020 23:49:26
BP Key: 24288 Status: AVAILABLE Compressed: YES Tag: TAG20200420T234921
Handle: VB$_1891149551_24282_12 Media:
List of Datafiles in backup set 24287
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
12 0 Incr 2013573 20/04/2020 23:49:22 NO /u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf
RMAN>
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.
And if I do a new incremental backup, ZDLRA generates a new virtual full backup:
RMAN> BACKUP INCREMENTAL LEVEL 1 DEVICE TYPE SBT FILESPERSET 1 DATAFILE 12;
Starting backup at 20/04/2020 23:51:58
using channel ORA_SBT_TAPE_1
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=00012 name=/u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf
channel ORA_SBT_TAPE_1: starting piece 1 at 20/04/2020 23:51:59
channel ORA_SBT_TAPE_1: finished piece 1 at 20/04/2020 23:52:02
piece handle=ORCL18C_a1uu5dsv_1_1 tag=TAG20200420T235158 comment=API Version 2.0,MMS Version 12.2.0.2
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:03
Finished backup at 20/04/2020 23:52:02
Starting Control File and SPFILE Autobackup at 20/04/2020 23:52:02
piece handle=c-558466555-20200420-0b comment=API Version 2.0,MMS Version 12.2.0.2
Finished Control File and SPFILE Autobackup at 20/04/2020 23:52:10
RMAN> list backup of datafile 12 completed after "sysdate - 20/1440";
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
24283 Incr 1 40.00K SBT_TAPE 00:00:04 20/04/2020 23:49:26
BP Key: 24284 Status: AVAILABLE Compressed: YES Tag: TAG20200420T234921
Handle: VB$_1891149551_24282I Media:
List of Datafiles in backup set 24283
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
12 1 Incr 2013573 20/04/2020 23:49:22 NO /u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
24287 Incr 0 40.00K SBT_TAPE 00:00:04 20/04/2020 23:49:26
BP Key: 24288 Status: AVAILABLE Compressed: YES Tag: TAG20200420T234921
Handle: VB$_1891149551_24282_12 Media:
List of Datafiles in backup set 24287
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
12 0 Incr 2013573 20/04/2020 23:49:22 NO /u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
24355 Incr 1 40.00K SBT_TAPE 00:00:03 20/04/2020 23:52:02
BP Key: 24356 Status: AVAILABLE Compressed: YES Tag: TAG20200420T235158
Handle: VB$_1891149551_24354I Media:
List of Datafiles in backup set 24355
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
12 1 Incr 2013810 20/04/2020 23:51:59 NO /u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
24359 Incr 0 40.00K SBT_TAPE 00:00:03 20/04/2020 23:52:02
BP Key: 24360 Status: AVAILABLE Compressed: YES Tag: TAG20200420T235158
Handle: VB$_1891149551_24354_12 Media:
List of Datafiles in backup set 24359
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
12 0 Incr 2013810 20/04/2020 23:51:59 NO /u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf
RMAN>
Simulating the error
Just to remember that this new incremental have all scn from 2013573 until 2013810.
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:
RMAN> BACKUP INCREMENTAL LEVEL 0 DEVICE TYPE disk format '/tmp/%U' DATAFILE 12 TAG 'BKP-DBF-TO-DISK';
Starting backup at 20/04/2020 23:54:01
released channel: ORA_SBT_TAPE_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=65 device type=DISK
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00012 name=/u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf
channel ORA_DISK_1: starting piece 1 at 20/04/2020 23:54:01
channel ORA_DISK_1: finished piece 1 at 20/04/2020 23:54:02
piece handle=/tmp/a3uu5e0p_1_1 tag=BKP-DBF-TO-DISK comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20/04/2020 23:54:02
Starting Control File and SPFILE Autobackup at 20/04/2020 23:54:02
piece handle=/u01/app/oracle/oradata/ORCL18C/autobackup/2020_04_20/o1_mf_s_1038268443_h9w6hwht_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 20/04/2020 23:54:10
RMAN> list backup tag = 'BKP-DBF-TO-DISK';
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
24391 Incr 0 1.18M DISK 00:00:00 20/04/2020 23:54:01
BP Key: 24394 Status: AVAILABLE Compressed: NO Tag: BKP-DBF-TO-DISK
Piece Name: /tmp/a3uu5e0p_1_1
List of Datafiles in backup set 24391
Container ID: 3, PDB Name: ORCL18P
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
12 0 Incr 2013972 20/04/2020 23:54:01 NO /u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf
RMAN>
Now, if I try to execute the incremental level 1, the ingested backup does not generate a new virtual backup:
RMAN> BACKUP INCREMENTAL LEVEL 1 DEVICE TYPE SBT FILESPERSET 1 DATAFILE 12;
Starting backup at 20/04/2020 23:55:23
released channel: ORA_DISK_1
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=65 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: RA Library (ZDLRAS1) SID=A3C0F4C16DAA11FAE053010310ACC1C4
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=00012 name=/u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf
channel ORA_SBT_TAPE_1: starting piece 1 at 20/04/2020 23:55:24
channel ORA_SBT_TAPE_1: finished piece 1 at 20/04/2020 23:55:27
piece handle=ORCL18C_a5uu5e3c_1_1 tag=TAG20200420T235524 comment=API Version 2.0,MMS Version 12.2.0.2
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:03
Finished backup at 20/04/2020 23:55:27
Starting Control File and SPFILE Autobackup at 20/04/2020 23:55:27
piece handle=c-558466555-20200420-0d comment=API Version 2.0,MMS Version 12.2.0.2
Finished Control File and SPFILE Autobackup at 20/04/2020 23:55:35
RMAN> list backup of datafile 12 completed after "sysdate - 20/1440";
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
24283 Incr 1 40.00K SBT_TAPE 00:00:04 20/04/2020 23:49:26
BP Key: 24284 Status: AVAILABLE Compressed: YES Tag: TAG20200420T234921
Handle: VB$_1891149551_24282I Media:
List of Datafiles in backup set 24283
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
12 1 Incr 2013573 20/04/2020 23:49:22 NO /u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
24287 Incr 0 40.00K SBT_TAPE 00:00:04 20/04/2020 23:49:26
BP Key: 24288 Status: AVAILABLE Compressed: YES Tag: TAG20200420T234921
Handle: VB$_1891149551_24282_12 Media:
List of Datafiles in backup set 24287
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
12 0 Incr 2013573 20/04/2020 23:49:22 NO /u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
24355 Incr 1 40.00K SBT_TAPE 00:00:03 20/04/2020 23:52:02
BP Key: 24356 Status: AVAILABLE Compressed: YES Tag: TAG20200420T235158
Handle: VB$_1891149551_24354I Media:
List of Datafiles in backup set 24355
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
12 1 Incr 2013810 20/04/2020 23:51:59 NO /u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
24359 Incr 0 40.00K SBT_TAPE 00:00:03 20/04/2020 23:52:02
BP Key: 24360 Status: AVAILABLE Compressed: YES Tag: TAG20200420T235158
Handle: VB$_1891149551_24354_12 Media:
List of Datafiles in backup set 24359
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
12 0 Incr 2013810 20/04/2020 23:51:59 NO /u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
24391 Incr 0 1.18M DISK 00:00:00 20/04/2020 23:54:01
BP Key: 24394 Status: AVAILABLE Compressed: NO Tag: BKP-DBF-TO-DISK
Piece Name: /tmp/a3uu5e0p_1_1
List of Datafiles in backup set 24391
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
12 0 Incr 2013972 20/04/2020 23:54:01 NO /u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
24430 Incr 1 256.00K SBT_TAPE 00:00:00 20/04/2020 23:55:24
BP Key: 24431 Status: AVAILABLE Compressed: NO Tag: TAG20200420T235524
Handle: ORCL18C_a5uu5e3c_1_1 Media: Recovery Appliance (ZDLRAS1)
List of Datafiles in backup set 24430
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
12 1 Incr 2014089 20/04/2020 23:55:24 NO /u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf
RMAN>
Look above that the last incremental backup not generated a new level 0, it is the backupset 24430 (and backuppiece 24431).
Task inside ZDLRA
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 and check using the state column..
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;
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
---------- --------------- ------------------------- ---------- ---------- ------------------------------ ----------------------------------- ----------- --------------- ---------- ---------- ---------- ----------
40203 INDEX_BACKUP ORDERING_WAIT 15993 ORCL18C 20-APR-20 11.55.26.779191 PM +02:00 0 1 24431
SQL>
And there is no incident for this error or state:
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;
no rows selected
SQL>
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.
But even with tasks in this state, you will not be unprotected, the backup is there and can be restored.
ZDLRA Internals
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 – same that you find with list backupset inside rman):
SQL> select bs_key, db_key, pdb_key from bp where bp_key = 24431;
BS_KEY DB_KEY PDB_KEY
---------- ---------- ----------
24430 15993 16000
SQL> select * from rc_database where db_key = 15993;
DB_KEY DBINC_KEY DBID NAME RESETLOGS_CHANGE# RESETLOGS FINAL_CHANGE#
---------- ---------- ---------- -------- ----------------- --------- -------------
15993 15994 558466555 ORCL18C 1477662 11-AUG-19
SQL> select df_key from df where dbinc_key = 15994 and file# = 12;
DF_KEY
----------
16026
SQL> select bdf_key, ckp_scn from bdf where bs_key = 24430;
BDF_KEY CKP_SCN
---------- ----------
24432 2014089
SQL>
###################################################################
RMAN> list backupset 24430;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
24430 Incr 1 256.00K SBT_TAPE 00:00:00 20/04/2020 23:55:24
BP Key: 24431 Status: AVAILABLE Compressed: NO Tag: TAG20200420T235524
Handle: ORCL18C_a5uu5e3c_1_1 Media: Recovery Appliance (ZDLRAS1)
List of Datafiles in backup set 24430
Container ID: 3, PDB Name: ORCL18P
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
12 1 Incr 2014089 20/04/2020 23:55:24 NO /u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf
RMAN>
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.
But if we go to the BLOCKS tables (ZDLRA table that stores the index – and “more or less” 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 about this.
SQL> select * from blocks where df_key = 16026 and scn >= 2014089;
no rows selected
SQL>
And if we check for SCN 2013972 (there came from backup was put in disk), nothing too (as expected):
SQL> select * from blocks where df_key = 16026 and scn >= 2013972;
no rows selected
SQL>
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:
SQL> select * from blocks where df_key = 16026 and scn >= 2013810 order by scn, chunkno ;
DF_KEY BLOCKNO SCN CKP_ID CHUNKNO COFFSET USED DBINC_KEY ENDBLK
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
16026 1 2013810 2013810 25601 33121 338 15994
16026 0 2013810 2013810 25601 8192 24576 15994
SQL>
And there is just PLANS for this virtual backup (nothing generated for the next existing backups):
SQL> select VB_KEY, DF_KEY, CKP_SCN, SRCBP_KEY, VCBP_KEY from vbdf where CKP_SCN >= 2013810 and df_key = 16026;
VB_KEY DF_KEY CKP_SCN SRCBP_KEY VCBP_KEY
---------- ---------- ---------- ---------- ----------
24354 16026 2013810 24293 24356
SQL> select * from plans_details where VB_KEY = 24354;
DF_KEY TYPE VB_KEY BLKRANK BLOCKNO CHUNKNO NUMBLKS COFFSET NUMBYTES
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
16026 1 24354 1 0 25601 1 8192 24576
16026 1 24354 1 2 16385 2 32788 256
16026 1 24354 1 4 14337 132 33045 14000
16026 1 24354 1 136 16385 3 33044 553
16026 1 24354 1 139 23553 1 32788 327
16026 1 24354 1 140 16385 2 33786 264
16026 1 24354 1 142 25601 1 32788 333
16026 1 24354 1 143 16385 1 34182 132
16026 1 24354 1 191 14337 3 47045 252
16026 1 24354 1 4294967295 25601 1 33121 338
10 rows selected.
SQL>
As you can figure out, ZDLRA can’t fill the gap to create the virtual full backup.
Recurring error
If you not solve the problem, and continue to ingest backups, the task will remain in ORDERING_WAIT:
RMAN> BACKUP INCREMENTAL LEVEL 1 DEVICE TYPE SBT FILESPERSET 1 DATAFILE 12;
Starting backup at 21/04/2020 00:04:00
using channel ORA_SBT_TAPE_1
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=00012 name=/u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf
channel ORA_SBT_TAPE_1: starting piece 1 at 21/04/2020 00:04:01
channel ORA_SBT_TAPE_1: finished piece 1 at 21/04/2020 00:04:04
piece handle=ORCL18C_a7uu5ejh_1_1 tag=TAG20200421T000400 comment=API Version 2.0,MMS Version 12.2.0.2
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:03
Finished backup at 21/04/2020 00:04:04
Starting Control File and SPFILE Autobackup at 21/04/2020 00:04:04
piece handle=c-558466555-20200421-00 comment=API Version 2.0,MMS Version 12.2.0.2
Finished Control File and SPFILE Autobackup at 21/04/2020 00:04:13
RMAN> list backup of datafile 12 completed after "sysdate - 5/1440";
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
24511 Incr 1 256.00K SBT_TAPE 00:00:01 21/04/2020 00:04:02
BP Key: 24512 Status: AVAILABLE Compressed: NO Tag: TAG20200421T000400
Handle: ORCL18C_a7uu5ejh_1_1 Media: Recovery Appliance (ZDLRAS1)
List of Datafiles in backup set 24511
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
12 1 Incr 2021757 21/04/2020 00:04:01 NO /u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf
RMAN>
####################################
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;
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
---------- --------------- ------------------------- ---------- ---------- ------------------------------ ----------------------------------- ----------- --------------- ---------- ---------- ---------- ----------
40203 INDEX_BACKUP ORDERING_WAIT 15993 ORCL18C 20-APR-20 11.55.26.779191 PM +02:00 0 1 24431
40210 INDEX_BACKUP ORDERING_WAIT 15993 ORCL18C 21-APR-20 12.04.05.207342 AM +02:00 0 1 24512
SQL>
Even if you try to do a cumulative incremental backup, the problem will be the same:
RMAN> BACKUP CUMULATIVE INCREMENTAL LEVEL 1 DEVICE TYPE SBT FILESPERSET 1 DATAFILE 12;
Starting backup at 21/04/2020 00:07:13
using channel ORA_SBT_TAPE_1
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=00012 name=/u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf
channel ORA_SBT_TAPE_1: starting piece 1 at 21/04/2020 00:07:13
channel ORA_SBT_TAPE_1: finished piece 1 at 21/04/2020 00:07:20
piece handle=ORCL18C_a9uu5eph_1_1 tag=TAG20200421T000713 comment=API Version 2.0,MMS Version 12.2.0.2
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:07
Finished backup at 21/04/2020 00:07:20
Starting Control File and SPFILE Autobackup at 21/04/2020 00:07:20
piece handle=c-558466555-20200421-01 comment=API Version 2.0,MMS Version 12.2.0.2
Finished Control File and SPFILE Autobackup at 21/04/2020 00:07:28
RMAN> list backup of datafile 12 completed after "sysdate - 2/1440";
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
24604 Incr 1 256.00K SBT_TAPE 00:00:02 21/04/2020 00:07:15
BP Key: 24605 Status: AVAILABLE Compressed: NO Tag: TAG20200421T000713
Handle: ORCL18C_a9uu5eph_1_1 Media: Recovery Appliance (ZDLRAS1)
List of Datafiles in backup set 24604
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
12 1 Incr 2021959 21/04/2020 00:07:13 NO /u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf
RMAN>
#############################################
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;
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
---------- --------------- ------------------------- ---------- ---------- ------------------------------ ----------------------------------- ----------- --------------- ---------- ---------- ---------- ----------
40203 INDEX_BACKUP ORDERING_WAIT 15993 ORCL18C 20-APR-20 11.55.26.779191 PM +02:00 0 1 24431
40210 INDEX_BACKUP ORDERING_WAIT 15993 ORCL18C 21-APR-20 12.04.05.207342 AM +02:00 0 1 24512
40215 INDEX_BACKUP ORDERING_WAIT 15993 ORCL18C 21-APR-20 12.07.18.140618 AM +02:00 0 1 24605
SQL>
Solving ORDERING_WAIT
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.
FOR RECOVER OF TAG
The first is to use the command “BACKUP [CUMULATIVE] INCREMENTAL LEVEL 1 … FOR RECOVER OF TAG ‘<TAG>’”. The idea here is to create one incremental backup that recovers since one specific tag. You can check the documentation for more details if you want, it exists since Oracle 10g.
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.
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:
RMAN> BACKUP INCREMENTAL LEVEL 1 DEVICE TYPE SBT FOR RECOVER OF TAG 'TAG20200420T235158' DATAFILE 12;
Starting backup at 21/04/2020 00:12:17
using channel ORA_SBT_TAPE_1
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=00012 name=/u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf
channel ORA_SBT_TAPE_1: starting piece 1 at 21/04/2020 00:12:17
channel ORA_SBT_TAPE_1: finished piece 1 at 21/04/2020 00:12:20
piece handle=ORCL18C_abuu5f31_1_1 tag=TAG20200420T235158 comment=API Version 2.0,MMS Version 12.2.0.2
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:03
Finished backup at 21/04/2020 00:12:20
Starting Control File and SPFILE Autobackup at 21/04/2020 00:12:20
piece handle=c-558466555-20200421-02 comment=API Version 2.0,MMS Version 12.2.0.2
Finished Control File and SPFILE Autobackup at 21/04/2020 00:12:28
RMAN>
And we can see that a new virtual full backup was created with this incremental backup (look the last 5 backupsets):
RMAN> list backup of datafile 12 completed after "sysdate - 40/1440";
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
24283 Incr 1 40.00K SBT_TAPE 00:00:04 20/04/2020 23:49:26
BP Key: 24284 Status: AVAILABLE Compressed: YES Tag: TAG20200420T234921
Handle: VB$_1891149551_24282I Media:
List of Datafiles in backup set 24283
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
12 1 Incr 2013573 20/04/2020 23:49:22 NO /u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
24287 Incr 0 40.00K SBT_TAPE 00:00:04 20/04/2020 23:49:26
BP Key: 24288 Status: AVAILABLE Compressed: YES Tag: TAG20200420T234921
Handle: VB$_1891149551_24282_12 Media:
List of Datafiles in backup set 24287
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
12 0 Incr 2013573 20/04/2020 23:49:22 NO /u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
24355 Incr 1 40.00K SBT_TAPE 00:00:03 20/04/2020 23:52:02
BP Key: 24356 Status: AVAILABLE Compressed: YES Tag: TAG20200420T235158
Handle: VB$_1891149551_24354I Media:
List of Datafiles in backup set 24355
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
12 1 Incr 2013810 20/04/2020 23:51:59 NO /u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
24359 Incr 0 40.00K SBT_TAPE 00:00:03 20/04/2020 23:52:02
BP Key: 24360 Status: AVAILABLE Compressed: YES Tag: TAG20200420T235158
Handle: VB$_1891149551_24354_12 Media:
List of Datafiles in backup set 24359
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
12 0 Incr 2013810 20/04/2020 23:51:59 NO /u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
24391 Incr 0 1.18M DISK 00:00:00 20/04/2020 23:54:01
BP Key: 24394 Status: AVAILABLE Compressed: NO Tag: BKP-DBF-TO-DISK
Piece Name: /tmp/a3uu5e0p_1_1
List of Datafiles in backup set 24391
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
12 0 Incr 2013972 20/04/2020 23:54:01 NO /u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
24430 Incr 1 256.00K SBT_TAPE 00:00:00 20/04/2020 23:55:24
BP Key: 24431 Status: AVAILABLE Compressed: NO Tag: TAG20200420T235524
Handle: ORCL18C_a5uu5e3c_1_1 Media: Recovery Appliance (ZDLRAS1)
List of Datafiles in backup set 24430
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
12 1 Incr 2014089 20/04/2020 23:55:24 NO /u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
24511 Incr 1 256.00K SBT_TAPE 00:00:01 21/04/2020 00:04:02
BP Key: 24512 Status: AVAILABLE Compressed: NO Tag: TAG20200421T000400
Handle: ORCL18C_a7uu5ejh_1_1 Media: Recovery Appliance (ZDLRAS1)
List of Datafiles in backup set 24511
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
12 1 Incr 2021757 21/04/2020 00:04:01 NO /u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
24604 Incr 1 256.00K SBT_TAPE 00:00:02 21/04/2020 00:07:15
BP Key: 24605 Status: AVAILABLE Compressed: NO Tag: TAG20200421T000713
Handle: ORCL18C_a9uu5eph_1_1 Media: Recovery Appliance (ZDLRAS1)
List of Datafiles in backup set 24604
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
12 1 Incr 2021959 21/04/2020 00:07:13 NO /u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
24814 Incr 1 40.00K SBT_TAPE 00:00:03 21/04/2020 00:12:20
BP Key: 24815 Status: AVAILABLE Compressed: YES Tag: TAG20200420T235158
Handle: VB$_1891149551_24813I Media:
List of Datafiles in backup set 24814
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
12 1 Incr 2025613 21/04/2020 00:12:17 NO /u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
24818 Incr 0 40.00K SBT_TAPE 00:00:03 21/04/2020 00:12:20
BP Key: 24819 Status: AVAILABLE Compressed: YES Tag: TAG20200420T235158
Handle: VB$_1891149551_24813_12 Media:
List of Datafiles in backup set 24818
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
12 0 Incr 2025613 21/04/2020 00:12:17 NO /u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf
RMAN>
And inside of ZDLRA, we can see that task previously in ORDERING_WAIT finished (check the BP_KEY column):
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);
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
---------- --------------- ------------------------- ---------- ---------- ------------------------------ ----------------------------------- ----------------------------------- ----------- --------------- ---------- ---------- ---------- ----------
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
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
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
SQL>
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
SQL> select * from blocks where df_key = 16026 and scn >= 2013810 order by scn, chunkno ;
DF_KEY BLOCKNO SCN CKP_ID CHUNKNO COFFSET USED DBINC_KEY ENDBLK
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
16026 0 2013810 2013810 25601 8192 24576 15994
16026 1 2013810 2013810 25601 33121 338 15994
16026 142 2021942 2025613 26625 32788 414 15994
16026 1 2025613 2025613 26625 33202 336 15994
16026 0 2025613 2025613 26625 8192 24576 15994
SQL>
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):
SQL> select VB_KEY, DF_KEY, CKP_SCN, SRCBP_KEY, VCBP_KEY from vbdf where CKP_SCN >= 2013810 and DF_KEY = 16026;
VB_KEY DF_KEY CKP_SCN SRCBP_KEY VCBP_KEY
---------- ---------- ---------- ---------- ----------
24354 16026 2013810 24293 24356
24813 16026 2025613 24706 24815
SQL>
SQL> select * from plans_details where VB_KEY IN (24354,24813) order by VB_KEY,BLOCKNO;
DF_KEY TYPE VB_KEY BLKRANK BLOCKNO CHUNKNO NUMBLKS COFFSET NUMBYTES
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
16026 1 24354 1 0 25601 1 8192 24576
16026 1 24354 1 2 16385 2 32788 256
16026 1 24354 1 4 14337 132 33045 14000
16026 1 24354 1 136 16385 3 33044 553
16026 1 24354 1 139 23553 1 32788 327
16026 1 24354 1 140 16385 2 33786 264
16026 1 24354 1 142 25601 1 32788 333
16026 1 24354 1 143 16385 1 34182 132
16026 1 24354 1 191 14337 3 47045 252
16026 1 24354 1 4294967295 25601 1 33121 338
16026 1 24813 1 0 26625 1 8192 24576
16026 1 24813 1 2 16385 2 32788 256
16026 1 24813 1 4 14337 132 33045 14000
16026 1 24813 1 136 16385 3 33044 553
16026 1 24813 1 139 23553 1 32788 327
16026 1 24813 1 140 16385 2 33786 264
16026 1 24813 1 142 26625 1 32788 414
16026 1 24813 1 143 16385 1 34182 132
16026 1 24813 1 191 14337 3 47045 252
16026 1 24813 1 4294967295 26625 1 33202 336
20 rows selected.
SQL>
So, this means that the incremental backup that we made with FOR RECOVERY OF TAG was ingested and used to fix the needed gap.
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 “RECOVERY OF TAG” command:
RMAN> run{
2> ALTER PLUGGABLE DATABASE ORCL18P CLOSE IMMEDIATE INSTANCES=ALL;
3> RESTORE DATAFILE 12;
4> RECOVER DATAFILE 12;
5> ALTER PLUGGABLE DATABASE ORCL18P OPEN INSTANCES=ALL;
6> }
Statement processed
starting full resync of recovery catalog
full resync complete
Starting restore at 21/04/2020 00:57:53
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=88 device type=DISK
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=70 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: RA Library (ZDLRAS1) SID=A3C1D44670B428B0E053010310AC5DA9
channel ORA_SBT_TAPE_1: starting datafile backup set restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_SBT_TAPE_1: restoring datafile 00012 to /u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf
channel ORA_SBT_TAPE_1: reading from backup piece VB$_1891149551_24813_12
channel ORA_SBT_TAPE_1: piece handle=VB$_1891149551_24813_12 tag=TAG20200420T235158
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:25
Finished restore at 21/04/2020 00:58:21
Starting recover at 21/04/2020 00:58:21
using channel ORA_DISK_1
using channel ORA_SBT_TAPE_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 21/04/2020 00:58:22
Statement processed
starting full resync of recovery catalog
full resync complete
RMAN>
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 “FOR TAG” 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 ‘<TAG>’ DATAFILE XX.
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:
RMAN> BACKUP CUMULATIVE INCREMENTAL LEVEL 1 DEVICE TYPE SBT FOR RECOVER OF TAG 'TAG20200419T232006' DATAFILE 12;
Starting backup at 19/04/2020 23:38:44
using channel ORA_SBT_TAPE_1
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=00012 name=/u01/app/oracle/oradata/ORCL18C/ORCL18P/users01.dbf
channel ORA_SBT_TAPE_1: starting piece 1 at 19/04/2020 23:38:44
channel ORA_SBT_TAPE_1: finished piece 1 at 19/04/2020 23:38:59
piece handle=ORCL18C_97uu2oo4_1_1 tag=TAG20200419T232006 comment=API Version 2.0,MMS Version 12.2.0.2
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:15
Finished backup at 19/04/2020 23:38:59
Starting Control File and SPFILE Autobackup at 19/04/2020 23:39:00
piece handle=c-558466555-20200419-04 comment=API Version 2.0,MMS Version 12.2.0.2
Finished Control File and SPFILE Autobackup at 19/04/2020 23:39:16
RMAN>
BACKUP FULL
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.
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.
Monitoring the Tasks
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 table.
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
It is a simple query, and a simple monitoring thing to do. But this will avoid a huge problem.
Reference:
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.”
