999 积分	1 好友	942 主题

发消息

[ORA-600/ORA-7445] 数据库升级11g文档 Hp-Ia64 平台

1^#

发表于 2014-10-20 20:03:43 | 查看: 5834| 回复: 2

数据库升级11g文档 Hp-Ia64 平台

软件要求
兼容列表
根据Oracle官方文档，给出下列软件兼容列表：
Product:  Oracle Server - Enterprise Edition
Product Release: 11gR2
Product Version: 11gR2
Platform: HP-IA 64
Platform Version: 11.31
Certification Status: Certified

系统配置
操作系统版本： HP-IA 64bit 11.31
HP Serviceguard extension for RAC A.11.20.00

补丁要求
                     HP-UX 11i V3 patch Bundle Sep/ 2008 (B.11.31.0809.326a) or higher
系统包和组件
For HP-UX 11i V3 (11.31):
PHCO_40381 11.31 Disk Owner Patch
PHCO_41479 11.31 (fixes an 11.2.0.2 ASM disk discovery issue)
PHKL_38038 VM patch - hot patching/Core file creation directory
PHKL_38938 11.31 SCSI cumulative I/O patch
PHKL_39351 Scheduler patch : post wait hang
PHSS_36354 11.31 assembler patch
PHSS_37042 11.31 hppac (packed decimal)
PHSS_37959 Libcl patch for alternate stack issue fix (QXCR1000818011)
PHSS_39094 11.31 linker + fdp cumulative patch
PHSS_39100 11.31 Math Library Cumulative Patch
PHSS_39102 11.31 Integrity Unwind Library
PHSS_38141 11.31 aC++ Runtime
Pro*C/C++, Oracle Call Interface, Oracle C++
Pro*C/C++, Oracle Call Interface, Oracle C++ Call Interface, Oracle XML Developer's Kit (XDK):-
Patch for HP-UX 11i V3 (11.31) on HP-UX Itanium:-
PHSS_39824 - 11.31 HP C/aC++ Compiler (A.06.23) patch
                     SSH
BASH
（bash-4.2.045-ia64-11.31.depot，termcap-1.3.1-ia64-11.31.depot，gettext-0.18.2.1-ia64-11.31.depot，libiconv-1.14-ia64-11.31.depot）


介质要求
Oracle 9.2.0.8 （生成环境tar包）
Oracle11gR2软件
                                    p1040452_112030_HPUX-IA64_1of7.zip
      p1040452_112030_HPUX-IA64_2of7.zip
Oracle11gR2独立补丁
         Oracle10gR2独立补丁包括针对特定平台的补丁更新（PSU）以及推荐推补丁
                                       p14727347_112030_HPUX-IA64(11.2.0.3.5 update Jan-13)
         p6880880_112000_HPUX-IA64(OPatch)
         p14810756_112031_HPUX-IA64 (raw device support)


用户和组
创建dba组和oracle用户（两台主机一致）
                     Home路径： /home/oracle

设置用户环境变量

以用户oracle登陆，编辑其$HOME/.profile文件，添加针对11gR2安装的信息如下：
下面是初始设置：
.profile_db

.profile_grid

.profile_9i

缺省.profile使用db环境


异步I/O
　　　　　　设置异步IO:

#mknod /dev/async c 101 0x4
#chmod 660 /dev/async
#chown oracle:dba /dev/async

# /usr/sbin/setprivgrp dba MLOCK TTSCHED RTPRIO
# vi /etc/privgroup
This should contain dba MLOCK RTSCHED RTPRIO
# cat /etc/privgroup
   dba MLOCK RTSCHED RTPRIO
# setprivgrp –f /etc/privgroup


系统参数
在每台机器上，以用户root登陆，修改系统参数。如果某些参数值已经大于这里的推荐值，则无需修改。
HP-IA参数要求：
Parameter Recommended Formula or Value
executable_stack 0
ksi_alloc_max 32768
max_thread_proc 1024
Maxdsiz 1073741824 (1 GB)
maxdsiz_64bit 2147483648 (2 GB)
Maxfiles 1024
maxfiles_lim 63488
Maxssiz 134217728(128M)
maxssiz_64bit 1073741824(1GB)
Maxuprc 3686
Msgmni 4096
Msgtql 4096
Ncsize 35840
Nflocks 4096
Ninode 34816
Nkthread 7184
Nproc 4096
Semmn6 4096
Semmns 8192
Semmnu 4092
Semvmx 32767
Max_async_ports 8192
Shmmax 1073741824(可以到物理内存)
Shmmni 4096
Shmseg 512


网络参数

Parameter Recommended Formula or Value
tcp_smallest_anon_port 9000
tcp_largest_anon_port 65500
udp_smallest_anon_port 9000
udp_largest_anon_port 65500

11g 软件安装


安装11g R2  Gird Infrastructure
安装Gird (11.2.0.3)
1. $ cd /oracle/soft/grid
2. $ ./runInstaller
指定11g ORACLE_BASE为/home/oracle/oracle,ORACLE_HOME为/oracle/11.2.0/grid
3. 指定cluster名字为系统名-cluser(crmcx-cluster?不能超过15),指定scan为系统名-scan(crmcx-scan)
4. 指定asm diskgroup 为 CRS
5. 安装完成以后，运行root.sh(双节点分别运行),退出

$ srvctl enable oc4j (启用oc4j)
$ crs_stat –t

Name          Type          Target State    Host
------------------------------------------------------------
ora.CRS.dg    ora....up.type ONLINE ONLINE idcy...b01s
ora....ER.lsnr ora....er.type ONLINE ONLINE idcy...b01s
ora....N1.lsnr ora....er.type ONLINE ONLINE idcy...b01s
ora.asm       ora.asm.type ONLINE ONLINE idcy...b01s
ora.cvu       ora.cvu.type ONLINE ONLINE idcy...b01s
ora.gsd       ora.gsd.type ONLINE ONLINE idcy...b01s
ora....SM1.asm application ONLINE ONLINE idcy...b01s
ora....1S.lsnr application ONLINE ONLINE idcy...b01s
ora....01s.gsd application ONLINE ONLINE idcy...b01s
ora....01s.ons application ONLINE ONLINE idcy...b01s
ora....01s.vip ora....t1.type ONLINE ONLINE idcy...b01s
ora....SM2.asm application ONLINE ONLINE idcy...b02s
ora....2S.lsnr application ONLINE ONLINE idcy...b02s
ora....02s.gsd application ONLINE ONLINE idcy...b02s
ora....02s.ons application ONLINE ONLINE idcy...b02s
ora....02s.vip ora....t1.type ONLINE ONLINE idcy...b02s
ora....network ora....rk.type ONLINE ONLINE idcy...b01s
ora.oc4j    ora.oc4j.type  ONLINE ONLINE idcy...b01s
ora.ons       ora.ons.type ONLINE ONLINE idcy...b01s
ora.scan1.vip  ora....ip.type ONLINE ONLINE idcy...b01s

安装完成以后修改目录属性：
# chown oracle:dba /oracle
# chown oracle:dba /oracle/11.2.0
# chown oracle:dba /oracle/11.2.0/grid
# chown oracle:dba /oracle/11.2.0/grid/lib
# chown oracle:dba /oracle/11.2.0/grid/bin

安装RDBMS （11.2.0.3）
1. $ cd /oracle/soft/database
2. ./runInstaller
指定11g ORACLE_BASE 为/home/oracle/oracle,ORACLE_HOME为/oracle/11.2.0/db
3. 安装完成以后运行root.sh(双节点分别运行)


安装PSU补丁（11.2.0.3.5 ）
1. $ cd /home/oracle/patches
2. $ unzip p14727347_112030_HPUX-IA64.zip(PSU 11.2.0.3.5)
3. $ORACLE_HOME/OPatch/ocm/bin/emocmrsp -no_banner(生成ocm.rsp)
4. #opatch auto /home/oracle/patches -och /oracle/11.2.0/grid –oh /oracle/11.2.0/db -ocmrf /home/oracle/patchesocm.rsf

分享0

下载专业ORACLE数据库恢复工具PRM-DUL For Oracle http://www.parnassusdata.com/

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线： 13764045638 QQ: 47079569 邮箱：service@parnassusdata.com

收藏0 回复只看该作者道具举报

ALLSTARS_ORACLE

2^#

发表于 2014-10-20 20:04:13

安装独立补丁
我们一般不建议安装太多的one-off补丁，通常遇到问题时，才安装有针对性、解决该问题的补丁。此次集成，需要安装的独立补丁包括官方推荐的补丁和关键补丁。
1. 安装补丁p14810756_112031_HPUX-IA64
$ cd /home/oracle/patches
$unzip p14810756_112031_HPUX-IA64.zip
$cd p14810756
$opatch apply –local ($ORACLE_HOME->grid home)
两个节点分别运行
$opatch apply –local ($ORACLE_HOME->db home)
两个节点分别运行


补丁列表

dcyycxdb01s[/home/oracle/patches/14810756]$opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.3
Copyright (c) 2012, Oracle Corporation.  All rights reserved.

Oracle Home    : /oracle/11.2.0/db
Central Inventory : /home/oracle/oraInventory
from          : /oracle/11.2.0/db/oraInst.loc
OPatch version : 11.2.0.3.3
OUI version    : 11.2.0.3.0
Log file location : /oracle/11.2.0/db/cfgtoollogs/opatch/opatch2013-04-10_10-56-14AM_1.log

Lsinventory Output file location : /oracle/11.2.0/db/cfgtoollogs/opatch/lsinv/lsinventory2013-04-10_10-56-14AM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g                                                 11.2.0.3.0
There are 1 products installed in this Oracle Home.

Interim patches (3) :

Patch  14810756    : applied on Wed Apr 10 10:55:45 GMT+08:00 2013
Unique Patch ID:  15903992
Created on 29 Jan 2013, 23:44:18 hrs PST8PDT
Bugs fixed:
   14810756
This patch overlays patches:
   13343438
This patch needs patches:
   13343438
as prerequisites

Patch  14727310    : applied on Mon Apr 08 15:07:18 GMT+08:00 2013
Unique Patch ID:  15669086
Patch description:  "Database Patch Set Update : 11.2.0.3.5 (14727310)"
Created on 1 Jan 2013, 05:02:20 hrs PST8PDT
Sub-patch  14275605; "Database Patch Set Update : 11.2.0.3.4 (14275605)"
Sub-patch  13923374; "Database Patch Set Update : 11.2.0.3.3 (13923374)"
Sub-patch  13696216; "Database Patch Set Update : 11.2.0.3.2 (13696216)"
Sub-patch  13343438; "Database Patch Set Update : 11.2.0.3.1 (13343438)"
Bugs fixed:
   13566938, 13593999, 10350832, 14138130, 12919564, 13624984, 13588248
   13080778, 13804294, 14258925, 12873183, 13645875, 12880299, 14664355
   14409183, 12998795, 14469008, 13719081, 13492735, 12857027, 14263036
   14263073, 13742433, 13732226, 12905058, 13742434, 12849688, 12950644
   13742435, 13464002, 12879027, 13534412, 14613900, 12585543, 12535346
   12588744, 11877623, 12847466, 13649031, 13981051, 12582664, 12797765
   14262913, 12923168, 13612575, 13384182, 13466801, 13484963, 11063191
   13772618, 13070939, 12797420, 13041324, 12976376, 11708510, 13742437
   13026410, 13737746, 13742438, 13326736, 13001379, 13099577, 14275605
   13742436, 9873405, 9858539, 14040433, 12662040, 9703627, 12617123
   12845115, 12764337, 13354082, 13397104, 12964067, 13550185, 12780983
   12583611, 14546575, 13476583, 15862016, 11840910, 13903046, 15862017
   13572659, 13718279, 13657605, 13448206, 13419660, 14480676, 13632717
   14063281, 13430938, 13467683, 13420224, 14548763, 12646784, 14035825
   12861463, 12834027, 15862021, 13377816, 13036331, 14727310, 13685544
   13499128, 15862018, 12829021, 15862019, 12794305, 14546673, 12791981
   13503598, 13787482, 10133521, 12718090, 13399435, 14023636, 12401111
   13257247, 13362079, 12917230, 13923374, 14480675, 13524899, 13559697
   14480674, 13916709, 14076523, 13773133, 13340388, 13366202, 13528551
   12894807, 13343438, 13454210, 12748240, 14205448, 13385346, 15853081
   12971775, 13035804, 13544396, 13035360, 14062795, 12693626, 13332439
   14038787, 14062796, 12913474, 14841409, 14390252, 13370330, 14062797
   13059165, 14062794, 12959852, 13358781, 12345082, 12960925, 9659614
   13699124, 14546638, 13936424, 13338048, 12938841, 12658411, 12620823
   12656535, 14062793, 12678920, 13038684, 14062792, 13807411, 12594032
   13250244, 15862022, 9761357, 12612118, 13742464, 14052474, 13457582
   13527323, 15862020, 12780098, 13502183, 13705338, 13696216, 10263668
   15862023, 13554409, 15862024, 13103913, 13645917, 14063280, 13011409

Patch  15876003    : applied on Mon Apr 08 14:59:54 GMT+08:00 2013
Unique Patch ID:  15870602
Patch description:  "Grid Infrastructure Patch Set Update : 11.2.0.3.5 (14727347)"
Created on 15 Jan 2013, 23:41:45 hrs PST8PDT
Bugs fixed:
   15876003, 14275572, 13919095, 13696251, 13348650, 13498267, 13430715
   14271305, 14082976, 12897902, 13719731, 13550689, 14242977, 12849377
   12975811, 13965075, 12950823, 12829429, 12538907, 13987807, 12848480
   12917897, 12726222, 12794268, 13002015, 13001901, 13789135, 12659561
   12965049, 13082238, 10114953, 13332363, 13396284, 13079948, 14168708
   12925041, 13066371, 12996428, 12995950, 12398492, 13111013, 12934171
   12765467, 12730342, 12829917, 12771830, 13924431, 13620816, 12876314
   13090686, 13241779, 10418841, 12594616, 13039908, 14407395, 13879428
   13037709, 15983149, 14102704, 13058611, 12758736, 13727853, 12878750
   13000491, 13024624, 13460353, 13531373, 13582411, 12914824, 13569812
   13371153, 13523527, 13339443, 13074261, 10260842, 13323698, 12897651
   14277586, 12728585, 13440962, 13947200, 12959140, 13652088, 12832204
   13938166, 12709476, 12885323, 13036424, 12947871, 12896850, 13334158
   13483672, 13001955, 12784559, 13776758, 11675721, 13825231, 14096821
   12639013, 12558569, 13540563, 11836951, 13077654, 12791719, 13857364
   12971251, 14625969, 12349553, 13869978, 13653178, 13085732, 12720728
   12857064, 12680491, 13355963, 13251796, 13811209, 13806545, 12990582
   13425727, 13993634, 12867511, 11772838, 14001941, 13634583, 10317921
   13023609, 13011520, 12899169, 13843080, 12650672

Rac system comprising of multiple nodes
  Local node = idcyycxdb01s
  Remote node = idcyycxdb02s

升级到11g R２

升级环境准备：
9i 环境启动数据库

1. . ./.profile_9i
2. Sqlplus “/as sysdba”
Sql>startup
3. Sql>@utlu112i_5.sql
4. 检查connect 权限
SELECT grantee FROM dba_role_privs
WHERE granted_role = 'CONNECT' and
grantee NOT IN (
'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
'WMSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');

RANTEE                      PRIVILEGE
------------------------------ ----------------------------------------
CONNECT                      CREATE VIEW
CONNECT                      CREATE TABLE
CONNECT                      ALTER SESSION
CONNECT                      CREATE CLUSTER
CONNECT                      CREATE SESSION
CONNECT                      CREATE SYNONYM
CONNECT                      CREATE SEQUENCE
CONNECT                      CREATE DATABASE LINK

生成授权connect的sql (grant_connect.sql)
Sql>
set pagesize 0
spool grant_connect.sql
SELECT 'grant create view,create table,alter session,create cluster,
create session,create synonym,create sequence,create database link to
'||grantee||';' FROM dba_role_privs
WHERE granted_role = 'CONNECT' and
grantee NOT IN (
'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
'WMSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');
spool off

5. 生成dblink重建脚本：
Sql>
set feedback off
spool create_dblink.sql
SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)
||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING
'''||L.HOST||''''
||chr(10)||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#;
spool off

6. 执行系统对象分析：
EXECUTE dbms_stats.gather_schema_stats('SYS',options=>'GATHER',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FOR ALL COLUMNS SIZE AUTO',cascade=>TRUE);
EXECUTE dbms_stats.gather_schema_stats('SYSTEM',options=>'GATHER',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FOR ALL COLUMNS SIZE AUTO',cascade=>TRUE);
EXECUTE dbms_stats.gather_schema_stats('OUTLN',options=>'GATHER',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FOR ALL COLUMNS SIZE AUTO',cascade=>TRUE);
EXECUTE dbms_stats.gather_schema_stats('DBSNMP',options=>'GATHER',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FOR ALL COLUMNS SIZE AUTO',cascade=>TRUE);
EXECUTE dbms_stats.gather_schema_stats('WMSYS',options=>'GATHER',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FOR ALL COLUMNS SIZE AUTO',cascade=>TRUE);

回复只看该作者道具举报

ALLSTARS_ORACLE

3^#

发表于 2014-10-20 20:04:29

7. 分析系统对象（可选）
Sql>
Set verify off
Set space 0
Set line 120
Set heading off
Set feedback off
Set pages 1000
Spool analyze.sql

SELECT 'Analyze cluster "'||cluster_name||'" validate structure cascade;'
FROM dba_clusters
WHERE owner='SYS'
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='NO'
AND (iot_type='IOT' OR iot_type is NULL)
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade into invalid_rows;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='YES';

spool off
Sql> analyze.sql

8. 确认job工作情况：
SELECT DISTINCT(TRUNC(last_refresh))
FROM dba_snapshot_refresh_times;
停止job(crmcx环境需要停止复制REPLX30)

9. 确认没有备份在进行：

SELECT * FROM v$recover_file;
SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

10. 确认没有pending 事务：

SQL> select * from dba_2pc_pending;

11. 停止数据库及监听：
$lsnrctl stop
$sqlplus “/as sysdba”
$sql>shutdown immediate

开始升级

执行 .profile_db
（设定新的$ORACLE_HOME,$ORACLE_BASE,PATH）
使用新的initcrmcx1.ora (生成spfile)
……
diag_dest=                            log_archive_format=
compatible=10.1.0
……

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> startup UPGRADE

SQL> CREATE TABLESPACE SYSAUX
   DATAFILE '/dev/ora_yycxdbvg07/xxxx'
   SIZE 4000M REUSE
   EXTENT MANAGEMENT LOCAL
   SEGMENT SPACE MANAGEMENT AUTO
   ONLINE;

SQL> set echo on
SQL> SPOOL upgrade.log
SQL> @catupgrd.sql
（脚本会自动关闭数据库）

12. 检查升级状况
SQL> STARTUP
SQL> @utlu112s.sql
Oracle Database 11.2 Post-Upgrade Status Tool          04-08-2013 18:39:33
.
Component                            Current    Version    Elapsed Time
Name                                  Status    Number    HH:MM:SS
.
Oracle Server
.                                        VALID    11.2.0.3.0  00:11:58
Oracle Real Application Clusters
.                                        VALID    11.2.0.3.0  00:00:00
Oracle Workspace Manager
.                                        VALID    11.2.0.3.0  00:00:45
Gathering Statistics
.                                                             00:01:24
Total Upgrade Time: 00:14:10

PL/SQL procedure successfully completed.

13. 运行升级后脚本
SQL> @catuppst.sql（包含PSU脚本）

SQL> @utlrp.sql

14. 授权
Sql>@grant_connect.sql

15. 重新建立dblink
Sql>create_dblink.sql

16. 准备升级TimeZone 版本
检查目前TimZone情况
SQL> conn / as sysdba
Connected.
SQL>SELECT version FROM v$timezone_file;

VERSION
----------
1

SQL>
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 <the old DST version number>
-- DST_SECONDARY_TT_VERSION 0 <<<<------ THIS NEEDS TO BE "0" !!!
-- DST_UPGRADE_STATE NONE <<<<------ THIS NEEDS TO BE "NONE" !!!

purge dba_recyclebin;

alter session set "_with_subquery"=materialize;

alter session set "_simple_view_merging"=TRUE;

exec DBMS_DST.BEGIN_PREPARE(14);

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

-- ------------------------------ ------------------------------
-- DST_PRIMARY_TT_VERSION <the old DST version number>
-- DST_SECONDARY_TT_VERSION <the new DST version number>
-- DST_UPGRADE_STATE PREPARE

TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;

set serveroutput on
BEGIN
DBMS_DST.FIND_AFFECTED_TABLES
(affected_tables => 'sys.dst$affected_tables',
log_errors => TRUE,
log_errors_table => 'sys.dst$error_table');
END;
/

SELECT * FROM sys.dst$affected_tables;

SELECT * FROM sys.dst$error_table;

EXEC DBMS_DST.END_PREPARE;

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

-- output should be
-- PROPERTY_NAME VALUE
-- ---------------------------- ------------------------------
-- DST_PRIMARY_TT_VERSION <the old DST version number>
-- DST_SECONDARY_TT_VERSION 0
-- DST_UPGRADE_STATE NONE

17. 正式升级TimeZone

conn / as sysdba
shutdown immediate;
startup upgrade;
set serveroutput on

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

purge dba_recyclebin;

TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;

alter session set "_with_subquery"=materialize;
alter session set "_simple_view_merging"=TRUE;

EXEC DBMS_DST.BEGIN_UPGRADE(14);

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

-- gives this output:
-- PROPERTY_NAME VALUE
-- --------------------------- ------------------------------
-- DST_PRIMARY_TT_VERSION <the new DST version number>
-- DST_SECONDARY_TT_VERSION <the old DST version number>
-- DST_UPGRADE_STATE UPGRADE

SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';

shutdown immediate
startup

alter session set "_with_subquery"=materialize;
alter session set "_simple_view_merging"=TRUE;

set serveroutput on
VAR numfail number
BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel => TRUE,
log_errors => TRUE,
log_errors_table => 'SYS.DST$ERROR_TABLE',
log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time => FALSE,
error_on_nonexisting_time => FALSE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/

VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

-- needed output:
-- PROPERTY_NAME VALUE
-- ---------------------------- ------------------------------
-- DST_PRIMARY_TT_VERSION <the new DST version number>
-- DST_SECONDARY_TT_VERSION 0
-- DST_UPGRADE_STATE NONE

SELECT * FROM v$timezone_file;

Your database DST version is now updated to 14
conn / as sysdba
SELECT VERSION FROM v$timezone_file;
select TZ_VERSION from registry$database;

conn / as sysdba
update registry$database set TZ_VERSION = 14;

commit;

18. EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

回复只看该作者道具举报

返回列表

		自动登录	找回密码
密码			注册