Table of Contents
Tibero provides various backup and recovery methods in order to deal with hardware failure or the abnormal termination of the database.
This chapter describes how to back up and recover data.
Tibero consists of control files, data files, temp files, and log files.
The characteristics and roles of each file are as follows:
Control files store the database structure, including the database name and the locations of all files in Tibero. In particular, status information for the data file and the log file used by Tibero is recorded in the control file. Control files are used to judge if recovery is needed when starting Tibero.
Because control files are very important for database recovery, saving several copies in physically different partitions is recommended. Maintaining multiple control files enables a database to be recovered even when a control file cannot be used due to its partition's failure, by using a control file from a different partition.
The control files should be kept up-to-date, because they contain information about the files that compose the current database. Control files themselves are not backed up, but the CREATE CONTROLFILE statement, which creates a control file, is backed up to recreate a control file when the database needs to be recovered.
To keep control files up-to-date, the statement to create the control file should be backed up whenever there is a structural change such as adding or modifying a file in the database. Backing up a control file itself can only be used only when the entire database is backed up in NOARCHIVELOG mode.
Data files store user data and, along with log files, are one of the two most important file types in a database.
Data files are defined by a Permanent tablespace and an Undo tablespace, and store database objects such as tables and indexes. A tablespace consists of one or more data files, and each file corresponds to a single tablespace.
Because data files store user data, they should always be backed up.
Temp files temporarily store data when a database has to handle more data than it has memory capacity.
Tibero uses temp files when performing operations such as sorting for processing user queries and storing data in temporary tables. Temp files can be defined only in temporary tablespaces, and temporary tablespaces can have more than one temp file.
Because temp files are not intended to permanently store data for the database, but are instead just temporary storage, the files do not need to be backed up.
Log files stores logs. Contents recorded in data files are recorded in log files in chronological order. The log files are later used for database recovery.
Log files are divided into online log files, which are used circularly during system operation, and archived log files, which store the reused online log files.
Archived log files are only created when ARCHIVELOG mode is enabled. In NOARCHIVELOG mode, only online log files are used, so restrictions follow when recovering a database because some log files might have been reused and changed.
All information about how a database has been modified is recorded in log files to keep data files current when they are recovered. Therefore, log files should be backed up with data files.
Backups protect a database from various kinds of failures. They are a set of procedures or methods to recover a failed system or maintain normal system operation.
Tibero provides two methods to back up a database:
Logical backups
Logical backups back up a database's logical units such as tables, indexes, and sequences. To accomplish this, Tibero provides two utilities, tbExport and tbImport. For detailed information about tbExport and tbImport, refer to Tibero Utility Guide.
Physical backups
Physical backups back up a database's physical files, and are performed by copying files at the OS level. The files that need a physical backup include data files and archived log files.
For online log files, this backup method is only useful when recovering the entire database after backing it up in NOARCHIVELOG mode.
It is not safe to copy files while a database is running.
This section describes the backup types.
The following is the list of backup types provided by Tibero:
Mode-dependent backup
A different backup is used depending on which mode the database is operating in:
A method to back up a database after terminating Tibero normally. For an example, refer to Section 6.2.2, “Consistent Backup” in “6.2.2. Performing a Backup”.
A method to back up a database when Tibero is operating or after it has terminated abnormally. Do not use this method in NOARCHIVELOG mode because it may cause an integrity issue. For an example, refer to Section 6.2.2, “Inconsistent Backup” in “6.2.2. Performing a Backup”.
This section describes the physical and logical backup methods provided by Tibero.
For data files and log files, the backup method varies according to the database status, so this section describes the backup methods: inconsistent backup for an operating database and consistent backup for a non-operating database.
Both physical and logical backups are supported for control files. However, physical backup is not recommended because the procedure is very significant and complicated (a control file must be backed up after backing up all the other data files to keep data integrity). When a database structure changes, it is recommended to back up an SQL statement that creates the control file (logical backup).
The following first example backs up a control file to the ctrlfile1.ctl file in the tibero6/backup directory (physical backup), and the second example backs up an SQL statement that creates the control file to the ctrlfile1.sql file (logical backup).
Physical backup for control files
[Example 6.1] Physical Backup for Control Files
SQL> alter database backup controlfile to '/tibero6/backup/ctrlfile1.ctl'; Altered.
Logical backup for control files
[Example 6.2] Logical Backup for Control Files
SQL> alter database backup controlfile to trace as '/tibero6/backup/ctrlfile1.sql' reuse NORESETLOGS; Altered.
The file that was created, ctrlfile1.sql, includes the following data:
[Example 6.3] Backed-up CREATE CONTROLFILE Statement
CREATE CONTROLFILE REUSE DATABASE "inventory" LOGFILE GROUP 0 ( '/disk1/log001.log', '/disk2/log002.log' ) SIZE 1M, GROUP 1 ( '/disk1/log003.log', '/disk2/log004.log' ) SIZE 1M, GROUP 2 ( '/disk1/log005.log', '/disk2/log006.log' ) SIZE 1M NORESETLOGS DATAFILE '/disk1/system001.dtf', '/disk1/undo001.dtf' NOARCHIVELOG MAXLOGFILES 255 MAXLOGMEMBERS 8 MAXDATAFILES 100 CHARACTER SET MSWIN949 NATIONAL CHARACTER SET UTF16 ;
Resetlogs for a 'CREATE CONTROLFILE' statement are made as assigned in a 'backup controlfile' statement. The 'backup controlfile' statement is used when resetlogs are needed after creating a trace file, and if resetlogs are not needed, the statement can be modified to 'NORESETLOGS' and applied to creating a control file.
There is no temp file in a 'CREATE CONTROLFILE' statement. No temp file exists if Tibero starts after a control file is created.
If a new control file is created, using functions through a temp file requires adding a temp file.
The location of a created control file is specified in the $TB_SID.tip file.
In the MOUNT or OPEN state, the list of control files can be viewed using the V$CONTROLFILE view as shown below:
[Example 6.5] Control File Lookup
SQL> SELECT NAME FROM V$CONTROLFILE;
NAME
------------------------------------------------------------
/disk1/c1.ctl
/disk2/c2.ctl
2 selected.
To create another control file, specify the location of the control file in the $TB_SID.tip file as the query result of [Example 6.5] and execute a 'CREATE CONTROLFILE' statement.
This section describes how to back up data after Tibero terminates normally.
Before running a consistent backup, search the control files, image files, and log files to back up.
Searching data files using the dynamic view V$DATAFILE in either the MOUNT or OPEN state is shown below. The MOUNT state is when Tibero's instance has been started, and the OPEN state is when all files defined in control files have been opened.
[Example 6.6] Data File Lookup
SQL> SELECT NAME FROM V$DATAFILE;
NAME
------------------------------------------------------------
/disk1/system001.dtf
/disk2/undo001.dtf
/disk3/user001.dtf
3 selected.
Searching online log files in MOUNT or OPEN state is shown below:
[Example 6.7] Online Log File Lookup
SQL> SELECT MEMBER FROM V$LOGFILE;
MEMBER
------------------------------------------------------------
/disk1/log001.log
/disk2/log002.log
/disk2/log003.log
/disk3/log004.log
/disk3/log005.log
/disk1/log006.log
6 selected.
If ARCHIVELOG mode is not in use, backing up online log files is
not recommended. Because online log files are archived In ARCHIVELOG
mode, backing up archived files is recommended. Archived files will be
saved in the location specified in the
LOG_ARCHIVE_DEST initialization parameter.
The database should be terminated in NORMAL mode as follows:
SQL> tbdown NORMAL; Tibero instance was terminated.
After the database has been terminated normally, use an OS file copy command to back up the files.
This section describes how to back up data while Tibero is operating.
It is not safe to copy files via the OS while a database is running. To perform a backup safely, the following statements should be executed to notify Tibero of the beginning and end of the backup.
alter tablespace {tablespace name} begin backup
...
alter tablespace {tablespace name} end backup
Because the number of logs related to tablespace changes increases between the 'begin backup' and 'end backup' commands, a greater and greater load will be placed on the database. To handle this, after a backup process has begun, finish the task quickly to return to the 'end backup' state.
The entire inconsistent backup process is shown below:
Select a tablespace to be backed up.
[Example 6.8] Inconsistent Backup - Selecting a Tablespace
SQL> select name,type from v$tablespace; NAME TYPE ------------------------------ ---- SYSTEM DATA UNDO UNDO USER DATA TEMP TEMP 3 selected.
Search for the data file of the tablespace to be backed up, and back it up using the 'begin backup' and 'end backup' commands.In the example below, the USER tablespace is backed up:
[Example 6.9] Inconsistent Backup - Using the 'begin backup' and 'end backup' Commands
SQL> select f.name
from v$tablespace t join v$datafile f on t.ts# = f.ts#
where t.name = 'USER';
NAME
------------------------------------------------------------
/disk3/user001.dtf
1 selected
SQL> alter tablespace SYSTEM begin backup;
Altered.
SQL> !cp /disk3/user001.dtf /backup/
SQL> alter tablespace SYSTEM end backup;
Altered.
While Tibero is operating, an unexpected failure may occur. A recovery is the process of responding to the failure.
To execute a recovery, the database needs to have been backed up.
Tibero records all changes that occur in the database in log files. Therefore, any changes which have occurred in the database can be recovered by applying logs. Log files include data modified by uncommitted transactions. In the recovery process, both archived log files and online log files can be used.
There are two recovery processes:
The process of recovering changes which have not been recorded in data files using logs.
Database stability can be maintained by recording all changes in logs to data files. In other words, the database should apply all tasks by a certain time of operation and see no changes afterwards.
Any problematic database can be restarted only after it becomes stable through this normal recovery.
The process of recovering data which has not been committed.
This is the process of recovering database changes that were made by uncommitted transactions when the database failed.
Tibero recovery operates differently depending on each boot mode.
NOMOUNT mode
In NOMOUNT mode, recovery can be performed at any time. The database and control files can be created in this mode. To operate in MOUNT mode, a control file is required. If there is no control file or a failure occurred in a control file, Tibero operates in NOMOUNT mode. The mode can be changed to MOUNT if a control file is created.
MOUNT mode
In MOUNT mode, conditions of data files, online log files, and control files are checked to prepare to start Tibero. If the files are all up-to-date, Tibero can operate in OPEN mode. If a physical failure occurred to a file, or the file has been recovered, media recovery is required and Tibero will stay in MOUNT mode. In this mode, a limited number of views can be accessed and media recovery can be performed.
OPEN mode
To operate in OPEN mode,Tibero should maintain the consistency of data files, online log files, and control files. In this mode, Tibero opens the files and operates normally, allowing users to use the database.
For a user to access a tablespace that is in offline status, the status of the tablespace needs to be changed to online. When this occurs, online media recovery should be executed for the tablespace to maintain consistency with other files,
Crash Recovery is an automatic recovery made without user input when a database has abnormally terminated due to a power failure, a system error, or a forcible shutdown. After the recovery completes, Tibero will operate normally.
One of the crash recovery processes is to restore Tibero to the state in which it was operating just before it abnormally terminated, by recording online log files which have not yet been reflected to data files. The other crash recovery process is to recover changes which were made by uncommitted transactions at the point in time when Tibero was restored to the operating state.
Unless a file is damaged, all processes for crash recovery will be carried out automatically, without the administrator's help.
Average Crash Recovery Time Setting
Tibero DBMS provides the Mean Crash Recovery Time parameter, which specifies the average crash recovery time. Mean Crash Recovery Time (hereafter MCRT) limits the I/O time required for recovering from a crash to adjust the average crash recovery time.
MCRT can be adjusted using the following parameter.
| Parameter | Description |
|---|---|
| _MCRT_TARGET | Average crash recovery time. (Default: 1800, Unit: Sec) |
Media recovery is a recovery process to help the database return to proper operation after a file in Tibero has become physically damaged or cannot be used normally.
Media recovery is not performed automatically. The administrator needs to check the situation and execute any necessary tasks. Media recovery is divided into complete recovery and incomplete recovery, depending on the recovery time: either the most recent point before the error occurred or at any particular time before the most recent point.
A complete recovery reflects all logs in an online log file including the most recent log.
An incomplete recovery restores an online log file to a particular point of time in the past other than the most recent time. After incomplete recovery, Tibero should be started in RESETLOGS mode.
RESETLOGS mode initializes online log files, and is used when the database is started without online log files.
Resetlogs are required when:
Incomplete media recovery was performed
A control file was created with resetlogs
Starting with resetlogs is like creating a new database. There is no compatibility between data and log files from before and after resetlogs. The database cannot be restored to the post-resetlogs state using backup files or log files from before resetlogs. Furthermore, it is impossible to restore the database to the pre-resetlogs state with incomplete recovery using files from after resetlogs. Therefore, it is strongly recommended to back up data again after the database has been started with RESETLOGS mode.
Starting the database with RESETLOGS mode is shown below:
Media recovery can be carried out only in MOUNT mode. One of the media recovery processes recovers a file which has an error using its backup file. The other process uses log files to recover changes which had not been reflected from when the file was previously backed up until a particular point in time. Only with a simple recovery process, Tibero is not normally operated.
Media recovery can be carried out only in MOUNT mode. It is handled with the following two processes: recovering a file with errors through a backup file, and recovering the changes starting from the last backup to a particular point in time using log files. Tibero cannot be restored fully just through a simple recovery process.
For media recovery, use the following views to find files with errors and recover them.
V$LOGFILE
V$CONTROLFILE
V$LOG
V$RECOVER_FILE
V$RECOVERY_FILE_STATUS
Media recovery is performed by reflecting each log file to the database in order. The database can only reflect log files which are needed for the current recovery. A sequence number is used to look for the necessary log file.
Sequence numbers are a series of log file numbers generated after the database is created, and all log files have a unique sequence number. A log file of a larger sequence number is more recent. A sequence number of an archived log file and online log file can be found through the file name and the V$LOG view, respectively.
While Tibero is operating, a data file may be physically damaged or not function correctly. When this happens, media recovery can be executed in OPEN mode for only the tablespace that contains the data file. This kind of media recovery is called online media recovery. Online media recovery only supports complete recovery.
Tibero provides various backup methods and recovery scenarios. A knowledgeable administrator can select and use the appropriate method for a situation. However, a normal user might be confused by the various methods and scenarios. To solve this problem, Tibero provides a recovery manager (hereafter RMGR).
RMGR supports a variety of backup methods and recovery scenarios. The functions of RMGR in Tibero are as follows:
Executes online backup for all data files in a Tibero database. To accomplish this, the database must be in ARCHIVELOG mode. RMGR automatically put all tablespaces in Hot Backup status and then executes online backup using the Begin Backup function of the database.
When the backup completes, RMGR releases all tablespaces from Hot Backup status using the End Backup function of the database. RMGR automatically decides which data files to back up by viewing V$DATAFILE.
The Incremental Backup can be executed if an online backup is received via RMGR. Only changes from the previous backup are recorded instead of backing up all files. This can dramatically reduce the disk space required for a backup.
To use this function, RMGR must first perform an online backup. A backup file is created by comparing the current database with the backup database. This function can be used only through RMGR.
Automatic Recovery uses a backup created by RMGR to automatically recover a database. The backup information is saved in the control file. By analyzing information about online full backup and incremental backup in the control file, this function automatically merges the information and recovers the database. If the control file cannot be accessed, the backup control file can be used by specifying the backup destination with the -o option.
To perform recovery in a TAC environment using RMGR, only one of the configured nodes must be in running state.
Tablespace Backup and Recovery
Only the required tablespaces are backed up or restored.
RMGR can be used to delete a backup set from the control file. Specify the target backup set ID with the --backup_set option, or the backup date with the --beforetime option to delete all backup sets that occurred before the specified time. The backup date format is 'YYYYMMDDHH(24)mmss'.
Since the backup set location is determined by referencing the control file, its location must be specified with the -o option to delete a backup set whose location has been modified after its creation. If the backup set specified in the control file is manually deleted by the user or it cannot be found because an incorrect location is specified, the operation terminates after deleting the backup set entry from the control file.
RMGR is executed with a shell command and supports the following options for various functions:
| Option | Description |
|---|---|
| backup | Performs a backup with RMGR. |
| recover | Recovers a database using a backup created by RMGR. |
| delete | Deletes a backup archived by RMGR that meets the user-specified conditions. |
| --userid | User name, password, and SID to be used to access a database. --userid USERID[[/PASSWD][@SID]] If you do not want to display a password, do not enter PASSWD and then enter it when asked to enter a password. --userid USERID/[@SID] If you use an account authenticated by OS, you do not need to enter USERID and PASSWD. However, the account can perform only backup and deletion currently. --userid / |
| -v, --verbose | Displays the progress of RMGR in detail. |
| -s, --silent | Displays the progress of RMGR with minimal information. |
| -h, --help | Shows how to use RMGR's options. |
| -i, --incremental | Executes an incremental backup with the last backup. |
| -C, --cumulative | Executes an incremental backup with the last full backup. |
| -c, --compress | Compresses data before it is saved for a backup. Typically, compression increases the process time and decreases the file size. |
| -u, --skip-unused | Skips unused blocks when a data file backup is created. This option can decrease the size of the files created when performing backup. |
| -o | Directory to back up to/from. If this option is not specified for a backup, RMGR_BACKUP_DEST is used as the default dest value. If this option is not specified for a recovery, the backed up directory is automatically detected. If this option is specified, all full/incremental backups must exist in the specified directory. |
| --with-archivelog | Backs up archive logs (for hot backup) when performing backup/recovery. tbrmgr backup --with-archivelogThis option is not supported in a cluster environment, except when all instances share the same LOG_ARCHIVE_DEST in an active storage in TAC-TAS. A backup archive log file has the name in the following format. bkl_<BACKUPSET#>_t<THREAD#>-r<RESETLOGS TSN>-s<SEQUENCE#>.arc For example, when BACKUPSET#, THREAD#, RESETLOGS TSN, and SEQEUNCE# are 1, 0, 0, and 1, respectively, the file name is 'bkl_1_t0-r0-s1.arc'. |
| --for-standby | Executes backup and recovery for standby configuration. Since archive log backup is not supported in a cluster, manually back up and recover the log. |
| --clone | Backs up data files and then online Redo log for database cloning. To configure a clone database, manually move log files to the database directory and then perform recovery. |
| --before-time | Deletes backups before the specified time. Time is specified in YYYYMMDDHHMMSS format. |
| --backup_set | Deletes the specified backup set when deleting a backup. tbrmgr delete --backup_set 1 |
| --untiltime | Performs time-based incomplete recovery. Recovers to the time specified by the option. The time format is YYYYMMDDHHMMSS. tbrmgr recovery -b /backup/rmgr.inf --untiltime 20130614165736 |
| --untilchange | Performs change-based incomplete recovery. Recovers to the specified TSN. tbrmgr recover --untilchange 16218 |
| --tablespace | Tablespace for recovery or backup. When a tablespace is specified, only part of the database is recovered or backed up. tbrmgr backup -o /backup/ --tablespace usr,system |
| --with-password-file | Backs up or recovers the password file along with data files. tbrmgr backup --with-password-file |
| --wallet | Authenticates the user to access an encrypted tablespace. The specified PASSWORD is used to open the WALLET for recovery. tbrmgr recover --wallet PASSWORD |
| -p, --parallel THREAD_COUNT | Performs a backup or recovery in parallel using as many threads as the value set in user-specified THREAD_COUNT. tbrmgr backup --parallel THREAD_COUNT |
| --recover-to | Moves data and log files to a specific directory and then recovers the files. For complete recovery, online Redo log must exist in the directory in advance. tbrmgr backup --recover-to /tibero_new_directory/ |
This section describes backup and recovery scenarios using RMGR.
Archive logs are required to recover using Backup Set created from the Online (Full / Incremental) Backup in RMGR. In case the archive logs are missing, it is recommended to create backups for them by using the --with-archivelog option.
Archive log backup using RMGR is not supported for TAC environment.
RMGR performs most of the tasks automatically after the user executes a command. The user can monitor the progress without further effort.
After the backup is complete, query the V$BACKUP_SET view to check the backup set and the V$BACKUP_ARCHIVED_LOG view to check for the archive log backup included in the backup set. Query the V$BACKUP_SET_TABLESPACE view to check the tablespace information for each backup set.
Backup and recovery can be performed even with raw device data files and Tibero Active Storage configuration. When using the Active Storage configuration, the Active Storage Instance must be already configured and running.
RMGR can be used for Online Full Backup to a specified location (-o option). If the path is not specified, the default dest value is RMGR_BACKUP_DEST.
[Example 6.11] Online Full Backup Scenario
$ tbrmgr backup -o /home/tbrdb/work/6/backup/ ================================================================== = Recovery Manager(RMGR) starts = = = = TmaxData Corporation Copyright (c) 2008-. All rights reserved. = ================================================================== ============================================= RMGR - ONLINE backup ============================================= DB connected archive log check succeeded 100.00% |=======================================>| 12800/12800 blks 0.08s Synchronizing... 100.00% |=======================================>| 25600/25600 blks 0.18s Synchronizing... 100.00% |=======================================>| 12800/12800 blks 0.10s Synchronizing... 100.00% |=======================================>| 1280/1280 blks 0.02s Synchronizing... Database full backup succeeded DB disconnected RMGR backup ends $ tbsql sys/tibero SQL> set line 200 SQL> col START_TIME for a20 SQL> col FINISH_TIME for a20 SQL> select * from V$BACKUP_SET a; SET_ID START_TIME ---------- ---------------------------------------------------------------- FINISH_TIME START_TSN ---------------------------------------------------------------- ---------- FINISH_TSN RESETLOGS_TSN BASE_SET SIZE(KB) IS_PARTIAL IS_INCREMENTAL ---------- ------------- ---------- ---------- ---------- -------------- WITH_ARCHIVELOG --------------- 1 2018/06/11 2018/06/11 36321 36338 0 0 453588 NO NO NO 1 row selected. SQL> select * from V$BACKUP_ARCHIVED_LOG; 0 row selected.
RMGR can perform Online Full Backup recovery by using the Compress (-c) option for creating a backup set by compressing the data and the Skip Unused (-u) option for excluding unused blocks from the backup.
[Example 6.12] Example of Online Full Backup Using Compress and Skip Unused Options
$ tbrmgr backup -c -u -o /home/tbrdb/work/6/backup/ ================================================================== = Recovery Manager(RMGR) starts = = = = TmaxData Corporation Copyright (c) 2008-. All rights reserved. = ================================================================== ============================================= RMGR - ONLINE backup ============================================= DB connected archive log check succeeded 100.00% |=======================================>| 12800/12800 blks 1.00s Synchronizing... 100.00% |=======================================>| 25600/25600 blks 1.85s Synchronizing... 100.00% |=======================================>| 12800/12800 blks 0.00s Synchronizing... 100.00% |=======================================>| 1280/1280 blks 0.06s Synchronizing... Database full backup succeeded DB disconnected RMGR backup ends
The --with-archivelog option can be used to create archive log backup when backing up data files for recovery. Since archive logs are required to perform Online Backup recovery, it must be backed up in case the original archive logs are missing.
A backup archive log file has the name in the following format.
bkl_<BACKUPSET#>_t<THREAD#>-r<RESETLOGSTSN>-s<SEQUENCE#>.arc
For example, when BACKUPSET#, THREAD#, RESETLOGS TSN, and SEQEUNCE# are 1, 0, 0, and 1, respectively, the file name is 'bkl_1_t0-r0-s1.arc'.
Query the V$BACKUP_SET view to check whether an archive log backup exists in each backup set and to view the archive log backup information.
[Example 6.13] Example of Online Full Backup Using Archive Log Option
$ tbrmgr backup --with-archivelog -o /home/tbrdb/work/6/backup/ ================================================================== = Recovery Manager(RMGR) starts = = = = TmaxData Corporation Copyright (c) 2008-. All rights reserved. = ================================================================== ============================================= RMGR - ONLINE backup ============================================= DB connected archive log check succeeded 100.00% |=======================================>| 12800/12800 blks 0.08s Synchronizing... 100.00% |=======================================>| 25600/25600 blks 0.18s Synchronizing... 100.00% |=======================================>| 12800/12800 blks 0.08s Synchronizing... 100.00% |=======================================>| 1280/1280 blks 0.02s Synchronizing... Database full backup succeeded DB disconnected RMGR backup ends $ tbsql sys/tibero SQL> set line 200 SQL> col START_TIME for a20 SQL> col FINISH_TIME for a20 SQL> select * from V$BACKUP_SET a; SET_ID START_TIME ---------- ---------------------------------------------------------------- FINISH_TIME START_TSN ---------------------------------------------------------------- ---------- FINISH_TSN RESETLOGS_TSN BASE_SET SIZE(KB) IS_PARTIAL IS_INCREMENTAL ---------- ------------- ---------- ---------- ---------- -------------- WITH_ARCHIVELOG --------------- 1 2016/06/16 2016/06/16 34386 34441 0 0 453588 NO NO YES 1 row selected. SQL> set line 200 SQL> col MIN_LOG_TIME for a20 SQL> col MAX_LOG_TIME for a20 SQL> col RESETLOG_TIME for a20 SQL> select * from V$BACKUP_ARCHIVED_LOG a; SET_ID MIN_LOG_TSN MAX_LOG_TSN MIN_LOG_TIME MAX_LOG_TIME ---------- ----------- ----------- -------------------- -------------------- 1 34386 34441 2016/06/16 2016/06/16 MIN_LOG_SEQUENCE MAX_LOG_SEQUENCE RESETLOG_TSN RESETLOG_TIME ---------------- ---------------- ------------ -------------------- 2 2 0 1 row selected.
An Incremental backup can be performed if there is at least one full backup set created from the Online Full Backup. The current database is compared with the latest backup set (base set) to back up only the changes between the two. This dramatically reduces the backup set size, but the backup set cannot be used if the base set is missing.
Query the V$BACKUP_SET view to check whether a backup set is an incremental backup set and the ID of the base set used for comparison. Base Set ID is displayed with a 0 for a full backup set which does not have a base set.
[Example 6.14] Example of Incremental Backup Using Archive Log Option
$ tbrmgr backup -i --with-archivelog -o /home/tbrdb/work/6/backup/ ================================================================== = Recovery Manager(RMGR) starts = = = = TmaxData Corporation Copyright (c) 2008-. All rights reserved. = ================================================================== ============================================= RMGR - INCREMENTAL backup ============================================= DB connected archive log check succeeded 100.00% |=======================================>| 12800/12800 blks 0.04s Synchronizing... 100.00% |=======================================>| 25600/25600 blks 0.04s Synchronizing... 100.00% |=======================================>| 12800/12800 blks 0.02s Synchronizing... 100.00% |=======================================>| 1280/1280 blks 0.02s Synchronizing... Database incremental backup succeeded DB disconnected RMGR backup ends $ tbsql sys/tibero SQL> set line 200 SQL> col START_TIME for a20 SQL> col FINISH_TIME for a20 SQL> select * from V$BACKUP_SET a; SET_ID START_TIME ---------- ---------------------------------------------------------------- FINISH_TIME START_TSN ---------------------------------------------------------------- ---------- FINISH_TSN RESETLOGS_TSN BASE_SET SIZE(KB) IS_PARTIAL IS_INCREMENTAL ---------- ------------- ---------- ---------- ---------- -------------- WITH_ARCHIVELOG --------------- 1 2016/06/16 2018/06/11 34386 34441 0 0 453588 NO NO YES 2 2016/06/16 2018/06/11 34448 35234 0 1 23730 NO YES YES 2 rows selected. SQL> set line 200 SQL> col MIN_LOG_TIME for a20 SQL> col MAX_LOG_TIME for a20 SQL> col RESETLOG_TIME for a20 SQL> select * from V$BACKUP_ARCHIVED_LOG a; SET_ID MIN_LOG_TSN MAX_LOG_TSN MIN_LOG_TIME MAX_LOG_TIME ---------- ----------- ----------- -------------------- -------------------- 1 34386 34441 2016/06/16 2016/06/16 2 34448 35234 2016/06/16 2016/06/16 MIN_LOG_SEQUENCE MAX_LOG_SEQUENCE RESETLOG_TSN RESETLOG_TIME ---------------- ---------------- ------------ -------------------- 2 2 0 6 6 0 2 row selected.
RMGR can perform recovery by using the backup set created through the Online Full Backup. The following is a recovery example for when the archive log backup is missing from the backup set. In this case, the original archive logs are required to perform recovery.
[Example 6.15] Example of Recovery Using Online Full Backup
$ tbsql sys/tibero SQL> set line 200 SQL> col START_TIME for a20 SQL> col FINISH_TIME for a20 SQL> select * from V$BACKUP_SET a; SET_ID START_TIME ---------- ---------------------------------------------------------------- FINISH_TIME START_TSN ---------------------------------------------------------------- ---------- FINISH_TSN RESETLOGS_TSN BASE_SET SIZE(KB) IS_PARTIAL IS_INCREMENTAL ---------- ------------- ---------- ---------- ---------- -------------- WITH_ARCHIVELOG --------------- 1 2016/06/16 2016/06/16 34386 34441 0 0 453588 NO NO NO 1 row selected. SQL> quit Disconnected. $ tbrmgr recover -o /home/tbrdb/work/6/backup/ ================================================================== = Recovery Manager(RMGR) starts = = = = TmaxData Corporation Copyright (c) 2008-. All rights reserved. = ================================================================== ============================================= RMGR - recovery ============================================= Tibero instance terminated (ABNORMAL mode). Control file #0 (/home/tbrdb/work/6/database/TB6/c1.ctl) is accessible Listener port = 45648 Tibero 6 TmaxData Corporation Copyright (c) 2008-. All rights reserved. Tibero instance started up (MOUNT mode). DB Connected RMGR BEGIN RESTORE full backup set_id: 1 last incremental backup set_id: 1 Applying FULL BACKUP (set_id:1, ts_id:0, df_id:0) 100.00% |=======================================>| 12800/12800 blks 0.00s Synchronizing... Applying FULL BACKUP (set_id:1, ts_id:1, df_id:1) 100.00% |=======================================>| 25600/25600 blks 0.20s Synchronizing... Applying FULL BACKUP (set_id:1, ts_id:3, df_id:2) 100.00% |=======================================>| 12800/12800 blks 0.00s Synchronizing... Applying FULL BACKUP (set_id:1, ts_id:4, df_id:3) 100.00% |=======================================>| 1280/1280 blks 0.00s Synchronizing... Database restore succeeded recoverSQL: ALTER DATABASE RECOVER AUTOMATIC Database automatic recovery succeeded DB disconnected Tibero instance terminated (NORMAL mode). Listener port = 45648 Tibero 6 TmaxData Corporation Copyright (c) 2008-. All rights reserved. Tibero instance started up (NORMAL mode). RMGR recovery ends
RMGR can perform recovery by using the backup set created through the Online Full and Archive Log Backups. The following is a recovery example for when the original archive logs are missing. It shows a successful recovery result when the --with-archivelog option is used to restore the archive log backup. Recovery fails if the option is omitted.
[Example 6.16] Example of Recovery Using Online Full and Archive Log Backups
$ tbsql sys/tibero SQL> set line 200 SQL> col START_TIME for a20 SQL> col FINISH_TIME for a20 SQL> select * from V$BACKUP_SET a; SET_ID START_TIME ---------- ---------------------------------------------------------------- FINISH_TIME START_TSN ---------------------------------------------------------------- ---------- FINISH_TSN RESETLOGS_TSN BASE_SET SIZE(KB) IS_PARTIAL IS_INCREMENTAL ---------- ------------- ---------- ---------- ---------- -------------- WITH_ARCHIVELOG --------------- 1 2016/06/16 2016/06/16 34386 34441 0 0 453588 NO NO YES 1 row selected. SQL> set line 200 SQL> col MIN_LOG_TIME for a20 SQL> col MAX_LOG_TIME for a20 SQL> col RESETLOG_TIME for a20 SQL> select * from V$BACKUP_ARCHIVED_LOG a; SET_ID MIN_LOG_TSN MAX_LOG_TSN MIN_LOG_TIME MAX_LOG_TIME ---------- ----------- ----------- -------------------- -------------------- 1 34386 34441 2016/06/15 2016/06/16 MIN_LOG_SEQUENCE MAX_LOG_SEQUENCE RESETLOG_TSN RESETLOG_TIME ---------------- ---------------- ------------ -------------------- 2 2 0 1 row selected. SQL> quit Disconnected. $ tbrmgr recover -o /home/tbrdb/work/6/backup/ ================================================================== = Recovery Manager(RMGR) starts = = = = TmaxData Corporation Copyright (c) 2008-. All rights reserved. = ================================================================== ============================================= RMGR - recovery ============================================= Tibero instance terminated (ABNORMAL mode). Control file #0 (/home/tbrdb/work/6/database/TB6/c1.ctl) is accessible Listener port = 45648 Tibero 6 TmaxData Corporation Copyright (c) 2008-. All rights reserved. Tibero instance started up (MOUNT mode). DB Connected RMGR BEGIN RESTORE full backup set_id: 1 last incremental backup set_id: 1 Applying FULL BACKUP (set_id:1, ts_id:0, df_id:0) 100.00% |=======================================>| 12800/12800 blks 0.00s Synchronizing... Applying FULL BACKUP (set_id:1, ts_id:1, df_id:1) 100.00% |=======================================>| 25600/25600 blks 0.20s Synchronizing... Applying FULL BACKUP (set_id:1, ts_id:3, df_id:2) 100.00% |=======================================>| 12800/12800 blks 0.00s Synchronizing... Applying FULL BACKUP (set_id:1, ts_id:4, df_id:3) 100.00% |=======================================>| 1280/1280 blks 0.00s Synchronizing... Database restore succeeded recoverSQL: ALTER DATABASE RECOVER AUTOMATIC RMGR Error: recovery failed (automatic recovery failed) SVR Error: Unable to find archive log file for thread 0 from change 34428. $ tbrmgr recover --with-archivelog -o /home/tbrdb/work/6/backup/ ================================================================== = Recovery Manager(RMGR) starts = = = = TmaxData Corporation Copyright (c) 2008-. All rights reserved. = ================================================================== ============================================= RMGR - recovery ============================================= Tibero instance terminated (ABNORMAL mode). Control file #0 (/home/tbrdb/work/6/database/TB6/c1.ctl) is accessible Listener port = 45648 Tibero 6 TmaxData Corporation Copyright (c) 2008-. All rights reserved. Tibero instance started up (MOUNT mode). DB Connected RMGR BEGIN RESTORE full backup set_id: 1 last incremental backup set_id: 1 Applying FULL BACKUP (set_id:1, ts_id:0, df_id:0) 100.00% |=======================================>| 12800/12800 blks 0.00s Synchronizing... Applying FULL BACKUP (set_id:1, ts_id:1, df_id:1) 100.00% |=======================================>| 25600/25600 blks 0.20s Synchronizing... Applying FULL BACKUP (set_id:1, ts_id:3, df_id:2) 100.00% |=======================================>| 12800/12800 blks 0.00s Synchronizing... Applying FULL BACKUP (set_id:1, ts_id:4, df_id:3) 100.00% |=======================================>| 1280/1280 blks 0.00s Synchronizing... Database restore succeeded recoverSQL: ALTER DATABASE RECOVER AUTOMATIC Database automatic recovery succeeded DB disconnected Tibero instance terminated (NORMAL mode). Listener port = 45648 Tibero 6 TmaxData Corporation Copyright (c) 2008-. All rights reserved. Tibero instance started up (NORMAL mode). RMGR recovery ends
RMGR can perform recovery by merging the backup sets created through the Online Full and Incremental Backups.
[Example 6.17] Example of Recovery Using Incremental and Archive Log Backups
$ tbsql sys/tibero SQL> set line 200 SQL> col START_TIME for a20 SQL> col FINISH_TIME for a20 SQL> select * from V$BACKUP_SET a; SET_ID START_TIME ---------- ---------------------------------------------------------------- FINISH_TIME START_TSN ---------------------------------------------------------------- ---------- FINISH_TSN RESETLOGS_TSN BASE_SET SIZE(KB) IS_PARTIAL IS_INCREMENTAL ---------- ------------- ---------- ---------- ---------- -------------- WITH_ARCHIVELOG --------------- 1 2016/06/16 2018/06/11 34386 34441 0 0 453588 NO NO YES 2 2016/06/16 2018/06/11 34448 35234 0 1 23730 NO YES YES 2 rows selected. 2 rows selected. SQL> set line 200 SQL> col MIN_LOG_TIME for a20 SQL> col MAX_LOG_TIME for a20 SQL> col RESETLOG_TIME for a20 SQL> select * from V$BACKUP_ARCHIVED_LOG a; SET_ID MIN_LOG_TSN MAX_LOG_TSN MIN_LOG_TIME MAX_LOG_TIME ---------- ----------- ----------- -------------------- -------------------- 1 34386 34441 2016/06/16 2016/06/16 2 34448 35234 2016/06/16 2016/06/16 MIN_LOG_SEQUENCE MAX_LOG_SEQUENCE RESETLOG_TSN RESETLOG_TIME ---------------- ---------------- ------------ -------------------- 2 2 0 6 6 0 2 row selected. SQL> quit Disconnected. $ tbrmgr recover --with-archivelog -o /home/tbrdb/work/6/backup ================================================================== = Recovery Manager(RMGR) starts = = = = TmaxData Corporation Copyright (c) 2008-. All rights reserved. = ================================================================== ============================================= RMGR - recovery ============================================= Tibero instance terminated (ABNORMAL mode). Control file #0 (/home/tbrdb/work/6/database/TB6/c1.ctl ) is accessible Listener port = 45648 Tibero 6 TmaxData Corporation Copyright (c) 2008-. All rights reserved. Tibero instance started up (MOUNT mode). DB Connected RMGR BEGIN RESTORE full backup set_id: 1 last incremental backup set_id: 2 Applying FULL BACKUP (set_id:1, ts_id:0, df_id:0) 100.00% |=======================================>| 12800/12800 blks 0.00s Synchronizing... Applying FULL BACKUP (set_id:1, ts_id:1, df_id:1) 100.00% |=======================================>| 25600/25600 blks 0.20s Synchronizing... Applying FULL BACKUP (set_id:1, ts_id:3, df_id:2) 100.00% |=======================================>| 12800/12800 blks 0.00s Synchronizing... Applying FULL BACKUP (set_id:1, ts_id:4, df_id:3) 100.00% |=======================================>| 1280/1280 blks 0.00s Synchronizing... Applying INCREMENTAL BACKUP (set_id:2, ts_id:0, df_id:0) 100.00% |=======================================>| 12800/12800 blks 0.60s Synchronizing... Applying INCREMENTAL BACKUP (set_id:2, ts_id:1, df_id:1) 100.00% |=======================================>| 25600/25600 blks 1.20s Synchronizing... Applying INCREMENTAL BACKUP (set_id:2, ts_id:3, df_id:2) 100.00% |=======================================>| 12800/12800 blks 0.80s Synchronizing... Applying INCREMENTAL BACKUP (set_id:2, ts_id:4, df_id:3) 100.00% |=======================================>| 1280/1280 blks 0.00s Synchronizing... Database restore succeeded recoverSQL: ALTER DATABASE RECOVER AUTOMATIC Database automatic recovery succeeded DB disconnected Tibero instance terminated (NORMAL mode). Listener port = 45648 Tibero 6 TmaxData Corporation Copyright (c) 2008-. All rights reserved. Tibero instance started up (NORMAL mode). RMGR recovery ends
The --tablespace option can be used to delete specified tablespace(s). Query the V$BACKUP_SET_TABLESPACE view to check for tablespaces that are included in each backup set.
[Example 6.18] Example of Tablespace Recovery
$ tbsql sys/tibero SQL> set line 200 SQL> col NAME for a20 SQL> select * from V$TABLESPACE a; TS# NAME TYPE BIGFILE FLASHBACK_ON ---------- -------------------- ---- ------- ------------ 0 SYSTEM DATA NO NO 1 UNDO UNDO NO NO 2 TEMP TEMP NO NO 3 USR DATA NO NO 4 SYSSUB DATA NO NO 5 rows selected. SQL> select * from V$BACKUP_SET_TABLESPACE; SET_ID TS# ---------- ---------- 1 0 1 1 1 3 1 4 4 rows selected. SQL> quit Disconnected. $ tbrmgr recover --tablespace USR -o /home/tbrdb/work/6/backup/ ================================================================== = Recovery Manager(RMGR) starts = = = = TmaxData Corporation Copyright (c) 2008-. All rights reserved. = ================================================================== ============================================= RMGR - recovery ============================================= Tibero instance terminated (ABNORMAL mode). Control file #0 (/home/tbrdb/work/6/database/TB6/c1.ctl) is accessible Listener port = 45648 Tibero 6 TmaxData Corporation Copyright (c) 2008-. All rights reserved. Tibero instance started up (MOUNT mode). DB Connected RMGR BEGIN RESTORE full backup set_id: 1 last incremental backup set_id: 1 Applying FULL BACKUP (set_id:1, ts_id:3, df_id:2) 100.00% |=======================================>| 12800/12800 blks 0.00s Synchronizing... Database restore succeeded recoverSQL: ALTER DATABASE RECOVER AUTOMATIC Database automatic recovery succeeded DB disconnected Tibero instance terminated (NORMAL mode). Listener port = 45648 Tibero 6 TmaxData Corporation Copyright (c) 2008-. All rights reserved. Tibero instance started up (NORMAL mode). RMGR recovery ends
This section describes how to delete a backup in RMGR.
To delete a backup in RMGR, the target backup can be specified in two ways. Use the --backup_set option to specify a backup set ID to delete a single backup. Use the --beforetime option to delete all backup sets that were created before the specified time.
RMGR references the control file to check the existence of the specified backup set and backup path before deleting the backup. Hence, the control file must be able to reference the backup set, and the backup destination option (-) must be specified if the actual backup path is different from the path specified in the control file. Query the V$BACKUP_SET view to check the backup set and the V$BACKUP_ARCHIVED_LOG view to check for the archive log backup included in the backup set.
If the backup set specified in the control file is manually deleted by the user or it cannot be found because an incorrect location is specified, the operation terminates after deleting the backup set entry from the control file. The same applies for when the backup set is saved on a tape device.
RMGR can delete a user-specified backup set by using the --backup_set option with the Backup Set ID.
The following example deletes a backup set with Backup Set ID = 1.
[Example 6.19] Deleting a Backup Using a Backup Set ID
$ tbsql sys/tibero SQL> set line 200 SQL> col START_TIME for a20 SQL> col FINISH_TIME for a20 SQL> select * from V$BACKUP_SET a; SET_ID START_TIME ---------- ---------------------------------------------------------------- FINISH_TIME START_TSN ---------------------------------------------------------------- ---------- FINISH_TSN RESETLOGS_TSN BASE_SET SIZE(KB) IS_PARTIAL IS_INCREMENTAL ---------- ------------- ---------- ---------- ---------- -------------- WITH_ARCHIVELOG --------------- 1 2018/06/11 2018/06/11 37093 37109 0 0 453588 NO NO YES 2 2018/06/11 2018/06/11 37361 37377 0 0 453588 NO NO YES 3 2018/06/11 2018/06/11 37390 37406 0 0 453588 NO NO YES 3 rows selected. SQL> quit Disconnected. $ tbrmgr delete --backup_set 1 -o /home/tbrdb/work/6/backup ================================================================== = Recovery Manager(RMGR) starts = = = = TmaxData Corporation Copyright (c) 2008-. All rights reserved. = ================================================================== ============================================= RMGR - delete ============================================= DB connected #1 of #3 backup sets erased RMGR delete ends $ tbsql sys/tibero SQL> set line 200 SQL> col START_TIME for a20 SQL> col FINISH_TIME for a20 SQL> select * from V$BACKUP_SET a; SET_ID START_TIME ---------- ---------------------------------------------------------------- FINISH_TIME START_TSN ---------------------------------------------------------------- ---------- FINISH_TSN RESETLOGS_TSN BASE_SET SIZE(KB) IS_PARTIAL IS_INCREMENTAL ---------- ------------- ---------- ---------- ---------- -------------- WITH_ARCHIVELOG --------------- 2 2018/06/11 2018/06/11 37361 37377 0 0 453588 NO NO YES 3 2018/06/11 2018/06/11 37390 37406 0 0 453588 NO NO YES 2 rows selected.
RMGR can use the --beforetime option to delete all backup sets that were created before the specified time.
The following example deletes all backup sets that were created (FINISH_TIME) before "2016/06/17 12:00:00". The backup date format is 'YYYYMMDDHH(24)mmss'.
[Example 6.20] Deleting a Backup Using a Backup Date
$ tbsql sys/tibero SQL> set line 200 SQL> col START_TIME for a20 SQL> col FINISH_TIME for a20 SQL> select * from V$BACKUP_SET a; SET_ID START_TIME ---------- ---------------------------------------------------------------- FINISH_TIME START_TSN ---------------------------------------------------------------- ---------- FINISH_TSN RESETLOGS_TSN BASE_SET SIZE(KB) IS_PARTIAL IS_INCREMENTAL ---------- ------------- ---------- ---------- ---------- -------------- WITH_ARCHIVELOG --------------- 2 2018/06/11 2018/06/11 37361 37377 0 0 453588 NO NO YES 3 2018/06/11 2018/06/11 37390 37406 0 0 453588 NO NO YES 2 rows selected. SQL> quit Disconnected. $ tbrmgr delete --beforetime 20180612120000 -o /home/tbrdb/work/6/backup/ ================================================================== = Recovery Manager(RMGR) starts = = = = TmaxData Corporation Copyright (c) 2008-. All rights reserved. = ================================================================== ============================================= RMGR - delete ============================================= DB connected #2 of #2 backup sets erased RMGR delete ends $ tbsql sys/tibero SQL> set line 200 SQL> col START_TIME for a20 SQL> col FINISH_TIME for a20 SQL> select * from V$BACKUP_SET a; 0 rows selected.