Transcript Informix Dynamic Server Backup and Recovery Best Practices

®
IBM Software Group
Informix High Availability Features
John F. Miller III
© 2005 IBM Corporation
IBM Software Group
Overview
 Backup and Restore
New & Recent Features
Backup Options
Restore Options
Best Practices
 High Availability Data Replication
 Archive Validation
 New and Recent HA Feature
2
IBM Software Group
Recent Backup & Restore Features


Point in Time Restore of a table from a backup
Ontape improvements






Onbar Improvements









No tape size
Large file support in ontape
Backup/restore using STDIO
Support of External Backup & Restore
New option to salvage log using ontape
More accurate space calculation for onbar backup
Reading and dumping logical log using onbar
Improved messages in bar activity log
Better handling of command line options in onbar
Sync Emergency boot file and sysutil database (onsmsync)
Progress Feedback
Re Directed Restore
Backup Verification
Parallel Log Cleaning
3
IBM Software Group
Backup Types
 Serial Backup
 Archives the entire system at a single point in time using only one data
stream
 Parallel Backup
 Archives the requested dbspace one at a time to N data streams
 External Backup
 Allows a third party application to backup the database server while
maintain logical consistency
4
IBM Software Group
Physical Backup

Level 0 (Full Backup)
 Backup of all used pages

Level 1 (Incremental Backup)
 Backup of all modified pages since last level-0 backup

Level 2 (Delta Backup)
 Backup of all modified pages since last level-1 backup

Level 1 and 2 backup is not supported using EBR
5
IBM Software Group
Overview
 Backup and Restore
New & Recent Features
Backup Options
Restore Options
Best Practices
 High Availability Data Replication
 Archive Validation
 New and Recent HA Feature
6
IBM Software Group
Restore Options

Cold Restore
 Restoring the server when the database engine is offline

Warm Restore
 Restore of dbspaces which occur while the database engine is online

Mixed Restore
 A cold restore of set dbspaces followed by a warm restore of other dbspaces

Logical Restore
 Follows physical restore
 Required
 After onbar restore from parallel backup
 Warm Restore
 After cold whole system restore it is optional
 Parallel logical recovery

ON_RECVRY_THREADS

OFF_RECVRY_THREADS
7
IBM Software Group
Restore Options

Point-in-time Table and/or column Level Restore

Imported Restore
 Transferring an archive taken on one computer and restoring it on a
second computer

Point-in-Time Restore
 Restoring the entire system to a single point in time

Suspended Restore
 Ability to continue the restore if a restore client fails

Restartable Restore
 Allows the DBA to pickup the restore from the failure point

Re-Directed Restore
 Allows chunk pathnames to be changed while doing the
restore
8
IBM Software Group
Backup and Restore Tools
ontape
On-Bar
External Backup and Restore
archecker
9
IBM Software Group
Point in Time Table Level Restore (PITTLR)
 Provide the customer with the ability to easily extract a set
of tables, a table or a portion of a table from a level 0
archive to a user specified point in time.
 The extracted data can be placed in an external table or on
a table on the server of the user’s choice regardless of
server version or machine type as long as the database
server is listed in the sqlhost file.
10
IBM Software Group
Benefits
 Extract a table or set of tables
 Filter the retrieved data
 Retrieve just a subset of columns
 Repartition the data
 Data may be placed in the same version database or on a
different database version with a different machine
architecture
11
IBM Software Group
Recovery of Lost Table
 Extracts a table called
test1:tlr from the most recent
backup of dbspace1 and
places the data in table
test1:tlr
database test1;
create table tlr (
a_serial
serial,
b_integer integer,
c_char
char(20),
d_decimal decimal
) in dbspace1;
insert into tlr
select * from tlr;
12
IBM Software Group
Using External Tables
 Extracts a table called
dl:source_tab from the most
recent backup of dbspace1
and sends the data in ASCII
format with fields delimited to
the file named /TMP/PIPE.
database d1;
create table source_tab
(cola int)
in dbspace1;
create external table
target_tab
(cola int)
USING (‘/TMP/PIPE’,
‘DELIMITED’);
insert into target_tab
select * from source_tab;
13
IBM Software Group
Distributed Restore
 Extracts a table called
test:source_tab from the
most recent backup of
dbspace1 and places the
data on the database server
rem_srv in the table
target_dbs:tlr_1
database target_dbs;
create table target_tab
( columns );
database test;
create table source_tab
( columns ) in dbspace1;
insert into
target_dbs@rem_srv.target_tab
select * from source_tab;
14
IBM Software Group
Ontape Features
 Backup at the Server level
 Support for incremental backups
 Manual or continuous logical log backup
 Restore entire system or single dbspace
 Backup is self describing
 All data backup is from the archive start time or before
 No longer required to set TAPESIZE
 Backup & Restore using STDIO
 Large file support in ontape
 Support of External Backup & Restore
 New option to salvage log using ontape
15
IBM Software Group
Ontape
onconfig
reserve pages
Database
server
ontape
Backup Media
Physical Log
Logical Logs
online log
16
IBM Software Group
On-Bar Features
 Parallel backup and restore
 System and dbspace level backup and restore
 Support for incremental backups
 Manual or automatic backup of logical logs
 Instance point-in time recovery
 Open interface for communication with storage managers (XBSA)
 Re-Directed Restore
17
IBM Software Group
OnBar Architecture
onbar
Storage
Manager
X
B
S
A
onconfig
Database
server
onbar_d
Backup Media
debug log
activity log
emergency
Boot file
sysutils
online log
18
IBM Software Group
External Backup & Restore (EBR) Features
 External Backup and Restore
 EBR allows administrators to make a consistent copy of their
dbspaces using external tools
 Used with many 3rd party backup products
 Allows for both cold and warm restores
 Basic Steps
 Block server at checkpoint
 Backup dbspace(s) using third party tools
 Unblock server
19
IBM Software Group
Flash Copy
 Requires Storage System
Capability
Informix Host
IDS
IDS
IDS
 Use EBR technique to
block server for copy
on-bar
Storage Spaces and logs
ISM Backup logs
FlashCopy
Backups
Production Server A
20
IBM Software Group
Remote copy using PPRC
 Requires Storage System Capability
 Uses EBR technique to block server for copy
 Makes a flash copy on local storage
 Establishes PPRC relationship between local and remote site
 Makes multiple flash copy on remote site
21
IBM Software Group
Remote Copy using PPRC
I n fo r m i x H o s t
IDS
IDS
IDS
o n -b a r
Fla s h Co p y D
Fla s h Co p y E ...
XBSA
St o r a g e Lo g i c a l
Lo g s
Sp a c e s
I SM
Lo g s
Fla s h Co p y
P r o d u c t io n
Se r ve r A
Fla s h Co p y
P P RC
P P RC Co p y C
Fla s h Co p y B
22
IBM Software Group
Overview
 Backup and Restore
New & Recent Features
Backup Options
Restore Options
Best Practices
 High Availability Data Replication
 Archive Validation
 New and Recent HA Feature
23
IBM Software Group
Best Practices
1.Planning
2.Planning
3.Planning
24
IBM Software Group
What is a Successful Recovery?

“Successful” recovery is
defined by your business
needs
25
IBM Software Group
Goals For Recovery
 Determine acceptable recovery time
 How long can your business function without the data?
 How long can your production system be down during a restore?
26
IBM Software Group
Right, Fast or Cheap?
Choose Two!
27
IBM Software Group
Recovery Strategy
Plan Recovery
Goals
Tune the
Strategy
Analyze/Test
the Strategy
Select
Tools
Implement
The Strategy
28
IBM Software Group
Overview
 Backup and Restore
New & Recent Features
Backup Options
Restore Options
Best Practices
 High Availability Data Replication
 Archive Validation
 New and Recent HA Feature
29
IBM Software Group
What Is HDR (High Availability Data Replication)?
 Two identical servers on two identical machines
 Primary server
 Fully functional server
 All database activity – insert/update/deletes, are performed on
this instance
 Automatically sends logs to secondary server
 Secondary server
 Read only server : allows read only query
 Always in recovery mode
 Receives logs from primary and replay them to keep in sync with
primary
 When Primary server goes down, secondary server takes over
as Standard server
30
IBM Software Group
HDR – Key points
Benefits

Easy of administration and setup

Reduced downtime using DR
switchover
Limitations
 Indexes are locked when created
on primary (till they are shipped to
secondary)

Update interval
 No Replication of Blobspace blobs
(only)

Automatic client redirection using
DBPATH
 Non logged databases are not
replicated

Improved performance by using
secondary as report server

Independent of geographic location
31
IBM Software Group
HDR Setup
Primary
Secondary
ontape
onmode
–s–d
–Lprimary
0 / onbar
Server-B
–b –L 0
A
B
Logs
Primary
onmode
ontape
–d –p
secondary
/ onbar –r
Server-A
-p
1) Backup made of primary server
2) Notify primary server identity of secondary
3) Physical restore backup on secondary server
4) Notify secondary server identity of primary
5) Logical log transmission begins
32
IBM Software Group
HDR – Normal Operations
Primary
Log Buffers are
transferred to
Secondary
Secondary
A
Network Link
For log Transmission
Can be
Updated
B
Users reading and
updating database
Read-only
Running
Reports
33
IBM Software Group
HDR – How it works
AcctTable
Logical
Log
Buffer
drsecapply
Logical Logs
Written to Disk
Primary
LogRecvr
AcctTable
HDR
Buffer
Reception
Buffer
DRINTERVAL
Sets maximum time lag in
seconds for HDR buffer
transmission
Recovery
Buffer
Logical Logs
Written to Disk
Secondary
Set to ‘-1’ for synchronous.
34
IBM Software Group
HDR Availability - Failover
Primary
Down
onmode –d standard
Secondary
Standard
A
B
Users reading and
updating database
(Not Any More!!)
Read-only
Running
Reports
35
IBM Software Group
HDR Restart with NO Downtime
Secondary
Recovery
Down
onmode –d primary Server-A
ontape -p
ontape
-l
oninit
-PHY
Standard
Primary
A
onmode –d secondary Server-B
B
1) Switch Server-B to primary mode
2) Physical restore of failed system (if media failure)
or restart Server-A to last checkpoint time
3) Notify Secondary server-A identity of Primary server
4) Possibly restore offline/backed up logs
5) Automatically transfers current logs from primary and resume
36
IBM Software Group
HDR Configuration Parameters
 DRINTERVAL
 Also decides sync or non-sync configuration
 DRTIMEOUT
 Actual value is 4 times of this
 DRAUTO
 Controls how the secondary should changes modes
 DRLOSTFOUND
37
IBM Software Group
HDR Threads

Main thread



Ping thread



Primary: dr_prsend
Secondary: dr_secrecv
Primary: dr_prping
Secondary: dr_secping
Index thread


Primary: dr_idx_send (dr_btsend)
Secondary: dr_idx_recv(dr_btrecv)
38
IBM Software Group
Overview
 Backup and Restore
New & Recent Features
Backup Options
Restore Options
Best Practices
 High Availability Data Replication
 High Availability Data Replication
 Archive Validation
 New and Recent HA Feature
39
IBM Software Group
Validating Archives

Does not have to be run on the
same computer as the archive
was taken

Uses very little space ~50MB

Both onbar and ontape
40
IBM Software Group
Validation
 onbar -v [ other options ]
 Are my backups usable?
 Run a verification before you need to restore
 After a restore fails
 Get some idea why the restore failed
 Perhaps the data was inconsistent
 Perhaps the data was incomplete
41
IBM Software Group
What is actually validated
 Format of each page on the archive is check (similar to oncheck -cd)
 Tape control pages are sanity check
 Each table is checked ensuring all pages of the table exist on the archive
tape
 Reserve page format is validated
 Each chunk free list is verified
 Table extents are checked for overlap
42
IBM Software Group
Example of Archive Validation
Backup Validation
onbar -r -v
(IDS 7.3)
onbar -v
(IUS 9.X)
Sample Output:
1999-06-28 105444 23595 23594 /spare2/9.20/bin/onbar_d -v
…Successfully connected to Storage Manager.
…Begin backup verification of level 0 for rootdbs (Storage Manager copy ID 102).
…Completed level 0 backup verification successfully.
…/spare2/9.20/bin/onbar_d complete, returning 0 (0x00)
43
IBM Software Group
Overview
 Backup and Restore
New & Recent Features
Backup Options
Restore Options
Best Practices
 High Availability Data Replication
 Archive Validation
 New and Recent HA Feature
44
IBM Software Group
Other Recent HA Features
 Fast Restart
Ensure logical recovery is limited in
time
 External Directives
The ability to add directives to SQL
statements without modifying the
program
 Re-Send Index
In an HDR environment copy an
index from the primary to correct a
corrupted index on the secondary
 Drop and Create an index online
Dropping or creating an index
without locking the table while user
are modifying the table
 Redundant Network Listeners
Multiple listener can now monitor
a single network port
 Single User Mode
An administration mode in which
only user informix can connect to
the database
 HDR setup using EBR
Using External Backup and
Restore to setup HDR
 ER auto resync
Automatically resync or repair
tables in ER
45
IBM Software Group
Other HA Features
 Database mirroring
 Thread suspension
 Online configuration tuning
 Online index checking
 Dynamic lock table allocation (avoiding out of locks)
 Event Alarms – automatic notification of database events
 Enterprise Replication
 InPlace Alter Table
46
IBM Software Group
Questions
47