Backup & Restore Load & Unload

1. Backup & Restore Load & Unload

2. Backup & Restore • What does DBA stands for ? • Default and Backup for Anything. • Why a backup ? • Disaster Recovery. • High Availability Data Replication (HDR) setup. • Cloning (Testing, development, QA, etc).

3. Backup & Restore • Why a database backup? – Disaster recovery • When a disaster strikes, you need a way to recover the data. The recovery process requires procedures and processes in place ahead of time that allow for data recovery when needed. A backup. • What might cause you a need to recover? • Unintended deletion of a database object (rows, columns, tables). • Unintended deletion of a server object (databases, chunks, dbspace). • Data corruption or incorrect data created. • Hardware failure (such as when a disk that contains chunk files fails). • Database server failure. • Natural disaster. • What to backup ? • Depending on the recovery plans.

4. Backup & Restore – Disaster Recovery • Goals and Objectives: • How much data loss, if any, is acceptable? • How long can your business function without the data? • How long can your production system be down during a restore? • How much transaction time can be lost? • How much budget is available for a recovery plan?

5. Backup & Restore – Disaster Recovery • Type –Time • Type: • Knowing the type or usage of data can help you determine how quickly it needs to be recovered. • Why was this data backed up to begin with? • Time: • How much transaction time can be lost? • Can we re-apply the transactions / load data via other means?

6. Backup & Restore – Disaster Recovery • Quantity – Distribution: • Quantity • What quantity of data has been lost? (e.g., we lost 25% of our customer profiles). • Distribution • What is the extent of the loss? (e.g., we lost the entire Midwest regional sales figures, but the East/West coast and international figures are intact).

7. Backup & Restore • Disaster Recovery – Right, Fast, or Cheap • There are three ways to implement recovery: right, fast, or cheap. • Choose the two highest on your priority list. These choices should directly relate to your data and time-loss recovery goals. • Most people will choose the right option as their first priority. The difficult step is to weigh the advantages and disadvantages of speed and cost.

8. Backup & Restore • Recovery strategies and scheduling backups: • Recovery strategy requires planning with an understanding of the business critical data, and comprehension of the backup tools available and their capabilities. • The first step in planning your recovery is to outline recovery goals based on the understanding of the business data. • Define what is a successful recovery for your system in terms of tolerance for data loss and acceptable time loss. Use key questions presented earlier. • Have a recovery checklist in place.

9. Backup & Restore • Recovery planning cycle:

10. Backup & Restore • Why a database backup – HDR setting: How it works? Database Logging (DDL, DML) Logical log transmission The need for a backup / restore to set it up Physical Logical HDR as backup solution in itself Primary Secondary

11. Backup & Restore • Why a database backup – Cloning: • Testing • Development • QA • etc

12. Backup & Restore • Informix concepts: • Database Server or Instance • Dbspace • Database • Tablespace • Logical Logs

13. sysutils Backup Media Backup & Restore onconfig Emergency Boot file ONINIT VPs rootdbs reserve pages online log sysmaster sbspace dbs1 dbs2 Physical Log Logical Logs /informix/rootchunk1 /informix/sbck1 /informix/sbck2 /informix/s1ck1 /informix/s1ck2 /informix/s2ck1 /informix/s2ck2 /informix/rootchunk2

14. Backup & Restore • Informix Backup A backup occurs when all or some server database objects are copied to storage media Physical backup Dbspaces Or Logical Logs Logical backup

15. Backup & Restore • Physical and Logical Backup: • Physical • Focuses on current contents of storage spaces (dbspaces, blobspaces, smart blobspaces). • Foundation of all backup and restore operations. • Works on page level. • Backup only used pages. • Logical • Focuses on transactions stored in logical log files. • Backup can be continuous (as log files fill) or manual. • How we can restore as much data as possible, even data updated between backups.

16. Backup & Restore • Physical Backup • Performed while server is On-Line or Quiescent: • To reduce impact on production. • Off-Line backup is possible, but is not supported because data consistency can’t be guaranteed. • Incremental Backup • To save time and space on backup media. • Backup only the pages that have changed since the last backup. • Three levels: • 0 - backup all used pages. • 1 - backup all changes since the last level 0 backup. • 2 - backup all changes since the last level 1 backup.

17. Backup & Restore • Logical Backup 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’ In order to successfully complete a logical restore, the logical-log backup must have been created with the same backup utility used to create the physical backup.

18. Backup & Restore • Logical Backup • Log file cannot be re-used before it is backed up. • No log backup: • Set LTAPEDEV to /dev/null or NUL • Continuous log backup. • Manual log backup. • Log salvage: • Backup logical log files when server is off-line. • Can only backup undamaged files still on disk.

19. Backup & Restore • Physical & Logical restore

20. Backup & Restore • Physical Restore • Cold: • Server is off-line. • Must restore all critical media. • Salvage logical log files still on disk, physical restore, logical restore. • Warm: • Server is On-Line or Quiescent. • Restore of critical media not allowed. • Small impact on production in most cases. • Physical restore of storage spaces, followed by logical log backup, then logical log restore.

21. Backup & Restore • Physical Restore(cont’d) • Mixed • Get back to production as soon as possible. • First step: Cold restore all critical and application significant spaces to bring server On-Line. • Second step: At a later time complete the restore with a warm restore of the remaining spaces. • Overall restore takes longer because there are 2 logical restores, one for each step.

22. Backup & Restore • Restore Options • Point in Time / Point in Log: • Restore to a specific time or to the end of a specific log. • All changes to data after the point in time are lost. • MUST restore all storage spaces to the same time. • Imported • Restore on one server a backup taken from another. • Intended for testing or to start up replication. • Creates a new server instance: • You cannot merge data from two servers into one. • All storage spaces: • This is not the way to get rid of old data. • Restartable and Suspended. • Redirected: • Allows chunk pathnames to be changed while doing the restore.

23. Backup & Restore • Logical Restore • Logical restore has 2 parts: • Copy the backed up log file to disk. • Read the data in the log file and replay the transactions. • Logical restore is performed only on the spaces just physically restored: • Can’t do logical restore by itself, must do physical restore first.

24. Backup & Restore • Select Tools: • Now that recovery goals have been set, it is time to identify the IBM Informix backup and recovery tools. In addition, other useful features and utilities that provide added data redundancy and high availability will be discussed.

25. Backup & Restore Utilities - ontape • Backup and restore utility for Informix that provides: • Backup at the server level. • Support for incremental backup. • Manual or continuous logical-log backup. • Restore at the system or dbspace level.

26. Backup & Restore Utilities - ontape • Backup & Restore • Advantages • Reliability - Data can be restore up to point of failure because of logical-log backups. • Ability to restore at the system or dbspace level . • (restore not backup at dbspace level. • Support for remote tape devices. • Support for external backup and restore. • Simple configuration. • Easy to use command syntax. • Generally used for small database servers.

27. Backup & Restore Utilities - ontape • Backup & Restore • Disadvantages: • Backup can only be done at the system level. • Each dbspace is backed up serially to a single device. For large systems, this can mean very slow backups. • Requires user input when device media must be changed. • No media management.

28. Backup & Restore Utilities - onbar • Backup & Restore Utility for Informix that provides: • Parallel backup and restore. • System level and dbspace level backup and restore. • Support for incremental backups. • Manual or automatic backup of logical logs. • Point-in-time recovery for an instance. • An open interface for communication with storage manager (XBSA). • Third-party storage managers may provide support for sophisticated tape stacker and jukebox devices.

29. Backup & Restore Utilities - onbar • Backup & Restore Utility for Informix that provides: • Advantages • Parallel backup and restore provides speed. • Reliability-Data can be restore up to point of failure because of logical-log backups. • Support for multiple devices. • Ability to restore at the server or dbspace level. This means the server can be made available to users once the critical dbspaces, i.e. root, logs, etc., have been restored: • Non-critical dbspaces can then be restored in order of importance.

30. Backup & Restore Utilities - onbar • onbar • Disadvantages: • Requires configuration and use of the IBM Informix Storage Manager or an additional cost third-party storage manager.

31. Backup & Restore - onbar • onbar enhancements over ontape: • High-speed parallel backup and restore. • Unattended operations. • Automatic backup of logical logs via the ALARMPROGRAM parameter. • Support for sophisticated backup storage devices through a storage manager. • Backup of selected dbspaces. • Restartable restore.

32. External Backup & Restore • Methods of backing up data at the O/S level • UNIX commands such as: • cpio • tar • od • File system snapshots. • 3rd mirror break off. • Storage manager “raw” backup. • External backup/restore feature of ON-Bar allows logical recovery. • Can be taken from a MACH-11 RSS of logged objects.

33. External Backup & Restore • External Backup & Restore • Advantages: • Availability, speed-Data can be backed up and restored more quickly, making the data available sooner. • Multiple copies of the same data can be kept. • Ability to copy raw physical devices. • Logical restore available if combined with ON-Bar External Restore.

34. External Backup & Restore • External Backup & Restore • Disadvantages: • No logical recovery available when using external backup utilities unless you use the ON-Bar External Backup/Restore feature. • Greater costs associated with third mirror break off solutions. • No media tracking for external backups is done by ON-Bar.

35. Backup & Restore – High Performance Loader • Load / Unload utilities – High Performance Loader (HPL): • Enables rapid loading/unloading of large amounts of data in parallel. • Performs data conversion. • Supports a variety of devices. • Stores load/unload operations as reproducible jobs.

36. Backup & Restore – High Performance Loader • Load / Unload utilities – High Performance Loader (HPL) • Advantages: • Enables rapid unloading/loading of large quantities of data. I/O to the devices and the server is performed in parallel. • Supports a variety of devices. • Supports multiple formats for load/unload files. • Data can be loaded from multiple sources. • Recovery at the table and row level. • Reusability of load/unload definition. Load/unload jobs are defined and stored in a database for reuse. • Could be used as a backup utility for a database with a few static tables.

37. Backup & Restore – High Performance Loader • Load / Unload utilities – High Performance Loader (HPL) • Disadvantages: • No transaction recovery available. Recovery only occurs up to the time data was last unloaded. • DBA must assure relational integrity of the database during the load and unload process. • Tool is more complex than other load/unload utilities. • Does not append to output devices.

38. Backup & Restore – dbimport/dbexport • Load / Unload utilities – dbimport / dbexport • dbimport: • Creates the database. • Imports ASCII data into the specified database. • Loads from disk or tape. • dbexport: • Unloads data and schema for an entire database. • Data stored in ASCII files. • Unloads to disk or tape.

39. Backup & Restore – dbimport/dbexport • Load / Unload utilities – dbimport / dbexport • Advantages: • Ability to capture data and schema. • ASCII files provide portability. • Recovery at the database level. • Inexpensive and easy way to backup small databases.

40. Backup & Restore – dbimport/dbexport • Load / Unload utilities – dbimport / dbexport • Disadvantages: • No transaction recovery available. Recovery only occurs up to the time the last export was taken. • Large databases will consume tape/disk resources; processing will be slow. • Requires an exclusive lock on the database when processing.

41. Backup & Restore – SQL load/unload • Load / Unload utilities – SQL load / unload: • SQL statements for loading and unloading data from ASCII files. • Adds rows to existing tables. • Must be run through DB-Access or ISQL.

42. Backup & Restore – SQL load/unload • Load / Unload utilities – SQL load / unload • Advantages • Data is added to existing tables: • dbimport and onload create new tables. • Specific rows can be unloaded. • Data unloaded is based upon an SQL SELECT statement. • ASCII files provide portability. • Recovery at the table and row level.

43. Backup & Restore – SQL load/unload • Load / Unload utilities – SQL load / unload • Disadvantages: • Dependent upon DB-Access or ISQL. • Does not load from tape. • No transaction recovery available. • Recovery only occurs up to the time the last export was taken. • Large amounts of data will be loaded and unloaded slowly.

44. Backup & Restore – onload/onunload • Load / Unload utilities – onload / onunload • onload • Creates database or tables in specified dbspaces. • Loads data created by onunload only. • Page size must be compatible. • onunload • Transfers data in binary pages. • Default write to tape unless –t option used to an existing file.

45. Backup & Restore – onload/onunload • Load / Unload utilities – onload / onunload • Advantages • Efficient data transfer. Data is read and written in binary pages, providing speed. • Recovery at the database and table level.

46. Backup & Restore – onload/onunload • Load / Unload utilities – onload / onunload • Disadvantages • Cannot be run for databases across a network. • Cannot be used for file I/O. • Loads data created by onunload only. • Binary data and page size limit portability. Page size varies from machine to machine (check your configuration file, onconfig, for the page size for your server system). You cannot use the utilities to transfer data from one server system to another with a different page size. Most server systems have either 4K or 2K pages. • No transaction recovery available. Recovery only occurs up to the time data was last unloaded. • You must read the file that onunload creates with the onload utility of the same version of your database server. You cannot use onunload and onload to move data from one version to another. • onunload does not preserve access privileges, synonyms, views, constraints, triggers, or default values that were associated with the original tables.

47. Backup & Restore - dbload • Load / Unload utilities – dbload • Loads data from multiple ASCII files on disk into one or more existing tables. • ASCII files can be created by the UNLOAD utility or by another means (c program, text editor or any other unload utility file that unloads data to ASCII). • Dbload offers more flexibility than LOAD.

48. Backup & Restore - dbload • Load / Unload utilities – dbload • Advantages • Data can be loaded from multiple sources. • Data is added to existing tables: • dbimport and onload create new tables. • ASCII files provide portability. • Recovery at the table and row level. • Provides more flexibility than LOAD. • Specify a starting point in the load file. • Add transaction logic (commit after every x rows). • Limit the number of bad rows read, at which time dbload terminates.

49. Backup & Restore - dbload • Load / Unload utilities – dbload • Disadvantages: • Doesn’t load from tape. • No transaction recovery available. • Recovery only occurs up to the time the last export was taken. • Large amounts of data will be loaded slowly. • DBA must assure relational integrity of the database during the load process.

50. Backup & Restore - Summary Summary Informix provides different Backup & Restore options ontape onbar External Backup & Restore Load & Unload utilities: Dbexport / dbimport Dbschema HPL SQL load / unload Onload / onunload Dbload