Taking Care of Block Change Tracking While Duplicating Databases

    Mar 22, 2017 11:32:00 AM 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 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: Oracle

    Deiby Gomez

    Written by Deiby Gomez

    Deiby Gómez is the youngest Oracle ACE and Oracle ACE Director in the world and the first Guatemalan with these awards. Deiby is the youngest Latin American with the highest certifications “Oracle Certified Master 11g” and “Oracle Certified Master 12c”. He received In Vegas, United States the "SELECT Journal Editor’s Choice Award 2016", he became the first Guatemalan with that award. He is a frequent speaker in several Oracle Events around the world like “Technology Network Latin American Tour 2013, 2014, 2015 and 2016 in several countries like Guatemala, Costa Rica, Nicaragua, El Salvador, Uruguay, Argentina, Mexico, Brazil, Ecuador, Colombia, Peru; Collaborate in Vegas, USA; Latin American Oracle Open World in Brazil (2015 and 2016) and Oracle Open World in San Francisco, USA (2015 and 2016). He is the first Guatemalan who was accepted by Oracle Corporation as “Beta Tester” for the version “12cR2” in 2015. He is the official Technical Reviewer of the Book “Oracle Database 12c Release 2 Multitenant (1st Edition, McGraw-Hill)" and Co-Author of the book "Oracle Database 12c Release 2 Testing Tools and Techniques for Performance and Scalability", both can be found in Amazon. He is the first Guatemala who appeared as an outstanding expert in the official magazine of Oracle Corporation called “Oracle Magazine” in the Edition November/December in 2014, this magazine is delivered around the world in several countries. He has published several articles in the Official Website of Oracle (www.oracle.com) in Portuguese, Spanish and English. Currently he is President of Guatemalan Oracle Users Group (GOUG). Director of Support Quality en Latin American Oracle Users Group Community (LAOUC) for 2016-2017, founder of a very well known group in Oracle Community called “Oraworld Team” that has members from India, Guatemala, Brazil, France and Switzerland having in total 4 Oracle ACE Directors, 3 Oracle ACE, 5 Oracle Certified Masters (OCM) and 1 PhD. Deiby was part of Oracle ACE Hackaton in Amsterdam, Netherlands in April 2016 where he was building several solutions using Oracle Cloud Products. Currently Deiby Gómez is CEO in Nuvola Consulting Group, a company that provides excellence on Support and Consulting services with Oracle Technology like Databases, Middleware, Cloud and Engineered systems. Deiby is well known in the community because of his resilience, entrepreneurship and his availability to help and share his knowledge. Deiby loves to travel, to play chess and to enjoy a good cup of coffee with friends. Oracle ACE (at the age of 23) Oracle Certified Master 11g (at the age of 24) Oracle ACE Director (at the age of 25) Oracle Certified Master 12c (at the age 26) SELECT Journal Editor’s Choice Award 2016 Speaker in several Universities, OTN Tour 2013,2014,2015. Collaborate15, LA Oracle Open World. Technical Reviewer of the Book "Oracle Database 12c Release 2 Multitenant (Oracle Press) 1st Edition" Blogger. Oracle Certifications: Oracle Linux Certified Implementation Specialist. Oracle Database 11g Administrator Certified Professional. Oracle Database 11g Administrator Certified Master (OCM 11g) Oracle Database 12c Administrator Certified Master (OCM 12c) Oracle Database 12c Maximum Availability Architecture Certified Master (MAA OCM 12c) Oracle Database 12c Maximum Availability Architecture Certified Expert (MAA OCE 12c) Oracle Database 12c Administrator Certified Professional. Oracle Service Oriented Architecture Infrastructure Implementation Certified Expert. Oracle Exadata Database Machine Administrator. Oracle RAC 11g and Grid Infraestructure Administrator Oracle RAC 12c and Grid Infraestructure Administrator Oracle Real Application Clusters 12c Certified Implementation Specialist Oracle Database 12c: Data Guard Administrator