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:
|
1 2 3 |
ORA-19755: could not open change tracking file ORA-19750: change tracking file: ‘/u02/oradata/ORCL/ORCL_bct.dbf’ ORA-27037: unable to obtain file status |
The problem is caused by the block change tracking file entry that exists in the target controlfile, but Oracle can’t find the file because the directory structure on the auxiliary server changes.
After the restore and recovery of the auxiliary database, the duplicate process tries to open the DB but the bct file doesn’t exist and the error is thrown.
If you do a quick google search you will find several workarounds:
- 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)
- disable the BCT on the target before running the duplicate (this forces your incremental backups to read all your target database!)
- Richard Harrison proposed another workaround, you can read more about it here.
There is another workaround that I like more (and that you can also find as comment in Richard’s post):
- Disable the Block Change Tracking on the auxiliary while it’s doing the restore/recovery (in mounted status)
(This solutions isn’t coming from me, but as far as I know, the father of this solution is a colleague working at Trivadis.)
You can easily fork a process before running the duplicate command that:
- loops and checks the auxiliary instance status
- run the disable as soon as the auxiliary is mounted
I’ve worked out this script that does the job:
|
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 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
#!/bin/ksh if [ $# -ne 1 ] ; then echo "Usage: $0 \$ORACLE_SID" exit 1 fi ORACLE_SID=$1 export ORACLE_SID ORACLE_HOME=`cat /etc/oratab | grep ^$ORACLE_SID | awk -F":" '{print $2}'` export ORACLE_HOME disable_trk () { echo "DISABLE BLOCK CHANGE TRACKING" sqlplus -s / as sysdba << EOF set echo on col FILENAME for a30 ALTER DATABASE DISABLE BLOCK CHANGE TRACKING; select * from v\$block_change_tracking; EOF } echo "Checking block change tracking for $ORACLE_SID" PATH=${ORACLE_HOME}/bin:$PATH; export PATH ## for how much time do you want to run the check? INTERVAL=30 MAXCOUNT=30 # loop until the database status is either MOUNTED or OPEN COUNT=1 while [ $COUNT -le $MAXCOUNT ] ; do STATUS=`sqlplus -s / as sysdba <<EOF whenever sqlerror exit SQL.SQLCODE; set head off feed off select status from v\\$instance; EOF ` if [ $? -eq 0 ] ; then if [ $STATUS != "MOUNTED" ] && [ $STATUS != "OPEN" ] ; then echo "$COUNT : Still not mounted" sleep $INTERVAL COUNT=$(($COUNT+1)) continue; else echo echo "If there is an error, BCT is enabled but Oracle can't find the file (ORA-27037)" echo "This is normal, I'll procede with the disable to avoid the error at the end of the duplicate." sqlplus -s / as sysdba <<EOF 2>/dev/null whenever sqlerror exit SQL.SQLCODE; set head off feed off select status from v\$block_change_tracking where status='ENABLED'; EOF # if i get an error, BCT is enabled but Oracle can't find the file (ORA-27037) if [ $? -eq 0 ] ; then echo "BCT already disabled" else echo "Got the error. Let's disable the BCT!" disable_trk fi break; fi else echo "$COUNT : Still not mounted" sleep $INTERVAL COUNT=$(($COUNT+1)) continue; fi done |
Run it just before the duplicate! e.g.
|
1 2 |
/u01/app/oracle/local/bin/disable_trk_dup.sh $ORACLE_SID & rman target=... auxiliary=... |
HTH
—
Ludovico
Latest posts by Ludovico (see all)
- New views in Oracle Data Guard 23c - January 3, 2024
- New in Data Guard 21c and 23c: Automatic preparation of the primary - December 22, 2023
- Does FLASHBACK QUERY work across incarnations or after a Data Guard failover? - December 13, 2023
Disabling BCT on auxillary/ target db to be recovered helped me to solve the issue
Thank you Ludovico: our tool team implemented both ‘set newname for block change tracking file to new;’ for 12c and your shell script for prior-to-12c versions.
Wouldn’t this not work since the Auxiliary database would be unmounted until the RMAN script re-creates the control file?
Since we are using ASM on our Source and Auxiliary database, we drop the Auxiliary database prior to running duplicate.
Hi Mark, I see it more like a non-implemented feature. The conversion of file names does not work for the BCT. I did not check if the behavior changed in 12cR2, I will do it soon and blog about it 🙂
It appears that the command “set newname for block change tracking file” did not help either. We will try the suggested work-around from Ludovic. I am hoping that Oracle has already identified this as a bug and will fix it soon.
Hey, thanks, this saved my ass today! Anyways, I should set it to off on the source DB, that’s a DataGuard, and I backup the secondary, so BCT doesn’t do anything.
Thanks for sharing I was able to add this into our automated rman dupliate jobs for our ZDLRA and this helped!
Glad it helped, Javier! Thank you for letting me know 🙂
Hi Ludo
In 12c and with some 11gR2 patches there is a new RMAN syntax:
“set newname for block change tracking file…”
Furthermore what you can use always and in my opinion the easiest approach is db_file_name_convert. That works very well for the BCT file.
Cheers Roland
Hi Roland, thank you for your comment! 🙂
Unfortunately, sometimes the db_file_name_convert doesn’t work as expected and I encounter the ORA-19755 error despite the convert is correctly set.
I wasn’t aware of the new syntax “set newname for block change tracking file”, I’ll definitely try it, but this would require additional checks prior to run the duplicate (if version > 11.2.0.x and BCT is enabled on the source).
Pingback: Block Change Tracking and Duplicate: avoid ORA-19755 - Ludovico Caldara - Blogs - triBLOG