주 콘텐츠로 건너뛰기
📓 Tibero Online Manual Click here 📓

검색

Welcome to Tibero GTS!

일부 Tablespace에 대해 Clone DB로 복구하기

문서유형ㅣ기술정보

분야ㅣ백업/복구

적용제품버전ㅣ6FS07PS, 7FS02PS, 7FS03PS, 7FS04PS

문서번호ㅣTBATI041

 

개요

일부 Tablespace에 대한 backup을 이용하여 Clone DB 생성하는 복구 시나리오입니다.


참고

해당 기능은 320958 패치가 적용된 버전에서 지원합니다.

 

 

방법

1. 테스트 데이터 입력

1.1. Tablespace 생성

테스트용 Tablespace를 생성합니다.

SQL> drop tablespace ts1 including contents and datafiles cascade constraint;
TBR-7073: Specified tablespace 'TS1' was not found.

SQL> drop tablespace ts2 including contents and datafiles cascade constraint;
TBR-7073: Specified tablespace 'TS2' was not found.

SQL> drop tablespace ts3 including contents and datafiles cascade constraint;
TBR-7073: Specified tablespace 'TS3' was not found.

SQL> drop tablespace ts4 including contents and datafiles cascade constraint;
TBR-7073: Specified tablespace 'TS4' was not found.

SQL> drop tablespace ts5 including contents and datafiles cascade constraint;
TBR-7073: Specified tablespace 'TS5' was not found.

SQL> drop tablespace ts_for_tbl including contents and datafiles cascade constraint;
TBR-7073: Specified tablespace 'TS_FOR_TBL' was not found.

SQL> drop tablespace ts_for_idx including contents and datafiles cascade constraint;
TBR-7073: Specified tablespace 'TS_FOR_IDX' was not found.

SQL> drop tablespace ts_has_dfs including contents and datafiles cascade constraint;
TBR-7073: Specified tablespace 'TS_HAS_DFS' was not found.

SQL> create tablespace ts1 datafile 'ts1.dtf' size 10M autoextend off;

Tablespace 'TS1' created.

SQL> create tablespace ts2 datafile 'ts2.dtf' size 20M autoextend off;

Tablespace 'TS2' created.

SQL> create tablespace ts3 datafile 'ts3.dtf' size 30M autoextend off;

Tablespace 'TS3' created.

SQL> create tablespace ts4 datafile 'ts4.dtf' size 40M autoextend off;

Tablespace 'TS4' created.

SQL> create tablespace ts5 datafile 'ts5.dtf' size 50M autoextend off;

Tablespace 'TS5' created.

SQL> create tablespace ts_has_dfs datafile 'ts_has_dfs_1.dtf' size 10M, 'ts_has_dfs_2.dtf' size 10M;

Tablespace 'TS_HAS_DFS' created.

SQL> create tablespace ts_for_tbl datafile 'ts_for_tbl.dtf' size 10M autoextend off;

Tablespace 'TS_FOR_TBL' created.

SQL> create tablespace ts_for_idx datafile 'ts_for_idx.dtf' size 10M autoextend off;

Tablespace 'TS_FOR_IDX' created.

 

1.2. Table 생성

테스트용 Table을 생성합니다.

SQL> create table t1 (a number) tablespace ts1;

Table 'T1' created.

SQL> create table t2 (a number) tablespace ts2;

Table 'T2' created.

SQL> create table t3 (a number) tablespace ts3;

Table 'T3' created.

SQL> create table t4 (a number) tablespace ts4;

Table 'T4' created.

SQL> create table t5 (a number) tablespace ts5;

Table 'T5' created.

SQL> create table t6 (a number) tablespace ts_has_dfs;

Table 'T6' created.

SQL> create table t7 (a number) tablespace ts_for_tbl;

Table 'T7' created.

SQL> create index t7_idx on t7(a) tablespace ts_for_idx;

Index 'T7_IDX' created.

 

1.3. Data 입력

테스트용 Table에 Data를 입력합니다.

SQL> begin
     insert into t1 select level from dual connect by level <= 1000;
     commit;
end;
  /

PSM completed.

SQL> begin
     for i in 1..2  loop
        insert into t2 select level from dual connect by level <= 1000;
        commit;
    end loop;
end;
/   

PSM completed.

SQL> begin
        for i in 1..3  loop
        insert into t3 select level from dual connect by level <= 1000;
        commit;
        end loop;
end;                   
/  

PSM completed.

SQL> begin
         for i in 1..4  loop
        insert into t4 select level from dual connect by level <= 1000;
        commit;
    end loop;
end;
/  

PSM completed.

SQL> begin
     for i in 1..5  loop
        insert into t5 select level from dual connect by level <= 1000;
         commit;
    end loop;
end;
/  

PSM completed.

SQL> begin
     for i in 1..6  loop
        insert into t6 select level from dual connect by level <= 1000;
        commit;
    end loop;
end;
/     

PSM completed.

SQL> begin
     for i in 1..10  loop
        insert into t7 select level from dual connect by level <= 1000;
        commit;
    end loop;
end;
/    

PSM completed.

 

1.4. Data 확인

테스트용 Table에 Data를 확인합니다.

SQL> select count(*) from t1;

  COUNT(*)
----------
      1000

1 row selected.

SQL>
SQL> select count(*) from t2;

  COUNT(*)
----------
      2000

1 row selected.

SQL> select count(*) from t3;

  COUNT(*)
----------
      3000

1 row selected.

SQL> select count(*) from t4;

  COUNT(*)
----------
      4000

1 row selected.

SQL> select count(*) from t5;

  COUNT(*)
----------
      5000

1 row selected.

SQL> select count(*) from t6;

  COUNT(*)
----------
      6000

1 row selected.

SQL> select count(*) from t7;

  COUNT(*)
----------
     10000

1 row selected.

 

1.5. Tablespace 및 Data file 확인

DB내 Tablesapce와 Data file 리스트를 확인합니다.

SQL> select name from v$tablespace;

NAME
--------------------------------------------------------------------------------------------------------------------------------
SYSTEM
UNDO
TEMP
USR
SYSSUB
TS1
TS2
TS3
TS4
TS5
TS_HAS_DFS
TS_FOR_TBL
TS_FOR_IDX

13 rows selected.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------
/home/tibero/tibero7/database/tibero0/system001.dtf
/home/tibero/tibero7/database/tibero0/undo001.dtf
/home/tibero/tibero7/database/tibero0/usr001.dtf
/home/tibero/tibero7/database/tibero0/syssub001.dtf
/home/tibero/tibero7/database/tibero0/ts1.dtf
/home/tibero/tibero7/database/tibero0/ts2.dtf
/home/tibero/tibero7/database/tibero0/ts3.dtf
/home/tibero/tibero7/database/tibero0/ts4.dtf
/home/tibero/tibero7/database/tibero0/ts5.dtf
/home/tibero/tibero7/database/tibero0/ts_has_dfs_1.dtf
/home/tibero/tibero7/database/tibero0/ts_has_dfs_2.dtf
/home/tibero/tibero7/database/tibero0/ts_for_tbl.dtf
/home/tibero/tibero7/database/tibero0/ts_for_idx.dtf

13 rows selected.

 

2. 백업 

2.1. begin backup 수행

online hot backup을 위해 begin backup을 수행합니다.

SQL> alter database begin backup;

Database altered.

 

2.2. Data file 복사

online 중에 Data file을 백업합니다.

$ rm -rf $TB_HOME/database/t7_bak
$ mkdir $TB_HOME/database/t7_bak
$ cp -rf $TB_HOME/database/$TB_SID/* $TB_HOME/database/t7_bak/

 

2.3. end backup 수행

Data file 백업이 완료되면 end backup을 수행합니다.

SQL> alter database end backup;

Database altered.

 

2.4. Log switch 수행 및 archivelog 백업

end backup 명령이 수행된 archivelog 확보를 위해 log switch를 수행합니다.

이후 archivelog를 백업합니다.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

$ cp -rf $TB_HOME/database/$TB_SID/archive/* $TB_HOME/database/t7_bak/archive

 

2.5. controlfile 백업

resetlogs 옵션으로 백업합니다.

SQL> alter database backup controlfile to trace as '/home/tibero/tibero7/database/t7_bak/ctrl_bak.sql' resetlogs;

Database altered.

 

2.6. DB 종료

운영중이던 DB를 종료합니다.

[tibero@kimmi ~]$ tbdown immediate

Tibero instance terminated (IMMEDIATE mode).

참고

한 서버&동일 유저에서 Clone DB 구축을 위해 필요한 과정이며, 다른 서버나 다른 유저에서 Clone DB 구축 시에는 생략 가능하다.

 

3. Clone DB 생성을 위한 일부 Tablespace restore

3.1. Data file 교체

기존 운영에 쓰인 Data file은 백업해두고, 백업 받아놓은 Data file의 경로를 변경합니다.

$ mv $TB_HOME/database/$TB_SID $TB_HOME/database/org_bak_20251210

$ mv $TB_HOME/database/t7_bak $TB_HOME/database/$TB_SID

 

3.2. 복구에서 제외할 Tablespace에 대한 Data file 삭제

백업본에서 복구에서 제외할 Tablespace에 대한 Data file을 삭제합니다.

$ rm $TB_HOME/database/$TB_SID/ts4.dtf
$ rm $TB_HOME/database/$TB_SID/ts5.dtf

 

3.3. controlfile 수정

복구에서 제외할 Data file은 주석 처리합니다.

resetlogs 옵션으로 받지 않은 경우, resetlogs로 수기 변경합니다.

$ vi $TB_HOME/database/$TB_SID/ctrl_bak.sql

CREATE CONTROLFILE REUSE DATABASE "tibero0"
LOGFILE
-- Thread #0
GROUP 0 (
        '/home/tibero/tibero7/database/tibero0/redo001.redo',
        '/home/tibero/tibero7/database/tibero0/redo002.redo'
          ) SIZE 10M,
GROUP 1 (
        '/home/tibero/tibero7/database/tibero0/redo011.redo',
        '/home/tibero/tibero7/database/tibero0/redo012.redo'
          ) SIZE 10M,
GROUP 2 (
        '/home/tibero/tibero7/database/tibero0/redo021.redo',
        '/home/tibero/tibero7/database/tibero0/redo022.redo'
          ) SIZE 10M
RESETLOGS
DATAFILE
        '/home/tibero/tibero7/database/tibero0/system001.dtf',
        '/home/tibero/tibero7/database/tibero0/undo001.dtf',
        '/home/tibero/tibero7/database/tibero0/usr001.dtf',
        '/home/tibero/tibero7/database/tibero0/syssub001.dtf',
        '/home/tibero/tibero7/database/tibero0/ts1.dtf',
        '/home/tibero/tibero7/database/tibero0/ts2.dtf',
        '/home/tibero/tibero7/database/tibero0/ts3.dtf',
        --'/home/tibero/tibero7/database/tibero0/ts4.dtf',
        --'/home/tibero/tibero7/database/tibero0/ts5.dtf',
        '/home/tibero/tibero7/database/tibero0/ts_has_dfs_1.dtf',
        '/home/tibero/tibero7/database/tibero0/ts_has_dfs_2.dtf',
        '/home/tibero/tibero7/database/tibero0/ts_for_tbl.dtf',
        '/home/tibero/tibero7/database/tibero0/ts_for_idx.dtf'
ARCHIVELOG
MAXLOGFILES 100
MAXFBLOGFILES 255
MAXLOGMEMBERS 8
MAXDATAFILES 8192
MAXARCHIVELOG 500
MAXBACKUPSET 500
MAXLOGHISTORY 500
MAXFBMARKER 168
MAXFBARCHIVELOG 500
CHARACTER SET MSWIN949
NATIONAL CHARACTER SET UTF16
;

 

3.4. nomount 기동 및 controlfile 재생성

controlfile 재생성을 위해 nomount로 기동합니다.

"3.3. controlfile 수정"에서 수정한 내용으로 controlfile을 재생성합니다.

$ tbboot -t nomount
Listener port = 8629

Tibero 7

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

[tibero@kimmi tibero0]$ tbsql sys/tibero

tbSQL 7

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

Connected to Tibero.

SQL> @$TB_HOME/database/$TB_SID/ctrl_bak.sql

Control File created.

 

3.5. mount 기동 및 Media Recovery 수행

media recovery를 위해 mount로 기동합니다.

[tibero@kimmi tibero0]$ tbdown immediate

Tibero instance terminated (IMMEDIATE mode).

[tibero@kimmi tibero0]$ tbboot mount

Tibero 7

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

[tibero@kimmi tibero0]$ tbsql sys/tibero

tbSQL 7

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

Connected to Tibero.

SQL> alter database recover automatic;

TBR-1147: Need log file (thread 0 seq 13) to recover from TSN 45357.
Recovery done until 2025/12/15 11:00:49 TSN 45351.
Recovery is incomplete and resetlogs is required.

 

3.6. resetlogs로 기동

DB를 resetlogs로 기동합니다.

resetlogs 기동 시, 복구 시 제외한 Data file로 인해 TBR-24018 에러가 발생하며 mount 모드로 기동됩니다.

$ tbdown immediate; tbboot resetlogs;

Tibero instance terminated (IMMEDIATE mode).

Change core dump dir to /home/tibero/tibero7/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.

 

3.7. Tablespace offline immediate수행

복구 시 제외한 Data file을 조회하여 offline immediate를 수행합니다.

SQL> select * from v$datafile df ,v$tablespace ts where df.TS# = ts.TS# and df.name like '%MISSING%';

     FILE# CREATE_TSN CREATE_DATE
---------- ---------- --------------------------------------------------------------------------------------------------------------------------------
       TS#     RFILE# BIGFILE STATUS            ENABLED      CKPT_TSN
---------- ---------- ------- ----------------- ---------- ----------
CKPT_DATE                                                                                                                        CREATE_BYTES
-------------------------------------------------------------------------------------------------------------------------------- ------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------
       TS# NAME                                                                                                                             TYPE BIGFILE
---------- -------------------------------------------------------------------------------------------------------------------------------- ---- -------
FLASHBACK_ON
------------
         7     532374 2025/12/10
         9          7 NO      MR NEEDED         READ WRITE     532374
2025/12/10                                                                                                                           41943040
/home/tibero/tibero7/database/tibero0/MISSING000007
         9 TS4                                                                                                                              DATA NO
NO

         8     532379 2025/12/10
        10          8 NO      MR NEEDED         READ WRITE     532379
2025/12/10                                                                                                                           52428800
/home/tibero/tibero7/database/tibero0/MISSING000008
        10 TS5                                                                                                                              DATA NO
NO


2 rows selected.

SQL> alter tablespace ts4 offline immediate;

Tablespace 'TS4' altered.


SQL> alter tablespace ts5 offline immediate;

Tablespace 'TS5' altered.

 

3.8. Media Recovery 수행

다시 한번 Media Recovery를 수행합니다.

SQL> alter database recover automatic;

TBR-1027: No log files found for thread 0 from recovery start tsn 45357.

 

3.9. resetlogs 기동

다시 한번 resetlogs로 기동합니다.

[tibero@kimmi tibero0]$ tbdown immediate; tbboot resetlogs

Tibero instance terminated (IMMEDIATE mode).

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 RESETLOGS mode).

 

3.10. 제외한 Tablespace에 대해 Drop DDL 수행

복구 대상에서 제외한 Tablespace에 대해 Drop tablespace 명령어를 수행합니다.

drop tablespace ts4 including contents and datafiles;

drop tablespace ts5 including contents and datafiles;

 

 

 

관련 대상