ALLSTARS_ORACLE 发表于 2014-10-20 20:03:43

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

数据库升级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

ALLSTARS_ORACLE 发表于 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$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 发表于 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;
页: [1]
查看完整版本: 数据库升级11g文档 Hp-Ia64 平台