Backup and Recovery
Backup for Control Files
Physical Backup for Control Files
SQL> alter database backup controlfile to
'/tibero6/backup/ctrlfile1.ctl';
Altered.
Logical Backup for Control Files
SQL> alter database backup controlfile to trace as
'/tibero6/backup/ctrlfile1.sql' reuse NORESETLOGS;
Altered.
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 ;
Specifying the Location of a Control File
CONTROL_FILES=$TB_HOME/database/$TB_SID/
Control File Lookup
SQL> SELECT NAME FROM V$CONTROLFILE; NAME ------------------------------------------------------------ /disk1/c1.ctl /disk2/c2.ctl 2 selected.
Consistent Backup
Data File Lookup
SQL> SELECT NAME FROM V$DATAFILE; NAME ------------------------------------------------------------ /disk1/system001.dtf /disk2/undo001.dtf /disk3/user001.dtf 3 selected.
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.
The database should be terminated in NORMAL mode as follows:
SQL> tbdown NORMAL;
Tibero instance was terminated.
Inconsistent Backup
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
Inconsistent Backup - Selecting a Tablespace
SQL> select name,type from v$tablespace; NAME TYPE ------------------------------ ---- SYSTEM DATA UNDO UNDO USER DATA TEMP TEMP 3 selected.
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.
Recovery（恢复）
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（介质恢复）
Complete Recovery
A complete recovery reflects all logs in an online log file including the most recent log.
Incomplete Recovery
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.
Online Media Recovery
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.
Recovery Manager (RMGR)恢复管理器,类似于Oracle的RMAN
基本功能
RMGR支持各种备份方法和恢复场景。RMGR在Tibero中的功能如下:
在线完整备份
对Tibero数据库中的所有数据文件执行联机备份。为此，数据库必须处于ARCHIVELOG模式。RMGR自动将所有表空间置于热备份状态，然后使用数据库的Begin备份功能执行联机备份。
备份完成后，RMGR使用数据库的End backup函数从热备份状态释放所有表空间。RMGR通过查看V$数据文件自动决定备份哪些数据文件。
增量备份
如果通过RMGR接收到联机备份，则可以执行增量备份。只记录以前备份的更改，而不备份所有文件。这可以极大地减少备份所需的磁盘空间。
要使用此功能，RMGR必须首先执行在线备份。通过比较当前数据库和备份数据库创建备份文件。这个函数只能通过RMGR使用。
自动恢复
自动恢复使用RMGR创建的备份来自动恢复数据库。备份信息保存在控制文件中。通过对控制文件中在线全备份和增量备份信息的分析，实现信息的自动合并和数据库的恢复。如果无法访问控制文件，则可以使用-o选项指定备份目标来使用备份控制文件。
-
Note
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-archivelog This 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. This is not supported in TAS. tbrmgr backup --recover-to /tibero_new_directory/ |
Backup and Recovery Examples
Online Full Backup
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.
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可以通过使用Compress (-c)选项(通过压缩数据创建备份集)和Skip (-u)选项(将未使用的块排除在备份之外)来执行在线完全备份恢复。
Online Full Backup Using Compress and Skip Unused Options
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 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
Online Full Backup Using Archive Log Option
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 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.
Incremental Backup Using Archive Log Option
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 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.
Recovery Using Online Full Backup
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 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可以通过使用通过在线完整和归档日志备份创建的备份集来执行恢复。以下是原始归档日志丢失时的恢复示例。当使用—with-archivelog选项恢复归档日志备份时，将显示一个成功的恢复结果。如果该选项被省略，则恢复失败。
Recovery Using Online Full and Archive Log Backups
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 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
Recovery Using Online Full and Incremental Backups
RMGR can perform recovery by merging the backup sets created through the Online Full and Incremental Backups.
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
Tablespace Recovery
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 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
Example of Deleting a Backup
Deleting a Backup Using a Backup Set ID
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.
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.
Deleting a Backup Using a Backup Date
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'.
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.