Skip to main content

Search

Welcome to Tibero GTS!

How to Recover Data Files Added After Hot Backup Following Incomplete Recovery

Document Type | Technical Information

Category | Administration

Applicable Product Version | Tibero 7.2.3

Document Number | TADTI146

 

Overview

If incomplete recovery is performed after a Hot Backup, and data files have been added after the backup point, the database cannot start normally during Resetlogs startup due to MISSING data files. This document describes the procedure to perform TSN-based incomplete recovery and complete the recovery including the newly added data files in such situations.

 

Recovery Scenario Summary

  1. Recreate the control file
  2. Extract archive log dump and perform incomplete recovery with tsn-1 value
  3. Resetlogs startup after incomplete recovery
  4. Add missing data files
  5. Re-perform incomplete recovery with tsn value after adding missing data files
  6. Start with resetlogs after recovery is complete

 

Method

1. Apply Controlfile

$ tbboot nomount
$ tbsql sys/tibero
SQL> CREATE CONTROLFILE REUSE DATABASE "testdb"
LOGFILE
-- Thread #0
GROUP 0 (
        '/root/tibero_engine/database/tbdata/system/redo001.redo',
        '/root/tibero_engine/database/tbdata/system/redo002.redo'
          ) SIZE 30M,
GROUP 1 (
        '/root/tibero_engine/database/tbdata/system/redo011.redo',
        '/root/tibero_engine/database/tbdata/system/redo012.redo'
          ) SIZE 30M,
GROUP 2 (
        '/root/tibero_engine/database/tbdata/system/redo021.redo',
        '/root/tibero_engine/database/tbdata/system/redo022.redo'
          ) SIZE 30M,
GROUP 3 (
        '/root/tibero_engine/database/tbdata/system/redo031.redo',
        '/root/tibero_engine/database/tbdata/system/redo032.redo'
          ) SIZE 30M,
GROUP 4 (
        '/root/tibero_engine/database/tbdata/system/redo041.redo',
        '/root/tibero_engine/database/tbdata/system/redo042.redo'
          ) SIZE 30M
RESETLOGS
DATAFILE
        '/root/tibero_engine/database/tbdata/system/system001.dtf',
        '/root/tibero_engine/database/tbdata/system/undo001.dtf',
        '/root/tibero_engine/database/tbdata/system/usr001.dtf',
        '/root/tibero_engine/database/tbdata/syssub001.dtf'
ARCHIVELOG
MAXLOGFILES 100
MAXFBLOGFILES 255
MAXLOGMEMBERS 8
MAXDATAFILES 256
MAXARCHIVELOG 500
MAXBACKUPSET 500
MAXLOGHISTORY 500
MAXFBMARKER 168
MAXFBARCHIVELOG 500
CHARACTER SET UTF8
NATIONAL CHARACTER SET UTF16
;

 

2. Perform TSN-based Incomplete Recovery

##### Extract TSN value using ARCHIVE
$ tblogdump -a 00001803 -n 0 log-t0-r0-s16.arc > log.txt
$ cat log.txt | grep next
next=0000.00010456 2025-12-18 17:55:23
โ€ป Applied TSN: 66645 (tsn -1)

##### Perform incomplete recovery

$ tbsql sys/tibero

SQL> alter database recover automatic database until change 66645;
 TBR-1147: Need log file (thread 0 seq 17) to recover from TSN 66645.
 Recovery done until 2025/12/18 17:55:13 TSN 66638.
 Recovery is incomplete and resetlogs is required.

 

3. DB Resetlogs Startup 

 - When starting with resetlogs, the following warning occurs and the database starts in MOUNT state.

$ tbboot resetlogs;
Change core dump dir to /root/tibero_engine/bin/prof.
Listener port = 8629

1) Find MISSING datafiles from V$DATAFILE.
2) Restore the corresponding backup datafiles.
3) If they were newly added after the start of the backup, create the physical files with each df id.
  : SQL> ALTER DATABASE CREATE DATAFILE df_id;
4) Rename(OS cmd) the corresponding files at DB_CREATE_FILE_DEST to their original names.
5) Rename(SQL) MISSING datafiles to each original names.
  : SQL> ALTER DATABASE RENAME FILE 'MISSING_file_path' to 'original_file_path';
6) Request the Media Recovery again as before.

********************************************************
* Critical Warning : Raise svmode failed. The reason is 
*   TBR-24018 :  CF-DD correction is required due to wrong CF/DD info or added/dropped datafiles while recovery process.
*   Current server mode is MOUNT.
*******************************************************
Tibero 7  

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started suspended at MOUNT mode.

 

4. Check and Create Missing Data Files (MISSING Data Files)

$ tbsql sys/tibero
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.

##### Check MISSING data files
SQL> select * from v$datafile;
TS#  NAME                                                                  STATUS
---  --------------------------------------------------------------------  ---------------
  0  /root/tibero_engine/database/tbdata/system/system001.dtf               ONLINE
  1  /root/tibero_engine/database/tbdata/system/undo001.dtf                 ONLINE
  3  /root/tibero_engine/database/tbdata/system/usr001.dtf                  ONLINE
  4  /root/tibero_engine/database/tbdata/syssub001.dtf                      ONLINE
  3  /root/tibero_engine/database/tbdata/MISSING000004                      MR NEEDED
  
  
##### Create data files with MR NEEDED status

SQL> alter database create datafile 4; 
Database altered.

SQL> select name from v$datafile;
NAME
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/root/tibero_engine/database/tbdata/system/system001.dtf
/root/tibero_engine/database/tbdata/system/undo001.dtf
/root/tibero_engine/database/tbdata/system/usr001.dtf
/root/tibero_engine/database/tbdata/syssub001.dtf
/root/tibero_engine/database/tbdata/MISSING000004
5 rows selected.



##### After creating data files with MR NEEDED status, rename to actual data file name
SQL> !cp -Rp /root/tibero_engine/database/tbdata/MISSING000004 /root/tibero_engine/database/tbdata/usr002.dtf
SQL> alter database rename file '/root/tibero_engine/database/tbdata/MISSING000004' to '/root/tibero_engine/database/tbdata/usr002.dtf';       
Database altered.

 

5. Re-perform Incomplete Recovery After Adding Missing Data Files

SQL> alter database recover automatic database until change 66645;
TBR-1147: Need log file (thread 0 seq 17) to recover from TSN 66645.
Recovery done until 2025/12/18 17:55:13 TSN 66638.
Recovery is incomplete and resetlogs is required.

SQL> exit
Disconnected.

 

6. DB Resetlogs Startup

$ tbdown immediate
Tibero instance terminated (IMMEDIATE mode).              

$ tbboot resetlogs;
Change core dump dir to /root/tibero_engine/bin/prof.
Listener port = 8629
Tibero 7  
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (NORMAL RESETLOGS mode).

$ tbsql sys/tibero

tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/root/tibero_engine/database/tbdata/system/temp001.dtf'SIZE 100M REUSE AUTOEXTEND ON NEXT 256K MAXSIZE 32G;

Tablespace 'TEMP' altered.

SQL> 
SQL> exit
Disconnected.
$ tbdown immediate

Tibero instance terminated (IMMEDIATE mode).              


$ tbboot
Change core dump dir to /root/tibero_engine/bin/prof.
Listener port = 8629

Tibero 7  

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (NORMAL mode).

 

Note

This incomplete recovery method was performed on Tibero 7. In the previous Tibero 6 version, if certain patches are not applied, additional procedures are required to add missing data files by applying specific parameters.

  • Related patches: 309656c, 282595, 320958
  • Parameter: DDLOAD_FOR_MOUNT_MODE=Y

 

 

 

Related to