OS: linux 7.5 DBMS: oracle 19.3 |
[목차여기]
- 오라클 alert.log의 시간대는 어떤설정을 기준으로 적용되는지에 대한 테스트 - OS시간대 기준으로 alert.log 시간이 적용된다 : 정확히는 DB 기동하는 세션의 시간대 |
참고: oracle 오브젝트 생성시간은 어떤 시간대(time zone)
현재설정(UTC)
현재설정은 UTC(sysdate 확인)로 되어있고 alert.log도 동일한 시간대로 찍히는것으로 확인됩니다
SQL> SELECT dbtimezone, sessiontimezone, sysdate, current_date FROM dual ;
DBTIME SESSIONTIMEZONE SYSDATE CURRENT_DATE
------ --------------------------------------------------------------------------- ------------------- -------------------
+09:00 +00:00 2024/02/18 09:07:51 2024/02/18 09:07:51
SQL> alter system switch logfile;
System altered.
SQL>
-- alert.log
2024-02-18T09:08:09.097034+00:00
Thread 1 advanced to log sequence 5130 (LGWR switch)
Current log# 6 seq# 5130 mem# 0: /ORA19/app/oracle/oradata/ORA19C/redo06.log
2024-02-18T09:08:09.338826+00:00
ARC0 (PID:3906): Archived Log entry 5436 added for T-1.S-5129 ID 0x4368ce8e LAD:1
환경변수 변경(UTC -> KST)
테스트를 위한 DB 종료
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
세션 환경변수 TZ='Asia/Seoul'를 설정하여 KST로 변경합니다
OS TZ은 그대로 UTC 인것을 확인 할 수 있습니다
-- 환경변수 설정
[oracle@svr1 ~]$ export TZ='Asia/Seoul'
[oracle@svr1 ~]$ env | grep TZ
TZ=Asia/Seoul
-- 세션 TZ는 KST
[oracle@svr1 ~]$ date
Sun Feb 18 18:10:52 KST 2024
-- OS time zone은 UTC
[oracle@svr1 ~]$ timedatectl
Warning: Ignoring the TZ variable. Reading the system's time zone setting only.
Local time: Sun 2024-02-18 09:10:57 UTC
Universal time: Sun 2024-02-18 09:10:57 UTC
RTC time: Sun 2023-11-12 06:59:16
Time zone: UTC (UTC, +0000)
NTP enabled: no
NTP synchronized: no
RTC in local TZ: no
DST active: n/a
[oracle@svr1 ~]$
해당 세션으로 DB기동 후 log switch 및 alert.log 확인 합니다
sysdate는 KST로 변경되었고 alert.log 시간대도 KST로 적용되었습니다
SQL> startup
ORACLE instance started.
Total System Global Area 1073737800 bytes
Fixed Size 8904776 bytes
Variable Size 880803840 bytes
Database Buffers 176160768 bytes
Redo Buffers 7868416 bytes
Database mounted.
Database opened.
SQL> alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';
SELECT dbtimezone, sessiontimezone, sysdate, current_date FROM dual ;
Session altered.
SQL> SQL>
DBTIME SESSIONTIMEZONE SYSDATE CURRENT_DATE
------ --------------------------------------------------------------------------- ------------------- -------------------
+09:00 +09:00 2024/02/18 18:13:09 2024/02/18 18:13:09
SQL> alter system switch logfile;
System altered.
-- alert.log 시간대가 KST로 변경됨
2024-02-18T18:12:50.023349+09:00
CJQ0 started with pid=52, OS id=17920
Completed: ALTER DATABASE OPEN
2024-02-18T18:13:34.304814+09:00
Thread 1 advanced to log sequence 5131 (LGWR switch)
Current log# 4 seq# 5131 mem# 0: /ORA19/app/oracle/oradata/ORA19C/redo04.log
2024-02-18T18:13:34.342728+09:00
ARC1 (PID:17900): Archived Log entry 5437 added for T-1.S-5130 ID 0x4368ce8e LAD:1반응형
신규세션 접속
기본설정인 신규세션으로 DB접속 시 sysdate가 UTC기준으로 보이는게 확인됩니다
[oracle@svr1 ~]$ ss
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Feb 18 09:15:25 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';
SELECT dbtimezone, sessiontimezone, sysdate, current_date FROM dual ;
Session altered.
SQL> SQL>
DBTIME SESSIONTIMEZONE SYSDATE CURRENT_DATE
------ --------------------------------------------------------------------------- ------------------- -------------------
+09:00 +00:00 2024/02/18 09:15:30 2024/02/18 09:15:30
환경변수 원복(KST -> UTC)
DB 종료 후 TZ환경변수 제거 합니다
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@svr1 ~]$
-- 현재 TZ는 KST
[oracle@svr1 ~]$ env | grep TZ
TZ=Asia/Seoul
[oracle@svr1 ~]$ date
Sun Feb 18 18:31:09 KST 2024
-- OS TZ는 UTC
[oracle@svr1 ~]$ timedatectl
Warning: Ignoring the TZ variable. Reading the system's time zone setting only.
Local time: Sun 2024-02-18 09:31:19 UTC
Universal time: Sun 2024-02-18 09:31:19 UTC
RTC time: Sun 2023-11-12 07:19:37
Time zone: UTC (UTC, +0000)
NTP enabled: no
NTP synchronized: no
RTC in local TZ: no
DST active: n/a
TZ 환경변수 설정 제거
-- TZ 변수 설정 제거
[oracle@svr1 ~]$ unset TZ
[oracle@svr1 ~]$ env | grep TZ
-- UTC로 변경됨
[oracle@svr1 ~]$ date
Sun Feb 18 09:31:29 UTC 2024
[oracle@svr1 ~]$ timedatectl
Local time: Sun 2024-02-18 09:31:33 UTC
Universal time: Sun 2024-02-18 09:31:33 UTC
RTC time: Sun 2023-11-12 07:19:52
Time zone: UTC (UTC, +0000)
NTP enabled: no
NTP synchronized: no
RTC in local TZ: no
DST active: n/a
[oracle@svr1 ~]$
DB 기동 후 alert.log 시간 확인
계정의 현재 시간대인 UTC를 기준으로 시간대가 적용되었습니다
SQL> startup
ORACLE instance started.
Total System Global Area 1073737800 bytes
Fixed Size 8904776 bytes
Variable Size 880803840 bytes
Database Buffers 176160768 bytes
Redo Buffers 7868416 bytes
Database mounted.
Database opened.
SQL>
SQL> alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';
SELECT dbtimezone, sessiontimezone, sysdate, current_date FROM dual ;
Session altered.
SQL> SQL>
DBTIME SESSIONTIMEZONE SYSDATE CURRENT_DATE
------ --------------------------------------------------------------------------- ------------------- -------------------
+09:00 +00:00 2024/02/18 09:32:20 2024/02/18 09:32:20
SQL> alter system switch logfile;
System altered.
SQL>
-- alert.log
2024-02-18T09:31:49.910966+00:00
CJQ0 started with pid=51, OS id=22741
Completed: ALTER DATABASE OPEN
2024-02-18T09:32:26.291804+00:00
Thread 1 advanced to log sequence 5132 (LGWR switch)
Current log# 5 seq# 5132 mem# 0: /ORA19/app/oracle/oradata/ORA19C/redo05.log
2024-02-18T09:32:26.350976+00:00
'DBMS > ORACLE' 카테고리의 다른 글
| [oracle] 스케줄러(dbms_scheduler) 실행 시간대(time zone) (0) | 2024.02.22 |
|---|---|
| [oracle] 리스너 timezone 설정 (0) | 2024.02.20 |
| [oracle] 오브젝트 생성시간은 어떤 시간대(time zone) (0) | 2024.02.17 |
| [oracle] 테이블 LOB Object 이름 지정 (0) | 2024.02.08 |
| [oracle] optimizer_adaptive_plans 기능 설명 및 권장값 (0) | 2023.12.22 |
댓글