1117 Disaster Recovery with InterBase

1
1117Disaster Recovery with InterBase

• Bill Todd
• The Database Group, Inc.

2
Data Loss

• Physical loss
• Theft

3
Two Questions

• How much data can you afford to lose?
• What disasters do you want to to protect against?

4
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 location

5
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

6
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

7
Attitude

• 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

8
Reliability

• 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

9
Access

• Only the DBA has login rights to the database
  server
• 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 monthly

10
Access

• Dont run apps as SYSDBA
• Dont 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

11
Backups

• IBConsole
• Uses services API
• Gbak
• Services API
• Local backup
• IBBackupService component
• Uses services API

12
Backups

• 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

13
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

14
gbak Command

• gbak -b -g -v -service localhostservice_mgr
  -user sysdba -password masterkey p\employee.gdb
  p\emp.gbk

15
gbak Backup Switches

16
Backup From Batch File

• gbak -b -g -v -service localhostservice_mgr
  -user sysdba -password masterkey p\employee.gdb
  p\emp.gbk 2gt emplog.txt
• Note that gbak writes to stderr, thus the 2 in
  front of the redirection operator (gt)

17
Sample Batch File

• _at_echo off
• echo Start gtgt emplog.txt
• gbak -b -g -service localhostservice_mgr
• -user sysdba -password masterkey
• p\employee.gdb p\emp.gbk 2gtgt emplog.txt
• date lt enter.txt gtgt emplog.txt
• time lt enter.txt gtgt emplog.txt
• echo End gtgt emplog.txt
• echo . gtgt emplog.txt

18
Backup Log
Start The
current date is Sat 05/08/2004 Enter the new
date (mm-dd-yy) The current time is
110737.13Enter the new time
End .
Start The
current date is Sat 05/08/2004 Enter the new
date (mm-dd-yy) The current time is
110740.02Enter the new time
End .
19
Windows Scheduler
C\gt at 2300 /everym,t,w,th,f,s,su
empbak.cmd To schedule the backup on another
machine C\gt at \\testserver 2300
/everym,t,w,th,f,s,su empbak.cmd You can also
use the Scheduled Tasks applet in Control Panel
20
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 weekly

21
Restoring a Backup

• IBConsole
• gbak
• IBRestoreService component
• Services API

22
Restoring with IBConsole

• From the menu choose
• Database Maintenance Backup/Restore Restore

23
Database Restore Dialog
24
Caution

• 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

25
Restore with gbak
gbak -r -p 4096 -service localhostservice_mgr
-user sysdba -password masterkey
p\employee.gbk p\employee.gdb
26
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

27
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

28
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

29
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

30
Checking for Corruption

• Use gfix

gfix -validate -full -no_update -user sysdba
-password masterkey employee.gdb
31
Repairing Database with gfix
gfix -validate -full -user sysdba -password
masterkey employee.gdb

• -validate checks database structure
• -full validates records
• Takes longer
• More thorough

32
Database Still Corrupt

• Records could not be repaired
• Use mend then backup restore

gfix -validate -full -mend -user sysdba -password
masterkey employee.gdb
33
Repairing with IBConsole

• IBConsole does not offer all of the options
  available with gfix
• Choose Database Maintenance Validate from the
  menu

34
Database Validation Dialog
35
Hard Drive Failure

• Maintain a live copy of the database on another
  drive
• InterBase Shadowing
• Operating system disk shadowing
• RAID drive array

36
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

37
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

38
InterBase Shadowing

• Create with CREATE SHADOW
• Activate shadow with gfix

gfix -activate employee.shd -user sysdba
-password masterkey
39
RAID

• Redundant Array of Inexpensive Disks
• 10 types
• Only four are readily available

40
RAID 0

• Provides improved performance but no fault
  tolerance
• Of no value in protecting your data

41
RAID 1

• Mirroring
• Provides 100 data redundancy
• Reboot is required if a drive fails

42
RAID 5

• Striping plus Error Correction Code (ECC)
• Fast reads, fast writes and fault tolerance
• Supports hot swappable disks

43
RAID 10

• RAID 1 RAID 0
• Combines striping for high performance with
  mirroring for fault tolerance
• Best combination of performance and fault
  tolerance for databases

44
Replication

• 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

45
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

46
Implementing Recovery Logging

• 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

47
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

48
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

49
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 logging
• - High load on a busy database
• Trigger logging at a preset interval
• Lower load on server
• - Log will be further behind database

50
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

51
Log Table Structure

• All changes for all tables recorded in two log
  tables
• Log Master Structure
• Sequence_Number
• Table_Name
• Action (insert, update, delete)
• Where_Clause

52
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

53
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
54
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

55
Questions?
56
Thank You

• Please fill out the speaker evaluation
• You can contact me further at bt2_at_dbginc.com

57
(No Transcript)