Document Type ㅣ Technical Information
CategoryㅣBackup/Recovery
Applicable Product VersionㅣTibero 7
Document Number ㅣ TBATI036
Overview
This document explains the procedure to recover a tablespace that was accidentally dropped during operation by utilizing Tibero's tbrmgr backup to restore it to the point before the drop on a temporary recovery server.
Procedure
1. Copy Files to Temporary Recovery Server
To reduce recovery time after the tablespace drop, copy the backed-up tbrmgr online full backup files and archive log files to the temporary recovery server.
2. Perform Incomplete Database Recovery
Rebuild the control file to perform an incomplete recovery of the database.
3. Table tbexport and tbimport
3. Export the tables using the dropped tablespace with tbexport, then import them into the production server with tbimport to complete the recovery.
Recovery Scenario
1. Create Test Tablespace and Test Table
[tibero@tibero ~]$ tbsql sys/tibero tbSQL 7 TmaxTibero Corporation Copyright (c) 2020-. All rights reserved. Connected to Tibero. SQL> create tablespace drop_test_ts datafile 'drop_test_ts.dtf' size 2M AUTOEXTEND ON NEXT 8M MAXSIZE 1G; Tablespace 'DROP_TEST_TS' created. SQL> create table tibero.test_tbl 2 (c1 char(10) 3 , c2 number(10)) 4 tablespace drop_test_ts; Table 'TIBERO.TEST_TBL' created.
2. Load Data into Test Table
SQL> insert into tibero.test_tbl
2 select trim(to_char(rownum, '0000000000')), rownum
3 from dual
4 connect by level <= 10000;
10000 rows inserted.
SQL> /
10000 rows inserted.
… omitted …
SQL> /
10000 rows inserted.
SQL> /
10000 rows inserted.
SQL> commit;
Commit completed.
SQL> select count(*) from tibero.test_tbl;
COUNT(*)
----------
180000
1 row selected.
3. tbrmgr Backup
[tibero@tibero ~]$ tbrmgr backup -o /home/tibero/tbrmgr -v
==============================================================================
= Recovery Manager(RMGR) starts =
= =
= TmaxTibero Corporation Copyright (c) 2020-. All rights reserved. =
==============================================================================
archive log check succeeded
RMGR '-o' option used
: backing up to /home/tibero/tbrmgr
==============================================================================
RMGR - Backup (FULL)
==============================================================================
Initializing the backup progress, it may take few minutes...
BACKUP (set_id: 2, ts_id: 0, df_id: 0)
from /sdiske/ps1/kkw/tibero7/database/tibero7/system001.dtf
100.0% |===============================| 38400/38400 blks 0.00s
Synchronizing...
BACKUP (set_id: 2, ts_id: 1, df_id: 1)
from /sdiske/ps1/kkw/tibero7/database/tibero7/undo001.dtf
100.0% |===============================| 64000/64000 blks 1.00s
Synchronizing...
BACKUP (set_id: 2, ts_id: 3, df_id: 2)
from /sdiske/ps1/kkw/tibero7/database/tibero7/usr001.dtf
100.0% |===============================| 38400/38400 blks 0.00s
Synchronizing...
BACKUP (set_id: 2, ts_id: 4, df_id: 3)
from /sdiske/ps1/kkw/tibero7/database/tibero7/tpr_ts.dtf
100.0% |===============================| 25600/25600 blks 0.00s
Synchronizing...
BACKUP (set_id: 2, ts_id: 5, df_id: 4)
from /sdiske/ps1/kkw/tibero7/database/tibero7/drop_test_ts.dtf
100.0% |===============================| 256/256 blks 0.00s
Synchronizing...
Switching an online logfile...
Backing up the control file...
Control file backup succeeded
Database backup succeeded
RMGR backup ends
4. Load Additional Data into Test Table
SQL> insert into tibero.test_tbl
2 select trim(to_char(rownum, '0000000000')), rownum
3 from dual
4 connect by level <= 10000;
10000 rows inserted.
SQL> /
10000 rows inserted.
SQL> commit;
Commit completed.
SQL> select count(*) from tibero.test_tbl;
COUNT(*)
----------
200000
1 row selected.
5. User Mistakenly Drops Tablespace and Check Time
SQL> alter session set nls_date_format = 'YYYY/MM/DD HH24:MI:SS'; Session altered. SQL> select sysdate from dual; SYSDATE -------------------------------- 2025/12/23 14:50:01 1 row selected. SQL> drop tablespace DROP_TEST_TS including contents and datafiles; Tablespace 'DROP_TEST_TS' dropped.
6. Redo Log Switch (Perform log switch as temporary recovery server does not have redo information)
SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered.
7. Backup Control File from Production Server with resetlogs
SQL> alter database backup controlfile to trace as 'cf_reset.sql' resetlogs; Database altered.
8. tbrmgr Backup Before Recovery
[tibero@tibero ~]$ tbrmgr backup -o /home/tibero/tbrmgr --with-archivelog
==============================================================================
= Recovery Manager(RMGR) starts =
= =
= TmaxTibero Corporation Copyright (c) 2020-. All rights reserved. =
==============================================================================
archive log check succeeded
RMGR '-o' option used
: backing up to /home/tibero/tbrmgr
==============================================================================
RMGR - Backup (FULL)
==============================================================================
Initializing the backup progress, it may take few minutes...
BACKUP (set_id: 2, ts_id: 0, df_id: 0)
from /sdiske/ps1/kkw/tibero7/database/tibero7/system001.dtf
100.0% |===============================| 38400/38400 blks 0.00s
Synchronizing...
BACKUP (set_id: 2, ts_id: 1, df_id: 1)
from /sdiske/ps1/kkw/tibero7/database/tibero7/undo001.dtf
100.0% |===============================| 64000/64000 blks 1.00s
Synchronizing...
BACKUP (set_id: 2, ts_id: 3, df_id: 2)
from /sdiske/ps1/kkw/tibero7/database/tibero7/usr001.dtf
100.0% |===============================| 38400/38400 blks 0.00s
Synchronizing...
BACKUP (set_id: 2, ts_id: 4, df_id: 3)
from /sdiske/ps1/kkw/tibero7/database/tibero7/tpr_ts.dtf
100.0% |===============================| 25600/25600 blks 0.00s
Synchronizing...
Switching an online logfile...
Backing up the control file...
Control file backup succeeded
Database backup succeeded
RMGR backup ends
9. Modify SID.tip File Information on Temporary Recovery Server
Refer to the Install manual for installing Tibero on the temporary recovery server and proceed up to running gen_tip.sh after engine installation.
[tibero@tibero2 config]$ vi tibero.tip # tip file generated from /home/tibero/tibero7/config/tip.template (2025. 12. 22. (Mon) 16:47:54 KST) #------------------------------------------------------------------------------- # # RDBMS initialization parameter # #------------------------------------------------------------------------------- DB_NAME=tibero LISTENER_PORT=8629 CONTROL_FILES="/home/tibero/tbdata/c1.ctl" #CERTIFICATE_FILE="/home/tibero/tibero5/config/svr_wallet/tibero.crt" #PRIVKEY_FILE="/home/tibero/tibero5/config/svr_wallet/tibero.key" #WALLET_FILE="/home/tibero/tibero5/config/svr_wallet/WALLET" MAX_SESSION_COUNT=20 TOTAL_SHM_SIZE=2G MEMORY_TARGET=4G
10. Copy tbrmgr Online Full Backup Files and Archive Log Files to Temporary Recovery Server Path
-- Copy tbrmgr backup files and archive log files to the DB_CREATE_FILE_DEST and LOG_ARCHIVE_DEST paths specified in the SID.tip file. [tibero@tibero ~]$ cd ~/tbrmgr [tibero@tibero tbrmgr]$ scp bkp_* tibero@192.168.230.9:/home/tibero/tbdata The authenticity of host '192.168.230.9 (192.168.230.9)' can't be established. RSA key fingerprint is b3:ea:c1:61:3d:e2:0d:78:ee:61:df:dc:0c:62:99:93. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.230.9' (RSA) to the list of known hosts. tibero@192.168.230.9's password: bkp_20251224_2_0_0_0 100% 10MB 5.0MB/s 00:02 bkp_20251224_2_1_1_0 100% 10MB 10.0MB/s 00:01 bkp_20251224_2_3_2_0 100% 256MB 10.2MB/s 00:25 bkp_20251224_2_4_3_0 100% 164MB 3.8MB/s 00:43 bkp_20251224_2_5_4_0 100% 256MB 3.7MB/s 01:09 bkp_20251224_2_cf_0 100% 164MB 3.8MB/s 00:43 [tibero@tibero tbrmgr]$ cd ~/tbdata [tibero@tibero tbdata]$ scp cf_reset.sql tibero@192.168.230.9:/home/tibero/tbdata tibero@192.168.230.9's password: cf_reset.sql 100% 917 0.9KB/s 00:00 [tibero@tibero tbdata]$ cd ~/tbarch [tibero@tibero tbarch]$ scp * tibero@192.168.230.9:/home/tibero/tbarch tibero@192.168.230.9's password: log-t0-r0-s30.arc 100% 1021KB 1.0MB/s 00:00 log-t0-r0-s31.arc 100% 1020KB 1.0MB/s 00:00 log-t0-r0-s32.arc 100% 1022KB 1.0MB/s 00:00 log-t0-r0-s33.arc 100% 1022KB 1.0MB/s 00:00 log-t0-r0-s34.arc 100% 1023KB 1.0MB/s 00:00 log-t0-r0-s35.arc 100% 1019KB 1.0MB/s 00:01 log-t0-r0-s36.arc 100% 949KB 949.0KB/s 00:00 log-t0-r0-s37.arc 100% 11KB 10.5KB/s 00:00 log-t0-r0-s38.arc 100% 512 0.5KB/s 00:00
11. Modify Control File Contents
[tibero@tibero2 tbdata]$ vi cf_reset.sql
CREATE CONTROLFILE REUSE DATABASE "tibero"
LOGFILE
GROUP 0 '/home/tibero/tbdata/log01.log' SIZE 1M,
GROUP 1 '/home/tibero/tbdata/log11.log' SIZE 1M,
GROUP 2 '/home/tibero/tbdata/log21.log' SIZE 1M
RESETLOGS
DATAFILE
'/home/tibero/tbdata/system001.dtf.000',
'/home/tibero/tbdata/undo001.dtf.000',
'/home/tibero/tbdata/usr001.dtf.000',
'/home/tibero/tbdata/apm_ts.dtf.000',
'/home/tibero/tbdata/drop_test_ts.dtf.000' -- Added dropped tablespace file information
ARCHIVELOG
MAXLOGFILES 100
MAXLOGMEMBERS 8
MAXDATAFILES 256
MAXARCHIVELOG 500
MAXLOGHISTORY 500
CHARACTER SET MSWIN949
NATIONAL CHARACTER SET UTF16
;
--ALTER DATABASE MOUNT
---- Adding Logfiles is required in MOUNT mode.
---- Recovery is required in MOUNT mode.
--ALTER DATABASE RECOVER AUTOMATIC;
--ALTER DATABASE OPEN RESETLOGS;
---- Adding Tempfiles is required in OPEN mode.
-- ALTER TABLESPACE TEMP ADD TEMPFILE '/home/tibero/tbdata/temp001.dtf'
-- SIZE 100M REUSE AUTOEXTEND ON NEXT 8M MAXSIZE 1G;
12. Rebuild Control File in Nomount Mode on Temporary Recovery Server
[tibero@tibero2 ~]$ 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). [tibero@tibero2 ~]$ tbsql sys/tibero tbSQL 7 TmaxTibero Corporation Copyright (c) 2020-. All rights reserved. Connected to Tibero. SQL> @/home/tibero/tbdata/cf_reset.sql Control File created. File finished. SQL> exit Disconnected. [tibero@tibero2 tbdata]$ tbdown Tibero instance terminated (NORMAL mode). [tibero@tibero2 tbdata]$ export TB_NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS'
13. Proceed with Database Recovery on Temporary Recovery Server
[tibero@tibero2 tbdata]$ tbrmgr recover -o /sdiske/ps1/kkw/tbrmgr/ --with-archivelog
==============================================================================
= Recovery Manager(RMGR) starts =
= =
= TmaxTibero Corporation Copyright (c) 2020-. All rights reserved. =
==============================================================================
RMGR '-o' option used
: restoring from /sdiske/ps1/kkw/tbrmgr/
==============================================================================
RMGR - recovery (WITH ARCHIVELOG)
==============================================================================
Shutting down the instance...
Tibero instance terminated (ABNORMAL mode).
info file is deleted.
unlink failed.: No such file or directory
Control file #0 (/sdiske/ps1/kkw/tibero7/database/tibero7/c1.ctl) is accessible
All control files are accessible. No need to restore the backup control file.
Booting up the instance...
Change core dump dir to /sdiske/ps1/kkw/tibero7/bin/prof.
Listener port = 8808
Tibero 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (MOUNT mode).
Initializing the restore progress, it may take few minutes...
RMGR begins restoring backup files.
Full backup set_id: 4
Last incremental backup set_id: 4
RESTORE (set_id: 4, ts_id: 0, df_id: 0) - FULL BACKUP
100.0% |===============================| 38400/38400 blks 0.00s
Synchronizing...
RESTORE (set_id: 4, ts_id: 1, df_id: 1) - FULL BACKUP
100.0% |===============================| 64000/64000 blks 0.00s
Synchronizing...
RESTORE (set_id: 4, ts_id: 3, df_id: 2) - FULL BACKUP
100.0% |===============================| 38400/38400 blks 0.00s
Synchronizing...
RESTORE (set_id: 4, ts_id: 4, df_id: 3) - FULL BACKUP
100.0% |===============================| 25600/25600 blks 0.00s
Synchronizing...
RESTORE (set_id: 4, ts_id: 5, df_id: 4) - FULL BACKUP
100.0% |===============================| 256/256 blks 0.00s
Synchronizing...
Settling...
Restoring logfiles...
Archivelog log-t0-r0-s14.arc (thread: 0, set_id: 4, low_tsn: 114907, next_tsn: 114981) restored
Archive logfiles restoration succeeded
Database restoration succeeded
Given recovery point: COMPLETE RECOVERY
Recoverable point using only backup archivelogs: change 114980
recoverSQL: ALTER DATABASE RECOVER AUTOMATIC
Database automatic recovery succeeded
Shutting down the instance...
Tibero instance terminated (NORMAL mode).
Booting up the instance...
Change core dump dir to /sdiske/ps1/kkw/tibero7/bin/prof.
Listener port = 8808
Tibero 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (NORMAL mode).
RMGR recovery ends
14. Start Temporary Recovery Server and Verify Data
[tibero@tibero2 tbdata]$ tbsql sys/tibero
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> select count(*) from tibero.test_tbl;
COUNT(*)
----------
200000
1 row selected.
15. Export Recovered Data from Temporary Recovery Server with tbexport, Create Tablespace on Production Server, and Import with tbimport
[tibero@tibero2 tbdata]$ tbexport sid=tibero ip=localhost username=sys password=tibero table=tibero.test_tbl file=export_test_tbl.dat log=export_test_tbl.log
tbexport 7
Copyright 2020 TmaxTibero Co., Ltd. All Rights Reserved.
the TABLE: tibero.test_tbl: Wed Dec 23 15:46:44 KST 2025
Export character set: MSWIN949
Export national character set: UTF-16
exporting table: "TIBERO"."TEST_TBL"
[0] TIBERO.TEST_TBL 200000 rows exported.
Packing the file...
Export completed successfully: Wed Dec 23 15:46:44 KST 2025
[tibero@tibero2 tbdata] scp /home/tibero/tbdata/export_test_tbl.dat tibero@192.168.230.3:/home/tibero/tbdata/
tibero@192.168.230.3's password:
export_test_tbl.dat 100% 3140KB 3.1MB/s 00:01
-------------------------------------------------------------------------------
[tibero@tibero tbdata]$ tbsql sys/tibero
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> create tablespace drop_test_ts datafile 'drop_test_ts.dtf' size 2M AUTOEXTEND ON NEXT 8M MAXSIZE 1G;
Tablespace 'DROP_TEST_TS' created.
SQL> exit
Disconnected.
[tibero@tibero tbdata]$ tbimport sid=tibero ip=localhost username=sys password=tibero table=tibero.test_tbl file=export_test_tbl.dat log=import_test_tbl.log
tbimport 7
Copyright 2020 TmaxTibero Co., Ltd. All Rights Reserved.
Unpacking the file...
Import character set: MSWIN949
the TABLE: tibero.test_tbl: Wed Dec 23 15:50:54 KST 2016
Import national character set: UTF-16
importing schema: "TIBERO"
importing table "TEST_TBL"
[3] TIBERO.TEST_TBL 200000/200000 rows imported.
Import completed successfully: Wed Dec 23 15:51:03 KST 2016
[tibero@tibero tbdata]$ tbsql sys/tibero
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero.
SQL> select count(*) from tibero.test_tbl;
COUNT(*)
----------
200000
1 row selected.