내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
OS 환경 : Oracle Linux 8.4 (64bit)
DB 환경 : Oracle Database 19.3.0.0
방법 : 오라클 19c 인덱스 nologging 옵션 생성후 복구 테스트2(정상)
일반적으로 테이블을 생성할 때 nologging 옵션을 사용하고 데이터를 append, direct path 로 삽입한 뒤 장애가 발생할 경우 복구가 어려워서
nologging 을 사용한 뒤에는 꼭 전체 백업을 진행하라고 얘기함, 하지만 인덱스의 경우는 nologging 을 사용해서 생성한뒤 문제가 생기면 어떻게 되는지 확인해보기 위해 테스트를 진행해봄
이전글에서는 rman 을 이용해 백업을 진행하고, 이후에 테이블 생성 및 데이터 삽입을 한뒤, nologging 옵션을 이용해 인덱스를 생성함
이후에 db 를 abort 로 중지시키고 nologging 인덱스 생성 이전에 만들어진 백업본으로 db를 복구 할 경우 정상적으로 인덱스 사용이 가능한지 확인해보았음
참고 : 오라클 19c 인덱스 nologging 옵션 생성후 복구 테스트1 ( https://positivemh.tistory.com/1155 )
참고로 인덱스 생성시 nologging 을 사용했을때 리두가 덜생성 되어 생성속도가 빠름
참고 : 오라클 19c 인덱스 생성시 nologging 옵션 속도 차이 확인 ( https://positivemh.tistory.com/1010 )
본문에서는 1차 rman 백업 후, 이후에 테이블 생성 및 데이터 삽입을 한뒤, nologging 옵션을 이용해 인덱스를 생성한뒤 2차로 rman 백업을 한번 더 해주고
이후에 db 를 abort 로 중지시키고 nologging 인덱스 생성 이후에 만들어진 백업본으로 db를 복구 할 경우 정상적으로 인덱스 사용이 가능한지 확인해봄
테스트1은 mount 상태로 기동 후 database 만 restore 하고 recover 해서 확인해보고
테스트2는 nomount 상태로 기동 후 controlfile 도 restore 하고 이후 database restore 및 recover 해서 확인해봄
테스트
0. 사전작업
1. database 만 restore
2. controlfile 부터 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-08 14:49:38
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-08 14:49:38
channel ORA_DISK_1: finished piece 1 at 2024-09-08 14:49:53
piece handle=/oradata1/rman/ORACLE19_6834ji42_1_1_20240908.bk tag=TAG20240908T144938 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 2024-09-08 14:49:54
Starting Control File and SPFILE Autobackup at 2024-09-08 14:49:54
piece handle=/ORA19/app/oracle/product/19.0.0/db_1/dbs/c-3209222764-20240908-02 comment=NONE
Finished Control File and SPFILE Autobackup at 2024-09-08 14:49:55
|
백업이 완료됨
백업본 확인
|
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
|
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
186 Full 2.28G DISK 00:00:10 2024-09-08 14:49:48
BP Key: 186 Status: AVAILABLE Compressed: NO Tag: TAG20240908T144938
Piece Name: /oradata1/rman/ORACLE19_6834ji42_1_1_20240908.bk
List of Datafiles in backup set 186
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
1 Full 65910963 2024-09-08 14:49:38 NO /oradata1/oracle19/system01.dbf
2 Full 65910963 2024-09-08 14:49:38 NO /oradata1/oracle19/sysaux01.dbf
3 Full 65910963 2024-09-08 14:49:38 NO /oradata1/oracle19/test01.dbf
4 Full 65910963 2024-09-08 14:49:38 NO /oradata1/oracle19/users01.dbf
5 Full 65910963 2024-09-08 14:49:38 NO /oradata1/oracle19/imsits01.dbf
6 Full 65910963 2024-09-08 14:49:38 NO /oradata1/oracle19/users02.dbf
7 Full 65910963 2024-09-08 14:49:38 NO /oradata1/oracle19/undotbs02.dbf
9 Full 65910963 2024-09-08 14:49:38 NO /oradata1/oracle19/test02.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
187 Full 10.92M DISK 00:00:00 2024-09-08 14:49:54
BP Key: 187 Status: AVAILABLE Compressed: NO Tag: TAG20240908T144954
Piece Name: /ORA19/app/oracle/product/19.0.0/db_1/dbs/c-3209222764-20240908-02
SPFILE Included: Modification time: 2024-09-06 21:10:33
SPFILE db_unique_name: ORACLE19
Control File Included: Ckp SCN: 65910976 Ckp time: 2024-09-08 14:49:54
|
샘플 유저가 없는경우 생성
참고 : 오라클 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초 소요됨
샘플 인덱스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초 미만 차이남
인덱스 확인
|
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 옵션 차이가 나고, 상태는 둘다 정상임
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-08 14:58:21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 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-08 14:58:21
channel ORA_DISK_1: finished piece 1 at 2024-09-08 14:58:36
piece handle=/oradata1/rman/ORACLE19_6a34jikd_1_1_20240908.bk tag=TAG20240908T145821 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 2024-09-08 14:58:36
Starting Control File and SPFILE Autobackup at 2024-09-08 14:58:36
piece handle=/ORA19/app/oracle/product/19.0.0/db_1/dbs/c-3209222764-20240908-03 comment=NONE
Finished Control File and SPFILE Autobackup at 2024-09-08 14:58:37
|
2차 rman 백업의 tag 는 TAG20240908T145821 임
백업 목록 확인
|
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
|
RMAN> list backup;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
186 Full 2.28G DISK 00:00:10 2024-09-08 14:49:48
BP Key: 186 Status: AVAILABLE Compressed: NO Tag: TAG20240908T144938
Piece Name: /oradata1/rman/ORACLE19_6834ji42_1_1_20240908.bk
List of Datafiles in backup set 186
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
1 Full 65910963 2024-09-08 14:49:38 NO /oradata1/oracle19/system01.dbf
2 Full 65910963 2024-09-08 14:49:38 NO /oradata1/oracle19/sysaux01.dbf
3 Full 65910963 2024-09-08 14:49:38 NO /oradata1/oracle19/test01.dbf
4 Full 65910963 2024-09-08 14:49:38 NO /oradata1/oracle19/users01.dbf
5 Full 65910963 2024-09-08 14:49:38 NO /oradata1/oracle19/imsits01.dbf
6 Full 65910963 2024-09-08 14:49:38 NO /oradata1/oracle19/users02.dbf
7 Full 65910963 2024-09-08 14:49:38 NO /oradata1/oracle19/undotbs02.dbf
9 Full 65910963 2024-09-08 14:49:38 NO /oradata1/oracle19/test02.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
187 Full 10.92M DISK 00:00:00 2024-09-08 14:49:54
BP Key: 187 Status: AVAILABLE Compressed: NO Tag: TAG20240908T144954
Piece Name: /ORA19/app/oracle/product/19.0.0/db_1/dbs/c-3209222764-20240908-02
SPFILE Included: Modification time: 2024-09-06 21:10:33
SPFILE db_unique_name: ORACLE19
Control File Included: Ckp SCN: 65910976 Ckp time: 2024-09-08 14:49:54
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
188 Full 3.17G DISK 00:00:12 2024-09-08 14:58:33
BP Key: 188 Status: AVAILABLE Compressed: NO Tag: TAG20240908T145821
Piece Name: /oradata1/rman/ORACLE19_6a34jikd_1_1_20240908.bk
List of Datafiles in backup set 188
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
1 Full 65914020 2024-09-08 14:58:21 NO /oradata1/oracle19/system01.dbf
2 Full 65914020 2024-09-08 14:58:21 NO /oradata1/oracle19/sysaux01.dbf
3 Full 65914020 2024-09-08 14:58:21 NO /oradata1/oracle19/test01.dbf
4 Full 65914020 2024-09-08 14:58:21 NO /oradata1/oracle19/users01.dbf
5 Full 65914020 2024-09-08 14:58:21 NO /oradata1/oracle19/imsits01.dbf
6 Full 65914020 2024-09-08 14:58:21 NO /oradata1/oracle19/users02.dbf
7 Full 65914020 2024-09-08 14:58:21 NO /oradata1/oracle19/undotbs02.dbf
9 Full 65914020 2024-09-08 14:58:21 NO /oradata1/oracle19/test02.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
189 Full 10.92M DISK 00:00:00 2024-09-08 14:58:36
BP Key: 189 Status: AVAILABLE Compressed: NO Tag: TAG20240908T145836
Piece Name: /ORA19/app/oracle/product/19.0.0/db_1/dbs/c-3209222764-20240908-03
SPFILE Included: Modification time: 2024-09-06 21:10:33
SPFILE db_unique_name: ORACLE19
Control File Included: Ckp SCN: 65914033 Ckp time: 2024-09-08 14:58:36
|
풀백업 피스가 2개 존재함
리두 로그 확인
|
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 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 NO CURRENT 1024
1 2 /ORA19/app/oracle/oradata/ORACLE19/redo02.log YES INACTIVE 1024
1 3 /ORA19/app/oracle/oradata/ORACLE19/redo03.log YES INACTIVE 1024
|
정상적으로 current 로그가 아카이브됨
아카이브 로그 확인
|
1
2
3
|
$ ls -lh /ORA19/app/oracle/arch
total 649M
-rw-r----- 1 oracle oinstall 649M Sep 9 14:59 oracle19_1_11_1179004313.arc
|
db abort 로 종료
|
1
2
|
SQL> shutdown abort
ORACLE instance shut down.
|
1. database 만 restore
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 'TAG20240908T145821';
Starting restore at 2024-09-08 15:19:00
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=391 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_6a34jikd_1_1_20240908.bk
channel ORA_DISK_1: piece handle=/oradata1/rman/ORACLE19_6a34jikd_1_1_20240908.bk tag=TAG20240908T145821
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 2024-09-08 15:19:36
|
database restore 완료됨
참고용 명령어(backuppiece 를 지정해서도 restore 가능함)
|
1
2
|
RMAN> catalog backuppiece '/oradata1/rman/ORACLE19_6a34jikd_1_1_20240908.bk';
RMAN> restore database;
|
데이터베이스 recover 진행
|
1
2
3
4
5
6
7
8
9
|
RMAN> recover database;
Starting recover at 2024-09-08 15:20:25
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 2024-09-08 15:20:25
|
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 312
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)상태임
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 |
--------------------------------------------------------------------------
|
정상적으로 읽어짐
2번 테스트를 위해 recover 이후 발생한 아카이브 로그가 있다면 삭제
(모든 리두에 대해 로그 스위치를 모두 진행해서 redo 를 한번 다 내려주는게 다음 테스트를 이어하기에 좋음)
|
1
2
3
4
5
6
7
8
|
$ ls -al /ORA19/app/oracle/arch
total 664112
drwxr-xr-x 2 oracle oinstall 77 Sep 6 21:02 .
drwxrwxr-x. 10 oracle oinstall 159 Sep 6 21:00 ..
-rw-r----- 1 oracle oinstall 680370688 Sep 9 14:59 oracle19_1_11_1179004313.arc
-rw-r----- 1 oracle oinstall 4608 Sep 9 15:20 oracle19_1_12_1179004313.arc
$ rm /ORA19/app/oracle/arch/oracle19_1_12_1179004313.arc
|
os 에서 삭제한 뒤 rman 에서 crosscheck 진행
|
1
2
3
4
5
6
7
8
9
10
|
RMAN> crosscheck archivelog all;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
validation succeeded for archived log
archived log file name=/ORA19/app/oracle/arch/oracle19_1_9_1178989648.arc RECID=542 STAMP=1179003567
validation failed for archived log
archived log file name=/ORA19/app/oracle/arch/oracle19_1_10_1178989648.arc RECID=543 STAMP=1179003731
validation failed for archived log
|
rman 에서 expired 아카이브로그 삭제
|
1
|
RMAN> delete noprompt expired archivelog all;
|
테스트2를 하기 위한 준비가 완료됨
2. controlfile 부터 restore
db abort 로 종료 부터 시작
|
1
2
|
SQL> shutdown abort
ORACLE instance shut down.
|
rman 접속 후 db nomount 로 기동
|
1
2
3
4
5
6
7
8
9
10
|
RMAN> startup nomount
Oracle instance started
Total System Global Area 2147481656 bytes
Fixed Size 8898616 bytes
Variable Size 620756992 bytes
Database Buffers 1509949440 bytes
Redo Buffers 7876608 bytes
|
controlfile resotre 진행(2차 백업한 컨트롤파일로 restore 함)
|
1
2
3
4
5
6
7
8
9
10
11
12
|
RMAN> restore controlfile from '/ORA19/app/oracle/product/19.0.0/db_1/dbs/c-3209222764-20240908-03';
Starting restore at 2024-09-08 15:40:40
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=772 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/ORA19/app/oracle/oradata/ORACLE19/control01.ctl
output file name=/ORA19/app/oracle/oradata/ORACLE19/control02.ctl
Finished restore at 2024-09-08 15:40:41
|
mount 모드로 변경
|
1
2
3
4
|
RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed
|
2차 백업본을 이용해 restore 진행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
RMAN> restore database from tag 'TAG20240908T145821';
Starting restore at 2024-09-08 15:42:10
using channel ORA_DISK_1
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_6a34jikd_1_1_20240908.bk
channel ORA_DISK_1: piece handle=/oradata1/rman/ORACLE19_6a34jikd_1_1_20240908.bk tag=TAG20240908T145821
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 2024-09-08 15:42:45
|
데이터베이스 recover 진행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
RMAN> recover database;
Starting recover at 2024-09-08 15:43:00
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 12 is already on disk as file /ORA19/app/oracle/oradata/ORACLE19/redo01.log
archived log for thread 1 with sequence 13 is already on disk as file /ORA19/app/oracle/oradata/ORACLE19/redo02.log
archived log for thread 1 with sequence 14 is already on disk as file /ORA19/app/oracle/oradata/ORACLE19/redo03.log
archived log file name=/ORA19/app/oracle/arch/oracle19_1_11_1179004313.arc thread=1 sequence=11
archived log file name=/ORA19/app/oracle/oradata/ORACLE19/redo01.log thread=1 sequence=12
archived log file name=/ORA19/app/oracle/oradata/ORACLE19/redo02.log thread=1 sequence=13
archived log file name=/ORA19/app/oracle/oradata/ORACLE19/redo03.log thread=1 sequence=14
media recovery complete, elapsed time: 00:00:00
Finished recover at 2024-09-08 15:43:01
|
db open
|
1
2
3
|
RMAN> alter database open resetlogs;
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 312
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)임
logging 인덱스 사용 테스트
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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 로 생성한 경우 인덱스 생성 이전에 만든 백업본을 이용해 복구를 하게 되면 인덱스의 상태는 vaild 상태로 표시되지만
실제로 해당 인덱스를 읽는 쿼리를 수행하면 block corrupted 에러 메세지가 발생하여 인덱스를 정상적으로 사용할 수 없음
이 경우 인덱스를 rebuild 해줘야 정상적으로 사용가능함
하지만 본문 테스트처럼 nologging 로 인덱스를 생성한 이후에 만들어진 백업본을 이용해 복구를 하게 되면 인덱스의 상태도 vaild 상태로 표시되고 실제 사용도 정상적으로 가능함
그렇기 때문에 nologging 작업 이후에는 꼭 풀백업을 바로 진행해주어야 db 를 안전하게 운영할 수 있음
참조 :
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/1155
'ORACLE > Backup&Recover' 카테고리의 다른 글
| 오라클 19c 인덱스 nologging 옵션 생성후 복구 테스트3(정상) (0) | 2024.10.11 |
|---|---|
| 오라클 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 |