3.1.3 Enable Total Recall (optional)

When Total Recall Option is activated, you should decide if one or more of the new tables should be added to a Flashback Data Archive.

Syntax to enable history tracking for a table is:

ALTER TABLE <tablename> FLASHBACK ARCHIVE [<Flashback Data Archive name>];

Note that the FDA name is required only when adding the table to a non-default FDA.

To disable history tracking for a table use:

ALTER TABLE <tablename> NO FLASHBACK ARCHIVE;

For convenience, we provide an example script that can help configuring archived tables:

import groovy.sql.Sql
import java.util.logging.*

def logger = Logger.getLogger(this.class.getName());

def config = new Config(args, 'setFlashbackArchive.cfg')
// Override the default level for the top-level logger
Logger.getLogger("").setLevel(config.log.level);
config.log()

def db = Sql.newInstance( config.db.url
                        , config.db.user
                        , config.db.passwd
                        , 'oracle.jdbc.driver.OracleDriver')
this.db = db

// Get the set of tables For which FBA needs to be switched off
def getTablesToSwitchOff(tables) {
    def tableList = tables.join("', '")
    switch_off = db.rows("""select fba.owner_name as owner
                            ,      fba.table_name
                            from   dba_flashback_archive_tables fba
                            where  fba.status = 'ENABLED'
                            and    fba.owner_name||'.'||fba.table_name not in ('""" + tableList + """')
                        """)
}

for (table in config.flashback.tables) {
    switch_on = db.firstRow("""select fba.owner_name as owner
                               ,      fba.table_name
                               from   dba_flashback_archive_tables fba
                               where  fba.owner_name||'.'||fba.table_name = $table
                               and    (  fba.status is null
                                      or fba.status != 'ENABLED'
                                      )
                            """)
    if (switch_on) {
        logger.info "Switching Flashback Archiving on for table ${table}"
        def stmt = "alter table " + table + " flashback archive ${config.flashback.archive}"
        try {
            println stmt
            db.execute(stmt)
        } catch (java.sql.SQLException e) {
            logger.warning "Error occurred while executing SQL " + stmt
            logger.warning "Error was " + e.getMessage()
            println "Unable to set Flashback Archive to ${config.flashback.archive} for table ${table} " + e.getMessage()
            println "Press enter to continue"
            new InputStreamReader(System.in).readLine()
        }
    }
}

// For every table that is not in the config-list but has FBA turned on, a confirmation is asked before actually
// turning off FBA
getTablesToSwitchOff(config.flashback.tables).each { it ->
    def tableName = "${it.owner}.${it.table_name}"
    println "Switching Flashback Archiving OFF for table ${tableName}"
    println "Are you sure you want to do that? This will purge all history for this table!! (Y/N)"
    response = new InputStreamReader(System.in).readLine()
    while (response.toUpperCase() != "Y" && response.toUpperCase() != "N") {
        println "Please respond with \"Y\" or \"N\""
        response = new InputStreamReader(System.in).readLine()
    }
    if (response.toUpperCase() == "Y") {
        def stmt = "alter table " + tableName + " no flashback archive"
        try {
            println stmt
            db.execute(stmt)
            logger.warning "Flashback Archiving was switched off for table ${tableName} as per users request"
        } catch (java.sql.SQLException e) {
            logger.warning "Error occurred while executing SQL " + stmt
            logger.warning "Error was " + e.getMessage()
            println "Unable to switch off Flashback Archive for table ${tableName} " + e.getMessage()
            println "Press enter to continue"
            new InputStreamReader(System.in).readLine()
        }
    } else {
        println "Flashback Archiving was NOT switched off for table ${tableName}."
        logger.warning "Flashback Archiving was NOT switched off for table ${tableName} as per users request"
    }
}

class Config {
  private static Logger logger = Logger.getLogger(Config.class.getName())
  def configFile
  def db = [:]
  def log = [:]
  def flashback
  
  def Config(args, configFile) {
    this.configFile = configFile
    def parsedConfig
    def cl = new CliBuilder(usage:
             'setFlashbackArchive.groovy [-c configFile]')

    cl.h(longOpt:'help', 'Show usage information and quit')
    cl.c(argName:'configFile', longOpt:'cfg', args:1, required:false, 'Config file, default is ' + configFile)

    def opt = cl.parse(args)

    if (!opt) {
      // the parse failed, the usage will be shown automatically
      println "\nInvalid command line, exiting..."
      System.exit(-1)
    } else if (opt.h) {
      cl.usage()
      System.exit(0)
    }

    if (opt.c) {
      this.configFile = opt.c
    }
    try {
      parsedConfig = new ConfigSlurper().parse(new File(this.configFile).toURL())
    } catch (FileNotFoundException e) {
      logger.severe "Config file ${this.configFile} not found"
      System.exit(-1)
    }
    db = parsedConfig.db
    db.url = db.protocol + "//" + db.hostname + ":"  + db.port + "/"  + db.sid
    log = parsedConfig.log
    flashback = parsedConfig.flashback
    if (!flashback) {
      println("\nflashback is not set in Config file")
      System.exit(-1)
    }
  }
  
  def log() {
    logger.config "db=" + db.toString()
  }
}

This script works in conjunction with a configuration file that can be created by copying the following to a file named setFlashbackArchive.cfg and changing its settings to appropriate values:

import java.util.logging.Level
// Allowable values: SEVERE | WARNING | INFO | CONFIG | FINE | FINER | FINEST  
log.level = Level.INFO

// user has to be a database user with the FLASHBACK ARCHIVE object privilege granted
// on the Archive used.
db {
  protocol = 'jdbc:oracle:thin:@'
  hostname = '<your hostname>'
  port     = '<your port>'
  sid      = '<your sid>'
  user     = '<your dbuser>'
  passwd   = '<your dbpassword>'
}

// This Flashback Archive has to exist.
// It can be created by a SYSDBA or a user with
// The tables argument contains a list of tables that should be archived. 
// If you remove a table from this list, archiving will be disabled.
flashback {
    archive = '<name of your FlashBack Archive>'
    tables = ['<owner.table1>', '<owner.table2>']
}

The script can be run using Groovy (groovy <scriptname>)