实验环境:

操作系统:redhat6.4

源数据库版本:oracle 11.2.0.4

目标数据库版本:oracle 12.2.0.1

一、升级前准备

1)查看组件状态

SQL> col COMP_NAME for a30

SQL> set line 200

SQL> select comp_name,version,status from dba_registry;

COMP_NAME                      VERSION                        STATUS

—————————— —————————— ———————-

OWB                            11.2.0.4.0                     VALID

Oracle Application Express     3.2.1.00.12                    VALID

Oracle Enterprise Manager      11.2.0.4.0                     VALID

OLAP Catalog                   11.2.0.4.0                     VALID

Spatial                        11.2.0.4.0                     VALID

Oracle Multimedia              11.2.0.4.0                     VALID

Oracle XML Database            11.2.0.4.0                     VALID

Oracle Text                    11.2.0.4.0                     VALID

Oracle Expression Filter       11.2.0.4.0                     VALID

Oracle Rules Manager           11.2.0.4.0                     VALID

Oracle Workspace Manager       11.2.0.4.0                     VALID

Oracle Database Catalog Views  11.2.0.4.0                     VALID

Oracle Database Packages and T 11.2.0.4.0                     VALID

ypes

JServer JAVA Virtual Machine   11.2.0.4.0                     VALID

Oracle XDK                     11.2.0.4.0                     VALID

Oracle Database Java Packages  11.2.0.4.0                     VALID

OLAP Analytic Workspace        11.2.0.4.0                     VALID

Oracle OLAP API                11.2.0.4.0                     VALID

2)检查无效对象,并创建临时表以作对比

SQL> select count(*),status from dba_objects group by status;

COUNT(*) STATUS

———- ——-

86381 VALID

SQL> create table invalid_object_20170321 as select * from dba_objects where status<>’VALID’;

Table created.

3)检查sys重复对象

SQL> select object_name, object_type from dba_objects where (object_name,object_type) in (select object_name,object_type from dba_objects where owner = ‘SYS’) and owner = ‘SYSTEM’;

OBJECT_NAME                    OBJECT_TYPE

—————————— ——————-

AQ$_SCHEDULES                  TABLE

AQ$_SCHEDULES_PRIMARY          INDEX

DBMS_REPCAT_AUTH               PACKAGE BODY

DBMS_REPCAT_AUTH               PACKAGE

二、创建回退方案

备份数据文件控制文件redo等,若升级失败,使用11g软件直接打开备份的文件即可

三、安装12c数据库软件

[oracle@test soft]$ unzip linuxx64_12201_database.zip

图略

四、准备升级

1) 拷贝12c自带的预检工具

cp /oracle/app2/product/12.2.0/db_1/suptools/orachk/.cgrep/preupgrd.sql /home/oracle

源数据库预检:

[oracle@test temp]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 22 10:07:27 2017

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @/oracle/temp/preupgrd.sql

Loading Pre-Upgrade Package…

Executing Pre-Upgrade Checks…

Pre-Upgrade Checks Complete.

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

Results of the checks are located at:

/oracle/app/cfgtoollogs/drb/preupgrade/preupgrade.log

Pre-Upgrade Fixup Script (run in source database environment):

/oracle/app/cfgtoollogs/drb/preupgrade/preupgrade_fixups.sql

Post-Upgrade Fixup Script (run shortly after upgrade):

/oracle/app/cfgtoollogs/drb/preupgrade/postupgrade_fixups.sql

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

Fixup scripts must be reviewed prior to being executed.

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

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

====>> USER ACTION REQUIRED  <<====

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

The following are *** ERROR LEVEL CONDITIONS *** that must be addressed

prior to attempting your upgrade.

Failure to do so will result in a failed upgrade.

1) Check Tag:    PURGE_RECYCLEBIN

Check Summary: Check that recycle bin is empty prior to upgrade

Fixup Summary:

“The recycle bin will be purged.”

You MUST resolve the above error prior to upgrade

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

预检的结果存放于/oracle/app/cfgtoollogs/drb/preupgrade/preupgrade.log

[oracle@test .cgrep]$ more  /oracle/app/cfgtoollogs/drb/preupgrade/preupgrade.log

Oracle Database Pre-Upgrade Information Tool 03-22-2017 10:08:05

Script Version: 12.1.0.1.0 Build: 006

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

Database Name:  DRB

Version:  11.2.0.4.0

Compatible:  11.2.0.4.0

Blocksize:  8192

Platform:  Linux x86 64-bit

Timezone file:  V14

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

[Renamed Parameters]

[No Renamed Parameters in use]

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

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

[Obsolete/Deprecated Parameters]

[No Obsolete or Desupported Parameters in use]

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

[Component List]

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

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

–> Oracle Enterprise Manager Repository   [upgrade]  VALID

–> Oracle Text                            [upgrade]  VALID

–> Oracle XML Database                    [upgrade]  VALID

–> Oracle Java Packages                   [upgrade]  VALID

–> Oracle Multimedia                      [upgrade]  VALID

–> Oracle Spatial                         [upgrade]  VALID

–> Expression Filter                      [upgrade]  VALID

–> Rule Manager                           [upgrade]  VALID

–> Oracle Application Express             [upgrade]  VALID

–> Oracle OLAP API                        [upgrade]  VALID

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

[Tablespaces]

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

–> SYSTEM tablespace is adequate for the upgrade.

minimum required size: 1250 MB

–> SYSAUX tablespace is adequate for the upgrade.

minimum required size: 1474 MB

–> UNDOTBS1 tablespace is adequate for the upgrade.

minimum required size: 400 MB

–> TEMP tablespace is adequate for the upgrade.

minimum required size: 60 MB

[No adjustments recommended]

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

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

[Pre-Upgrade Checks]

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

WARNING: –> Enterprise Manager Database Control repository found in the database

In Oracle Database 12c, Database Control is removed during

the upgrade. To save time during the Upgrade, this action

can be done prior to upgrading using the following steps after

copying rdbms/admin/emremove.sql from the new Oracle home

– Stop EM Database Control:

$> emctl stop dbconsole

– Connect to the Database using the SYS account AS SYSDBA:

SET ECHO ON;

SET SERVEROUTPUT ON;

@emremove.sql

Without the set echo and serveroutput commands you will not

be able to follow the progress of the script.

WARNING: –> Standby database not synced

Sync standby database prior to upgrade.

Your standby databases should be synched prior to upgrading.

WARNING: –> Existing DBMS_LDAP dependent objects

Database contains schemas with objects dependent on DBMS_LDAP package.

Refer to the Upgrade Guide for instructions to configure Network ACLs.

USER APEX_030200 has dependent objects.

INFORMATION: –> OLAP Catalog(AMD) exists in database

Starting with Oracle Database 12c, OLAP is desupported.

If you are not using the OLAP Catalog component and want

to remove it, then execute the

ORACLE_HOME/oraolap/admin/catnoamd.sql script before or

after the upgrade.

INFORMATION: –> Older Timezone in use

Database is using a time zone file older than version 18.

After the upgrade, it is recommended that DBMS_DST package

be used to upgrade the 11.2.0.4.0 database time zone version

to the latest version which comes with the new release.

Please refer to My Oracle Support note number 977512.1 for details.

ERROR: –> RECYCLE_BIN not empty.

Your recycle bin contains 3 object(s).

It is REQUIRED that the recycle bin is empty prior to upgrading.

Immediately before performing the upgrade, execute the following

command:

EXECUTE dbms_preup.purge_recyclebin_fixup;

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

[Pre-Upgrade Recommendations]

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

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

********* Dictionary Statistics *********

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

Please gather dictionary statistics 24 hours prior to

upgrading the database.

To gather dictionary statistics execute the following command

while connected as SYSDBA:

EXECUTE dbms_stats.gather_dictionary_stats;

^^^ MANUAL ACTION SUGGESTED ^^^

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

[Post-Upgrade Recommendations]

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

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

******** Fixed Object Statistics ********

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

Please create stats on fixed objects two weeks

after the upgrade using the command:

EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

^^^ MANUAL ACTION SUGGESTED ^^^

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

************  Summary  ************

1 ERROR exist that must be addressed prior to performing your upgrade.

3 WARNINGS that Oracle suggests are addressed to improve database performance.

2 INFORMATIONAL messages that should be reviewed prior to your upgrade.

After your database is upgraded and open in normal mode you must run

rdbms/admin/catuppst.sql which executes several required tasks and completes

the upgrade process.

You should follow that with the execution of rdbms/admin/utlrp.sql, and a

comparison of invalid objects before and after the upgrade using

rdbms/admin/utluiobj.sql

If needed you may want to upgrade your timezone data using the process

described in My Oracle Support note 977512.1

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

按照预检结果进行操作

1. 清理回收站

SQL> EXECUTE dbms_preup.purge_recyclebin_fixup

PL/SQL procedure successfully completed.

SQL> PURGE DBA_RECYCLEBIN

DBA Recyclebin purged.

2.收集数字字典统计信息

SQL> EXECUTE dbms_preup.purge_recyclebin_fixup

PL/SQL procedure successfully completed.

SQL> EXECUTE dbms_stats.gather_dictionary_stats;

PL/SQL procedure successfully completed.

3.在升级前卸载EM,可减少升级所需时间

SET ECHO ON;

SET SERVEROUTPUT ON;

12c软件下 $ORACLE_HOME/rdbms/admin复制emremove.sql

运行@?/emremove.sql 卸载

4.确认无数据文件需介质恢复

SQL> select * from v$recover_file ;

no rows selected

5.确认无数据文件处于backup模式

SQL> SELECT * FROM v$backup WHERE status != ‘NOT ACTIVE’;

no rows selected

6.处理分布式事务

SQL> select * from dba_2pc_pending;
If this returns rows you should do the following:

SQL> SELECT local_tran_id
FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry(”);
SQL> COMMIT;

7.拷贝参数文件,密码文件至12c软件dbs

修改参数文件

*.compatible=’11.2.0.4.0′ 修改为目标版本

*.processes=300 12c 最少300

删除*.sec_case_sensitive_logon=false

本次实验,我将pga,cache,memory等参数注释,只剩余sga大小,由数据库自行分配

8.修改环境变量

修改.bash_profile一下两行

export ORACLE_BASE=/oracle/app2

export ORACLE_HOME=/oracle/app2/product/12.2.0/dbhome_1

9.关闭源数据库以及监听

五、升级数据库

1. 使用12c的软件以upgrade模式打开数据库

SQL> startup upgrade

ORACLE instance started.

2. 刷数据字典

3. [oracle@test admin]$ /oracle/app2/product/12.2.0/db_1/perl/bin/perl catctl.pl catupgrd.sql

Argument list for [catctl.pl]

Run in                c = 0

Do not run in         C = 0

Input Directory       d = 0

Echo OFF              e = 1

Simulate              E = 0

Forced cleanup        F = 0

Log Id                i = 0

Child Process         I = 0

Log Dir               l = 0

Priority List Name    L = 0

Upgrade Mode active   M = 0

SQL Process Count     n = 0

SQL PDB Process Count N = 0

Open Mode Normal      o = 0

Start Phase           p = 0

End Phase             P = 0

Reverse Order         r = 0

AutoUpgrade Resume    R = 0

Script                s = 0

Serial Run            S = 0

RO User Tablespaces   T = 0

Display Phases        y = 0

Debug catcon.pm       z = 0

Debug catctl.pl       Z = 0

catctl.pl VERSION: [12.2.0.1.0]

STATUS: [production]

BUILD: [RDBMS_12.2.0.1.0_LINUX.X64_170125]

/oracle/app2/product/12.2.0/db_1/rdbms/admin/orahome = [/oracle/app2/product/12.2.0/db_1]

/oracle/app2/product/12.2.0/db_1/bin/orabasehome = [/oracle/app2/product/12.2.0/db_1]

catctlGetOrabase = [/oracle/app2/product/12.2.0/db_1]

Analyzing file /oracle/app2/product/12.2.0/db_1/rdbms/admin/catupgrd.sql

Log file directory = [/tmp/cfgtoollogs/upgrade20170322144739]

catcon: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20170322144739/catupgrd_catcon_3409.lst]

catcon: See [/tmp/cfgtoollogs/upgrade20170322144739/catupgrd*.log] files for output generated by scripts

catcon: See [/tmp/cfgtoollogs/upgrade20170322144739/catupgrd_*.lst] files for spool files, if any

Number of Cpus        = 1

Database Name         = drb

DataBase Version      = 11.2.0.4.0

catcon: ALL catcon-related output will be written to [/oracle/app2/product/12.2.0/db_1/cfgtoollogs/drb/upgrade20170322144740/catupgrd_catcon_3409.lst]

catcon: See [/oracle/app2/product/12.2.0/db_1/cfgtoollogs/drb/upgrade20170322144740/catupgrd*.log] files for output generated by scripts

catcon: See [/oracle/app2/product/12.2.0/db_1/cfgtoollogs/drb/upgrade20170322144740/catupgrd_*.lst] files for spool files, if any

Log file directory = [/oracle/app2/product/12.2.0/db_1/cfgtoollogs/drb/upgrade20170322144740]

Parallel SQL Process Count            = 4

Components in [drb]

Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT JAVAVM ORDIM OWM SDO XDB XML XOQ]

Not Installed [DV EM MGW ODM OLS RAC WK]

——————————————————

Phases [0-115]         Start Time:[2017_03_22 14:47:41]

——————————————————

***********   Executing Change Scripts   ***********

Serial   Phase #:0    [drb] Files:1

…..

消耗一小时三十分钟,产生3G归档,升级前可调大undo日志的大小

4. 重启数据库至正常读写状态,刷新无效对象,并执行预检生成的修复脚本

[oracle@test admin]$ cd $ORACLE_HOME/rdbms/admin

[oracle@test admin]$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b catuppst -d ”’.”’ catuppst.sql

catcon: ALL catcon-related output will be written to [/oracle/app2/product/12.2.0/db_1/rdbms/admin/catuppst_catcon_14071.lst]

catcon: See [/oracle/app2/product/12.2.0/db_1/rdbms/admin/catuppst*.log] files for output generated by scripts

catcon: See [/oracle/app2/product/12.2.0/db_1/rdbms/admin/catuppst_*.lst] files for spool files, if any

catcon.pl: completed successfully

[oracle@backup admin]$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d ”’.”’ utlrp.sql

此时dba_registry内所有组件状态从UPGRADED变为VALID

执行预检生成的修复脚本,包括重新收集x$ 基表和字典 统计信息等

SQL> @/home/oracle/precheck.log/postupgrade_fixups.sql

修复脚本之后还需执行如下脚本

@/rdbms/admin/utlu122s.sql

后续将tnsname.ora listeners.ora从源环境拷贝至新环境

查看组件状态

Oracle Server                             VALID      12.2.0.1.0

JServer JAVA Virtual Machine              VALID      12.2.0.1.0

Oracle Workspace Manager                  VALID      12.2.0.1.0

OLAP Analytic Workspace                   VALID      12.2.0.1.0

Oracle OLAP API                           VALID      12.2.0.1.0

Oracle XDK                                VALID      12.2.0.1.0

Oracle Text                               VALID      12.2.0.1.0

Oracle XML Database                       VALID      12.2.0.1.0

Oracle Database Java Packages             VALID      12.2.0.1.0

Oracle Multimedia                         VALID      12.2.0.1.0

Spatial                                   VALID      12.2.0.1.0

Oracle Application Express                VALID     5.0.4.00.12




Related posts

coded by nessus
分享:  DeliciousGReader鲜果豆瓣CSDN网摘
Trackback

no comment untill now

Add your comment now

无觅相关文章插件