sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【案例】Oracle数据库升级 10.2.0.5升级至11.2.0.2详细步骤

时间:2016-11-24 20:09   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心案例分析:分享一篇关于Oracle数据库升级的笔记,详细记录了Oracle 10.2.0.5升级至11.2.0.2的详细步骤与遇到问题的处理办法。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: Step by Step oracle database 10gR2 upgrade to 11.2.0.2

1. 安装11.2.0.2.0 software;

安装软件之前,需要先安装如下的包:

-- 对HP IA64平台,安装11.2之前,需要安装如下的操作os patch:

PHCO_40381
PHKL_38691
PHKL_38762
PHKL_38938
PHKL_39351
PHSS_36354
PHSS_37042
PHSS_37959
PHSS_38141
PHSS_39100
PHSS_39102

其中PHKL_39351单独是无法安装的,安装过程中提示需要安装如下

依赖相关的包:

-- PHKL_39351

Patch Dependencies:

        11.31: PHKL_36142 PHKL_36699 PHKL_37465
                PHKL_38691 PHKL_37650

但是安装上面的包时,又需要其他的包,hp真是BT,无奈之下我只能下载如下的zip包,
同时把所有依赖patch全部下载,然后安装该zip包,即可。

hpux_11.31_11180102.zip

-- 安装11.2.0.2以后,安装如下两个重要的one-off patch

    Bug 10368698
    Bug 10419629

2. 运行dbupgdiag.sql进行检查(或运行@ utlu112i.sql)

$ cd 
$ sqlplus / as sysdba

sql> alter session set nls_language='American';
sql> @dbupgdiag.sql
sql> exit

该脚本需要从Mos上下载。(个人感觉运行两个脚本结合一起用比较好)

该脚本运行情况如下:

SQL> @ dbupgdiag.sql

Enter location for Spooled output:

19_Nov_2011_0837       .log

DMSBHMC_
SP2-0606: Cannot create SPOOL file "0/db_upg_diag_DMSBHMC_19_Nov_2011_0837.log"

                          *** Start of LogFile ***

  Oracle Database Upgrade Diagnostic Utility       11-19-2011 20:37:11

===============
Hostname
===============

dmsdbp02

===============
Database Name
===============

DMSBHMC

===============
Database Uptime
===============

20:03 19-NOV-11

=================
Database Wordsize
=================

This is a 64-bit database

================
Software Version
================

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 HPUX: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

=============
Compatibility
=============

Compatibility is set as 10.2.0.3.0

================
Archive Log Mode
================

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /ora_arch/DMSBHMC/
Oldest online log sequence     38503
Next log sequence to archive   38505
Current log sequence           38505

================
Auditing Check
================

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /ora_dump/DMSBHMC/adump/
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      NONE

===========================================
Tablespace and the owner of the aud$ table
===========================================

OWNER        TABLESPACE_NAME
------------ ------------------------------
SYS          SYSTEM

============================================================================
count of records in the sys.aud$ table where dbid is null- Standard Auditing
============================================================================

         0

============================================================================================
count of records in the system.aud$ when dbid is null,Std Auditing with OLS or DV installed
============================================================================================
select count(*) from system.aud$ where dbid is null
                            *
ERROR at line 1:
ORA-00942: table or view does not exist

=============================================================================
count of records in the sys.fga_log$ when dbid is null,Fine Grained Auditing
=============================================================================

         0

==========================================
Oracle Label Security is installed or not
==========================================

Oracle Label Security is NOT installed at database level

================
Number of AQ Records in Message Queue Tables
================

SYS - ALERT_QT - 28968
SYS - AQ$_MEM_MC - 0
SYS - AQ_EVENT_TABLE - 0
SYS - AQ_SRVNTFN_TABLE - 0
SYS - KUPC$DATAPUMP_QUETAB - 0
SYS - SCHEDULER$_EVENT_QTAB - 0
SYS - SCHEDULER$_JOBQTAB - 0
SYS - SYS$SERVICE_METRICS_TAB - 0
SYSMAN - MGMT_NOTIFY_QTABLE - 0
SYSTEM - DEF$_AQCALL - 0
SYSTEM - DEF$_AQERROR - 0
WMSYS - WM$EVENT_QUEUE_TABLE - 0

================
Time Zone version
================

         4

================
Local Listener
================

================
Default and Temporary Tablespaces By User
================

USERNAME                     TEMPORARY_TABLESPACE   DEFAULT_TABLESPACE
---------------------------- ---------------------- ----------------------
PERFSTAT                     TEMP_TS                STATSPACK
BHMCCRM                      TEMP_TS                DMSB_TS01
DMSB01                       TEMP_TS                DMSB_TS01
KCHC01                       TEMP_TS                USERS
BMC                          TEMP_TS                USERS
BHMCIT                       TEMP_TS                USERS
SCOTT                        TEMP_TS                USERS
MDDATA                       TEMP_TS                USERS
TSMSYS                       TEMP_TS                USERS
DIP                          TEMP_TS                USERS
ORACLE_OCM                   TEMP_TS                USERS
DBSNMP                       TEMP_TS                SYSAUX
SYSMAN                       TEMP_TS                SYSAUX
EXFSYS                       TEMP_TS                SYSAUX
DMSYS                        TEMP_TS                SYSAUX
WMSYS                        TEMP_TS                SYSAUX
ORDSYS                       TEMP_TS                SYSAUX
XDB                          TEMP_TS                SYSAUX
SI_INFORMTN_SCHEMA           TEMP_TS                SYSAUX
ORDPLUGINS                   TEMP_TS                SYSAUX
MDSYS                        TEMP_TS                SYSAUX
CTXSYS                       TEMP_TS                SYSAUX
ANONYMOUS                    TEMP_TS                SYSAUX
MGMT_VIEW                    TEMP_TS                SYSTEM
SYS                          TEMP_TS                SYSTEM
SYSTEM                       TEMP_TS                SYSTEM
OUTLN                        TEMP_TS                SYSTEM
DRS                          DRS_TMP                DRS_SPA
DRS_BACKUP                   TBST_BTEP              TBSD_TDEF

================
Component Status
================

Comp ID Component                          Status    Version        Org_Version    Prv_Version
------- ---------------------------------- --------- -------------- -------------- --------------
APS     OLAP Analytic Workspace            REMOVED   10.2.0.4.0
CATALOG Oracle Database Catalog Views      VALID     10.2.0.4.0
CATJAVA Oracle Database Java Packages      VALID     10.2.0.4.0
CATPROC Oracle Database Packages and Types VALID     10.2.0.4.0
CONTEXT Oracle Text                        VALID     10.2.0.4.0
EM      Oracle Enterprise Manager          VALID     10.2.0.4.0
EXF     Oracle Expression Filter           VALID     10.2.0.4.0
JAVAVM  JServer JAVA Virtual Machine       VALID     10.2.0.4.0
ODM     Oracle Data Mining                 VALID     10.2.0.4.0
ORDIM   Oracle interMedia                  VALID     10.2.0.4.0
OWM     Oracle Workspace Manager           VALID     10.2.0.4.3
RUL     Oracle Rules Manager               VALID     10.2.0.4.0
SDO     Spatial                            VALID     10.2.0.4.0
XDB     Oracle XML Database                VALID     10.2.0.4.0
XML     Oracle XDK                         VALID     10.2.0.4.0
XOQ     Oracle OLAP API                    REMOVED   10.2.0.4.0

======================================================
List of Invalid Database Objects Owned by SYS / SYSTEM
======================================================

Number of Invalid Objects
------------------------------------------------------------------
There are no Invalid Objects

DOC>################################################################
DOC>
DOC> If there are no Invalid objects below will result in zero rows.
DOC>
DOC>################################################################
DOC>#

no rows selected

================================
List of Invalid Database Objects
================================

Number of Invalid Objects
------------------------------------------------------------------
There are no Invalid Objects

DOC>################################################################
DOC>
DOC> If there are no Invalid objects below will result in zero rows.
DOC>
DOC>################################################################
DOC>#

no rows selected

======================================================
Count of Invalids by Schema
======================================================

==============================================================
Identifying whether a database was created as 32-bit or 64-bit
==============================================================

DOC>###########################################################################
DOC>
DOC> Result referencing the string 'B023' ==> Database was created as 32-bit
DOC> Result referencing the string 'B047' ==> Database was created as 64-bit
DOC> When String results in 'B023' and when upgrading database to 10.2.0.3.0
DOC> (64-bit) ,For known issue refer below articles
DOC>
DOC> Note 412271.1 ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While
DOC>               Upgrading Or Patching Databases To 10.2.0.3
DOC> Note 579523.1 ORA-600 [22635],ORA-600 [KOKEIIX1],ORA-7445 [KOPESIZ] and
DOC>              OCI-21500 [KOXSIHREAD1] Reported While Upgrading To 11.1.0.6
DOC>
DOC>###########################################################################
DOC>#

Metadata Initial DB Creation Info
-------- -----------------------------------
B047     Database was created as 64-bit

===================================================
Number of Duplicate Objects Owned by SYS and SYSTEM
===================================================

Counting duplicate objects ....

  COUNT(1)
----------
         4

=========================================
Duplicate Objects Owned by SYS and SYSTEM
=========================================

Querying duplicate objects ....

OBJECT_NAME                              OBJECT_TYPE
---------------------------------------- ----------------------------------------
AQ$_SCHEDULES                            TABLE
AQ$_SCHEDULES_PRIMARY                    INDEX
DBMS_REPCAT_AUTH                         PACKAGE
DBMS_REPCAT_AUTH                         PACKAGE BODY

DOC>
DOC>################################################################################
DOC>
DOC> If any objects found please follow below article.
DOC> Note 1030426.6 How to Clean Up Duplicate Objects Owned by SYS and SYSTEM schema
DOC> Read the Exceptions carefully before taking actions.
DOC>
DOC>################################################################################
DOC>#

================
JVM Verification
================

JAVAVM - Installed properly

================================================
Checking Existence of Java-Based Users and Roles
================================================

DOC>
DOC>################################################################################
DOC>
DOC> There should not be any Java Based users for database version 9.0.1 and above.
DOC> If any users found,it is faulty JVM.
DOC>
DOC>################################################################################
DOC>#

User Existence
---------------------------
No Java Based Users

DOC>
DOC>###############################################################
DOC>
DOC> Healthy JVM Should contain Six Roles.
DOC> If there are more or less than six role,JVM is inconsistent.
DOC>
DOC>###############################################################
DOC>#

Role
------------------------------
There are 6 JAVA related roles

Roles

ROLE
------------------------------
JAVAUSERPRIV
JAVAIDPRIV
JAVASYSPRIV
JAVADEBUGPRIV
JAVA_ADMIN
JAVA_DEPLOY

=========================================
List of Invalid Java Objects owned by SYS
=========================================

There are no SYS owned invalid JAVA objects

DOC>
DOC>#################################################################
DOC>
DOC> Check the status of the main JVM interface packages DBMS_JAVA
DOC> and INITJVMAUX and make sure it is VALID.
DOC> If there are no Invalid objects below will result in zero rows.
DOC>
DOC>#################################################################
DOC>#

no rows selected

INFO: Below query should succeed with 'foo' as result.

JAVAVM TESTING
---------------
foo

                            *** End of LogFile ***

not spooling currently

Upload db_upg_diag_DMSBHMC_19_Nov_2011_0837.log from "0" directory

3. 根据 utlu112i.sql 的运行结果进行升级前的准备, 如下:

SQL> @ utlu112i.sql

Oracle Database 11.2 Pre-Upgrade Inhttp://www.oracleplus.netformation Tool 11-18-2011 18:52:27
Script Version: 11.2.0.2.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name:          DMSBHMC
--> version:       10.2.0.4.0
--> compatible:    10.2.0.3.0
--> blocksize:     8192
--> platform:      HP-UX IA (64-bit)
--> timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 786 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 7423 MB
WARNING: --> SYSAUX tablespace is not large enough for the upgrade.
.... currently allocated size: 1000 MB
.... minimum required size: 1008 MB
.... increase current size by: 8 MB
.... tablespace is AUTOEXTEND ENABLED.
--> TEMP_TS tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 64-bit database.
**********************************************************************
--> If Target Oracle is 32-Bit,refer here for Update Parameters:
-- No update parameter changes are required.
.

--> If Target Oracle is 64-Bit,refer here for Update Parameters:
-- No update parameter changes are required.
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
--> background_dump_dest         11.1       DEPRECATED   replaced by  "diagnostic_dest"
--> user_dump_dest               11.1       DEPRECATED   replaced by  "diagnostic_dest"
.

**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views         [upgrade]  VALID
--> Oracle Packages and Types    [upgrade]  VALID
--> JServer JAVA Virtual Machine [upgrade]  VALID
--> Oracle XDK for Java          [upgrade]  VALID
--> Oracle Workspace Manager     [upgrade]  VALID
--> OLAP Analytic Workspace      [upgrade]  INVALID
--> OLAP Catalog                 [upgrade]  INVALID
--> EM Repository                [upgrade]  VALID
--> Oracle Text                  [upgrade]  VALID
--> Oracle XML Database          [upgrade]  VALID
--> Oracle Java Packages         [upgrade]  VALID
--> Oracle interMedia            [upgrade]  VALID
--> Spatial                      [upgrade]  VALID
--> Data Mining                  [upgrade]  VALID
--> Expression Filter            [upgrade]  VALID
--> Rule Manager                 [upgrade]  VALID
--> Oracle OLAP API              [upgrade]  INVALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using a timezone file older than version 14.
.... After the release migration,it is recommended that DBMS_DST package
.... be used to upgrade the 10.2.0.4.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Database contains INVALID objects prior to upgrade.
.... The list of invalid SYS/SYSTEM objects was written to
.... registry$sys_inv_objs.
.... The list of non-SYS/SYSTEM objects was written to
.... registry$nonsys_inv_objs.
.... Use utluiobj.sql after the upgrade to identify any new invalid
.... objects due to the upgrade.
.... USER PUBLIC has 1 INVALID objects.
.... USER DRS has 12 INVALID objects.
.... USER DMSB01 has 29 INVALID objects.
WARNING: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... Upgrade Guide for instructions to save the EM data prior to upgrade.
WARNING: --> Your recycle bin contains 298 object(s).
.... It is REQUIRED that the recycle bin is empty prior to upgrading
.... your database.  The command:
        PURGE DBA_RECYCLEBIN
.... must be executed immediately prior to executing your upgrade.
.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:

    EXECUTE dbms_stats.gather_dictionary_stats;

**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.

To view existing non-default events execute the following commands
while connected AS SYSDBA:
  Events:
    SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
      WHERE  UPPER(name) ='EVENT' AND  isdefault='FALSE'

  Trace Events:
    SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
      WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'

Changes will need to be made in the init.ora or spfile.

**********************************************************************
SP2-0042: unknown command "$" - rest of line ignored.
SQL> spool off
[DMSBHMC] oracle@dmsdbp02:/ora_engine $

4. 应客户要求,停库,进行物理全冷备


-- cp 所有数据文件,controlfile, redo log(tmp除外)
-- tar 包备份10g $ORACLE_HOME

5. 扩展相关的表空间


我这里扩容sysaux和system即可。

6. Remove OLAP组件

EXECUTE:

@ /olap/admin/catnoamd.SQL;
@ /olap/admin/olapidrp.plb;
@ /olap/admin/catnoaps.SQL;
@ /olap/admin/catnoxoq.SQL;
@ /olap/admin/cwm2drop.SQL;

然后进行CHECK:

SQL> Col comp_name FOR a35
SQL> Col STATUS FOR a15
SQL> SET LINES 150
SQL> SELECT comp_name,version,STATUS FROM dba_registry;

COMP_NAME                           VERSION                        STATUS
----------------------------------- ------------------------------ ---------------
Spatial                             10.2.0.4.0                     VALID
Oracle interMedia                   10.2.0.4.0                     VALID
Oracle Enterprise Manager           10.2.0.4.0                     VALID
Oracle XML DATABASE                 10.2.0.4.0                     VALID
Oracle Text                         10.2.0.4.0                     VALID
Oracle Expression FILTER            10.2.0.4.0                     VALID
Oracle Rules Manager                10.2.0.4.0                     VALID
Oracle Workspace Manager            10.2.0.4.3                     VALID
Oracle DATA Mining                  10.2.0.4.0                     VALID
Oracle DATABASE Catalog Views       10.2.0.4.0                     VALID
Oracle DATABASE Packages AND Types  10.2.0.4.0                     VALID
JServer JAVA Virtual Machine        10.2.0.4.0                     VALID
Oracle XDK                          10.2.0.4.0                     VALID
Oracle DATABASE Java Packages       10.2.0.4.0                     VALID
OLAP Analytic Workspace             10.2.0.4.0                     REMOVED
Oracle OLAP API                     10.2.0.4.0                     REMOVED

16 ROWS selected.

针对不同版本,remove OLAP组件,请参看如下文档:

Master Note FOR Oracle XML DATABASE (XDB) Install / Deinstall [ID 1292089.1]

7. Recomplied(or clear) invalid objects


首先运行utlrp.sql,对于无效对象,跟应用确认如下可以删除:
DROP PROCEDURE "DMSB01.UPDATE_VINORDER"    ;
DROP PROCEDURE "DMSB01.UPDATE_ORDE_STATUS" ;
DROP PROCEDURE CLEAN_WSTCLEP_DUP         ;
DROP PROCEDURE CONV_LONG_VARCHAR         ;
DROP PROCEDURE OVEH_MIGRATION            ;
DROP PROCEDURE GEN_CUST_ID2              ;
DROP PROCEDURE BILL_MIGRATION            ;
DROP PROCEDURE GEN_BILL_NO               ;
DROP PROCEDURE BILL_REPM_MAIGRAION       ;
DROP PROCEDURE BILL_REPL_MIGRATION       ;
DROP PROCEDURE GEN_CUST_ID               ;
DROP PROCEDURE REPP_MIGRATION            ;
DROP PROCEDURE REPM_MIGRATION            ;
DROP PROCEDURE REPL_MIGRATION            ;
DROP PROCEDURE CLPP_MIGRATION            ;
DROP PROCEDURE CLOP_MIGRATION            ;
DROP PROCEDURE CLAIM_MIGRATION           ;
DROP PROCEDURE TEST                      ;
DROP PROCEDURE GEN_CLAIM_NO              ;
DROP PROCEDURE BILL_REPP_MIGRATION       ;
DROP FUNCTION DRS.CCC ;
DROP VIEW DRS.V_20110601_03_01_02SP_PZ  ;
DROP VIEW DRS.V_20110901_03_01_02SP_PZ  ;
DROP VIEW DRS.V_20110401_03_01_02SP_PZ  ;
DROP VIEW DRS.V_TJ_0901_FQHZ            ;
DROP VIEW DRS.V_TJ_1008_FQHZ            ;
DROP VIEW DRS.V_20110503_03_01_02SP_PZ  ;
DROP VIEW DRS.V_TJ_0802_FQHZ            ;
DROP VIEW DRS.V_20110704_03_01_02SP_PZ  ;
DROP VIEW DRS.V_20110801_03_01_02SP_PZ  ;

8. 清理回收站

-- select count(*) from dba_recyclebin;
-- purge dba_recyclebin

SQL> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.

9. Check again


-- select cocunt(*) from dba_objects where status='INVALID';
-- 再次运行 utlu112i.sql

10. 收集统计


运行如下脚本即可(需要从metalink下载):

check_schema_stale_stats.sql

执行情况如下:
SQL> @ check_stale_stats.SQL

-------------------------------------------------------------------------------------------------------
-- CTXSYS schema contains stale statistics use the following to gather the statistics --
-------------------------------------------------------------------------------------------------------

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('CTXSYS', OPTIONS=>'GATHER STALE', ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO', CASCADE=>TRUE);
-- There are no stale statistics in DMSYS schema.

-- There are no stale statistics in EXFSYS schema.

-------------------------------------------------------------------------------------------------------
-- MDSYS schema contains stale statistics use the following to gather the statistics --
-------------------------------------------------------------------------------------------------------

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('MDSYS', OPTIONS=>'GATHER STALE', ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO', CASCADE=>TRUE);
-- There are no stale statistics in ORDSYS schema.

-------------------------------------------------------------------------------------------------------
-- SYS schema contains stale statistics use the following to gather the statistics --
-------------------------------------------------------------------------------------------------------

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('SYS', OPTIONS=>'GATHER STALE', ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO', CASCADE=>TRUE);

-------------------------------------------------------------------------------------------------------
-- SYSMAN schema contains stale statistics use the following to gather the statistics --
-------------------------------------------------------------------------------------------------------

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('SYSMAN', OPTIONS=>'GATHER STALE', ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO', CASCADE=>TRUE);
-- There are no stale statistics in WMSYS schema.

-------------------------------------------------------------------------------------------------------
-- XDB schema contains stale statistics use the following to gather the statistics --
-------------------------------------------------------------------------------------------------------

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('XDB', OPTIONS=>'GATHER STALE', ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO', CASCADE=>TRUE);

根据上面结果,执行相关的SQL即可。

11. Check for TIMESTAMP WITH TIMEZONE Datatype


select TZ_VERSION from registry$database;

12. Check that the National Characterset (NLS_NCHAR_CHARACTERSET) is UTF8 or AL16UTF16.


select value from NLS_DATABASE_PARAMETERS where parameter = 'NLS_NCHAR_CHARACTERSET';

If this is UTF8 or AL16UTF16 then no action is needed.
If is not UTF8 or AL16UTF16 then refer to the following article:

Note 276914.1 The National Character Set in Oracle 9i and 10g.

13. Optimizer Statistics

$ sqlplus "/as sysdba"

SQL> @check_schema_stale_stats.SQL

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

14. Disable Oracle Database Vault

15. Backing up Enterprise Manager Database Control Data

16. check 数据字典错误

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

$ sqlplus "/ as sysdba"

SQL> @$ORACLE_HOME/rdbms/admin/utlvalid.SQL
SQL> @analyze.SQL

17. Ensure that all snapshot refreshes are successfully completed,and that replication is stopped.


SELECT DISTINCT(TRUNC(last_refresh))
FROM dba_snapshot_refresh_times;

18. Ensure that no files need media recovery.

SQL> SELECT * FROM v$recover_file;

no rows selected

19. Ensure that no files are  in backup mode.


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

20. Resolve outstanding distributed transactions prior to the upgrade.
SQL> SELECT * FROM dba_2pc_pending;

IF this RETURNS ROWS you should do the following:

SQL> SELECT local_tran_id FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');
SQL> COMMIT;
21. To check if a standby database exists
SQL> SELECT SUBSTR(VALUE,INSTR(VALUE,'=',INSTR(UPPER(VALUE),'SERVICE'))+1)
  2  FROM v$parameter
  3  WHERE name LIKE 'log_archive_dest%' AND UPPER(VALUE) LIKE 'SERVICE%';

no ROWS selected

22. Disable all batch and cron jobs.

23. 如果有em,那么需要备份em原数据


由于客户这里并没有配置em,故我跳过该步骤,如果存在的话,那么参看如下文档进行操作即可。

How To Save Oracle Enterprise Manager Database Control Data Before Upgrading The Single Instance Database To Other Release   [ID 870877.1]

24. Ensure the users SYS and SYSTEM have 'SYSTEM' as their default tablespace.


You must have sufficient space in the tablespace or be set to extents unlimited.
-- 操作记录

SQL> SELECT SUBSTR(VALUE,INSTR(VALUE,'=',INSTR(UPPER(VALUE),'SERVICE'))+1)
  2    FROM v$parameter
  3   WHERE name LIKE 'log_archive_dest%' AND UPPER(VALUE) LIKE 'SERVICE%';

no ROWS selected

SQL> SELECT username,default_tablespace
  2    FROM dba_users
  3   WHERE username IN ('SYS','SYSTEM');

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
SYS                            SYSTEM
SYSTEM                         SYSTEM

25. Ensure that if the aud$ table exists that it is in the SYS schema and in the SYSTEM tablespace.
SQL> SELECT owner,tablespace_name
  2    FROM dba_tables
  3   WHERE TABLE_NAME='AUD$';

OWNER                          TABLESPACE_NAME
------------------------------ ------------------------------
SYS                            SYSTEM
26. Check whether database has any externally authenticated SSL users.
SQL> SELECT name
  2    FROM sys.USER$
  3   WHERE ext_username IS NOT NULL
  4     AND password = 'GLOBAL';

no ROWS selected
27. stop database,stoo listener and dbconsole.

-- shutdown immediate
-- lsnrctl stop
-- emctl stop dbconsole

28. 修改.profile,编辑复制原spfile进行适当编辑生成一个11gR2 pfile


--  修改.profile 中$ORACLE_HOME即可(指定到11gR2 目录)
    如下:

    export ORACLE_HOME=/ora_engine/product/11.2.0/dbhome_1

-- 修改pfile,如下:

   具体内容省略。

   备注:如何确认10g中那些参数在11gR2中已经废弃,那么可以参看utlu112i.sql的运行结果,并作相应修改即可。

-- 将10g 密码文件 listener.ora,tnsnames.ora拷贝到11gR2中

-- 修改/etc/oratab

29.  进行升级


当然,你可以使用dbua进行升级,我这里选择使用脚本进行:
-- shutdown immediate;
-- startup upgrade;

SQL> SET echo ON
SQL> SPOOL upgrade.log
SQL> @ /ora_engine/product/11.2.0/dbhome_1/rdbms/admin/catupgrd.SQL
SQL> spool off

$ sqlplus "/as sysdba"
SQL> STARTUP
SQL> @ /ora_engine/product/11.2.0/dbhome_1/rdbms/admin/utlu112s.SQL 
SQL> @ /ora_engine/product/11.2.0/dbhome_1/rdbms/admin/catuppst.SQL
SQL> @ /ora_engine/product/11.2.0/dbhome_1/rdbms/admin/utlrp.SQL

30. 升级完成以后,检查组件是否正常


select comp_name,version,status from sys.dba_registry

31. check indexes


select owner,index_name from dba_indexes where status='UNUSABLE';

进查,对17个状态unusable的index进行rebuild。

32. 启动listener,dbconsole,进行应用测试

33. 进行TDE加密配置


关于TDE加密实施涉及到用户信息,故省略。

注意: 当使用

       alter table table_name move tablespace tbs_encrypt parallel n;
       或
       alter index index_name rebuild tablespace tbs_encrypt parallel n;

       后记得使用

       alter table/index table_name/index_name parallel 1;

       修改默认值。

34. 由于是双机环境(VCS),在备机上安装相应的os patch,然后将主机器上的11g ORACLE_HOME


    直接tar包传过去解压即可,当然tar过去以后最好是relink all一下。

补充:这次实施过程较为波折,所以打算补充几句,从周5晚上开始,到今天早上9点才搞定,
      其中遇到了如下几个问题,并对相关的注意事项进行说明:

(1) 必须参考官方文档,对于提到的os patch包最好全部打上;

(2) 安装oracle所推荐的patch如 10368698 10419629 针对类似安装升级不管是大版本升级还是
    安装psu等等我们不仅要参考安装文档或reamde,而且更要关注Mos上的文档,对于提到的
    相关bug,如果能下载安装,最好是升级之前打上;

(3) 升级之后,可能会面临一些性能上的问题,建议参考如下文档:

Things to Consider Before Upgrade to 11.2.0.2 in Relation to Database Performance [ID 1320966.1]

其中涉及到的几个patch如下:

E:\software\11.2.0.2-forHPIA64\Relation to Database Performance-patch>dir *.zip

驱动器 E 中的卷是 work
卷的序列号是 B36D-13B7

E:\software\11.2.0.2-forHPIA64\Relation to Database Performance-patch 的目录

2011/11/19  14:18           177,067 p10149223_112020_HPUX-IA64.zip
2011/11/19  14:11           248,243 p10259620_112020_HPUX-IA64.zip
2011/11/19  14:14           207,794 p10269193_112020_HPUX-IA64.zip
2011/11/19  14:17           869,533 p10623249_112022_HPUX-IA64.zip
2011/11/19  14:13           189,808 p11719151_112020_HPUX-IA64.zip
2011/11/19  14:18           186,073 p12363485_112020_HPUX-IA64.zip
2011/11/19  14:12           196,735 p13004894_112020_HPUX-IA64.zip
2011/11/19  14:13            20,917 p9842771_112020_Generic.zip

               8 个文件      2,096,170 字节
               0 个目录 48,185,565,184 可用字节

该文档中提到了几个性能相关的bug,看来11.2.0.2问题也真的不少啊。

(4) 关于oracle TDE,不多说了,以前写过几篇相关的文章,另外大家也可以参考如下MOS文档:

Quick and Dirty TDE Setup and FAQ [ID 1251597.1]

                                     +++++++++ 怪事趣谈 +++++++++

怪事1:

不过在升级过程中我还是遇到问题了,第一天晚上在运行cataupgrd.sql时,发现奇慢无比,其中通过看alert日志,
发现在更新xdb时非常慢,花了差不多2小时35分钟,通过对比上个月前的测试,实际上上次测试该步骤就6分钟左右而已。

虽然该步是进行过去了,但是在后面更新ORDIM组件时,运行到如下脚本时,session挂住了:

INSERT INTO MDSYS.SDO_CS_SRS (
  CS_NAME,
  SRID,
  AUTH_SRID,
  AUTH_NAME,
  WKTEXT,
  WKTEXT3D,
  CS_BOUNDS
  ) (
  SELECT
    COORD_REF_SYS_NAME "CS_NAME",
    SRID "SRID",
    SRID "AUTH_SRID",
    INFORMATION_SOURCE "AUTH_NAME",
    NVL(
      LEGACY_WKTEXT,
      MDSYS.sdo_cs.internal_det_srid_wkt(srid)) "WKTEXT",
    sdo_cs.get_3d_wkt(srid) "WKTEXT3D",
    LEGACY_CS_BOUNDS "CS_BOUNDS"
  FROM
    MDSYS.SDO_COORD_REF_SYS)

通过查看10月份弄的测试库,发现SDO_COORD_REF_SYS数据量非常小,把该sql拿到测试库上运行不到30s就完成了。

第一天晚上就卡在这里,经过1个小时之后,凌晨4点,觉得进行回退,以免影响应用。

昨晚继续进行升级操作,最开始怀疑或许pfile中内存相关参数设置太小了,跑之前我sga和pga分别调整到8G,3G;
另外通过查询mos发现运行cataupgrd.sql在xdb组件更新慢的情况很可能是bug 10368698, 于是运行脚本之前我也
将该patch打上,不过最后发现仍然效果一样。

通过查看alert发现仍然花了2.5小时左右,而且仍然运行到上面的insert语句时,停止不动了,过了30分钟,客户
建议取消,放弃该方案。

迫于无奈之下,通过top可以看到目前基本上消耗集中在一个cpu上,而且消耗了99%,但是不是user,而且sys消耗,
这有些怪异。

突发奇想,想看看这个session进程目前是什么情况,于是使用oradebug对该进行操作了下,过了不到10s,居然奇迹
出现insert执行ok了,然后随后差不多30分钟完成了cataupgrd.sql脚本的运行,检查日志也没有发现任何错误。


怪事2:

做完升级以及TDE加密,应用测试也ok以后,将11gR2 oracle home进行tar包传到备机,安装相关的os patch以后,
进行tar xvf解压,然后relink all,发现relink.log有错误,运行sqlplus报错。

看错误是找不到一些lib文件,进行对比,发现tar的包少文件,接着重新tar了一次,传到备机上解压后分布对比
主和备上的product目录(oracle home), 发现居然文件总数不一样,怪了。而且更奇怪的是,我第一次的tar包,
由于原oracle base目录空间不足,只能在临时目录解压然后mv(cp)过去,发现mv(cp)过去以后的product和以前
的product文件总数也不相同。

最后无奈之下,xxx公司的人用NBU直接备份主机上的ORACLE_HOME到备机上,然后就ok了。

不得不说,真是一个神奇的国度。

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle数据库升级 10.2.0.5升级至11.2.0.2详细步骤

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1318.html

Oracle研究中心

关键词:

Oracle数据库升级步骤

Oracle 10.2.0.5 To 11.2.0.2详细步骤