http://tiany.blog.51cto.com/513694/1411882
Oracle 10gR2升级到Oracle 11gR2
当Oracle database从10gR2升级到11gR2之后，需要升级timezone version，以下详细介绍了timezone的升级过程。
Oracle timezone 升级
背景描述：
如果需要支持一个国际化的应用，那么数据库端的国际化特性的支持也就显得尤其重要。Oracle中有很多特性支持国际化，如字符集、时区等等。如果相关参数设置不当，或者由于对相关特性不够了解，以至于在设计阶段没有考虑完全，那么肯定会对应用造成一定的损失。
升级前准备：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL*Plus: Release
11.2.
0.1.
0
Production
on
Mon Mar
16
14
:
07
:
28
2015
Copyright (c)
1982
,
2009
, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release
11.2.
0.1.
0
- Production
With the Partitioning, OLAP, Data Mining
and
Real Application Testing options
SQL> select *
from
v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release
11.2.
0.1.
0
- Production
PL/SQL Release
11.2.
0.1.
0
- Production
CORE
11.2.
0.1.
0
Production
TNS
for
Linux: Version
11.2.
0.1.
0
- Production
NLSRTL Version
11.2.
0.1.
0
- Production
|
查看数据库当前timezone 版本：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
|
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
4
根据当前timezone的版本，又分三种情况：
1
）等于
14
：这已经是11g需要的版本了，所以升级前后都不需要做任何事，这种情况很罕见。
注意：
11.2.
0.1.
0
的timezone最高支持到
11
，可以通过升级数据库到
11.2.
0.3.
0
，将timezone升级到
14
SQL> select *
from
v$version;
BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise EditionRelease
11.2.
0.1.
0
- 64bit
Production
PL/SQL Release
11.2.
0.1.
0
- Production
CORE
11.2.
0.1.
0
Production
TNS
for
64
-bit Windows: Version
11.2.
0.1.
0
- Production
NLSRTL Version
11.2.
0.1.
0
– Production
SQL> SELECT NAME,VALUE$ FROM PROPS$WHERE
NAME=
'DST_PRIMARY_TT_VERSION'
;
NAME VALUE$
-----------------------------------------------------------------------
DST_PRIMARY_TT_VERSION
11
SQL> select *
from
v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise EditionRelease
11.2.
0.3.
0
- 64bit
Production
PL/SQL Release
11.2.
0.3.
0
- Production
CORE
11.2.
0.3.
0
Production
TNS
for
Linux: Version
11.2.
0.3.
0
-Production
NLSRTL Version
11.2.
0.3.
0
– Production
SQL> SELECT NAME,VALUE$ FROM PROPS$WHERE
NAME=
'DST_PRIMARY_TT_VERSION'
;
NAME VALUE$
-------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION
14
2
）高于
14
：升级前，必须得给11g软件打上该timezone版本的DST补丁，这种情况也很罕见。
3
）低于
14
：大多数都是这种情况，在升级前不需要在11g软件层面打补丁，在升级后需要再数据库层面将Timezone升级至
14
，具体看后面的步骤
SQL> set linesize
120
SQL> r
1
* SELECT PROPERTY_NAME, SUBSTR(property_value,
1
,
30
) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE
'DST_%'
ORDER BY PROPERTY_NAME
PROPERTY_NAME VALUE
------------------------------ --------------------------------------------------
DST_PRIMARY_TT_VERSION
4
DST_SECONDARY_TT_VERSION
0
DST_UPGRADE_STATE NONE
|
准备升级timezone到11：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
|
（升级到
14
出现以下错误）
SQL> exec DBMS_DST.BEGIN_PREPARE(
14
);
BEGIN DBMS_DST.BEGIN_PREPARE(
14
); END;
*
ERROR
at
line
1
:
ORA
-30094
: failed to find the time zone data file
for
version
14
in
$ORACLE_HOME/oracore/zoneinfo
ORA
-06512
:
at
"SYS.DBMS_DST"
, line
57
ORA
-06512
:
at
"SYS.DBMS_DST"
, line
1258
ORA
-06512
:
at
line
1
[oracle@rh55 ~]$ find $ORACLE_HOME -name
'zoneinfo'
/u01/app/oracle/product/
11.2.
0
/db_1/oracore/zoneinfo
[oracle@rh55 ~]$ ls -l /u01/app/oracle/product/
11.2.
0
/db_1/oracore/zoneinfo
total
10092
drwxr-xr-x
2
oracle oinstall
4096
Mar
13
11
:
45
big
drwxr-xr-x
2
oracle oinstall
4096
Mar
13
11
:
45
little
-rw-r--r--
1
oracle oinstall
5725
Jun
12
2009
readme.txt
-rw-r--r--
1
oracle oinstall
25681
Jul
16
2009
timezdif.csv
-rw-r--r--
1
oracle oinstall
792894
Jul
31
2009
timezlrg_10.dat
-rw-r--r--
1
oracle oinstall
787272
Jul
31
2009
timezlrg_11.dat
-rw-r--r--
1
oracle oinstall
493675
Jul
31
2009
timezlrg_1.dat
-rw-r--r--
1
oracle oinstall
507957
Jul
31
2009
timezlrg_2.dat
-rw-r--r--
1
oracle oinstall
527717
Jul
31
2009
timezlrg_3.dat
-rw-r--r--
1
oracle oinstall
531137
Jul
31
2009
timezlrg_4.dat
-rw-r--r--
1
oracle oinstall
587487
Jul
31
2009
timezlrg_5.dat
-rw-r--r--
1
oracle oinstall
586750
Jul
31
2009
timezlrg_6.dat
-rw-r--r--
1
oracle oinstall
601242
Jul
31
2009
timezlrg_7.dat
-rw-r--r--
1
oracle oinstall
616723
Jul
31
2009
timezlrg_8.dat
-rw-r--r--
1
oracle oinstall
801410
Jul
31
2009
timezlrg_9.dat
-rw-r--r--
1
oracle oinstall
345637
Jul
31
2009
timezone_10.dat
-rw-r--r--
1
oracle oinstall
345356
Jul
31
2009
timezone_11.dat
-rw-r--r--
1
oracle oinstall
274427
Jul
31
2009
timezone_1.dat
-rw-r--r--
1
oracle oinstall
274900
Jul
31
2009
timezone_2.dat
-rw-r--r--
1
oracle oinstall
286651
Jul
31
2009
timezone_3.dat
-rw-r--r--
1
oracle oinstall
286264
Jul
31
2009
timezone_4.dat
-rw-r--r--
1
oracle oinstall
286310
Jul
31
2009
timezone_5.dat
-rw-r--r--
1
oracle oinstall
286217
Jul
31
2009
timezone_6.dat
-rw-r--r--
1
oracle oinstall
286815
Jul
31
2009
timezone_7.dat
-rw-r--r--
1
oracle oinstall
302100
Jul
31
2009
timezone_8.dat
-rw-r--r--
1
oracle oinstall
351525
Jul
31
2009
timezone_9.dat
在zoneinfo目录下没有version
14
的时区文件
准备升级timezone到
11
：
SQL> exec DBMS_DST.BEGIN_PREPARE(
11
);
PL/SQL procedure successfully completed.
查看升级准备信息：
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value,
1
,
30
) value
2
FROM DATABASE_PROPERTIES
3
WHERE PROPERTY_NAME LIKE
'DST_%'
4
ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ----------------------------------------
DST_PRIMARY_TT_VERSION
4
DST_SECONDARY_TT_VERSION
11
DST_UPGRADE_STATE PREPARE
|
准备升级工作：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
SQL> BEGIN
2
DBMS_DST.FIND_AFFECTED_TABLES
3
(affected_tables =>
'sys.dst$affected_tables'
,
4
log_errors => TRUE,
5
log_errors_table =>
'sys.dst$error_table'
);
6
END;
7
/
PL/SQL procedure successfully completed.
SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
Table truncated.
SQL> TRUNCATE TABLE sys.dst$affected_tables;
Table truncated.
SQL> TRUNCATE TABLE sys.dst$error_table;
Table truncated.
SQL> SELECT * FROM sys.dst$affected_tables;
no rows selected
SQL>SELECT * FROM sys.dst$error_table;
no rows selected
SQL> SELECT * FROM sys.dst$error_table
where
ERROR_NUMBER=
'1883'
;
no rows selected
SQL> SELECT * FROM sys.dst$error_table
where
ERROR_NUMBER=
'1878'
;
no rows selected
SQL> SELECT * FROM sys.dst$error_table
where
ERROR_NUMBER
not
in
(
'1878'
,
'1883'
);
no rows selected
|
结束升级准备：
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL> EXEC DBMS_DST.END_PREPARE;
PL/SQL procedure successfully completed.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value,
1
,
30
) value
2
FROM DATABASE_PROPERTIES
3
WHERE PROPERTY_NAME LIKE
'DST_%'
4
ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ----------------------------------------
DST_PRIMARY_TT_VERSION
4
DST_SECONDARY_TT_VERSION
0
DST_UPGRADE_STATE NONE
|
升级过程：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
|
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area
627732480
bytes
Fixed Size
1338336
bytes
Variable Size
427820064
bytes
Database Buffers
192937984
bytes
Redo Buffers
5636096
bytes
Database mounted.
Database opened.
SQL> set serveroutput
on
SQL> purge dba_recyclebin;
DBA Recyclebin purged.
SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
Table truncated.
SQL> TRUNCATE TABLE sys.dst$affected_tables;
Table truncated.
SQL> TRUNCATE TABLE sys.dst$error_table;
Table truncated.
SQL> alter session set
"_with_subquery"
=materialize;
Session altered.
将timezone version升级到
11
：
SQL> EXEC DBMS_DST.BEGIN_UPGRADE(
11
);
PL/SQL procedure successfully completed.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value,
1
,
30
) value
2
FROM DATABASE_PROPERTIES
3
WHERE PROPERTY_NAME LIKE
'DST_%'
4
ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ----------------------------------------
DST_PRIMARY_TT_VERSION
11
DST_SECONDARY_TT_VERSION
4
DST_UPGRADE_STATE UPGRADE
SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES
where
UPGRADE_IN_PROGRESS=
'YES'
;
OWNER TABLE_NAME UPG
------------------------------ ------------------------------ ---
SYSMAN MGMT_PROV_NET_CONFIG YES
SYSMAN MGMT_PROV_IP_RANGE YES
SYSMAN MGMT_PROV_SUITE_INST_MEMBERS YES
SYSMAN MGMT_PROV_BOOTSERVER YES
SYSMAN AQ$_MGMT_NOTIFY_QTABLE_L YES
SYSMAN AQ$_MGMT_LOADER_QTABLE_S YES
SYSMAN AQ$_MGMT_LOADER_QTABLE_L YES
SYSMAN AQ$_MGMT_NOTIFY_QTABLE_S YES
SYSMAN MGMT_PROV_STAGING_DIRS YES
SYSMAN MGMT_PROV_OPERATION YES
SYSMAN MGMT_PROV_ASSIGNMENT YES
OWNER TABLE_NAME UPG
------------------------------ ------------------------------ ---
SYSMAN MGMT_CONFIG_ACTIVITIES YES
SYSMAN MGMT_PROV_CLUSTER_NODES YES
SYSMAN MGMT_PROV_RPM_REP YES
SYSMAN MGMT_PROV_DEFAULT_IMAGE YES
IX AQ$_STREAMS_QUEUE_TABLE_S YES
IX AQ$_STREAMS_QUEUE_TABLE_L YES
IX AQ$_ORDERS_QUEUETABLE_S YES
IX AQ$_ORDERS_QUEUETABLE_L YES
19
rows selected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area
627732480
bytes
Fixed Size
1338336
bytes
Variable Size
427820064
bytes
Database Buffers
192937984
bytes
Redo Buffers
5636096
bytes
Database mounted.
Database opened.
SQL> alter session set
"_with_subquery"
=materialize;
Session altered.
执行timezone升级过程：
SQL> set serveroutput
on
SQL> VAR numfail number
SQL> BEGIN
2
DBMS_DST.UPGRADE_DATABASE(:numfail,
3
parallel => TRUE,
4
log_errors => TRUE,
5
log_errors_table =>
'SYS.DST$ERROR_TABLE'
,
6
log_triggers_table =>
'SYS.DST$TRIGGER_TABLE'
,
7
error_on_overlap_time => FALSE,
8
error_on_nonexisting_time => FALSE);
9
DBMS_OUTPUT.PUT_LINE(
'Failures:'
|| :numfail);
10
END;
11
/
Table list: SYSMAN.MGMT_PROV_SUITE_INST_MEMBERS
Number
of failures:
0
Table list: SYSMAN.MGMT_PROV_STAGING_DIRS
Number
of failures:
0
Table list: SYSMAN.MGMT_PROV_RPM_REP
Number
of failures:
0
Table list: SYSMAN.MGMT_PROV_OPERATION
Number
of failures:
0
Table list: SYSMAN.MGMT_PROV_NET_CONFIG
Number
of failures:
0
Table list: SYSMAN.MGMT_PROV_IP_RANGE
Number
of failures:
0
Table list: SYSMAN.MGMT_PROV_DEFAULT_IMAGE
Number
of failures:
0
Table list: SYSMAN.MGMT_PROV_CLUSTER_NODES
Number
of failures:
0
Table list: SYSMAN.MGMT_PROV_BOOTSERVER
Number
of failures:
0
Table list: SYSMAN.MGMT_PROV_ASSIGNMENT
Number
of failures:
0
Table list: SYSMAN.MGMT_CONFIG_ACTIVITIES
Number
of failures:
0
Table list: SYSMAN.AQ$_MGMT_NOTIFY_QTABLE_S
Number
of failures:
0
Table list: SYSMAN.AQ$_MGMT_NOTIFY_QTABLE_L
Number
of failures:
0
Table list: SYSMAN.AQ$_MGMT_LOADER_QTABLE_S
Number
of failures:
0
Table list: SYSMAN.AQ$_MGMT_LOADER_QTABLE_L
Number
of failures:
0
Table list: IX.AQ$_STREAMS_QUEUE_TABLE_S
Number
of failures:
0
Table list: IX.AQ$_STREAMS_QUEUE_TABLE_L
Number
of failures:
0
Table list: IX.AQ$_ORDERS_QUEUETABLE_S
Number
of failures:
0
Table list: IX.AQ$_ORDERS_QUEUETABLE_L
Number
of failures:
0
Failures:
0
PL/SQL procedure successfully completed.
结束升级，校验升级信息：
SQL> VAR fail number
SQL> BEGIN
2
DBMS_DST.END_UPGRADE(:fail);
3
DBMS_OUTPUT.PUT_LINE(
'Failures:'
|| :fail);
4
END;
5
/
An upgrade window has been successfully ended.
Failures:
0
PL/SQL procedure successfully completed.
|
确认升级成功：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value,
1
,
30
) value
2
FROM DATABASE_PROPERTIES
3
WHERE PROPERTY_NAME LIKE
'DST_%'
4
ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ----------------------------------------
DST_PRIMARY_TT_VERSION
11
DST_SECONDARY_TT_VERSION
0
DST_UPGRADE_STATE NONE
SQL> SELECT * FROM v$timezone_file;
FILENAME VERSION
-------------------- ----------
timezlrg_11.dat
11
|
本文转自 客居天涯 51CTO博客，原文链接：http://blog.51cto.com/tiany/1621119，如需转载请自行联系原作者
