728x90
2026.03.07 - [📁 Database/Oracle] - [Oracle] Non-CDB에서 Timezone 업그레이드 방법(DSTv43 -> 44)
전체 과정을 해당 포스팅과 비교하면 이해도가 높아짐
Non-CDB에서 Timezone 업그레이드 방법
- OS : Red Hat Enterprise Linux 9.7
- DB : Single DB 19.21(DSTv42)
- Timezone Upgrade : 42 -> 44
현재 DB 엔진이 가지고 있는 최신 Timezone 버전
SELECT DBMS_DST.get_latest_timezone_version
FROM dual;
GET_LATEST_TIMEZONE_VERSION
---------------------------
421) Timezone DSTv44 패치
37537949 Timezone DST 44 패치 다운로드
OS에 맞는 패치 다운로드
-> Oracle 공식 문서
참고) 최신 timezone 파일 가져오기
아래 오라클 문서에서 데이터베이스에 사용할 최신 timezone 파일을 찾을 수 있음
37537949 패치파일 압축 해제
$ unzip -q p37537949_190000_Linux-x86-64.zipDB, Listener Down
$ lsnrctl stop
SQL> shutdown immediate37537949 패치 적용
$ cd 37537949/
$ opatch apply -local(참고)패치 로그
Oracle Interim Patch Installer version 12.2.0.1.49
Copyright (c) 2026, Oracle Corporation. All rights reserved.
Oracle Home : /ORA19/app/oracle/product/19c
Central Inventory : /ORA19/app/oraInventory
from : /ORA19/app/oracle/product/19c/oraInst.loc
OPatch version : 12.2.0.1.49
OUI version : 12.2.0.7.0
Log file location : /ORA19/app/oracle/product/19c/cfgtoollogs/opatch/opatch2026-03-07_23-36-06PM_1.log
Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 37537949
Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Backing up files...
Applying interim patch '37537949' to OH '/ORA19/app/oracle/product/19c'
Patching component oracle.oracore.rsf, 19.0.0.0.0...
Patch 37537949 successfully applied.
Log file location: /ORA19/app/oracle/product/19c/cfgtoollogs/opatch/opatch2026-03-07_23-36-06PM_1.log
OPatch succeeded.
패치 적용 확인
$ opatch lspatches
37537949;RDBMS - DSTV44 UPDATE - TZDATA2025A
35643107;Database Release Update : 19.21.0.0.231017 (35643107)
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
OPatch succeeded.DB, Listener Up
SQL> startup
$ lsnrctl start2) 현재 Timezone 버전 조회
Timezone 파일 확인
SELECT * FROM v$timezone_file;
FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_42.dat 42 0
registry$database 뷰를 이용해서도 조회 가능
SELECT tz_version FROM registry$database;
TZ_VERSION
----------
42PROPERTY_NAME에 따른 PROPERTY_VALUE 기억해두기
COL property_name FORMAT A30
COL property_value FORMAT A20
SELECT property_name, property_value
FROM database_properties
WHERE property_name LIKE 'DST_%'
ORDER BY property_name;
PROPERTY_NAME PROPERTY_VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION 42
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONEDB 엔진이 가지고 있는 가장 최신의 timezone file 버전을 보여줌
37537949 패치를 적용했으므로 DB 엔진은 44 버전을 최신 버전으로 가지고 있음
SELECT DBMS_DST.get_latest_timezone_version
FROM dual;
GET_LATEST_TIMEZONE_VERSION
---------------------------
443) 업그레이드 준비
utltz_countstats.sql: TIMESTAMP WITH TIME ZONE(TSTZ) 데이터가 포함된 모든 테이블의 num_rows에 대한 옵티마이저 통계를 표시
이 스크립트는 데이터베이스 옵티마이저 통계를 기반으로 하므로 통계가 최신 상태여야 함
만약 최신 상태가 아니라면 utltz_countstar.sql 스크립트를 실행해 통계를 최신 상태로 만든 후
utltz_countstats.sql 실행
@?/rdbms/admin/utltz_countstar.sql
Session altered.
.
Estimating amount of TSTZ data using COUNT(*).
This might take some time ...
.
For SYS tables first ...
Note: empty tables are not listed.
Owner.TableName.ColumnName - COUNT(*) of that column
SYS.AQ$_ALERT_QT_S.CREATION_TIME - 4
SYS.AQ$_ALERT_QT_S.DELETION_TIME - 4
SYS.AQ$_ALERT_QT_S.MODIFICATION_TIME - 4
SYS.AQ$_AQ$_MEM_MC_S.CREATION_TIME - 3
SYS.AQ$_AQ$_MEM_MC_S.DELETION_TIME - 3
SYS.AQ$_AQ$_MEM_MC_S.MODIFICATION_TIME - 3
SYS.AQ$_AQ_PROP_TABLE_S.CREATION_TIME - 1
SYS.AQ$_AQ_PROP_TABLE_S.DELETION_TIME - 1
SYS.AQ$_AQ_PROP_TABLE_S.MODIFICATION_TIME - 1
SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.CREATION_TIME - 1
SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.DELETION_TIME - 1
SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.MODIFICATION_TIME - 1
SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.CREATION_TIME - 1
SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.DELETION_TIME - 1
SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.MODIFICATION_TIME - 1
SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.CREATION_TIME - 3
SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.DELETION_TIME - 3
SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.MODIFICATION_TIME - 3
SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.CREATION_TIME - 1
SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.DELETION_TIME - 1
SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.MODIFICATION_TIME - 1
SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.CREATION_TIME - 1
SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.DELETION_TIME - 1
SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.MODIFICATION_TIME - 1
SYS.AQ$_SUBSCRIBER_TABLE.CREATION_TIME - 1
SYS.AQ$_SUBSCRIBER_TABLE.DELETION_TIME - 1
SYS.AQ$_SUBSCRIBER_TABLE.MODIFICATION_TIME - 1
SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.CREATION_TIME - 4
SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.DELETION_TIME - 4
SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.MODIFICATION_TIME - 4
SYS.ATSK$_SCHEDULE_CONTROL.MRCT_TASK_TIME_TZ - 1
SYS.KET$_AUTOTASK_STATUS.ABA_START_TIME - 1
SYS.KET$_AUTOTASK_STATUS.ABA_STATE_TIME - 1
SYS.KET$_AUTOTASK_STATUS.MW_RECORD_TIME - 1
SYS.KET$_AUTOTASK_STATUS.MW_START_TIME - 1
SYS.KET$_AUTOTASK_STATUS.RECONCILE_TIME - 1
SYS.KET$_CLIENT_CONFIG.FIELD_2 - 7
SYS.KET$_CLIENT_CONFIG.LAST_CHANGE - 7
SYS.OPTSTAT_HIST_CONTROL$.SPARE6 - 45
SYS.OPTSTAT_HIST_CONTROL$.SVAL2 - 45
SYS.OPTSTAT_SNAPSHOT$.TIMESTAMP - 641
SYS.OPTSTAT_USER_PREFS$.CHGTIME - 72
SYS.RADM_FPTM$.TSWTZ_COL - 1
SYS.REG$.NTFN_GROUPING_START_TIME - 2
SYS.REG$.REG_TIME - 2
SYS.SCHEDULER$_EVENT_LOG.LOG_DATE - 29
SYS.SCHEDULER$_GLOBAL_ATTRIBUTE.ATTR_TSTAMP - 11
SYS.SCHEDULER$_JOB.END_DATE - 22
SYS.SCHEDULER$_JOB.LAST_ENABLED_TIME - 22
SYS.SCHEDULER$_JOB.LAST_END_DATE - 22
SYS.SCHEDULER$_JOB.LAST_START_DATE - 22
SYS.SCHEDULER$_JOB.NEXT_RUN_DATE - 22
SYS.SCHEDULER$_JOB.START_DATE - 22
SYS.SCHEDULER$_SCHEDULE.END_DATE - 4
SYS.SCHEDULER$_SCHEDULE.REFERENCE_DATE - 4
SYS.SCHEDULER$_WINDOW.ACTUAL_START_DATE - 9
SYS.SCHEDULER$_WINDOW.END_DATE - 9
SYS.SCHEDULER$_WINDOW.LAST_START_DATE - 9
SYS.SCHEDULER$_WINDOW.MANUAL_OPEN_TIME - 9
SYS.SCHEDULER$_WINDOW.NEXT_START_DATE - 9
SYS.SCHEDULER$_WINDOW.START_DATE - 9
SYS.WRI$_ALERT_HISTORY.CREATION_TIME - 7
SYS.WRI$_ALERT_HISTORY.TIME_SUGGESTED - 7
SYS.WRI$_OPTSTAT_AUX_HISTORY.SAVTIME - 18
SYS.WRI$_OPTSTAT_AUX_HISTORY.SPARE6 - 18
SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SAVTIME - 17951
SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SPARE6 - 17951
SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SAVTIME - 28948
SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SPARE6 - 28948
SYS.WRI$_OPTSTAT_IND_HISTORY.SAVTIME - 2238
SYS.WRI$_OPTSTAT_IND_HISTORY.SPARE6 - 2238
SYS.WRI$_OPTSTAT_OPR.END_TIME - 122
SYS.WRI$_OPTSTAT_OPR.SPARE6 - 122
SYS.WRI$_OPTSTAT_OPR.START_TIME - 122
SYS.WRI$_OPTSTAT_OPR_TASKS.END_TIME - 6236
SYS.WRI$_OPTSTAT_OPR_TASKS.SPARE6 - 6236
SYS.WRI$_OPTSTAT_OPR_TASKS.START_TIME - 6236
SYS.WRI$_OPTSTAT_TAB_HISTORY.SAVTIME - 2376
SYS.WRI$_OPTSTAT_TAB_HISTORY.SPARE6 - 2376
SYS.WRM$_DATABASE_INSTANCE.STARTUP_TIME_TZ - 3
SYS.XS$PRIN.END_DATE - 15
SYS.XS$PRIN.START_DATE - 15
Total count * of SYS TSTZ columns is : 123305
There are in total 169 SYS TSTZ columns.
.
For non-SYS tables ...
Note: empty tables are not listed.
Owner.TableName.ColumnName - COUNT(*) of that column
GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.CREATION_TIME - 1
GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.DELETION_TIME - 1
GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.MODIFICATION_TIME - 1
WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.CREATION_TIME - 1
WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.DELETION_TIME - 1
WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.MODIFICATION_TIME - 1
WMSYS.WM$WORKSPACES_TABLE$.CREATETIME - 1
WMSYS.WM$WORKSPACES_TABLE$.LAST_CHANGE - 1
Total count * of non-SYS TSTZ columns is : 8
There are in total 17 non-SYS TSTZ columns.
Total Minutes elapsed : 0
Session altered.
@?/rdbms/admin/utltz_countstats.sql
Session altered.
.
Amount of TSTZ data using num_rows stats info in DBA_TABLES.
.
For SYS tables first ...
Note: empty tables are not listed.
Stat date - Owner.TableName.ColumnName - num_rows
07/03/2026 - SYS.AQ$_ALERT_QT_S.CREATION_TIME - 3
07/03/2026 - SYS.AQ$_ALERT_QT_S.DELETION_TIME - 3
07/03/2026 - SYS.AQ$_ALERT_QT_S.MODIFICATION_TIME - 3
07/03/2026 - SYS.AQ$_AQ$_MEM_MC_S.CREATION_TIME - 3
07/03/2026 - SYS.AQ$_AQ$_MEM_MC_S.DELETION_TIME - 3
07/03/2026 - SYS.AQ$_AQ$_MEM_MC_S.MODIFICATION_TIME - 3
07/03/2026 - SYS.AQ$_AQ_PROP_TABLE_S.CREATION_TIME - 1
07/03/2026 - SYS.AQ$_AQ_PROP_TABLE_S.DELETION_TIME - 1
07/03/2026 - SYS.AQ$_AQ_PROP_TABLE_S.MODIFICATION_TIME - 1
07/03/2026 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.CREATION_TIME - 1
07/03/2026 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.DELETION_TIME - 1
07/03/2026 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.MODIFICATION_TIME - 1
07/03/2026 - SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.CREATION_TIME - 1
07/03/2026 - SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.DELETION_TIME - 1
07/03/2026 - SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.MODIFICATION_TIME - 1
07/03/2026 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.CREATION_TIME - 3
07/03/2026 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.DELETION_TIME - 3
07/03/2026 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.MODIFICATION_TIME - 3
07/03/2026 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.CREATION_TIME - 1
07/03/2026 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.DELETION_TIME - 1
07/03/2026 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.MODIFICATION_TIME - 1
07/03/2026 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.CREATION_TIME - 1
07/03/2026 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.DELETION_TIME - 1
07/03/2026 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.MODIFICATION_TIME - 1
07/03/2026 - SYS.AQ$_SUBSCRIBER_TABLE.CREATION_TIME - 1
07/03/2026 - SYS.AQ$_SUBSCRIBER_TABLE.DELETION_TIME - 1
07/03/2026 - SYS.AQ$_SUBSCRIBER_TABLE.MODIFICATION_TIME - 1
07/03/2026 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.CREATION_TIME - 4
07/03/2026 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.DELETION_TIME - 4
07/03/2026 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.MODIFICATION_TIME - 4
07/03/2026 - SYS.KET$_AUTOTASK_STATUS.ABA_START_TIME - 1
07/03/2026 - SYS.KET$_AUTOTASK_STATUS.ABA_STATE_TIME - 1
07/03/2026 - SYS.KET$_AUTOTASK_STATUS.MW_RECORD_TIME - 1
07/03/2026 - SYS.KET$_AUTOTASK_STATUS.MW_START_TIME - 1
07/03/2026 - SYS.KET$_AUTOTASK_STATUS.RECONCILE_TIME - 1
07/03/2026 - SYS.KET$_CLIENT_CONFIG.FIELD_2 - 7
07/03/2026 - SYS.KET$_CLIENT_CONFIG.LAST_CHANGE - 7
07/03/2026 - SYS.OPTSTAT_HIST_CONTROL$.SPARE6 - 45
07/03/2026 - SYS.OPTSTAT_HIST_CONTROL$.SVAL2 - 45
07/03/2026 - SYS.OPTSTAT_SNAPSHOT$.TIMESTAMP - 579
07/03/2026 - SYS.OPTSTAT_USER_PREFS$.CHGTIME - 72
07/03/2026 - SYS.RADM_FPTM$.TSWTZ_COL - 1
07/03/2026 - SYS.REG$.NTFN_GROUPING_START_TIME - 2
07/03/2026 - SYS.REG$.REG_TIME - 2
07/03/2026 - SYS.SCHEDULER$_EVENT_LOG.LOG_DATE - 29
07/03/2026 - SYS.SCHEDULER$_GLOBAL_ATTRIBUTE.ATTR_TSTAMP - 11
07/03/2026 - SYS.SCHEDULER$_JOB.END_DATE - 21
07/03/2026 - SYS.SCHEDULER$_JOB.LAST_ENABLED_TIME - 21
07/03/2026 - SYS.SCHEDULER$_JOB.LAST_END_DATE - 21
07/03/2026 - SYS.SCHEDULER$_JOB.LAST_START_DATE - 21
07/03/2026 - SYS.SCHEDULER$_JOB.NEXT_RUN_DATE - 21
07/03/2026 - SYS.SCHEDULER$_JOB.START_DATE - 21
07/03/2026 - SYS.SCHEDULER$_SCHEDULE.END_DATE - 4
07/03/2026 - SYS.SCHEDULER$_SCHEDULE.REFERENCE_DATE - 4
07/03/2026 - SYS.SCHEDULER$_WINDOW.ACTUAL_START_DATE - 9
07/03/2026 - SYS.SCHEDULER$_WINDOW.END_DATE - 9
07/03/2026 - SYS.SCHEDULER$_WINDOW.LAST_START_DATE - 9
07/03/2026 - SYS.SCHEDULER$_WINDOW.MANUAL_OPEN_TIME - 9
07/03/2026 - SYS.SCHEDULER$_WINDOW.NEXT_START_DATE - 9
07/03/2026 - SYS.SCHEDULER$_WINDOW.START_DATE - 9
07/03/2026 - SYS.WRI$_ALERT_HISTORY.CREATION_TIME - 1
07/03/2026 - SYS.WRI$_ALERT_HISTORY.TIME_SUGGESTED - 1
07/03/2026 - SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SAVTIME - 17798
07/03/2026 - SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SPARE6 - 17798
07/03/2026 - SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SAVTIME - 27618
07/03/2026 - SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SPARE6 - 27618
07/03/2026 - SYS.WRI$_OPTSTAT_IND_HISTORY.SAVTIME - 2236
07/03/2026 - SYS.WRI$_OPTSTAT_IND_HISTORY.SPARE6 - 2236
07/03/2026 - SYS.WRI$_OPTSTAT_OPR.END_TIME - 93
07/03/2026 - SYS.WRI$_OPTSTAT_OPR.SPARE6 - 93
07/03/2026 - SYS.WRI$_OPTSTAT_OPR.START_TIME - 93
07/03/2026 - SYS.WRI$_OPTSTAT_OPR_TASKS.END_TIME - 6184
07/03/2026 - SYS.WRI$_OPTSTAT_OPR_TASKS.SPARE6 - 6184
07/03/2026 - SYS.WRI$_OPTSTAT_OPR_TASKS.START_TIME - 6184
07/03/2026 - SYS.WRI$_OPTSTAT_TAB_HISTORY.SAVTIME - 2257
07/03/2026 - SYS.WRI$_OPTSTAT_TAB_HISTORY.SPARE6 - 2257
07/03/2026 - SYS.WRM$_DATABASE_INSTANCE.STARTUP_TIME_TZ - 1
07/03/2026 - SYS.XS$PRIN.END_DATE - 15
07/03/2026 - SYS.XS$PRIN.START_DATE - 15
Total numrows of SYS TSTZ columns is : 119732
There are in total 169 SYS TSTZ columns.
.
For non-SYS tables ...
Note: empty tables are not listed.
Stat date - Owner.Tablename.Columnname - num_rows
07/03/2026 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.CREATION_TIME - 1
07/03/2026 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.DELETION_TIME - 1
07/03/2026 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.MODIFICATION_TIME -
1
07/03/2026 - WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.CREATION_TIME - 1
07/03/2026 - WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.DELETION_TIME - 1
07/03/2026 - WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.MODIFICATION_TIME - 1
07/03/2026 - WMSYS.WM$WORKSPACES_TABLE$.CREATETIME - 1
07/03/2026 - WMSYS.WM$WORKSPACES_TABLE$.LAST_CHANGE - 1
Total numrows of non-SYS TSTZ columns is : 8
There are in total 17 non-SYS TSTZ columns.
Total Minutes elapsed : 0
Session altered.
Prepare Begin-End
여기에 Prepare 과정이 Begin부터 End까지 포함
현재 DST 버전과 최신 DST 버전이 찍힘
@?/rdbms/admin/utltz_upg_check.sql
Session altered.
INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues ...
INFO: Database version is 19.0.0.0 .
INFO: Database RDBMS DST version is DSTv42 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv44 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen ...
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run utltz_upg_apply.sql to do the actual RDBMS DST update.
INFO: Note that the utltz_upg_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.
Session altered.
참고) 만약 RAC 환경이라면
RAC 데이터베이스를 Single 인스턴스로 변경해야 함(cluster_database를 FALSE로 설정)
그렇지 않으면 utltz_upg_check.sql을 실행할 때 아래와 같은 경고가 표시
...
WARNING: This RAC database is not started in single instance mode.
WARNING: Set cluster_database = false and start as single instance
WARNING: BEFORE running utltz_upg_apply.sql!
WARNING: This is REQUIRED!
...RAC 데이터베이스 -> Single 인스턴스로 변경 방법(cluster_database를 FALSE로 설정)
1) #2 종료
2) #1 로 접속 후 cluster_database 파라미터 false로 변경
3) #1 재시작
[oracle@blt01~]$ srvctl stop instance -d bltdb -i bltdb2 -f
[oracle@~]$ ~]$ bltdb1
SYS@bltdb1> alter system set cluster_database=false scope=spfile;
System altered.
SYS@bltdb1> shutdown immediate
SYS@bltdb1> startup4) Timezone 업그레이드(Time Zone File 업그레이드) : non-CDB
timezone 업그레이드 과정 중에는 어떤 애플리케이션도 데이터베이스에서 timezone 데이터를 조회하거나 삽입해서는 안 됨
DB 엔진이 가진 가장 최신 Timezone 버전(DBMS_DST.get_latest_timezone_version)으로 업그레이드
utltz_upg_check.sql가 성공적으로 실행되면 utltz_upg_apply.sql을 사용하여 Timezone 업그레이드 실행
이 과정에서 데이터베이스가 두 번 재시작
- 첫 번째 재시작에서는 UPGRADE 모드로 데이터베이스를 재시작하여 DST 업그레이드를 시작하고 SYS 소유의 모든 TSTZ 데이터를 업데이트
- 두 번째 재시작에서는 NORMAL 모드로 데이터베이스를 재시작하여 SYS 소유가 아닌 TSTZ 데이터를 업그레이드
@?/rdbms/admin/utltz_upg_apply.sql
Session altered.
INFO: If an ERROR occurs, the script will EXIT SQL*Plus.
INFO: The database RDBMS DST version will be updated to DSTv44 .
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 2499804176 bytes
Fixed Size 8928272 bytes
Variable Size 536870912 bytes
Database Buffers 1946157056 bytes
Redo Buffers 7847936 bytes
Database mounted.
Database opened.
INFO: Starting the RDBMS DST upgrade.
INFO: Upgrading all SYS owned TSTZ data.
INFO: It might take time before any further output is seen ...
An upgrade window has been successfully started.
INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 2499804176 bytes
Fixed Size 8928272 bytes
Variable Size 536870912 bytes
Database Buffers 1946157056 bytes
Redo Buffers 7847936 bytes
Database mounted.
Database opened.
INFO: Upgrading all non-SYS TSTZ data.
INFO: It might take time before any further output is seen ...
INFO: Do NOT start any application yet that uses TSTZ data!
INFO: Next is a list of all upgraded tables:
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv44 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this SQL*Plus session.
INFO: Do not use it for timezone related selects.
Session altered.
dba_tstz_tables 뷰는 timezone 파일 업그레이드 과정에서 처리되는 테이블과 해당 테이블의 현재 업그레이드 상태를 표시
set lines 300 pages 1000
COLUMN owner FORMAT A30
COLUMN table_name FORMAT A30
SELECT owner,
table_name,
upgrade_in_progress
FROM dba_tstz_tables
ORDER BY 1,2;
OWNER TABLE_NAME UPG
------------------------------ ------------------------------ ---
DBSNMP MGMT_DB_FEATURE_LOG NO
GSMADMIN_INTERNAL AQ$_CHANGE_LOG_QUEUE_TABLE_L NO
GSMADMIN_INTERNAL AQ$_CHANGE_LOG_QUEUE_TABLE_S NO
SYS ACCHK_EVENTS NO
SYS ACCHK_SESSION NO
SYS ACCHK_STATISTICS NO
SYS ACLMVREFSTAT$ NO
SYS ALERT_QT NO
SYS AQ$_ALERT_QT_L NO
SYS AQ$_ALERT_QT_S NO
SYS AQ$_AQ$_MEM_MC_L NO
SYS AQ$_AQ$_MEM_MC_S NO
SYS AQ$_AQ_PROP_TABLE_L NO
SYS AQ$_AQ_PROP_TABLE_S NO
SYS AQ$_DEQUEUE_LOG_PARTITION_MAP NO
SYS AQ$_DURABLE_SUBS NO
SYS AQ$_ORA$PREPLUGIN_BACKUP_QTB_L NO
SYS AQ$_ORA$PREPLUGIN_BACKUP_QTB_S NO
SYS AQ$_PDB_MON_EVENT_QTABLE$_L NO
SYS AQ$_PDB_MON_EVENT_QTABLE$_S NO
SYS AQ$_QUEUE_PARTITION_MAP NO
SYS AQ$_SCHEDULER$_EVENT_QTAB_L NO
SYS AQ$_SCHEDULER$_EVENT_QTAB_S NO
SYS AQ$_SCHEDULER$_REMDB_JOBQTAB_L NO
SYS AQ$_SCHEDULER$_REMDB_JOBQTAB_S NO
SYS AQ$_SCHEDULER_FILEWATCHER_QT_L NO
SYS AQ$_SCHEDULER_FILEWATCHER_QT_S NO
SYS AQ$_SUBSCRIBER_TABLE NO
SYS AQ$_SYS$SERVICE_METRICS_TAB_L NO
SYS AQ$_SYS$SERVICE_METRICS_TAB_S NO
SYS ATSK$_SCHEDULE_CONTROL NO
SYS BLOCKCHAIN_TABLE_CHAIN$ NO
SYS BLOCKCHAIN_TABLE_DROPPED$ NO
SYS FGR$_FILE_GROUPS NO
SYS FGR$_FILE_GROUP_FILES NO
SYS FGR$_FILE_GROUP_VERSIONS NO
SYS IMPDP_STATS NO
SYS KET$_AUTOTASK_STATUS NO
SYS KET$_CLIENT_CONFIG NO
SYS KET$_CLIENT_TASKS NO
SYS LTXID_TRANS NO
SYS NOTIFICATION$ NO
SYS NOTIFICATIONS$ NO
SYS OPTSTAT_HIST_CONTROL$ NO
SYS OPTSTAT_SNAPSHOT$ NO
SYS OPTSTAT_USER_PREFS$ NO
SYS RADM_FPTM$ NO
SYS REG$ NO
SYS SCHEDULER$_CONSTRAINTS_STATS NO
SYS SCHEDULER$_EVENT_LOG NO
SYS SCHEDULER$_EVENT_QTAB NO
SYS SCHEDULER$_FILEWATCHER_HISTORY NO
SYS SCHEDULER$_FILEWATCHER_RESEND NO
SYS SCHEDULER$_FILE_WATCHER NO
SYS SCHEDULER$_GLOBAL_ATTRIBUTE NO
SYS SCHEDULER$_JOB NO
SYS SCHEDULER$_JOB_DESTINATIONS NO
SYS SCHEDULER$_JOB_OUT_ARGS NO
SYS SCHEDULER$_JOB_RUN_DETAILS NO
SYS SCHEDULER$_LIGHTWEIGHT_JOB NO
SYS SCHEDULER$_REMDB_JOBQTAB NO
SYS SCHEDULER$_REMOTE_JOB_STATE NO
SYS SCHEDULER$_SCHEDULE NO
SYS SCHEDULER$_STEP_STATE NO
SYS SCHEDULER$_WINDOW NO
SYS SCHEDULER$_WINDOW_DETAILS NO
SYS SCHEDULER_FILEWATCHER_QT NO
SYS STATS_TARGET$ NO
SYS TAB_STATS$ NO
SYS TSM_DST$ NO
SYS TSM_SRC$ NO
SYS WRI$_ADV_AI_COL_USAGE NO
SYS WRI$_ALERT_HISTORY NO
SYS WRI$_ALERT_OUTSTANDING NO
SYS WRI$_OPTSTAT_AUX_HISTORY NO
SYS WRI$_OPTSTAT_HISTGRM_HISTORY NO
SYS WRI$_OPTSTAT_HISTHEAD_HISTORY NO
SYS WRI$_OPTSTAT_IND_HISTORY NO
SYS WRI$_OPTSTAT_OPR NO
SYS WRI$_OPTSTAT_OPR_TASKS NO
SYS WRI$_OPTSTAT_TAB_HISTORY NO
SYS WRM$_DATABASE_INSTANCE NO
SYS WRM$_PDB_INSTANCE NO
SYS WRM$_PDB_IN_SNAP NO
SYS WRM$_PDB_IN_SNAP_BL NO
SYS WRM$_SNAPSHOT NO
SYS WRMS$_SNAPSHOT NO
SYS WRR$_REPLAY_DIVERGENCE NO
SYS XS$PRIN NO
SYS XS$ROLE_GRANT NO
WMSYS AQ$_WM$EVENT_QUEUE_TABLE_L NO
WMSYS AQ$_WM$EVENT_QUEUE_TABLE_S NO
WMSYS WM$METADATA_MAP_TBL NO
WMSYS WM$MP_PARENT_WORKSPACES_TABLE$ NO
WMSYS WM$WORKSPACES_TABLE$ NO
WMSYS WM$WORKSPACE_SAVEPOINTS_TABLE$ NO
96 rows selected.
5) 현재 Timezone 버전 조회
Timezone 파일 확인
SELECT * FROM v$timezone_file;
FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_44.dat 44 0업그레이드 후 v$timezone_file과 registry$database에 출력되는 버전 값이 다를 경우가 있는데, 이는 정상임
-> Doc ID 1255474.1 참고
sql 파일을 이용해 업그레이드할 경우 registry$database까지 변경되는 모습을 확인할 수 있음
SELECT tz_version FROM registry$database;
TZ_VERSION
----------
44DST_PRIMARY_TT_VERSION이 44로 올라고 업그레이드 상태는 NONE으로 바뀜
COL property_name FORMAT A30
COL property_value FORMAT A20
SELECT property_name, property_value
FROM database_properties
WHERE property_name LIKE 'DST_%'
ORDER BY property_name;
PROPERTY_NAME PROPERTY_VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION 44
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE참고
Upgrade Oracle Database Time Zone (DST) version: Key Considerations
728x90
'📁 Database > Oracle' 카테고리의 다른 글
| [Oracle] Non-CDB에서 Timezone 업그레이드 방법(DSTv43 -> 44) (0) | 2026.03.07 |
|---|---|
| [Oracle] Oracle Instant Client rpm 설치 가이드 (for Linux x86-64) (0) | 2026.02.25 |
| [Oracle] Huge Page 설정 스크립트 링크 (0) | 2026.02.13 |
| [Oracle] 테이블명 변경 시 오브젝트 타입별 상태 영향도 파악(VALID → INVALID / 다시 VALID 되는 조건) (0) | 2025.12.04 |
| [Oracle] DBA_HIST_ACTIVE_SESS_HISTORY (ASH의 과거 데이터) (0) | 2025.11.28 |