本帖最后由 ioriakyo 于 2014-8-18 17:14 编辑

DBUA是一种ORACLE推荐的数据库升级解决方案,使用场景:同平台,数据量大,且停机时间短!

由于DBUA对于环境的要求非常严格,下面给大家大概介绍下升级步骤,具体实施希望大家仔细参考附件的官方资料!

步骤:
1:查看源环境组件安装情况,如:
COMP_ID COMP_NAME VERSION STATUS MODIFIED
---------- ------------------------------ ---------- ----------- --------------------
CATALOG Oracle9i Catalog Views 9.2.0.8.0 VALID 29-MAR-2010 14:37:09
CATPROC Oracle9i Packages and Types 9.2.0.8.0 VALID 29-MAR-2010 14:37:09
OWM Oracle Workspace Manager 9.2.0.1.0 VALID 29-MAR-2010 14:24:15
JAVAVM JServer JAVA Virtual Machine 9.2.0.8.0 VALID 29-MAR-2010 14:25:14
XML Oracle XDK for Java 9.2.0.10.0 VALID 29-MAR-2010 14:37:09
CATJAVA Oracle9i Java Packages 9.2.0.8.0 VALID 29-MAR-2010 14:37:09
ORDIM Oracle interMedia 9.2.0.8.0 VALID 29-MAR-2010 14:30:20
SDO Spatial 9.2.0.8.0 VALID 29-MAR-2010 14:32:20
CONTEXT Oracle Text 9.2.0.8.0 VALID 29-MAR-2010 14:31:05
XDB Oracle XML Database 9.2.0.8.0 VALID 29-MAR-2010 14:37:09
WK Oracle Ultra Search 9.2.0.8.0 VALID 29-MAR-2010 14:33:00
COMP_ID COMP_NAME VERSION STATUS MODIFIED
---------- ------------------------------ ---------- ----------- --------------------
OLS Oracle Label Security 9.2.0.8.0 VALID 29-MAR-2010 14:37:10
ODM Oracle Data Mining 9.2.0.8.0 VALID 29-MAR-2010 14:35:33
APS OLAP Analytic Workspace 9.2.0.8.0 LOADED 29-MAR-2010 14:35:41
XOQ Oracle OLAP API 9.2.0.8.0 LOADED 29-MAR-2010 14:35:54
AMD OLAP Catalog 9.2.0.8.0 VALID 27-DEC-2010 17:17:28

2:检查隐含参数:
检查隐含参数:
SQL> col NAME format a40
SQL> col DESCRIPTION format a50
SQL> col value format a20
SQL> set linesize 200
SQL> SELECT name,VALUE,description from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\';
如:
NAME VALUE DESCRIPTION
---------------------------------------- -------------------- --------------------------------------------------
_kghdsidx_count 4 max kghdsidx count
_pga_max_size 2147483648 Maximum size of the PGA memory for one process
_smm_max_size 1048576 maximum work area size in auto mode (serial)
_smm_px_max_size 2097152 maximum work area size in auto mode (global)
修改屏蔽隐含参数：
vi initdw2.ora
#*._kghdsidx_count=4
#*._pga_max_size=2147483648
#*._smm_max_size=1048576
#*._smm_px_max_size=2097152
shutdown immediate;
startup;

3:运行检查脚本:
$ORACLE_HOME/rdbms/admin/utlu112i.sql(版本不同，可能脚本也不同，具体参见官方资料),如:
SQL> @utlu112i_5.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 06-08-2013 11:22:40
Script Version: 11.2.0.3.0 Build: 006
.
**********************************************************************
Database:
**********************************************************************
--> name: ***
--> version: 9.2.0.8.0
--> compatible: 9.2.0.0.0
--> blocksize: 8192
--> timezone file: V1
.
**********************************************************************
Logfiles: [make adjustments in the current environment]
**********************************************************************
--> The existing log files are adequate. No changes are required.
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 1156 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 60 MB
--> CWMLITE tablespace is adequate for the upgrade.
.... minimum required size: 15 MB
--> DRSYS tablespace is adequate for the upgrade.
.... minimum required size: 10 MB
--> ODM tablespace is adequate for the upgrade.
.... minimum required size: 10 MB
--> XDB tablespace is adequate for the upgrade.
.... minimum required size: 147 MB
.
**********************************************************************
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:
WARNING: --> "compatible" must be set to at least 10.1.0
WARNING: --> "java_pool_size" needs to be increased to at least 64 MB
.
--> If Target Oracle is 64-Bit, refer here for Update Parameters:
WARNING: --> "compatible" must be set to at least 10.1.0
WARNING: --> "java_pool_size" needs to be increased to at least 128 MB
.
**********************************************************************
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]
**********************************************************************
--> hash_join_enabled 10.1 OBSOLETE
--> log_parallelism 10.1 OBSOLETE
--> log_archive_start 10.1 DEPRECATED
--> parallel_automatic_tuning 10.1 DEPRECATED
--> background_dump_dest 11.1 DEPRECATED replaced by "diagnostic_dest"
--> user_dump_dest 11.1 DEPRECATED replaced by "diagnostic_dest"
--> parallel_automatic_tuning 11.2 DEPRECATED
.
**********************************************************************
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] LOADED
--> OLAP Catalog [upgrade] VALID
--> Oracle Label Security [upgrade] VALID
... To successfully upgrade Oracle Label Security, choose
... 'Select Options' in Oracle installer and then select
... Oracle Label Security.
--> 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
--> Oracle Ultra Search [upgrade] VALID
--> Oracle OLAP API [upgrade] LOADED
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Passwords exist in some database links.
.... Passwords will be encrypted during the upgrade.
.... Downgrade of database links with passwords is not supported.
WARNING: --> Deprecated CONNECT role granted to some user/roles.
.... CONNECT role after upgrade has only CREATE SESSION privilege.
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 9.2.0.8.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 CTXSYS has 1 INVALID objects.
.... USER DW has 90 INVALID objects.
.... USER MID has 2 INVALID objects.
.... USER ODS has 9 INVALID objects.
.... USER REPORT has 12 INVALID objects.
.... USER SYS has 2 INVALID objects.
.... USER WKSYS has 1 INVALID objects.
WARNING: --> There are materialized view refreshes in progress.
.... Ensure all materialized view refreshes are complete prior to upgrade.
WARNING: --> log_archive_format must be updated.(关闭归档、alter system set log_archive_format='dw2_%t_%s_%r.arc' scope=spfile;)
.... As of 10.1, log_archive_format requires a %r format qualifier
.... be present in its format string. Your current setting is:
.... log_archive_format='dw2_%t_%s.arc'.
.... Archive Logging is currently ON, and failure to add the %r to the
.... format string will prevent the upgraded database from starting up.
.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following commands
while connected as SYSDBA:
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('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('ORDPLUGINS',options=>'GATHER'
,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE
,method_opt=>'FOR ALL COLUMNS SIZE AUTO'
,cascade=>TRUE);
EXECUTE dbms_stats.gather_schema_stats('ORDSYS',options=>'GATHER'
,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE
,method_opt=>'FOR ALL COLUMNS SIZE AUTO'
,cascade=>TRUE);
EXECUTE dbms_stats.gather_schema_stats('LBACSYS',options=>'GATHER'
,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE
,method_opt=>'FOR ALL COLUMNS SIZE AUTO'
,cascade=>TRUE);
EXECUTE dbms_stats.gather_schema_stats('WKSYS',options=>'GATHER'
,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE
,method_opt=>'FOR ALL COLUMNS SIZE AUTO'
,cascade=>TRUE);
EXECUTE dbms_stats.gather_schema_stats('XDB',options=>'GATHER'
,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE
,method_opt=>'FOR ALL COLUMNS SIZE AUTO'
,cascade=>TRUE);
EXECUTE dbms_stats.gather_schema_stats('CTXSYS',options=>'GATHER'
,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE
,method_opt=>'FOR ALL COLUMNS SIZE AUTO'
,cascade=>TRUE);
EXECUTE dbms_stats.gather_schema_stats('MDSYS',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);
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('SYS',options=>'GATHER'
,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE
,method_opt=>'FOR ALL COLUMNS SIZE AUTO'
,cascade=>TRUE);
**********************************************************************
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.
**********************************************************************
**********************************************************************
SYSAUX Tablespace:
[Create tablespace in the Oracle Database 11.2 environment]
**********************************************************************
--> New "SYSAUX" tablespace
.... minimum required size for database upgrade: 500 MB
.

4:根据检查脚本的提示，修改相应属性,完成后再运行一次脚本确认修改成功.

5:收集组件和无效对象：
------------------------------------------------------------------------------------
set pagesize500
set linesize 200

select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from
dba_registry order by comp_name;
COMP_NAME

select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type from
dba_objects where status='INVALID' order by owner,object_type;

select owner,object_type,count(*) from dba_objects where status='INVALID' group by
owner,object_type order by owner,object_type ;

6:编译无效对象,并再次检查对象有效性：
----------------------------------------------------------------
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> @utlrp.sql
SQL> @?/rdbms/admin/utlrp.sql
No errors.
No errors.

7:定义事件属性:
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'

8:收集统计信息
如:
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);

9:查看是否有审计记录:
Audit records：
If do not want to keep the records collected before 11GR2 then you can just do in source environment :
truncate table sys.aud$;
truncate table sys.fga_log$;
SQL> select count(*) from sys.aud$;
0
SQL> select count(*) from sys.fga_log$;

10:备份统计信息

11:检查角色权限,并授权,如:
Check Deprecated CONNECT Role：
SQL> SELECT grantee FROM dba_role_privs
2 WHERE granted_role = 'CONNECT' and
3 grantee NOT IN (
4 'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
5 'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
6 'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
7 'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
8 'WMSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
9 'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');
test
In Oracle 9.2.x and 10.1.x CONNECT role includes the following privileges:
SELECT GRANTEE,PRIVILEGE
FROM DBA_SYS_PRIVS
WHERE GRANTEE ='CONNECT'
GRANTEE 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

12:停监听:

13:重复对象处理：
SQL> column object_name format a30
SQL> select object_name, object_type
2 from dba_objects
3 where object_name||object_type in
4 (select object_name||object_type
5 from dba_objects
6 where owner = 'SYS')
7 and owner = 'SYSTEM';
如:
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------
AQ$_SCHEDULES TABLE
AQ$_SCHEDULES_PRIMARY INDEX
DBMS_REPCAT_AUTH PACKAGE
DBMS_REPCAT_AUTH PACKAGE BODY
SQL> set pause off
SQL> set heading off
SQL> set pagesize 0
SQL> set feedback off
SQL> set verify off
SQL> spool dropsys.sql
SQL> select 'DROP ' || object_type || ' SYSTEM.' || object_name || ';'
2 from dba_objects
3 where object_name||object_type in
4 (select object_name||object_type
5 from dba_objects
6 where owner = 'SYS')
7 and owner = 'SYSTEM';
spool off
exit
DROP TABLE SYSTEM.AQ$_SCHEDULES;
DROP INDEX SYSTEM.AQ$_SCHEDULES_PRIMARY;
DROP PACKAGE SYSTEM.DBMS_REPCAT_AUTH;
DROP PACKAGE BODY SYSTEM.DBMS_REPCAT_AUTH;
SQL> DROP TABLE SYSTEM.AQ$_SCHEDULES;
DROP INDEX SYSTEM.AQ$_SCHEDULES_PRIMARY;
DROP PACKAGE SYSTEM.DBMS_REPCAT_AUTH;
DROP PACKAGE BODY SYSTEM.DBMS_REPCAT_AUTH;
Table dropped.
SQL> DROP INDEX SYSTEM.AQ$_SCHEDULES_PRIMARY
*
ERROR at line 1:
ORA-01418: specified index does not exist

SQL>
Package dropped.
SQL> DROP PACKAGE BODY SYSTEM.DBMS_REPCAT_AUTH
*
ERROR at line 1:
ORA-04043: object DBMS_REPCAT_AUTH does not exist

14:备份DBLINK
SQL> SELECT
2 'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)
3 ||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME||chr(10)
4 ||'connect to ' || L.USERID || ' identified by '''
5 ||L.PASSWORD||''' using ''' || L.host || ''''
6 ||chr(10)||';' TEXT
7 FROM sys.link$ L,
8 sys.user$ U
9 WHERE L.OWNER# = U.USER# ;

15:确保所有的物化视图/快照已经成功完成，不存在任何的复制进程
SQL> select distinct(trunc(last_refresh)) from dba_snapshot_refresh_times;

16:修改：
TEST:[/]#more /etc/oratab
# *:/ora9i/app/oracle/product/9.2.0:N
# *:/ora9i/app/oracle/product/9.2.0:N
ORCL:/ora9i/app/oracle/product/9.2.0:N
+ASM:/ora11g/app/grid/product/11.2.0:N

17:运行DBUA

18:用户授权;

由于图片涉及客户信息，不便展示,请谅解!

参考文档:
Complete Checklist to Upgrade the Database to 11gR2 using DBUA [ID 870814.1]
Complete Checklist for Manual Upgrades to 11gR2 [ID 837570.1]