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