OS 환경 : Oracle Linux 7.7 (64bit)
DB 환경 : Oracle Database 12.2.0.1, 19.27.0.0
방법 : 오라클 19c 업그레이드 시 job의 nls_env 유의사항
오라클 11g나 12c에서 19c로 업그레이드 할때 rman을 이용해 그대로 신규서버로 가서 업그레이드를 할수도 있고
동일한 서버에서 업그레이드를 할수도 있음
하지만 이때 job이 있는 경우 job이 19c로 가면서 scheduler로 복사됨(이관됨)
이때 문제가 발생하는데 job이나 scheduler는 등록하는 세션의 nls와 동일하게 등록됨
만약 형변환 함수를 제대로 작성해주지 않은 프로시저를 실행하는 job이 존재하는 경우
이 job이 기존에 등록될때는 NLS_LANG=KOREAN_KOREA.KO16MSWIN949에 NLS_DATE_FORMAT='RR/MM/DD' 환경에서 등록했을때는 잘 실행되다가
업그레이드 이후에는 자동으로 AMERICAN_AMERICA.KO16MSWIN949에 NLS_DATE_FORMAT='DD-MON-RR'로 변경되어 job 실행에 문제가 생길 수 있음
(업그레이드 명령을 수행하는 리눅스 shell 세션은 기본이 AMERICAN_AMERICA에 'DD-MON-RR'이기때문)
본문에서는 해당 시나리오를 직접 수행하여 문제를 확인해봄
테스트
오렌지 등 db 툴로 접속하여 현재 세션레벨 nls 정보 확인(툴은 KOREAN_KOREA, 'RR/MM/DD' 환경임)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
SQL> select * from nls_session_parameters;
PARAMETER VALUE
---------------------------------------- ----------------------------------------
NLS_LANGUAGE KOREAN <<---
NLS_TERRITORY KOREA <<---
NLS_CURRENCY ?
NLS_ISO_CURRENCY KOREA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT RR/MM/DD <<---
NLS_DATE_LANGUAGE KOREAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT RR/MM/DD HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT RR/MM/DD HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY ?
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
|
현재 세션의 NLS_DATE_FORMAT이 'RR/MM/DD'임
현재 db레벨 nls 정보 확인(sqlplus)
|
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
|
SQL>
set lines 200 pages 1000
col parameter for a40
col value for a40
select * from nls_database_parameters;
PARAMETER VALUE
---------------------------------------- ----------------------------------------
NLS_RDBMS_VERSION 12.2.0.1.0
NLS_NCHAR_CONV_EXCP FALSE
NLS_LENGTH_SEMANTICS BYTE
NLS_COMP BINARY
NLS_DUAL_CURRENCY ?
NLS_TIMESTAMP_TZ_FORMAT RR/MM/DD HH24:MI:SSXFF TZR
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_FORMAT RR/MM/DD HH24:MI:SSXFF
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_SORT BINARY
NLS_DATE_LANGUAGE KOREAN
NLS_DATE_FORMAT RR/MM/DD <<---
NLS_CALENDAR GREGORIAN
NLS_NUMERIC_CHARACTERS .,
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_CHARACTERSET KO16MSWIN949
NLS_ISO_CURRENCY KOREA
NLS_CURRENCY ?
NLS_TERRITORY KOREA <<---
NLS_LANGUAGE KOREAN <<---
|
현재 db의 NLS_DATE_FORMAT이 'RR/MM/DD'임
nls 파라미터 확인(sqlplus)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
SQL> show parameter nls
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_calendar string GREGORIAN
nls_comp string BINARY
nls_currency string $
nls_date_format string DD-MON-RR <<---
nls_date_language string AMERICAN
nls_dual_currency string $
nls_iso_currency string AMERICA
nls_language string AMERICAN <<---
nls_length_semantics string BYTE
nls_nchar_conv_excp string FALSE
nls_numeric_characters string .,
nls_sort string BINARY
nls_territory string AMERICA <<---
nls_time_format string HH.MI.SSXFF AM
nls_time_tz_format string HH.MI.SSXFF AM TZR
nls_timestamp_format string DD-MON-RR HH.MI.SSXFF AM
nls_timestamp_tz_format string DD-MON-RR HH.MI.SSXFF AM TZR
|
sqlplus에서는 AMERICAN_AMERICA에 'DD-MON-RR'로 설정되어 있음
.bash_profile에서 NLS_LANG를 AMERICAN_AMERICA.KO16MSWIN949로 설정했기 때문에 이렇게 보임
현재 shell 세션의 NLS 확인
|
1
2
3
4
|
$ echo $NLS_DATE_FORMAT
(결과없음)
$ echo $NLS_LANG
AMERICAN_AMERICA.KO16MSWIN949
|
.bash_profile에는 NLS_LANG가 AMERICAN_AMERICA.KO16MSWIN949로 등록되어 있음
정리하면 현재 db의 NLS_DATE_FORMAT은 'RR/MM/DD'이고
sqlplus로 접속했을때 NLS_DATE_FORMAT은 'DD-MON-RR'이고
오렌지 등 툴로 접속했을때 NLS_DATE_FORMAT은 'RR/MM/DD'임
12c에서 샘플 테이블 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
drop table test1 purge;
drop table result_log_sqlplus purge;
drop table result_log_tool purge;
create table test1(col1 number, col_dt date);
insert into test1 values (1, to_date('2025/10/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss'));
insert into test1 values (2, to_date('2025/10/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss'));
insert into test1 values (3, to_date('2025/10/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss'));
insert into test1 values (4, to_date('2025/10/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss'));
commit;
create table result_log_sqlplus(result number, dt date);
create table result_log_tool(result number, dt date);
|
동일한 동작을 하는 프로시저 2개 생성
|
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
|
SQL>
CREATE OR REPLACE PROCEDURE get_rows_from_test_sqlplus_job AS
v_str VARCHAR2(20) := '2025/12/05';
v_col_dt DATE;
v_bound DATE;
v_cnt PLS_INTEGER;
BEGIN
v_col_dt := v_str;
v_bound := ADD_MONTHS(TRUNC(v_col_dt, 'MM'), -1);
select count(*) into v_cnt
from test1
where col_dt <= v_bound;
insert into result_log_sqlplus (result, dt)
select v_cnt, sysdate from dual;
commit;
END;
/
CREATE OR REPLACE PROCEDURE get_rows_from_test_tool_job AS
v_str VARCHAR2(20) := '2025/12/05';
v_col_dt DATE;
v_bound DATE;
v_cnt PLS_INTEGER;
BEGIN
v_col_dt := v_str;
v_bound := ADD_MONTHS(TRUNC(v_col_dt, 'MM'), -1);
select count(*) into v_cnt
from test1
where col_dt <= v_bound;
insert into result_log_tool (result, dt)
select v_cnt, sysdate from dual;
commit;
END;
/
|
위 프로시저는 v_str VARCHAR2(20) := '25/12/05'; 부분이 v_col_dt로 넘어가고
v_col_dt가 v_bound := ADD_MONTHS(TRUNC(v_col_dt, 'MM'), -1);로 들어가서 date 포맷이 맞지 않는 경우 에러가 발생함
현재 '2025/12/05' 형식으로 데이터가 들어가기 때문에 nls_date_format이 'RR/MM/DD' 일때만 정상 동작함
각각 프로시저를 실행하는 job을 2개 등록
sqlplus에서 job 등록
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL>
DECLARE
jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT(
job => jobno,
what => 'get_rows_from_test_sqlplus_job;', -- 프로시저 이름
next_date => SYSDATE, -- 즉시 시작
interval => 'SYSDATE + 1/1440' -- 1분 주기
);
COMMIT;
END;
/
PL/SQL procedure successfully completed.
|
오렌지 등 db 툴에서 job 등록
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
DECLARE
jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT(
job => jobno,
what => 'get_rows_from_test_tool_job;', -- 프로시저 이름
next_date => SYSDATE, -- 즉시 시작
interval => 'SYSDATE + 1/1440' -- 1분 주기
);
COMMIT;
END;
|
등록된 job 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
SQL>
set lines 200 pages 1000
col interval for a41
col what for a31
select job, what, to_char(last_date, 'yyyy/mm/dd hh24:mi:ss') last_date, to_char(next_date, 'yyyy/mm/dd hh24:mi:ss') next_date, broken, interval, nls_env
from dba_jobs
where what like 'get_rows_from_test_%'
order by job;
JOB WHAT LAST_DATE NEXT_DATE B INTERVAL
---------- ------------------------------- ------------------- ------------------- - -----------------------------------------
NLS_ENV
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
41 get_rows_from_test_sqlplus_job; 2025/10/04 16:39:42 N SYSDATE + 1/1440
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,'
NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'
42 get_rows_from_test_tool_job; 2025/10/04 16:38:47 2025/10/04 16:39:47 N SYSDATE + 1/1440
NLS_LANGUAGE='KOREAN' NLS_TERRITORY='KOREA' NLS_CURRENCY='?' NLS_ISO_CURRENCY='KOREA' NLS_NUMERIC_CHARACTERS='.,'
NLS_DATE_FORMAT='RR/MM/DD' NLS_DATE_LANGUAGE='KOREAN' NLS_SORT='BINARY'
|
sqlplus에서 등록한 잡은 NLS_LANGUAGE와 NLS_TERRITORY가 AMERICAN_AMERICA에 'DD-MON-RR'이고
tool에서 등록한 잡은 KOREAN_KOREA에 'RR/MM/DD'로 등록됨
그리고 sqlplus에서 등록한 잡은 last_date가 안찍힘(실행안되는중)
참고로 12c에선 job을 등록해도 dba_scheduler_jobs에는 등록안됨
|
1
2
3
4
5
6
|
SQL>
select job_name
from dba_scheduler_jobs
where job_name like 'DBMS_JOB$';
no rows selected
|
참고용
|
1
2
3
4
5
|
#수동 실행
SQL> exec dbms_job.run(42);
#삭제
SQL> exec dbms_job.remove(42);
|
job 실행 기록 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL> select result, to_char(dt, 'yyyy/mm/dd hh24:mi:ss') dt from result_log_sqlplus;
no rows selected
SQL> select result, to_char(dt, 'yyyy/mm/dd hh24:mi:ss') dt from result_log_tool;
RESULT DT
---------- -------------------
4 2025/10/04 16:38:47
4 2025/10/04 16:39:53
|
sqlplus에서 등록한 job은 제대로 수행이 안되고 있고 툴에서 등록한 job만 수행이 잘됨
alert log 확인시 sqlplus에서 등록한 job은 에러가 발생중임
|
1
2
3
4
5
6
7
8
|
$ tail -300f /app/oracle/diag/rdbms/ora12fs/ORA12FS/trace/alert_ORA12FS.log
2025-10-04T16:39:53.382570+09:00 Errors in file /app/oracle/diag/rdbms/ora12fs/ORA12FS/trace/ORA12FS_j000_27659.trc:
ORA-12012: error on auto execute of job 41
ORA-01861: literal does not match format string
ORA-06512: at "SYS.GET_ROWS_FROM_TEST_SQLPLUS_JOB", line 7
ORA-06512: at line 1
|
ORA-01861: literal does not match format string라고 날짜형식이 안맞다고 나옴
일단 에러는 놔둔 상태로 진행함
db 업그레이드
19c 엔진 설치
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
$ mkdir -p /app/oracle/product/19c
$ cd /app/oracle/product/19c
$ unzip -q /app/oracle/media/19c/LINUX.X64_193000_db_home.zip
$ mv OPatch/ OPatchold
$ unzip -q /app/oracle/media/19c/p6880880_190000_Linux-x86-64.zip
$ ./runInstaller \
-silent -waitforcompletion \
-ignorePrereqFailure \
oracle.install.option=INSTALL_DB_SWONLY \
UNIX_GROUP_NAME=dba \
INVENTORY_LOCATION=/app/oraInventory \
ORACLE_HOME=/app/oracle/product/19c \
ORACLE_BASE=/app/oracle \
oracle.install.db.InstallEdition=EE \
oracle.install.db.DBA_GROUP=dba \
oracle.install.db.OSDBA_GROUP=dba \
oracle.install.db.OSOPER_GROUP=dba \
oracle.install.db.OSBACKUPDBA_GROUP=dba \
oracle.install.db.OSDGDBA_GROUP=dba \
oracle.install.db.OSKMDBA_GROUP=dba \
oracle.install.db.OSRACDBA_GROUP=dba
# su - root
# /prod/oracle/db/19.0.0.0/root.sh
|
업그레이드 사전 스크립트 수행
|
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
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
|
$ /app/oracle/product/12c/jdk/bin/java -jar /app/oracle/product/19c/rdbms/admin/preupgrade.jar TEXT TERMINAL
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2025-10-04T16:16:09
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: ORA12FS
Container Name: ORA12FS
Container ID: 0
Version: 12.2.0.1.0
DB Patch Level: No Patch Bundle applied
Compatible: 12.2.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 26
Database log mode: NOARCHIVELOG
Readonly: FALSE
Edition: EE
Oracle Component Upgrade Action Current Status
---------------- -------------- --------------
Oracle Server [to be upgraded] VALID
Oracle Workspace Manager [to be upgraded] VALID
Oracle XML Database [to be upgraded] VALID
==============
BEFORE UPGRADE
==============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
1. Run 12.2.0.1.0 $ORACLE_HOME/rdbms/admin/utlrp.sql to recompile invalid
objects. You can view the individual invalid objects with
SET SERVEROUTPUT ON;
EXECUTE DBMS_PREUP.INVALID_OBJECTS;
3 objects are INVALID.
There should be no INVALID objects in SYS/SYSTEM or user schemas before
database upgrade.
2. (AUTOFIXUP) Gather stale data dictionary statistics prior to database
upgrade in off-peak time using:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Dictionary statistics do not exist or are stale (not up-to-date).
Dictionary statistics help the Oracle optimizer find efficient SQL
execution plans and are essential for proper upgrade timing. Oracle
recommends gathering dictionary statistics in the last 24 hours before
database upgrade.
For information on managing optimizer statistics, refer to the 12.2.0.1
Oracle Database SQL Tuning Guide.
INFORMATION ONLY
================
3. To help you keep track of your tablespace allocations, the following
AUTOEXTEND tablespaces are expected to successfully EXTEND during the
upgrade process.
Min Size
Tablespace Size For Upgrade
---------- ---------- -----------
TEMP 20 MB 150 MB
Minimum tablespace sizes for upgrade are estimates.
4. Check the Oracle Backup and Recovery User's Guide for information on how
to manage an RMAN recovery catalog schema.
If you are using a version of the recovery catalog schema that is older
than that required by the RMAN client version, then you must upgrade the
catalog schema.
It is good practice to have the catalog schema the same or higher version
than the RMAN client version you are using.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database ORA12FS
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/app/oracle/cfgtoollogs/ORA12FS/preupgrade/preupgrade_fixups.sql
=============
AFTER UPGRADE
=============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
5. Upgrade the database time zone file using the DBMS_DST package.
The database is using time zone file version 26 and the target 19 release
ships with time zone file version 32.
Oracle recommends upgrading to the desired (latest) version of the time
zone file. For more information, refer to "Upgrading the Time Zone File
and Timestamp with Time Zone Data" in the 19 Oracle Database
Globalization Support Guide.
6. To identify directory objects with symbolic links in the path name, run
$ORACLE_HOME/rdbms/admin/utldirsymlink.sql AS SYSDBA after upgrade.
Recreate any directory objects listed, using path names that contain no
symbolic links.
Some directory object path names may currently contain symbolic links.
Starting in Release 18c, symbolic links are not allowed in directory
object path names used with BFILE data types, the UTL_FILE package, or
external tables.
7. (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
command:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Oracle recommends gathering dictionary statistics after upgrade.
Dictionary statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. After a database
upgrade, statistics need to be re-gathered as there can now be tables
that have significantly changed during the upgrade or new tables that do
not have statistics gathered yet.
8. Gather statistics on fixed objects after the upgrade and when there is a
representative workload on the system using the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
This recommendation is given for all preupgrade runs.
Fixed object statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. Those
statistics are specific to the Oracle Database release that generates
them, and can be stale upon database upgrade.
For information on managing optimizer statistics, refer to the 12.2.0.1
Oracle Database SQL Tuning Guide.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database ORA12FS
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/app/oracle/cfgtoollogs/ORA12FS/preupgrade/postupgrade_fixups.sql
==================
PREUPGRADE SUMMARY
==================
/app/oracle/cfgtoollogs/ORA12FS/preupgrade/preupgrade.log
/app/oracle/cfgtoollogs/ORA12FS/preupgrade/preupgrade_fixups.sql
/app/oracle/cfgtoollogs/ORA12FS/preupgrade/postupgrade_fixups.sql
Execute fixup scripts as indicated below:
Before upgrade:
Log into the database and execute the preupgrade fixups
@/app/oracle/cfgtoollogs/ORA12FS/preupgrade/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups
@/app/oracle/cfgtoollogs/ORA12FS/preupgrade/postupgrade_fixups.sql
Preupgrade complete: 2025-10-04T16:16:09
|
사전 검사에서 나온 부분 조치
|
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
|
SQL> select * from v$tempfile;
FILE# CREATION_CHANGE# CREATION_TIM TS# RFILE# STATUS
---------- ---------------- ------------ ---------- ---------- -------
ENABLED BYTES BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ---------- ------------ ----------
NAME
--------------------------------------------------------------------------------
CON_ID
----------
1 3539 21-SEP-25 3 1 ONLINE
READ WRITE 20971520 2560 20971520 8192
/app/oracle/oradata/ORA12FS/temp01.dbf
0
SQL> alter database tempfile 1 resize 300m;
Database altered.
SQL> @/app/oracle/cfgtoollogs/ORA12FS/preupgrade/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2025-10-04 16:16:05
For Source Database: ORA12FS
Source Database Version: 12.2.0.1.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
1. invalid_objects_exist NO Manual fixup recommended.
2. dictionary_stats YES None.
3. tablespaces_info YES None.
4. rman_recovery_version NO Informational only.
Further action is optional.
The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database is not ready
for upgrade. To resolve the outstanding issues, start by reviewing
the preupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.
PL/SQL procedure successfully completed.
|
11g db 종료
|
1
|
SQL> shutdown immediate
|
pfile 복제 및 ORACLE_HOME 19c로 설정
|
1
2
3
4
5
6
7
8
|
SQL> create pfile from spfile;
$ cp /app/oracle/product/12c/dbs/initORA12FS.ora /app/oracle/product/19c/dbs/
$ cp /app/oracle/product/12c/dbs/orapw* /app/oracle/product/19c/dbs/
$ cp /app/oracle/product/12c/network/admin/*.ora /app/oracle/product/19c/network/admin/
$ cd ~
$ vi .bash_profile
export ORACLE_HOME=/app/oracle/product/19c
$ . ./.bash_profile
|
19c db 기동
|
1
2
3
4
5
6
7
8
9
10
11
|
$ sqlplus / as sysdba
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 1543500144 bytes
Fixed Size 8896880 bytes
Variable Size 385875968 bytes
Database Buffers 1140850688 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
|
db 업그레이드 수행
|
1
2
|
$ cd $ORACLE_HOME/bin
$ nohup ./dbupgrade > dbupgrade.log &
|
약 20분 후 업그레이드 완료됨
db 기동후 스크립트 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL> startup
ORACLE instance started.
Total System Global Area 2516581440 bytes
Fixed Size 8928320 bytes
Variable Size 536870912 bytes
Database Buffers 1962934272 bytes
Redo Buffers 7847936 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/utlusts.sql
SQL> @?/rdbms/admin/utlrp.sql
SQL> @/app/oracle/cfgtoollogs/ORA12FS/preupgrade/postupgrade_fixups.sql
|
등록된 job 확인(dba_jobs)
|
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
|
SQL>
set lines 200 pages 1000
col interval for a40
col what for a31
select job, what, to_char(last_date, 'yyyy/mm/dd hh24:mi:ss') last_date, to_char(next_date, 'yyyy/mm/dd hh24:mi:ss') next_date, broken, interval, nls_env
from dba_jobs
where what like 'get_rows_from_test_%'
order by job;
JOB WHAT LAST_DATE NEXT_DATE B INTERVAL
---------- ------------------------------- ------------------- ------------------- - ----------------------------------------
NLS_ENV
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
41 get_rows_from_test_sqlplus_job; 2025/10/04 17:00:07 2025/10/04 17:01:07 N SYSDATE + 1/1440
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_CALENDAR='GREGORIAN'
NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN ' NLS_SORT='BINARY_CI' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD-MON-RR HH.MI.SSXFF AM' NLS_TIME_TZ_FORMAT='HH.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH.MI.SSXFF AM TZR' NLS_DUAL_CURRENCY='$' NLS_COMP='BINARY' NLS_LENGTH_SEMANTICS='BYTE' NLS_NCHAR_CONV_EXCP='FALSE' 42 get_rows_from_test_tool_job; 2025/10/04 17:00:07 2025/10/04 17:01:07 N SYSDATE + 1/1440
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_CALENDAR='GREGORIAN'
NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY_CI' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD-MON-RR HH.MI.SSXFF AM' NLS_TIME_TZ_FORMAT='HH.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH.MI.SSXFF AM TZR' NLS_DUAL_CURRENCY='$' NLS_COMP='BINARY' NLS_LENGTH_SEMANTICS='BYTE' NLS_NCHAR_CONV_EXCP='FALSE' #참고용 기존 job 확인시
JOB WHAT LAST_DATE NEXT_DATE B INTERVAL
---------- ------------------------------- ------------------- ------------------- - -----------------------------------------
NLS_ENV
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
41 get_rows_from_test_sqlplus_job; 2025/10/04 16:39:42 N SYSDATE + 1/1440
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,'
NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'
42 get_rows_from_test_tool_job; 2025/10/04 16:38:47 2025/10/04 16:39:47 N SYSDATE + 1/1440
NLS_LANGUAGE='KOREAN' NLS_TERRITORY='KOREA' NLS_CURRENCY='?' NLS_ISO_CURRENCY='KOREA' NLS_NUMERIC_CHARACTERS='.,'
NLS_DATE_FORMAT='RR/MM/DD' NLS_DATE_LANGUAGE='KOREAN' NLS_SORT='BINARY'
|
기존에는 등록되었던 job에 비해 더 많은 nls_env가 생김(NLS_TIME_FORMAT, NLS_COMP 등등)
그리고 tool에서 등록한 job은 KOREAN_KOREA, 'RR/MM/DD'로 등록했었지만 업그레이드 이후 AMERICAN_AMERICA에 'DD-MON-RR'로 변경됨
등록된 scheduler 확인(dba_scheduler_jobs)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
SQL>
set lines 200 pages 1000
col job_name for a15
col job_action for a40
select job_name, job_action, enabled,
to_char(last_start_date, 'yyyy/mm/dd hh24:mi:ss') last_date,
to_char(next_run_date, 'yyyy/mm/dd hh24:mi:ss') next_date, nls_env
from dba_scheduler_jobs
where job_name like 'DBMS_JOB$%'
order by 1;
JOB_NAME JOB_ACTION ENABL LAST_DATE NEXT_DATE
--------------- ---------------------------------------- ----- ------------------- -------------------
NLS_ENV
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DBMS_JOB$_41 get_rows_from_test_sqlplus_job; TRUE 2025/10/04 17:00:07 2025/10/04 17:01:07
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_CALENDAR='GREGORIAN'
NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY_CI' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD-MON-RR HH.MI.SSXFF AM' NLS_TIME_TZ_FORMAT='HH.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH.MI.SSXFF AM TZR' NLS_DUAL_CURRENCY='$' NLS_COMP='BINARY' NLS_LENGTH_SEMANTICS='BYTE' NLS_NCHAR_CONV_EXCP='FALSE' DBMS_JOB$_42 get_rows_from_test_tool_job; TRUE 2025/10/04 17:00:07 2025/10/04 17:01:07
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_CALENDAR='GREGORIAN'
NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY_CI' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD-MON-RR HH.MI.SSXFF AM' NLS_TIME_TZ_FORMAT='HH.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH.MI.SSXFF AM TZR' NLS_DUAL_CURRENCY='$' NLS_COMP='BINARY' NLS_LENGTH_SEMANTICS='BYTE' NLS_NCHAR_CONV_EXCP='FALSE' |
기존 job 번호와 동일한 번호의 job_name이 DBMS_JOB$와 합쳐져서 만들어짐
실행 기록 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL> select result, to_char(dt, 'yyyy/mm/dd hh24:mi:ss') dt from result_log_sqlplus;
no rows selected
SQL> select result, to_char(dt, 'yyyy/mm/dd hh24:mi:ss') dt from result_log_tool;
RESULT DT
---------- -------------------
4 2025/10/04 16:38:47
4 2025/10/04 16:39:53
|
업그레이드전에 실행된 job 내역만 있고 업그레이드 이후에는 job이 실행되지 않음
수동으로 job 실행
|
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
|
SQL> exec dbms_job.run(41);
BEGIN dbms_job.run(41); END;
*
ERROR at line 1:
ORA-01861: literal does not match format string
ORA-06512: at "SYS.GET_ROWS_FROM_TEST_SQLPLUS_JOB", line 7
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_ISCHED", line 9232
ORA-06512: at "SYS.DBMS_ISCHED", line 242
ORA-06512: at "SYS.DBMS_ISCHED", line 9217
ORA-06512: at "SYS.DBMS_IJOB", line 376
ORA-06512: at "SYS.DBMS_JOB", line 262
ORA-06512: at line 1
SQL> exec dbms_job.run(42);
BEGIN dbms_job.run(42); END;
*
ERROR at line 1:
ORA-01861: literal does not match format string
ORA-06512: at "SYS.GET_ROWS_FROM_TEST_TOOL_JOB", line 7
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_ISCHED", line 9232
ORA-06512: at "SYS.DBMS_ISCHED", line 242
ORA-06512: at "SYS.DBMS_ISCHED", line 9217
ORA-06512: at "SYS.DBMS_IJOB", line 376
ORA-06512: at "SYS.DBMS_JOB", line 262
ORA-06512: at line 1
|
2개 job 모두 에러 발생함
원인 :
job이 실행되지 못한 이유는 nls_date_format이 변경되었기 때문임
기존에 'RR/MM/DD'인 job은 잘실행되었고 'DD-MON-RR'인 job은 실행되지 않았었음
하지만 업그레이드 이후에는 2개 job 모두 'DD-MON-RR'로 변경되어서 job이 실행되지 않는것
해결 방안 :
1. 프로시저에 적절한 형변환 함수를 사용
2. job을 기존 nls format으로 재등록
3. job의 nls를 속성을 수동으로 변경(공식 문서에 없는 방식)
해결방안1. 프로시저에 적절한 형변환 함수를 사용
job 수행시 line 7에서 에러가 난다고 되어 있음
|
1
2
3
4
5
6
7
|
SQL> exec dbms_job.run(42);
BEGIN dbms_job.run(42); END;
*
ERROR at line 1:
ORA-01861: literal does not match format string
ORA-06512: at "SYS.GET_ROWS_FROM_TEST_TOOL_JOB", line 7 <<---
|
프로시저에서 7번째 줄에는 v_col_dt := v_str; 부분이 존재함
프로시저를 보면 v_str VARCHAR2(20) := '2025/12/05'; 를 v_col_dt := v_str;에 넣어서 v_bound에서 사용하는데
v_str 은 varchar2 형식이고 v_col_dt는 date 형식임
이때 v_col_dt := v_str; 부분이 문제가 되는것임
date형에 varchar2형을 넣으려고 해서 에러가 나는것임
이때 아래와 같이 to_date 함수로 한번 감싸서 넣어주면 에러가 발생하지 않음
|
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
|
SQL>
CREATE OR REPLACE PROCEDURE get_rows_from_test_sqlplus_job AS
v_str VARCHAR2(20) := '2025/12/05';
v_col_dt DATE;
v_bound DATE;
v_cnt PLS_INTEGER;
BEGIN
--v_col_dt := v_str;
v_col_dt := to_date(v_str,'yyyy/mm/dd');
v_bound := ADD_MONTHS(TRUNC(v_col_dt, 'MM'), -1);
select count(*) into v_cnt
from test1
where col_dt <= v_bound;
insert into result_log_sqlplus (result, dt)
select v_cnt, sysdate from dual;
commit;
END;
/
CREATE OR REPLACE PROCEDURE get_rows_from_test_tool_job AS
v_str VARCHAR2(20) := '2025/12/05';
v_col_dt DATE;
v_bound DATE;
v_cnt PLS_INTEGER;
BEGIN
--v_col_dt := v_str;
v_col_dt := to_date(v_str,'yyyy/mm/dd');
v_bound := ADD_MONTHS(TRUNC(v_col_dt, 'MM'), -1);
select count(*) into v_cnt
from test1
where col_dt <= v_bound;
insert into result_log_tool (result, dt)
select v_cnt, sysdate from dual;
commit;
END;
/
|
수동으로 job 또는 프로시저 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL> exec dbms_job.run(41);
PL/SQL procedure successfully completed.
SQL> exec dbms_job.run(42);
PL/SQL procedure successfully completed.
SQL> exec get_rows_from_test_sqlplus_job;
PL/SQL procedure successfully completed.
SQL> exec get_rows_from_test_tool_job;
PL/SQL procedure successfully completed.
|
정상적으로 수행됨
해결방안2. job을 기존 nls format으로 재등록
먼저 해결 방안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
|
SQL>
CREATE OR REPLACE PROCEDURE get_rows_from_test_sqlplus_job AS
v_str VARCHAR2(20) := '2025/12/05';
v_col_dt DATE;
v_bound DATE;
v_cnt PLS_INTEGER;
BEGIN
v_col_dt := v_str;
--v_col_dt := to_date(v_str,'yyyy/mm/dd');
v_bound := ADD_MONTHS(TRUNC(v_col_dt, 'MM'), -1);
select count(*) into v_cnt
from test1
where col_dt <= v_bound;
insert into result_log_sqlplus (result, dt)
select v_cnt, sysdate from dual;
commit;
END;
/
CREATE OR REPLACE PROCEDURE get_rows_from_test_tool_job AS
v_str VARCHAR2(20) := '2025/12/05';
v_col_dt DATE;
v_bound DATE;
v_cnt PLS_INTEGER;
BEGIN
v_col_dt := v_str;
--v_col_dt := to_date(v_str,'yyyy/mm/dd');
v_bound := ADD_MONTHS(TRUNC(v_col_dt, 'MM'), -1);
select count(*) into v_cnt
from test1
where col_dt <= v_bound;
insert into result_log_tool (result, dt)
select v_cnt, sysdate from dual;
commit;
END;
/
|
현재 프로시저는 두개 모두 제대로 동작하지 않는 상태임
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL> exec get_rows_from_test_sqlplus_job;
BEGIN get_rows_from_test_sqlplus_job; END;
*
ERROR at line 1:
ORA-01861: literal does not match format string
ORA-06512: at "SYS.GET_ROWS_FROM_TEST_SQLPLUS_JOB", line 7
ORA-06512: at line 1
SQL> exec get_rows_from_test_tool_job;
BEGIN get_rows_from_test_tool_job; END;
*
ERROR at line 1:
ORA-01861: literal does not match format string
ORA-06512: at "SYS.GET_ROWS_FROM_TEST_TOOL_JOB", line 7
ORA-06512: at line 1
|
기존 job 삭제
|
1
2
3
|
SQL>
exec dbms_job.remove(41);
exec dbms_job.remove(42);
|
nls_date_format 세션 레벨로 변경
|
1
2
3
|
SQL> alter session set nls_date_format='RR/MM/DD';
Session altered.
|
nls 파라미터 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
SQL> show parameter nls_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_calendar string GREGORIAN
nls_comp string BINARY
nls_currency string $
nls_date_format string RR/MM/DD
nls_date_language string AMERICAN
nls_dual_currency string $
nls_iso_currency string AMERICA
nls_language string AMERICAN
nls_length_semantics string BYTE
nls_nchar_conv_excp string FALSE
nls_numeric_characters string .,
nls_sort string BINARY
nls_territory string AMERICA
nls_time_format string HH.MI.SSXFF AM
nls_time_tz_format string HH.MI.SSXFF AM TZR
nls_timestamp_format string DD-MON-RR HH.MI.SSXFF AM
nls_timestamp_tz_format string DD-MON-RR HH.MI.SSXFF AM TZR
|
정상적으로 NLS_DATE_FORMAT이 'RR/MM/DD'로 표시됨
job 재등록(모두 sqlplus 현재 세션에서 등록)
|
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
|
SQL>
DECLARE
jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT(
job => jobno,
what => 'get_rows_from_test_sqlplus_job;', -- 프로시저 이름
next_date => SYSDATE, -- 즉시 시작
interval => 'SYSDATE + 1/1440' -- 1분 주기
);
COMMIT;
END;
/
SQL>
DECLARE
jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT(
job => jobno,
what => 'get_rows_from_test_tool_job;', -- 프로시저 이름
next_date => SYSDATE, -- 즉시 시작
interval => 'SYSDATE + 1/1440' -- 1분 주기
);
COMMIT;
END;
/
|
등록된 job 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL>
set lines 200 pages 1000
col interval for a41
col what for a31
select job, what, to_char(last_date, 'yyyy/mm/dd hh24:mi:ss') last_date, to_char(next_date, 'yyyy/mm/dd hh24:mi:ss') next_date, broken, interval, nls_env
from dba_jobs
where what like 'get_rows_from_test_%'
order by job;
JOB WHAT LAST_DATE NEXT_DATE B INTERVAL
---------- ------------------------------- ------------------- ------------------- - -----------------------------------------
NLS_ENV
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
43 get_rows_from_test_sqlplus_job; 2025/10/08 16:32:33 2025/10/08 16:33:33 N SYSDATE + 1/1440
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_CALENDAR='GREGORIAN'
NLS_DATE_FORMAT='RR/MM/DD' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD-MON-RR HH.MI.SSXFF AM' NLS_TIME_TZ_FORMAT='HH.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH.MI.SSXFF AM TZR' NLS_DUAL_CURRENCY='$' NLS_COMP='BINARY' NLS_LENGTH_SEMANTICS='BYTE' NLS_NCHAR_CONV_EXCP='FALSE' 44 get_rows_from_test_tool_job; 2025/10/08 16:32:35 2025/10/08 16:33:35 N SYSDATE + 1/1440
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_CALENDAR='GREGORIAN'
NLS_DATE_FORMAT='RR/MM/DD' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD-MON-RR HH.MI.SSXFF AM' NLS_TIME_TZ_FORMAT='HH.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH.MI.SSXFF AM TZR' NLS_DUAL_CURRENCY='$' NLS_COMP='BINARY' NLS_LENGTH_SEMANTICS='BYTE' NLS_NCHAR_CONV_EXCP='FALSE' |
NLS_LANGUAGE='AMERICAN'에 NLS_TERRITORY='AMERICA'여도 NLS_DATE_FORMAT은 'RR/MM/DD'임
수동으로 job 수행(세션을 재접속해도 동일하게 잘 수행됨)
|
1
2
3
4
5
6
7
|
SQL> exec dbms_job.run(43);
PL/SQL procedure successfully completed.
SQL> exec dbms_job.run(44);
PL/SQL procedure successfully completed.
|
2개 모두 정상적으로 수행됨
해결방안3. job의 nls를 속성을 수동으로 변경(공식 문서에 없는 방식)
먼저 해결 방안2으로 변경한 내용을 원복
job 삭제 후 세션 재접속 후 job 재등록
|
1
2
3
|
SQL>
exec dbms_job.remove(43);
exec dbms_job.remove(44);
|
job 재등록(모두 sqlplus 현재 세션에서 등록)
|
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
|
SQL>
DECLARE
jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT(
job => jobno,
what => 'get_rows_from_test_sqlplus_job;', -- 프로시저 이름
next_date => SYSDATE, -- 즉시 시작
interval => 'SYSDATE + 1/1440' -- 1분 주기
);
COMMIT;
END;
/
SQL>
DECLARE
jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT(
job => jobno,
what => 'get_rows_from_test_tool_job;', -- 프로시저 이름
next_date => SYSDATE, -- 즉시 시작
interval => 'SYSDATE + 1/1440' -- 1분 주기
);
COMMIT;
END;
/
|
등록된 job 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL>
set lines 200 pages 1000
col interval for a41
col what for a31
select job, what, to_char(last_date, 'yyyy/mm/dd hh24:mi:ss') last_date, to_char(next_date, 'yyyy/mm/dd hh24:mi:ss') next_date, broken, interval, nls_env
from dba_jobs
where what like 'get_rows_from_test_%'
order by job;
JOB WHAT LAST_DATE NEXT_DATE B INTERVAL
---------- ------------------------------- ------------------- ------------------- - -----------------------------------------
NLS_ENV
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
47 get_rows_from_test_sqlplus_job; 2025/10/08 16:43:25 2025/10/08 16:44:25 N SYSDATE + 1/1440
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_CALENDAR='GREGORIAN'
NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD-MON-RR HH.MI.SSXFF AM' NLS_TIME_TZ_FORMAT='HH.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH.MI.SSXFF AM TZR' NLS_DUAL_CURRENCY='$' NLS_COMP='BINARY' NLS_LENGTH_SEMANTICS='BYTE' NLS_NCHAR_CONV_EXCP='FALSE' 48 get_rows_from_test_tool_job; 2025/10/08 16:43:28 2025/10/08 16:44:28 N SYSDATE + 1/1440
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_CALENDAR='GREGORIAN'
NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD-MON-RR HH.MI.SSXFF AM' NLS_TIME_TZ_FORMAT='HH.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH.MI.SSXFF AM TZR' NLS_DUAL_CURRENCY='$' NLS_COMP='BINARY' NLS_LENGTH_SEMANTICS='BYTE' NLS_NCHAR_CONV_EXCP='FALSE' |
AMERICAN_AMERICA에 'DD-MON-RR'로 원복됨
등록된 scheduler 확인(dba_scheduler_jobs)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
SQL>
set lines 200 pages 1000
col job_name for a15
col job_action for a40
select job_name, job_action, enabled,
to_char(last_start_date, 'yyyy/mm/dd hh24:mi:ss') last_date,
to_char(next_run_date, 'yyyy/mm/dd hh24:mi:ss') next_date, nls_env
from dba_scheduler_jobs
where job_name like 'DBMS_JOB$%'
order by 1;
JOB_NAME JOB_ACTION ENABL LAST_DATE NEXT_DATE
--------------- ---------------------------------------- ----- ------------------- -------------------
NLS_ENV
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DBMS_JOB$_47 get_rows_from_test_sqlplus_job; TRUE 2025/10/08 16:44:25 2025/10/08 16:45:25
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_CALENDAR='GREGORIAN'
NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD-MON-RR HH.MI.SSXFF AM' NLS_TIME_TZ_FORMAT='HH.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH.MI.SSXFF AM TZR' NLS_ DUAL_CURRENCY='$' NLS_COMP='BINARY' NLS_LENGTH_SEMANTICS='BYTE' NLS_NCHAR_CONV_EXCP='FALSE'
DBMS_JOB$_48 get_rows_from_test_tool_job; TRUE 2025/10/08 16:44:28 2025/10/08 16:45:28
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_CALENDAR='GREGORIAN'
NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD-MON-RR HH.MI.SSXFF AM' NLS_TIME_TZ_FORMAT='HH.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH.MI.SSXFF AM TZR' NLS_DUAL_CURRENCY='$' NLS_COMP='BINARY' NLS_LENGTH_SEMANTICS='BYTE' NLS_NCHAR_CONV_EXCP='FALSE' |
AMERICAN_AMERICA에 'DD-MON-RR'로 원복됨
아래 구문으로 nls 변경
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL>
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"DBMS_JOB$_47"',
attribute => 'NLS_ENV',
value => 'NLS_LANGUAGE=KOREAN NLS_TERRITORY=KOREA NLS_CURRENCY=''?'' NLS_ISO_CURRENCY=''KOREA'' NLS_NUMERIC_CHARACTERS=''.,'' NLS_DATE_FORMAT=''RR/MM/DD'' NLS_DATE_LANGUAGE=''KOREAN'' NLS_SORT=''BINARY'''
);
END;
/
SQL>
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"DBMS_JOB$_48"',
attribute => 'NLS_ENV',
value => 'NLS_LANGUAGE=KOREAN NLS_TERRITORY=KOREA NLS_CURRENCY=''?'' NLS_ISO_CURRENCY=''KOREA'' NLS_NUMERIC_CHARACTERS=''.,'' NLS_DATE_FORMAT=''RR/MM/DD'' NLS_DATE_LANGUAGE=''KOREAN'' NLS_SORT=''BINARY'''
);
END;
/
|
등록된 job 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL>
set lines 200 pages 1000
col interval for a41
col what for a31
select job, what, to_char(last_date, 'yyyy/mm/dd hh24:mi:ss') last_date, to_char(next_date, 'yyyy/mm/dd hh24:mi:ss') next_date, broken, interval, nls_env
from dba_jobs
where what like 'get_rows_from_test_%'
order by job;
JOB WHAT LAST_DATE NEXT_DATE B INTERVAL
---------- ------------------------------- ------------------- ------------------- - -----------------------------------------
NLS_ENV
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
47 get_rows_from_test_sqlplus_job; 2025/10/08 16:51:25 2025/10/08 16:53:13 N SYSDATE + 1/1440
NLS_LANGUAGE=KOREAN NLS_TERRITORY=KOREA NLS_CURRENCY='?' NLS_ISO_CURRENCY='KOREA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='RR/MM/DD' NLS_DATE_LANGUAGE='KOREAN' NLS_SORT='BINARY'
48 get_rows_from_test_tool_job; 2025/10/08 16:52:28 2025/10/08 16:53:33 N SYSDATE + 1/1440
NLS_LANGUAGE=KOREAN NLS_TERRITORY=KOREA NLS_CURRENCY='?' NLS_ISO_CURRENCY='KOREA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='RR/MM/DD' NLS_DATE_LANGUAGE='KOREAN' NLS_SORT='BINARY'
|
설정한 nls로 변경됨
등록된 scheduler 확인(dba_scheduler_jobs)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
SQL>
set lines 200 pages 1000
col job_name for a15
col job_action for a40
select job_name, job_action, enabled,
to_char(last_start_date, 'yyyy/mm/dd hh24:mi:ss') last_date,
to_char(next_run_date, 'yyyy/mm/dd hh24:mi:ss') next_date, nls_env
from dba_scheduler_jobs
where job_name like 'DBMS_JOB$%'
order by 1;
JOB_NAME JOB_ACTION ENABL LAST_DATE NEXT_DATE
--------------- ---------------------------------------- ----- ------------------- -------------------
NLS_ENV
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DBMS_JOB$_47 get_rows_from_test_sqlplus_job; TRUE 2025/10/08 16:53:13 2025/10/08 16:54:13
NLS_LANGUAGE=KOREAN NLS_TERRITORY=KOREA NLS_CURRENCY='?' NLS_ISO_CURRENCY='KOREA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='RR/MM/DD' NLS_DATE_LANGUAGE='KOREAN' NLS_SORT='BINARY'
DBMS_JOB$_48 get_rows_from_test_tool_job; TRUE 2025/10/08 16:52:28 2025/10/08 16:53:33
NLS_LANGUAGE=KOREAN NLS_TERRITORY=KOREA NLS_CURRENCY='?' NLS_ISO_CURRENCY='KOREA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='RR/MM/DD' NLS_DATE_LANGUAGE='KOREAN' NLS_SORT='BINARY'
|
설정한 nls로 변경됨
수동으로 job 수행(세션을 재접속해도 동일하게 잘 수행됨)
|
1
2
3
4
5
6
7
|
SQL> exec dbms_job.run(47);
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> exec dbms_job.run(48);
PL/SQL 처리가 정상적으로 완료되었습니다.
|
2개 모두 정상적으로 수행됨(완료 메세지가 한글로 표시됨)
(한글이 깨져서 나올수도 있으니 다른 nls 값들은 그대로 두고 NLS_DATE_FORMAT만 수정하는게 어떨까 싶음)
결론 :
오라클 11g나 12c에서 19c로 업그레이드 할때 rman을 이용해 그대로 신규서버로 가서 업그레이드를 할수도 있고
동일한 서버에서 업그레이드를 할수도 있음
하지만 이때 job이 있는 경우 job이 19c로 가면서 scheduler로 복사됨(이관됨)
이때 문제가 발생하는데 job이나 scheduler는 등록하는 세션의 nls와 동일하게 등록됨
만약 형변환 함수를 제대로 작성해주지 않은 프로시저를 실행하는 job이 존재하는 경우
이 job이 기존에 등록될때는 NLS_LANG=KOREAN_KOREA.KO16MSWIN949에 NLS_DATE_FORMAT='RR/MM/DD' 환경에서 등록했을때는 잘 실행되다가
업그레이드 이후에는 자동으로 AMERICAN_AMERICA.KO16MSWIN949에 NLS_DATE_FORMAT='DD-MON-RR'로 변경되어 job 실행에 문제가 생길 수 있음
(업그레이드 명령을 수행하는 리눅스 shell 세션은 기본이 AMERICAN_AMERICA에 'DD-MON-RR'이기때문)
이런 문제를 방지하기 위해 습관적으로 형변환 함수를 잘 써주거나, 업그레이드 수행 전 .bash_profile에 nls를 기존 job과 동일하게 적어두는게 안전함
개인적으로는 db 업그레이드는 모든 오브젝트가 그대로 업그레이드 되야한다고 생각하는데 job의 nls_env가 변경되는것은 문제가 될수 있다고 생각함 추후에는 이런 부분이 수정되면 좋을것 같음
참조 :
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_JOBS.html
https://oracle-base.com/articles/12c/scheduler-enhancements-12cr2
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 19c utlrp 시 수행되는 job (0) | 2025.10.19 |
|---|---|
| 오라클 26ai 공개 (0) | 2025.10.14 |
| 오라클 19c 수동 shutdown (0) | 2025.10.09 |
| 오라클 19c autotask, 자동통계수집 잡 확인 및 비활성화 (0) | 2025.09.19 |
| 오라클 19c 통계정보 lock 방법 (0) | 2025.08.03 |