Chapter 6. Backup and Recovery

Table of Contents

6.1. Component Files in Tibero
6.2. Backups
6.2.1. Backup Types
6.2.2. Performing a Backup
6.3. Recovery
6.3.1. Recovery for Each Boot Mode
6.3.2. Crash Recovery
6.3.3. Media Recovery
6.3.4. Online Media Recovery
6.4. Recovery Manager (RMGR)
6.4.1. Basic Functions
6.4.2. RMGR Options
6.4.3. Backup and Recovery Examples
6.4.4. Example of Deleting a Backup

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:

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:

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.

Backup for Control Files

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).

The file that was created, ctrlfile1.sql, includes the following data:


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.

Note

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:


Consistent Backup

This section describes how to back up data after Tibero terminates normally.

Viewing Data Files and Online Log Files

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.


Searching online log files in MOUNT or OPEN state is shown below:


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.

Inconsistent Backup

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:

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.

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.

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.

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:

RMGR is executed with a shell command and supports the following options for various functions:

OptionDescription
backupPerforms a backup with RMGR.
recoverRecovers a database using a backup created by RMGR.
deleteDeletes 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, --verboseDisplays the progress of RMGR in detail.
-s, --silentDisplays the progress of RMGR with minimal information.
-h, --helpShows how to use RMGR's options.
-i, --incrementalExecutes an incremental backup with the last backup.
-C, --cumulativeExecutes an incremental backup with the last full backup.
-c, --compressCompresses data before it is saved for a backup. Typically, compression increases the process time and decreases the file size.
-u, --skip-unusedSkips unused blocks when a data file backup is created. This option can decrease the size of the files created when performing backup.
-oDirectory 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/ 

This section describes backup and recovery scenarios using RMGR.

Note

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.

Note

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.

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.

[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.


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.


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 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.

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 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.

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 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


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 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


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 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


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 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.

Note

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.

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.

[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.


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'.