适用范围

OceanBase V3。

问题概述

前台应用程序报错 ORA-01850: hour must be between 0 and 23，截图如下：

问题排查

• 通过错误代码 ORA-01850 找到对应的 OceanBase 错误码 5645 https://www.oceanbase.com/docs/enterprise-oceanbase-database-cn-10000000000944896

[root@ocp ~]# ob_error ora 1850

Oracle:
        Oracle Error Code: ORA-01850
        Message: hour must be between 0 and 23
        Related OceanBase Error Code:
                OB_ERR_INVALID_HOUR24_VALUE(-5645)

• 查询视图 gv$sql_audit，ret_code=-5645

select /*+ query_timeout(100000000000) read_consistency(weak) parallel(16) */svr_ip, 
tenant_id, sid, date_format(usec_to_time(request_time),'%Y-%m-%d %H:%i:%s') request_time, 
sql_id, plan_id, elapsed_time, transaction_hash, execute_time, params_value  
from gv$sql_audit 
where usec_to_time(request_time) >= '2024-05-23 08:00:00' and ret_code=-5645 order by 3;

• 根据SQL_ID查看SQL文本，并和应用开发人员确认

select ifnull(query_sql,'') query_sql 
from oceanbase.gv$plan_cache_plan_stat 
where sql_id='45FFAE22C01366EDC7ADB64D159A908B' and query_sql<>'' limit 1\G

• 脱敏后的大致SQL如下

SELECT count(distinct t.servnumber) cnt FROM dbmt.test_table t 
where t.rid = 'Chg2' 
and t.rdate between to_date('20240523000000','yyyymmddhh24miss') and to_date('2024052385959','yyyymmddhh24miss');