문서유형ㅣ기술정보
분야ㅣ백업/복구
적용제품버전ㅣTibero 7
문서번호ㅣTBATI036
개요
Tibero의 tbrmgr 백업을 활용하여, 운영 중 실수로 Drop된 Tablespace를 임시 복구 서버에서 Drop 이전 시점으로 복구하는 절차를 설명합니다.
방법
1. 임시 복구 서버로 파일 복사
Tablespace Drop 후 복구시간 단축을 위해 백업받은 tbrmgr online full backup 파일 및 Archive Log 파일을 임시 복구 서버에 복사합니다.
2. DB 불완전복구 수행
컨트롤 파일을 재구성하여 DB를 불완전 복구합니다.
3. Table tbexport 및 tbimport
3. Drop된 Tablespace를 사용하는 Table을 tbexport 후 운영서버에 tbimport하여 복구를 완료합니다.
복구 시나리오
1. Test Tablespace 및 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. Test Table에 Data 적재
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> /
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. Test Table에 추가 Data 적재
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. 사용자 실수에 의한 Tablespace Drop 및 시간확인
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 (임시 복구서버에는 Redo 정보가 없으므로 Log Switch 수행)
SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered.
7. 운영서버의 Control 파일을 resetlogs로 Backup
SQL> alter database backup controlfile to trace as 'cf_reset.sql' resetlogs; Database altered.
8. 복구 전 tbrmgr Backup
[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. 임시 복구 서버의 SID.tip파일 정보 변경
임시 복구서버 Tibero 설치는 Install 메뉴얼을 참고하고 엔진 설치 후 gen_tip.sh까지만 진행합니다.
[tibero@tibero2 config]$ vi tibero.tip # tip file generated from /home/tibero/tibero7/config/tip.template (2025. 12. 22. (월) 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. 임시 복구서버 Path로 tbrmgr online full backup 파일 및 Archive log 파일 복사
-- SID.tip 파일에서의 DB_CREATE_FILE_DEST, LOG_ARCHIVE_DEST 경로에 tbrmgr 백업파일 및 Archive Log 파일을 복사한다. [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. Control 파일 내용 수정
[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' -- Drop된 Tablespace 파일정보 추가
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. 임시 복구서버 Nomount mode에서 Control 파일 재구성
[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. 임시 복구 서버의 DB 복구 진행
[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. 임시 복구서버 기동 및 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. 임시 복구서버의 복구된 Data tbexport 및 운영서버 Tablespace Create 후 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.