An example of ORA-01882 troubleshooting on Oracle 10.2.0.4

Upgrading any software, especially enterprise-level system components, is a very complicated process. The upgrade path, data retention plan, rollback steps, and impact degree of original business functions all require repeated tests and demonstrations. All O & M personnel should be cautious when encountering upgrade problems.
 
I recently took over an upgraded system and encountered some problems during the test. Solve the problem by searching for MOS and network resources. Record it and stay with the friends you need.

Recommended reading:

ORA-01172, ORA-01151 error handling

ORA-00600 [2662] troubleshooting

Troubleshooting for ORA-01078 and LRM-00109

Notes on ORA-00471 Processing Methods

ORA-00314, redolog corruption, or missing Handling Methods

Solution to ORA-00257 archive logs being too large to store

 

1. Environment Introduction

 

I took over a Linux upgrade to 10.2.0.4.

 

SQL> select * from v $ version;

BANNER

-----------------------------------------------

Oracle Database 10g Release 10.2.0.4.0-64bit Production

PL/SQL Release 10.2.0.4.0-Production

CORE 10.2.0.4.0 Production

TNS for Linux: Version 10.2.0.4.0-Production

NLSRTL Version 10.2.0.4.0-Production

 

2. fault issue display

 

During inspection, dba_scheduler_jobs, which records internal Oracle job scheduling, cannot be queried.

 

SQL> select * from dba_scheduler_jobs;

 

Select * from dba_scheduler_jobs

ORA-01882: Time Zone not found

 

The official explanation for the error 1882 is as follows:

 

[Oracle @ allfirst ~] $ Oerr ora 1, 1882

01882,000 00, "timezone region % s not found"

// * Cause: The specified region name was not found.

// * Action: Please contact Oracle Customer Support.

 

However, not all fields support query actions.

 

SQL> select owner, job_name from dba_scheduler_jobs;

 

OWNER JOB_NAME

------------------------------------------------------------

SYS SQLSCRIPT_4084880

SYS AUTO_SPACE_ADVISOR_JOB

SYS GATHER_STATS_JOB

Sys fgr $ AUTOPURGE_JOB

SYS PURGE_LOG

Exfsys rlm $ SCHDNEGACTION

Exfsys rlm $ EVTCLEANUP

ORACLE_OCM MGMT_STATS_CONFIG_JOB

ORACLE_OCM MGMT_CONFIG_JOB

 

9 rows selected

 

From the field nature, it is suspected that the field related to the Time Zone is Time Zone.

 

SQL> select column_name, data_type from dba_tab_columns where owner = 'sys 'and table_name = upper ('dba _ scheduler_jobs') and data_type like '% TIME ZONE % ';
 
 

COLUMN_NAME DATA_TYPE

------------------------------------------------------------

START_DATE TIMESTAMP (6) WITH TIME ZONE

END_DATE TIMESTAMP (6) WITH TIME ZONE

LAST_START_DATE TIMESTAMP (6) WITH TIME ZONE

NEXT_RUN_DATE TIMESTAMP (6) WITH TIME ZONE

 

However, not all time zone fields cannot be displayed. In addition, this problem does not occur in this view.

 

 

SQL> select start_date from dba_scheduler_jobs;

START_DATE

-----------------------------------------------------

-12 05.48.23.742796 PM +

28-1-14 02.42.49.000000 PM + 08:00

13-5-13 07.40.35.640706 PM +

13-5-13 07.32.40.314879 PM + 08:00

 

9 rows selected

 

SQL> select * from SYS. scheduler $ _ job;

Select * from SYS. scheduler $ _ job

 

ORA-01882: Time Zone not found

3. Problem Analysis

Intuitively, this error is caused by timezone-related values in some data tables of the database.

TimeZone is not only an environmental variable in Oracle, but also incorporated into the data value retention process. In the Oracle field type, there are only two fields related to the time zone: timestamp with time zone and timestamp with local time zone.
 
The Time Zone of Oracle is controlled by the time zone file. For databases of different versions, select the time zone file of different versions.

 

SQL> select * from v $ timezone_file;

FILENAME VERSION

----------------------

Timezlrg. dat 4

 

A frequent fault occurs when the time zone file is not updated during Database Upgrade. This causes the upgrade to fail. The Time Zone file belongs to the DST technology system. 10.2.0.2 uses DSTv2 for DST, DSTv3 for 10.2.0.3, and DSTv4 for 10.2.0.4. The DST version we just tested is correct.
 
Another characteristic of the time zone problem is the difference in server and client features. If you need to determine whether the problem is a server issue, you need to directly execute the command on the server.

 

[Oracle @ allfirst/] $ sqlplus/nolog

SQL * Plus: Release 10.2.0.4.0-Production on Tuesday January 28 14:54:51 2014

Copyright (c) 1982,200 7, Oracle. All Rights Reserved.

 

SQL> conn/as sysdba

Connected.

SQL> select * from dba_scheduler_jobs;

ERROR:

ORA-01882: Time Zone zone % s not found

 

It indicates a problem with the database server. If the problem is caused by a client, upgrade the Client Version in time.

A conjecture is that when Oracle is upgrading the version, the database Time Zone file is indeed upgraded, but the corresponding internal data is not updated yet. This causes incompatibility issues.

In MOS, We also checked the article discussed: Time Zone IDs for 7 Time Zones Changed in Time Zone Files Version 3 and Higher, Possible ORA-1882 After Upgrade (Document ID 414590.1 ).
 
This section describes how to use the check script to fix the vulnerability.

For more details, please continue to read the highlights on the next page:

1 2 Next Page