Learning Center
Plans & pricing Sign in
Sign Out

Disaster Recovery with InterBase


Disaster Recovery with
                      Bill Todd
       The Database Group, Inc.
           Data Loss
Physical loss
     Two Questions…
How much data can you afford to lose?
What disasters do you want to to protect
       A Range of Answers
You can lose one day of changes
   – Nightly backups are all you need
You cannot lose any data, even if your building
  burns to the ground
   – You need real time backup to a remote
         Physical Security
Most overlooked aspect of data protection
Without it anyone can:
  – Spill coffee on the server
  – Kick the plug out of the wall
  – Knock the server off of a table
  – Take it home
  – Take the hard drive home
  – Delete the database
                Log Off!
Never leave the server unattended with a user
  logged on
Anyone who gets access to the machine can add
  a new user to the Administrators group and
  access the server remotely
Without it every other aspect of your data
 protection plan will fail sooner or later
  – The DBA will run to the restroom without
    logging out
  – People who are supposed to change their
    passwords will use predictable ones that are
    easy to remember
  – People will write passwords down
  – People will give passwords over the phone
Use a dedicated database server
  – More software running on the DB server =
    more likely to crash
  – More software running on the DB server =
    more people who need access to the server
Only the DBA has login rights to the database
Do not share any drives or folders on the
   database server
If you use a non-dedicated server:
    – Never allow access to the database or server
      software folders
    – Make everyone change their password
Don’t run apps as SYSDBA
Don’t run apps as the database owner
Design your app so that you can easily change
  the account it runs under
Anyone who can log in as SYSDBA or the
  database owner and has access to gbak can
  steal your data
   – Uses services API
   – Services API
   – Local backup
IBBackupService component
   – Uses services API
All backups are full backups
Backup using services API
    – Runs entirely on the server
    – Backup file must be on the server
Backup using gbak
    – Backup across the network
    – Backup file can be placed anywhere
      To Make Backup Fast
Use the services API
Place the backup file on a different hard drive than
  the database
Disable garbage collection
   – If you want garbage collection run a sweep
     after the backup
      gbak Command
gbak -b -g -v -service localhost:service_mgr
  -user sysdba -password masterkey
  p:\employee.gdb p:\emp.gbk
      gbak Backup Switches
-b         Backup the database

-g         No garbage collection

-v         Display progress messages

-service   Use services API
    Backup From Batch File
gbak -b -g -v -service localhost:service_mgr -user
  sysdba -password masterkey p:\employee.gdb
  p:\emp.gbk 2> emplog.txt

Note that gbak writes to stderr, thus the 2 in front
 of the redirection operator (>)
        Sample Batch File
@echo off
echo Start================== >> emplog.txt
gbak -b -g -service localhost:service_mgr
-user sysdba -password masterkey
p:\employee.gdb p:\emp.gbk 2>> emplog.txt
date < enter.txt >> emplog.txt
time < enter.txt >> emplog.txt
echo End================== >> emplog.txt
echo . >> emplog.txt
            Backup Log
The current date is: Sat 05/08/2004
Enter the new date: (mm-dd-yy)
The current time is: 11:07:37.13
Enter the new time:
The current date is: Sat 05/08/2004
Enter the new date: (mm-dd-yy)
The current time is: 11:07:40.02
Enter the new time:
        Windows Scheduler
C:\> at 23:00 /every:m,t,w,th,f,s,su empbak.cmd
To schedule the backup on another machine:
C:\> at \\testserver 23:00 /every:m,t,w,th,f,s,su

You can also use the Scheduled Tasks applet in
Control Panel
   Making Backups Reliable
Backup output must be reviewed daily
Reviewer must sign a log book
The reviewer must have a backup
The log book must be signed by a manager
  Restoring a Backup
IBRestoreService component
Services API
Restoring with IBConsole
 From the menu choose
 Database | Maintenance | Backup/Restore |
Database Restore Dialog
Do not overwrite the exiting database
  – If the restore fails you have nothing
  – Restore to a different file name. If successful
    delete the old database and rename the file
     • Does not work with multi-file databases
   – Copy the database file using O/S copy then
     restore with overwrite
         Restore with gbak
gbak -r -p 4096 -service localhost:service_mgr
-user sysdba -password masterkey
p:\employee.gbk p:\employee.gdb
         Restore with gbak
Do not use the –validate option
It will slow the restore dramatically
It may cause the restore to fail
No validation is the default in 7.1
Before 7.1 you cannot disable validation
             O/S Backups
Backup the gbak backup file, not the database file
    – File may not be backup up if in use
    – File may be corrupt if backed up
If you must backup the database file with an O/S
   backup or copy program stop the IB server
   service first
            Forced Writes
Always run with forced writes on unless
   performance is unacceptable
If you must run with forced writes off use a
   dedicated server with a UPS and a stable
   operating system
Be aware that a server crash may corrupt the
   database because everything in the write cache
   will be lost
         Backup Strategies
Backup at least once per day
Store backup files on a different machine
Store backup files off site
Review backup output after every backup
Checking for Corruption
Use gfix

gfix -validate -full -no_update -user
sysdba -password masterkey
    Repairing Database with
gfix -validate -full -user sysdba -
password masterkey employee.gdb
-validate checks database structure
-full validates records
    – Takes longer
    – More thorough
Database Still Corrupt
Records could not be repaired
Use –mend then backup & restore

gfix -validate -full -mend -user
sysdba -password masterkey
Repairing with IBConsole
IBConsole does not offer all of the options
  available with gfix
Choose Database | Maintenance | Validate
  from the menu
Database Validation Dialog
         Hard Drive Failure
Maintain a live copy of the database on another
  – InterBase Shadowing
  – Operating system disk shadowing
  – RAID drive array
      InterBase Shadowing
Maintains two copies of the database
  simultaneously on two drives
Drive configuration
   – Drive 1 = operating system
   – Drive 2 = the database
   – Drive 3 = the shadow
Alternative drive configuration
   – Drive 1 = O/S & database
   – Drive 2 = the shadow
      InterBase Shadowing
+ Provides fast recovery if database drive fails
- Shadow must be on a local drive
- Software failure will corrupt shadow
- No point-in-time recovery
 InterBase Shadowing
Activate shadow with gfix

gfix -activate employee.shd -user
sysdba -password masterkey
Redundant Array of Inexpensive Disks
10 types
Only four are readily available
            RAID 0
Provides improved performance but no
  fault tolerance
Of no value in protecting your data
              RAID 1
Provides 100% data redundancy
Reboot is required if a drive fails
                   RAID 5
Striping plus Error Correction Code (ECC)
Fast reads, fast writes and fault tolerance
Supports hot swappable disks
                 RAID 10
Combines striping for high performance with
  mirroring for fault tolerance
Best combination of performance and fault
  tolerance for databases
+ Backup database can be on another machine
   on your network, even in another location
- Replicated DB is behind the master
- Imposes additional load on server
- Replicator may fall behind if load is heavy
- A network failure will stop replication
         Recovery Logging
Record all changes since last full backup in a
  separate file
To recover the database:
   – Restore from last full backup
   – Apply changes in recovery log
+ Provides point-in-time recovery
InterBase does not provide recovery logging
     Implementing Recovery
Logging must be under transaction control
It is impossible to write to an external file under
    transaction control
You must log to a database table
Logging to a table within the database defeats the
    purpose of logging since the log will be lost with
    the database
        A Hybrid Approach
Log to the database using triggers to write to the
  log table
Copy the logged information to an external file as
  soon as possible after the transaction commits
        A Hybrid Approach
Use IB database for external file
  – Logger can use two phase commit
    transaction to insert info into log database
    and delete from log table
  – Log database can be on another machine
    Triggering Log Copying
Use an after insert trigger on the log table to post
  an event to the log client
   – + Very little delay between change and
   – - High load on a busy database
Trigger logging at a preset interval
   – + Lower load on server
   – - Log will be further behind database
        Log Table Structure
One log table for each database table
   – Same structure as data table
   – Generated primary key
   – Column to indicate insert, update or delete
- Great care required to process log data in an
  order that does not violate referential integrity
        Log Table Structure
All changes for all tables recorded in two log
Log Master Structure
    – Sequence_Number
    – Table_Name
    – Action (insert, update, delete)
    – Where_Clause
       Log Table Structure
Log Detail Structure
  – Detail_Number
  – Sequence_Number (foreign key)
  – Column_Name
  – Type_Code
     • Integer number of column containing value
  – Value columns
     • One column for each data type
               Two Table Log
Log master gets one row for each insert, update
  or delete
Log detail table gets one row for each non-null
  column in each insert or update
This approach solves the RI sequencing problem

This structure was first suggested by Dalton Calford in
his paper “Some Solutions to Old Problems”
  Recovering Using the Log
Edit the log tables if necessary
Restore from last full backup
Run recovery program
  – Generate SQL statements from log
  – Execute statements
                  Thank You

Please fill out the speaker evaluation

    You can contact me further at …

To top