同机上升级oracle10.2.0.5到11.2.0.4

最新推荐文章于 2022-03-03 12:53:56 发布

coujiongyong0208

最新推荐文章于 2022-03-03 12:53:56 发布

阅读量149

点赞数

文章标签：数据库 java

在同一台机器上将oracle10.2.0.4升级到11.2.0.5。以下是升级过程
1、在本机上首先安装oracle11.2.0.4的数据库环境

安装过程就不用多说了，建好目录，上传相关的包等，正常进行安装即可！

2、将11g ORACLE_HOME/rdbms/admin/utlu112i.sql拷贝到oracle10g相应的目录中

[oracle@scmtest ~]$ cp /u01/app/oracle/product/11.2.0/db_1/rdbms/admin/utlu112i.sql / u01/app/oracle/product/10.2.0/db1/rdbms/admin/

[oracle@scmtest ~]$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db1

[oracle@scmtest ~]$ cd /u01/app/oracle/product/10.2.0/db1/bin

./sqlplus "/as sysdba"

SQL> spool /u02/upgrade.log;

SQL> @/u01/app/oracle/product/10.2.0/db1/rdbms/admin/utlu112i.sql;

得到一个检查结果：

SQL> @?/rdbms/admin/utlu112i.sql;
Oracle Database 11.2 Pre-Upgrade Information Tool 11-12-2013 08:24:15
Script Version: 11.2.0.4.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--&gt name:          YLINDB
--&gt version:       10.2.0.5.0
--&gt compatible:    10.2.0.5.0
--&gt blocksize:     8192
--&gt platform:      Linux x86 64-bit
--&gt timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--&gt SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 1036 MB
--&gt UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--&gt SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 1142 MB
--&gt TEMP1 tablespace is adequate for the upgrade.
.... minimum required size: 60 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.
**********************************************************************
--&gt If Target Oracle is 32-Bit, refer here for Update Parameters:
WARNING: --&gt "java_pool_size" needs to be increased to at least 64 MB
.

--&gt If Target Oracle is 64-Bit, refer here for Update Parameters:
WARNING: --&gt "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]
**********************************************************************
--&gt background_dump_dest 11.1 DEPRECATED replaced by "diagnostic_dest"
--&gt user_dump_dest 11.1 DEPRECATED replaced by "diagnostic_dest"
.

**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--&gt Oracle Catalog Views         [upgrade] VALID
--&gt Oracle Packages and Types    [upgrade] VALID
--&gt JServer JAVA Virtual Machine [upgrade] VALID
--&gt Oracle XDK for Java          [upgrade] VALID
--&gt Oracle Workspace Manager     [upgrade] VALID
--&gt OLAP Analytic Workspace      [upgrade] VALID
--&gt OLAP Catalog                 [upgrade] VALID
--&gt EM Repository                [upgrade] VALID
--&gt Oracle Text                  [upgrade] VALID
--&gt Oracle XML Database          [upgrade] VALID
--&gt Oracle Java Packages         [upgrade] VALID
--&gt Oracle interMedia            [upgrade] VALID
--&gt Spatial                      [upgrade] VALID
--&gt Data Mining                  [upgrade] VALID
--&gt Expression Filter            [upgrade] VALID
--&gt Rule Manager                 [upgrade] VALID
--&gt Oracle OLAP API              [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --&gt 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.5.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --&gt 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 MDSYS has 18 INVALID objects.
.... USER DMSYS has 2 INVALID objects.
.... USER PUBLIC has 341 INVALID objects.
.... USER CTXSYS has 1 INVALID objects.
.... USER ORACLE_OCM has 2 INVALID objects.
.... USER EXFSYS has 8 INVALID objects.
.... USER DBSNMP has 4 INVALID objects.
.... USER SYSMAN has 3 INVALID objects.
.... USER XDB has 3 INVALID objects.
.... USER SYS has 411 INVALID objects.
.... USER WMSYS has 25 INVALID objects.
WARNING: --&gt 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.
.
**********************************************************************
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.

**********************************************************************

根据结果调整参数，内存资源。
主要调整：java_pool_size,列出来的几个表空间的大小是否满足要求

3、创建11g参数文件

[oracle@scmtest ~]$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db1

[oracle@scmtest ~]$ cd /u01/app/oracle/product/10.2.0/db1/bin

[oracle@scmtest ~]$ ./sqlplus "/as sysdba"

SQL> create pfile='/u02/upgrade11g.ora' from spfile;

修改参数文件:
java_pool_size                >=128m
background_dump_dest          =>diagnostic_dest
user_dump_dest                =>diagnostic_dest

这里必须修改，否则到后面启动时报参数错误

[oracle@scmtest ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
[oracle@scmtest ~]$ cd /u01/app/oracle/product/11.2.0/db_1/bin
[oracle@scmtest ~]$ ./sqlplus "/as sysdba"

SQL> create spfile from pfile = ='/u02/upgrade11g.ora' ;

4、以upgrade启动数据库（11g ORACLE_HOME）

sqlplus "/as sysdba"

SQL> STARTUP UPGRADE

5、开始升级

SQL> SPOOL /u02/upgrade.log

SQL> @catupgrd.sql

这里运行时间很长，运行结束后，会自动关闭数据库，并退出sqlplus

The catupgrd.sql script determines which upgrade scripts must be run, runs them, and then shuts down the database. You must run the script in the Oracle Database 11g Release 2 (11.2) environment.

The upgrade script creates and alters certain data dictionary tables. It also upgrades or installs the following database components in the new Oracle Database 11g Release 2 (11.2) database:

6、重新启动数据库，查看升级结果
SQL> STARTUP

SQL> @?/rdbms/admin/utlu112s.sql

检查升级结果

7、运行catuppst.sql，执行升级

SQL> @?/rdbms/admin/catuppst.sql

This may generate the following informational messages:
Generating apply and rollback scripts...
Check the following file for errors:
Apply script: .*
Rollback script: .*
Executing script file...
Updating registry...
Check the following log file for errors:

Generating apply and rollback scripts...
Check the following file for errors:
.../cfgtoollogs/catbundle/catbundle_PSU_*.log
Apply script:
.../rdbms/admin/catbundle_PSU_*_APPLY.sql
Rollback script:
.../rdbms/admin/catbundle_PSU_*_ROLLBACK.sql
Executing script file...

Updating registry...
Check the following log file for errors:
.../cfgtoollogs/catbundle/catbundle_PSU_*.log

执行以下脚本，对失效对象进行再编译
SQL> @?/rdbms/admin/utlrp.sql

检查是否还存在失效对象

SQL> SELECT count(*) FROM dba_invalid_objects;

SQL> SELECT distinct object_name FROM dba_invalid_objects;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29371470/viewspace-1168690/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29371470/viewspace-1168690/