可以升级oracle版本吗,Oracle跨版本升级

1、首先在原有环境中安装11.2.0.3rdbms软件到新目录

使用oracle用户修改.bash_profile环境变量添加11g安装信息

export ORACLE_BASE=/oracle/app1/oracle

export ORACLE_HOME=/oracle/app1/oracle/product/11.2/db_1

show parameters instance;

2、预升级脚本

SQL> @/u01/app/oracle/product/11.2/db_1/rdbms/admin/utlu112i.sql

spool /tmp/upgrade.log

select tablespace_name,bytes/1024/1024 from dba_data_files;

/oradata/fsdb/system01.dbf

SYSTEM 300

/oradata/fsdb/undotbs01.dbf

UNDOTBS1 230

/oradata/fsdb/sysaux01.dbf

SYSAUX 120

/oradata/fsdb/users01.dbf

USERS 5

select file_name,file_id from dba_data_files;

查询id

/oradata/fsdb/system01.dbf

/oradata/fsdb/undotbs01.dbf

/oradata/fsdb/sysaux01.dbf

/oradata/fsdb/users01.dbf

alter database datafile 1 resize 800M; 修改数据文件大小或者添加数据文件(更常用)

alter tablespace undotbs1 add datafile’/u01/app/oracle/oradata/update/undotbs02.dbf’ size 500M; 在UNDOTBS1表空间添加数据文件(sql不区分大小写,路径添加单引号)

alter database datafile 3 resize 300M;

desc dba_temp_files;

select file_name,file_id,bytes/1024/1024 from dba_temp_files;

alter tablespace temp add tempfile ‘/u01/app/oracle/oradata/update/temp02.dbf’ size 100M;

3、生成pfile，将建议中提到的选项进行修改

create pfile=’/tmp/fsdb.ora’ from spfile;

vi /tmp/fsdb.ora

*.audit_file_dest=’/oracle/app/oracle/admin/fsdb/adump’

*.compatible=‘11.2.0.0.0’

*.control_files=’/oradata/fsdb/control01.ctl’,’/oradata/fsdb/control02.ctl’,’/oradata/fsdb/control03.ctl’

*.db_block_size=8192

*.db_domain=’’

*.db_file_multiblock_read_count=16

*.db_name=‘fsdb’

*.dispatchers=’(PROTOCOL=TCP) (SERVICE=orclXDB)’

*.job_queue_processes=10

*.open_cursors=300

*.pga_aggregate_target=199229440

*.processes=150

*.remote_login_passwordfile=‘EXCLUSIVE’

*.sessions=170

*.sga_target=736870912

*.undo_management=‘AUTO’

*.undo_tablespace=‘UNDOTBS1’

*.diagnostic_dest=’/oracle/app/oracle’

mv fsdb.ora /u01/app/oracle/product/11.2/db_1/dbs/initfsdb.ora

ls -l /u01/app/oracle/product/11.2/db_1/dbs/initfsdb.ora

EXECUTE dbms_stats.gather_dictionary_stats;

4.关闭实例 shutdown immediate 退出

在高版本的base目录下创建adump目录(必须有文件夹,有无内容均可)

cd /oracle/app/oracle

cp -r admin /oracle/app/oracle

5.修改参数文件.bash_profile 改为高版本环境变量

vi .bash_profile

. ./.bash_profile

6.使用生成的pfile启动高版本软件到升级模式

指定参数文件位置

startup upgrade pfile=’/tmp/fsdb.ora’

或者将参数文件拷贝至默认位置

cp /tmp/fsdb.ora/oracle/app1/oracle/product/11.2/db_1/dbs/initfsdb.ora

8.运行升级脚本进行升级

SQL> @?/rdbms/admin/catupgrd

10、查看数据库组件状态及版本是否正常

参照

SQL> @/tmp/utlu112i.sql

Oracle Database 11.2 Pre-Upgrade Information Tool 11-05-2014 14:26:29

Script Version: 11.2.0.3.0 Build: 001

.

Database:

–> name: ORCL

–> version: 10.2.0.5.0

–> compatible: 10.2.0.1.0

–> blocksize: 8192

–> platform: Linux x86 64-bit

–> timezone file: V4

.

Tablespaces: [make adjustments in the current environment]

–> SYSTEM tablespace is adequate for the upgrade.

… minimum required size: 743 MB

–> UNDOTBS1 tablespace is adequate for the upgrade.

… minimum required size: 400 MB

–> SYSAUX tablespace is adequate for the upgrade.

… minimum required size: 456 MB

–> TEMP tablespace is adequate for the upgrade.

… minimum required size: 60 MB

–> EXAMPLE tablespace is adequate for the upgrade.

… minimum required size: 69 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:

WARNING: --> “sga_target” needs to be increased to at least 596 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”

.

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 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: --> Database contains INVALID objects prior to upgrade.

… The list of invalid SYS/SYSTEM objects was written to

… registrysysinvobjs.....Thelistofnon−SYS/SYSTEMobjectswaswrittento....registrysys_inv_objs.

.... The list of non-SYS/SYSTEM objects was written to

.... registrysysi​nvo​bjs.....Thelistofnon−SYS/SYSTEMobjectswaswrittento....registrynonsys_inv_objs.

… Use utluiobj.sql after the upgrade to identify any new invalid

… objects due to the upgrade.

… USER MDSYS has 103 INVALID objects.

… USER DMSYS has 1 INVALID objects.

… USER PUBLIC has 169 INVALID objects.

… USER CTXSYS has 9 INVALID objects.

… USER EXFSYS has 1 INVALID objects.

… USER PM has 1 INVALID objects.

… USER SH has 1 INVALID objects.

… USER OE has 3 INVALID objects.

… USER XDB has 7 INVALID objects.

… USER SYS has 106 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 22 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.