探索Oracle之数据库升级六 11.2.0.4.3 Upgrade12c(12.1.0.1)

      Oracle 12c发布距今已经一年有余了，其最大亮点是一个可以插拔的数据库(PDB)，这是在之前版本没有的；但是如果我们要将以前版本的数据库升级到12c来，那么也应顺其自然的将其变成一个pdb，那么我们的工作不仅包含了数据库软件的升级，同时也包含如何将一个NO-CDB的数据库plug to CDB none。

二、升级要求:

三、升级前准备:      

   3.1、查看数据库版本及补丁信息

SQL select * from v$version;

BANNER

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

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

PL/SQL Release 11.2.0.4.0 - Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 – Production

SQL show parameter name;

NAME TYPE VALUE

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

cell_offloadgroup_name string

db_file_name_convert string

db_name string woo

db_unique_name string woo

global_names boolean FALSE

instance_name string woo

lock_name_space string

log_file_name_convert string

processor_group_name string

service_names string woo

SQL set pagesize 500

SQL set line 300 

SQL col comp_name format a40

SQL col comp_name format a35

SQL col version format a15

SQL col status format a7

SQL select comp_name,version,status from dba_registry;

COMP_NAME VERSION STATUS

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

Oracle Enterprise Manager 11.2.0.4.0 VALID

OWB 11.2.0.3.0 VALID

Oracle Application Express 3.2.1.00.12 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 Types 11.2.0.4.0 VALID

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

18 rows selected.

[oracle@db01 OPatch]$ ./opatch lspatches

18522509;Database Patch Set Update : 11.2.0.4.3 (18522509) 

3.2、备份数据库

[oracle@db01 Phycal]$ pwd

 /DBBackup/Phycal

 [oracle@db01 Phycal]$ rman target / 

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Nov 20 04:41:01 2014 

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. 

connected to target database: WOO (DBID=4199532651) 

RMAN run{

 2 allocate channel chan_name type disk;

 3 backup database format /DBBackup/Phycal/WOO%U.bak TAG before_upgrade;

 4 BACKUP CURRENT CONTROLFILE;

 5 } 

 using target database control file instead of recovery catalog

 allocated channel: chan_name

 channel chan_name: SID=28 device type=DISK 

 Starting backup at 20-NOV-14

 channel chan_name: starting full datafile backup set

 channel chan_name: specifying datafile(s) in backup set

 input datafile file number=00001 name=/DBData/woo/woo/system01.dbf

 input datafile file number=00002 name=/DBData/woo/woo/sysaux01.dbf

 input datafile file number=00003 name=/DBData/woo/woo/undotbs01.dbf

 input datafile file number=00004 name=/DBData/woo/woo/users01.dbf

 channel chan_name: starting piece 1 at 20-NOV-14

 channel chan_name: finished piece 1 at 20-NOV-14

 piece handle=/DBBackup/Phycal/WOO0apo2ar4_1_1.bak tag=BEFORE_UPGRADE comment=NONE

 channel chan_name: backup set complete, elapsed time: 00:01:25

 channel chan_name: starting full datafile backup set

 channel chan_name: specifying datafile(s) in backup set

 including current control file in backup set

 including current SPFILE in backup set

 channel chan_name: starting piece 1 at 20-NOV-14

 channel chan_name: finished piece 1 at 20-NOV-14

 piece handle=/DBBackup/Phycal/WOO0bpo2atp_1_1.bak tag=BEFORE_UPGRADE comment=NONE

 channel chan_name: backup set complete, elapsed time: 00:00:01

 Finished backup at 20-NOV-14 

 Starting backup at 20-NOV-14

 channel chan_name: starting full datafile backup set

 channel chan_name: specifying datafile(s) in backup set

 including current control file in backup set

 channel chan_name: starting piece 1 at 20-NOV-14

 channel chan_name: finished piece 1 at 20-NOV-14

 piece handle=/DBSoft/fast_recovery_area/WOO/backupset/2014_11_20/o1_mf_ncnnf_TAG20141120T044237_b6t05y5x_.bkp tag=TAG20141120T044237 comment=NONE

 channel chan_name: backup set complete, elapsed time: 00:00:01

 Finished backup at 20-NOV-14

 released channel: chan_name 

RMAN list backupset; 


 BS Key Type LV Size Device Type Elapsed Time Completion Time

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

 9 Full 1.20G DISK 00:01:24 20-NOV-14 

 BP Key: 9 Status: AVAILABLE Compressed: NO Tag: BEFORE_UPGRADE

 Piece Name: /DBBackup/Phycal/WOO0apo2ar4_1_1.bak

 List of Datafiles in backup set 9

 File LV Type Ckp SCN Ckp Time Name

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

 1 Full 3242521 20-NOV-14 /DBData/woo/woo/system01.dbf

 2 Full 3242521 20-NOV-14 /DBData/woo/woo/sysaux01.dbf

 3 Full 3242521 20-NOV-14 /DBData/woo/woo/undotbs01.dbf

 4 Full 3242521 20-NOV-14 /DBData/woo/woo/users01.dbf 

 BS Key Type LV Size Device Type Elapsed Time Completion Time

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

 10 Full 9.64M DISK 00:00:02 20-NOV-14 

 BP Key: 10 Status: AVAILABLE Compressed: NO Tag: BEFORE_UPGRADE

 Piece Name: /DBBackup/Phycal/WOO0bpo2atp_1_1.bak

 SPFILE Included: Modification time: 12-NOV-14

 SPFILE db_unique_name: WOO

 Control File Included: Ckp SCN: 3242657 Ckp time: 20-NOV-14 

 BS Key Type LV Size Device Type Elapsed Time Completion Time

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

 11 Full 9.61M DISK 00:00:01 20-NOV-14 

 BP Key: 11 Status: AVAILABLE Compressed: NO Tag: TAG20141120T044237

 Piece Name: /DBSoft/fast_recovery_area/WOO/backupset/2014_11_20/o1_mf_ncnnf_TAG20141120T044237_b6t05y5x_.bkp

 Control File Included: Ckp SCN: 3242671 Ckp time: 20-NOV-14 

四、执行预升级脚本:
4.1 根据Metalink ID:1503653 step 3中提到的到Metalink ID:556610.1下载预升级脚本preupgrd.sql检查是否满足升级条件。

[oracle@db01 ~]$ sqlplus / as sysdba @preupgrd.sql

SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 20 05:18:26 2014

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

Loading Pre-Upgrade Package...

Executing Pre-Upgrade Checks...

Pre-Upgrade Checks Complete.

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

Results of the checks are located at:

 /DBSoft/cfgtoollogs/woo/preupgrade/preupgrade.log

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

 /DBSoft/cfgtoollogs/woo/preupgrade/preupgrade_fixups.sql

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

 /DBSoft/cfgtoollogs/woo/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

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

SQL 

       在这里已经把需要修改的相关操作封装到了preupgrade_fixups.sql脚本中，执行该脚本按照提示修复存在的问题即可。

4.2 主要需要修复如下问题: 

       ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE;   

       @$ORACLE_HOME/rdbms/admin/emremove.sql   

       @$ORACLE_HOME/olap/admin/catnoamd.sql   

        EXECUTE dbms_stats.gather_dictionary_stats;

4.3 执行dbupgdiag.sql收集升级前信息   

    如果有异常参考Metalink ID:556610.1进行修改。

[oracle@db01 ~]$ ll dbupgdiag.sql 

-rwxr-xr-x 1 oracle oinstall 24140 Nov 20 04:52 dbupgdiag.sql

[oracle@db01 ~]$ sqlplus / as sysdba @dbupgdiag.sql

SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 20 05:04:47 2014

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

Enter location for Spooled output:

Enter value for 1: 

SP2-0137: DEFINE requires a value following equal sign

20_Nov_2014_0504 .log

Enter value for log_path: /home/oracle/diag/

SP2-0606: Cannot create SPOOL file \"/home/oracle/woo_upgrade_diag.log/db_upg_diag_woo_20_Nov_2014_0504.log\"


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

PL/SQL Release 11.2.0.4.0 - Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

=============

Compatibility

=============

Compatibility is set as 11.2.0.0.0

================

Archive Log Mode

================

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 151

Next log sequence to archive 153

Current log sequence 153

================

Auditing Check

================

NAME TYPE VALUE

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

audit_file_dest string /DBSoft/admin/woo/adump

audit_sys_operations boolean FALSE

audit_syslog_level string

audit_trail string DB

================

Cluster Check

================

NAME TYPE VALUE

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

cluster_database boolean FALSE

cluster_database_instances integer 1

DOC ################################################################

DOC If CLUSTER_DATABASE is set to TRUE, change it to FALSE before

DOC upgrading the database

DOC ################################################################

DOC #

===========================================

Tablespace and the owner of the aud$ table

===========================================

OWNER TABLESPACE_NAME

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

SYS SYSTEM

============================================================================

count of records in the sys.aud$ table where dbid is null- Standard Auditing

============================================================================

============================================================================================

count of records in the system.aud$ when dbid is null, Std Auditing with OLS or DV installed

============================================================================================

select count(*) from system.aud$ where dbid is null

ERROR at line 1:

ORA-00942: table or view does not exist

=============================================================================

count of records in the sys.fga_log$ when dbid is null, Fine Grained Auditing

=============================================================================

==========================================

Oracle Label Security is installed or not

==========================================

Oracle Label Security is NOT installed at database level

================

Number of AQ Records in Message Queue Tables

================

SYS - ALERT_QT - 58

SYS - AQ$_MEM_MC - 0

SYS - AQ_EVENT_TABLE - 0

SYS - AQ_PROP_TABLE - 0

SYS - KUPC$DATAPUMP_QUETAB - 0

SYS - SCHEDULER$_EVENT_QTAB - 0

SYS - SCHEDULER$_REMDB_JOBQTAB - 0

SYS - SCHEDULER_FILEWATCHER_QT - 0

SYS - SYS$SERVICE_METRICS_TAB - 0

SYSMAN - MGMT_LOADER_QTABLE - 0

SYSMAN - MGMT_NOTIFY_INPUT_QTABLE - 0

SYSMAN - MGMT_NOTIFY_QTABLE - 0

SYSMAN - MGMT_PAF_MSG_QTABLE_1 - 0

SYSMAN - MGMT_PAF_MSG_QTABLE_2 - 0

SYSMAN - MGMT_TASK_QTABLE - 28

SYSTEM - DEF$_AQCALL - 0

SYSTEM - DEF$_AQERROR - 0

WMSYS - WM$EVENT_QUEUE_TABLE - 0

================

Time Zone version

================

================

Local Listener

================


USERNAME TEMPORARY_TABLESPACE DEFAULT_TABLESPACE

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

MGMT_VIEW TEMP SYSTEM

SYS TEMP SYSTEM

SYSTEM TEMP SYSTEM

DBSNMP TEMP SYSAUX

SYSMAN TEMP SYSAUX

OUTLN TEMP SYSTEM

FLOWS_FILES TEMP SYSAUX

MDSYS TEMP SYSAUX

ORDSYS TEMP SYSAUX

EXFSYS TEMP SYSAUX

WMSYS TEMP SYSAUX

APPQOSSYS TEMP SYSAUX

APEX_030200 TEMP SYSAUX

OWBSYS_AUDIT TEMP SYSAUX

ORDDATA TEMP SYSAUX

CTXSYS TEMP SYSAUX

ANONYMOUS TEMP SYSAUX

XDB TEMP SYSAUX

ORDPLUGINS TEMP SYSAUX

OWBSYS TEMP SYSAUX

SI_INFORMTN_SCHEMA TEMP SYSAUX

OLAPSYS TEMP SYSAUX

SCOTT TEMP USERS

ORACLE_OCM TEMP USERS

XS$NULL TEMP USERS

MDDATA TEMP USERS

DIP TEMP USERS

APEX_PUBLIC_USER TEMP USERS

SPATIAL_CSW_ADMIN_USR TEMP USERS

SPATIAL_WFS_ADMIN_USR TEMP USERS

================

Component Status

================

Comp ID Component Status Version Org_Version Prv_Version

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

AMD OLAP Catalog VALID 11.2.0.4.0 11.2.0.3.0 11.2.0.3.0

APEX Oracle Application Express VALID 3.2.1.00.12

APS OLAP Analytic Workspace VALID 11.2.0.4.0 11.2.0.3.0 11.2.0.3.0

CATALOG Oracle Database Catalog Views VALID 11.2.0.4.0 11.2.0.3.0 11.2.0.3.0

CATJAVA Oracle Database Java Packages VALID 11.2.0.4.0 11.2.0.3.0 11.2.0.3.0

CATPROC Oracle Database Packages and Types VALID 11.2.0.4.0 11.2.0.3.0 11.2.0.3.0

CONTEXT Oracle Text VALID 11.2.0.4.0 11.2.0.3.0 11.2.0.3.0

EM Oracle Enterprise Manager VALID 11.2.0.4.0

EXF Oracle Expression Filter VALID 11.2.0.4.0 11.2.0.3.0 11.2.0.3.0

JAVAVM JServer JAVA Virtual Machine VALID 11.2.0.4.0 11.2.0.3.0 11.2.0.3.0

ORDIM Oracle Multimedia VALID 11.2.0.4.0 11.2.0.3.0 11.2.0.3.0

OWB OWB VALID 11.2.0.3.0

OWM Oracle Workspace Manager VALID 11.2.0.4.0 11.2.0.3.0

RUL Oracle Rules Manager VALID 11.2.0.4.0 11.2.0.3.0 11.2.0.3.0

SDO Spatial VALID 11.2.0.4.0 11.2.0.3.0 11.2.0.3.0

XDB Oracle XML Database VALID 11.2.0.4.0 11.2.0.3.0 11.2.0.3.0

XML Oracle XDK VALID 11.2.0.4.0 11.2.0.3.0 11.2.0.3.0

XOQ Oracle OLAP API VALID 11.2.0.4.0 11.2.0.3.0 11.2.0.3.0

======================================================

List of Invalid Database Objects Owned by SYS / SYSTEM

======================================================


Number of Invalid Objects

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

There are no Invalid Objects

DOC ################################################################

DOC If there are no Invalid objects below will result in zero rows.

DOC ################################################################

DOC #

no rows selected

================================

List of Invalid Database Objects

================================

Number of Invalid Objects

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

There are no Invalid Objects

DOC ################################################################

DOC If there are no Invalid objects below will result in zero rows.

DOC ################################################################

DOC #

no rows selected

======================================================

Count of Invalids by Schema

======================================================


==============================================================

Identifying whether a database was created as 32-bit or 64-bit

==============================================================

DOC ###########################################################################

DOC Result referencing the string \B023\ == Database was created as 32-bit

DOC Result referencing the string \B047\ == Database was created as 64-bit

DOC When String results in \B023\ and when upgrading database to 10.2.0.3.0

DOC (64-bit) , For known issue refer below articles

DOC Note 412271.1 ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While

DOC Upgrading Or Patching Databases To 10.2.0.3

DOC Note 579523.1 ORA-600 [22635], ORA-600 [KOKEIIX1], ORA-7445 [KOPESIZ] and

DOC OCI-21500 [KOXSIHREAD1] Reported While Upgrading To 11.1.0.6

DOC ###########################################################################

DOC #

Metadata Initial DB Creation Info

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

B047 Database was created as 64-bit

===================================================

Number of Duplicate Objects Owned by SYS and SYSTEM

===================================================

Counting duplicate objects ....

 COUNT(1)

----------

=========================================

Duplicate Objects Owned by SYS and SYSTEM

=========================================

Querying duplicate objects ....

DOC ################################################################################

DOC If any objects found please follow below article.

DOC Note 1030426.6 How to Clean Up Duplicate Objects Owned by SYS and SYSTEM schema

DOC Read the Exceptions carefully before taking actions.

DOC ################################################################################

DOC #

========================

Password protected roles

========================

DOC ################################################################################

DOC In version 11.2 password protected roles are no longer enabled by default so if

DOC an application relies on such roles being enabled by default and no action is

DOC performed to allow the user to enter the password with the set role command, it

DOC is recommended to remove the password from those roles (to allow for existing

DOC privileges to remain available). For more information see:

DOC Note 745407.1 : What Roles Can Be Set as Default for a User?

DOC ################################################################################

DOC #

Querying for password protected roles ....


Password protected Role Assigned by default to user

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

OWB$CLIENT OWBSYS

================

JVM Verification

================


================================================

Checking Existence of Java-Based Users and Roles

================================================


DOC ################################################################################

DOC There should not be any Java Based users for database version 9.0.1 and above.

DOC If any users found, it is faulty JVM.

DOC ################################################################################

DOC #


DOC Healthy JVM Should contain Six Roles.

DOC If there are more or less than six role, JVM is inconsistent.

DOC ###############################################################

DOC #


DOC Check the status of the main JVM interface packages DBMS_JAVA

DOC and INITJVMAUX and make sure it is VALID.

DOC If there are no Invalid objects below will result in zero rows.

DOC #################################################################

DOC #

no rows selected

DOC #################################################################

DOC If the JAVAVM component is not installed in the database (for

DOC example, after creating the database with custom scripts), the

DOC next query will report the following error:

DOC select dbms_java.longname(\foo\) \"JAVAVM TESTING\" from dual

DOC *

DOC ERROR at line 1:

DOC ORA-00904: \"DBMS_JAVA\".\"LONGNAME\": invalid identifier

DOC If the JAVAVM component is installed, the query should succeed

DOC with \foo\ as result.

DOC #################################################################

DOC #

JAVAVM TESTING

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

===================================

Oracle Multimedia/InterMedia status

===================================

Oracle Multimedia/interMedia is installed and listed with the following version: 11.2.0.4.0 and status: VALID

Checking for installed Database Schemas...

ORDSYS user exists.

ORDPLUGINS user exists.

MDSYS user exists.

SI_INFORMTN_SCHEMA user exists.

ORDDATA user exists.

Checking for Prerequisite Components...

JAVAVM installed and listed as valid

XDK installed and listed as valid

XDB installed and listed as valid

Validating Oracle Multimedia/interMedia...(no output if component status is valid)

PL/SQL procedure successfully completed.

 *** End of LogFile ***


[oracle@db01 ~]$ cd $ORACLE_HOME/rdbms/admin 

[oracle@db01 admin]$ sqlplus / as sysdba @utlrp.sql

SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 20 06:13:55 2014

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

TIMESTAMP

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

COMP_TIMESTAMP UTLRP_BGN 2014-11-20 06:13:56

DOC The following PL/SQL block invokes UTL_RECOMP to recompile invalid

DOC objects in the database. Recompilation time is proportional to the

DOC number of invalid objects in the database, so this command may take

DOC a long time to execute on a database with a large number of invalid

DOC objects.

DOC Use the following queries to track recompilation progress:

DOC 1. Query returning the number of invalid objects remaining. This

DOC number should decrease with time.

DOC SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);

DOC 2. Query returning the number of objects compiled so far. This number

DOC should increase with time.

DOC SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;

DOC This script automatically chooses serial or parallel recompilation

DOC based on the number of CPUs available (parameter cpu_count) multiplied

DOC by the number of threads per CPU (parameter parallel_threads_per_cpu).

DOC On RAC, this number is added across all RAC nodes.

DOC UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel

DOC recompilation. Jobs are created without instance affinity so that they

DOC can migrate across RAC nodes. Use the following queries to verify

DOC whether UTL_RECOMP jobs are being created and run correctly:

DOC 1. Query showing jobs created by UTL_RECOMP

DOC SELECT job_name FROM dba_scheduler_jobs

DOC WHERE job_name like \UTL_RECOMP_SLAVE_%\;

DOC 2. Query showing UTL_RECOMP jobs that are running

DOC SELECT job_name FROM dba_scheduler_running_jobs

DOC WHERE job_name like \UTL_RECOMP_SLAVE_%\;

DOC #

PL/SQL procedure successfully completed.

TIMESTAMP

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

COMP_TIMESTAMP UTLRP_END 2014-11-20 06:14:06

DOC The following query reports the number of objects that have compiled

DOC with errors.

DOC If the number is higher than expected, please examine the error

DOC messages reported with each object (using SHOW ERRORS) to see if they

DOC point to system misconfiguration or resource constraints that must be

DOC fixed before attempting to recompile these objects.

DOC #

OBJECTS WITH ERRORS

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

DOC The following query reports the number of errors caught during

DOC recompilation. If this number is non-zero, please query the error

DOC messages in the table UTL_RECOMP_ERRORS to see if any of these errors

DOC are due to misconfiguration or resource constraints that must be

DOC fixed before objects can compile successfully.

DOC #

ERRORS DURING RECOMPILATION

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

Function created.

PL/SQL procedure successfully completed.

Function dropped.

PL/SQL procedure successfully completed.

SQL 


五、开始安装ORACLE 12C软件

   5.1 创建12c安装所需目录

[oracle@db01 DBSoft]$ mkdir -p /DBSoft/Product/12.1.0/db_1

[oracle@db01 DBSoft]$ chown -R oracle:oinstall /DBSoft/Product/12.1.0

[oracle@db01 DBSoft]$ chmod -R 755 /DBSoft/Product/12.1.0

[oracle@db01 DBSoft]$ cd /DBSoft/Product/12.1.0 






     5.2 修改用户环境变量：

[oracle@db01 Product]$ vi ~/.bash_profile

export PATH

export EDITOR=vi

export ORACLE_SID=woo

export ORACLE_BASE=/DBSoft

export ORACLE_HOME=$ORACLE_BASE/Product/12.1.0/db_1

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export PATH=$ORACLE_HOME/bin:$PATH

umask 022 




      5.3 解压缩软件:

[oracle@db01 ~]$ unzip linuxamd64_12c_database_1of2.zip

[oracle@db01 ~]$ unzip linuxamd64_12c_database_2of2.zip






 六、开始安装12c软件:
     6.1 进入解压目录执行./runInstaller 开启12c安装进程


   6.2 直接点击Next，下一步开始安装


    6.3 选择skip software updates，点击Next


     6.4 选择最后一个选项 Upgrade an existing database，点击Next



      6.5 选择所有的语言，点击Next



      6.6 选择第一个选项 EntePrise Edition (6.4GB)



     6.7 指定Oracle 12c的安装目录，会从环境变量中自动读取，点击Next



      6.8 安装Oracle的所属组和用户，无误后点击Next



    6.9 检查Oracle 12c的安装信息，无误后点击Install 开始安装



    6.10 开始安装，这个过程等待约15分钟



     6.11 弹出提示执行root.sh脚本



     6.12 开启新窗口，通过root用户登录，执行root.sh脚本



     6.13 执行完root.sh脚本后，点击完成，自动弹出监听配置，开始配置监听



      6.14 配置监听名称，默认即可



    6.15 选择监听可用的协议，通常配置TCP就可以了



     6.16 配置端口，使用默认的1521，点击即可



     6.17 问你是否还需要配置其他监听，选择No，点击Next即可



     6.18 这步提示是否需要创建一个用于客户端连接的服务，选择No，点击Next即可



     6.19 点击Finish，完成网络的配置



     6.20 现在开始对数据库进行升级(DBUA)，选择第一个选项Upgrade Oracle Database，选择Next



     6.21 选择需要升级的数据库，点击Next



     6.22 执行升级检查，选择fix，后点击Next



     6.23 配置升级选项，所有的都选上，设置和CPU数量相同的并行度，点击Next



     6.24 选择EM配置，点击Next



     6.25 升级是否移动数据文件，这里不需要移动，点击Next



    6.26 选择需要升级的监听，点击Next



   6.27 数据恢复选项，升级前是否对数据库进行一次备份操作，选择备份，并指定路径，点击Next



     6.28 检查下Summary，点击Finish开始升级



     6.29 现在开始执行升级操作



     6.30 至此升级已经完成，耗时至少四个小时以上，务必预留好足够多的升级时间。



    6.31 升级已经完成，点击Next



     6.32 点击Close关闭升级窗口



 

        至此11g upgrade 12c已经接近尾声了，后续还需要做一些升级后的检查。

 

七、执行升级后的检查

    7.1 Oracle 12c版本及组件状态的检查

 

 

SQL select banner from v$version;

BANNER

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

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

PL/SQL Release 12.1.0.1.0 - Production

CORE 12.1.0.1.0 Production

TNS for Linux: Version 12.1.0.1.0 - Production

NLSRTL Version 12.1.0.1.0 – Production

SQL set pagesize 500

SQL set line 300 

SQL col comp_name format a40

SQL col comp_name format a35

SQL col version format a15

SQL col status format a7

SQL select comp_name,version,status from dba_registry;

COMP_NAME VERSION STATUS

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

Oracle Application Express 4.2.0.00.27 VALID

OWB 11.2.0.3.0 VALID

OLAP Catalog 11.2.0.4.0 OPTION

Spatial 12.1.0.1.0 VALID

Oracle Multimedia 12.1.0.1.0 VALID

Oracle XML Database 12.1.0.1.0 VALID

Oracle Text 12.1.0.1.0 VALID

Oracle Workspace Manager 12.1.0.1.0 VALID

Oracle Database Catalog Views 12.1.0.1.0 VALID

Oracle Database Packages and Types 12.1.0.1.0 VALID

JServer JAVA Virtual Machine 12.1.0.1.0 VALID

Oracle XDK 12.1.0.1.0 VALID

Oracle Database Java Packages 12.1.0.1.0 VALID

OLAP Analytic Workspace 12.1.0.1.0 VALID

Oracle OLAP API 12.1.0.1.0 VALID

15 rows selected. 

 

     7.2  从上面我们可以看到有些组件的状态是不对的，这是老版本的组件，不能直接通过升级上SQL进行删除

SQL @$ORACLE_HOME/rdbms/admin/emremove.sql

SQL @$ORACLE_HOME/olap/admin/catnoamd.sql

SQL EXECUTE dbms_stats.gather_dictionary_stats; 




    7.3 再次查看就没有问题了:

SQL select comp_name,version,status from dba_registry;

COMP_NAME VERSION STATUS

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

Oracle Application Express 4.2.0.00.27 VALID

OWB 11.2.0.3.0 VALID

Spatial 12.1.0.1.0 VALID

Oracle Multimedia 12.1.0.1.0 VALID

Oracle XML Database 12.1.0.1.0 VALID

Oracle Text 12.1.0.1.0 VALID

Oracle Workspace Manager 12.1.0.1.0 VALID

Oracle Database Catalog Views 12.1.0.1.0 VALID

Oracle Database Packages and Types 12.1.0.1.0 VALID

JServer JAVA Virtual Machine 12.1.0.1.0 VALID

Oracle XDK 12.1.0.1.0 VALID

Oracle Database Java Packages 12.1.0.1.0 VALID

OLAP Analytic Workspace 12.1.0.1.0 VALID

Oracle OLAP API 12.1.0.1.0 VALID

14 rows selected. 






   7.4  /etc/oratab信息对比

---升级前

[root@db01 ~]# grep DBSoft /etc/oratab 

woo:/DBSoft/Product/11.2.0/db_1:N

---升级后

[root@db01 ~]# grep DBSoft /etc/oratab 

woo:/DBSoft/Product/12.1.0/db_1:N

注意：这部分不需要我们手工去修改/etc/oratab记录，执行升级会自动完成修改。 




    7.5 升级后执行postupgrade_fixups.sql检查

SQL @/DBSoft/cfgtoollogs/woo/preupgrade/postupgrade_fixups.sql

Post Upgrade Fixup Script Generated on 2014-11-20 05:18:29 Version: 12.1.0.1 Build: 007

Beginning Post-Upgrade Fixups...

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

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

Check Tag: OLD_TIME_ZONES_EXIST

Check Summary: Check for use of older timezone data file

Fix Summary: Update the timezone using the DBMS_DST package after upgrade is complete.

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

Fixup Returned Information:

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

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

PL/SQL procedure successfully completed.

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

 [Post-Upgrade Recommendations]

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

PL/SQL procedure successfully completed.

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

 ******** 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 ^^^

PL/SQL procedure successfully completed.

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

 ************* Fixup Summary ************

 1 fixup routine generated an INFORMATIONAL message that should be reviewed.





    7.6 检查DST
     从升级后的检查结果来看，DST目前是18，说明不需要人工处理了，如果不是需要手工来处理，参考Metalink

 ID: 977512.1 或者参考Metalink ID 1585343.1 

SQL r

 1 SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

 2 FROM DATABASE_PROPERTIES

 3 WHERE PROPERTY_NAME LIKE \DST_%\

 4* ORDER BY PROPERTY_NAME

PROPERTY_NAME VALUE

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

DST_PRIMARY_TT_VERSION 18

DST_SECONDARY_TT_VERSION 0

DST_UPGRADE_STATE NONE

SQL select * from v$timezone_file;

FILENAME VERSION CON_ID

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

timezlrg_18.dat 18 0

SQL select TZ_VERSION from registry$database;

TZ_VERSION

----------

 18 

     7.7  字符集检查

检查国家字符集，如果是以下字符集则不需要做操作:

如果返回结果是 UTF8或者

 AL16UTF16，那么什么都不需要做了。
如果返回结果不是 UTF8或者 AL16UTF16，那么请参考下面的文档：   

Note 276914.1 

ID 276914.1)

 strong SQL select value from nls_database_parameters where parameter=\NLS_NCHAR_CHARACTERSET\;

VALUE

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

AL16UTF16 

 /strong 

      7.8 修改参数文件中版本号

SQL show parameter compatible

NAME TYPE VALUE

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

compatible string 11.2.0.0.0

noncdb_compatible boolean FALSE

SQL alter system set compatible = \12.1.0.1.0\ scope=spfile;

System altered.

SQL startup force;

ORACLE instance started.

Total System Global Area 2772574208 bytes

Fixed Size 2292240 bytes

Variable Size 2533361136 bytes

Database Buffers 218103808 bytes

Redo Buffers 18817024 bytes

Database mounted.

Database opened.

SQL show parameter compatible

NAME TYPE VALUE

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

compatible string 12.1.0.1.0

noncdb_compatible boolean FALSE

SQL 


     至此，经过漫长而辛苦的升级，我们已经将11.2.0.4顺利升级到了12.1.0.1，整个升级过程虽然有点长，但还是比较顺利的。故需再生产环境中升级请大家务必预留好可用于升级的时间窗口，升级时间确实是非常的长。


 


 prudentwoo

 10g/11g OCP 11g OCM，ITPUB和CSDN专家及专家讲师；有着多年数据库从业经验，资深Oracle数据库专家，现就职于北京海量数据技术股份有限公司担任高级dba职务，为央视，银行，电信等各行业及企业提供过技术支持服务