Please note that to view the My Oracle Support documents linked in this post you have to register for My Oracle Support first!
I know that this issue is quite special and hopefully most of you guys will never hit it. So here’s what happened …
The bigfile tablespace feature
As you maybe (not) know: RDBMS version 10g Oracle introduces bigfile tablespaces. This feature allows you to have a tablespace size up to 32TB when using a 8k blocks size or up to 128TB for a single tablespace when using 32k blocks. So far so good.
Some useful information
Here’s a cool My Oracle Support note (262472.1) regarding bigfile tablespaces.
The problem we ran into
But some day in one of our databases (in the meantime the database was upgraded to 18c) one of the bigfile tablespaces ran out of space. Additionally to the fact that a full tablespace is never a funny thing: having a bigfile tablespace filled up to the maximum size is a much worse thing.
What you also maybe (not) know: bigfile tablespaces can only have one single datafile. So it’s quite difficult to solve the issue when a bigfile tablespace is full.
Later we noticed that the RMAN backup pieces of this filled up tablespace are not present within the RMAN catalog nor in the database’s controlfile anymore. The good message is … they are still available in the filesystem.
After struggling around with this issue for a while we found a note in My Oracle Support which states that this is a bug in RDBMS 11g and later. Even if the files are available on disk, RMAN doesn’t recognize them anymore.
As stated in MoS note 2407835.1 you can re-catalog these files if they are still available on disk. Despite the fact that the note states that you can use the “catalog start with” clause to re-catalog every valid backup piece within the directory, this clause is not working. The only way to re-catalog the missing pieces is using the “catalog backuppiece” clause for every single file. Maybe you can imagine that it takes some time if you separate your 32TB bigfile tablespace into backup pieces of 256G size.
To sum it up: it’s quite a strange bug but if you know about it, it’s quite easy to fix.
The details about the issue
Following you can see the file we backed up
|
1 2 3 4 5 |
SQL> select file_id,bytes/1024/1024/1024 from dba_data_files where file_id = 8; FILE_ID BYTES/1024/1024/1024 ---------- -------------------- 8 32767 |
And below, a snippet of the backup logfile is shown
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00008 name=+DATA01/ORCL01/DATAFILE/bigts1.303.1010455979 backing up blocks 1 through 67108864 channel ORA_DISK_1: starting piece 1 at 20191013_130535 ...................... input datafile file number=00008 name=+DATA01/ORCL01/DATAFILE/bigts1.303.1010455979 backing up blocks 67108865 through 134217728 channel ORA_DISK_13: starting piece 2 at 20191013_130603 channel ORA_DISK_6: finished piece 1 at 20191013_130629 piece handle=/zfssa/BACKUP/ORCL01/inc0_ORCL01_20191013_s289825_p1 tag=TAG20191013T130533 comment=NONE channel ORA_DISK_6: backup set complete, elapsed time: 00:00:53 channel ORA_DISK_6: starting compressed incremental level 0 datafile backup set channel ORA_DISK_6: specifying datafile(s) in backup set input datafile file number=00008 name=+DATA01/ORCL01/DATAFILE/bigts1.303.1010455979 backing up blocks 134217729 through 201326592 channel ORA_DISK_6: starting piece 3 at 20191013_130629 channel ORA_DISK_14: finished piece 1 at 20191013_130629 piece handle=/zfssa/BACKUP/ORCL01/inc0_ORCL01_20191013_s289833_p1 tag=TAG20191013T130533 comment=NONE channel ORA_DISK_14: backup set complete, elapsed time: 00:00:52 channel ORA_DISK_14: starting compressed incremental level 0 datafile backup set channel ORA_DISK_14: specifying datafile(s) in backup set input datafile file number=00008 name=+DATA01/ORCL01/DATAFILE/bigts1.303.1010455979 backing up blocks 201326593 through 268435456 ....................... input datafile file number=00008 name=+DATA01/ORCL01/DATAFILE/bigts1.303.1010455979 backing up blocks 4160749569 through 4227858432 ...................... input datafile file number=00008 name=+DATA01/ORCL01/DATAFILE/bigts1.303.1010455979 backing up blocks 4227858433 through 4294836224 .................. channel ORA_DISK_1: finished piece 52 at 20191013_151856 piece handle=/zfssa/BACKUP/ORCL01/inc0_ORCL01_20191013_s289820_p52 tag=TAG20191013T130533 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:27:12 |
We checked the datafile within RMAN (tried with or without catalog with the same result)
|
1 2 3 4 5 6 7 8 9 10 11 12 |
RMAN> list backup of datafile 8; specification does not match any backup in the repository RMAN> report need backup datafile 8; RMAN retention policy will be applied to the command RMAN retention policy is set to recovery window of 10 days Report of files that must be backed up to satisfy 10 days recovery window File Days Name ---- ----- ----------------------------------------------------- 8 1142 +DATA01/ORCL01/DATAFILE/bigts1.303.1010455979 |
After that we tried to catalog all the files using ‘catalog start with‘
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
RMAN> catalog start with '/zfssa/BACKUP/ORCL01/'; searching for all files that match the pattern /zfssa/BACKUP/ORCL01/ List of Files Unknown to the Database ===================================== File Name: /zfssa/BACKUP/ORCL01/afiedt.buf Do you really want to catalog the above files (enter YES or NO)? yes cataloging files... no files cataloged #-# <<<<<<<<<<<<<<<<<<<<<<<<<<< nothing cataloged?!?!? List of Files Which Were Not Cataloged ======================================= File Name: /zfssa/BACKUP/ORCL01/afiedt.buf RMAN-07517: Reason: The file header is corrupted #-# sure this is not a valid RMAN backup piece RMAN> list backup of datafile 8 summary; specification does not match any backup in the repository |
After cataloging every file with the glob *20191003* in the directory /zfssa/BACKUP/ORCL01/ the following output appeared on the command line:
|
1 2 3 4 5 6 7 8 |
RMAN> list backup of datafile 8 summary; List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- --------------- ------- ------- ---------- --- 4080220 B 0 A DISK 13.10.19 64 1 YES TAG20191013T130533 |
Finally we were able to restore file number 8
|
1 |
RMAN> restore datafile 8; |
