Informix High Availability Features

Informix High Availability Features John F. Miller III

Backup and Restore New & Recent Features Backup Options Restore Options Best Practices High Availability Data Replication Archive Validation New and Recent HA Feature Overview

Recent Backup & Restore Features • Point in Time Restore of a table from a backup • Ontape 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 • Onbar Improvements • 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

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

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

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

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

Backup and Restore Tools ontape On-Bar External Backup and Restore archecker

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.

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

Extracts a table called test1:tlr from the most recent backup of dbspace1 and places the data in table test1:tlr Recovery of Lost Table 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;

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. Using External Tables 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;

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 Distributed Restore 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;

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

Databaseserver Physical Log Logical Logs Backup Media reserve pages Ontape onconfig ontape online log

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

Databaseserver sysutils onbar_d Backup Media OnBar Architecture onconfig onbar X B S A Storage Manager emergency Boot file activity log debug log online log

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

Flash Copy • Requires Storage System Capability • Use EBR technique to block server for copy Informix Host IDS IDS IDS on-bar Storage Spaces and logs FlashCopy Backups ISM Backup logs Production Server A

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

Remote Copy using PPRC

Best Practices • Planning • Planning • Planning

What is a Successful Recovery? • “Successful” recovery is defined by your business needs

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?

Right, Fast or Cheap? Choose Two!

Recovery Strategy Plan Recovery Goals Tune the Strategy Select Tools Analyze/Test the Strategy Implement The Strategy

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

Easy of administration and setup Reduced downtime using DR switchover Update interval Automatic client redirection using DBPATH Improved performance by using secondary as report server Independent of geographic location Indexes are locked when created on primary (till they are shipped to secondary) No Replication of Blobspace blobs (only) Non logged databases are not replicated HDR – Key points Benefits Limitations

HDR Setup Primary Secondary ontape –s –L 0 / onbar –b –L 0 onmode –d primary Server-B A B Logs ontape –p / onbar –r -p onmode –d secondary Server-A Primary 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

Log Buffers are transferred to Secondary B Users reading and updating database Running Reports HDR – Normal Operations Primary Secondary A Network Link For log Transmission Can be Updated Read-only

LogRecvr AcctTable AcctTable Logical Log Buffer Recovery Buffer drsecapply Reception Buffer HDR Buffer Logical Logs Written to Disk Logical Logs Written to Disk HDR – How it works DRINTERVAL Sets maximum time lag in seconds for HDR buffer transmission Set to ‘-1’ for synchronous. Primary Secondary

onmode –d standard B Running Reports HDR Availability - Failover Primary Down Secondary Standard A Read-only Users reading and updating database (Not Any More!!)

HDR Restart with NO Downtime B ontape -l ontape -p oninit -PHY onmode –d primary Server-A Recovery Secondary Down Primary Standard A onmode –d secondary Server-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

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

HDR Threads • Main thread • Primary: dr_prsend • Secondary: dr_secrecv • Ping thread • Primary: dr_prping • Secondary: dr_secping • Index thread • Primary: dr_idx_send (dr_btsend) • Secondary: dr_idx_recv(dr_btrecv)

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 Overview

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

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

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

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)

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 Other Recent HA Features

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

Questions

Informix High Availability Features