{"id":1094,"date":"2015-06-25T07:54:03","date_gmt":"2015-06-25T05:54:03","guid":{"rendered":"http:\/\/www.ludovicocaldara.net\/dba\/?p=1094"},"modified":"2020-08-18T16:32:50","modified_gmt":"2020-08-18T14:32:50","slug":"bct-duplicate-avoid-ora19755","status":"publish","type":"post","link":"https:\/\/www.ludovicocaldara.net\/dba\/bct-duplicate-avoid-ora19755\/","title":{"rendered":"Block Change Tracking and Duplicate: avoid ORA-19755"},"content":{"rendered":"<p>If you use Block Change Tracking on your production database and try to duplicate it, you there are good possibilities that you will encounter this error:<\/p>\n<pre class=\"lang:plsql decode:true\">ORA-19755: could not open change tracking file\r\nORA-19750: change tracking file: \u2018\/u02\/oradata\/ORCL\/ORCL_bct.dbf\u2019\r\nORA-27037: unable to obtain file status<\/pre>\n<p>The problem is caused by the block change tracking file entry that exists in the target controlfile, but Oracle can&#8217;t find the file because the directory structure on the auxiliary server changes.<\/p>\n<p>After the restore and recovery of the auxiliary database, the duplicate process tries to open the DB but the bct file doesn&#8217;t exist and the error is thrown.<\/p>\n<p>If you do a quick google search you will find several workarounds:<\/p>\n<ul>\n<li>disable the block change tracking after you get the error and manually open the auxiliary instance (this prevent the possibility to get the duplicate outcome from the rman return code)<\/li>\n<li>disable the BCT on the target before running the duplicate (this forces your incremental backups to read all your target database!)<\/li>\n<li>Richard Harrison proposed another workaround, <a href=\"http:\/\/dbaharrison.blogspot.ch\/2014\/01\/rman-duplicate-database-addendum.html\">you can read more about it here<\/a>.<\/li>\n<\/ul>\n<p>There is another workaround that I like more (and that you can also find as comment in Richard&#8217;s post):<\/p>\n<ul>\n<li>Disable the Block Change Tracking on the auxiliary while it&#8217;s doing the restore\/recovery (in mounted status)<\/li>\n<\/ul>\n<p>(This solutions isn&#8217;t coming from me, but as far as I know, the father of this solution is a colleague working at Trivadis.)<\/p>\n<p>You can easily fork a process before running the duplicate command that:<\/p>\n<ul>\n<li>loops and checks the auxiliary instance status<\/li>\n<li>run the disable as soon as the auxiliary is mounted<\/li>\n<\/ul>\n<p>I&#8217;ve worked out this script that does the job:<\/p>\n<pre class=\"lang:sh decode:true\">#!\/bin\/ksh\r\n\r\nif [ $# -ne 1 ] ; then\r\n        echo \"Usage: $0 \\$ORACLE_SID\"\r\n        exit 1\r\nfi\r\nORACLE_SID=$1\r\nexport ORACLE_SID\r\n\r\nORACLE_HOME=`cat \/etc\/oratab | grep ^$ORACLE_SID | awk -F\":\" '{print $2}'`\r\nexport ORACLE_HOME\r\n\r\n\r\ndisable_trk () {\r\n        echo \"DISABLE BLOCK CHANGE TRACKING\"\r\n        sqlplus -s \/ as sysdba &lt;&lt; EOF\r\n        set echo on\r\n        col FILENAME for a30\r\n        ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;\r\n        select * from v\\$block_change_tracking;\r\nEOF\r\n}\r\n\r\necho \"Checking block change tracking for $ORACLE_SID\"\r\n\r\nPATH=${ORACLE_HOME}\/bin:$PATH; export PATH\r\n\r\n## for how much time do you want to run the check?\r\nINTERVAL=30\r\nMAXCOUNT=30\r\n\r\n# loop until the database status is either MOUNTED or OPEN\r\nCOUNT=1\r\nwhile [ $COUNT -le $MAXCOUNT ] ; do\r\n        STATUS=`sqlplus -s \/ as sysdba &lt;&lt;EOF\r\n                whenever sqlerror exit SQL.SQLCODE;\r\n                set head off feed off\r\n                select status from v\\\\$instance;\r\nEOF\r\n`\r\n        if [ $? -eq 0 ] ; then\r\n                if [ $STATUS != \"MOUNTED\" ] &amp;&amp; [ $STATUS != \"OPEN\" ] ; then\r\n                        echo \"$COUNT : Still not mounted\"\r\n                        sleep $INTERVAL\r\n                        COUNT=$(($COUNT+1))\r\n                        continue;\r\n                else\r\n                        echo\r\n                        echo \"If there is an error, BCT is enabled but Oracle can't find the file (ORA-27037)\"\r\n                        echo \"This is normal, I'll procede with the disable to avoid the error at the end of the duplicate.\"\r\n                        sqlplus -s \/ as sysdba &lt;&lt;EOF 2&gt;\/dev\/null\r\n                        whenever sqlerror exit SQL.SQLCODE;\r\n                        set head off feed off\r\n                        select status from v\\$block_change_tracking where status='ENABLED';\r\nEOF\r\n                        # if i get an error, BCT is enabled but Oracle can't find the file (ORA-27037)\r\n                        if [ $? -eq 0 ] ; then\r\n                                echo \"BCT already disabled\"\r\n                        else\r\n                                echo \"Got the error. Let's disable the BCT!\"\r\n                                disable_trk\r\n                        fi\r\n                        break;\r\n                fi\r\n        else\r\n                echo \"$COUNT : Still not mounted\"\r\n                sleep $INTERVAL\r\n                COUNT=$(($COUNT+1))\r\n                continue;\r\n        fi\r\ndone<\/pre>\n<p>Run it\u00a0 just before the duplicate! e.g.<\/p>\n<pre class=\"lang:sh decode:true \">\/u01\/app\/oracle\/local\/bin\/disable_trk_dup.sh $ORACLE_SID &amp;\r\nrman target=... auxiliary=...<\/pre>\n<p>HTH<\/p>\n<p>&#8212;<\/p>\n<p>Ludovico<\/p>\n","protected":false},"excerpt":{"rendered":"<p>If you use Block Change Tracking on your production database and try to duplicate it, you there are good possibilities that you will encounter this error: ORA-19755: could not open change tracking file ORA-19750: change tracking file: \u2018\/u02\/oradata\/ORCL\/ORCL_bct.dbf\u2019 ORA-27037: unable &hellip; <a href=\"https:\/\/www.ludovicocaldara.net\/dba\/bct-duplicate-avoid-ora19755\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[326,3,330,132],"tags":[66,234,45,232,231,233,9,22],"class_list":["post-1094","post","type-post","status-publish","format-standard","hentry","category-oracle","category-oracledb","category-oracle-inst-upg","category-triblog","tag-backup","tag-block-change-tracking","tag-duplicate","tag-ora-19750","tag-ora-19755","tag-ora-27037","tag-oracle","tag-oracle-database"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1094","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/comments?post=1094"}],"version-history":[{"count":4,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1094\/revisions"}],"predecessor-version":[{"id":1101,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1094\/revisions\/1101"}],"wp:attachment":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/media?parent=1094"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/categories?post=1094"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/tags?post=1094"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}