주 콘텐츠로 건너뛰기

검색

Welcome to Tibero GTS!

nologging + dpi 작업 후 유의사항 (백업/복구)

문서유형ㅣ기술정보

분야ㅣ백업/복구

적용제품버전ㅣTibero 7.2.3

문서번호ㅣTBATI044


개요

nologging 테이블에 dpi로 데이터 적재 후 백업/복구시 유의사항에 대해 기술하였습니다.
 
 

테스트 구성 환경

  • 서버명 : Tibero
  • OS version : CentOS Stream release 10 (Coughlan)
  • DB Version : Tibero 7.2.3

 

 

방법

시나리오

1.아카이브 모드 변경

[tibero@node1 ~]$ tbdown

Tibero instance terminated (NORMAL mode).              

[tibero@node1 ~]$ tbboot mount
Change core dump dir to /home/tibero/tibero7/bin/prof.
/usr/bin/pstack or /usr/bin/gstack is required to dump the callstack.
****************************************************
* The following path was not found.
* /usr/bin/pstack or /usr/bin/gstack
* pstack or gstack is required to dump the callstack.
****************************************************
Listener port = 8629

Tibero 7  

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (MOUNT mode).
[tibero@node1 ~]$ tbsql sys/tibero

tbSQL 7

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

Connected to Tibero.

SQL> alter database archivelog;

Database altered.

SQL> q
Disconnected.
[tibero@node1 ~]$ tbdown

Tibero instance terminated (NORMAL mode).              

[tibero@node1 ~]$ tbboot
Change core dump dir to /home/tibero/tibero7/bin/prof.
/usr/bin/pstack or /usr/bin/gstack is required to dump the callstack.
****************************************************
* The following path was not found.
* /usr/bin/pstack or /usr/bin/gstack
* pstack or gstack is required to dump the callstack.
****************************************************
Listener port = 8629

Tibero 7  

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

 

2.테스트 유저 생성

[tibero@node1 ~]$ tbsql sys/tibero

tbSQL 7

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

Connected to Tibero.

SQL> drop user test cascade;
TBR-7069: Specified user 'TEST' was not found.

SQL> create user test identified by test;

User 'TEST' created.

SQL> grant dba to test;

Granted.

 

3.테스트 테이블 생성

SQL> drop table test.tbl_nologgin_dpi;
TBR-7071: Schema object 'TEST.TBL_NOLOGGIN_DPI' was not found or is invalid.

SQL> create table test.tbl_nologgin_dpi (a number, b number, c date, d varchar(30));

Table 'TEST.TBL_NOLOGGIN_DPI' created.

 

4.테이블 nologging 변경 및 logging 모드 확

SQL> alter table test.tbl_nologgin_dpi nologging;

Table 'TEST.TBL_NOLOGGIN_DPI' altered.

SQL> select logging from dba_tables where owner='TEST' and table_name='TBL_NOLOGGIN_DPI';

LOGGING
-------
NO

1 row selected.

 

5.데이터베이스 핫백업 시작

SQL> alter database begin backup;

Database altered.

 

6.데이터파일 복사 시작 (타세션)

[tibero@node1 ~/tbdata]$ cd /home/tibero/tbdata
[tibero@node1 ~/tbdata]$ cp *.dtf /home/tibero/backup/

 

7.dpi 모드로 데이터 적재 시작 (타세션)

SQL> insert /*+ append */ into test.tbl_nologgin_dpi select level, level, sysdate, 'tbl_nologgin_dpi' from dual connect by level <= 5000000;

5000000 rows inserted.

SQL> commit;

Commit completed.

SQL> insert /*+ append */ into test.tbl_nologgin_dpi select level, level, sysdate, 'tbl_nologgin_dpi' from dual connect by level <= 5000000;

5000000 rows inserted.

SQL> commit;

Commit completed.

 

8.강제 아카이브 스위치 (타세션)

[tibero@node1 ~]$ tbsql sys/tibero

tbSQL 7

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

Connected to Tibero.

SQL> set interval 3
SQL> alter system switch logfile;

System altered.

SQL> loop /

System altered.


System altered.


System altered.


System altered.


System altered.


System altered.


System altered.


System altered.


System altered.


System altered.


System altered.

 

10.데이터베이스 핫백업 종료

SQL> alter database end backup;

Database altered.

SQL> select * from v$backup;

     FILE# STATUS        CHANGE# TIME
---------- ---------- ---------- --------------------------------------------------------------------------------------------------------------------------------
         0 NOT ACTIVE      51216 2025/12/15
         1 NOT ACTIVE      51218 2025/12/15
         2 NOT ACTIVE      51220 2025/12/15
         3 NOT ACTIVE      51222 2025/12/15

4 rows selected.

 

11.적재된 데이터 건수 확인

SQL> select count(*) from test.tbl_nologgin_dpi;

  COUNT(*)
----------
  10000000

1 row selected.

 

12.데이터베이스 종료 및 장애 유발 (데이터 파일 삭제)

[tibero@node1 ~]$ tbdown

Tibero instance terminated (NORMAL mode).              

[tibero@node1 ~]$ cd /home/tibero/tbdata
[tibero@node1 ~/tbdata]$ rm *.dtf
[tibero@node1 ~/tbdata]$ ls -rlt
합계 390096
drwx------. 3 tibero dba        17 12월 15일  13:57 java
-rw-------. 1 tibero dba 104857600 12월 15일  15:03 redo021.redo
-rw-------. 1 tibero dba 104857600 12월 15일  15:03 redo001.redo
-rw-------. 1 tibero dba 104857600 12월 15일  15:05 redo011.redo
-rw-------. 1 tibero dba  84885504 12월 15일  15:05 c1.ctl

 

13.데이터베이스 Mount 기동 확인 및 백업본 복사

[tibero@node1 ~/tbdata]$ tbboot
Change core dump dir to /home/tibero/tibero7/bin/prof.
/usr/bin/pstack or /usr/bin/gstack is required to dump the callstack.
****************************************************
* The following path was not found.
* /usr/bin/pstack or /usr/bin/gstack
* pstack or gstack is required to dump the callstack.
****************************************************
Listener port = 8629

********************************************************
* Critical Warning : Raise svmode failed. The reason is 
*   TBR-1024 :  Database needs media recovery: open failed(/home/tibero/tbdata/system001.dtf).
*   Current server mode is MOUNT.
********************************************************


Tibero 7  

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


[tibero@node1 ~/tbdata]$ tbdown

Tibero instance terminated (NORMAL mode).  

[tibero@node1 ~/tbdata]$ cd /home/tibero/backup/
[tibero@node1 ~/backup]$ cp * /home/tibero/tbdata/
[tibero@node1 ~/backup]$ cd /home/tibero/tbdata
[tibero@node1 ~/tbdata]$ ls -rlt
합계 1969152
drwx------. 3 tibero dba         17 12월 15일  13:57 java
-rw-------. 1 tibero dba  104857600 12월 15일  15:03 redo021.redo
-rw-------. 1 tibero dba  104857600 12월 15일  15:03 redo001.redo
-rw-------. 1 tibero dba  104857600 12월 15일  15:05 redo011.redo
-rw-------. 1 tibero dba   84885504 12월 15일  15:05 c1.ctl
-rw-------. 1 tibero dba  104857600 12월 15일  15:07 system001.dtf
-rw-------. 1 tibero dba   60817408 12월 15일  15:07 syssub001.dtf
-rw-------. 1 tibero dba 1480589312 12월 15일  15:07 usr001.dtf
-rw-------. 1 tibero dba  209715200 12월 15일  15:07 undo001.dtf
-rw-------. 1 tibero dba  104857600 12월 15일  15:07 temp001.dtf

 

14.데이터베이스 완전 복구 수행

[tibero@node1 ~/tbdata]$ tbboot mount
Change core dump dir to /home/tibero/tibero7/bin/prof.
/usr/bin/pstack or /usr/bin/gstack is required to dump the callstack.
****************************************************
* The following path was not found.
* /usr/bin/pstack or /usr/bin/gstack
* pstack or gstack is required to dump the callstack.
****************************************************
Listener port = 8629

Tibero 7  

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (MOUNT mode).
[tibero@node1 ~/tbdata]$ tbsql sys/tibero

tbSQL 7

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

Connected to Tibero.

SQL> alter database recover automatic;

Database altered.

 

15.복구진행시 slog 확인

[2025-12-15T15:08:33.770344] [DDL-86] [I] Executing DDL: alter database recover automatic
[2025-12-15T15:08:33.772814] [RVP-11] [I] RV master received MEDIA_RECOVERY request
[2025-12-15T15:08:33.772890] [CLC-38] [I] RV slave 1 received MEDIA_RECOVERY job 
[2025-12-15T15:08:33.785609] [RV-38] [I] DF #0 (ts 0) resetlogs tsn=0, CF resetlogs tsn=0
[2025-12-15T15:08:33.785674] [RV-38] [I] DF #0(0) CKPT 2025-12-15 14:53:58,0000.0000c759(176,177) (backup_begin=0000.0000c810) CF[CKPT 2025-12-15 15:05:39,0000.0000cc3e(185), STOP 2025-12-15 15:05:39,0000.0000cc3e] FUZZY(open=1,backup=1,mr=0,read-only=0,offline=0) rf(0000.00000000) 3
[2025-12-15T15:08:33.791333] [RV-38] [I] DF #1 (ts 1) resetlogs tsn=0, CF resetlogs tsn=0
[2025-12-15T15:08:33.791367] [RV-38] [I] DF #1(1) CKPT 2025-12-15 14:53:58,0000.0000c759(176,177) (backup_begin=0000.0000c812) CF[CKPT 2025-12-15 15:05:39,0000.0000cc3e(185), STOP 2025-12-15 15:05:39,0000.0000cc3e] FUZZY(open=1,backup=1,mr=0,read-only=0,offline=0) rf(0000.00000000) 3
[2025-12-15T15:08:33.796355] [RV-38] [I] DF #2 (ts 3) resetlogs tsn=0, CF resetlogs tsn=0
[2025-12-15T15:08:33.796383] [RV-38] [I] DF #2(3) CKPT 2025-12-15 14:53:58,0000.0000c759(176,177) (backup_begin=0000.0000c814) CF[CKPT 2025-12-15 15:05:39,0000.0000cc3e(185), STOP 2025-12-15 15:05:39,0000.0000cc3e] FUZZY(open=1,backup=1,mr=0,read-only=0,offline=0) rf(0000.00000000) 3
[2025-12-15T15:08:33.798251] [RV-38] [I] DF #3 (ts 4) resetlogs tsn=0, CF resetlogs tsn=0
[2025-12-15T15:08:33.798264] [RV-38] [I] DF #3(4) CKPT 2025-12-15 14:53:58,0000.0000c759(176,177) (backup_begin=0000.0000c816) CF[CKPT 2025-12-15 15:05:39,0000.0000cc3e(185), STOP 2025-12-15 15:05:39,0000.0000cc3e] FUZZY(open=1,backup=1,mr=0,read-only=0,offline=0) rf(0000.00000000) 3
[2025-12-15T15:08:33.820399] [CLC-38] [I] MRSET (4194304 buckets) initialized
[2025-12-15T15:08:33.820474] [RV-38] [I] Media Recovery starts from 0000.0000c810 (min ckpt_tsn or backup_begin)
[2025-12-15T15:08:33.975717] [CLC-38] [I] MR set recovery target logfile (THREAD#0, seq #59): /home/tibero/tbarch/log-t0-r0-s59.arc 
[2025-12-15T15:08:33.976162] [RCF-38] [I] DATAFILE(DF #0) OPEN: for recovery (fuzzy_tsn 0000.0000c840)
[2025-12-15T15:08:33.976324] [RCF-38] [I] DATAFILE(DF #1) OPEN: for recovery (fuzzy_tsn 0000.0000c840)
[2025-12-15T15:08:33.976458] [RCF-38] [I] DATAFILE(DF #2) OPEN: for recovery (fuzzy_tsn 0000.0000c840)
[2025-12-15T15:08:33.976468] [RCF-38] [I] DATAFILE(DF #3) OPEN: for recovery (fuzzy_tsn 0000.0000c840)
....생략
[2025-12-15T15:08:38.227239] [RPL-38] [I] CV made the block corrupt by NOLOGGING DP: [3|02_00058920](13) SGMT=4294967295, op= 1. 5, flags=0x41.1, ver=0000.00000000/254, rba=72.2023.152 (blk=0000.0000cbf7/1)
[2025-12-15T15:08:38.227243] [RPL-38] [I] CV made the block corrupt by NOLOGGING DP: [3|02_00058921](13) SGMT=4294967295, op= 1. 5, flags=0x41.1, ver=0000.00000000/254, rba=72.2023.212 (blk=0000.0000cbf7/1)
[2025-12-15T15:08:38.227248] [RPL-38] [I] CV made the block corrupt by NOLOGGING DP: [3|02_00058922](13) SGMT=4294967295, op= 1. 5, flags=0x41.1, ver=0000.00000000/254, rba=72.2023.272 (blk=0000.0000cbf7/1)
[2025-12-15T15:08:38.227253] [RPL-38] [I] CV made the block corrupt by NOLOGGING DP: [3|02_00058923](13) SGMT=4294967295, op= 1. 5, flags=0x41.1, ver=0000.00000000/254, rba=72.2024.376 (blk=0000.0000cbf7/1)
[2025-12-15T15:08:38.227258] [RPL-38] [I] CV made the block corrupt by NOLOGGING DP: [3|02_00058924](13) SGMT=4294967295, op= 1. 5, flags=0x41.1, ver=0000.00000000/254, rba=72.2024.436 (blk=0000.0000cbf7/1)
[2025-12-15T15:08:38.227263] [RPL-38] [I] CV made the block corrupt by NOLOGGING DP: [3|02_00058925](13) SGMT=4294967295, op= 1. 5, flags=0x41.1, ver=0000.00000000/254, rba=72.2024.496 (blk=0000.0000cbf7/1)
[2025-12-15T15:08:38.227268] [RPL-38] [I] CV made the block corrupt by NOLOGGING DP: [3|02_00058926](13) SGMT=4294967295, op= 1. 5, flags=0x41.1, ver=0000.00000000/254, rba=72.2025.80 (blk=0000.0000cbf7/1)
[2025-12-15T15:08:38.227275] [RV-38] [I] MMR 59295 blks done
[2025-12-15T15:08:38.227280] [RV-38] [I] recovered until 0000.0000cc3e (incomp: 0)
[2025-12-15T15:08:42.538960] [RCF-38] [I] DB CKPT updated: 0000.0000cc3e => 0000.0000cc3e
[2025-12-15T15:08:42.584709] [RV-38] [I] fuzzy ffff.ffffffff stop ffff.ffffffff
[2025-12-15T15:08:42.615021] [RV-38] [I] MR finished until 0000.0000cc3e
[2025-12-15T15:08:42.780957] [RVP-11] [I] RV master received SLAVE 1(38) DONE
[2025-12-15T15:08:42.781265] [DDL-86] [I] Alter database type 0 succeeded.
[2025-12-15T15:08:42.781308] [DDL-86] [I] DDL execution succeeded

 

16.복구완료 후 적재 테이블 건수 확인 (corrupt 오류 발생)

 [tibero@node1 ~/tbdata]$ tbboot
Change core dump dir to /home/tibero/tibero7/bin/prof.
/usr/bin/pstack or /usr/bin/gstack is required to dump the callstack.
****************************************************
* The following path was not found.
* /usr/bin/pstack or /usr/bin/gstack
* pstack or gstack is required to dump the callstack.
****************************************************
Listener port = 8629

Tibero 7  

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Tibero instance started up (NORMAL mode).
[tibero@node1 ~/tbdata]$ tbsql sys/tibero

tbSQL 7

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

Connected to Tibero.

SQL> select count(*) from test.tbl_nologgin_dpi;
TBR-1152: Unable to read dp corrupt block: file# 2, block# 10.

 

결론

핫백업 수행 중 nologging + dpi 방식으로 데이터를 적재할 경우, 복구 과정에서 위 시나리오와 같이 정상적으로 복구가 완료되더라도 대상 테이블을 읽는 과정에서 corrupt 오류가 발생할 수 있습니다.

따라서 nologging + dpi 방식으로 데이터 적재를 수행한 이후에는 반드시 풀백업을 수행해야 합니다.

 

 

 

관련 대상