SQL BackTrack and Flash Recovery Area

Jul 15, 2008 / By Luke Davies

Tags: ,

Is your database in archive log mode?

Is your database in flashback mode?

Have you defaulted your archive location to the flash recovery area?

Is your ORACLE_SID lowercase?

Do you use SQL BackTrack?

If your answer is yes to all these questions then beware!

There is a bug in SQL BackTrack (at least in version 6.8) that prevents your backing up of the archive logs from the flash recovery area. This will result in the inability to recover from any online backups that you have taken if you somehow lose those archive logs.

You do not receive any obvious errors when doing the backup but, if you are observant, you may spot the fact that the backup report does not show that it backed up any archive logs. To really check whether you are suffering from this problem then you need to check the $DTBASE/obacktrack/log/datatools.log. This is the log that gets written whenever any SQL BackTrack command is run. You will see an error such as

17:29:16.61 obacktrack[839826](2835/oramisc.cpp): Unable to open directory: '/home/oracle/data/test/test/archivelog'

You will know if you have been caught by this bug when you try to restore and you get messages that look something like this.

BMCBKO4802051E: The following Oracle error occurred during
BMCBKO4802051E: SQL-BackTrack processing : 'ORA-01195: online backup of file 1 needs more recovery to be consistent'
BMCBKO4802051E: The following Oracle error occurred during
BMCBKO4802051E: SQL-BackTrack processing : 'ORA-01110: data file 1: '/home/oracle/data/test/system01.dbf''
BMCBKO4805906E: Unable to perform recovery.

The problem arises when Oracle creates a directory in the flash recovery area that correspond to the database name. This directory is in uppercase. So, in my case, my ORACLE_SID is test and the init.ora parameter db_recovery_file_dest='/home/oracle/data/test'.

Oracle creates the directory
/home/oracle/data/test/TEST.

However when SQL BackTrack sees that the archive location is defaulted to the flash recovery area it tries to look in the directory
/home/oracle/data/test/test.

This fails … silently!

There are 2 workarounds to this issue that I have tested.

  1. Create a symbolic(soft) link to point to the real directory using ln -s
  2. Explicitly set the archive location using the init.ora parameter log_archive_dest_n

The workaround suggested by BMC (the supplier of SQL BackTrack) is to change the database name to uppercase. This will work too, but I haven’t tested it.

3 Responses to “SQL BackTrack and Flash Recovery Area”

  • Jeff Aven says:

    Another option is to use HyperBac (www.hyperbac.com) instead of SQL BackTrack. It directly integrates with RMAN and will provide better in line compression for RMAN backups including archivelog backups, much more cost effective option for older Oracle systems than BackTrack.

  • Vijay says:

    sql-backtrack is not just about backup, It has lot more to offer than backup. One of the coolest feature is guided database recovery, also not to forget database duplication or cloning feature.
    There is one more additional feature called ‘Incremental Database Duplication’.

  • Carnage, Don says:

    HyperBac is just a wrapper on RMAN, SQL BT is far superior compare to any other tool for backup/recovery even when compare to RMAN, its very fast to gel will TSM or networker and faster backup/recovery compare to any other product

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>