Overview

This document describes how to complete recovery by abandoning the relevant tablespace when a specific data file has been deleted in an environment without a backup.

Therefore, you must be aware that data loss will occur.

To use this method, a binary that includes the function to correct the control file data dictionary is required.

(A binary that supports the alter database check controlfile from data dictionary statement is required.)

Test Environment

OS: Linux node1 5.14.0-706.el9.x86_64 #1 SMP PREEMPT_DYNAMIC Fri May 15 22:16:01 UTC 2026 x86_64 x86_64 x86_64 GNU/Linux
Tibero: Tibero 7 (DB 7.2.5) Build 313465
Configuration: Single

Method

Scenario

Create a test tablespace
Cause a failure (delete the test tablespace file)
Back up the control file with the noresetlogs option
Shut down Tibero and verify the failure
Comment out the files related to the affected tablespace in the backed-up control file
Re-create the control file
Proceed with recovery
Correct the control file data files
Take the test tablespace offline
Restart Tibero
Drop the test tablespace
Add a tempfile

Test Results

#Create a test tablespace

SQL> create tablespace ts_test datafile 'test01.dtf' size 1G;

Tablespace 'TS_TEST' created.
 

#Cause a failure (delete the test tablespace file)

node1@tibero:/home/tibero # cd tbdata
node1@tibero:/home/tibero/tbdata # ls -rlt
total 2042860
-rw-------. 1 tibero dba  104857600  Sep  4 16:05 temp001.dtf
drwx------. 3 tibero dba         17  Sep  4 16:06 java
-rw-------. 1 tibero dba  104857600  Sep  9 16:47 usr001.dtf
-rw-------. 1 tibero dba  104857600  Sep  9 16:47 redo011.redo
-rw-------. 1 tibero dba  104857600  Sep  9 16:47 redo021.redo
-rw-------. 1 tibero dba 1073741824  Sep  9 16:47 test01.dtf
-rw-------. 1 tibero dba  209715200  Sep  9 16:47 undo001.dtf
-rw-------. 1 tibero dba   94371840  Sep  9 16:47 syssub001.dtf
-rw-------. 1 tibero dba  104857600  Sep  9 16:47 system001.dtf
-rw-------. 1 tibero dba  104857600  Sep  9 16:47 redo001.redo
-rw-------. 1 tibero dba   84885504  Sep  9 16:47 c1.ctl
node1@tibero:/home/tibero/tbdata # rm test01.dtf
node1@tibero:/home/tibero/tbdata # ls -rlt
total 994284
-rw-------. 1 tibero dba 104857600 Sep  4 16:05 temp001.dtf
drwx------. 3 tibero dba        17 Sep  4 16:06 java
-rw-------. 1 tibero dba 104857600 Sep  9 16:47 usr001.dtf
-rw-------. 1 tibero dba 104857600 Sep  9 16:47 redo011.redo
-rw-------. 1 tibero dba 104857600 Sep  9 16:47 redo021.redo
-rw-------. 1 tibero dba 209715200 Sep  9 16:47 undo001.dtf
-rw-------. 1 tibero dba  94371840 Sep  9 16:47 syssub001.dtf
-rw-------. 1 tibero dba 104857600 Sep  9 16:47 system001.dtf
-rw-------. 1 tibero dba 104857600 Sep  9 16:47 redo001.redo
-rw-------. 1 tibero dba  84885504 Sep  9 16:47 c1.ctl
 

#Back up the control file with the noresetlogs option

node1@tibero:/home/tibero # tbsql sys/tibero

tbSQL 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.

Connected to Tibero.

SQL> alter database backup controlfile to trace as '/home/tibero/c1.sql';

Database altered.
 

#Shut down Tibero and verify the failure

node1@tibero:/home/tibero # tbdown
tbdown failed to receive reply.
node1@tibero:/home/tibero #
node1@tibero:/home/tibero #
node1@tibero:/home/tibero # ps -ef | grep tbsvr
tibero      1495    1199  0 16:49 pts/0    00:00:00 grep --color=auto tbsvr
node1@tibero:/home/tibero # tbboot
Change core dump dir to /home/tibero/tibero7/bin/prof.
Listener port = 8629
[2026-09-09T16:50:02.520] [FRM-104] [C-CX4]
********************************************************
* Critical Warning : Raise svmode failed. The reason is
*   TBR-1024 :  Database needs media recovery: open failed(/home/tibero/tbdata/test01.dtf).
*   Current server mode is MOUNT.
********************************************************


Tibero 7

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

#Comment out the files related to the affected tablespace in the backed-up control file

CREATE CONTROLFILE REUSE DATABASE "tibero"
LOGFILE
GROUP 1         '/home/tibero/tbdata/redo001.redo' SIZE 100M,
GROUP 2         '/home/tibero/tbdata/redo011.redo' SIZE 100M,
GROUP 3         '/home/tibero/tbdata/redo021.redo' SIZE 100M
NORESETLOGS
DATAFILE
       '/home/tibero/tbdata/system001.dtf',
       '/home/tibero/tbdata/undo001.dtf',
       '/home/tibero/tbdata/usr001.dtf',
       '/home/tibero/tbdata/syssub001.dtf' --,   -- The comma must also be deleted
       --'/home/tibero/tbdata/test01.dtf'       -- Comment out
NOARCHIVELOG
MAXINSTANCES 8
MAXLOGFILES 255
MAXFBLOGFILES 255
MAXLOGMEMBERS 8
MAXDATAFILES 4096
MAXARCHIVELOG 500
MAXBACKUPSET 500
MAXLOGHISTORY 500
MAXFBMARKER 168
MAXFBARCHIVELOG 500
CHARACTER SET UTF8
NATIONAL CHARACTER SET UTF16
;
 

#Re-create the control file

node1@tibero:/home/tibero # tbdown

Tibero instance terminated (NORMAL mode).

node1@tibero:/home/tibero # tbboot nomount
Change core dump dir to /home/tibero/tibero7/bin/prof.
Listener port = 8629

Tibero 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (NOMOUNT mode).
node1@tibero:/home/tibero # tbsql sys/tibero

tbSQL 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.

Connected to Tibero.

SQL> @c1

Control File created.

SQL> quit
Disconnected.
node1@tibero:/home/tibero # tbdown

Tibero instance terminated (NORMAL mode).



#Proceed with recovery

node1@tibero:/home/tibero # tbboot mount
Change core dump dir to /home/tibero/tibero7/bin/prof.
Listener port = 8629

Tibero 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (MOUNT mode).
node1@tibero:/home/tibero # tbsql sys/tibero

tbSQL 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.

Connected to Tibero.

SQL> alter database recover automatic;

Database altered.
 

#Correct the control file data files

SQL> alter database check controlfile from data dictionary;
[2026-09-09T16:54:03.554] [CLC-163] [I-OT2]
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.

Database altered.



#Take the test tablespace offline

SQL> alter system set _ENABLE_UNDO_TS_OFFLINE=y;     --This statement is required

System altered.

SQL> alter tablespace ts_test offline immediate;

Tablespace 'TS_TEST' altered.



#Restart Tibero

node1@tibero:/home/tibero # tbdown

Tibero instance terminated (NORMAL mode).

node1@tibero:/home/tibero # tbboot
Change core dump dir to /home/tibero/tibero7/bin/prof.
Listener port = 8629

Tibero 7

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



#Drop the test tablespace

SQL> drop tablespace ts_test;

Tablespace 'TS_TEST' dropped.
 

#Add a tempfile

SQL> alter tablespace temp add tempfile 'temp001.dtf' size 5g;

Tablespace 'TEMP' altered.

SQL> select * from v$tempfile;

    FILE# CREATE_TSN CREATE_DATE                                                                                                                             TS#     RFILE# ENABLED     BLOCKS CREATE_SIZE
---------- ---------- -------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- ------- ---------- -----------
NAME
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        0          0                                                                                                                                           2          0 YES         655360      655360
/home/tibero/tbdata/temp001.dtf


1 row selected.

Errors That May Occur During the Procedure

Unable to take the target tablespace offline

#Error message

SQL> alter tablespace ts_test offline immediate;
TBR-24052: Only allowed if media recover is enabled.
 

#Enable the _ENABLE_UNDO_TS_OFFLINE parameter

SQL> alter system set _ENABLE_UNDO_TS_OFFLINE=y;

System altered.

SQL> alter tablespace ts_test offline immediate;

Tablespace 'TS_TEST' altered.

Unable to add a tempfile

#Error message

SQL> alter tablespace temp add tempfile 'temp001.dtf';
TBR-1001: Unable to create file /home/tibero/tbdata/temp001.dtf.
 

#Remove the existing temp file and try adding it again

node1@tibero:/home/tibero/tbdata # rm temp001.dtf

node1@tibero:/home/tibero/tbdata # tbsql sys/tibero

tbSQL 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.

Connected to Tibero.

SQL> alter tablespace temp add tempfile 'temp001.dtf' size 5g;

Tablespace 'TEMP' altered.

Related to

Search

Welcome to Tibero GTS!

Complete Recovery by Abandoning a Specific TS in an Environment Without a Backup

Overview

Test Environment

Method

Scenario

Test Results

Errors That May Occur During the Procedure

업무 외 시간 안내

Search

Welcome to Tibero GTS!

Overview

Test Environment

Method

Scenario

Test Results

Errors That May Occur During the Procedure