Considerations for Altibase Backup Policy#
Overview#
This document describes how to set up a database backup policy in case data is lost due to unforeseen circumstances such as data file conflict, loss, deletion.
This document introduces the backup types and features provided by Altibase and help users to determine the appropriate backup method for the operating environment. However, this document does not introduce commands or options for each backup method.
Info
For errors and improvements related to this document, please contact the technical support portal or technical support center.
- Technical support portal: http://support.altibase.com/en
- Technical support center: +82-2-2082-1114
Info
This document is provided for informational purposes only and is subject to change without prior notice. It may contain errors, and Altibase makes no express or implied warranties of any kind, including but not limited to warranties of merchantability or fitness for a particular purpose.
The development, release, and timing of any features or functionality described for Altibase products in this document are at the sole discretion of Altibase.
Altibase may hold patents, trademarks, copyrights, or other intellectual property rights related to the contents of this document.
Backup Types#
The simple classification of backup types provided by Altibase is as follows. The classification criteria follow the Administrator Manual.
- Logical backup
- Physical backup
- Online backup
- Offline backup
- Incremental backup (supported starting from Altibase 6.3.1)
Logical Backup#
Logical backup is to back up database objects and data in the form of a text file that can be checked by a user using the aexport and iLoader utilities.
| Backup type | Description | Example of backup result | |
|---|---|---|---|
| Logical Backup | aexport | aexport is a tool to support data migration between Altibase servers, but can be used for backup purposes. This creates all the database object creation statements created on the Altibase server as a file named .sql. It provides three methods to perform according to the purpose - All database objects - By database user - By object |
Example of aexport execution result and file contents.$ ls -l | head Total 60 -rw-rw-rw- 1 eheejung eheejung 303 2019-07-12 13:15 ALL_ALT_TBL.sql -rw-rw-rw- 1 eheejung eheejung 0 2019-07-12 13:15 ALL_CRT_DIR.sql -rw-rw-rw- 1 eheejung eheejung 0 2019-07-12 13:15 ALL_CRT_FK.sql -rw-rw-rw- 1 eheejung eheejung 1539 2019-07-12 13:15 ALL_CRT_INDEX.sql -rw-rw-rw- 1 eheejung eheejung 0 2019-07-12 13:15 ALL_CRT_JOB.sql -rw-rw-rw- 1 eheejung eheejung 0 2019-07-12 13:15 ALL_CRT_LIB.sql -rw-rw-rw- 1 eheejung eheejung 0 2019-07-12 13:15 ALL_CRT_LINK.sql -rw-rw-rw- 1 eheejung eheejung 0 2019-07-12 13:15 ALL_CRT_REP.sql -rw-rw-rw- 1 eheejung eheejung 0 2019-07-12 13:15 ALL_CRT_SEQ.sql ...omitted... $ cat ALL_CRT_TBL.sql | head connect "ALTITEST" / "ALTITEST" --############################ -- "ALTITEST"."ORDERS" --############################ create table "ORDERS" ( "ONO" BIGINT, "ORDER_DATE" DATE, |
| iloader | Like aexport, this is a tool to support data migration, but it can be used for backup purposes. In table units, data is backed up in a text file that can be read by users. |
Example of DEPARTMENT sample table iloader execution $ cat SYS_DEPARTMENTS.dat 1001,"RESEARCH DEVELOPMENT DEPT 1 ","New York ",16 1002,"RESEARCH DEVELOPMENT DEPT 2 ","Sydney ",13 1003,"SOLUTION DEVELOPMENT DEPT ","Osaka ",14 2001,"QUALITY ASSURANCE DEPT ","Seoul ",17 3001,"CUSTOMERS SUPPORT DEPT ","London ",4 3002,"PRESALES DEPT ","Peking ",5 4001,"MARKETING DEPT ","Brasilia ",8 4002,"BUSINESS DEPT ","Palo Alto ",7 |
Physical Backup#
Physical backup is to copy files such as data files and log anchor files that make up the Altibase server and backing them up to a different physical location.
Physical backup classified into online backup and offline backup according to the Altibase server operation status.
| Backup type | Description | Example of backup result | |
|---|---|---|---|
| Physical Backup |
Online backup | Performing physical backup while the Altibase server is running. - There are two methods: - All databases - Tablespace-level | Online backup only backs up one stable memory checkpoint image file. Data files in the disk temporary tablespace are not backed up. $ ls -l Total 2811920 -rw-r----- 1 tech2 tech2 727556096 2019-07-12 14:12 SYS_TBS_MEM_DATA-0-0 -rw-r----- 1 tech2 tech2 4300800 2019-07-12 14:12 SYS_TBS_MEM_DIC-0-0 -rw-r----- 1 tech2 tech2 9632 2019-07-12 14:12 loganchor0 -rw-r----- 1 tech2 tech2 9632 2019-07-12 14:12 loganchor1 -rw-r----- 1 tech2 tech2 9632 2019-07-12 14:12 loganchor2 -rw-r----- 1 tech2 tech2 1073750016 2019-07-12 14:13 system001.dbf -rw-r----- 1 tech2 tech2 1073750016 2019-07-12 14:13 undo001.dbf |
| Offline backup | Physical backup while the Altibase server has stopped. Only full database backup is possible. |
Offline backup backs up all data files.[tech2@bd5 online_backup]$ ls -l Total 3629032 -rw-r----- 1 tech2 tech2 727556096 2019-07-12 14:12 SYS_TBS_MEM_DATA-0-0 -rw-r----- 1 tech2 tech2 727556096 2019-07-12 14:12 SYS_TBS_MEM_DATA-1-0 -rw-r----- 1 tech2 tech2 4300800 2019-07-12 14:12 SYS_TBS_MEM_DIC-0-0 -rw-r----- 1 tech2 tech2 4300800 2019-07-12 14:12 SYS_TBS_MEM_DIC-1-0 -rw-r----- 1 tech2 tech2 9632 2019-07-12 14:12 loganchor0 -rw-r----- 1 tech2 tech2 9632 2019-07-12 14:12 loganchor1 -rw-r----- 1 tech2 tech2 9632 2019-07-12 14:12 loganchor2 -rw-r----- 1 tech2 tech2 1073750016 2019-07-12 14:13 system001.dbf -rw-r----- 1 tech2 tech2 104865792 2019-07-12 15:09 temp001.dbf -rw-r----- 1 tech2 tech2 1073750016 2019-07-12 14:13 undo001.dbf |
Incremental Backup#
Incremental backup is to back up only data pages that have changed since the last incremental backup and the 'page change tracking' must be activated.
The entire database or tablespace-level can be backed up.
| Backup type | Description | Example of backup result | ||
|---|---|---|---|---|
| Incremental Backup | Level 0 incremental backup | Level 0 is incremental backup is similar to physical backup, but is a backup that is used for determining whether data pages have been changed. | $ ls -l TAG_20190712_141610/ Total 2811924 -rw-r----- 1 tech2 tech2 727556096 2019-07-12 14:16 SYS_TBS_MEM_DATA-0-0_TAG_20190712_141610.ibak -rw-r----- 1 tech2 tech2 4300800 2019-07-12 14:16 SYS_TBS_MEM_DIC-0-0_TAG_20190712_141610.ibak -rw-r----- 1 tech2 tech2 3296 2019-07-12 14:16 backupinfo -rw-r----- 1 tech2 tech2 9632 2019-07-12 14:16 loganchor0 -rw-r----- 1 tech2 tech2 9632 2019-07-12 14:16 loganchor1 -rw-r----- 1 tech2 tech2 9632 2019-07-12 14:16 loganchor2 -rw-r----- 1 tech2 tech2 1073750016 2019-07-12 14:16 system001.dbf_TAG_20190712_141610.ibak -rw-r----- 1 tech2 tech2 1073750016 2019-07-12 14:16 undo001.dbf_TAG_20190712_141610.ibak |
|
| Level 1 incremental backup | Differential incremental backup | This backs up pages that have changed since the most recently performed level 0 or level 1 incremental backup. | ||
| Cumulative incremental backup | This backs up pages that have changed since the most recently performed level 0. |
Considerations for Determining Backup Policy#
Selecting the backup policy should be decided in consideration of the operation environment, such as the importance of data, recovery policy, and backup size. This chapter describes the considerations for determining a backup policy.
Select backup target#
To determine a backup policy, the first thing to do is to select a backup target. In addition, in consideration of the characteristics of the backup target the recovery policy, backup type, backup cycle, backup time, and storage period of backup files must be determined first.
This backup plan is not something that the DBA can do alone and requires a data manager to cooperate with. This is because the person who is in charge of the operation needs to know which backup target is most suitable.
Recovery point#
There is a difference in the time of recovery depending on the backup type. Depending on the importance of the backup target, a recovery point should be determined a backup method should be selected accordingly.
There are three major recovery points.
| Recovery point | Description |
|---|---|
| Backup point | This refers to the restoration to the point in time when the backup was performed. |
| Complete recovery | This refers to the restoration to the most recent point in the database. This is possible only when online log files and archive log files are not lost. |
| Incomplete recovery | This refers to the restoration to a certain past point in time. Incomplete recovery is possible at two points in time. - UNTIL CANCEL Recover by specifying a specific log file - UNTIL TIME Recover by specifying the date and time |
The table below summarizes the recovery points by backup type. Select a backup method suitable for the backup target by referring to the table.
| Backup type | Backup point | Complete backup | Incomplete backup | |
|---|---|---|---|---|
| Logical backup | Possible | N/A | N/A | |
| Physical backup | Online backup | Possible | Possible | Possible |
| Online backup | Possible | N/A | N/A | |
| Incremental backup | Possible | Possible | Possible |
Recovery time#
Recovery is an operation that must be performed after a service interruption. Operation environments in which service downtime must be minimized when determining the backup policy.
Since the factors that affect the recovery time differ depending on the backup type, please refer to the table below and select the method that suits the characteristics of the backup target.
| Backup type | Factors affecting recovery time | Description | |
|---|---|---|---|
| Logical backup | aexport | - Number of database objects | If creating objects is necessary during the recovery process, the number of objects can affect the recovery time. |
| iloader | - Number of tables - Data usage of tables - Number of indexes | Since the information of the file is read and inserted into the table (INSERT), the number of tables, the number of records, and the number of indexes affect the recovery time. | |
| Physical backup | Online backup | - Data usage of memory tables - Memory index - Number of log files | Recovery is in progress while the Altibase server is running. It takes time to load the memory table data into memory and rebuild the memory index. Since log files are used in the recovery process, the number of log files to be reflected affects the recovery time. The log file is determined by the amount of transactions that have occurred since the backup. |
| Offline backup | - Data usage of memory tables - Memory index | The recovery time is shortest because the Altibase is restored by running only the backup file. When running the Altibase server, it takes time to load the memory table data into the memory and rebuild the memory index. |
|
| Incremental backup | - Data usage of memory tables - Memory index - Number of log files | During the recovery process, it takes time to load the memory table data into memory and rebuild the memory index. Since the log files are used in the recovery process, the number of log files to be reflected affects the recovery time. The log file is determined by the amount of transactions that have occurred since the backup. |
Backup performing time#
The backup performing time takes a long time as the size of the database is larger, and it affects the transaction while executing the backup. Therefore, it is recommended to perform a backup at idle time during the Altibase operation.
This table summarizes the factors that affect the backup time by backup type and how they affect transactions.
-
Backup time#
Backup type Factors affecting the time to back up Description Logical backup aexport - Number of database objects Since the object creation script is created, it is affected by the number of database objects at backup time. iloader - Number of tables - Usage of tables Since a text file created by retrieving data for each table, the time required for backup is affected by the number and usage of tables. Physical backup Online backup - Size of disk table data file - Size of memory checkpoint image Online backup is a process of physically copying data files, so the size of the data file affects the time required for backup. Offline backup - Size of disk table data file - Size of memory checkpoint image file Offline backup is also a process of physically copying data files, and the time required for backup is affected by the size of the data file. Incremental backup Level 0 incremental backup - Size of disk table data file - Size of memory checkpoint image file As a backup in a similar method to online backup, the size of the data file affects the time required for backup. Level 1 incremental backup - amount of change of data page The time required for level 1 incremental backup is affected by the amount of data page change caused by the change transaction. -
Transaction#
This is a table comparing the impact on transactions when performing a backup
Backup type Impact on transaction Logical backup aexport Temporarily unable to perform DDL iloader Memory GC increase during backup Physical backup Online backup Online log file increase due to inability to perform checkpoint during backup Offline backup Service downtime required Incremental backup Normal DB performance degradation by activating the 'Page Change Tracking' function
Online log file increase due to inability to perform checkpoint during backup
Backup copy storage cycle#
It is recommended to keep at least two backup copies.
This is to prepare for the loss of backup copies due to an incorrect backup or a disk fault or user fault.
Configuring disk partitions for backup#
If the object storing the backup file is a disk, it is recommended that the backup location be set to space physically separated from the path where the database data files and log files are located.
During the database operation, disk I/O occurs as follows. However, an exception in the case of a service that mainly occurs only retrieving transactions in the memory table.
- Disk I/O for log files where changes to the database are recorded
- Disk I/O to record to the memory checkpoint image file in the memory table
- Disk I/O to read and record data files in the disk table
Therefore, disk space is separated to minimize bottlenecks between disk I/O occurring during database operation and disk I/O while executing the backup.
Summary (Considerations for Altibase Backup Policy)#
This chapter summarizes the characteristics of each backup type, which are organized in the following tables.
Comparison by backup type#
The following is a table comparing the pros and cons of each backup type.
| Classification | Logical backup | Physical backup | Incremental backup | ||
|---|---|---|---|---|---|
| aexport | iloader | Online backup | Offline backup | ||
| Characteristics | - Object-level backup/recovery - Require backup only when changing object structure | - Table-level - Record-level recovery is possible - When point-in-time recovery is unnecessary - When need to recover while the Altibase server is running | - When to minimize data loss - When point-in-time recovery is required - When there is no burden on the archive log mode operation - When need to prepare for physical loss of files such as data files and log anchor files | - When Altibase server downtime for backup can be secured - When to minimize recovery time | - When to minimize data loss - When point-in-time recovery is required - When there is no burden on the archive log mode operation - When to prepare for physical loss of files such as data files and log anchor files - When the DB performance degradation due to activating the 'Page Change Tracking" function can be sustained |
| Pros | - Altibase server operation possible during recovery | - Altibase server operation possible during recovery | - Point-in-time recovery is possible - Possible to prepare for the physical failure of data files and log anchor files | - Minimize recovery time | - Point-in-time recovery is possible - Possible to prepare for the physical failure of data files and log anchor files - Save backup time compared to online backup - Save backup space compared to online backup |
| Cons | - - | - Point-in-time recovery is unable | - Manage archive log files - Operating Altibase server is unable during recovery | - - Operating Altibase server is unable during recovery | - Manage archive log files - Operating Altibase server is unable during recovery |
The following is a table comparing the features of each backup method in detail.
| Backup type | Altibase server running status | Database mode | Backup file | Recovery point | Recovery target | Etc. | ||
|---|---|---|---|---|---|---|---|---|
| Backup | Recovery | |||||||
| Logical backup | aexport | Running | Running | Irrelevant | - Database object creation script (.sql extension) - Automation script (.sh extension) | - Backup point | - Object-level | |
| iloader | Running | Running | - FORM file for each table - Data file in text format | - Backup point | - Table-level - Record-level | |||
| Physical backup | Online backup | Running | Running (Control step) |
Archive mode | - Disk data file - 1 memory checkpoint image file - Log anchor file - Archive log file | - Complete recovery - Incomplete recovery | - All databases - Tablespace-level | - Require archive log file management |
| Offline backup | Stopped | - | Irrelevant | - Disk data file - Memory checkpoint image file - Log anchor file - Online log file | - Backup point | - All databases | ||
| Incremental backup | Running | Running (Control step) |
Archive mode | - Disk data file - 1 memory checkpoint image file - Log anchor file - Archive log file - backupInfo file | - Complete recovery - Incomplete recovery | - All databases - Tablespace-level | - Need to active 'Page Change Tracking" function - Require Archive log file |
Related documents#
- Technical document " Altibase Capacity Guide"
- Technical document "Volume Configuration Guide Considering disk I/O"
- Administrator Manual -> 10. Backup and Recovery
- Administrator Manual -> 11. Incremental backup and Recovery
- Utilities Manual -> 1. aexport
- iLoader User's Manual
- Manual page: https://github.com/ALTIBASE/Documents/tree/master/Manuals