Oracle.ManagedDataAccess - "ORA-01882: timezone region not found" 오류가 발생하는 이유
오랜만에 .NET Core 3.1 + docker 환경에서 Oracle.ManagedDataAccess.Core를 사용해 Oracle 접속 테스트를 했는데요, 다음과 같은 오류가 발생하는군요. ^^;
An unhandled exception occurred while processing the request.
OracleException: ORA-00604: error occurred at recursive SQL level 1
ORA-01882: timezone region not found
OracleInternal.ConnectionPool.PoolManager<PM, CP, PR>.Get(ConnectionString csWithDiffOrNewPwd, bool bGetForApp, OracleConnection connRefForCriteria, string affinityInstanceName, bool bForceMatch)
Stack Query Cookies Headers Routing
OracleException: ORA-00604: error occurred at recursive SQL level 1 ORA-01882: timezone region not found
OracleInternal.ConnectionPool.PoolManager<PM, CP, PR>.Get(ConnectionString csWithDiffOrNewPwd, bool bGetForApp, OracleConnection connRefForCriteria, string affinityInstanceName, bool bForceMatch)
OracleInternal.ConnectionPool.OraclePoolManager.Get(ConnectionString csWithNewPassword, bool bGetForApp, OracleConnection connRefForCriteria, string affinityInstanceName, bool bForceMatch)
OracleInternal.ConnectionPool.OracleConnectionDispenser<PM, CP, PR>.Get(ConnectionString cs, PM conPM, ConnectionString pmCS, SecureString securedPassword, SecureString securedProxyPassword, OracleConnection connRefForCriteria)
Oracle.ManagedDataAccess.Client.OracleConnection.Open()
razor31_sample.Pages.IndexModel.DoOracleManagedCall(int delay) in Index.cshtml.cs
razor31_sample.Pages.IndexModel.OnGet(string fileName) in Index.cshtml.cs
Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.ExecutorFactory+VoidHandlerMethod.Execute(object receiver, object[] arguments)
Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.InvokeHandlerMethodAsync()
Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.InvokeNextPageFilterAsync()
Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.Rethrow(PageHandlerExecutedContext context)
Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.InvokeInnerFilterAsync()
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, object state, bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeFilterPipelineAsync()
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)
Show raw exception details
검색해 보면 다행히 답이 나옵니다.
ORA-01882: timezone region not found
; https://stackoverflow.com/questions/9156379/ora-01882-timezone-region-not-found
답변 중에 "Uday Singh"의 글을 보면, Oracle DB에 등록되지 않은 timezone을 클라이언트에서 보내는 경우 발생한다고 하는데요, 아마도 OracleConnection이 기본값으로 전송하는 timezone이 서버에 등록되지 않은 것이기 때문에 저런 오류가 발생하는 듯합니다.
참고로, 서버에 등록된 timezone은
SQL Developer와 같은 도구를 이용해 오라클에 접속해 다음의 쿼리를 전송하면 됩니다.
SELECT DISTINCT tzname FROM V$TIMEZONE_NAMES
제 경우에 테스트로 설치한 Oracle 11g Express Edition은 다음과 같은 timezone이 열거됩니다.
Africa/Addis_Ababa
Africa/Bissau
Africa/Ceuta
Africa/Harare
Africa/Kinshasa
Africa/Maputo
Africa/Windhoek
America/Argentina/Buenos_Aires
America/Cayenne
America/Cordoba
America/Danmarkshavn
America/Guadeloupe
America/Hermosillo
America/Indiana/Indianapolis
America/Indiana/Knox
America/Knox_IN
America/La_Paz
America/Manaus
America/Mendoza
America/Nipigon
America/Panama
America/Swift_Current
Asia/Beirut
Asia/Macau
Asia/Saigon
Asia/Tokyo
Atlantic/Faeroe
Atlantic/St_Helena
Australia/Melbourne
Australia/West
Chile/EasterIsland
Eire
Etc/GMT
Etc/GMT+3
Etc/GMT+7
Etc/GMT-11
Etc/GMT-3
Europe/Andorra
Europe/Bratislava
Europe/Dublin
Europe/Jersey
Europe/Nicosia
Europe/Tirane
Europe/Zaporozhye
GMT+0
Indian/Chagos
Indian/Mauritius
Mexico/General
MST7MDT
NZ
NZ-CHAT
Pacific/Easter
Pacific/Palau
Poland
ROC
US/Alaska
Africa/Abidjan
Africa/Asmara
Africa/Brazzaville
Africa/Douala
Africa/Lome
Africa/Mogadishu
Africa/Monrovia
Africa/Niamey
Africa/Nouakchott
Africa/Ouagadougou
Africa/Timbuktu
Africa/Tripoli
America/Araguaina
America/Belize
America/Coral_Harbour
America/Cuiaba
America/Grenada
America/Indiana/Tell_City
America/Juneau
America/Kentucky/Louisville
America/Louisville
America/Montevideo
America/North_Dakota/New_Salem
America/Paramaribo
America/Rainy_River
America/Resolute
America/Sao_Paulo
America/Tortola
America/Vancouver
Asia/Bahrain
Asia/Brunei
Asia/Dili
Asia/Hovd
Asia/Karachi
Asia/Kolkata
Asia/Riyadh
Asia/Tehran
Asia/Ulaanbaatar
Atlantic/Stanley
Australia/Broken_Hill
Chile/Continental
Etc/GMT+10
Etc/GMT+9
Etc/GMT-2
Europe/Athens
Europe/Bucharest
Europe/Guernsey
Europe/Sofia
Europe/Vienna
Europe/Zagreb
GMT-0
Indian/Comoro
Jamaica
MST
Pacific/Chatham
Pacific/Noumea
US/Aleutian
US/Arizona
US/Pacific-New
Africa/Dar_es_Salaam
Africa/Kigali
Africa/Porto-Novo
America/Argentina/Catamarca
America/Argentina/ComodRivadavia
America/Bahia
America/Belem
America/Boise
America/Denver
America/Eirunepe
America/Indiana/Vevay
America/Indiana/Vincennes
America/Kentucky/Monticello
America/Maceio
America/Matamoros
America/Mexico_City
America/Moncton
America/Monterrey
America/Porto_Acre
America/Yellowknife
Antarctica/Palmer
Asia/Aqtobe
Asia/Dubai
Asia/Kashgar
Asia/Macao
Asia/Manila
Asia/Nicosia
Asia/Oral
Asia/Qyzylorda
Asia/Sakhalin
Atlantic/Bermuda
Australia/Canberra
Australia/NSW
Australia/South
Australia/Tasmania
Brazil/DeNoronha
Brazil/East
Canada/Yukon
EST5EDT
Etc/GMT+8
Etc/GMT-10
Etc/GMT-5
Etc/Greenwich
Europe/Brussels
Europe/Malta
Europe/Podgorica
Europe/Prague
Europe/Samara
Europe/San_Marino
Europe/Tiraspol
Iceland
Indian/Christmas
Mexico/BajaSur
Pacific/Enderbury
Pacific/Fakaofo
Pacific/Majuro
Pacific/Ponape
Pacific/Tahiti
Pacific/Tongatapu
Pacific/Wake
PRC
Africa/Accra
Africa/Bamako
Africa/Conakry
Africa/Johannesburg
Africa/Libreville
Africa/Lusaka
Africa/Sao_Tome
America/Argentina/Cordoba
America/Argentina/Salta
America/Argentina/San_Luis
America/Atka
America/Barbados
America/Bogota
America/Buenos_Aires
America/Costa_Rica
America/Guatemala
America/Indiana/Winamac
America/Marigot
America/Montserrat
America/Phoenix
America/Porto_Velho
America/Rosario
America/Santa_Isabel
America/Santiago
America/Scoresbysund
America/St_Vincent
America/Thule
America/Toronto
Antarctica/DumontDUrville
Asia/Aden
Asia/Amman
Asia/Ashkhabad
Asia/Bishkek
Asia/Choibalsan
Asia/Chungking
Asia/Colombo
Asia/Dhaka
Asia/Dushanbe
Asia/Gaza
Asia/Irkutsk
Asia/Jerusalem
Asia/Novosibirsk
Asia/Omsk
Asia/Rangoon
Asia/Tel_Aviv
Asia/Thimbu
Asia/Vientiane
Asia/Vladivostok
Atlantic/Canary
Australia/Currie
Australia/Hobart
Australia/Lord_Howe
Australia/Perth
Australia/Sydney
Canada/Central
Etc/GMT+0
Etc/GMT+5
Etc/GMT-8
Etc/GMT-9
Europe/Belgrade
Europe/Berlin
Europe/Budapest
Europe/Gibraltar
Europe/Isle_of_Man
Europe/Kaliningrad
Europe/Ljubljana
Europe/Luxembourg
Europe/Paris
Europe/Tallinn
Europe/Uzhgorod
Europe/Volgograd
GMT
HST
Indian/Cocos
MET
Mexico/BajaNorte
Pacific/Apia
Pacific/Efate
Pacific/Fiji
Pacific/Guadalcanal
Pacific/Johnston
Pacific/Pitcairn
Pacific/Port_Moresby
Pacific/Samoa
Pacific/Truk
Pacific/Yap
US/Eastern
US/Michigan
Africa/Bangui
Africa/Blantyre
Africa/Cairo
Africa/Khartoum
Africa/Lagos
Africa/Malabo
Africa/Maseru
Africa/Nairobi
Africa/Tunis
America/Argentina/Tucuman
America/Bahia_Banderas
America/Blanc-Sablon
America/Cancun
America/Dominica
America/Havana
America/Indiana/Marengo
America/Los_Angeles
America/Managua
America/Martinique
America/Mazatlan
America/Menominee
America/Miquelon
America/Nome
America/Rankin_Inlet
America/Santo_Domingo
America/Shiprock
America/St_Thomas
America/Virgin
America/Whitehorse
America/Winnipeg
America/Yakutat
Antarctica/South_Pole
Arctic/Longyearbyen
Asia/Baghdad
Asia/Baku
Asia/Bangkok
Asia/Calcutta
Asia/Damascus
Asia/Harbin
Asia/Jakarta
Asia/Kabul
Asia/Singapore
Asia/Urumqi
Atlantic/Azores
Australia/Adelaide
Australia/LHI
Brazil/West
Canada/Pacific
CST6CDT
Cuba
Egypt
Etc/GMT+2
Etc/GMT+4
Etc/GMT0
Etc/GMT-14
Europe/Amsterdam
Europe/Helsinki
Europe/Istanbul
Europe/Kiev
Europe/Lisbon
Europe/Mariehamn
Europe/Minsk
GB-Eire
Greenwich
Indian/Antananarivo
Indian/Mayotte
Kwajalein
Pacific/Auckland
Pacific/Galapagos
Pacific/Honolulu
Pacific/Nauru
Pacific/Wallis
US/Central
US/Mountain
UTC
Africa/Gaborone
America/Asuncion
America/Campo_Grande
America/Chicago
America/Detroit
America/Edmonton
America/Ensenada
America/Goose_Bay
America/Halifax
America/Indiana/Petersburg
America/Indianapolis
America/Iqaluit
America/Jamaica
America/Merida
America/Montreal
America/New_York
America/Ojinaga
America/Port-au-Prince
America/St_Johns
America/St_Lucia
America/Thunder_Bay
Antarctica/Casey
Antarctica/Davis
Antarctica/Macquarie
Antarctica/McMurdo
Antarctica/Syowa
Asia/Almaty
Asia/Chongqing
Asia/Hong_Kong
Asia/Kathmandu
Asia/Kuching
Asia/Kuwait
Asia/Magadan
Asia/Shanghai
Asia/Thimphu
Asia/Yakutsk
Asia/Yerevan
Atlantic/Faroe
Atlantic/Jan_Mayen
Atlantic/Madeira
Atlantic/South_Georgia
Australia/Darwin
Australia/Eucla
Canada/East-Saskatchewan
CET
CST
Etc/GMT+1
Etc/GMT-1
Etc/GMT-12
Europe/Belfast
Europe/Chisinau
Europe/Copenhagen
Europe/London
Europe/Rome
Europe/Skopje
Europe/Warsaw
GB
Indian/Maldives
Israel
Pacific/Gambier
Pacific/Kosrae
Pacific/Norfolk
Pacific/Rarotonga
Pacific/Saipan
PST8PDT
US/Hawaii
W-SU
Africa/Algiers
Africa/Asmera
Africa/Banjul
Africa/Bujumbura
Africa/Casablanca
Africa/Djibouti
Africa/El_Aaiun
Africa/Luanda
America/Anchorage
America/Argentina/La_Rioja
America/Argentina/Mendoza
America/Argentina/San_Juan
America/Aruba
America/Boa_Vista
America/Catamarca
America/Cayman
America/Chihuahua
America/Curacao
America/Dawson_Creek
America/El_Salvador
America/Fortaleza
America/Grand_Turk
America/Guayaquil
America/Guyana
America/North_Dakota/Center
America/Port_of_Spain
America/Recife
America/Regina
America/Rio_Branco
America/Tegucigalpa
Antarctica/Mawson
Asia/Anadyr
Asia/Dacca
Asia/Ho_Chi_Minh
Asia/Istanbul
Asia/Jayapura
Asia/Katmandu
Asia/Muscat
Asia/Phnom_Penh
Asia/Pontianak
Asia/Pyongyang
Asia/Seoul
Asia/Tashkent
Asia/Ujung_Pandang
Atlantic/Cape_Verde
Australia/ACT
Australia/North
Australia/Victoria
Canada/Atlantic
Canada/Mountain
Canada/Newfoundland
Canada/Saskatchewan
EET
Etc/GMT+11
Etc/GMT+12
Etc/GMT+6
Etc/GMT-0
Etc/GMT-13
Etc/GMT-4
Europe/Madrid
Europe/Monaco
Europe/Moscow
Europe/Oslo
Europe/Vaduz
Europe/Zurich
GMT0
Indian/Reunion
Libya
Navajo
Pacific/Funafuti
Pacific/Marquesas
Pacific/Pago_Pago
Pacific/Tarawa
ROK
Singapore
US/Indiana-Starke
US/Pacific
WET
Africa/Dakar
Africa/Freetown
Africa/Kampala
Africa/Lubumbashi
Africa/Mbabane
Africa/Ndjamena
America/Adak
America/Anguilla
America/Antigua
America/Argentina/Jujuy
America/Argentina/Rio_Gallegos
America/Argentina/Ushuaia
America/Atikokan
America/Cambridge_Bay
America/Caracas
America/Dawson
America/Fort_Wayne
America/Glace_Bay
America/Godthab
America/Inuvik
America/Jujuy
America/Lima
America/Nassau
America/Noronha
America/Pangnirtung
America/Puerto_Rico
America/St_Barthelemy
America/St_Kitts
America/Tijuana
Asia/Aqtau
Asia/Ashgabat
Asia/Kamchatka
Asia/Krasnoyarsk
Asia/Kuala_Lumpur
Asia/Makassar
Asia/Qatar
Asia/Samarkand
Asia/Taipei
Asia/Tbilisi
Asia/Ulan_Bator
Asia/Yekaterinburg
Atlantic/Reykjavik
Australia/Brisbane
Australia/Lindeman
Australia/Queensland
Australia/Yancowinna
Brazil/Acre
Canada/Eastern
EST
Etc/GMT-6
Etc/GMT-7
Europe/Riga
Europe/Sarajevo
Europe/Simferopol
Europe/Stockholm
Europe/Vatican
Europe/Vilnius
Hongkong
Indian/Kerguelen
Indian/Mahe
Iran
Japan
Pacific/Guam
Pacific/Kiritimati
Pacific/Kwajalein
Pacific/Midway
Pacific/Niue
Portugal
PST
Turkey
US/East-Indiana
US/Samoa
검색해 보면, docker ubuntu 환경의 경우 기본 timezone 값이 UTC라고 하는데요, 사실 이 값은 저 목록에 포함된 값입니다. 그런데, 왜 오류가 발생하는 것일까요? ^^ (잠시 후 아래에서 좀 더 자세하게 설명합니다.)
아무튼, 그래서 저 목록 중의 하나를 보내면 되는데요, 이를 위해 간단하게 환경 설정에 TZ 값으로 지정하면 된다고 합니다. 따라서 docker의 경우 dockerfile에 이렇게 ENV 명령어를 포함하면 됩니다.
...[생략]...
FROM base AS final
WORKDIR /app
COPY --from=publish /app/publish .
ENV TZ=Asia/Seoul
ENTRYPOINT ["dotnet", "razor31_sample.dll"]
...[생략]...
좀 더 검색을 해보니까, 어떤 글에서는 오라클이 설치된 서버의 TIMEZONE과 접속하려는 측의 TIMEZONE이 맞아야 한다고 하는데요, 제 경우에는 그 문제는 아닌 듯합니다. 왜냐하면, 아래와 같이 TZ를 설정해도,
ENV TZ=America/New_York
잘 동작했기 때문입니다. 뿐만 아니라 "ENV TZ=UTC"로 입력해도 잘 동작합니다. 그냥 유추하는 건데요, 아마도 저렇게 맞춰주어야 동작했다는 것은 오라클 DB에 등록된 TIMEZONE의 목록이 몇 개 없었던 것이 아닐까.. 추측해 봅니다.
그런데, 호기심이 생기는군요. ^^ 과연 Oracle.ManagedDataAccess가 사용하는 기본 TIMEZONE 값이 뭘까요?
좀 더 검색해 보니, 과거 ODP.NET 시절에는 OracleGlobalization 클래스가 정적 메서드로 GetClientInfo를 제공해 주었기 때문에,
// https://docs.oracle.com/database/121/ODPNT/OracleGlobalizationClass.htm#ODPNT3126
using System;
using Oracle.DataAccess.Client;
class GetClientInfoSample
{
static void Main()
{
// Get client's globalization info
OracleGlobalization glob = OracleGlobalization.GetClientInfo();
// Prints "glob.Language = AMERICAN"
Console.WriteLine("glob.Language = " + glob.Language);
glob.Dispose();
}
}
기본 timezone을 알아낼 수 있었지만, Oracle.ManagedDataAccess에서는 GetClientInfo 정적 메서드가 삭제되었습니다. 대신 OracleConnection 개체에서 GetSessionInfo가 제공되는데요,
using (OracleConnection oraConnection = new OracleConnection(oradb))
{
oraConnection.Open();
OracleGlobalization og = oraConnection.GetSessionInfo();
}
아쉽게도, 저건 Open을 이미 성공한 다음에 호출할 수 있습니다. (현재 Open에서 보내는 기본 TIMEZONE을 알고 싶은 건데... ^^;)
Reflection을 통해 OracleGlobalization 인스턴스를 생성하는 코드를 찾다 보니 다음의 코드가 나옵니다.
internal OracleGlobalizationImpl(int lcid)
{
int zoneID = 0;
OracleGlobalizationImpl.GetLocaleSpecificNLSValues(lcid, ref this.m_language, ref this.m_territory, ref this.m_timeZone, ref zoneID);
}
여기서 lcid는 m_appThreadLCID라는 변수가 넘어가는데, 의미상으로 보면 스레드의 LCID 값이므로 다음과 같은 식으로 Reflection을 이용해 호출할 수 있습니다.
int lcid = Thread.CurrentThread.CurrentCulture.LCID;
Type type = typeof(OracleConnection).Assembly.GetType("OracleInternal.ServiceObjects.OracleGlobalizationImpl");
object[] args = new object[] { lcid, "", "", "", 0 };
MethodInfo mi = type.GetMethod("GetLocaleSpecificNLSValues", BindingFlags.Static | BindingFlags.NonPublic);
mi.Invoke(null, args);
/*
args[0] == 1042 (== Thread.CurrentThread.CurrentCulture.LCID)
args[1] == "KOREAN"
args[2] == "KOREA"
args[3] == "Asia/Seoul";
args[4] == 273
*/
docker 환경이 아닌, 제 컴퓨터에서 실행하면 저렇게 값이 잘 나옵니다. 그리고 docker 환경으로 테스트를 했더니 값이 이렇게 나옵니다.
args[0] == 127 (== Thread.CurrentThread.CurrentCulture.LCID)
args[1] == "AMERICAN"
args[2] == "AMERICA"
args[3] == "Etc/UTC";
args[4] == 28
아하... docker의 기본 TIMEZONE 값이 "Etc/UTC"라고 되어 있군요, 그리고 분명히 이 값은 "V
$TIMEZONE_NAMES"에 등록돼 있지 않으므로 ORA-01882 오류 원인이 잘 설명됩니다. 이 상태에서 환경 변수를 이용해 TZ 설정을 UTC로 하면, 위의 출력 결과가 다음과 같이 바뀝니다.
args[0] == 127 (== Thread.CurrentThread.CurrentCulture.LCID)
args[1] == "AMERICAN"
args[2] == "AMERICA"
args[3] == "Etc/GMT";
args[4] == 1
왜 UTC가 "Etc/GMT"로 해석되는지는 알 수 없지만, 어쨌든 "Etc/GMT"는 "V
$TIMEZONE_NAMES"에 값이 등록돼 있고 따라서 오류가 발생하지 않는 것도 역시 잘 설명됩니다. 참고로, "TZ=Asia/Seoul"로 설정했더니 이번에는 TIMEZONE 값에 그대로 반영이 됩니다.
args[0] == 127 (== Thread.CurrentThread.CurrentCulture.LCID)
args[1] == "AMERICAN"
args[2] == "AMERICA"
args[3] == "Asia/Seoul";
args[4] == 273
어쨌든, 이렇게 해서 수수께끼가 풀렸습니다. ^^
[이 글에 대해서 여러분들과 의견을 공유하고 싶습니다. 틀리거나 미흡한 부분 또는 의문 사항이 있으시면 언제든 댓글 남겨주십시오.]