Informix Tutorials-Informix Backups And HDR

Advanced DataToolsWebcastfrom the IBM Informix ChampionsInformix TutorialInformix Backup, Recoveryand High Availabilityby Lester KnutsenThursday, May 28, 2020 2:00pmEDT

Lester KnutsenLester Knutsen is President of Advanced DataToolsCorporation and has been building large datawarehouse and business systems using InformixDatabase software since 1983. Lester focuses onlarge database performance tuning, training, andconsulting. Lester is a member of the IBM GoldConsultant program and was presented with one ofthe Inaugural IBM Information Champion awards byIBM. Lester was one of the founders of theInternational Informix Users Group and theWashington Area Informix User ols.com703-256-0267 x1022

Webcast Guidelines The Webcast is being recorded. TheWebcast replay and slides will beavailable in a few days. Please Mute your line. Backgroundsounds will distract everyone. Use the Chat Button in the upper rightto ask questions.3

Informix TutorialInformix Backup, Recoveryand High Availabilityby Lester KnutsenHow do you backup and restore and Informix Server? HighAvailability is crucial to a successful database. We will coverdatabase backups, restores, and setting up High Availability DataReplication (HDR).

Agenda Informix Backup OptionsLogical Log BackupsInformix Server BackupsTesting BackupsInformix High Availability OptionsSetting up Informix HDRInformix Backup HDR5

Examples and Demos Logical Log BacksOntape BackupOntape RestoreOnArchive Backup CheckHDR SetupHDR Switch6

Informix Backup Options

Informix Backup Options Database Backup and Restore– dbexport and dbimport– onunload and onload (binary) Table Backup and Restore––––SQL unload and loadonunload and onload (binary)High Performance Loader (HPL) or External Tablesarchecker for table level restore Informix Server Backup and Restore– ontape– onbarInformix Backup HDR8

SQL Unload and Load The Unload and Load Statement are SQL Extensions to Informix inDbaccess and ISQL used to unload/load a table or part of a tableCreates ASCII or text file of the data in a table with a field delimiterPortable to other systems, even non-Informix databasesCan be used with select statements to unload some fields (list fields tounload) or some rows (add a where clause)Blob data will be unloaded as hexadecimal dataSyntax to unload a table to a file:unload to filename select * from tablename;Syntax to load a table from a file:load from filename insert into tablename;Informix Backup HDR9

Dbexport and Dbimport To unload a whole database Creates ASCII files with all the data and theschema Portable to other systems Locks the database during an export Database may be exported to a directory ortapeInformix Backup HDR10

Onload and Onunload To create a binary unload of a whole database or onetable Can only be loaded on the same machine type andInformix version Loads and unloads disk pages, does not compressrows Locks database/table during unload May be unloaded to tape or fileInformix Backup HDR11

Onbar and OntapeOn-line backup of whole server, selected dbspaces, and logicallogs. Use this tool to backup your entire server on a regularbasis. Creates a binary unload of the server. Can only berestored on a system with exactly the same operating systemversion, configuration, dbspaces, ONCONFIG, and version ofInformix.Loads and unloads disk pages, does not compress rows.Does NOT lock database or tables during backup. Users cancontinue to access the system.Supports archive levels:0 - full system1 - only disk pages changed since last 02 - disk pages changes since last 1Informix Backup HDR12

Onbar and OntapeTaskBack up all database server data?Back up logical-log files?Perform continuous logical-log backups?Perform continuous logical-log restore?Back up while the database server is online?Back up while the database server is in quiescent mode?Restore all database server data?Restore selected storage spaces?Back up and restore storage spaces serially?Perform cold restores with the database server offline?Initialize high availability data replication?Perform separate physical and logical restores?Rename a chunk path name or device during a cold restore?Perform imported restores?Perform external backups and restores?Transform data with external programs?Encrypt or decrypt a storage space during a restore?Informix Backup syesyesyesyesyes13

Onbar and OntapeDifferencesTaskUse a storage manager to track backups and storage media?Back up selected storage spaces?Restore data to a specific point in time?Back up and restore different storage spaces in parallel?Use multiple tape drives concurrently for backups and restores?Restart a restore?Monitor performance?Change logging mode for databases?Back up to or restore from cloud storage?Onbar sSource: IBM Knowledge CenterComparison of the onbarand ontape ter/en/SSGU8G 14.1.0/com.ibm.bar.doc/ids bar 177.htmInformix Backup HDR14

Onbar and Ontape Choice Use Onbar– Have a very large Server and need parallel backups toreduce backup time– Use a storage manager with multiple tape drivesconcurrently for backups and restores– Require point in time restore capabilities Use Ontape– Simple and easy to setup– No parallel capabilities– Supports remote backup to other servers and cloudDo not Mix using Onbar and OntapeInformix Backup HDR15

InformixLogical Log Backups

Backing Up Logical LogsWebcast Replay: orial-managing-informix-logs/Informix Backup HDR17

Informix Server Backups

Important - Saving ConfigurationInformationThe Informix Server system configuration must be documentedso it can be rebuilt from scratch. The DBA must have copies of: ONCONFIG file: ( INFORMIXDIR/etc/ ONCONFIG)SQLHOSTS file: ( INFORMIXDIR/etc/sqlhosts or INFORMIXSQLHOSTS)TCP/IP Services and hosts files (/etc/services, /etc/hosts.equiv and /etc/hosts)Disk space layout (onstat -d)Disk partition layout or logical volume layoutLinks between dbspaces and disk partitions Databases schema(s)When using Onbar you will need the Emergency Boot file (ixbar)Informix Backup HDR19

Informix Server BackupLevels 0 – Full Server Backup 1 – Backup all pages changed since thelast level 0 2 – Backup all pages changed since thelast level 1Informix Backup HDR20

Informix Server Backups Recommend full daily level 0 archiveswhenever possible Requires naming and labeling all files ortapes and coordinating with Logical LogbackupsInformix Backup HDR21

Backing up the InformixServerONCONFIG entries define the Backup DeviceTAPEDEVTAPEBLKTAPESIZE/dev/tapedev1610240# Tape device path# Tape block size (Kbytes)# Maximum amount of data to put on tape (Kbytes)TAPEDEV Options: /dev/null – performing a backup will reset internal parameterswithout performing an actual backup /dev/rmt0 – Full Backup to tape /backup/ INFORMIXSERVER/archive – backup to a directory /backup/ INFORMIXSERVER/filename.ontape – backup to a fileInformix Backup HDR22

Backing up the Server toDisk The entire backup must fit on one file system Must then be backed up to tape usingStorage Manager or Unix tape utilities(tar,cpio,dump) Backing up to a Directory on a File SystemInformix Backup HDR23

Figure – Informix backup to diskInformix Backup HDR24

Restoring Informix Server Restore Configuration FilesRe-Create dbspace links and devicesOntape RestoreRestore and roll forward Logical LogsInformix Backup HDR25

Restore the ConfigurationFiles Restore INFORMIXDIR ONCONFIG File( INFORMIXDIR/etc/ ONCONFIG) Tape device configuration SQLHOSTS File( INFORMIXDIR/etc/sqlhosts) TCP/IP services and hosts files (/etc/services,/etc/hosts.equiv and /etc/hosts)Informix Backup HDR26

Re-Create Chunk DevicesLinks Re-create orginal disk space (onstat -d) Re-create orginal disk partition layout orlogical volume layout Re-create links between dbspaces anddisk partitionsInformix Backup HDR27

Ontape Restore Informix Server must be off-line(onmode -ky) Restore command - ontape -rInformix Backup HDR28

Figure – Informix Restore from diskInformix Backup HDR29

Figure – Informix Restore from diskhyW t?t anptrmo porP imyKe thisisInformix Backup HDR30

Ontape Restore (continued)Restore prompts:1) If Logical Logs are available, ontape will prompt youto save them so that they can be used to roll forwardafter the restore.2) After the restore, ontape will prompt for a level 1 or 2archive to restore over the level 0 archive.3) After restoring all archives, ontape will prompt for anylogical log tapes that need to be restored.If the restore is successful, Informix Server will go toquiescent mode.Informix Backup HDR31

Ontape Backup to STDIO Backup to standard output or a pipe toanother program– Used to compress backup files– Used to encrypt backup filesontape -s -L 0 -t STDIO newbackup.ontapeontape -r -t STDIO newbackup.ontapeontape -s -L 0 -t STDIO gzip newfile.gzipInformix Backup HDR32

External Backups External Backups are when you use anotherNon-Informix program to backup your data. Examples– VM Snapshots– SAN Disk Cloning– Unix dd or cpio commandsImportant to make sure all data structures inmemory are Synced to diskInformix Backup HDR33

External Backups StepsImportant to make sure all data structures inmemory are Synced to disk1. Block the Informix Server so the data structures ondisk are not changing– Onmode –c block2. Perform your backup using the other program3. Unblock the Informix Server– Onmode –c unblockInformix Backup HDR34

Testing Informix Backups

ARChecker Validate a backup tape from Ontape orOnbar Can run on a different machine Allows extract of a specific table fromthe archiveInformix Backup HDR36

Archecker c config.std#Description:#Default ac config.std for archecker archive *********************************AC MSGPATH/tmp/ac msg.log# archecker message logAC STORAGE/tmp# Directory used for temp storageAC VERBOSE1# 1 verbose messages 0 terse messagesAC TAPEDEV/dev/rmt/0# Must match TAPEDEV in onconfigAC TAPEBLOCK64# Must match TAPEBLK in *********************************Informix Backup HDR37

Archecker Configuration AC STORAGE - This is the name of the directory where archecker temporaryfiles are kept. The amount of space required will be determined by the numberof chunks and the number of tables. You will need a lot of free space in thisfilesystem. To estimate, I recommend having 1MB of free space for every 2GBof dbspace on your system. If this directory is not set, it will default to yourcurrent directory. AC MSGPATH - Location and pathname of archecker's message log. All errorand status messages will be placed in this file. AC TAPEDEV - The name of the tape device to be used for reading andchecking the archive. AC TAPEBLOCK - The size of the tape block in KB. It must match theblocksize from the ONCONFIG file used for the archive. If it does not match, youwill get an error that will indicate the correct blocksize to use.Informix Backup HDR38

Archecker CommandOptionsarchecker -b -D -d -R -F -v -s -t -T -V-bDirect XBSA access-DDelete old files from previous run and exit-dDelete old files from previous run-FRetrieve list of pages off the archive-PRead Performance info only-RRestart the checking-sPrint status message to the screen-tRead the tape directly-TRestart at a specific tape-vVerbose mode, print dots to the screen for every 25MBof data-VDisplay versionInformix Backup HDR39

Using Archecker Command to test backups on the samemachine that a backup was made on:archecker -tdsvInformix Backup HDR40

Using ArcheckerInformix Backup HDR41

Using ArcheckerInformix Backup HDR42

Informix High AvailabilityOptions

Types of Informix DataReplication High Availability Data Replication –HDR and RSS Enterprise Replication – ER or CDR Shared Disk Replication Cluster – SDSor MACH Continuous Log Restore Flexible Grid – Cluster of ServersInformix Backup HDR44

Replication for Disaster Recovery Protect data from catastrophic loss ofphysical resources Ensure user experience under extremeconditions Ensure organizational survivalInformix Backup HDR45