一、测试环境
1. 虚拟机上安装windows 2003企业版
2. 10.2.0.3 DBUA升级到11.2.0.1
二、下载软件
10.2.0.1安装软件,10.2.0.3补丁,11.2.0.1安装软件,并上传到在windows 2003上建立的C:\install目录
三、版本升级要求
Dbua对升级版本有要求,所以进行版本升级前,首先要看是否属于dbua支持的版本
可以直接升级到11.2版本的数据:
Source Database | Target Database |
9.2.0.8 or higher | 11.2.x |
10.1.0.5 or higher | 11.2.x |
10.2.0.2 or higher | 11.2.x |
11.1.0.6 or higher | 11.2.x |
非直接升级至11.2版本的数据库
Source Database |
| Upgrade Path for Target Database |
|
|
7.3.3 (or lower) | ----> | 7.3.4 -> 9.2.0.8 | ----> | 11.2.x |
8.0.5 (or lower) | ----> | 8.0.6 -> 9.2.0.8 | ----> | 11.2.x |
8.1.7 (or lower) | ----> | 8.1.7.4 -> 10.2.0.4 | ----> | 11.2.x |
9.0.1.3 (or lower) | ----> | 9.0.1.4 -> 10.2.0.4 | ----> | 11.2.x |
9.2.0.7(or lower) | ----> | 9.2.0.8 | ----> | 11.2.x |
(参考文档:ID 837570.1)
四、dbua升级过程
4.1安装10.2.0.1并打10.2.0.3补丁
1. 安装10.2.0.1软件,注意只安装软件,不建库。安装目录:C:\oracle\product\10.2.0\db_1。
2. 将10.2.0.1升级至10.2.0.3。因为利用dbua升级至11.2需要数据库版本为10.2.0.2以上,我选择先将10.2.0.1升级至10.2.0.3。
3. 创建数据库test。
4. 创建10.2数据库linstener,便于后面的操作。
5. 在10.2上创建表空间data,用户data01,并为此创建table。
6. 关闭数据库,并且把所以的表空间更改为read-only模式,对源数据库进行冷备。
4.2 升级前准备
4.2.1源数据库的准备
1. 因为隐含的参数不会通过dbua升级到目标数据库,所以在升级之前我们先检查源数据库的隐含参数。Oracle建议删除所有隐含参数
SHAPE \* MERGEFORMAT
SQL> SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\';
未选定行 |
2. 在升级前确保所有的数据库选项为valid
SHAPE \* MERGEFORMAT
SQL> select owner,object_name,object_type from dba_objects Where status='INVALID';
未选定行
3. 确保你不包含sys和system共有的duplicate objects
SHAPE \* MERGEFORMAT
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 wner = 'SYS')
7 and wner = 'SYSTEM';
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------
AQ$_SCHEDULES TABLE
AQ$_SCHEDULES_PRIMARY INDEX
DBMS_REPCAT_AUTH PACKAGE
DBMS_REPCAT_AUTH PACKAGE BODY
HELP TABLE
HELP_TOPIC_SEQ INDEX
已选择6行。
SHAPE \* MERGEFORMAT
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------
AQ$_SCHEDULES TABLE
AQ$_SCHEDULES_PRIMARY INDEX
DBMS_REPCAT_AUTH PACKAGE
DBMS_REPCAT_AUTH PACKAGE BODY
HELP TABLE
HELP_TOPIC_SEQ INDEX
这些选项都是允许的。如果要清除其他共有的duplicate objects,请参考NOTE.1030426.6
4. 更改log_archive_format参数
形式:LOG_ARCHIVE_FORMAT='archive_%t_%s_%r.dbf'
5. 核对时区TIMESTAMP WITH TIMEZONE
SHAPE \* MERGEFORMAT
SQL> select TZ_VERSION from registry$database;
TZ_VERSION ---------- 3 |
因为11g的时区版本为11,在dbua执行检查会告警,提示打补丁升级。
7. 检查字符集是否为UTF8 或者AL16UTF16
8. 检查Optimizer Statistics
SHAPE \* MERGEFORMAT
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
PL/SQL 过程已成功完成。 |
9. 检查Database Vault option.选项
10. 检查是否安装em,是否开启
11. 关闭listener
4.2.2目标数据库准备
安装11.2.0.1软件,选择仅安装软件,类型选择单实例。安装目录:C:\app\Administrator\product\11.2.0\dbhome_1。安装的10g和11g需要在不同的安装目录,最好在不同的盘符。并安装相应listener.
4.2.3利用utlu112i.sql检查源数据库
SQL> @C:\app\Administrator\product\11.2.0\dbhome_3\RDBMS\ADMIN\utlu112i.sql
对升级前机型测试。
Oracle Database 11.2 Pre-Upgrade Information Tool 07-12-2011 15:47:49
.
**********************************************************************
Database:
**********************************************************************
--> name: TEST
--> version: 10.2.0.3.0
--> compatible: 10.2.0.3.0
--> blocksize: 8192
--> platform.: Microsoft Windows IA (32-bit)
--> timezone file: V3
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 726 MB
.... AUTOEXTEND additional space required: 246 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 455 MB
.... AUTOEXTEND additional space required: 430 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 463 MB
.... AUTOEXTEND additional space required: 213 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
.... AUTOEXTEND additional space required: 41 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
WARNING: --> "sga_target" needs to be increased to at least 336 MB
WARNING: --> "java_pool_size" needs to be increased to at least 64 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]
**********************************************************************
--> background_dump_dest 11.1 DEPRECATED replaced by
"diagnostic_dest"
--> user_dump_dest 11.1 DEPRECATED replaced by
"diagnostic_dest"
--> core_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] VALID
--> OLAP Catalog [upgrade] VALID
--> 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] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using a timezone file older than version 11.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 10.2.0.3.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Database contains schemas with stale optimizer statistics.
.... Refer to the Upgrade Guide for instructions to update
.... schema statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
.... SYS;
.... SYSMAN
WARNING: --> Database contains schemas with objects dependent on network
packages.
.... Refer to the Upgrade Guide for instructions to configure Network ACLs.
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:--> recycle bin in use.
.... Your recycle bin turned on.
.... 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.
. PL/SQL 过程已成功完成。
7.根据测试结果对源数据库进行配置
修改表空间和参数大小
五、Dbua升级数据库
1.打开11g的dbua工具
2.在选择数据库中会出现所要升级的数据库,现版本为10.2.03,输入其sys的密码。
4.获取完所要升级的数据库信息后,弹出告警信息。
告警处理:
l 网络方案,可以忽略
l 时区告警,处理方式,升级完毕打补丁
l 过时的优化程序统计信息, 执行SQL>exec dbms_stats.gather_dictionary_stats
l 未处理
5.因为已经做过数据库冷备份,选择不备份数据库
6.升级过程中不移动数据库文件。
7.指定快速恢复区,因为10g建立的快速恢复区,所以默认位置不变。
8.开始升级
9.升级结果,完成
10.配置数据库口令
如果要恢复旧的数据库,则选择恢复数据库
六、升级后测试
1.检查版本和参数
C:\Documents and Settings\Administrator>set ORACLE_SID=test
C:\Documents and Settings\Administrator>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on 星期三 7月 13 13:11:17 2011
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> show parameter diag
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest string C:\APP\ADMINISTRATOR
SQL> show sga
Total System Global Area 351522816 bytes
Fixed Size 1374556 bytes
Variable Size 201328292 bytes
Database Buffers 142606336 bytes
Redo Buffers 6213632 bytes
SQL> show parameter sga;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 336M
sga_target big integer 336M
SQL> show parameter memory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 0
memory_target big integer 0
shared_memory_address integer 0
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string C:\APP\ADMINISTRATOR\PRODUCT\1
1.2.0\DBHOME_1\DATABASE\SPFILE
TEST.ORA
2.TOAD和sqldeveloper登陆
Sqldeveloper登陆顺利,在用toad登陆的时候,出现如下warning:
查找资料,11.1的client端对11.2的server是支持的,所以关闭忽略。
3.登陆Enterprise Manager Database Control 测试正常
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22821701/viewspace-702468/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22821701/viewspace-702468/