Overview

This document explains the process of performing recovery when operating Tibero and an unexpected failure causes the database to be unable to operate normally.

Method

1. Overview of Recovery

• To perform recovery, a backed-up database must exist.
• Tibero records all changes occurring in the database into log files, so all changes made to the database after the backup can be recovered by applying the logs.

Note

Log files include data modified by uncommitted transactions, so both archive log files and log files can be used during recovery.

2. Recovery Process

 1) Applying changes not recorded in data files using logs

• The database reaches a stable state by applying all log changes to the data files.
• All operations up to a specific point in time during database operation must be reflected, and no changes should occur afterward.
• The database can only be started if normal recovery is completed and it reaches a stable state.

 2) Recovery with uncommitted data

• Recovery is performed using data modified by transactions that were not committed when the database was shut down.

3. Types of Recovery

 1) Recovery types by failure type (when backup exists)

 2) Recovery by startup mode

 3) Complete recovery and incomplete recovery

• Complete Recovery

  - Media recovery applying all logs up to the most recent online log file.

• Incomplete Recovery

  - Recovery up to a specific point before the most recent online log file.

  - After incomplete recovery, Tibero must be started in RESETLOGS mode.

  - RESETLOGS initializes online log files, making them incompatible with previously backed-up files, so new backups must be taken.

   

4. Recovery Methods

 1) NOARCHIVE MODE recovery method

• Without ARCHIVELOG mode, media recovery is not possible; only recovery from backup files of specific corrupted data files is possible.
• Backup must be taken after completely shutting down Tibero by backing up all files that compose Tibero as is, excluding temporary files.
• Recovery can only restore to the state of Tibero at the time of backup; all subsequent operations must be redone.

Note

A possible issue is that physical disk errors may prevent recovery to the original location.

• If data files or online log files cannot be restored to their original locations, restore them to a different location and modify the data file locations set in the control file.

   

  $ tbboot mount
  Change core dump dir to /tibero/tibero7/bin/prof. 
  Listener port = 8629
  
  Tibero 7
  
  TmaxTibero Corporation Copyright (c) 2020-. All rights reserved. 
  Tibero instance started up (MOUNT mode).
  
  
  $ tbsql sys/tibero
  
  SQL> select file#, name from v$datafile;
  
      FILE# NAME
  ---------- ----------------------------------------    
      0 /tbdata/system001.dtf
      1 /tbdata/undo001.dtf
      2 /tbdata/usr001.dtf
      3 /tbdata/tpr_ts.dtf
  
  4 rows selected.
  
  SQL> alter database rename file '/tbdata/system001.dtf' to '/tbdata2/system001.dtf'; 
  
  Altered. 
  
  SQL> tbdown immedate
  
  Tibero instance was terminated.
  
  SQL> quit
  
  $ tbboot

 2) ARCHIVELOG MODE recovery method

• With ARCHIVELOG mode, media recovery is possible by backing up data files and archive log files.
• If a log file is corrupted, recovery is attempted by copying another log member file from the same log group.

Note

If all log files in a log group are corrupted, remove the corrupted group and add another group. However, if the corrupted group is the current log group, incomplete recovery to the commit point is required.

• Data files belonging to the system tablespace are essential for Tibero operation and must be restored. For corrupted specific data files, restore from backup to proceed with recovery.

   

  TBR-1024 : Database needs media recovery failed(/tbdata/system001.dtf)

1. Check v$recover_file view for any MISSING data

    

2. Copy existing backed-up data files and apply

    

3. Check v$recover_file view for any CREATE FILE data

    

4. Execute RECOVER AUTOMATIC: alter database recover automatic database

    

5. Check v$recover_file view for any ERROR data

    

6. After tbboot, verify normal operation

3) When data files of non-system tablespaces are corrupted

• You can either restore the data files of the non-system tablespace or drop the tablespace.
   
• Restoration is the same as above; dropping the tablespace is suitable if its contents are not important or can be safely deleted.

TBR-1024 : Database needs media recovery failed(/tbdata/test1.dtf)

1. Check ERROR parts in v$recover_file view

    

2. Set datafile offline for drop: alter database datafile 4 offline for drop

    

3. tbdown immediate

    

4. tbboot

    

5. Connect with tbsql: tbsql sys/tibero

    

6. Drop tablespace: drop tablespace <TablespaceName> including contents and datafiles;

    

7. Check data files: select file#, name from v$datafile;

    

4) When data files without backup are corrupted

• Create new data files using ALTER DATABASE CREATE DATAFILE and then perform recovery.
   

• All archive log files generated since the tablespace creation time must exist.

   

1. Check ERROR parts in v$recover_file view

    

2. Create deleted tablespace: alter database create datafile <data file path>;

    

3. Check for FILE CREATE data in v$recover_file view

    

4. Run recover automatic: alter database recover automatic database;

    

5. Check for errors in v$recover_file view

    

6. After tbdown, run tbboot

    

7. Log in and verify data (select * from <table name>;)

5) When control file is corrupted

• You need to create a new control file using CREATE CONTROLFILE statement.
• It is recommended to maintain multiple mirrored copies of control files to prevent failure.
  If a mirrored file is intact, copy it to the corrupted file and restart Tibero.

• When creating a control file, temporary files (=TEMP TABLESPACE) do not exist and must be added.

   

  ERROR_TCCF_READ_FAILED(-24003) [ Unable to read control file ]

 Recovery Procedure

1. Check control file open error during tbboot

   $ tbboot
   listener port = 8629
   change core dump dir to /tibero/tibero7/bin/prof
   ******************************************************** 
   * Critical Warning : Raise svmode failed. The reason is 
   *   TBR-24003 :  Unable to read control file.   
   *   Current server mode is NOMOUNT. 
   ********************************************************
   
   Tibero 7
   
   TmaxTibero Corporation Copyright (c) 2020-. All rights reserved. 
   Tibero instance started up (NOMOUNT mode).

2. Run with logical backup control file content (create controlfile reuse database….)
    
3. Restart: tbdown immediate, tbboot
    
4. Perform media recovery: alter database recover automatic;
    
5. Check temp tablespace with dba_temp_files view
    
6. Add temp tablespace: alter tablespace temp add tempfile [file path] size size reuse (exists in logical backup control file)

• If log file is corrupted, recover by copying another log file in the same group (if mirrored).
• If all log files in a log group are corrupted, remove the corrupted group and add another group. If the corrupted group is the current log group, incomplete recovery is required.

• Symptoms (Tibero7 version, related messages during tbboot after failure)

[Table] Log status and boot behavior by failure

• Processing order

[Table] Recovery methods by log status and failure

Recovery Operation Procedure

Media Recovery – Complete Recovery

• Describes recovery when data files are deleted or similar failures occur.
• Example: [Failure] When a data file is deleted [Data File Deletion: steps for explanation]

[Example] Failure when a data file is deleted

$ tbdown immediate
$ cp /tbdata /tbdata_bak --backup location
$ tbboot
$ tbsql tibero/tmax

SQL> CREATE TABLE T1 (C1 VARCHAR(5));        -- New data inserted.

created.

SQL> INSERT INTO T1 (C1) VALUES ('00011'); 
SQL> INSERT INTO T1 (C1) VALUES ('00012'); 
SQL> INSERT INTO T1 (C1) VALUES ('000113'); 
SQL> COMMIT;

SQL> conn sys/tibero
SQL> ALTER SYSTEM SWITCH LOGFILE;         --New data reflected in Archive Log.
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;

SQL> exit

$ tbdown immediate
$ rm /tbdata/my_file001.dtf                --Data file deleted

Tables and data are created/entered after backup, and data is reflected in Archive Log.

Recovery Procedure – Applying Backup File

1. Start in MOUNT mode: When data files are deleted, start in MOUNT mode.
2. Log in as system account: Log in as sys account.
3. Execute RECOVER AUTOMATIC: Run "ALTER DATABASE RECOVER AUTOMATIC".
4. After tbdown, run tbboot and log in to verify data.

[Example] Complete Recovery (Recovery after applying backup data)

$ cp /tbdata/my_file001.dtf /tbdata --Copy backed-up data file to original location
$ tbboot mount
$ tbsql sys /tibero

SQL> ALTER DATABASE RECOVER AUTOMATIC; 
SQL> exit

$ tbdown immediate
$ tbboot
$ tbsql tibero/tmax

SQL> select * from t1; C1
-----
00011
00012
00013

3 rows selected.

Media Recovery – Incomplete Recovery

Recovery to a previous point in time in archive log mode

Example: [Check data time] Time-based incomplete recovery (Archive log mode)

[Example] Time-based incomplete recovery (Check data time)

$ tbdown immediate
$ cp /tbdata /tbdata_bak --Backup files
$ tbboot                         
$ tbsql tibero/tmax

SQL> select * from t1;                      --First data input

C1
----------
00011

SQL> !date                               --Check time 2023. 11. 29. (Wed) 18:01:04 KST
SQL> INSERT INTO T1 (C1) VALUES ('00021'); --Second data input
SQL> COMMIT;
SQL> select * from t1;

C1
----------
00011
00021

SQL> exit

$ tbdown immediate

Recovery Procedure - Applying Backup File

Apply entire backup file

1. Start in MOUNT mode

2. Log in as sys account

3. Recover up to a specific time before the target point

SQL> alter database recover automatic database until time '[Previous time: 'YYYY-MM-DD HH24:MI:SS']';

4. Run tbdown immediate from the command line

5. Execute RESETLOGS: tbboot resetlogs

Log in and verify data

Example: [Time-based recovery] Time-based incomplete recovery (Archive log mode)

[Example] Incomplete Recovery (Time-based Recovery)

$ cp /tbdata_bak  /tbdata --Must apply entire backup file when applying backup
$ tbboot –t mount
$ export TB_NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS' -- Client DATE format setting
$ tbsql sys/tibero

SQL> alter database recover automatic database until time '2023-11-29 18:01:04'; --Apply recovery base time

Altered.

SQL>exit

$ tbdown immediate
$ tbboot resetlogs         --Execute RESETLOGS
$ tbsql sys/tibero

SQL> select * from t1;      --Data restored to previous point

C1
--------
00011

1 row selected.

Recovery Verification

• Once Tibero starts normally (Normal mode), you can immediately connect and use Tibero.
• After performing media recovery incomplete recovery, if started in resetlogs mode, previously backed-up data files and archive log files cannot be used, so
   you must perform a new database online backup to create a new backup copy.
• tbexport / tbimport / flashback features can only be performed when Tibero is running normally [Normal mode].

Recovery Reference

Views related to recovery

[Table] Recovery-related reference views