OS 환경 : Oracle Linux 8.4 (64bit)
DB 환경 : Oracle Database 19.3.0.0
방법 : 오라클 19c 인덱스 nologging 옵션 생성후 복구 테스트1(에러발생)
일반적으로 테이블을 생성할 때 nologging 옵션을 사용하고 데이터를 append, direct path 로 삽입한 뒤 장애가 발생할 경우 복구가 어려워서
nologging 을 사용한 뒤에는 꼭 전체 백업을 진행하라고 얘기함, 하지만 인덱스의 경우는 nologging 을 사용해서 생성한뒤 문제가 생기면 어떻게 되는지 확인해보기 위해 테스트를 진행해봄
참고로 인덱스 생성시 nologging 을 사용했을때 리두가 덜생성 되어 생성속도가 빠름
참고 : 오라클 19c 인덱스 생성시 nologging 옵션 속도 차이 확인 ( https://positivemh.tistory.com/1010 )
본문에서는 rman 을 이용해 백업을 진행하고, 이후에 테이블 생성 및 데이터 삽입을 한뒤, nologging 옵션을 이용해 인덱스를 생성함
이후에 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;
|
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
|
RMAN> backup database;
Starting backup at 2024-09-06 20:56:06
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 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-06 20:56:06
channel ORA_DISK_1: finished piece 1 at 2024-09-06 20:56:21
piece handle=/oradata1/rman/ORACLE19_5u34caf6_1_1_20240906.bk tag=TAG20240906T205606 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 2024-09-06 20:56:21
Starting Control File and SPFILE Autobackup at 2024-09-06 20:56:21
piece handle=/ORA19/app/oracle/product/19.0.0/db_1/dbs/c-3209222764-20240906-06 comment=NONE
Finished Control File and SPFILE Autobackup at 2024-09-06 20:56:22
|
백업이 완료됨
백업본 확인
|
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
------- ---- -- ---------- ----------- ------------ -------------------
178 Full 2.32G DISK 00:00:09 2024-09-06 20:56:15
BP Key: 178 Status: AVAILABLE Compressed: NO Tag: TAG20240906T205606
Piece Name: /oradata1/rman/ORACLE19_5u34caf6_1_1_20240906.bk
List of Datafiles in backup set 178
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
1 Full 65386458 2024-09-06 20:56:06 NO /oradata1/oracle19/system01.dbf
2 Full 65386458 2024-09-06 20:56:06 NO /oradata1/oracle19/sysaux01.dbf
3 Full 65386458 2024-09-06 20:56:06 NO /oradata1/oracle19/test01.dbf
4 Full 65386458 2024-09-06 20:56:06 NO /oradata1/oracle19/users01.dbf
5 Full 65386458 2024-09-06 20:56:06 NO /oradata1/oracle19/imsits01.dbf
6 Full 65386458 2024-09-06 20:56:06 NO /oradata1/oracle19/users02.dbf
7 Full 65386458 2024-09-06 20:56:06 NO /oradata1/oracle19/undotbs02.dbf
9 Full 65386458 2024-09-06 20:56:06 NO /oradata1/oracle19/test02.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
179 Full 10.92M DISK 00:00:00 2024-09-06 20:56:21
BP Key: 179 Status: AVAILABLE Compressed: NO Tag: TAG20240906T205621
Piece Name: /ORA19/app/oracle/product/19.0.0/db_1/dbs/c-3209222764-20240906-06
SPFILE Included: Modification time: 2024-09-06 20:52:01
SPFILE db_unique_name: ORACLE19
Control File Included: Ckp SCN: 65386471 Ckp time: 2024-09-06 20:56:21
|
샘플 유저가 없는경우 생성
참고 : 오라클 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 옵션 차이가 나고, 상태는 둘다 정상임
리두 로그 확인
|
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 648M
-rw-r----- 1 oracle oinstall 648M Sep 6 20:59 oracle19_1_9_1178989648.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
|
백업본 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;
Starting restore at 2024-09-06 21:01:00
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_5u34caf6_1_1_20240906.bk
channel ORA_DISK_1: piece handle=/oradata1/rman/ORACLE19_5u34caf6_1_1_20240906.bk tag=TAG20240906T205606
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 2024-09-06 21:01:36
|
database restore 완료됨
데이터베이스 recover 진행
|
1
2
3
4
5
6
7
8
9
|
RMAN> recover database;
Starting recover at 2024-09-06 21:01:52
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:05
Finished recover at 2024-09-06 21:01:57
|
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)임
nologging 인덱스를 invaild 일것이라고 생각했는데 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
|
SQL> select /*+ index(t1 t1_ix_nolog) */ * from t1 where col2 = 1;
select /*+ index(t1 t1_ix_nolog) */ * from t1 where col2 = 1
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 100120)
ORA-01110: data file 5: '/oradata1/oracle19/imsits01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
|
nologging 옵션으로 생성한 인덱스는 block corrupted 가 발생했다고 나옴
alert log 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
$ vi /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/alert_oracle19.log
2024-09-06T21:02:46.198056+09:00
Errors in file /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_3364033.trc (incident=1044287):
ORA-01578: ORACLE data block corrupted (file # 5, block # 100120)
ORA-01110: data file 5: '/oradata1/oracle19/imsits01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
Incident details in: /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/incident/incdir_1044287/oracle19_ora_3364033_i1044287.trc
2024-09-06T21:02:46.939428+09:00
*****************************************************************
An internal routine has requested a dump of selected redo.
This usually happens following a specific internal error, when
analysis of the redo logs will help Oracle Support with the
diagnosis.
It is recommended that you retain all the redo logs generated (by
all the instances) during the past 12 hours, in case additional
redo dumps are required to help with the diagnosis.
*****************************************************************
|
alert log 에도 에러가 남음
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 |
--------------------------------------------------------------------------
|
정상적으로 읽어짐
해결을 위해 t1_ix_nolog 인덱스 rebuild
|
1
2
3
|
SQL> alter index t1_ix_nolog rebuild online;
Index altered.
|
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 |
---------------------------------------------------------------------------------------------------
|
rebuild 이후 인덱스가 정상적으로 이용됨
2번 테스트를 위해 recover 이후 발생한 아카이브 로그가 있다면 삭제
|
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 909824 Sep 6 21:02 oracle19_1_10_1178989648.arc
-rw-r----- 1 oracle oinstall 679134720 Sep 6 20:59 oracle19_1_9_1178989648.arc
$ rm /ORA19/app/oracle/arch/oracle19_1_10_1178989648.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
|
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 진행(초기에 백업한 컨트롤파일로 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-20240906-06';
Starting restore at 2024-09-06 21:10:19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=771 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-06 21:10:21
|
mount 모드로 변경
|
1
2
3
4
|
RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed
|
database restore 진행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
RMAN> restore database;
Starting restore at 2024-09-06 21:10:45
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=771 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_5u34caf6_1_1_20240906.bk
channel ORA_DISK_1: piece handle=/oradata1/rman/ORACLE19_5u34caf6_1_1_20240906.bk tag=TAG20240906T205606
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 2024-09-06 21:11:20
|
데이터베이스 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-06 21:11:36
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 10 is already on disk as file /ORA19/app/oracle/oradata/ORACLE19/redo01.log
archived log for thread 1 with sequence 11 is already on disk as file /ORA19/app/oracle/oradata/ORACLE19/redo02.log
archived log for thread 1 with sequence 12 is already on disk as file /ORA19/app/oracle/oradata/ORACLE19/redo03.log
archived log file name=/ORA19/app/oracle/arch/oracle19_1_9_1178989648.arc thread=1 sequence=9
archived log file name=/ORA19/app/oracle/oradata/ORACLE19/redo01.log thread=1 sequence=10
archived log file name=/ORA19/app/oracle/oradata/ORACLE19/redo02.log thread=1 sequence=11
archived log file name=/ORA19/app/oracle/oradata/ORACLE19/redo03.log thread=1 sequence=12
media recovery complete, elapsed time: 00:00:06
Finished recover at 2024-09-06 21:11:42
|
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)임
nologging 인덱스를 invaild 일것이라고 생각했는데 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
|
SQL> select /*+ index(t1 t1_ix_nolog) */ * from t1 where col2 = 1;
select /*+ index(t1 t1_ix_nolog) */ * from t1 where col2 = 1
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 100120)
ORA-01110: data file 5: '/oradata1/oracle19/imsits01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
|
nologging 옵션으로 생성한 인덱스는 block corrupted 가 발생했다고 나옴
alert log 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
$ vi /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/alert_oracle19.log
2024-09-06T21:13:02.498295+09:00
Errors in file /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_3368785.trc (incident=1060472):
ORA-01578: ORACLE data block corrupted (file # 5, block # 100120)
ORA-01110: data file 5: '/oradata1/oracle19/imsits01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
Incident details in: /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/incident/incdir_1060472/oracle19_ora_3368785_i1060472.trc
2024-09-06T21:13:03.243021+09:00
*****************************************************************
An internal routine has requested a dump of selected redo.
This usually happens following a specific internal error, when
analysis of the redo logs will help Oracle Support with the
diagnosis.
It is recommended that you retain all the redo logs generated (by
all the instances) during the past 12 hours, in case additional
redo dumps are required to help with the diagnosis.
*****************************************************************
|
alert log 에도 에러가 남음
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 |
--------------------------------------------------------------------------
|
정상적으로 읽어짐
문제 해결을 위해 t1_ix_nolog 인덱스 rebuild
|
1
2
3
|
SQL> alter index t1_ix_nolog rebuild online;
Index altered.
|
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 |
---------------------------------------------------------------------------------------------------
|
rebuild 이후 인덱스가 정상적으로 이용됨
결론 :
백업 이후 인덱스를 nologging 로 생성한 경우 인덱스 생성 이전에 만든 백업본을 이용해 복구를 하게 되면 인덱스의 상태는 vaild 상태로 표시되지만
실제로 해당 인덱스를 읽는 쿼리를 수행하면 block corrupted 에러 메세지가 발생하여 인덱스를 정상적으로 사용할 수 없음
이 경우 인덱스를 rebuild 해줘야 정상적으로 사용가능함
그렇기 때문에 nologging 작업 이후에는 꼭 풀백업을 바로 진행해주어야 db 를 안전하게 운영할 수 있음
이어지는글
오라클 19c 인덱스 nologging 옵션 생성후 복구 테스트2(정상) ( https://positivemh.tistory.com/1156 )
참조 :
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/logging_clause.html#GUID-C4212274-5595-4045-A599-F033772C496E
https://positivemh.tistory.com/1010
'ORACLE > Backup&Recover' 카테고리의 다른 글
| 오라클 19c 인덱스 nologging 옵션 생성후 복구 테스트3(정상) (0) | 2024.10.11 |
|---|---|
| 오라클 19c 인덱스 nologging 옵션 생성후 복구 테스트2(정상) (0) | 2024.09.19 |
| 오라클 19c 스탠다드 에디션에서 엔터프라이즈 에디션으로 datapump 테스트 (0) | 2024.04.13 |
| 오라클 19c 스탠다드 에디션에서 엔터프라이즈 에디션으로 백업복구(cold backup) 테스트 (0) | 2024.04.01 |
| 오라클 19c DUL 이용 Truncate 된 데이터 복구(비공식 툴) (0) | 2024.03.28 |