내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
OS 환경 : Oracle Linux 8.4 (64bit)
DB 환경 : Oracle Database 19.3.0.0
방법 : 오라클 19c 인덱스 nologging 옵션 생성후 복구 테스트3(정상)
일반적으로 테이블을 생성할 때 nologging 옵션을 사용하고 데이터를 append, direct path 로 삽입한 뒤 장애가 발생할 경우 복구가 어려워서
nologging 을 사용한 뒤에는 꼭 전체 백업을 진행하라고 얘기함, 하지만 인덱스의 경우는 nologging 을 사용해서 생성한뒤 문제가 생기면 어떻게 되는지 확인해보기 위해 테스트를 진행해봄
두번 째 전글에서는 rman 을 이용해 백업을 진행하고, 이후에 테이블 생성 및 데이터 삽입을 한뒤, nologging 옵션을 이용해 인덱스를 생성함
이후에 db 를 abort 로 중지시키고 nologging 인덱스 생성 이전에 만들어진 백업본으로 db를 복구 할 경우 정상적으로 인덱스 사용이 가능한지 확인해보았음
참고 : 오라클 19c 인덱스 nologging 옵션 생성후 복구 테스트1(에러발생) ( https://positivemh.tistory.com/1155 )
그리고 이전 글에서는 1차 rman 백업 후, 이후에 테이블 생성 및 데이터 삽입을 한뒤, nologging 옵션을 이용해 인덱스를 생성한뒤 2차로 rman 백업을 한번 더 해주고
이후에 db 를 abort 로 중지시키고 nologging 인덱스 생성 이후에 만들어진 백업본으로 db를 복구 할 경우 정상적으로 인덱스 사용이 가능한지 확인해보았음
참고 : 오라클 19c 인덱스 nologging 옵션 생성후 복구 테스트2(정상) ( https://positivemh.tistory.com/1156 )
이렇게 nologging 으로 인덱스를 생성하더라도 이후에 full 백업을 해주게 되면 추후에도 정상적으로 복구가 가능함
본문에서는 2차 백업 후 데이터를 더 삽입해 인덱스가 더 쌓이게 되면 2차 백업본으로 복구했을때 정상적으로 복구가 가능한지 확인해봄
(참고로 테스트를 여러번해서 중간에 extent 갯수는 맞지 않을 수 있음)
테스트
0. 사전작업
1. 스냅샷 저장
2_1. append insert 후 restore
2_2. 일반 insert 후 restore
테스트
0. 사전작업
rman 백업 환경설정 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
$ export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
$ rman target /
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORACLE19 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/oradata1/rman/%d_%U_%T.bk';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/ORA19/app/oracle/product/19.0.0/db_1/dbs/snapcf_oracle19_3.f';
|
/oradata1/rman 경로에 백업이 저장됨
기존 아카이브로그 모두 삭제
|
1
|
RMAN> delete noprompt archivelog all;
|
1차 rman 백업 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
RMAN> backup database;
Starting backup at 2024-09-22 16:36:17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/oradata1/oracle19/imsits01.dbf
input datafile file number=00007 name=/oradata1/oracle19/undotbs02.dbf
input datafile file number=00001 name=/oradata1/oracle19/system01.dbf
input datafile file number=00002 name=/oradata1/oracle19/sysaux01.dbf
input datafile file number=00004 name=/oradata1/oracle19/users01.dbf
input datafile file number=00006 name=/oradata1/oracle19/users02.dbf
input datafile file number=00003 name=/oradata1/oracle19/test01.dbf
input datafile file number=00009 name=/oradata1/oracle19/test02.dbf
channel ORA_DISK_1: starting piece 1 at 2024-09-22 16:36:17
channel ORA_DISK_1: finished piece 1 at 2024-09-22 16:36:24
piece handle=/oradata1/rman/ORACLE19_6r35olk1_1_1_20240922.bk tag=TAG20240922T163617 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 2024-09-22 16:36:24
Starting Control File and SPFILE Autobackup at 2024-09-22 16:36:24
piece handle=/ORA19/app/oracle/product/19.0.0/db_1/dbs/c-3209222764-20240922-03 comment=NONE
Finished Control File and SPFILE Autobackup at 2024-09-22 16:36:25
|
백업이 완료됨
백업본 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
|
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
199 Full 10.92M DISK 00:00:00 2024-09-22 16:01:54
BP Key: 199 Status: AVAILABLE Compressed: NO Tag: TAG20240922T160154
Piece Name: /ORA19/app/oracle/product/19.0.0/db_1/dbs/c-3209222764-20240922-02
SPFILE Included: Modification time: 2024-09-22 15:31:49
SPFILE db_unique_name: ORACLE19
Control File Included: Ckp SCN: 66963765 Ckp time: 2024-09-22 16:01:54
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
200 Full 2.24G DISK 00:00:06 2024-09-22 16:36:23
BP Key: 200 Status: AVAILABLE Compressed: NO Tag: TAG20240922T163617
Piece Name: /oradata1/rman/ORACLE19_6r35olk1_1_1_20240922.bk
List of Datafiles in backup set 200
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
1 Full 66964962 2024-09-22 16:36:17 NO /oradata1/oracle19/system01.dbf
2 Full 66964962 2024-09-22 16:36:17 NO /oradata1/oracle19/sysaux01.dbf
3 Full 66964962 2024-09-22 16:36:17 NO /oradata1/oracle19/test01.dbf
4 Full 66964962 2024-09-22 16:36:17 NO /oradata1/oracle19/users01.dbf
5 Full 66964962 2024-09-22 16:36:17 NO /oradata1/oracle19/imsits01.dbf
6 Full 66964962 2024-09-22 16:36:17 NO /oradata1/oracle19/users02.dbf
7 Full 66964962 2024-09-22 16:36:17 NO /oradata1/oracle19/undotbs02.dbf
9 Full 66964962 2024-09-22 16:36:17 NO /oradata1/oracle19/test02.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
201 Full 10.92M DISK 00:00:01 2024-09-22 16:36:25
BP Key: 201 Status: AVAILABLE Compressed: NO Tag: TAG20240922T163624
Piece Name: /ORA19/app/oracle/product/19.0.0/db_1/dbs/c-3209222764-20240922-03
SPFILE Included: Modification time: 2024-09-22 15:31:49
SPFILE db_unique_name: ORACLE19
Control File Included: Ckp SCN: 66964973 Ckp time: 2024-09-22 16:36:24
|
샘플 유저가 없는경우 생성
참고 : 오라클 19c 테스트 시 사용하는 imsi 유저 생성 쿼리 ( https://positivemh.tistory.com/1017 )
샘플 테이블 생성
|
1
2
3
4
|
SQL>
conn imsi/imsi
drop table t1 purge;
create table t1 (col1 number, col2 number, col3 varchar2(5));
|
샘플 데이터 삽입
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL>
DECLARE
TYPE tbl_ins IS TABLE OF t1%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR d IN 1..10 LOOP
FOR i IN 1..1000000 LOOP
w_ins(i).col1 := i||ceil(dbms_random.value(1, 10000000));
w_ins(i).col2 := i||ceil(dbms_random.value(1, 10000000));
w_ins(i).col3 := dbms_random.string('x',5);
END LOOP;
FORALL i in 1..1000000 INSERT INTO t1 VALUES w_ins(i);
COMMIT;
END LOOP;
END;
/
PL/SQL procedure successfully completed.
|
데이터가 정상적으로 삽입됨
테이블 크기 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
set lines 200 pages 1000
col owner for a15
col segment_name for a40
select owner, segment_name, segment_type, round(bytes/1024/1024,2) mb
from dba_segments
where owner = 'IMSI'
and segment_name = 'T1';
OWNER SEGMENT_NAME SEGMENT_TYPE MB
--------------- ---------------------------------------- ------------------ ----------
IMSI T1 TABLE 312
|
312mb임
세션 재접속 후 샘플 인덱스1 logging 으로 생성
|
1
2
3
4
5
6
7
|
SQL>
set timing on
create index t1_ix_log on t1(col1, col3) logging;
Index created.
Elapsed: 00:00:07.41
|
logging 로 생성시 7.41초 소요됨
리두 생성량 확인(redo_stat.sql)
|
1
2
3
4
5
6
7
8
9
|
SQL>
select round(vm.value/1024, 2) "REDO_KB", round(vm.value/1024/1024, 2) "REDO_MB"
from v$statname vs, v$mystat vm
where vs.statistic# = vm.statistic#
and vs.name = 'redo size';
REDO_KB REDO_MB
---------- ----------
290016.53 283.22
|
리두가 약 283mb 생성됨
세션 재접속 후 샘플 인덱스2 nologging 으로 생성
|
1
2
3
4
5
6
7
|
SQL>
set timing on
create index t1_ix_nolog on t1(col2, col3) nologging;
Index created.
Elapsed: 00:00:06.87
|
nologging 로 생성시 6.87초 소요됨
테이블 크게가 작아서 그런지 시간은 1초 미만 차이남
리두 생성량 확인(redo_stat.sql)
|
1
2
3
4
5
6
7
8
9
|
SQL>
select round(vm.value/1024, 2) "REDO_KB", round(vm.value/1024/1024, 2) "REDO_MB"
from v$statname vs, v$mystat vm
where vs.statistic# = vm.statistic#
and vs.name = 'redo size';
REDO_KB REDO_MB
---------- ----------
821.05 .8
|
리두가 약 0.8mb 생성됨
인덱스 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
|
SQL>
set lines 200 pages 1000
col owner for a15
col index_type for a10
col index_name for a20
col table_name for a10
col columns for a20
col logging for a10
SELECT a.*, (SELECT status from dba_indexes where index_name = a.index_name and rownum<=1) status from (
SELECT
ind.owner AS "OWNER",
ind.table_name AS "TABLE_NAME",
ind.index_name AS "INDEX_NAME",
ind.index_type AS "INDEX_TYPE",
ind.logging AS "LOGGING",
LISTAGG(ind_col.column_name, ', ') WITHIN GROUP (ORDER BY ind_col.column_position) AS "COLUMNS"
FROM
dba_indexes ind
JOIN
dba_ind_columns ind_col
ON ind.owner = ind_col.index_owner
AND ind.index_name = ind_col.index_name
WHERE
ind.table_name = 'T1'
AND ind.owner = 'IMSI'
GROUP BY
ind.owner,
ind.index_name,
ind.index_type,
ind.table_name,
ind.logging
ORDER BY
ind.owner,
ind.index_name) a;
OWNER TABLE_NAME INDEX_NAME INDEX_TYPE LOGGING COLUMNS STATUS
--------------- ---------- -------------------- ---------- ---------- -------------------- --------
IMSI T1 T1_IX_LOG NORMAL YES COL1, COL3 VALID
IMSI T1 T1_IX_NOLOG NORMAL NO COL2, COL3 VALID
|
logging 옵션 차이가 나고, 상태는 둘다 정상임
T1_IX_NOLOG 인덱스의 익스텐트 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL>
set lines 200 pages 1000
col segment_name for a20
select segment_name, blocks, count(*), max(extent_id)
from dba_extents
where segment_name = 'T1_IX_NOLOG'
group by segment_name, blocks
order by 2;
SEGMENT_NAME BLOCKS COUNT(*) MAX(EXTENT_ID)
-------------------- ---------- ---------- --------------
T1_IX_NOLOG 8 16 15
T1_IX_NOLOG 128 63 78
T1_IX_NOLOG 1024 28 106
|
2차 rman 백업 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
$ rman target /
RMAN> backup database;
Starting backup at 2024-09-22 16:39:54
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/oradata1/oracle19/imsits01.dbf
input datafile file number=00007 name=/oradata1/oracle19/undotbs02.dbf
input datafile file number=00001 name=/oradata1/oracle19/system01.dbf
input datafile file number=00002 name=/oradata1/oracle19/sysaux01.dbf
input datafile file number=00004 name=/oradata1/oracle19/users01.dbf
input datafile file number=00006 name=/oradata1/oracle19/users02.dbf
input datafile file number=00003 name=/oradata1/oracle19/test01.dbf
input datafile file number=00009 name=/oradata1/oracle19/test02.dbf
channel ORA_DISK_1: starting piece 1 at 2024-09-22 16:39:55
channel ORA_DISK_1: finished piece 1 at 2024-09-22 16:40:10
piece handle=/oradata1/rman/ORACLE19_6t35olqr_1_1_20240922.bk tag=TAG20240922T163954 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 2024-09-22 16:40:10
Starting Control File and SPFILE Autobackup at 2024-09-22 16:40:10
piece handle=/ORA19/app/oracle/product/19.0.0/db_1/dbs/c-3209222764-20240922-04 comment=NONE
Finished Control File and SPFILE Autobackup at 2024-09-22 16:40:11
|
2차 rman 백업의 tag 는 TAG20240922T163954 임
백업 목록 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
|
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
199 Full 10.92M DISK 00:00:00 2024-09-22 16:01:54
BP Key: 199 Status: AVAILABLE Compressed: NO Tag: TAG20240922T160154
Piece Name: /ORA19/app/oracle/product/19.0.0/db_1/dbs/c-3209222764-20240922-02
SPFILE Included: Modification time: 2024-09-22 15:31:49
SPFILE db_unique_name: ORACLE19
Control File Included: Ckp SCN: 66963765 Ckp time: 2024-09-22 16:01:54
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
200 Full 2.24G DISK 00:00:06 2024-09-22 16:36:23
BP Key: 200 Status: AVAILABLE Compressed: NO Tag: TAG20240922T163617
Piece Name: /oradata1/rman/ORACLE19_6r35olk1_1_1_20240922.bk
List of Datafiles in backup set 200
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
1 Full 66964962 2024-09-22 16:36:17 NO /oradata1/oracle19/system01.dbf
2 Full 66964962 2024-09-22 16:36:17 NO /oradata1/oracle19/sysaux01.dbf
3 Full 66964962 2024-09-22 16:36:17 NO /oradata1/oracle19/test01.dbf
4 Full 66964962 2024-09-22 16:36:17 NO /oradata1/oracle19/users01.dbf
5 Full 66964962 2024-09-22 16:36:17 NO /oradata1/oracle19/imsits01.dbf
6 Full 66964962 2024-09-22 16:36:17 NO /oradata1/oracle19/users02.dbf
7 Full 66964962 2024-09-22 16:36:17 NO /oradata1/oracle19/undotbs02.dbf
9 Full 66964962 2024-09-22 16:36:17 NO /oradata1/oracle19/test02.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
201 Full 10.92M DISK 00:00:01 2024-09-22 16:36:25
BP Key: 201 Status: AVAILABLE Compressed: NO Tag: TAG20240922T163624
Piece Name: /ORA19/app/oracle/product/19.0.0/db_1/dbs/c-3209222764-20240922-03
SPFILE Included: Modification time: 2024-09-22 15:31:49
SPFILE db_unique_name: ORACLE19
Control File Included: Ckp SCN: 66964973 Ckp time: 2024-09-22 16:36:24
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
202 Full 3.13G DISK 00:00:08 2024-09-22 16:40:03
BP Key: 202 Status: AVAILABLE Compressed: NO Tag: TAG20240922T163954
Piece Name: /oradata1/rman/ORACLE19_6t35olqr_1_1_20240922.bk
List of Datafiles in backup set 202
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
1 Full 66967539 2024-09-22 16:39:55 NO /oradata1/oracle19/system01.dbf
2 Full 66967539 2024-09-22 16:39:55 NO /oradata1/oracle19/sysaux01.dbf
3 Full 66967539 2024-09-22 16:39:55 NO /oradata1/oracle19/test01.dbf
4 Full 66967539 2024-09-22 16:39:55 NO /oradata1/oracle19/users01.dbf
5 Full 66967539 2024-09-22 16:39:55 NO /oradata1/oracle19/imsits01.dbf
6 Full 66967539 2024-09-22 16:39:55 NO /oradata1/oracle19/users02.dbf
7 Full 66967539 2024-09-22 16:39:55 NO /oradata1/oracle19/undotbs02.dbf
9 Full 66967539 2024-09-22 16:39:55 NO /oradata1/oracle19/test02.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
203 Full 10.92M DISK 00:00:00 2024-09-22 16:40:10
BP Key: 203 Status: AVAILABLE Compressed: NO Tag: TAG20240922T164010
Piece Name: /ORA19/app/oracle/product/19.0.0/db_1/dbs/c-3209222764-20240922-04
SPFILE Included: Modification time: 2024-09-22 15:31:49
SPFILE db_unique_name: ORACLE19
Control File Included: Ckp SCN: 66967555 Ckp time: 2024-09-22 16:40:10
|
풀백업 피스가 2개 존재함
vmware 사용중이므로 스냅샷 백업 진행(또는 2_2 테스트 전 이부분까지 다시 수행)
2_1. append insert 후 restore
샘플 데이터 추가 삽입
|
1
2
3
4
5
6
7
8
|
SQL>
insert /*+ append */ into t1 select * from t1;
10000000 rows created.
SQL> commit;
Commit complete.
|
테이블 크기 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
set lines 200 pages 1000
col owner for a15
col segment_name for a40
select owner, segment_name, segment_type, round(bytes/1024/1024,2) mb
from dba_segments
where owner = 'IMSI'
and segment_name = 'T1';
OWNER SEGMENT_NAME SEGMENT_TYPE MB
--------------- ---------------------------------------- ------------------ ----------
IMSI T1 TABLE 624
|
624mb임
T1_IX_NOLOG 인덱스의 익스텐트 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL>
set lines 200 pages 1000
col segment_name for a20
select segment_name, blocks, count(*), max(extent_id)
from dba_extents
where segment_name = 'T1_IX_NOLOG'
group by segment_name, blocks
order by 2;
SEGMENT_NAME BLOCKS COUNT(*) MAX(EXTENT_ID)
-------------------- ---------- ---------- --------------
T1_IX_NOLOG 8 16 15
T1_IX_NOLOG 128 63 78
T1_IX_NOLOG 256 2 111
T1_IX_NOLOG 384 1 109
T1_IX_NOLOG 896 1 108
T1_IX_NOLOG 1024 63 145
6 rows selected.
|
extent 들이 많이 추가됨
리두 로그 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
set lines 200 pages 1000
col member for a60
select thread#, l.group#, member, archived, l.status, (bytes/1024/1024) MB
from v$log l, v$logfile f
where f.group# = l.group#
order by 1, 2;
THREAD# GROUP# MEMBER ARC STATUS MB
---------- ---------- ------------------------------------------------------------ --- ---------------- ----------
1 1 /ORA19/app/oracle/oradata/ORACLE19/redo01.log YES ACTIVE 1024
1 2 /ORA19/app/oracle/oradata/ORACLE19/redo02.log NO CURRENT 1024
1 3 /ORA19/app/oracle/oradata/ORACLE19/redo03.log YES INACTIVE 1024
|
현재 current redo 를 아카이브로 내보내기
|
1
2
3
|
SQL> alter system archive log current;
System altered.
|
체크포인트 수행
|
1
2
3
|
SQL> alter system checkpoint;
System altered.
|
리두 로그 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
set lines 200 pages 1000
col member for a60
select thread#, l.group#, member, archived, l.status, (bytes/1024/1024) MB
from v$log l, v$logfile f
where f.group# = l.group#
order by 1, 2;
THREAD# GROUP# MEMBER ARC STATUS MB
---------- ---------- ------------------------------------------------------------ --- ---------------- ----------
1 1 /ORA19/app/oracle/oradata/ORACLE19/redo01.log YES INACTIVE 1024
1 2 /ORA19/app/oracle/oradata/ORACLE19/redo02.log YES INACTIVE 1024
1 3 /ORA19/app/oracle/oradata/ORACLE19/redo03.log NO CURRENT 1024
|
정상적으로 current 로그가 아카이브됨
아카이브 로그 확인
|
1
2
3
4
5
6
7
|
$ ls -lh /ORA19/app/oracle/arch
total 4.1G
-rw-r----- 1 oracle oinstall 919M Sep 22 16:42 oracle19_1_41_1179245436.arc
-rw-r----- 1 oracle oinstall 1000M Sep 22 16:45 oracle19_1_42_1179245436.arc
-rw-r----- 1 oracle oinstall 1000M Sep 22 16:45 oracle19_1_43_1179245436.arc
-rw-r----- 1 oracle oinstall 1000M Sep 22 16:45 oracle19_1_44_1179245436.arc
-rw-r----- 1 oracle oinstall 212M Sep 22 16:46 oracle19_1_45_1179245436.arc
|
db abort 로 종료
|
1
2
|
SQL> shutdown abort
ORACLE instance shut down.
|
rman 접속 후 db mount 로 기동
|
1
2
3
4
5
6
7
8
9
10
11
12
|
RMAN> startup mount
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 2147481656 bytes
Fixed Size 8898616 bytes
Variable Size 620756992 bytes
Database Buffers 1509949440 bytes
Redo Buffers 7876608 bytes
|
2차 백업본을 이용해 restore
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
RMAN> restore database from tag 'TAG20240922T163954';
Starting restore at 2024-09-22 16:45:40
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1153 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /oradata1/oracle19/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /oradata1/oracle19/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /oradata1/oracle19/test01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /oradata1/oracle19/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /oradata1/oracle19/imsits01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /oradata1/oracle19/users02.dbf
channel ORA_DISK_1: restoring datafile 00007 to /oradata1/oracle19/undotbs02.dbf
channel ORA_DISK_1: restoring datafile 00009 to /oradata1/oracle19/test02.dbf
channel ORA_DISK_1: reading from backup piece /oradata1/rman/ORACLE19_6t35olqr_1_1_20240922.bk
channel ORA_DISK_1: piece handle=/oradata1/rman/ORACLE19_6t35olqr_1_1_20240922.bk tag=TAG20240922T163954
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 2024-09-22 16:46:15
|
database restore 완료됨
참고용 명령어(backuppiece 를 지정해서도 restore 가능함)
|
1
2
|
RMAN> catalog backuppiece '/oradata1/rman/ORACLE19_6t35olqr_1_1_20240922.bk';
RMAN> restore database;
|
데이터베이스 recover 진행
|
1
2
3
4
5
6
7
8
9
|
RMAN> recover database;
Starting recover at 2024-09-22 16:46:33
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished recover at 2024-09-22 16:46:36
|
db open
|
1
2
3
|
RMAN> alter database open;
Statement processed
|
테이블 및 인덱스 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
|
SQL>
set lines 200 pages 1000
col owner for a15
col segment_name for a40
select owner, segment_name, segment_type, round(bytes/1024/1024,2) mb
from dba_segments
where owner = 'IMSI'
and segment_name = 'T1';
OWNER SEGMENT_NAME SEGMENT_TYPE MB
--------------- ---------------------------------------- ------------------ ----------
IMSI T1 TABLE 624
SQL>
set lines 200 pages 1000
col owner for a15
col index_type for a10
col index_name for a20
col table_name for a10
col columns for a20
col logging for a10
SELECT a.*, (SELECT status from dba_indexes where index_name = a.index_name and rownum<=1) status from (
SELECT
ind.owner AS "OWNER",
ind.table_name AS "TABLE_NAME",
ind.index_name AS "INDEX_NAME",
ind.index_type AS "INDEX_TYPE",
ind.logging AS "LOGGING",
LISTAGG(ind_col.column_name, ', ') WITHIN GROUP (ORDER BY ind_col.column_position) AS "COLUMNS"
FROM
dba_indexes ind
JOIN
dba_ind_columns ind_col
ON ind.owner = ind_col.index_owner
AND ind.index_name = ind_col.index_name
WHERE
ind.table_name = 'T1'
AND ind.owner = 'IMSI'
GROUP BY
ind.owner,
ind.index_name,
ind.index_type,
ind.table_name,
ind.logging
ORDER BY
ind.owner,
ind.index_name) a;
OWNER TABLE_NAME INDEX_NAME INDEX_TYPE LOGGING COLUMNS STATUS
--------------- ---------- -------------------- ---------- ---------- -------------------- --------
IMSI T1 T1_IX_LOG NORMAL YES COL1, COL3 VALID
IMSI T1 T1_IX_NOLOG NORMAL NO COL2, COL3 VALID
|
테이블과 인덱스 모두 정상(vaild)상태임
T1_IX_NOLOG 인덱스의 익스텐트 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL>
set lines 200 pages 1000
col segment_name for a20
select segment_name, blocks, count(*), max(extent_id)
from dba_extents
where segment_name = 'T1_IX_NOLOG'
group by segment_name, blocks
order by 2;
SEGMENT_NAME BLOCKS COUNT(*) MAX(EXTENT_ID)
-------------------- ---------- ---------- --------------
T1_IX_NOLOG 8 16 15
T1_IX_NOLOG 128 63 78
T1_IX_NOLOG 256 2 111
T1_IX_NOLOG 384 1 109
T1_IX_NOLOG 896 1 108
T1_IX_NOLOG 1024 63 145
6 rows selected.
|
shutdown abort 전에 존재했던 extent 가 모두 존재함
logging 인덱스 사용 테스트
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
conn imsi/imsi
set autot on
select /*+ index(t1 t1_ix_log) */ * from t1 where col1 = 1;
no rows selected
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 32408 | 949K| 10751 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 32408 | 949K| 10751 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_IX_LOG | 12963 | | 146 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
|
logging 옵션으로 생성한 인덱스는 정상적으로 사용됨
nologging 인덱스 사용 테스트
|
1
2
3
4
5
6
7
8
9
10
|
SQL> select /*+ index(t1 t1_ix_nolog) */ * from t1 where col2 = 1;
no rows selected
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 32408 | 949K| 10751 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 32408 | 949K| 10751 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_IX_NOLOG | 12963 | | 146 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
|
nologging 옵션으로 생성한 인덱스도 block corrupted 없이 정상적으로 사용됨
full scan 확인
|
1
2
3
4
5
6
7
8
9
|
SQL> select /*+ full(t1) */ * from t1 where col2 = 1;
no rows selected
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 32408 | 949K| 10761 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 32408 | 949K| 10761 (1)| 00:00:01 |
--------------------------------------------------------------------------
|
정상적으로 읽어짐
nologging 으로 생성한 인덱스도 이후에 데이터가 삽입된 뒤에도 정상적으로 복구가됨
초기 생성 또는 rebuild 이후에는 nologging 이어도 복구가 되는게 맞는건지 확인하기 위해
샘플 테이블을 추가로 만들고 리두 생성량을 각각 확인해봄
샘플 테이블 count 확인
|
1
2
3
4
5
|
SQL> select count(*) from t1;
COUNT(*)
----------
20000000
|
샘플 테이블2, 3 생성
|
1
2
3
4
5
|
SQL>
drop table t2 purge;
create table t2 (col1 number, col2 number, col3 varchar2(5));
drop table t3 purge;
create table t3 (col1 number, col2 number, col3 varchar2(5));
|
샘플 테이블2에 인덱스 생성(logging)
|
1
2
3
|
SQL> create index t2_ix_nolog on t2(col2, col3) logging;
Index created.
|
샘플 테이블3에 인덱스 생성(nologging)
|
1
2
3
|
SQL> create index t3_ix_nolog on t3(col2, col3) nologging;
Index created.
|
세션 재접속 후 샘플 테이블2에 데이터 삽입
|
1
2
3
|
SQL> insert /*+ append */ into t2 select * from t1 where rownum <= 1000000;
1000000 rows created.
|
logging 인덱스 리두 생성량 확인(redo_stat.sql)
|
1
2
3
4
5
6
7
8
9
|
SQL>
select round(vm.value/1024, 2) "REDO_KB", round(vm.value/1024/1024, 2) "REDO_MB"
from v$statname vs, v$mystat vm
where vs.statistic# = vm.statistic#
and vs.name = 'redo size';
REDO_KB REDO_MB
---------- ----------
139622.55 136.35
|
세션 재접속 후 샘플 테이블3에 데이터 삽입
|
1
2
3
|
SQL> insert /*+ append */ into t3 select * from t1 where rownum <= 1000000;
1000000 rows created.
|
nologging 인덱스 리두 생성량 확인(redo_stat.sql)
|
1
2
3
4
5
6
7
8
9
|
SQL>
select round(vm.value/1024, 2) "REDO_KB", round(vm.value/1024/1024, 2) "REDO_MB"
from v$statname vs, v$mystat vm
where vs.statistic# = vm.statistic#
and vs.name = 'redo size';
REDO_KB REDO_MB
---------- ----------
139868.17 136.59
|
데이터가 이미 존재하는 상태에서 nologging 옵션으로 인덱스 생성시에는 유의미하게 리두 생성량을 줄여졌지만
데이터가 존재하지 않는 상태에서 nologging 옵션으로 인덱스 생성시에는 이후 append 로 데이터 삽입시 리두 생성량을 줄이지 못했음
리두 생성량이 줄지 않았기 때문에 속도는 동일할것으로 예상되지만 상세 테스트는 다음글에서 진행해보겠음
2_1 테스트 전에 만들어둔 스냅샷으로 복구(또는 2_1 테스트 전 부분까지 다시 수행)
2_2. 일반 insert 후 restore
샘플 데이터 추가 삽입
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SQL>
DECLARE
TYPE tbl_ins IS TABLE OF t1%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..1000000 LOOP
w_ins(i).col1 := i||ceil(dbms_random.value(1, 10000000));
w_ins(i).col2 := i||ceil(dbms_random.value(1, 10000000));
w_ins(i).col3 := dbms_random.string('y',5);
END LOOP;
FORALL i in 1..1000000 INSERT INTO t1 VALUES w_ins(i);
COMMIT;
END;
/
PL/SQL procedure successfully completed.
|
데이터가 정상적으로 삽입됨
테이블 크기 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
set lines 200 pages 1000
col owner for a15
col segment_name for a40
select owner, segment_name, segment_type, round(bytes/1024/1024,2) mb
from dba_segments
where owner = 'IMSI'
and segment_name = 'T1';
OWNER SEGMENT_NAME SEGMENT_TYPE MB
--------------- ---------------------------------------- ------------------ ----------
IMSI T1 TABLE 344
|
344mb임
T1_IX_NOLOG 인덱스의 익스텐트 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL>
set lines 200 pages 1000
col segment_name for a20
select segment_name, blocks, count(*), max(extent_id)
from dba_extents
where segment_name = 'T1_IX_NOLOG'
group by segment_name, blocks
order by 2;
SEGMENT_NAME BLOCKS COUNT(*) MAX(EXTENT_ID)
-------------------- ---------- ---------- --------------
T1_IX_NOLOG 8 16 15
T1_IX_NOLOG 128 63 78
T1_IX_NOLOG 1024 29 107
|
1024 block 를 가진 extent 가 1개 추가됨
리두 로그 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
set lines 200 pages 1000
col member for a60
select thread#, l.group#, member, archived, l.status, (bytes/1024/1024) MB
from v$log l, v$logfile f
where f.group# = l.group#
order by 1, 2;
THREAD# GROUP# MEMBER ARC STATUS MB
---------- ---------- ------------------------------------------------------------ --- ---------------- ----------
1 1 /ORA19/app/oracle/oradata/ORACLE19/redo01.log YES ACTIVE 1024
1 2 /ORA19/app/oracle/oradata/ORACLE19/redo02.log NO CURRENT 1024
1 3 /ORA19/app/oracle/oradata/ORACLE19/redo03.log YES INACTIVE 1024
|
현재 current redo 를 아카이브로 내보내기
|
1
2
3
|
SQL> alter system archive log current;
System altered.
|
체크포인트 수행
|
1
2
3
|
SQL> alter system checkpoint;
System altered.
|
리두 로그 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
set lines 200 pages 1000
col member for a60
select thread#, l.group#, member, archived, l.status, (bytes/1024/1024) MB
from v$log l, v$logfile f
where f.group# = l.group#
order by 1, 2;
THREAD# GROUP# MEMBER ARC STATUS MB
---------- ---------- ------------------------------------------------------------ --- ---------------- ----------
1 1 /ORA19/app/oracle/oradata/ORACLE19/redo01.log YES INACTIVE 1024
1 2 /ORA19/app/oracle/oradata/ORACLE19/redo02.log YES INACTIVE 1024
1 3 /ORA19/app/oracle/oradata/ORACLE19/redo03.log NO CURRENT 1024
|
정상적으로 current 로그가 아카이브됨
아카이브 로그 확인
|
1
2
3
4
|
$ ls -lh /ORA19/app/oracle/arch
total 932M
-rw-r----- 1 oracle oinstall 932M Sep 22 16:42 oracle19_1_26_1179245436.arc
-rw-r----- 1 oracle oinstall 28K Sep 22 16:44 oracle19_1_27_1179245436.arc
|
db abort 로 종료
|
1
2
|
SQL> shutdown abort
ORACLE instance shut down.
|
rman 접속 후 db mount 로 기동
|
1
2
3
4
5
6
7
8
9
10
11
12
|
RMAN> startup mount
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 2147481656 bytes
Fixed Size 8898616 bytes
Variable Size 620756992 bytes
Database Buffers 1509949440 bytes
Redo Buffers 7876608 bytes
|
2차 백업본을 이용해 restore
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
RMAN> restore database from tag 'TAG20240922T163954';
Starting restore at 2024-09-22 16:45:40
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1153 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /oradata1/oracle19/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /oradata1/oracle19/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /oradata1/oracle19/test01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /oradata1/oracle19/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /oradata1/oracle19/imsits01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /oradata1/oracle19/users02.dbf
channel ORA_DISK_1: restoring datafile 00007 to /oradata1/oracle19/undotbs02.dbf
channel ORA_DISK_1: restoring datafile 00009 to /oradata1/oracle19/test02.dbf
channel ORA_DISK_1: reading from backup piece /oradata1/rman/ORACLE19_6t35olqr_1_1_20240922.bk
channel ORA_DISK_1: piece handle=/oradata1/rman/ORACLE19_6t35olqr_1_1_20240922.bk tag=TAG20240922T163954
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 2024-09-22 16:46:15
|
database restore 완료됨
참고용 명령어(backuppiece 를 지정해서도 restore 가능함)
|
1
2
|
RMAN> catalog backuppiece '/oradata1/rman/ORACLE19_6t35olqr_1_1_20240922.bk';
RMAN> restore database;
|
데이터베이스 recover 진행
|
1
2
3
4
5
6
7
8
9
|
RMAN> recover database;
Starting recover at 2024-09-22 16:46:33
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished recover at 2024-09-22 16:46:36
|
db open
|
1
2
3
|
RMAN> alter database open;
Statement processed
|
테이블 및 인덱스 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
|
SQL>
set lines 200 pages 1000
col owner for a15
col segment_name for a40
select owner, segment_name, segment_type, round(bytes/1024/1024,2) mb
from dba_segments
where owner = 'IMSI'
and segment_name = 'T1';
OWNER SEGMENT_NAME SEGMENT_TYPE MB
--------------- ---------------------------------------- ------------------ ----------
IMSI T1 TABLE 344
SQL>
set lines 200 pages 1000
col owner for a15
col index_type for a10
col index_name for a20
col table_name for a10
col columns for a20
col logging for a10
SELECT a.*, (SELECT status from dba_indexes where index_name = a.index_name and rownum<=1) status from (
SELECT
ind.owner AS "OWNER",
ind.table_name AS "TABLE_NAME",
ind.index_name AS "INDEX_NAME",
ind.index_type AS "INDEX_TYPE",
ind.logging AS "LOGGING",
LISTAGG(ind_col.column_name, ', ') WITHIN GROUP (ORDER BY ind_col.column_position) AS "COLUMNS"
FROM
dba_indexes ind
JOIN
dba_ind_columns ind_col
ON ind.owner = ind_col.index_owner
AND ind.index_name = ind_col.index_name
WHERE
ind.table_name = 'T1'
AND ind.owner = 'IMSI'
GROUP BY
ind.owner,
ind.index_name,
ind.index_type,
ind.table_name,
ind.logging
ORDER BY
ind.owner,
ind.index_name) a;
OWNER TABLE_NAME INDEX_NAME INDEX_TYPE LOGGING COLUMNS STATUS
--------------- ---------- -------------------- ---------- ---------- -------------------- --------
IMSI T1 T1_IX_LOG NORMAL YES COL1, COL3 VALID
IMSI T1 T1_IX_NOLOG NORMAL NO COL2, COL3 VALID
|
테이블과 인덱스 모두 정상(vaild)상태임
T1_IX_NOLOG 인덱스의 익스텐트 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL>
set lines 200 pages 1000
col segment_name for a20
select segment_name, blocks, count(*), max(extent_id)
from dba_extents
where segment_name = 'T1_IX_NOLOG'
group by segment_name, blocks
order by 2;
SEGMENT_NAME BLOCKS COUNT(*) MAX(EXTENT_ID)
-------------------- ---------- ---------- --------------
T1_IX_NOLOG 8 16 15
T1_IX_NOLOG 128 63 78
T1_IX_NOLOG 1024 29 107
|
shutdown abort 전에 존재했던 extent 가 모두 존재함
logging 인덱스 사용 테스트
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
conn imsi/imsi
set autot on
select /*+ index(t1 t1_ix_log) */ * from t1 where col1 = 1;
no rows selected
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 32408 | 949K| 10751 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 32408 | 949K| 10751 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_IX_LOG | 12963 | | 146 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
|
logging 옵션으로 생성한 인덱스는 정상적으로 사용됨
nologging 인덱스 사용 테스트
|
1
2
3
4
5
6
7
8
9
10
|
SQL> select /*+ index(t1 t1_ix_nolog) */ * from t1 where col2 = 1;
no rows selected
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 32408 | 949K| 10751 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 32408 | 949K| 10751 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_IX_NOLOG | 12963 | | 146 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
|
nologging 옵션으로 생성한 인덱스도 block corrupted 없이 정상적으로 사용됨
full scan 확인
|
1
2
3
4
5
6
7
8
9
|
SQL> select /*+ full(t1) */ * from t1 where col2 = 1;
no rows selected
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 32408 | 949K| 10761 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 32408 | 949K| 10761 (1)| 00:00:01 |
--------------------------------------------------------------------------
|
정상적으로 읽어짐
nologging 으로 생성한 인덱스도 이후에 데이터가 삽입된 뒤에도 정상적으로 복구가됨
초기 생성 또는 rebuild 이후에는 nologging 이어도 복구가 되는게 맞는건지 확인하기 위해
샘플 테이블을 추가로 만들고 리두 생성량을 각각 확인해봄
샘플 테이블 count 확인
|
1
2
3
4
5
|
SQL> select count(*) from t1;
COUNT(*)
----------
11000000
|
샘플 테이블2, 3 생성
|
1
2
3
4
5
|
SQL>
drop table t2 purge;
create table t2 (col1 number, col2 number, col3 varchar2(5));
drop table t3 purge;
create table t3 (col1 number, col2 number, col3 varchar2(5));
|
샘플 테이블2에 인덱스 생성(logging)
|
1
2
3
|
SQL> create index t2_ix_nolog on t2(col2, col3) logging;
Index created.
|
샘플 테이블3에 인덱스 생성(nologging)
|
1
2
3
|
SQL> create index t3_ix_nolog on t3(col2, col3) nologging;
Index created.
|
세션 재접속 후 샘플 테이블2에 데이터 삽입
|
1
2
3
|
SQL> insert into t2 select * from t1 where rownum <= 1000000;
1000000 rows created.
|
logging 인덱스 리두 생성량 확인(redo_stat.sql)
|
1
2
3
4
5
6
7
8
9
|
SQL>
select round(vm.value/1024, 2) "REDO_KB", round(vm.value/1024/1024, 2) "REDO_MB"
from v$statname vs, v$mystat vm
where vs.statistic# = vm.statistic#
and vs.name = 'redo size';
REDO_KB REDO_MB
---------- ----------
233679.4 228.2
|
세션 재접속 후 샘플 테이블3에 데이터 삽입
|
1
2
3
|
SQL> insert into t3 select * from t1 where rownum <= 1000000;
1000000 rows created.
|
nologging 인덱스 리두 생성량 확인(redo_stat.sql)
|
1
2
3
4
5
6
7
8
9
|
SQL>
select round(vm.value/1024, 2) "REDO_KB", round(vm.value/1024/1024, 2) "REDO_MB"
from v$statname vs, v$mystat vm
where vs.statistic# = vm.statistic#
and vs.name = 'redo size';
REDO_KB REDO_MB
---------- ----------
233609.97 228.13
|
데이터가 이미 존재하는 상태에서 nologging 옵션으로 인덱스 생성시에는 유의미하게 리두 생성량을 줄여졌지만
데이터가 존재하지 않는 상태에서 nologging 옵션으로 인덱스 생성시에는 이후 데이터 삽입시 리두 생성량을 줄이지 못했음
리두 생성량이 줄지 않았기 때문에 속도는 동일할것으로 예상되지만 상세 테스트는 다음글에서 진행해보겠음
결론 :
백업 이후 인덱스를 nologging 로 생성한 경우 인덱스 생성 이전에 만든 백업본을 이용해 복구를 하게 되면 인덱스의 상태는 vaild 상태로 표시되지만
실제로 해당 인덱스를 읽는 쿼리를 수행하면 block corrupted 에러 메세지가 발생하여 인덱스를 정상적으로 사용할 수 없음
이 경우 인덱스를 rebuild 해줘야 정상적으로 사용가능함
하지만 본문 테스트처럼 nologging 로 인덱스를 생성한 이후에 만들어진 백업본을 이용해 복구를 하게 되면 인덱스의 상태도 vaild 상태로 표시되고 실제 사용도 정상적으로 가능함
그리고 이후에 삽입되는 데이터의 경우에도(append, noappend 모두) redo 가 nologging 옵션과 관계 없이 정상적으로 쌓이기 때문에 2차 백업본으로 복구하는 경우 인덱스가 깨지지 않고 정상적으로 사용가능한것으로 보임
그렇기 때문에 nologging 작업 이후에는 꼭 풀백업을 바로 진행해주어야 db 를 안전하게 운영할 수 있음
(그렇다면 인덱스는 nologging 으로 운영해도 문제가 없는것인지 의문이 듬, 해당부분에 대해 아시는분이 있다면 댓글 부탁드립니다)
참고 :
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/logging_clause.html#GUID-C4212274-5595-4045-A599-F033772C496E
https://forums.oracle.com/ords/apexds/post/restore-database-using-old-backup-piece-1304
https://positivemh.tistory.com/1010
https://positivemh.tistory.com/1063
https://positivemh.tistory.com/1155
https://positivemh.tistory.com/1156
'ORACLE > Backup&Recover' 카테고리의 다른 글
| 오라클 19c 인덱스 nologging 옵션 생성후 복구 테스트2(정상) (0) | 2024.09.19 |
|---|---|
| 오라클 19c 인덱스 nologging 옵션 생성후 복구 테스트1(에러발생) (0) | 2024.09.16 |
| 오라클 19c 스탠다드 에디션에서 엔터프라이즈 에디션으로 datapump 테스트 (0) | 2024.04.13 |
| 오라클 19c 스탠다드 에디션에서 엔터프라이즈 에디션으로 백업복구(cold backup) 테스트 (0) | 2024.04.01 |
| 오라클 19c DUL 이용 Truncate 된 데이터 복구(비공식 툴) (0) | 2024.03.28 |