如何去除时区升级模式

郑全

在11g 升级到12c的过程中,只要有一个错误没有解决,就不能下一步,
如果我们在检查前提条件中,发现了 时区处于升级模式,那么,如何来消除呢?

我们引用了mos上的一篇文章,使用这个文章中的方法可以解决:

Applies to:   Oracle Exadata Storage Server Software - Version 11.2.2.3.0 to 11.2.3.2.1 [Release 11.2]
Oracle Exadata Hardware - Version 11.2.0.1 and later
Exadata Database Machine V2 - Version All Versions and later
Exadata Database Machine X2-2 Hardware - Version All Versions and later
Exadata Database Machine X2-8 - Version All Versions and later
Information in this document applies to any platform.

Symptoms RDS (and database) performance suffers when database is in timezone upgrade mode.

To determine if the database is in timezone upgrade mode you might see something like:
select name, value$ from sys.props$ where name like '%DST%';

DST_UPGRADE_STATE DATAPUMP(1)
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 11
or
select property_value from sys.database_properties where property_name = 'DST_UPGRADE_STATE';

PROPERTY_VALUE
------------------------------------------------------------------------------
DATAPUMP(1)

In an AWR or Statspack report the 'cell num smart IO sessions using passthru mode due to timezone' statistic is seen to be increasing.  

And in v$cell_state, you would see:

<noopt_reasons group="Predicate Noopt Reasons" name="Smart scan">
<stat name="Smart IO uses passthru as timezone file is unavailable">390</stat>

ChangesTimezone was recently changed (usually to and from daylight savings time).
CauseWhen the DB timezone is in upgrade mode or inconsistent mode, I/Os issued from DB nodes to cell nodes will not go through smart scan and hence we do block I/O instead.  This results in cell nodes shipping all blocks rather than blocks of interest (filtered) to the database. If the I/O size is huge the IO might saturate the RDS traffic and impact the RDA service times along with database performance.
Solution 1. Enable event 30090:
SQL> ALTER SESSION SET EVENTS '30090 TRACE NAME CONTEXT FOREVER, LEVEL 32';
  
2. Execute the following:

SQL> exec dbms_dst.unload_secondary;

3. Validate that the database is not in DST upgrade mode

SQL> select property_value from sys.database_properties where property_name =  'DST_UPGRADE_STATE';
  
PROPERTY_VALUE
----------------------------------
NONE

SQL> select name, value$ from sys.props$ where name like '%DST%';
  

NAME
------------------------------
VALUE$
  
------------------------------------------------------------------------------
DST_UPGRADE_STATE
NONE

DST_PRIMARY_TT_VERSION
14

DST_SECONDARY_TT_VERSION
0