本帖最后由 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]