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;

预升级脚本

SQL> @/oracle/app1/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

1

 

/oradata/fsdb/undotbs01.dbf

2

 

/oradata/fsdb/sysaux01.dbf

3

 

/oradata/fsdb/users01.dbf

4

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

alter tablespace undotbs1 add datafile '/oradata/fsdb/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/fsdb/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

ll /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/app1/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

.... 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 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.

 

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

转载于:https://www.cnblogs.com/YJDBA/p/9724369.html