Backup and Restore utilities

1. Backup and Restore utilities Information Management Partner Technologies

2. Agenda Overview of Backup and Restore Ontape utility Onbar utility Appendix 2

3. Concept of Backup

4. Types of Backups • Server sends raw online pages just like they exist on disk • FULL • Usually considered a complete backup of all devices to a known point of consistency • Contains the physical image of device pages, logical logs, and any updates occurring during the backup • Might be run every day, or weekly depending upon needs • INCREMENTAL • A backup of any changes since the last full backup • Usually faster than full backups as only a portion of the instance is saved • Incremental backups will gradually get longer as more changes are made, and a full backup is not done

5. Incremental Backups Level 1 Level 2 Level 0 • A level 0 backup contains a copy of all data on the server as of the time the backup started • A level1 backup contains a copy of all data on the server that has changed since the last level 0 backup • Generally faster than a level 0 backup • A level 2 backup contains a copy of all data on the server that has changed since the last level 1 or level 0 backup • Generally faster than a level 0 and level 1 backup

6. Logical-Log Backups Log 1 Log 2 …… A logical-log backup is the process of copying the contents of a logical log file to secondary storage media. This allows the logical log to be reused. Log n • Without logical-log backups, you can only recover a failed system from full backups • Logging occurs even in non-logging databases • In order to successfully complete a logical restore, the logical-log backup must have been created with the same backup utilityused to create the physical backup

7. Logical Log Contents • The logical logs store several types of transaction records • Each logical-log record has I information • Depending on the record type, additional information may also be stored

8. When are Logs Backed Up? • On-Demand • Administrator or operator issues a command • All used logical logs are backed up • Automatic • Triggered by ALARMPROGRAM • Logical logs are backed up when a log full event is triggered • Continuous • Running process backs up each logical log as it fills Restoring logical log records is slow compared to restoring pages from a dbspace backup. To minimize the number of logical log records that must be applied during a recovery, create frequent dbspace backups.

9. Physical and logical Restore

10. Types of Restores • Cold • A restore that occurs when the root dbspace or a dbspace that holds the physical or logical logs is inaccessible • Instance is offline • Warm • A restore that occurs when the root dbspace and the dbspace(s) containing the physical and logical logs are accessible • The server must be in online, quiescent or single-user mode • Mixed • Cold restore followed by a warm restore

11. Cold Restore

12. Warm restore

13. Log Salvage

14. Archecker – Table-Level Restore • Utility to restore or copy data • Restore data • Use the archecker utility to restore a specific table or set of tables that have previously been backed up with ON-Bar or ontape • These tables can be restored to a specific point in time • This is useful, for example, to restore a table that has accidentally been dropped • Copy data • The archecker utility can also be used as a method of copying data • For example, you can move a table from the production system to another system

15. Backup and Recovery strategy

16. ontape • The size of the buffers used to transmit data - Controlled by TAPEBLOCK config param • Simple to set up and run • Serial backup of dbspaces • Whole system backup only • Restore entire system or single dbspace • STDIO (Informix 10 feature) • Back up to standard output (stdout) and restore from (stdin) • Not interactive, easier to automate, introduces flexibility • ontape -t option can be used to override TAPEDEV • IFX_ONTAPE_FILE_PREFIX • An environment variable that ontape will use to change the prefix (<hostname>_<servernum>) portion of the backup file name(s)

17. ontape - Threads Scanner Thread (arcbackup1) Backup Thread (ontape) • Started by backup client • Initiate the backup process • Request a checkpoint • Blocks server for archive • Prepares list of pages for backup • Forks two other backup threads • Interacts with backup client • Sends buffers to client for backup • The thread, geared for I/O performance • Handed a list of pages to backup • Scans data from disk into shared memory buffers • Makes NO decisions about the data • Ensures the page address is correct (format) Before Image Processor Thread (arcbackup2) • Monitors the before image queues • Responsible for collecting all the images that are modified during the archive • Drains the before image memory queue, by storing the page images into temp tables • Can create multiple temp tables for a single dbspace if required

18. Configuration parameters for backup and recovery ontape • TAPEDEV -- device path for backups. To use standard I/O, set to stdio. • TAPEBLK -- media block size, in KB, for backups • TAPESIZE – media capacity for a backup tape. Acceptable values are 0 (unlimited) or \ • any positive integral multiple of TAPEBLK • LTAPEDEV -- device path for logical log backup device • LTAPEBLK -- media block size, in KB, for logical log backups • LTAPESIZE – media capacity for a logical log backup tape. Acceptable values are 0 (unlimited) or any positive integral multiple of LTAPEBLK Introduction to Informix Dynamic Server 18

19. Backup and Restore options of ontape

20. onbar • Requires storage manager • Communicates with the Storage Manger via XBSA Interface • Able to support a variety of XBSA compliant storage managers • Parallel backup and restore of storage spaces • Point-in-time Restore • Restartable Restore • Unattended operations – need to look at BAR_ACT_LOG • Integrated archive validation – onbar -v • Filtering during Backup and Restore (IDS 11) • Automating Backups • New options with IDS 11 with SQL Admin, Database Scheduler, and OAT

21. onbar - Archive or Restore Model • The archive is broken down into archive jobs with each dbspace being its own backup • An onbar_d thread is started to backup a single dbspace • Connects to database server and Storage manager requesting the backup session • Updates sysutils and ixbar file

22. onbar - Performance and Parallelism • ON-Bar uses buffers, called transport buffers, to receive or transmit data to the storage manager • BAR_XFER_BUF_SIZE • Size of the buffers • Default = 31 or 15 (2K / 4K port) • BAR_NB_XPORT_COUNT • Number of buffers (3-99) • Default 10 • BAR_MAX_BACKUP • Number of backup processes • Default 4 Significantly affects ON-Bar performance

23. onbar - Performance Factors • BAR_MAX_BACKUP • onbar will fork as many processes up to the value set (parallelize operation) • If set to 1, operation is serialized • If set to 0, the parallelism will be unlimited except for OS limits • BAR_NB_XPORT_COUNT • Applies to each onbar backup stream • Increase the amount of virtual memory “buckets” used • If there are more buckets to fill with data, then less waiting for buckets to become empty • Virtual Memory used ((BAR_NB_XPORT_COUNT * BAR_XFER_BUF_SIZE * page_size) + 5 MB) * # of dbspaces archived in parallel • BAR_XFER_BUF_SIZE • Max value: 15 pages (4k pagesize) & 31 pages (2k pagesize); max 64KB • Do not change - If you change the transport buffer size, you may invalidate all your existing backup objects!

24. Emergency Boot File “ixbar” • Located in $INFORMIXDIR/etc/ixbar.<servernum> • Contains an entry for each backup object • Replaces sysutils database when server is off-line during restore

25. onbar Restores • Cold Restore (onbar -r) • Restore started with database server off-line. • Required when restoring critical dbspaces (root dbspace and any dbspace containing physical or the logical logs). • Warm Restore (onbar -r --> restores only “down” dbspaces) • Restore of non-critical dbspaces started with database server on-line (or in quiescent mode) • Mixed Restore • Restore critical dbspaces using cold restore to bring the instance on-line, then use warm restore to restore non-critical dbspaces. • Point-in-Time Restore (onbar -r -t “2008-08-22 14:12:00”) • Must back up logs. • Can restore the entire system to a specific log or to a point-in-time.

26. onbar Restores • Whole-System Restore • Requires a whole system backup. • Only type of restore that can be done without applying logical logs. • onbar -r -w • Can be done in parallel starting in IDS 11 • Restore in stages • Physical restore command, then logical restore command • onbar –RESTART • Must have had RESTARTABLE_RESTORE set to ON (default) in the ONCONFIG before you started the restore • If the failure occurred during a physical restore (warm or cold), restarts the restore at the storage space and level where the failure occurred • If a failure occurred during a cold logical restore, restarts the logical restore from the most recent log checkpoint • Restartable logical restore is supported for cold restores only

27. onbar - BAR_MAX_BACKUP • Example: BAR_MAX_BACKUP 4 • Means 4 onbar processes running at the same time • Means 4 Backup Sessions going on in parallel in database server • Means 4 sets of transport buffers created in database server • BAR_NB_XPORT_COUNT 20 - Each set of transport buffer has 20 buffers • BAR_XFER_BUF_SIZE 31 - Each transport buffer holds 31 BASE page sizes (2K * 31 = 62K) • BAR_XFER_BUF_SIZE 15 - Each transport buffer holds 15 BASE page sizes (4K * 15 = 60K)

28. Parallel Whole System Backups and Restores NEW in IDS 11 • Root dbspace is still backed up first and by itself • Then only are the rest of the dbspaces backed up • Parallelism is controlled by BAR_MAX_BACKUP • If BAR_MAX_BACKUP is set to 1 (one), then onbar -b -w performs a serial backup. • If BAR_MAX_BACKUP is set to an integer value greater than 1, then onbar -b -w performs a parallel backup • Only one archive checkpoint for all dbspaces at beginning of backup • Regular backups perform a separate checkpoint for each space archived • Whole system backups can be restored to a consistent point without any explicit logical log backups and restores

29. Monitor Backup and Restore – Server Side • Logical log status: onstat –l • Backup logical logs using ontape/onbar –b -l before starting an archive • Storage space status: onstat –d • Ensure flags for dbspaces and chunks are “normal” prior to a backup • Backup status: oncheck –pr • Provides the same information as onstat -g arc but can be run when the system is offline • Buffer queuing status: onstat -g stq • Print stream queue information • Backup thread status: onstat -garc • Display information about the last committed archive for each dbspace and also information about any current ongoing archives

30. onstat -g arc • Before-image bin is a temporary table created in a temporary/root dbspace Before-image queue list size and length Dbspaces - Ongoing archives num DBSpace Q Size Q Len Buffer partnum size scanner Current-page 2 dbspace1 92 0 4 0x100085 240 0x2033ee 2:118 3 dbspace2 69 0 1 0x100084 150 0x302f1a 3:4201 Dbspaces - Archive Status name number level date log log-position rootdbs 1 0 10/04/2001.10:17 5 0x10b608 dbspace1 2 0 10/04/2001.10:17 5 0x10b608 dbspace2 3 0 10/04/2001.10:17 5 0x10b608 sbspace1 4 0 10/04/2001.10:17 5 0x10b608 sbspace2 5 0 10/04/2001.10:17 5 0x10b608 Current Page being Archived Name and number of the dbspace Archive Level Date and time of the last archive

31. onstat -g stq – Monitor movement of Data • Shows if the archive client or archive server is running faster • Inconsistent performance is an indication that more memory buffers can improve performance Stream Queue: (session 10 cnt 10) 0:ac8d400 1:ac9d400 2:acad400 3:acbd400 4:accd400 5:acdd400 6:aced400 7:acfd400 8:ad0d400 9:ad1d400 Full Queue: (cnt 9 waiters 0) 0:ac9d400 1:acad400 2:0 3:accd400 4:acdd400 5:aced400 6:acfd400 7:ad0d400 8:ad1d400 Empty Queue: (cnt 0 waiters 1) Chunk Number: Physical Address Session ID Number of stream queue buffers Number of threads waiting for the stream queue buffer • Buffers/work by server • Buffers being filled by the archive client

32. The On-Bar Activity Log • Contains a log of ON-Bar activity • BAR_ACT_LOG configuration parameter • Specifies a file name and location • Logs • Status, warnings and error messages • List of dbspaces/blobspaces and logical logs included in a backup/restore • View Log onbar –m • Format PROCESSID PARENT PROCESS ID TIMESTAMP MESSAGE

33. OnBar vs. OnTape 33

34. 34

35. HDR Traffic HDR Secondary Primary Client Apps Blade Server A <New Orleans> Building-A Blade Server B <Memphis> High-Availability Data Replication (HDR) • Use • Disaster Recovery • Two identical servers on two identical machines • Primary server • Secondary server • Primary server • Fully functional server. • All database activity – insert/update/deletes, are performed on this instance • 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 Benefits • Simple to administer • Little configuration required • Just backup the primary and restore to secondary Read-Only

36. High-Availability Data Replication (HDR): Easy Setup • Requirements: • Same hardware (vendor and architecture) • Logged databases • Same storage paths on each machine • Backup the primary system • ontape –s –L 0 • Set the type of the primary • onmode –d primary <secondary_server_name> • Restore the backup on the secondary • ontape -p • Change the type of the secondary • onmode –d secondary <primary_server_name> • DONE!

37. Remote Standalone Server (RSS) Replication to Multiple Remote Secondary Nodes • Extends HDR to include new type of secondary (RSS) • Can have 0 to N RSS nodes • Use • Capacity Relief • Web Applications / Reporting • Ideal for disaster recovery • Supports Secondary - RSS conversions (onmode) • RSS can be used in combination with HDR secondary • RSS can be promoted to HDR secondary • HDR secondary can be converted into RSS Primary Node HDR Secondary • Allows for simultaneous local and remote replication for HA • Support read-write operations • Simple online setup and use BENEFITS RSS #1 RSS #2 • Similarities with HDR secondary node • Receive logs from Primary • Has its own set of disks to manage • Primary performance does not affect RSS servers; and vice versa • Created by performing a backup/restore of the instance • Differences with HDR • Can only be promoted to HDR secondary, not primary • Can only be updated asynchronously • Only manual failover supported • Uses full duplex communication (SMX) with RSS nodes • Does not support SYNC mode, not even for checkpoints • Requires Index Page Logging be turned on

38. Remote Secondary Server (RSS): Easy Setup • Requirements: (similar to HDR) • Same hardware (vendor and architecture) • Logged databases • Same storage paths on each machine • Configuration: • Primary:LOG_INDEX_BUILDS: Enable index page logging • Dynamically: onmode –wf LOG_INDEX_BUILDS=1 • Identify the RSS server on the primary • onmode –d add rss <server_name> • Backup the primary system • ontape –s –L 0 • Restore the backup on the secondary • ontape -p • Identify the primary on the RSS server • onmode –d rss <primary_server_name> • DONE!

39. onstat -g stq – Queues • Buffer Queue • Empty Queue • Between client and ontape thread • Full Queue • between ontape and arcbackup1 thread • Archive before image Queue • Between sqlexec thread and arcbackup2 • Before image is put into Queue while physical logging

40. Common Backup and Restore Problems • Storage Manager • Timeout of onbar • Error 131 Object not found • Salvaging logs and getting wrong object • Restore • System appears to be hung because: • The tape is done • onstat -D shows no I/O • Very little CPU activity • System is clearing the physical and logical logs!!! • Storage Space • Check that the devices are linked properly • KAIO only uses raw I/O • Overlapping data Check Informix message log Clearing the physical and logical logs has started Cleared 2100 MB of the physical and logical logs in 612 seconds

41. Archive Validation • For ontape backups, backup validation is done by standalone archecker utility • For onbar, archecker backup validation is integrated with onbar --use the onbar -v option • What is checked? • Format of each page on the archive is checked • Tape control pages are sanity checked • Each table is checked ensuring all pages of the table exist on the archive • Reserve page format is validated • Each chunk free list is verified • Table extents are checked for overlap (oncheck -pe)

42. Table-Level Point-In-Time-Restore • Provide the customer with the ability to extract a set of tables, a table or a portion of a table from a level 0 archive (and logical logs) to a user specified point in time • The extracted data can be placed in a file (external table) or in a table on any database server listed in the sqlhosts file (local or remote database server) • This is not a backup and restore method, but the ability to restore an archived table in situations --you’ve accidentally dropped a table, deleted rows from a table, or truncated a table • For performance, think in terms of insert into ... select from an archived table archecker -tdvs -f schema_cmd.txt (for restore from ontape backup) archecker -bdvs -f schema_cmd.txt (for restore from onbar backup)

43. Tuning Informix UtilitiesPerformance Tuning Bootcamp Information Management Partner Technologies

44. Presenter: Kishore Chinda E-mail ID: kchinda@in.ibm.com