oracle database 11...,升级oracle database 11.1到12.1

升级oracle database 11.1到12.1

oracle 11.1可以直接升级到12.1的版本，详细可以见mos相关可以直接升级的指导

os:RHEL 5.5

oracle:11.1.0.7, 单实例

方式:采用out-of place的升级方式

11.1的ORACLE_HOME为/u01/app/oracle/product/11.1.7/db_1

12.1的ORACLE_HOME为/u01/app/oracle/product/12.1.0/db_1

1,rman备份原有数据库;

2,安装oracle 12的软件到/u01/app/oracle/product/12.1.0/db_1;

3,清除回收站，检查无效的对像和无效的组件,确认隐含参数使用情况.必须的对像.

例12c xdb是必须要组件，

4,收集gater_dictionary_stats和gather_fixed_objects_stats,aud$对像清空,

5,对数据库库进行逻辑检查 ,rman:backup validate check logical database;

6,在11.1的库实行升级预检查.SQL> @/u01/app/oracle/product/12.1.0/db_1/rdbms/admin/preupgrd.sql;

该过程会生成一个log文件，对于其中的问题需要解决然后才能升级.

7, 使用12C的orapwd生成新的密码文件主，使用原来的spfile生成一个pfile。

8,关闭原来的实例，使用新的ORACLE_HOME启动数据库

[oracle@localhost ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1

[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.1.0 Production on Sat May 10 13:34:57 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> conn / as sysdba;

Connected to an idle instance.

SQL>  startup upgrade pfile='/home/oracle/12.ora';

ORACLE instance started.

9, 并行运行脚本(12c新特性)，并行度8,会启动16个并行服务器进程.

[oracle@localhost admin]$ /u01/app/oracle/product/12.1.0/db_1/perl/bin/perl catctl.pl -n 8 catupgrd.sql

Serial   Phase #:54 Files: 1      Time: 157s

Serial   Phase #:55 Files: 1      Time: 141s

Serial   Phase #:56 Files: 1      Time: 48s

Grand Total Time: 2439s

10,以正常的方式启动数据,并且创建spfile

SQL> startup nomount pfile='/home/oracle/12.ora';

ORACLE instance started.

Total System Global Area 2288205824 bytes

Fixed Size                  2290848 bytes

Variable Size            1275071328 bytes

Database Buffers          989855744 bytes

Redo Buffers               20987904 bytes

SQL> create spfile from pfile='/home/oracle/12.ora';

File created.

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 2288205824 bytes

Fixed Size                  2290848 bytes

Variable Size            1275071328 bytes

Database Buffers          989855744 bytes

Redo Buffers               20987904 bytes

Database mounted.

Database opened.

11,重新收集gather_dictionary_stats和gather_fixed_objects_stats

SQL> exec dbms_stats.gather_dictionary_stats;

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_fixed_objects_stats;

PL/SQL procedure successfully completed.

12,重新编译无效对像

SQL> @utlprp 8;   (cpu核数的一到两倍)

TIMESTAMP

--------------------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_BGN  2014-05-10 14:40:44

.................

13, 运行utlu121s.sql和utluiobj.sql检查无效的对像.

SQL> @utlu121s.sql;

.

Oracle Database 12.1 Post-Upgrade Status Tool           05-10-2014 14:46:51

.

Component                               Current         Version  Elapsed Time

Name                                    Status          Number   HH:MM:SS

.

Oracle Server

.                                         VALID      12.1.0.1.0  00:07:37

JServer JAVA Virtual Machine

.                                         VALID      12.1.0.1.0  00:02:29

Oracle Workspace Manager

.                                         VALID      12.1.0.1.0  00:00:38

OLAP Analytic Workspace

.                                         VALID      12.1.0.1.0  00:00:17

Oracle OLAP API

.                                         VALID      12.1.0.1.0  00:00:13

Oracle XDK

.                                         VALID      12.1.0.1.0  00:01:03

Oracle Text

.                                         VALID      12.1.0.1.0  00:00:41

Oracle XML Database

.                                         VALID      12.1.0.1.0  00:02:38

Oracle Database Java Packages

.                                         VALID      12.1.0.1.0  00:00:07

Oracle Multimedia

.                                         VALID      12.1.0.1.0  00:02:06

Spatial

.                                         VALID      12.1.0.1.0  00:03:04

Oracle Application Express

.                                         VALID     4.2.0.00.27  00:13:31

Final Actions

.                                                                00:01:49

Total Upgrade Time: 00:36:40

PL/SQL procedure successfully completed.

SQL> @utluiobj.sql;

.

Oracle Database 12.1 Post-Upgrade Invalid Objects Tool 05-10-2014 14:47:09

.

This tool lists post-upgrade invalid objects that were not invalid

prior to upgrade (it ignores pre-existing pre-upgrade invalid objects).

.

Owner                     Object Name                     Object Type

PL/SQL procedure successfully completed.