简介面向Oracle数据库运维、升级与补丁实施场景的时区版本调整脚本包适用于需要将数据库时区版本更新至最新版本的DBA及系统实施人员。压缩包内含4个SQL脚本整体约16KB体积轻量主要围绕时区版本检查、应用调整及TSTZ相关统计辅助展开其中检查脚本用于升级前评估应用脚本用于执行调整并附有使用说明便于按检查、应用的顺序配合时区补丁完成操作。已有1693人学习具备一定实践参考基础。读者可获得一套可直接参考的时区升级脚本与执行思路帮助在正式变更前确认当前时区状态、识别潜在问题并按脚本提示完成版本调整减少手工编写SQL带来的遗漏风险适合作为Oracle时区补丁配套工具留存备用。1. 时区文件差一个版本数据泵就先报错某模拟项目从测试库往新库导数据expdp 导出顺利impdp 跑到第三张表时直接返回 ORA-01805两边的 Oracle 主版本、字符集、NLS 参数一路对齐过问题却卡在一个很少被注意的地方——时区文件版本。源库v$timezone_file里是 18目标库还停在 14。Oracle 的TIMESTAMP WITH TIME ZONE列存的不是一段文本而是“UTC 时间 时区规则引用”两个库的规则版本不同同一行数据放到另一边就没有确定答案报错只是较轻微的情况更麻烦的是不报错、但时间悄悄偏掉几个小时。DBMS_DST 是官方留给这类问题的入口DBMS_DST_scriptsV1.9.zip 这套脚本把散落在手册各处的检查、准备、升级、验证串成了一条按顺序执行的路径。手上有一批 TSTZ 列、又要做跨库迁移或版本对齐的人迟早会用到它。2. 装前环境清点DBMS_DST 跑起来的四个硬条件时区升级这件事麻烦的地方在于它没有“安装失败就重来”的空间。时区文件版本号在数据字典里是单调递增的升上去以后没有任何官方降级脚本唯一能退回去的办法是恢复备份。所以在跑脚本之前先把环境条件逐条核对清楚比装完再去找原因便宜得多。这一章列出的检查项全部是只读查询跑多少遍都不会改变现场。2.1 版本与时区文件版本先对上号第一件事是确认当前版本以及它支持哪种升级路径。DBMS_DST 这个包本身从 10g 就有了但不同大版本之间可以调用过程、行为方式、是否需要以 UPGRADE 模式重启差别不小。我一般会先把下面这条查询跑出来把VERSION这一列的数字记在变更单上。-- 当前时区文件版本升级前必须留档升级后用来对比 SELECT version, filename FROM v$timezone_file; -- 会话时区与数据库时区不一致时 TSTZ 的换算结果会不一样 SELECT dbtimezone, sessiontimezone FROM dual; -- 有没有真正的 TSTZ / TSLTZ 列没有的话这套升级对业务意义有限 SELECT owner, table_name, column_name, data_type FROM dba_tab_cols WHERE data_type IN (TIMESTAMP WITH TIME ZONE,TIMESTAMP WITH LOCAL TIME ZONE) AND hidden_column NO AND owner NOT IN (SYS,SYSTEM,SYSMAN,DBSNMP,XDB,MDSYS,CTXSYS,WMSYS,ORDSYS,OLAPSYS,EXFSYS,APEX_030200);第一条查询返回的VERSION就是目标版本号的起点FILENAME会告诉你它对应$ORACLE_HOME/oracore/zoneinfo/下哪个.dat文件。第二条查询关注DBTIMEZONE如果它被设置成某个固定时区并且SESSIONTIMEZONE与之不同后续对比数据时就要用同一个会话时区否则你看到的时间偏移是会话造成的跟升级无关。第三条查询决定要不要做这件事业务表里根本没有 TSTZ 列升级时区文件只是让版本号更一致收益有限风险却不小。版本差异这块实际会碰到的组合大致如下选路径前对一眼数据库版本可用方式需要留意的点11.2.0.4 及更早BEGIN_PREPARE / END_PREPARE / BEGIN_UPGRADE 分步执行需要手工以 UPGRADE 模式重启步骤多别漏12.1 / 12.2分步执行或 UPGRADE_DATABASE 一步完成一步完成会自动重启数据库务必在维护窗口内执行19c 及以上同上另有 FIND_AFFECTED_TABLES 便于抽样多租户环境要进到具体 PDB 里执行CDB$ROOT 单独看目标版本号从哪来直接从 Oracle 安装目录里看当前介质自带哪个时区文件即可不用去猜。# 看当前 $ORACLE_HOME 自带哪些时区文件编号最大的通常就是可选目标 ls -l $ORACLE_HOME/oracore/zoneinfo/ | grep -E timezlrg|timezone_timezlrg_*.dat是带闰秒信息的完整版timezone_*.dat是精简版。文件名后缀的数字就是版本号。注意一点目标版本必须大于当前VERSION小于等于没有意义DBMS_DST 会直接拒绝。2.2 权限、表空间和 UNDO 的余量检查DBMS_DST 的执行身份没有商量余地必须以 SYSDBA 连接。用普通 DBA 账号执行 BEGIN_PREPARE 时报出来的错误往往指向一个看不出原因的内部对象权限多花半天才能定位。我习惯直接以操作系统认证进入# OS 认证进库避免口令和口令文件带来的额外变量 sqlplus -s / as sysdba准备阶段会扫描全库所有 TSTZ 表把受影响的行记进SYS.DST$AFFECTED_TABLES过程中产生大量递归 SQL 和 UNDO。库大的时候这一步可能跑几十分钟甚至更久UNDO 表空间不够就会卡在中间报 ORA-01555回滚又要再等一遍。所以动手前先看一眼余量-- 数据文件余量重点看 SYSTEM 和 UNDO SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024/1024, 2) AS total_gb, ROUND(SUM(maxbytes)/1024/1024/1024, 2) AS max_gb FROM dba_data_files GROUP BY tablespace_name ORDER BY 1; -- UNDO 的实际使用峰值别只看当前占用 SELECT begin_time, end_time, undoblks, maxquerylen FROM v$undostat ORDER BY begin_time DESC FETCH FIRST 10 ROWS ONLY;MAXBYTES这一列决定了自动扩展的上限TOTAL_GB不高但MAX_GB足够的时候只是数据文件没长到位提前手工扩一下更稳妥两个都贴近上限就是真的没空间了。V$UNDOSTAT里的UNDOBLKS反映最近一段时间的 UNDO 消耗峰值按它的量级再乘一个安全系数是我判断够不够的常用做法。准备阶段的中间结果主要落在 SYSTEM 表空间SYSTEM 只剩几百兆的时候不要开始这一步不会跟你商量。2.3 备份与不可逆点先留恢复手段时区文件版本不能回退这句话值得单独占一段。升级完成后即使把数据库恢复到升级前的物理备份只要中间有过一次正常写入那些 TSTZ 值就是按新规则写进去的混着旧规则读出来只会更混乱。所以备份要满足两个条件一是时间点必须在升级动作之前二是要确认它真的能恢复也就是做一次 restore validate不要只看备份任务显示成功。具体做法我一般固定成三步RMAN 全备加归档日志备份、导出一份关键 TSTZ 表的样本数据、把v$timezone_file的查询结果和DBTIMEZONE写进变更记录。# 全备包含归档恢复窗口留给升级动作之后的两天 rman target / EOF CONFIGURE BACKUP OPTIMIZATION ON; BACKUP DATABASE PLUS ARCHIVELOG; BACKUP VALIDATE DATABASE ARCHIVELOG ALL; LIST BACKUP SUMMARY; EOFBACKUP VALIDATE这一步很多人省掉但它才是真正验证备份能不能用的动作只读不写代价是 I/O不产生备份集。真要出事的时候一份没验证过的备份和一个没有备份的区别只在于你会多花几个小时才发现没有备份。样本数据导出的思路很简单挑两三张有代表性的 TSTZ 表把主键和时间列按固定条件导成 CSV升级后拿同样的 SQL 再导一次做比对。这个文件后面第六章还要用。3. 脚本就位V1.9 包怎么放、怎么校验脚本包本身不大但它会被反复执行而且每次执行都会改动数据字典。放的位置、执行的身份、日志落在哪这几件事一旦随意后面排查起来就会互相干扰。3.1 上传、校验与目录规划千万不要把包解到$ORACLE_HOME下面。那个目录的属主、权限、以及升级补丁时的行为都很敏感混进几个自建 SQL 文件下次打补丁清理时容易连自己一起清掉。我的习惯是在 oracle 用户家目录下按版本号建独立目录一次升级一个目录执行日志单独放log事后可以直接归档。# 以 oracle 用户执行不要用 root 建目录再 chown mkdir -p /home/oracle/dst_upgrade/v19/log cd /home/oracle/dst_upgrade/v19 # 上传后先校验别拿到半截文件就开始跑 md5sum DBMS_DST_scriptsV1.9.zip # 解压不覆盖已有文件出问题能看出是哪次改的 unzip -n DBMS_DST_scriptsV1.9.zip -d . # 先看清单和执行顺序说明比直接跑脚本重要 ls -l head -60 README* 2/dev/nullmd5sum的结果要跟分发方给出的值逐位对上对不上就直接重新拿一份不要抱着“应该没事”的心态继续。unzip -n保证重复解压不会覆盖你已经改过的参数文件。最后那句head不是走形式这类脚本包的编号顺序有时和实际执行顺序不完全一致README 里通常会写明哪些是只读检查、哪些会改数据字典、哪一步之后必须停。3.2 脚本清单与连接方式包里的脚本按用途大体分四类编号不同版本可能微调实际以目录里文件头部的注释为准。理解这个分类比记住文件名有用类别作用是否改动数据字典环境检查版本、表空间、TSTZ 列统计否只读准备阶段BEGIN_PREPARE、生成受影响表清单是写中间表正式升级BEGIN_UPGRADE、重启前后确认是切换时区文件版本验证与回收版本核对、样本比对、清理中间表部分每一类脚本都用同一个方式执行SQL*Plus 加 SPOOL把标准输出完整留档。脚本里出现WHENEVER SQLERROR EXIT是有意设计的任何一步报错立即中断避免带着错误状态往下跑。连接方式固定成下面这段模板后续每一步换脚本名就行# 统一入口-s 静默掉 bannerSPOOL 保留完整输出 sqlplus -s / as sysdba EOF SET SERVEROUTPUT ON SIZE UNLIMITED SET ECHO ON SET LINESIZE 200 SET PAGESIZE 200 WHENEVER SQLERROR EXIT FAILURE SPOOL /home/oracle/dst_upgrade/v19/log/step_check.log /home/oracle/dst_upgrade/v19/01_check_env.sql SPOOL OFF EXIT EOF几个参数值得说明一下。SET ECHO ON让日志里保留你执行过的原始命令回头复盘时不用靠记忆还原。WHENEVER SQLERROR EXIT FAILURE会让 SQL*Plus 在第一个错误处退出并返回非零码外面套一层 shell 判断就能拦住自动化脚本继续往下走。SPOOL的路径必须提前存在SPOOL 不会自动建目录路径不存在时报的是 ORA-01031 之类看起来毫不相干的错误这是新手最容易卡住的一步。3.3 在测试库先跑一遍 dry run再小的一次时区升级也值得在一份克隆库上先跑一遍。没有克隆环境的话退一步的做法是只跑检查类脚本加BEGIN_PREPARE看到受影响表清单和预估耗时然后立刻END_PREPARE撤回正式升级留到窗口内做。这个动作能提前暴露两个问题受影响表的实际数量和分布决定窗口够不够、以及 SQL*Plus 环境本身的坑字符集、NLS 参数、脚本路径。-- 只准备不升级看完清单就撤回不改时区文件版本 EXEC DBMS_DST.BEGIN_PREPARE(32); -- 受影响表按行数排个序心里对耗时有个数 SELECT * FROM SYS.DST$AFFECTED_TABLES ORDER BY 1; -- 撤回准备阶段这一步不改变任何业务数据 EXEC DBMS_DST.END_PREPARE;END_PREPARE的作用是把准备阶段写入的中间对象清掉。它不会碰业务数据也不会改时区版本可以放心地反复执行“准备—查看—撤回”这个循环。三次以上还不放心说明真正的问题不在脚本而在窗口和回退方案没有谈定。4. 执行时区升级从 BEGIN_PREPARE 到重启的完整命令序列到了正式窗口节奏反而要放慢。这一步的每一步都有明确的进入条件和退出确认不用一口气跑完跑一步看一步才是稳妥做法。四个阶段的顺序不能调换中间任何一步失败都退回到“看日志、看清单、决定是否恢复备份”的判断上不要尝试跳过某一步继续往下走。4.1 准备阶段生成受影响表清单准备阶段的目的很单一扫描全库把哪些表的哪些行在时区规则变化后会出问题记到系统表里。命令只有一句但执行前要确认当前没有大事务、没有大批量导数任务在跑否则扫描结果会带着这些临时状态。-- 目标版本号写实际要升到的那个数字必须大于当前 v$timezone_file.version DEFINE tz_target 32 EXEC DBMS_DST.BEGIN_PREPARE(tz_target); -- 中间表放在 SYSTEM执行前确认余量执行中盯着增长 SELECT tablespace_name, bytes/1024/1024 AS mb FROM dba_segments WHERE segment_name LIKE SYS_DST%; -- 受影响明细按表聚合看数量级 SELECT COUNT(*) AS affected_rows, COUNT(DISTINCT table_name) AS affected_tables FROM SYS.DST$AFFECTED_TABLES;BEGIN_PREPARE的参数是一个 NUMBER语义是“准备升级到这个版本”不是“从哪个版本升”。这一点容易搞混参数写小了会直接报错写大了则会在后面正式升级时失败。执行期间SYS_DST%相关段会持续增长长在 SYSTEM 表空间里监控脚本要盯住这个表空间不要盯错地方。准备阶段跑完清单也拿到了接下来做一个判断受影响行数是不是在可接受范围内。几十万行以内正常窗口就够上千万行就要考虑分批策略或者先和业务确认这段时间内 TSTZ 列的读写能不能停。这一步的判断失误是导致窗口超时、半夜回滚的主要原因。4.2 正式升级UPGRADE 模式重启的那一段正式升级的核心动作是把数据库的时区文件版本切到新值并让所有已经存在的 TSTZ 数据按新规则重新解释一遍。不同版本这块的细节差别最大也是整个流程里最容易出错的一段。以分步执行为例-- 第一步发起升级fail_on_dst_issues 设 TRUE 时遇到差异直接中止 EXEC DBMS_DST.BEGIN_UPGRADE(tz_target, fail_on_dst_issues TRUE); -- 中间表里如果有记录说明有数据无法自动转换先看内容再决定 SELECT * FROM SYS.DST$ERROR_TABLE;fail_on_dst_issues这个参数我的取舍是第一次在测试库跑设TRUE让潜在问题暴露出来正式库上确认测试库无报错后也设TRUE宁可中断也不要带着未知差异升完。设成FALSE时遇到问题会记进错误表继续往下走看起来流程跑通了实际埋了雷。发起升级之后需要让数据库以 UPGRADE 模式重启一次切换动作在启动过程中完成。这一步必须在维护窗口内做且要确认没有其他实例或连接在访问-- 关闭并以 UPGRADE 模式启动让升级动作在启动阶段落地 SHUTDOWN IMMEDIATE; STARTUP UPGRADE; -- 启动完成后确认状态然后正常重启一次 SELECT * FROM v$timezone_file; SHUTDOWN IMMEDIATE; STARTUP;从 UPGRADE 模式启动到正常启动之间数据库是拒绝普通连接的这段时间应用侧的表现是“连不上”不是“报错”监控告警会响提前跟值班说一声能省掉很多电话。RAC 环境所有实例都要走一遍这个重启只重启一个节点是最常见的失误症状是部分会话的时间正常、部分偏移看起来像玄学问题。12c 以后也可以用一步完成的方式代价是它自己会重启数据库执行前必须确认窗口和会话都已经清理干净-- 一步完成准备、升级、重启parallel 按 CPU 核数的四分之一给 EXEC DBMS_DST.UPGRADE_DATABASE(tz_target, parallel 8, fail_on_dst_issues TRUE);parallel参数控制扫描和转换的并行度给太大反而会因为争用把时间拖长按核数的四分之一起步比较稳。这个调用方式省掉了手工重启但也意味着中间过程你看不到出问题时只能靠日志倒推所以我更倾向于在关键库上走分步。4.3 收尾重新编译对象与统计信息升级完成后还有两件容易被跳过的事。一是重新编译可能失效的对象升级过程中涉及的包、视图、触发器有可能进入 INVALID 状态二是重新收集受影响表的统计信息TSTZ 列的数据分布变了优化器还拿着旧统计执行计划可能突然变差。-- 重新编译失效对象输出里剩下的 INVALID 需要人工看 ?/rdbms/admin/utlrp.sql -- 对受影响表精确收集统计信息大表用比例采样避免长时间锁 BEGIN FOR r IN (SELECT owner, table_name FROM SYS.DST$AFFECTED_TABLES GROUP BY owner, table_name) LOOP DBMS_STATS.GATHER_TABLE_STATS( ownname r.owner, tabname r.table_name, estimate_percent DBMS_STATS.AUTO_SAMPLE_SIZE, cascade TRUE, degree 4); END LOOP; END; /cascade TRUE表示索引统计一起收TSTZ 列上有索引的话必须带上否则索引的选择性估算还是旧的。degree 4是并行度按库的实际负载调整业务高峰期跑这个循环会明显吃 I/O尽量放在收尾阶段的低峰。utlrp.sql跑完还会剩下少量 INVALID 对象多数是本来就没在用的但需要逐个确认不能默认它们无害。到这里升级动作本身结束了。剩下的判断是“到底成没成”这部分留到第六章先把踩过的坑说清楚。5. 避坑与排查五个典型故障的定位路径这一章记录的是真出过问题、并且能在日志里找到明确线索的五种情况。这类故障的共同点是报错信息指向的位置和真正的原因往往隔着一层只看报错会往错误的方向查很久。5.1 现象、原因与处理对照现象一升级完成跨库查询照样报 ORA-01805。原因多半不在服务端而在客户端。客户端自带的时区文件版本比服务端低做 TSTZ 运算时用的是本地的规则。解决方式是统一客户端版本或者显式指定时区文件在客户端环境里设置ORA_TZFILE指向较新的timezlrg_*.dat然后重连验证。这条在 JDBC 场景尤其常见驱动版本和 ojdbc 包的组合比数据库版本更容易被忽略。现象二BEGIN_PREPARE 跑了两个小时没结束UNDO 表空间告警。原因是准备阶段全库扫描且没有分批中间结果全部堆在 UNDO 和 SYSTEM 里。处理方式是中止后用END_PREPARE清理现场扩 UNDO 到按V$UNDOSTAT峰值估算的两倍再重跑。想缩短时间可以先用FIND_AFFECTED_TABLES抽样看分布确认没有超大表以后再整体执行。现象三RAC 环境升级后部分会话时间偏了 8 小时。原因是只有一个实例走了 UPGRADE 模式重启其他实例还在用旧时区文件。解决方式是把所有实例依次关闭、以 UPGRADE 模式启动、再正常启动全部走完后确认每个实例查到的v$timezone_file.version一致。这一步没有捷径只能逐个来。现象四升级第二天SYSTEM 表空间满了报 ORA-01653。原因是准备阶段和升级阶段创建的中间表没有及时清理。处理方式是确认升级已经完成后按脚本包里的清理脚本回收这些中间对象不要手工去 drop 系统表。想避免的话在窗口开始前就把 SYSTEM 的自动扩展上限调高或者提前扩一个数据文件。现象五升级成功但某张 TSTZ 大表的查询突然慢了十倍。原因通常是统计信息没跟上优化器按旧的 NDV 估算选择了一个糟糕的执行计划。处理方式是单独对这张表做一次GATHER_TABLE_STATS加cascade TRUE然后看执行计划是否回到预期。仍然不对的话去查DBA_HIST_SQLSTAT里升级前后的计划变化而不是直接加 hint。5.2 用系统视图定位而不是靠猜出问题的时候先跑一组固定的查询来确认当前状态就能排除掉大半猜测-- 服务端时区文件版本所有实例必须一致 SELECT * FROM v$timezone_file; -- 升级中间表和错误表有记录说明转换过程有异常 SELECT COUNT(*) FROM SYS.DST$ERROR_TABLE; SELECT COUNT(*) FROM SYS.DST$AFFECTED_TABLES; -- 最近的告警日志位置ORA-错误最终都要回到这里看 SELECT value FROM v$diag_info WHERE name Diag Trace;V$DIAG_INFO这条例句看着不起眼但它是整章里最实用的一个告警日志的位置在不同版本、不同平台上并不一样靠记忆去找经常找错目录。拿到路径之后grep -n ORA-挨个看升级期间产生的错误会集中出现在那一个时间段比翻应用日志快得多。6. 装完怎么验证三个断言确认时区真的对齐了升级动作跑完、数据库正常启动这一刻的判断最容易出问题v$timezone_file显示的版本变了人就会觉得成了。但版本号只是一个声明它不保证数据被正确重解释、不保证应用读出来的时间符合预期。我一般用三个断言来收尾全部通过才签字。第一个断言是版本层面的一致。所有实例、所有需要访问这个库的客户端看到的时区文件版本要能对得上-- 断言一服务端版本等于目标值 SELECT version, filename FROM v$timezone_file; -- 期望 version 32filename timezlrg_32.dat第二个断言是数据层面的一致性。用第三章导出的那份样本 CSV按完全相同的 SQL 和会话时区再导一次做逐行比对-- 断言二同一批数据两次导出结果一致会话时区必须与第一次相同 ALTER SESSION SET TIME_ZONE Asia/Shanghai; SELECT id, TO_CHAR(create_time AT TIME ZONE UTC, YYYY-MM-DD HH24:MI:SS) AS utc_time FROM app_order WHERE id BETWEEN 100000 AND 100100 ORDER BY id;# 两次导出做 diff只看差异行注意先统一换行符 diff -u sample_before.csv sample_after.csv | head -50这里的坑在于会话时区。第二次导出如果没设TIME_ZONE结果跟第一次不一样是必然的跟升级无关会白白折腾半天。所以比对脚本里把ALTER SESSION写死比在文档里写一句“注意时区”有用得多。第三个断言是应用层面的回归也是最不该省的一条。挑三到五个直接读 TSTZ 列的业务查询在升级前后各跑一次比对返回结果和响应时间。下面这张表是我自己用的检查清单每次升级照着填检查项通过标准不通过的常见原因服务端时区文件版本等于目标版本所有实例一致RAC 有节点未重启客户端时区文件版本不低于服务端驱动或客户端未更新样本数据比对差异行数为 0会话时区不一致或数据确实被改动受影响表统计信息最近一次收集时间在升级之后收尾步骤跳过应用关键查询结果与响应时间均在阈值内执行计划变差需重新收集统计三个断言都过这件事才算完。我见过太多人停在第一个断言版本号一对上就开始写报告结果第二天业务报过来某一批订单时间不对回头再查中间已经隔了一整天的写入样本比对的基础没了。从那以后我每次动手升级时区之前都强制先做三件事把v$timezone_file的输出截图贴进变更单、导一份样本数据、确认所有实例都进了同一份检查清单。这三件事加起来不超过二十分钟但它是唯一能在事后说清“到底变了什么”的依据也是这件事上唯一能买到的后悔药。希望帮到你。本文还有配套的精品资源点击获取