Toad World

Taking Care of Block Change Tracking While Duplicating Databases

Avatar photo
Post author:Written By

Before Oracle 12c, pluggable databases didn’t exist; whenever we wanted to clone a database we had use either RMAN Backup and Restoreor 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 11.2.0.3 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:

  • Restore datafiles
  • Recover until before the SCN that requires Block Change Tracking

Or

  • Duplicate database with UNTIL SCN

Similar to the following example:

duplicate database to 'db2' backup location '/home/oracle/backup' NOFILENAMECHECK UNTIL SCN <scn#>
DB_FILE_NAME_CONVERT=('/oradata/db1/','/oradata/db2/');

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;
Database altered.

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';
Database altered.

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:

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-27038: created file already exists
Additional information: 1

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
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  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
DATAFILE
  '/oradata/db2/system01.dbf',
  '/oradata/db2/sysaux01.dbf',
  '/oradata/db2/undotbs01.dbf',
  '/oradata/db2/users01.dbf'
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
----------  ------------ -------------------------
DISABLED

 

Conclusion

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 11.2.0.3 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.

Tags:

About the Author

Deiby Gomez

Deiby Gómez is the first Oracle ACE Director of Guatemala. He has the highest technical certification in the world: "Oracle Certified Master 11g", "Oracle Certified Master 12c" and "Maximum Availability Architecture Oracle Certified Master 12c", he is the first person ever in Central America with all these certifications. Deiby likes to work with complex scenarios, huge and highly available critical databases where a deep knowledge of Oracle is needed. Deiby also has strong knowledge on Oracle Fusion Middleware and Oracle Cloud (PaaS & IaaS). Deiby was the winner of "IOUG SELECT Journal Editor’s Choice Award 2016" in Las Vegas, USA. He is a frequent speaker in Oracle Events around the World like OTN LAD Tour '13, '14, '15, '16, '17 (Colombia, Guatemala, El Salvador, Ecuador, Uruguay, Argentina, Brazil, Perú, Mexico, Costa Rica); Collaborate in Las Vegas, USA and Oracle Open World '15, '16, '17 (Brazil and USA). He was the first Guatemalan accepted as Beta Tester (12cR2) in San Francisco in 2015. Several articles have been published by him in English, Spanish and Portuguese in Oracle’s website, Toad World, and his own blog. Deiby appeared in the Official "Oracle Magazine" in Nov/Dec 2014 Edition as an outstanding expert. Deiby is the Technical Reviewer of the book “Oracle Database 12cR2 Multitenant - Oracle Press” and he is co-author of the book “Oracle Database 12cR2 Testing Tools and Techniques for Performance and Scalability - Oracle Press”. He loves to share his knowledge, to help people, to solve problems, to make friends and to play Chess.

Start the discussion at forums.toadworld.com