프린트 하기

OS 환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.3.0.0

 

방법 : 오라클 19c 체크포인트 발생 상황 테스트(기타 체크포인트)

오라클에서 체크포인트가 발생하는 경우들을 확인해봄
확인하는 방법으로는 ckpt 프로세스와 dbwr 프로세스에 대해 트레이스를 걸어 확인해봄
본문에서는 스레드 체크포인트, 테이블스페이스 및 데이터 파일 체크포인트외 다른 체크포인트만 확인해봄

참고 : 오라클 19c 체크포인트 발생 상황 테스트(스레드 체크포인트) ( https://positivemh.tistory.com/1073 )

참고 : 오라클 19c 체크포인트 발생 상황 테스트(테이블스페이스 체크포인트) ( https://positivemh.tistory.com/1074 )

참고 : 오라클 19c 체크포인트 발생 상황 테스트(기타 체크포인트) ( https://positivemh.tistory.com/1075 )

 

 

체크포인트(Checkpoint)란?
참조 : 오라클 19c 체크포인트 정리 ( https://positivemh.tistory.com/1071 )

 

 

사전 설정
트레이스 설정

백그라운드 프로세스 정보 경로 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> 
set lines 200 pages 1000
col spid for a10
col tracefile for a100
select p.pname, p.spid, s.sid, s.serial#, s.sql_trace, p.tracefile
from v$session s, v$process p
where s.paddr=p.addr
and (p.pname like 'DBW%' or p.pname = 'CKPT');
 
PNAME SPID              SID    SERIAL# SQL_TRAC TRACEFILE
----- ---------- ---------- ---------- -------- ----------------------------------------------------------------------------------------------------
CKPT  2406              768      11017 DISABLED /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ckpt_2406.trc
DBW0  2394             1149      31657 DISABLED /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_dbw0_2394.trc

*spid : os 에서 보이는 pid

 

 

백그라운드 프로세스 트레이스 설정(sid와 serial# 사용)
ckpt 트레이스 설정

1
2
3
SQL> exec dbms_monitor.session_trace_enable(768, 11017, true, true);
 
PL/SQL procedure successfully completed.

 

 

dbwr 트레이스 설정

1
2
3
SQL> exec dbms_monitor.session_trace_enable(1149, 31657, true, true);
 
PL/SQL procedure successfully completed.

 

 

백그라운드 프로세스 정보 경로 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> 
set lines 200 pages 1000
col spid for a10
col tracefile for a100
select p.pname, p.spid, s.sid, s.serial#, s.sql_trace, p.tracefile
from v$session s, v$process p
where s.paddr=p.addr
and (p.pname like 'DBW%' or p.pname = 'CKPT');
 
PNAME SPID              SID    SERIAL# SQL_TRAC TRACEFILE
----- ---------- ---------- ---------- -------- ----------------------------------------------------------------------------------------------------
CKPT  2406              768      11017 ENABLED /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ckpt_2406.trc
DBW0  2394             1149      31657 ENABLED /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_dbw0_2394.trc

sql_trace 컬럼값이 enable로 변경됨 됨

 

 

트레이스 확인

1
2
3
4
5
6
7
8
9
10
11
12
ckpt 트레이스 확인
*** 2024-04-06T22:29:19.303033+09:00
WAIT #0: nam='rdbms ipc message' ela= 3007344 timeout=300 p2=0 p3=0 obj#=-1 tim=173569441174
WAIT #0: nam='control file parallel write' ela= 552 files=2 block#=3 requests=2 obj#=-1 tim=173569441969
WAIT #0: nam='control file sequential read' ela= 6 file#=0 block#=1 blocks=1 obj#=-1 tim=173569442091
WAIT #0: nam='control file sequential read' ela= 3 file#=0 block#=15 blocks=1 obj#=-1 tim=173569442120
WAIT #0: nam='control file sequential read' ela= 2 file#=0 block#=17 blocks=1 obj#=-1 tim=173569442128
WAIT #0: nam='control file sequential read' ela= 3 file#=0 block#=286 blocks=1 obj#=-1 tim=173569442137
 
dbwr 트레이스 확인
*** 2024-04-06T22:29:19.943679+09:00
WAIT #0: nam='rdbms ipc message' ela= 3008003 timeout=300 p2=0 p3=0 obj#=-1 tim=173570081821

매3초 증분 체크포인트가 실행되면서
ckpt 트레이스에 'rdbms ipc message', 'control file parallel write', 'control file sequential read' 이벤트가 발생하고
dbwr 트레이스에 'rdbms ipc message' 이벤트가 발생함

 

 

이렇게 체크포인트 발생시 ckpt 트레이스에 'rdbms ipc message', 'control file parallel write', 'control file sequential read' 이벤트가 발생하는것을 확인할 수 있음
매 3초 증분 체크포인트가 계속 발생하기 때문에 이 체크포인트 발생 직후(1초 이후) 위 테스트 명령을 수행해
트레이스에 다른 이벤트가 발생하는지를 확인해 체크포인트 발생여부를 모니터링함
예를들어 22:32:37에 증분 체크포인트가 발생했다면 다음 증분 체크포인트는 22:32:40에 발생함
이 사이에 테스트 명령을 수행하고 트레이스에 이벤트가 남으면 체크포인트가 발생했다고 판단하겠음
(참고로 이 이벤트들이 체크포인트가 발생을 증명한다는 공식 문서는 찾지못함)

 

 

테스트
체크포인트가 발생하는 경우를 체크포인트 종류별로 구분하여 테스트해봄
1. 스레드 체크포인트 확인
1_1. 일관된 DB 종료 시(shutdown immediate)
1_2. 일관된 DB 종료 시(shutdown normal)
1_3. 일관된 DB 종료 시(shutdown transactional)
1_4. 비일관된 DB 종료 시(shutdown abort)
1_5. DB 기동 시
1_6. alter system checkpoint 구문 실행 시
1_7. alter database begin backup 구문 실행 시
1_8. alter database end backup 구문 실행 시
1_9. log switch 시
결과 참고 : 오라클 19c 체크포인트 발생 상황 테스트(스레드 체크포인트) ( https://positivemh.tistory.com/1073 )

 

 

2. 테이블스페이스 및 데이터 파일 체크포인트 확인
2_1. 테이블스페이스를 읽기 전용으로 변경 시
2_2. 테이블스페이스를 읽기 쓰기로 변경 시
2_3. 테이블스페이스를 오프라인으로 변경 시
2_4. 테이블스페이스를 온라인으로 변경 시
2_5. 테이블스페이스 생성 시
2_6. 테이블스페이스 삭제 시
2_7. 데이터 파일 축소 시
2_8. 데이터 파일 증가 시
2_9. 데이터 파일 추가 시
2_10. 데이터 파일 삭제 시
2_11. alter tablespace begin backup 명령 실행 시
2_12. alter tablespace end backup 명령 실행 시
결과 참고 : 오라클 19c 체크포인트 발생 상황 테스트(테이블스페이스 체크포인트) ( https://positivemh.tistory.com/1074 )

 

 

3. 증분 체크포인트 확인
3_1. 매 3초
결과 참고 : 본문

 

 

4. 기타 체크포인트 확인
4_1. 리두로그 추가 시
4_2. 리두로그 삭제 시
4_3. rman 백업 시
4_4. 컨트롤파일 백업본 생성 시
4_5. 테이블 생성 시
4_6. 테이블 Truncate 시
4_7. 테이블 삭제 시
결과 참고 : 본문

 

 

4. 기타 체크포인트 확인
4_1. 리두로그 추가 시
현재 리두로그 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL>
set lines 200
set pages 1000
col member for a60
select 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;
 
    GROUP# MEMBER                                                       ARC STATUS                   MB
---------- ------------------------------------------------------------ --- ---------------- ----------
         1 /ORA19/app/oracle/oradata/ORACLE19/redo01.log                YES INACTIVE                300
         2 /ORA19/app/oracle/oradata/ORACLE19/redo02.log                YES INACTIVE                300
         3 /ORA19/app/oracle/oradata/ORACLE19/redo03.log                NO  CURRENT                 300

 

 

리두로그 추가

1
2
3
SQL> alter database add logfile group 4 '/ORA19/app/oracle/oradata/ORACLE19/redo04.log' size 300m;
 
Database altered.

 

 

트레이스 확인(10:30:33에 마지막 증분 체크포인트 실행됨)

1
2
3
4
5
ckpt 트레이스
추가 이벤트 미발생
 
dbwr 트레이스
추가 이벤트 미발생

결과 : 체크포인트 미발생함

 

 

4_2. 리두로그 삭제 시
리두로그 삭제

1
2
3
4
SQL> alter database drop logfile group 4;
 
Database altered.
SQL> !rm /ORA19/app/oracle/oradata/ORACLE19/redo04.log

 

 

트레이스 확인(10:32:00에 마지막 증분 체크포인트 실행됨)

1
2
3
4
5
ckpt 트레이스
추가 이벤트 미발생
 
dbwr 트레이스
추가 이벤트 미발생

결과 : 체크포인트 미발생함

 

 

4_3. rman 백업 시
rman 백업

1
2
$ rman target /
RMAN> backup database;

 

 

트레이스 확인(10:35:04에 마지막 증분 체크포인트 실행됨)

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
ckpt 트레이스
*** 2024-04-07T10:35:06.231617+09:00 <<< 증분 체크포인트가 아닌 이벤트 발생
WAIT #0: nam='rdbms ipc message' ela= 1455308 timeout=300 p2=0 p3=0 obj#=-1 tim=303516369760
WAIT #0: nam='rdbms ipc message' ela= 1195 timeout=155 p2=0 p3=0 obj#=-1 tim=303516371122
WAIT #0: nam='rdbms ipc message' ela= 60 timeout=154 p2=0 p3=0 obj#=-1 tim=303516371275
WAIT #0: nam='rdbms ipc message' ela= 32 timeout=154 p2=0 p3=0 obj#=-1 tim=303516371325
WAIT #0: nam='rdbms ipc message' ela= 29 timeout=154 p2=0 p3=0 obj#=-1 tim=303516371369
WAIT #0: nam='rdbms ipc message' ela= 29 timeout=154 p2=0 p3=0 obj#=-1 tim=303516371414
WAIT #0: nam='rdbms ipc message' ela= 29 timeout=154 p2=0 p3=0 obj#=-1 tim=303516371457
WAIT #0: nam='rdbms ipc message' ela= 44 timeout=154 p2=0 p3=0 obj#=-1 tim=303516371516
WAIT #0: nam='rdbms ipc message' ela= 32 timeout=154 p2=0 p3=0 obj#=-1 tim=303516371566
WAIT #0: nam='rdbms ipc message' ela= 29 timeout=154 p2=0 p3=0 obj#=-1 tim=303516371610
WAIT #0: nam='rdbms ipc message' ela= 29 timeout=154 p2=0 p3=0 obj#=-1 tim=303516371653
WAIT #0: nam='rdbms ipc message' ela= 28 timeout=154 p2=0 p3=0 obj#=-1 tim=303516371696
WAIT #0: nam='rdbms ipc message' ela= 305 timeout=154 p2=0 p3=0 obj#=-1 tim=303516372021
WAIT #0: nam='rdbms ipc message' ela= 25215 timeout=154 p2=0 p3=0 obj#=-1 tim=303516397293
WAIT #0: nam='rdbms ipc message' ela= 41 timeout=152 p2=0 p3=0 obj#=-1 tim=303516397412
WAIT #0: nam='rdbms ipc message' ela= 14 timeout=152 p2=0 p3=0 obj#=-1 tim=303516397441
WAIT #0: nam='rdbms ipc message' ela= 14 timeout=152 p2=0 p3=0 obj#=-1 tim=303516397468
WAIT #0: nam='rdbms ipc message' ela= 13 timeout=152 p2=0 p3=0 obj#=-1 tim=303516397493
WAIT #0: nam='rdbms ipc message' ela= 20 timeout=152 p2=0 p3=0 obj#=-1 tim=303516397538
WAIT #0: nam='rdbms ipc message' ela= 13 timeout=152 p2=0 p3=0 obj#=-1 tim=303516397565
WAIT #0: nam='rdbms ipc message' ela= 12 timeout=152 p2=0 p3=0 obj#=-1 tim=303516397590
WAIT #0: nam='rdbms ipc message' ela= 12 timeout=152 p2=0 p3=0 obj#=-1 tim=303516397614
WAIT #0: nam='rdbms ipc message' ela= 13 timeout=152 p2=0 p3=0 obj#=-1 tim=303516397639
 
dbwr 트레이스
추가 이벤트 미발생

결과 : 체크포인트 발생함

 

 

4_4. 컨트롤파일 백업본 생성 시
컨트롤파일 백업본 생성

1
2
3
SQL> alter database backup controlfile to trace as '/home/oracle/con1.sql';
 
Database altered.

 

 

트레이스 확인(10:39:11에 마지막 증분 체크포인트 실행됨)

1
2
3
4
5
ckpt 트레이스
추가 이벤트 미발생
 
dbwr 트레이스
추가 이벤트 미발생

결과 : 체크포인트 미발생함

 

 

4_5. 테이블 생성 시
테이블 생성

1
2
3
SQL> create table test_tbl (col1 number, col2 varchar2(128));
 
Table created.

 

 

트레이스 확인(10:40:14에 마지막 증분 체크포인트 실행됨)

1
2
3
4
5
ckpt 트레이스
추가 이벤트 미발생
 
dbwr 트레이스
추가 이벤트 미발생

결과 : 체크포인트 미발생함

 

 

4_6. 테이블 Truncate 시
테이블 데이터 삽입

1
2
3
4
5
6
7
SQL> insert into test_tbl select object_id, object_name from dba_objects;
 
24040 rows created.
 
SQL> commit;
 
Commit complete.

 

 

테이블 건수 확인

1
2
3
4
5
SQL> select count(*) from test_tbl;
 
  COUNT(*)
----------
     24040

 

 

테이블 Truncate

1
2
3
SQL> truncate table test_tbl;
 
Table truncated.

 

 

트레이스 확인(10:41:26에 마지막 증분 체크포인트 실행됨)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
ckpt 트레이스
*** 2024-04-07T10:41:27.859372+09:00 <<< 증분 체크포인트가 아닌 이벤트 발생
WAIT #0: nam='rdbms ipc message' ela= 1195532 timeout=300 p2=0 p3=0 obj#=-1 tim=303897997517
WAIT #0: nam='rdbms ipc message' ela= 4190 timeout=181 p2=0 p3=0 obj#=-1 tim=303898001835
WAIT #0: nam='rdbms ipc message' ela= 3335 timeout=180 p2=0 p3=0 obj#=-1 tim=303898005219
WAIT #0: nam='rdbms ipc message' ela= 246 timeout=180 p2=0 p3=0 obj#=-1 tim=303898005562
 
 
dbwr 트레이스
*** 2024-04-07T10:41:27.859535+09:00 <<< 증분 체크포인트가 아닌 이벤트 발생
WAIT #0: nam='rdbms ipc message' ela= 1324867 timeout=300 p2=0 p3=0 obj#=-1 tim=303897997690
WAIT #0: nam='db file parallel write' ela= 1169 requests=6 interrupt=0 timeout=0 obj#=-1 tim=303898000969
WAIT #0: nam='db file parallel write' ela= 364 requests=1 interrupt=0 timeout=0 obj#=-1 tim=303898001753
WAIT #0: nam='rdbms ipc message' ela= 1 timeout=167 p2=0 p3=0 obj#=-1 tim=303898001810
WAIT #0: nam='rdbms ipc message' ela= 529 timeout=167 p2=0 p3=0 obj#=-1 tim=303898002346
WAIT #0: nam='rdbms ipc message' ela= 180 timeout=167 p2=0 p3=0 obj#=-1 tim=303898002555
WAIT #0: nam='db file parallel write' ela= 155 requests=1 interrupt=0 timeout=0 obj#=-1 tim=303898002733
WAIT #0: nam='rdbms ipc message' ela= 2726 timeout=167 p2=0 p3=0 obj#=-1 tim=303898005499

결과 : 체크포인트 발생함

 

 

4_7. 테이블 삭제 시
테이블 삭제

1
2
3
SQL> drop table test_tbl purge; 
 
Table dropped.

 

 

트레이스 확인(10:42:53에 마지막 증분 체크포인트 실행됨)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
ckpt 트레이스
*** 2024-04-07T10:42:55.364038+09:00 <<< 증분 체크포인트가 아닌 이벤트 발생
WAIT #0: nam='rdbms ipc message' ela= 1405177 timeout=300 p2=0 p3=0 obj#=-1 tim=303985502179
WAIT #0: nam='rdbms ipc message' ela= 1036 timeout=159 p2=0 p3=0 obj#=-1 tim=303985503366
WAIT #0: nam='rdbms ipc message' ela= 586 timeout=159 p2=0 p3=0 obj#=-1 tim=303985503995
WAIT #0: nam='rdbms ipc message' ela= 7745 timeout=159 p2=0 p3=0 obj#=-1 tim=303985511804
 
dbwr 트레이스
*** 2024-04-07T10:42:55.364232+09:00 <<< 증분 체크포인트가 아닌 이벤트 발생
WAIT #0: nam='rdbms ipc message' ela= 1599040 timeout=300 p2=0 p3=0 obj#=-1 tim=303985502382
WAIT #0: nam='db file parallel write' ela= 673 requests=1 interrupt=0 timeout=0 obj#=-1 tim=303985503171
WAIT #0: nam='rdbms ipc message' ela= 832 timeout=140 p2=0 p3=0 obj#=-1 tim=303985504077
WAIT #0: nam='rdbms ipc message' ela= 2275 timeout=140 p2=0 p3=0 obj#=-1 tim=303985506604
WAIT #0: nam='rdbms ipc message' ela= 2072 timeout=140 p2=0 p3=0 obj#=-1 tim=303985508746
WAIT #0: nam='db file parallel write' ela= 2667 requests=1 interrupt=0 timeout=0 obj#=-1 tim=303985511455

결과 : 체크포인트 발생함
참고로 drop 시 purge를 넣지 않아도 체크포인트 발생함

 

 

결론 :
4. 기타 체크포인트 확인
4_1. 리두로그 추가 시 : 체크포인트 미발생함
4_2. 리두로그 삭제 시 : 체크포인트 미발생함
4_3. rman 백업 시 : 체크포인트 발생함
4_4. 컨트롤파일 백업본 생성 시 : 체크포인트 미발생함
4_5. 테이블 생성 시 : 체크포인트 미발생함
4_6. 테이블 Truncate 시 : 체크포인트 발생함
4_7. 테이블 삭제 시 : 체크포인트 발생함

 

 

참고용 스크립트
트레이스를 매번 손으로 입력해서 걸기 귀찮아서 아래 스크립트를 이용함

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
$ cat bgp_trc.sql
set lines 200 pages 1000
col spid for a10
col tracefile for a100
select p.pname, p.spid, s.sid, s.serial#, s.sql_trace, 'tail -300f '||p.tracefile, 'exec dbms_monitor.session_trace_enable('||s.sid||','||s.serial#||', true, true);'
from v$session s, v$process p
where s.paddr=p.addr
and (p.pname like 'DBW%' or p.pname = 'CKPT');
 
PNAME SPID              SID    SERIAL# SQL_TRAC
----- ---------- ---------- ---------- --------
'TAIL-300F'||P.TRACEFILE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'EXECDBMS_MONITOR.SESSION_TRACE_ENABLE('||S.SID||','||S.SERIAL#||',TRUE,TRUE);'
--------------------------------------------------------------------------------------------------------------------------------------
DBW0  37236            1149      29246 DISABLED
tail -300f /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_dbw0_37236.trc
exec dbms_monitor.session_trace_enable(1149,29246, true, true);
 
CKPT  37240             386      52080 DISABLED
tail -300f /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ckpt_37240.trc
exec dbms_monitor.session_trace_enable(386,52080, true, true);

 

 

참조 : 

1490838.1, 1431133.1, 1526181.1, 270571.1, 568049.1
https://hemantoracledba.blogspot.com/2008/11/tracing-process-tracing-dbwr.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/background-processes.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_MONITOR.html#GUID-C9054D20-3A70-484F-B11B-CC591A10D609
https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/process-architecture.html#GUID-D3174B3E-BCCA-473F-961E-84A36FD5C372
https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/oracle-database-instance.html#GUID-4EC2658A-9519-4188-90C6-AA79F14D
https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/oracle-database-instance.html#GUID-A3FAC5B6-928C-474C-9D4C-E5C5B27544E985D2
https://docs.oracle.com/en//database/oracle/oracle-database/23/dbiad/db_CKPT.html
https://avdeo.com/2015/09/07/how-many-checkpoints-in-oracle-database/
https://docs.oracle.com/en/database/oracle/oracle-database/19/rcmrf/SHUTDOWN.html
http://www.gurubee.net/wiki/pages/26742841
https://positivemh.tistory.com/284
https://positivemh.tistory.com/1073
https://positivemh.tistory.com/1074
https://positivemh.tistory.com/1075

저작자표시

'ORACLE > Admin' 카테고리의 다른 글

오라클 19c 체크포인트 발생 상황 테스트(테이블스페이스 체크포인트)  (0) 2024.04.12
오라클 19c 체크포인트 발생 상황 테스트(스레드 체크포인트)  (0) 2024.04.10
오라클 19c 체크포인트 정리  (0) 2024.04.07
오라클 클라우드(OCI) 무료 계정 생성 가이드  (2) 2024.04.06
오라클 19c DML 시 REDO 발생량 확인(nologging, append, parallel 등)  (4) 2024.04.01

티스토리툴바