문서유형ㅣ기술정보
분야ㅣ백업/복구
적용제품버전ㅣ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 방식으로 데이터 적재를 수행한 이후에는 반드시 풀백업을 수행해야 합니다.