프린트 하기

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