區(qū)文件版本升級(jí)：ORA-01805排查與DBMS_DST腳本實(shí)踐)
簡(jiǎn)介面向 Oracle DBA 與數(shù)據(jù)庫(kù)運(yùn)維人員的時(shí)區(qū)版本升級(jí)腳本包用于將數(shù)據(jù)庫(kù)時(shí)區(qū)版本調(diào)整至較新版本通常配合官方時(shí)區(qū)補(bǔ)丁使用。包內(nèi)共 4 個(gè) SQL 文件壓縮包約 16KB體量輕便主要包含升級(jí)前檢查、時(shí)區(qū)調(diào)整應(yīng)用以及相關(guān)統(tǒng)計(jì)輔助腳本并附有執(zhí)行順序說(shuō)明先運(yùn)行檢查腳本評(píng)估環(huán)境再執(zhí)行應(yīng)用腳本完成調(diào)整便于在變更前確認(rèn)兼容性與影響范圍。已有 1693 人學(xué)習(xí)適合需要處理時(shí)區(qū)版本不一致、補(bǔ)丁配套升級(jí)或升級(jí)前預(yù)檢的運(yùn)維場(chǎng)景。腳本以 SQL 形式組織方便在 SQL*Plus 等環(huán)境中按說(shuō)明逐步執(zhí)行可作為時(shí)區(qū)版本調(diào)整的輔助工具幫助讀者快速完成環(huán)境核查、變更實(shí)施與結(jié)果驗(yàn)證降低手工操作遺漏風(fēng)險(xiǎn)。1. 凌晨?jī)牲c(diǎn)的 ORA-01805多半是時(shí)區(qū)文件版本沒對(duì)齊去年冬天一個(gè)報(bào)表庫(kù)半夜跑批失敗報(bào)ORA-01805: possible error in date/time operation詭異的是同一條 SQL 在數(shù)據(jù)庫(kù)服務(wù)器上手工執(zhí)行正常應(yīng)用連上來(lái)就報(bào)錯(cuò)。查了兩個(gè)小時(shí)才定位數(shù)據(jù)庫(kù)用的時(shí)區(qū)文件和應(yīng)用服務(wù)器上客戶端帶的時(shí)區(qū)數(shù)據(jù)差了三四個(gè)版本兩邊對(duì)同一個(gè)TIMESTAMP WITH TIME ZONE值的偏移解釋不一致SQL 里只要帶AT TIME ZONE、FROM_TZ或者跨時(shí)區(qū)比較就必然報(bào)錯(cuò)。這類問題不是改 SQL 能解決的動(dòng)的是數(shù)據(jù)庫(kù)的時(shí)區(qū)版本time zone file version。手工做一遍要走BEGIN_PREPARE → UPGRADE_DATABASE → END_PREPARE → BEGIN_UPGRADE → UPGRADE_DATABASE → END_UPGRADE六個(gè)動(dòng)作中間還得配合只讀窗口、錯(cuò)誤表排查、版本校驗(yàn)漏一步就白跑。這份 DBMS_DST_scripts V1.9 干的就是把這一串按順序固化成可重復(fù)執(zhí)行的腳本包。它適合手里有含 TSTZ 列的老庫(kù)、要在可控停機(jī)窗口里做一次時(shí)區(qū)版本抬升的運(yùn)維和 DBA也適合第一次做這件事、想先照著一份能跑的流程走一遍的人。如果庫(kù)里一個(gè) TSTZ 列都沒有那用不上它換掉 tzfile 重啟就完事。2. 拆包看結(jié)構(gòu)V1.9 封的是三段窗口和兩張錯(cuò)誤表2.1 時(shí)區(qū)文件是張映射表升級(jí)動(dòng)的是存量數(shù)據(jù)的偏移解釋先把概念掰清楚不然容易把時(shí)區(qū)版本升級(jí)當(dāng)成改參數(shù)。Oracle 的時(shí)區(qū)規(guī)則放在$ORACLE_HOME/oracore/zoneinfo/下文件名形如timezlrg_N.dat、timezone_N.datN 就是版本號(hào)。這個(gè)文件里存的不是北京時(shí)間等于 UTC8這種常量而是一張地區(qū) → 歷史偏移規(guī)則的映射表包含歷次夏令時(shí)的起止時(shí)間點(diǎn)。某個(gè)地區(qū)調(diào)整過一次切換日期就要出一個(gè)新版本。v$timezone_file告訴你當(dāng)前數(shù)據(jù)庫(kù)正在用第幾版v$timezone_file_supplied列出這套 ORACLE_HOME 里實(shí)際放了幾份、都到第幾版。兩個(gè)視圖一對(duì)你就知道能不能升、能升到幾。關(guān)鍵在于 TSTZ 列的物理存儲(chǔ)內(nèi)部記的是 UTC 時(shí)刻加一個(gè)偏移量不存地區(qū)名。數(shù)據(jù)庫(kù)沒有這條數(shù)據(jù)屬于哪個(gè)地區(qū)的信息一旦規(guī)則被修正舊數(shù)據(jù)里那個(gè)裸偏移量就沒有辦法再反推回正確的絕對(duì)時(shí)刻。所以升級(jí)動(dòng)作本質(zhì)是按新規(guī)則把每一行 TSTZ 值重算一遍寫回新的內(nèi)部表示。這也解釋了為什么改DBTIMEZONE、ALTER SESSION SET TIME_ZONE全都沒用——它們只影響顯示不碰存量數(shù)據(jù)。一個(gè)直白的例子某地區(qū)把夏令時(shí)起點(diǎn)從 3 月最后一個(gè)周日改到第二個(gè)周日落在這一小時(shí)里的歷史數(shù)據(jù)按舊規(guī)則反推的 UTC 時(shí)刻會(huì)比正確值差 3600 秒。升級(jí)之后這批行的顯示值可能平移一小時(shí)這是正?，F(xiàn)象不是腳本寫錯(cuò)了。2.2 三條路徑選錯(cuò)會(huì)白白多停兩小時(shí)腳本包一般會(huì)把三種做法都留出入口按庫(kù)的體量選路徑動(dòng)作序列停機(jī)特征適用場(chǎng)景單段式BEGIN_UPGRADE→UPGRADE_DATABASE→END_UPGRADE全程停機(jī)時(shí)長(zhǎng)約等于全量掃 TSTZ 表庫(kù)不大、TSTZ 表少、能停夠時(shí)間兩段式BEGIN_PREPARE→UPGRADE_DATABASE試跑→END_PREPARE→BEGIN_UPGRADE→UPGRADE_DATABASE→END_UPGRADE試跑段不停機(jī)正式段停機(jī)生產(chǎn)庫(kù)、表多、不敢一次壓上只換 tzfile替換oracore/zoneinfo下文件后重啟一次重啟庫(kù)內(nèi)無(wú) TSTZ 數(shù)據(jù)只需版本對(duì)齊兩段式是我默認(rèn)選的理由不是更穩(wěn)這種空話而是試跑段能提前把所有轉(zhuǎn)換異常的行炸出來(lái)。UPGRADE_DATABASE在 prepare 窗口里跑完不落盤END_PREPARE會(huì)把改動(dòng)回滾代價(jià)只是多掃一遍表的 IO 和時(shí)間。生產(chǎn)庫(kù)上這一遍能換到正式窗口里不會(huì)因?yàn)榕K數(shù)據(jù)中途停很劃算。第三個(gè)路徑容易被誤解只換 tzfile 只讓二進(jìn)制認(rèn)識(shí)新規(guī)則存量 TSTZ 數(shù)據(jù)仍按舊規(guī)則存著客戶端版本對(duì)齊的問題能解決數(shù)據(jù)層面的一致性問題解決不了。2.3 包里各腳本的分工腳本包各版本文件名略有出入按功能對(duì)號(hào)入座即可不要死記名字。功能分區(qū)典型內(nèi)容依賴對(duì)象什么時(shí)候跑環(huán)境體檢查v$timezone_file、v$timezone_file_supplied、TSTZ 列清單、行數(shù)估算數(shù)據(jù)字典視圖窗口前一周影響面分析調(diào)DBMS_DST.FIND_AFFECTED_TABLES輸出受影響的表和時(shí)區(qū)列表DBMS_DST窗口前一周試跑段BEGIN_PREPAREUPGRADE_DATABASE錯(cuò)誤寫進(jìn)DST$ERRORSDBMS_DST低峰期可反復(fù)跑正式段END_PREPARE、BEGIN_UPGRADE、UPGRADE_DATABASE、END_UPGRADEDBMS_DST停機(jī)窗口內(nèi)校驗(yàn)段版本核對(duì)、快照比對(duì)、失效對(duì)象統(tǒng)計(jì)上一階段產(chǎn)物正式段結(jié)束后立刻清理段刪快照表、刪錯(cuò)誤表、刪臨時(shí)基線表SYS 權(quán)限觀察期結(jié)束后2.4 這份包管不到的三件事別指望一個(gè) SQL 腳本包把周邊全包了有三塊必須自己收尾。一是客戶端側(cè)。應(yīng)用服務(wù)器上 Oracle Client 的 tzfile、JDBC 驅(qū)動(dòng)自帶的時(shí)區(qū)數(shù)據(jù)、甚至某些語(yǔ)言的運(yùn)行時(shí)庫(kù)都有自己的一份時(shí)區(qū)規(guī)則。數(shù)據(jù)庫(kù)升到新版本、客戶端還停在舊版本ORA-01805照樣復(fù)現(xiàn)。升級(jí)前后要逐個(gè)節(jié)點(diǎn)核對(duì)這一條我吃過虧。二是主備架構(gòu)。主庫(kù)做的升級(jí)會(huì)通過 redo 傳到備庫(kù)但備庫(kù)的二進(jìn)制里必須已經(jīng)包含新版本 tzfile否則 apply 會(huì)直接停住。順序是先把所有節(jié)點(diǎn)的二進(jìn)制和補(bǔ)丁對(duì)齊再在主庫(kù)動(dòng)數(shù)據(jù)本文還有配套的精品資源點(diǎn)擊獲取 Oracle時(shí)區(qū)文件版本升級(jí)：ORA-01805排查與DBMS_DST腳本實(shí)踐
簡(jiǎn)介面向 Oracle DBA 與數(shù)據(jù)庫(kù)運(yùn)維人員的時(shí)區(qū)版本升級(jí)腳本包用于將數(shù)據(jù)庫(kù)時(shí)區(qū)版本調(diào)整至較新版本通常配合官方時(shí)區(qū)補(bǔ)丁使用。包內(nèi)共 4 個(gè) SQL 文件壓縮包約 16KB體量輕便主要包含升級(jí)前檢查、時(shí)區(qū)調(diào)整應(yīng)用以及相關(guān)統(tǒng)計(jì)輔助腳本并附有執(zhí)行順序說(shuō)明先運(yùn)行檢查腳本評(píng)估環(huán)境再執(zhí)行應(yīng)用腳本完成調(diào)整便于在變更前確認(rèn)兼容性與影響范圍。已有 1693 人學(xué)習(xí)適合需要處理時(shí)區(qū)版本不一致、補(bǔ)丁配套升級(jí)或升級(jí)前預(yù)檢的運(yùn)維場(chǎng)景。腳本以 SQL 形式組織方便在 SQL*Plus 等環(huán)境中按說(shuō)明逐步執(zhí)行可作為時(shí)區(qū)版本調(diào)整的輔助工具幫助讀者快速完成環(huán)境核查、變更實(shí)施與結(jié)果驗(yàn)證降低手工操作遺漏風(fēng)險(xiǎn)。1. 凌晨?jī)牲c(diǎn)的 ORA-01805多半是時(shí)區(qū)文件版本沒對(duì)齊去年冬天一個(gè)報(bào)表庫(kù)半夜跑批失敗報(bào)ORA-01805: possible error in date/time operation詭異的是同一條 SQL 在數(shù)據(jù)庫(kù)服務(wù)器上手工執(zhí)行正常應(yīng)用連上來(lái)就報(bào)錯(cuò)。查了兩個(gè)小時(shí)才定位數(shù)據(jù)庫(kù)用的時(shí)區(qū)文件和應(yīng)用服務(wù)器上客戶端帶的時(shí)區(qū)數(shù)據(jù)差了三四個(gè)版本兩邊對(duì)同一個(gè)TIMESTAMP WITH TIME ZONE值的偏移解釋不一致SQL 里只要帶AT TIME ZONE、FROM_TZ或者跨時(shí)區(qū)比較就必然報(bào)錯(cuò)。這類問題不是改 SQL 能解決的動(dòng)的是數(shù)據(jù)庫(kù)的時(shí)區(qū)版本time zone file version。手工做一遍要走BEGIN_PREPARE → UPGRADE_DATABASE → END_PREPARE → BEGIN_UPGRADE → UPGRADE_DATABASE → END_UPGRADE六個(gè)動(dòng)作中間還得配合只讀窗口、錯(cuò)誤表排查、版本校驗(yàn)漏一步就白跑。這份 DBMS_DST_scripts V1.9 干的就是把這一串按順序固化成可重復(fù)執(zhí)行的腳本包。它適合手里有含 TSTZ 列的老庫(kù)、要在可控停機(jī)窗口里做一次時(shí)區(qū)版本抬升的運(yùn)維和 DBA也適合第一次做這件事、想先照著一份能跑的流程走一遍的人。如果庫(kù)里一個(gè) TSTZ 列都沒有那用不上它換掉 tzfile 重啟就完事。2. 拆包看結(jié)構(gòu)V1.9 封的是三段窗口和兩張錯(cuò)誤表2.1 時(shí)區(qū)文件是張映射表升級(jí)動(dòng)的是存量數(shù)據(jù)的偏移解釋先把概念掰清楚不然容易把時(shí)區(qū)版本升級(jí)當(dāng)成改參數(shù)。Oracle 的時(shí)區(qū)規(guī)則放在$ORACLE_HOME/oracore/zoneinfo/下文件名形如timezlrg_N.dat、timezone_N.datN 就是版本號(hào)。這個(gè)文件里存的不是北京時(shí)間等于 UTC8這種常量而是一張地區(qū) → 歷史偏移規(guī)則的映射表包含歷次夏令時(shí)的起止時(shí)間點(diǎn)。某個(gè)地區(qū)調(diào)整過一次切換日期就要出一個(gè)新版本。v$timezone_file告訴你當(dāng)前數(shù)據(jù)庫(kù)正在用第幾版v$timezone_file_supplied列出這套 ORACLE_HOME 里實(shí)際放了幾份、都到第幾版。兩個(gè)視圖一對(duì)你就知道能不能升、能升到幾。關(guān)鍵在于 TSTZ 列的物理存儲(chǔ)內(nèi)部記的是 UTC 時(shí)刻加一個(gè)偏移量不存地區(qū)名。數(shù)據(jù)庫(kù)沒有這條數(shù)據(jù)屬于哪個(gè)地區(qū)的信息一旦規(guī)則被修正舊數(shù)據(jù)里那個(gè)裸偏移量就沒有辦法再反推回正確的絕對(duì)時(shí)刻。所以升級(jí)動(dòng)作本質(zhì)是按新規(guī)則把每一行 TSTZ 值重算一遍寫回新的內(nèi)部表示。這也解釋了為什么改DBTIMEZONE、ALTER SESSION SET TIME_ZONE全都沒用——它們只影響顯示不碰存量數(shù)據(jù)。一個(gè)直白的例子某地區(qū)把夏令時(shí)起點(diǎn)從 3 月最后一個(gè)周日改到第二個(gè)周日落在這一小時(shí)里的歷史數(shù)據(jù)按舊規(guī)則反推的 UTC 時(shí)刻會(huì)比正確值差 3600 秒。升級(jí)之后這批行的顯示值可能平移一小時(shí)這是正?，F(xiàn)象不是腳本寫錯(cuò)了。2.2 三條路徑選錯(cuò)會(huì)白白多停兩小時(shí)腳本包一般會(huì)把三種做法都留出入口按庫(kù)的體量選路徑動(dòng)作序列停機(jī)特征適用場(chǎng)景單段式BEGIN_UPGRADE→UPGRADE_DATABASE→END_UPGRADE全程停機(jī)時(shí)長(zhǎng)約等于全量掃 TSTZ 表庫(kù)不大、TSTZ 表少、能停夠時(shí)間兩段式BEGIN_PREPARE→UPGRADE_DATABASE試跑→END_PREPARE→BEGIN_UPGRADE→UPGRADE_DATABASE→END_UPGRADE試跑段不停機(jī)正式段停機(jī)生產(chǎn)庫(kù)、表多、不敢一次壓上只換 tzfile替換oracore/zoneinfo下文件后重啟一次重啟庫(kù)內(nèi)無(wú) TSTZ 數(shù)據(jù)只需版本對(duì)齊兩段式是我默認(rèn)選的理由不是更穩(wěn)這種空話而是試跑段能提前把所有轉(zhuǎn)換異常的行炸出來(lái)。UPGRADE_DATABASE在 prepare 窗口里跑完不落盤END_PREPARE會(huì)把改動(dòng)回滾代價(jià)只是多掃一遍表的 IO 和時(shí)間。生產(chǎn)庫(kù)上這一遍能換到正式窗口里不會(huì)因?yàn)榕K數(shù)據(jù)中途停很劃算。第三個(gè)路徑容易被誤解只換 tzfile 只讓二進(jìn)制認(rèn)識(shí)新規(guī)則存量 TSTZ 數(shù)據(jù)仍按舊規(guī)則存著客戶端版本對(duì)齊的問題能解決數(shù)據(jù)層面的一致性問題解決不了。2.3 包里各腳本的分工腳本包各版本文件名略有出入按功能對(duì)號(hào)入座即可不要死記名字。功能分區(qū)典型內(nèi)容依賴對(duì)象什么時(shí)候跑環(huán)境體檢查v$timezone_file、v$timezone_file_supplied、TSTZ 列清單、行數(shù)估算數(shù)據(jù)字典視圖窗口前一周影響面分析調(diào)DBMS_DST.FIND_AFFECTED_TABLES輸出受影響的表和時(shí)區(qū)列表DBMS_DST窗口前一周試跑段BEGIN_PREPAREUPGRADE_DATABASE錯(cuò)誤寫進(jìn)DST$ERRORSDBMS_DST低峰期可反復(fù)跑正式段END_PREPARE、BEGIN_UPGRADE、UPGRADE_DATABASE、END_UPGRADEDBMS_DST停機(jī)窗口內(nèi)校驗(yàn)段版本核對(duì)、快照比對(duì)、失效對(duì)象統(tǒng)計(jì)上一階段產(chǎn)物正式段結(jié)束后立刻清理段刪快照表、刪錯(cuò)誤表、刪臨時(shí)基線表SYS 權(quán)限觀察期結(jié)束后2.4 這份包管不到的三件事別指望一個(gè) SQL 腳本包把周邊全包了有三塊必須自己收尾。一是客戶端側(cè)。應(yīng)用服務(wù)器上 Oracle Client 的 tzfile、JDBC 驅(qū)動(dòng)自帶的時(shí)區(qū)數(shù)據(jù)、甚至某些語(yǔ)言的運(yùn)行時(shí)庫(kù)都有自己的一份時(shí)區(qū)規(guī)則。數(shù)據(jù)庫(kù)升到新版本、客戶端還停在舊版本ORA-01805照樣復(fù)現(xiàn)。升級(jí)前后要逐個(gè)節(jié)點(diǎn)核對(duì)這一條我吃過虧。二是主備架構(gòu)。主庫(kù)做的升級(jí)會(huì)通過 redo 傳到備庫(kù)但備庫(kù)的二進(jìn)制里必須已經(jīng)包含新版本 tzfile否則 apply 會(huì)直接停住。順序是先把所有節(jié)點(diǎn)的二進(jìn)制和補(bǔ)丁對(duì)齊再在主庫(kù)動(dòng)數(shù)據(jù)本文還有配套的精品資源點(diǎn)擊獲取