19cにアップグレードしてTO_DATEで困った話 [SQL・DDL]
2019年5月1日、日本が令和を迎えるとき、私はExadataを12.2から19cへアップグレードする作業を行っていた。プロジェクトは開発期間とはいえ運用開始に向けての試験が詰まっていおり、この10連休というまたとない長期間DBを停止されるタイミングを狙ったという訳である。個人的にDBのアップグレードという作業は（長年Oracleにかかわってきたものの）初めての経験であり、そもそも可能な限り「避けるべきもの」、という位置づけであった。しかし、今回どうしてもとなったのは、長期保守の観点でやはりターミナルである19cに上げたい、ということとなったのである。あるいみ、DBの元号変更をこのGWに経験した訳である。
一言にアップグレードといっても、実際の作業をイメージするのは難しいかもしれない。DBのアップグレードは通常DB/GIを上げることを指すが、Exadataの場合これに加え、ESS（IBSW含む）、ZFS、EMを含めて整合性の取れた形にする必要がある。また、DB/GIとESSのバージョンは依存関係があるため、ESSだけ先走ってあげてしまうと、サポートされていない組み合わせになることもある。
そのため今回は一度DBにRUを当てて、ESSを上げられる状態にし、ESSを上げ、再びDB/GIを上げてという手順を取った。当然、この前後にミドルの落とし上げやOS・DBのバックアップ等の作業を含めると、かなりの作業量となる。手順を確立する上で、アップグレードの事前検証は必須であるが、それがあったとしても、環境差異を完全に吸収することはできないため、ある程度のリスクは想定しなければならないだろう。連休中などサポートが手薄な状況で最後に頼れるのはいつも人である。
さて、全体的にアップグレードにより12.2で踏んでいたORA-600系の不具合やいくつかのExadataのクリティカルイシューが修正されたことは純粋に喜ばしいことであった。以前このBlogでも紹介したスマートスキャンの結果不正についてもこのバージョンで修正されていることを確認している。Oracleの不具合の多さ、品質に疑問を感じることも少なくないが、一方でかなりの不具合は既に修正が出ていることが多いため、パッチを当てることができさえすれば問題を事前に回避することはできると思っている。あくまでもパッチを適用できれば、の話であるが。
一方、アップグレードにより発生した新たな問題もあった。Oracleコミュニティに投稿したが、19cでTO_DATEのフォーマットのマッチングのチェック仕様の一部が変更されたのである。以下を見てほしい。
*12.2.0.1
SQL> select TO_DATE('20190520','YYMMDD') from dual;
TO_DATE('20190520
-----------------
20190520 00:00:00
*19.2.0.0
SQL> select TO_DATE('20190520','YYMMDD') from dual;
select TO_DATE('20190520','YYMMDD') from dual
*
ERROR at line 1:
ORA-01843: not a valid month
基本的に上記で指定しているYYMMDDはYYYYMMDDと記述すべきであり、19cでエラーになる・ならないに関わらずこのような曖昧な記述は避けるべきである。しかし、実はSQLリファレンスマニュアルにYYのマッチングに失敗したらYYYYを試すという挙動が記載されている。これは自分も知らなかったので正直驚いた。
---
SQL reference manual / String-to-Date Conversion Rules
The following additional formatting rules apply when converting string values to date values
...
-If a match fails between a datetime format element and the corresponding characters in the date string, then Oracle attempts alternative format elements, as shown in Table 2-20.
Table 2-20 Oracle Format Matching
original format element:'YY' --> additional format elements to try in place of the original: 'YYYY'
---
この挙動、興味深いのは19cにおいて TO_DATE('20120520','YYMMDD')はエラーにならないのである。20→YY、12→MMにマッチングさせるところで、月のチェックが通るためと思われる。このため、データの内容に応じてエラーになる・ならないが変わるのである。また、TO_DATE('2019/05/20','YY/MM/DD')は19cでもエラーは出ない。/により曖昧さが排除されている結果と思われる。同様な関数としてTO_TIMESTAMPが気になるが、こちらは12c・19cの挙動に変更はなさそうであった。どうやらコード（実際は構文を解析するパーサだとは思うが）が異なるためらしい。
なお、多くの日本で稼動しているOracleDatabaseにおいて、Table 2-20 Oracle Format Matchingに記載されているYY→YYYY以外のフォーマットマッチングについて、同様の事象が発生するのはRR→RRRRのみである。それは、上記のNLS_LANGが日本語の場合、MONとMONTHのフォーマットに違いはなく、いずれも「7月」といった文言を変換できる。また、TO_DATE('7月','MONTH')は必ずエラーとなる挙動は12c,19c変わらずである。
OracleとしてはこのTO_DATEの挙動変更はあくまで「仕様」であり、不具合ではないというスタンスらしい。このため、19cへのマイグレーションにおいてはSQLの非互換として意識しておく必要がある。しかし、この手の細かな仕様変更はリリースノートに記載されていないから厄介である。なぜこの事象に気がついたか、経験のあるDBAなら察していただけるであろう。世の中のSQLが、このようなOracleのニッチな仕様を前提として動いていないことを祈るばかりである。
※2021/2/11追記
当時、My Oracle Support Community へ投稿したときの記事がこちら(a different TO_DATE behavior in 19c)。コミュニティの有識者の中では不具合ではないか、同じ事象に遭遇した、という声があった。
以上
一言にアップグレードといっても、実際の作業をイメージするのは難しいかもしれない。DBのアップグレードは通常DB/GIを上げることを指すが、Exadataの場合これに加え、ESS（IBSW含む）、ZFS、EMを含めて整合性の取れた形にする必要がある。また、DB/GIとESSのバージョンは依存関係があるため、ESSだけ先走ってあげてしまうと、サポートされていない組み合わせになることもある。
そのため今回は一度DBにRUを当てて、ESSを上げられる状態にし、ESSを上げ、再びDB/GIを上げてという手順を取った。当然、この前後にミドルの落とし上げやOS・DBのバックアップ等の作業を含めると、かなりの作業量となる。手順を確立する上で、アップグレードの事前検証は必須であるが、それがあったとしても、環境差異を完全に吸収することはできないため、ある程度のリスクは想定しなければならないだろう。連休中などサポートが手薄な状況で最後に頼れるのはいつも人である。
さて、全体的にアップグレードにより12.2で踏んでいたORA-600系の不具合やいくつかのExadataのクリティカルイシューが修正されたことは純粋に喜ばしいことであった。以前このBlogでも紹介したスマートスキャンの結果不正についてもこのバージョンで修正されていることを確認している。Oracleの不具合の多さ、品質に疑問を感じることも少なくないが、一方でかなりの不具合は既に修正が出ていることが多いため、パッチを当てることができさえすれば問題を事前に回避することはできると思っている。あくまでもパッチを適用できれば、の話であるが。
一方、アップグレードにより発生した新たな問題もあった。Oracleコミュニティに投稿したが、19cでTO_DATEのフォーマットのマッチングのチェック仕様の一部が変更されたのである。以下を見てほしい。
*12.2.0.1
SQL> select TO_DATE('20190520','YYMMDD') from dual;
TO_DATE('20190520
-----------------
20190520 00:00:00
*19.2.0.0
SQL> select TO_DATE('20190520','YYMMDD') from dual;
select TO_DATE('20190520','YYMMDD') from dual
*
ERROR at line 1:
ORA-01843: not a valid month
基本的に上記で指定しているYYMMDDはYYYYMMDDと記述すべきであり、19cでエラーになる・ならないに関わらずこのような曖昧な記述は避けるべきである。しかし、実はSQLリファレンスマニュアルにYYのマッチングに失敗したらYYYYを試すという挙動が記載されている。これは自分も知らなかったので正直驚いた。
---
SQL reference manual / String-to-Date Conversion Rules
The following additional formatting rules apply when converting string values to date values
...
-If a match fails between a datetime format element and the corresponding characters in the date string, then Oracle attempts alternative format elements, as shown in Table 2-20.
Table 2-20 Oracle Format Matching
original format element:'YY' --> additional format elements to try in place of the original: 'YYYY'
---
この挙動、興味深いのは19cにおいて TO_DATE('20120520','YYMMDD')はエラーにならないのである。20→YY、12→MMにマッチングさせるところで、月のチェックが通るためと思われる。このため、データの内容に応じてエラーになる・ならないが変わるのである。また、TO_DATE('2019/05/20','YY/MM/DD')は19cでもエラーは出ない。/により曖昧さが排除されている結果と思われる。同様な関数としてTO_TIMESTAMPが気になるが、こちらは12c・19cの挙動に変更はなさそうであった。どうやらコード（実際は構文を解析するパーサだとは思うが）が異なるためらしい。
なお、多くの日本で稼動しているOracleDatabaseにおいて、Table 2-20 Oracle Format Matchingに記載されているYY→YYYY以外のフォーマットマッチングについて、同様の事象が発生するのはRR→RRRRのみである。それは、上記のNLS_LANGが日本語の場合、MONとMONTHのフォーマットに違いはなく、いずれも「7月」といった文言を変換できる。また、TO_DATE('7月','MONTH')は必ずエラーとなる挙動は12c,19c変わらずである。
OracleとしてはこのTO_DATEの挙動変更はあくまで「仕様」であり、不具合ではないというスタンスらしい。このため、19cへのマイグレーションにおいてはSQLの非互換として意識しておく必要がある。しかし、この手の細かな仕様変更はリリースノートに記載されていないから厄介である。なぜこの事象に気がついたか、経験のあるDBAなら察していただけるであろう。世の中のSQLが、このようなOracleのニッチな仕様を前提として動いていないことを祈るばかりである。
※2021/2/11追記
当時、My Oracle Support Community へ投稿したときの記事がこちら(a different TO_DATE behavior in 19c)。コミュニティの有識者の中では不具合ではないか、同じ事象に遭遇した、という声があった。
以上
2019-07-07 21:05
nice!(0)
コメント(0)
コメント 0