TABLE BACKUP, RECOVERY AND REPLICATION USING SQL*PLUS
    
    
    Abstract:
    
    A technique has been developed whereby a complete auditing 
    system can be quickly established on any selected table or 
    suite of tables, using a combination of SQL*Plus scripts and 
    table triggers. An asynchronous master-master replication 
    system has been built using the same underlying audit 
    technology. The system has been successfully used for several 
    years, proving to be totally reliable. It has now been extended 
    to permit selective recovery of previously committed transactions, 
    without the inconvenience of invoking the conventional Oracle 
    systems-level restore procedures. Auditing and recovery can 
    be carried out in an individual schema, or across multiple 
    shared schemas using the replication facilities..
    
    The procedure requires the addition of some extra audit 
    fields to be added to each table to be audited, as well 
    as the creation of a history table for each audited table, 
    to store pre-change rows, as well as the creation of a 
    database trigger for each data table. All these components 
    are created using automated SQL*Plus code generation features.
     
    The recovery process operates by identifying either an 
    individual transaction to reverse, or a suite of transactions 
    which form a continuous series up to the present table status. 
    These recovery processes can be replicated to or from any 
    participating instances. The presence of foreign key constraint 
    relationships determine the minimum number of recovery 
    transactions. This process is controlled by a suite of 
    SQL*Plus scripts which again make use of code generation 
    functionality.
    
    There are several major benefits to this approach - firstly, 
    audit control of any controlled table is total, and can 
    extend back in time for as long as disk space is available 
    to hold the history data. Secondly, the master table can be 
    'rolled back' to any chosen point in time in the past, so 
    long as the audit history table contains a complete 
    transaction record. Thirdly, it does not require dba-level 
    permissions to function, but can operate on an individual 
    (private) schema.. Fourthly, the replication system provides 
    the means of maintaining the integrity of an entire Oracle 
    environment, irrespective of the number of instances deployed. 
    And finally - it is immune to changes between different 
    versions of Oracle.
    
    
    Peter Robson
    Database Administrator,