By Deiby Gomez
Before Oracle 12c, pluggable databases didn’t exist; whenever we wanted to clone a database we had use either RMAN Backup and Restore or RMAN Duplicate (Active or from backup location). There are several use cases to consider when we are talking about cloning databases, from low-criticality cases like cloning a database into a new empty server to highly critical cases like cloning a production database in the same production server. Whatever the use case, most of the time, a DBA considers only four kinds of files when planning to restore a database: spfile, controlfiles, datafiles and redologs.
But what if the database uses Block Change Tracking? The database has several others files that we also should consider; for instance, password file (for Physical Standby Databases) and Flashback Logs (to use Flashback Database) and, of course, “Block Change Tracking” (in the use case of this article, for RMAN Duplicates and RMAN restores). In the following image, we can see the “big picture” of an Oracle database:
But why is Block Change Tracking important to consider? Well, let me tell you a story. Several years ago I was working on duplicating a big database into a new server. The database was 188.8.131.52 and I was using RMAN Duplicate from Backup pieces. You can read more about RMAN duplicates in my previous articles:
I was preparing the environment in the target database, I made sure to have all the directories created that are referenced by the spfile, the required space to create the new database, the permissions on the directory. To perform this duplication, I had approximately one day, but the database was around 400GB so for sure I knew it was going to take time to duplicate such a database. I decided to specify a different path (DB_FILE_NAME_CONVERT) to the new directories for the datafiles since the directory structure in the target server was different from the directory structure in the source server. I remember that day because it was a hard day for me, mostly because I had to deal with the time window (one day) and I was running out of time. Well, I prepared everything and then I raised the “RMAN Duplicate from backup pieces”. It took several hours to restore the datafiles and a couple of hours more to recover them, but it was when recovering the datafiles that I hit the following bug:
Bug 18371441 : RMAN DUPLICATE FAILS TO CREATE BCT FILE
The messages I received from RMAN were similar to the following:
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/.../o1_mf_1_90_9m65pp1g_.arc'
ORA-00283: recovery session canceled due to errors
ORA-19755: could not open change tracking file
ORA-19750: change tracking file: '/oradata/.../blockchangetracking.dbf'
ORA-17503: ksfdopn:2 Failed to open file /oradata/l.../blockchangetracking.dbf
It was kind of strange for me to see that the block change tracking file was involved in an “alter database recover logfile” operation. I tried to complete the recovery of the database but was unsuccessful because the database reached the problematic SCN. I had to recover the database for a previous SCN, and the only way to fix this was to raise the RMAN duplicate again from the beginning. Here was where I started to feel the stress because I had only a few hours available to complete this task.
So, some advice: Always check out if your database uses block change tracking file if you are in one of the versions impacted by this bug.
The bug seems to occur when Block Change Tracking is enabled in the source database and a datafile was “autoextended”. When the sequence in which the datafile size was changed is applied to the auxiliary instance, the duplicate fails when trying to create the Block Change Tracking, leaving the database in an inconsistent state in the middle of a recovery process.
The bug seems to be confirmed in the following versions:
The bug has been fixed in the following:
I forgot to tell you that that day I also hit the following bug :
Bug 11744544 - Set newname for database does not apply to block change tracking file (Doc ID 11744544.8)
Yes… it was a hard day for me.
So, I decided to disable the block change tracking file before applying the “recovery”. There are two ways to do this:
Similar to the following example:
duplicate database to 'db2' backup location '/home/oracle/backup' NOFILENAMECHECK UNTIL SCN <scn#>
In the end I was able to duplicate the source database. It took me some extra hours but the work was accepted by the customer and everybody was happy.
So the advice from this article is always to check whether the database is using block change tracking. There are several bugs related to block change tracking while duplicating databases with RMAN, and even restoring and recovering databases. If the source database is using block change tracking, I advise that you disable it in the target database before restore and recover. By doing so you will avoid encountering the bugs related to it. This procedure is also recommended in the note: Rman Duplicate fail ORA-19755, Tries Open The Block Change Tracking File of Source DB (Doc ID 1098638.1).
You can use the following sentence to check whether a database is using Block Change Tracking:
SQL> select * from V$BLOCK_CHANGE_TRACKING
STATUS FILENAME BYTES CON_ID
---------- -------------------- ---------- ----------
ENABLED /home/oracle/bct.dbf 11599872 0
To disable block change tracking (in the target database) before to apply redologs you can use the following statement:
SQL> alter database disable block change tracking;
To enable block change tracking after successfully restoring or duplicating the new database you can use the following statement:
SQL> alter database enable block change tracking using file '/home/oracle/bct.dbf';
Now let me tell you a little bit more about some other scenarios that I also tested:
Using RMAN Backup / Restore – Source server not the same as Target Server
Usually when we restore a database from backup in another server there are several activities involved, like performing an RMAN backup from the source database with all the archivelogs, transferring all those backup files to the other server, preparing the target server with the spfile, restoring the controlfile, renaming all the files to a new directory in case the directory structure is different between source server and target server and then restoring the datafiles, applying recover, and finally opening the database with resetlogs. In this scenario if we are duplicating a database that uses block change tracking and the bug 18371441 is not present, all you have to do is ensure that the directory where the source block change tracking file is located also exists in the target server; otherwise you will receive the following error:
Executing: alter database enable block change tracking using file '/home/oracle/blockchangetracking/bct.dbf'
ORACLE error from auxiliary database: ORA-19751: could not create the change tracking file
ORA-19750: change tracking file: '/home/oracle/blockchangetracking/bct.dbf'
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1
Using RMAN Backup / Restore – Source server the same as Target Server
If you are duplicating a block change tracking and you are not encountering bug 18371441, then don’t worry about overwriting the current block change tracking of the source database. Even if you didn’t take the block change tracking file into consideration, when the RMAN duplicate tries to recreate the block change tracking of the target database you will receive the following error:
ORA-27038: created file already exists
This error says that the new block change tracking was not created because it “already exists”. We know that that existing block change tracking doesn’t belong to the new database, but this is OK; as long as it was not overwritten, we are OK. We can safely enable block change tracking in the new database using a different file name.
Using RMAN Restore – Same Server
If a new database is being created by restoring an existing RMAN backup and the source database is in the same server where the target database is intended to be stored, I suggest creating a control file “to trace” and then creating the controlfile using that trace file; of course, after reviewing to make sure that all the datafile paths are different from the paths that the original database. When the controlfile is recreated, the block change tracking file is automatically disabled. I already confirmed this:
SQL> CREATE CONTROLFILE set DATABASE "DB2" RESETLOGS ARCHIVELOG
GROUP 1 '/oradata/db2/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/oradata/db2/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/oradata/db2/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
CHARACTER SET WE8MSWIN1252
; 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
Control file created.
SQL> select * from V$BLOCK_CHANGE_TRACKING;
STATUS FILENAME BYTES
---------- ------------ -------------------------
In this article there was presented a real use case where the file that usually DBAs think that don’t have importance, in fact it has and a lot of importance actually. Block Change Tracking can be used in any database, it is recommended in huge databases where the time to perform a backup is long. This article makes DBAs to put attention either the BCT is being used or not, because there are some bugs, especially since 184.108.40.206 until 12.1, like the bugs presented in this article, that could take the DBAs to run out of time in a maintenance window. It is better to always recommended to review if BCT is used and proceed properly while restoring a database or duplicating it. In this article there was presented the steps to review if BCT was used, there were provided recommendations and how to proceed to avoid the well-known bugs.