探索Oracle之資料庫升級六 11.2.0.4.3 Upgrade12c(12.2.0.1)

dbhelper發表於2015-01-17
Oracle資料庫

探索Oracle之資料庫升級六
11.2.0.4.3 Upgrade12c(12.2.0.1)

一、前言：

       Oracle 12c釋出距今已經一年有餘了，其最大亮點是一個可以插拔的資料庫(PDB)，這是在之前版本沒有的；但是如果我們要將以前版本的資料庫升級到12c來，那麼也應順其自然的將其變成一個pdb，那麼我們的工作不僅包含了資料庫軟體的升級，同時也包含如何將一個NO-CDB的資料庫plug to CDB none。

二、升級要求:
 

三、升級前準備:

3.1、檢視資料庫版本及補丁資訊
 
  1. SQL> select * from v$version;

  2. BANNER
  3. --------------------------------------------------------------------------------
  4. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  5. PL/SQL Release 11.2.0.4.0 - Production
  6. CORE 11.2.0.4.0 Production
  7. TNS for Linux: Version 11.2.0.4.0 - Production
  8. NLSRTL Version 11.2.0.4.0 – Production

  10. SQL> show parameter name;

  12. NAME TYPE VALUE
  13. ------------------------------------ ----------- ------------------------------
  14. cell_offloadgroup_name string
  15. db_file_name_convert string
  16. db_name string woo
  17. db_unique_name string woo
  18. global_names boolean FALSE
  19. instance_name string woo
  20. lock_name_space string
  21. log_file_name_convert string
  22. processor_group_name string
  23. service_names string woo

  25. SQL> set pagesize 500
  26. SQL> set line 300
  27. SQL> col comp_name format a40
  28. SQL> col comp_name format a35
  29. SQL> col version format a15
  30. SQL> col status format a7
  31. SQL> select comp_name,version,status from dba_registry;

  33. COMP_NAME VERSION STATUS
  34. ----------------------------------- --------------- -------
  35. Oracle Enterprise Manager 11.2.0.4.0 VALID
  36. OWB 11.2.0.3.0 VALID
  37. Oracle Application Express 3.2.1.00.12 VALID
  38. OLAP Catalog 11.2.0.4.0 VALID
  39. Spatial 11.2.0.4.0 VALID
  40. Oracle Multimedia 11.2.0.4.0 VALID
  41. Oracle XML Database 11.2.0.4.0 VALID
  42. Oracle Text 11.2.0.4.0 VALID
  43. Oracle Expression Filter 11.2.0.4.0 VALID
  44. Oracle Rules Manager 11.2.0.4.0 VALID
  45. Oracle Workspace Manager 11.2.0.4.0 VALID
  46. Oracle Database Catalog Views 11.2.0.4.0 VALID
  47. Oracle Database Packages and Types 11.2.0.4.0 VALID
  48. JServer JAVA Virtual Machine 11.2.0.4.0 VALID
  49. Oracle XDK 11.2.0.4.0 VALID
  50. Oracle Database Java Packages 11.2.0.4.0 VALID
  51. OLAP Analytic Workspace 11.2.0.4.0 VALID
  52. Oracle OLAP API 11.2.0.4.0 VALID

  54. 18 rows selected.

  56. [oracle@db01 OPatch]$ ./opatch lspatches
  57. 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;


List of Backup Sets
===================


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檢查是否滿足升級條件。  
  1. [oracle@db01 ~]$ sqlplus / as sysdba @preupgrd.sql

  3. SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 20 05:18:26 2014
  4. Copyright (c) 1982, 2013, Oracle. All rights reserved.

  6. Connected to:
  7. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  8. With the Partitioning, OLAP, Data Mining and Real Application Testing options

  10. Loading Pre-Upgrade Package...
  11. Executing Pre-Upgrade Checks...
  12. Pre-Upgrade Checks Complete.
  13.       ************************************************************

  15. Results of the checks are located at:
  16.  /DBSoft/cfgtoollogs/woo/preupgrade/preupgrade.log

  18. Pre-Upgrade Fixup Script (run in source database environment):
  19.  /DBSoft/cfgtoollogs/woo/preupgrade/preupgrade_fixups.sql

  21. Post-Upgrade Fixup Script (run shortly after upgrade):
  22.  /DBSoft/cfgtoollogs/woo/preupgrade/postupgrade_fixups.sql

  24.       ************************************************************

  26.          Fixup scripts must be reviewed prior to being executed.

  28.       ************************************************************

  30.       ************************************************************
  31.                    ====>> USER ACTION REQUIRED <<====
  32.       ************************************************************

  34.  The following are *** ERROR LEVEL CONDITIONS *** that must be addressed
  35.                     prior to attempting your upgrade.
  36.             Failure to do so will result in a failed upgrade.


  39.  1) Check Tag: PURGE_RECYCLEBIN
  40.     Check Summary: Check that recycle bin is empty prior to upgrade
  41.     Fixup Summary:
  42.      \"The recycle bin will be purged.\"

  44.             You MUST resolve the above error prior to upgrade

  46.       ************************************************************

  48. 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進行修改

  1. [oracle@db01 ~]$ ll dbupgdiag.sql
  2. -rwxr-xr-x 1 oracle oinstall 24140 Nov 20 04:52 dbupgdiag.sql

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

  6. SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 20 05:04:47 2014
  7. Copyright (c) 1982, 2013, Oracle. All rights reserved.

  9. Connected to:
  10. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  11. With the Partitioning, OLAP, Data Mining and Real Application Testing options

  13. Enter location for Spooled output:

  15. Enter value for 1:
  16. SP2-0137: DEFINE requires a value following equal sign

  18. 20_Nov_2014_0504 .log

  20. woo_
  21. Enter value for log_path: /home/oracle/diag/
  22. SP2-0606: Cannot create SPOOL file \"/home/oracle/woo_upgrade_diag.log/db_upg_diag_woo_20_Nov_2014_0504.log\"



  26.                           *** Start of LogFile ***

  28.   Oracle Database Upgrade Diagnostic Utility 11-20-2014 05:05:16

  30. ===============
  31. Hostname
  32. ===============

  34. db01

  36. ===============
  37. Database Name
  38. ===============

  40. WOO

  42. ===============
  43. Database Uptime
  44. ===============

  46. 00:34 20-NOV-14

  48. =================
  49. Database Wordsize
  50. =================

  52. This is a 64-bit database

  54. ================
  55. Software Version
  56. ================

  58. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  59. PL/SQL Release 11.2.0.4.0 - Production
  60. CORE 11.2.0.4.0 Production
  61. TNS for Linux: Version 11.2.0.4.0 - Production
  62. NLSRTL Version 11.2.0.4.0 - Production

  64. =============
  65. Compatibility
  66. =============

  68. Compatibility is set as 11.2.0.0.0

  70. ================
  71. Archive Log Mode
  72. ================

  74. Database log mode Archive Mode
  75. Automatic archival Enabled
  76. Archive destination USE_DB_RECOVERY_FILE_DEST
  77. Oldest online log sequence 151
  78. Next log sequence to archive 153
  79. Current log sequence 153

  81. ================
  82. Auditing Check
  83. ================


  86. NAME TYPE VALUE
  87. ------------------------------------ ----------- ------------------------------
  88. audit_file_dest string /DBSoft/admin/woo/adump
  89. audit_sys_operations boolean FALSE
  90. audit_syslog_level string
  91. audit_trail string DB

  93. ================
  94. Cluster Check
  95. ================

  97. NAME TYPE VALUE
  98. ------------------------------------ ----------- ------------------------------
  99. cluster_database boolean FALSE
  100. cluster_database_instances integer 1

  102. DOC>################################################################
  103. DOC>
  104. DOC> If CLUSTER_DATABASE is set to TRUE, change it to FALSE before
  105. DOC> upgrading the database
  106. DOC>
  107. DOC>################################################################
  108. DOC>#

  110. ===========================================
  111. Tablespace and the owner of the aud$ table
  112. ===========================================

  114. OWNER TABLESPACE_NAME
  115. ------------ ------------------------------
  116. SYS SYSTEM

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


  123.          0


  126. ============================================================================================
  127. count of records in the system.aud$ when dbid is null, Std Auditing with OLS or DV installed
  128. ============================================================================================
  129. select count(*) from system.aud$ where dbid is null
  130.                             *
  131. ERROR at line 1:
  132. ORA-00942: table or view does not exist




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

  141.          0



  145. ==========================================
  146. Oracle Label Security is installed or not
  147. ==========================================

  149. Oracle Label Security is NOT installed at database level

  151. ================
  152. Number of AQ Records in Message Queue Tables
  153. ================

  155. SYS - ALERT_QT - 58
  156. SYS - AQ$_MEM_MC - 0
  157. SYS - AQ_EVENT_TABLE - 0
  158. SYS - AQ_PROP_TABLE - 0
  159. SYS - KUPC$DATAPUMP_QUETAB - 0
  160. SYS - SCHEDULER$_EVENT_QTAB - 0
  161. SYS - SCHEDULER$_REMDB_JOBQTAB - 0
  162. SYS - SCHEDULER_FILEWATCHER_QT - 0
  163. SYS - SYS$SERVICE_METRICS_TAB - 0
  164. SYSMAN - MGMT_LOADER_QTABLE - 0
  165. SYSMAN - MGMT_NOTIFY_INPUT_QTABLE - 0
  166. SYSMAN - MGMT_NOTIFY_QTABLE - 0
  167. SYSMAN - MGMT_PAF_MSG_QTABLE_1 - 0
  168. SYSMAN - MGMT_PAF_MSG_QTABLE_2 - 0
  169. SYSMAN - MGMT_TASK_QTABLE - 28
  170. SYSTEM - DEF$_AQCALL - 0
  171. SYSTEM - DEF$_AQERROR - 0
  172. WMSYS - WM$EVENT_QUEUE_TABLE - 0

  174. ================
  175. Time Zone version
  176. ================


  179.         14

  181. ================
  182. Local Listener
  183. ================




  188. ================
  189. Default and Temporary Tablespaces By User
  190. ================


  193. USERNAME TEMPORARY_TABLESPACE DEFAULT_TABLESPACE
  194. ---------------------------- ---------------------- ----------------------
  195. MGMT_VIEW TEMP SYSTEM
  196. SYS TEMP SYSTEM
  197. SYSTEM TEMP SYSTEM
  198. DBSNMP TEMP SYSAUX
  199. SYSMAN TEMP SYSAUX
  200. OUTLN TEMP SYSTEM
  201. FLOWS_FILES TEMP SYSAUX
  202. MDSYS TEMP SYSAUX
  203. ORDSYS TEMP SYSAUX
  204. EXFSYS TEMP SYSAUX
  205. WMSYS TEMP SYSAUX
  206. APPQOSSYS TEMP SYSAUX
  207. APEX_030200 TEMP SYSAUX
  208. OWBSYS_AUDIT TEMP SYSAUX
  209. ORDDATA TEMP SYSAUX
  210. CTXSYS TEMP SYSAUX
  211. ANONYMOUS TEMP SYSAUX
  212. XDB TEMP SYSAUX
  213. ORDPLUGINS TEMP SYSAUX
  214. OWBSYS TEMP SYSAUX
  215. SI_INFORMTN_SCHEMA TEMP SYSAUX
  216. OLAPSYS TEMP SYSAUX
  217. SCOTT TEMP USERS
  218. ORACLE_OCM TEMP USERS
  219. XS$NULL TEMP USERS
  220. MDDATA TEMP USERS
  221. DIP TEMP USERS
  222. APEX_PUBLIC_USER TEMP USERS
  223. SPATIAL_CSW_ADMIN_USR TEMP USERS
  224. SPATIAL_WFS_ADMIN_USR TEMP USERS


  227. ================
  228. Component Status
  229. ================

  231. Comp ID Component Status Version Org_Version Prv_Version
  232. ------- ---------------------------------- --------- -------------- -------------- --------------
  233. AMD OLAP Catalog VALID 11.2.0.4.0 11.2.0.3.0 11.2.0.3.0
  234. APEX Oracle Application Express VALID 3.2.1.00.12
  235. APS OLAP Analytic Workspace VALID 11.2.0.4.0 11.2.0.3.0 11.2.0.3.0
  236. CATALOG Oracle Database Catalog Views VALID 11.2.0.4.0 11.2.0.3.0 11.2.0.3.0
  237. CATJAVA Oracle Database Java Packages VALID 11.2.0.4.0 11.2.0.3.0 11.2.0.3.0
  238. CATPROC Oracle Database Packages and Types VALID 11.2.0.4.0 11.2.0.3.0 11.2.0.3.0
  239. CONTEXT Oracle Text VALID 11.2.0.4.0 11.2.0.3.0 11.2.0.3.0
  240. EM Oracle Enterprise Manager VALID 11.2.0.4.0
  241. EXF Oracle Expression Filter VALID 11.2.0.4.0 11.2.0.3.0 11.2.0.3.0
  242. JAVAVM JServer JAVA Virtual Machine VALID 11.2.0.4.0 11.2.0.3.0 11.2.0.3.0
  243. ORDIM Oracle Multimedia VALID 11.2.0.4.0 11.2.0.3.0 11.2.0.3.0
  244. OWB OWB VALID 11.2.0.3.0
  245. OWM Oracle Workspace Manager VALID 11.2.0.4.0 11.2.0.3.0
  246. RUL Oracle Rules Manager VALID 11.2.0.4.0 11.2.0.3.0 11.2.0.3.0
  247. SDO Spatial VALID 11.2.0.4.0 11.2.0.3.0 11.2.0.3.0
  248. XDB Oracle XML Database VALID 11.2.0.4.0 11.2.0.3.0 11.2.0.3.0
  249. XML Oracle XDK VALID 11.2.0.4.0 11.2.0.3.0 11.2.0.3.0
  250. XOQ Oracle OLAP API VALID 11.2.0.4.0 11.2.0.3.0 11.2.0.3.0



  254. ======================================================
  255. List of Invalid Database Objects Owned by SYS / SYSTEM
  256. ======================================================


  259. Number of Invalid Objects
  260. ------------------------------------------------------------------
  261. There are no Invalid Objects

  263. DOC>################################################################
  264. DOC>
  265. DOC> If there are no Invalid objects below will result in zero rows.
  266. DOC>
  267. DOC>################################################################
  268. DOC>#


  271. no rows selected


  274. ================================
  275. List of Invalid Database Objects
  276. ================================


  279. Number of Invalid Objects
  280. ------------------------------------------------------------------
  281. There are no Invalid Objects

  283. DOC>################################################################
  284. DOC>
  285. DOC> If there are no Invalid objects below will result in zero rows.
  286. DOC>
  287. DOC>################################################################
  288. DOC>#


  291. no rows selected


  294. ======================================================
  295. Count of Invalids by Schema
  296. ======================================================

  298. ==============================================================
  299. Identifying whether a database was created as 32-bit or 64-bit
  300. ==============================================================

  302. DOC>###########################################################################
  303. DOC>
  304. DOC> Result referencing the string \'B023\' ==> Database was created as 32-bit
  305. DOC> Result referencing the string \'B047\' ==> Database was created as 64-bit
  306. DOC> When String results in \'B023\' and when upgrading database to 10.2.0.3.0
  307. DOC> (64-bit) , For known issue refer below articles
  308. DOC>
  309. DOC> Note 412271.1 ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While
  310. DOC> Upgrading Or Patching Databases To 10.2.0.3
  311. DOC> Note 579523.1 ORA-600 [22635], ORA-600 [KOKEIIX1], ORA-7445 [KOPESIZ] and
  312. DOC> OCI-21500 [KOXSIHREAD1] Reported While Upgrading To 11.1.0.6
  313. DOC>
  314. DOC>###########################################################################
  315. DOC>#


  318. Metadata Initial DB Creation Info
  319. -------- -----------------------------------
  320. B047 Database was created as 64-bit

  322. ===================================================
  323. Number of Duplicate Objects Owned by SYS and SYSTEM
  324. ===================================================

  326. Counting duplicate objects ....


  329.   COUNT(1)
  330. ----------
  331.          0

  333. =========================================
  334. Duplicate Objects Owned by SYS and SYSTEM
  335. =========================================

  337. Querying duplicate objects ....


  340. DOC>
  341. DOC>################################################################################
  342. DOC>
  343. DOC> If any objects found please follow below article.
  344. DOC> Note 1030426.6 How to Clean Up Duplicate Objects Owned by SYS and SYSTEM schema
  345. DOC> Read the Exceptions carefully before taking actions.
  346. DOC>
  347. DOC>################################################################################
  348. DOC>#

  350. ========================
  351. Password protected roles
  352. ========================

  354. DOC>
  355. DOC>################################################################################
  356. DOC>
  357. DOC> In version 11.2 password protected roles are no longer enabled by default so if
  358. DOC> an application relies on such roles being enabled by default and no action is
  359. DOC> performed to allow the user to enter the password with the set role command, it
  360. DOC> is recommended to remove the password from those roles (to allow for existing
  361. DOC> privileges to remain available). For more information see:
  362. DOC>
  363. DOC> Note 745407.1 : What Roles Can Be Set as Default for a User?
  364. DOC>
  365. DOC>################################################################################
  366. DOC>#

  368. Querying for password protected roles ....


  371. Password protected Role Assigned by default to user
  372. ------------------------------ ------------------------------
  373. OWB$CLIENT OWBSYS

  375. ================
  376. JVM Verification
  377. ================


  380. ================================================
  381. Checking Existence of Java-Based Users and Roles
  382. ================================================

  384. DOC>
  385. DOC>################################################################################
  386. DOC>
  387. DOC> There should not be any Java Based users for database version 9.0.1 and above.
  388. DOC> If any users found, it is faulty JVM.
  389. DOC>
  390. DOC>################################################################################
  391. DOC>#


  394. User Existence
  395. ---------------------------
  396. No Java Based Users

  398. DOC>
  399. DOC>###############################################################
  400. DOC>
  401. DOC> Healthy JVM Should contain Six Roles.
  402. DOC> If there are more or less than six role, JVM is inconsistent.
  403. DOC>
  404. DOC>###############################################################
  405. DOC>#


  408. Role
  409. ------------------------------
  410. There are 6 JAVA related roles

  412. Roles


  415. ROLE
  416. ------------------------------
  417. JAVA_DEPLOY
  418. JAVAUSERPRIV
  419. JAVAIDPRIV
  420. JAVASYSPRIV
  421. JAVADEBUGPRIV
  422. JAVA_ADMIN

  424. =========================================
  425. List of Invalid Java Objects owned by SYS
  426. =========================================

  428. There are no SYS owned invalid JAVA objects

  430. DOC>
  431. DOC>#################################################################
  432. DOC>
  433. DOC> Check the status of the main JVM interface packages DBMS_JAVA
  434. DOC> and INITJVMAUX and make sure it is VALID.
  435. DOC>
  436. DOC> If there are no Invalid objects below will result in zero rows.
  437. DOC>
  438. DOC>#################################################################
  439. DOC>#


  442. no rows selected


  445. DOC>
  446. DOC>#################################################################
  447. DOC>
  448. DOC> If the JAVAVM component is not installed in the database (for
  449. DOC> example, after creating the database with custom scripts), the
  450. DOC> next query will report the following error:
  451. DOC>
  452. DOC> select dbms_java.longname(\'foo\') \"JAVAVM TESTING\" from dual
  453. DOC> *
  454. DOC> ERROR at line 1:
  455. DOC> ORA-00904: \"DBMS_JAVA\".\"LONGNAME\": invalid identifier
  456. DOC>
  457. DOC> If the JAVAVM component is installed, the query should succeed
  458. DOC> with \'foo\' as result.
  459. DOC>
  460. DOC>#################################################################
  461. DOC>#


  464. JAVAVM TESTING
  465. ---------------
  466. foo

  468. ===================================
  469. Oracle Multimedia/InterMedia status
  470. ===================================

  472. .
  473. Oracle Multimedia/interMedia is installed and listed with the following version: 11.2.0.4.0 and status: VALID
  474. .
  475. Checking for installed Database Schemas...
  476. ORDSYS user exists.
  477. ORDPLUGINS user exists.
  478. MDSYS user exists.
  479. SI_INFORMTN_SCHEMA user exists.
  480. ORDDATA user exists.
  481. .
  482. Checking for Prerequisite Components...
  483. JAVAVM installed and listed as valid
  484. XDK installed and listed as valid
  485. XDB installed and listed as valid
  486. Validating Oracle Multimedia/interMedia...(no output if component status is valid)

  488. PL/SQL procedure successfully completed.


  491.                             *** End of LogFile ***

  493. not spooling currently

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

  497. Upload db_upg_diag_woo_20_Nov_2014_0504.log from \"/home/oracle/diag\" directory

  499. SQL> exit

4.4 升級前檢查無效物件:
 
  1. [oracle@db01 ~]$ cd $ORACLE_HOME/rdbms/admin
  2. [oracle@db01 admin]$ sqlplus / as sysdba @utlrp.sql

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

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


  9. Connected to:
  10. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  11. With the Partitioning, OLAP, Data Mining and Real Application Testing options


  14. TIMESTAMP
  15. --------------------------------------------------------------------------------
  16. COMP_TIMESTAMP UTLRP_BGN 2014-11-20 06:13:56

  18. DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
  19. DOC> objects in the database. Recompilation time is proportional to the
  20. DOC> number of invalid objects in the database, so this command may take
  21. DOC> a long time to execute on a database with a large number of invalid
  22. DOC> objects.
  23. DOC>
  24. DOC> Use the following queries to track recompilation progress:
  25. DOC>
  26. DOC> 1. Query returning the number of invalid objects remaining. This
  27. DOC> number should decrease with time.
  28. DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
  29. DOC>
  30. DOC> 2. Query returning the number of objects compiled so far. This number
  31. DOC> should increase with time.
  32. DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
  33. DOC>
  34. DOC> This script automatically chooses serial or parallel recompilation
  35. DOC> based on the number of CPUs available (parameter cpu_count) multiplied
  36. DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
  37. DOC> On RAC, this number is added across all RAC nodes.
  38. DOC>
  39. DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
  40. DOC> recompilation. Jobs are created without instance affinity so that they
  41. DOC> can migrate across RAC nodes. Use the following queries to verify
  42. DOC> whether UTL_RECOMP jobs are being created and run correctly:
  43. DOC>
  44. DOC> 1. Query showing jobs created by UTL_RECOMP
  45. DOC> SELECT job_name FROM dba_scheduler_jobs
  46. DOC> WHERE job_name like \'UTL_RECOMP_SLAVE_%\';
  47. DOC>
  48. DOC> 2. Query showing UTL_RECOMP jobs that are running
  49. DOC> SELECT job_name FROM dba_scheduler_running_jobs
  50. DOC> WHERE job_name like \'UTL_RECOMP_SLAVE_%\';
  51. DOC>#

  53. PL/SQL procedure successfully completed.


  56. TIMESTAMP
  57. --------------------------------------------------------------------------------
  58. COMP_TIMESTAMP UTLRP_END 2014-11-20 06:14:06

  60. DOC> The following query reports the number of objects that have compiled
  61. DOC> with errors.
  62. DOC>
  63. DOC> If the number is higher than expected, please examine the error
  64. DOC> messages reported with each object (using SHOW ERRORS) to see if they
  65. DOC> point to system misconfiguration or resource constraints that must be
  66. DOC> fixed before attempting to recompile these objects.
  67. DOC>#

  69. OBJECTS WITH ERRORS
  70. -------------------
  71.                   0

  73. DOC> The following query reports the number of errors caught during
  74. DOC> recompilation. If this number is non-zero, please query the error
  75. DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
  76. DOC> are due to misconfiguration or resource constraints that must be
  77. DOC> fixed before objects can compile successfully.
  78. DOC>#

  80. ERRORS DURING RECOMPILATION
  81. ---------------------------
  82.                           0


  85. Function created.


  88. PL/SQL procedure successfully completed.


  91. Function dropped.


  94. PL/SQL procedure successfully completed.

  96. SQL>

五、開始安裝ORACLE 12C軟體

   5.1 建立12c 安裝所需目錄

  1. [oracle@db01 DBSoft]$ mkdir -p /DBSoft/Product/12.1.0/db_1
  2. [oracle@db01 DBSoft]$ chown -R oracle:oinstall /DBSoft/Product/12.1.0
  3. [oracle@db01 DBSoft]$ chmod -R 755 /DBSoft/Product/12.1.0
  4. [oracle@db01 DBSoft]$ cd /DBSoft/Product/12.1.0

       5.2 修改使用者環境變數：
  1. [oracle@db01 Product]$ vi ~/.bash_profile
  2. export PATH
  3. export EDITOR=vi
  4. export ORACLE_SID=woo
  5. export ORACLE_BASE=/DBSoft
  6. export ORACLE_HOME=$ORACLE_BASE/Product/12.1.0/db_1
  7. export LD_LIBRARY_PATH=$ORACLE_HOME/lib
  8. export PATH=$ORACLE_HOME/bin:$PATH
  9. umask 022

     5.3 解壓縮軟體:
  1. [oracle@db01 ~]$ unzip linuxamd64_12c_database_1of2.zip
  2. [oracle@db01 ~]$ unzip linuxamd64_12c_database_2of2.zip
六、開始安裝12c軟體:
     6.1 進入解壓目錄執行./runInstaller 開啟12c安裝程式
 探索Oracle之資料庫升級六 11.2.0.4.3 Upgrade12c(12.2.0.1)

探索Oracle之資料庫升級六 11.2.0.4.3 Upgrade12c(12.2.0.1)

探索Oracle之資料庫升級六 11.2.0.4.3 Upgrade12c(12.2.0.1)
探索Oracle之資料庫升級六 11.2.0.4.3 Upgrade12c(12.2.0.1)

探索Oracle之資料庫升級六 11.2.0.4.3 Upgrade12c(12.2.0.1)

探索Oracle之資料庫升級六 11.2.0.4.3 Upgrade12c(12.2.0.1)

探索Oracle之資料庫升級六 11.2.0.4.3 Upgrade12c(12.2.0.1)

探索Oracle之資料庫升級六 11.2.0.4.3 Upgrade12c(12.2.0.1)

探索Oracle之資料庫升級六 11.2.0.4.3 Upgrade12c(12.2.0.1)

探索Oracle之資料庫升級六 11.2.0.4.3 Upgrade12c(12.2.0.1)

探索Oracle之資料庫升級六 11.2.0.4.3 Upgrade12c(12.2.0.1)

探索Oracle之資料庫升級六 11.2.0.4.3 Upgrade12c(12.2.0.1)

探索Oracle之資料庫升級六 11.2.0.4.3 Upgrade12c(12.2.0.1)

探索Oracle之資料庫升級六 11.2.0.4.3 Upgrade12c(12.2.0.1)

探索Oracle之資料庫升級六 11.2.0.4.3 Upgrade12c(12.2.0.1)

探索Oracle之資料庫升級六 11.2.0.4.3 Upgrade12c(12.2.0.1)

探索Oracle之資料庫升級六 11.2.0.4.3 Upgrade12c(12.2.0.1)

探索Oracle之資料庫升級六 11.2.0.4.3 Upgrade12c(12.2.0.1)

探索Oracle之資料庫升級六 11.2.0.4.3 Upgrade12c(12.2.0.1)

探索Oracle之資料庫升級六 11.2.0.4.3 Upgrade12c(12.2.0.1)

執行DBUA 對資料庫進行升級操作。
探索Oracle之資料庫升級六 11.2.0.4.3 Upgrade12c(12.2.0.1)

探索Oracle之資料庫升級六 11.2.0.4.3 Upgrade12c(12.2.0.1)

探索Oracle之資料庫升級六 11.2.0.4.3 Upgrade12c(12.2.0.1)

探索Oracle之資料庫升級六 11.2.0.4.3 Upgrade12c(12.2.0.1)

探索Oracle之資料庫升級六 11.2.0.4.3 Upgrade12c(12.2.0.1)

探索Oracle之資料庫升級六 11.2.0.4.3 Upgrade12c(12.2.0.1)

探索Oracle之資料庫升級六 11.2.0.4.3 Upgrade12c(12.2.0.1)

探索Oracle之資料庫升級六 11.2.0.4.3 Upgrade12c(12.2.0.1)

探索Oracle之資料庫升級六 11.2.0.4.3 Upgrade12c(12.2.0.1)

探索Oracle之資料庫升級六 11.2.0.4.3 Upgrade12c(12.2.0.1)
探索Oracle之資料庫升級六 11.2.0.4.3 Upgrade12c(12.2.0.1)探索Oracle之資料庫升級六 11.2.0.4.3 Upgrade12c(12.2.0.1)探索Oracle之資料庫升級六 11.2.0.4.3 Upgrade12c(12.2.0.1)探索Oracle之資料庫升級六 11.2.0.4.3 Upgrade12c(12.2.0.1)

探索Oracle之資料庫升級六 11.2.0.4.3 Upgrade12c(12.2.0.1)

至此，用於升級的12c軟體包已經安裝完成。


六、執行升級檢查

  1. SQL> select banner from v$version;

  3. BANNER
  4. --------------------------------------------------------------------------------
  5. Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
  6. PL/SQL Release 12.1.0.1.0 - Production
  7. CORE 12.1.0.1.0 Production
  8. TNS for Linux: Version 12.1.0.1.0 - Production
  9. NLSRTL Version 12.1.0.1.0 – Production

  11. SQL> set pagesize 500
  12. SQL> set line 300
  13. SQL> col comp_name format a40
  14. SQL> col comp_name format a35
  15. SQL> col version format a15
  16. SQL> col status format a7
  17. SQL> select comp_name,version,status from dba_registry;

  19. COMP_NAME VERSION STATUS
  20. ----------------------------------- --------------- -------
  21. Oracle Application Express 4.2.0.00.27 VALID
  22. OWB 11.2.0.3.0 VALID
  23. OLAP Catalog 11.2.0.4.0 OPTION
  24.                                                     OFF
  25. Spatial 12.1.0.1.0 VALID
  26. Oracle Multimedia 12.1.0.1.0 VALID
  27. Oracle XML Database 12.1.0.1.0 VALID
  28. Oracle Text 12.1.0.1.0 VALID
  29. Oracle Workspace Manager 12.1.0.1.0 VALID
  30. Oracle Database Catalog Views 12.1.0.1.0 VALID
  31. Oracle Database Packages and Types 12.1.0.1.0 VALID
  32. JServer JAVA Virtual Machine 12.1.0.1.0 VALID
  33. Oracle XDK 12.1.0.1.0 VALID
  34. Oracle Database Java Packages 12.1.0.1.0 VALID
  35. OLAP Analytic Workspace 12.1.0.1.0 VALID
  36. Oracle OLAP API 12.1.0.1.0 VALID

  38. 15 rows selected.

16.2  從上面我們可以看到有些元件的狀態是不對的，這是老版本的元件，不能直接通過升級上12c，需要執行如下SQL進行刪除。
  1. SQL> @$ORACLE_HOME/rdbms/admin/emremove.sql
  2. SQL> @$ORACLE_HOME/olap/admin/catnoamd.sql
  3. SQL> EXECUTE dbms_stats.gather_dictionary_stats;
 再次檢視就沒有問題了:

  1. SQL> select comp_name,version,status from dba_registry;

  3. COMP_NAME VERSION STATUS
  4. ----------------------------------- --------------- -------
  5. Oracle Application Express 4.2.0.00.27 VALID
  6. OWB 11.2.0.3.0 VALID
  7. Spatial 12.1.0.1.0 VALID
  8. Oracle Multimedia 12.1.0.1.0 VALID
  9. Oracle XML Database 12.1.0.1.0 VALID
  10. Oracle Text 12.1.0.1.0 VALID
  11. Oracle Workspace Manager 12.1.0.1.0 VALID
  12. Oracle Database Catalog Views 12.1.0.1.0 VALID
  13. Oracle Database Packages and Types 12.1.0.1.0 VALID
  14. JServer JAVA Virtual Machine 12.1.0.1.0 VALID
  15. Oracle XDK 12.1.0.1.0 VALID
  16. Oracle Database Java Packages 12.1.0.1.0 VALID
  17. OLAP Analytic Workspace 12.1.0.1.0 VALID
  18. Oracle OLAP API 12.1.0.1.0 VALID

  20. 14 rows selected.

16.3 /etc/oratab 資訊對比
  1. ---升級前
  2. [root@db01 ~]# grep DBSoft /etc/oratab
  3. woo:/DBSoft/Product/11.2.0/db_1:N

  5. ---升級後
  6. [root@db01 ~]# grep DBSoft /etc/oratab
  7. woo:/DBSoft/Product/12.1.0/db_1:N

注意：這部分不需要我們手工去修改/etc/oratab記錄，執行升級會自動完成修改。

16.4 執行升級後postupgrade_fixups.sql檢查:

  1. SQL> @/DBSoft/cfgtoollogs/woo/preupgrade/postupgrade_fixups.sql
  2. Post Upgrade Fixup Script Generated on 2014-11-20 05:18:29 Version: 12.1.0.1 Build: 007
  3. Beginning Post-Upgrade Fixups...

  5. PL/SQL procedure successfully completed.
  6. PL/SQL procedure successfully completed.

  8. **********************************************************************
  9. Check Tag: OLD_TIME_ZONES_EXIST
  10. Check Summary: Check for use of older timezone data file
  11. Fix Summary: Update the timezone using the DBMS_DST package after upgrade is complete.
  12. **********************************************************************
  13. Fixup Returned Information:
  14. INFORMATION: --&gt Older Timezone in use

  16.      Database is using a time zone file older than version 18.
  17.      After the upgrade, it is recommended that DBMS_DST package
  18.      be used to upgrade the 12.1.0.1.0 database time zone version
  19.      to the latest version which comes with the new release.
  20.      Please refer to My Oracle Support note number 977512.1 for details.
  21. **********************************************************************

  23. PL/SQL procedure successfully completed.

  25. **********************************************************************
  26.                      [Post-Upgrade Recommendations]
  27. **********************************************************************

  29. PL/SQL procedure successfully completed.

  31.                         *****************************************
  32.                         ******** Fixed Object Statistics ********
  33.                         *****************************************

  35. Please create stats on fixed objects two weeks
  36. after the upgrade using the command:
  37.    EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

  39. ^^^ MANUAL ACTION SUGGESTED ^^^

  41. PL/SQL procedure successfully completed.

  43.            **************************************************
  44.                 ************* Fixup Summary ************

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


  49. PL/SQL procedure successfully completed.

  51. *************** Post Upgrade Fixup Script Complete ********************

  53. PL/SQL procedure successfully completed.

  55. SQL>
16.5 檢查DST
     從升級後的檢查結果來看，DST目前是18，說明不需要人工處理了，如果不是需要手工來處理，參考Metalink ID: 977512.1 或者參考Metalink ID 1585343.1
  1. SQL> r
  2.   1 SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
  3.   2 FROM DATABASE_PROPERTIES
  4.   3 WHERE PROPERTY_NAME LIKE \'DST_%\'
  5.   4* ORDER BY PROPERTY_NAME

  7. PROPERTY_NAME VALUE
  8. ---------------------------------------- ------------------------------------------------------------------------------------------------------------------------
  9. DST_PRIMARY_TT_VERSION 18
  10. DST_SECONDARY_TT_VERSION 0
  11. DST_UPGRADE_STATE NONE

  13. SQL> select * from v$timezone_file;

  15. FILENAME VERSION CON_ID
  16. -------------------- ---------- ----------
  17. timezlrg_18.dat 18 0

  19. SQL> select TZ_VERSION from registry$database;

  21. TZ_VERSION
  22. ----------
  23.         18

6.6 字符集檢查:

檢查國家字符集，如果是以下字符集則不需要做操作:

如果返回結果是 UTF8 或者 AL16UTF16，那麼什麼都不需要做了。
如果返回結果不是 UTF8 或者 AL16UTF16，那麼請參考下面的文件：

Note 276914.1 The National Character Set ( NLS_NCHAR_CHARACTERSET ) in Oracle 9i, 10g , 11g and 12c (文件 ID 276914.1)

  1. SQL> select value from nls_database_parameters where parameter=\'NLS_NCHAR_CHARACTERSET\';

  3. VALUE
  4. --------------------------------------------------------------------------------
  5. AL16UTF16
 

6.7 修改引數檔案中的版本號：

  1. SQL> show parameter compatible

  3. NAME TYPE VALUE
  4. ------------------------------------ ----------- ------------------------------
  5. compatible string 11.2.0.0.0
  6. noncdb_compatible boolean FALSE

  8. SQL> alter system set compatible = \'12.1.0.1.0\' scope=spfile;
  9. System altered.

  11. SQL> startup force;
  12. ORACLE instance started.

  14. Total System Global Area 2772574208 bytes
  15. Fixed Size 2292240 bytes
  16. Variable Size 2533361136 bytes
  17. Database Buffers 218103808 bytes
  18. Redo Buffers 18817024 bytes
  19. Database mounted.
  20. Database opened.
  21. SQL>
  22. SQL> show parameter compatible

  24. NAME TYPE VALUE
  25. ------------------------------------ ----------- ------------------------------
  26. compatible string 12.1.0.1.0
  27. noncdb_compatible boolean FALSE
  28. SQL>



至此，經過漫長而辛苦的升級，我們已經將11.2.0.4順利升級到了12.1.0.1，整個升級過程雖然有點長，但還是比較順利的。故需再生產環境中升級請大家務必預留好可用於升級的時間視窗，升級時間確實是非常的長。









來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29439655/viewspace-1373046/，如需轉載，請註明出處，否則將追究法律責任。

相關文章