1 前言
The latest generation of the world's most popular database, Oracle Database 12c Release 2 (12.2), is now available everywhere - in the Cloud, with Oracle Cloud at Customer,
and on-premises. This latest release provides organizations of all sizes with access to the world’s fastest, most scalable and reliable database technology in a cost-effective,
hybrid Cloud environment. 12.2 also includes a series of innovations that helps customers easily transform to the Cloud while preserving their investments in Oracle Database
technologies, skills and resources.
最新一代世界上最流行的資料庫，Oracle資料庫12 ?第2版（12.2），在雲中現已無處不在，與Oracle 雲在客戶和內部部署。此最新版本為各種規模的組織提供了在經濟高效的混
合雲環境中訪問世界上最快，最可擴充套件和可靠的資料庫技術。12.2還包括一系列創新，幫助客戶輕鬆轉換到雲，同時保留對Oracle資料庫技術，技能和資源的投資。

Oracle 12.2.0.1於2017年3月初提供下載，本文介紹11.2.0.4升級12.2.0.1實戰操作過程.
2 升級要求

3 升級計劃
升級前：Oracle 11.2.0.4à升級後：Oracle 12.2.0.1
4 升級前準備
4.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> set pagesize 500
set line 300
col comp_name format a40
col comp_name format a35
col version format a15
col status format a7
select comp_name,version,status from dba_registry;SQL>
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 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.

?
4.2 升級前檢查
1、Gathering Optimizer Statistics to Decrease Oracle Database Downtime
? Non-CDB Oracle Database: Oracle recommends that you use the
DBMS_STATS.GATHER_DICTIONARY_STATS procedure to gather these statistics.
For example, enter the following SQL statement:
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
2、Verifying Materialized View Refreshes are Complete Before Upgrade
SQL> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s
WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;
3、Ensuring That No Files Are in Backup Mode Before Upgrading
SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
4、Ensuring That No Files Need Media Recovery Before Upgrading
SQL> SELECT * FROM v$recover_file;
5、Resolving Outstanding Distributed Transactions Before Upgrading
1. Run the following statement:
SQL> SELECT * FROM dba_2pc_pending;
2. If the query in the previous step returns any rows, then run the following
statements:
SQL> SELECT local_tran_id FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');
SQL> COMMIT;
6、Purging the Database Recycle Bin Before Upgrading
SQL> PURGE DBA_RECYCLEBIN

四、資料庫備份
1. Sign on to Oracle RMAN:
rman "target / nocatalog"
2. Run the following RMAN commands:
RUN
{ALLOCATE CHANNEL chan_name TYPE DISK;
BACKUP DATABASE FORMAT 'some_backup_directory%U' TAG before_upgrade;
BACKUP CURRENT CONTROLFILE FORMAT 'controlfile location and name';
}

5 預升級檢查
5.1 參考文件
Oracle Database Pre-Upgrade Utility (Document ID 884522.1) at My Oracle
Support

5.2 執行預升級檢查
Example 2-4 Non-CDB In the Source Oracle Home Example
1. Run the new release Oracle Database Pre-Upgrade Information Tool on the earlier
release Oracle Database server (12.2), using the environment settings you have set
to the earlier release Oracle home.
mkdir –p /u01/app/oracle/product/12.2.0/rdbms/admin/
mv preupgrade.jar /u01/app/oracle/product/12.2.0/rdbms/admin/
$ORACLE_HOME/jdk/bin/java -jar /u01/app/oracle/product/12.2.0/rdbms/admin/
preupgrade.jar TERMINAL TEXT

[oracle@oracle12c soft]$ $ORACLE_HOME/jdk/bin/java -jar /u01/app/oracle/product/12.2.0/rdbms/admin/preupgrade.jar TERMINAL TEXT
Report generated by Oracle Database Pre-Upgrade Information Tool Version
12.2.0.1.0

Upgrade-To version: 12.2.0.1.0

=======================================
Status of the database prior to upgrade
=======================================

Database Name: HSQL
Container Name: Not Applicable in Pre-12.1 database
Container ID: Not Applicable in Pre-12.1 database
Version: 11.2.0.4.0
Compatible: 11.2.0.4.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 14
Database log mode: NOARCHIVELOG
Readonly: FALSE
Edition: EE

Oracle Component Upgrade Action Current Status
---------------- -------------- --------------
Oracle Server [to be upgraded] VALID
JServer JAVA Virtual Machine [to be upgraded] VALID
Oracle XDK for Java [to be upgraded] VALID
Oracle Workspace Manager [to be upgraded] VALID
OLAP Analytic Workspace [to be upgraded] VALID
Oracle Enterprise Manager Repository [to be upgraded] VALID
Oracle Text [to be upgraded] VALID
Oracle XML Database [to be upgraded] VALID
Oracle Java Packages [to be upgraded] VALID
Oracle Multimedia [to be upgraded] VALID
Oracle Spatial [to be upgraded] VALID
Expression Filter [to be upgraded] VALID
Rule Manager [to be upgraded] VALID
Oracle Application Express [to be upgraded] VALID
Oracle OLAP API [to be upgraded] VALID

==============
BEFORE UPGRADE
==============

Run /preupgrade_fixups.sql to complete all
of the BEFORE UPGRADE action items below marked with '(AUTOFIXUP)'.

REQUIRED ACTIONS
================
+ Adjust TABLESPACE SIZES as needed.
Auto 12.2.0.1.0
Tablespace Size Extend Min Size Action
---------- ---------- -------- ---------- ------

SYSAUX 510 MB ENABLED 1420 MB None
SYSTEM 740 MB ENABLED 1249 MB None
TEMP 29 MB ENABLED 150 MB None
UNDOTBS1 80 MB ENABLED 400 MB None

Note that 12.2.0.1.0 minimum sizes are estimates.
If you plan to upgrade multiple pluggable databases concurrently,
then you must ensure that the UNDO tablespace size is equal to at least
the number of pluggable databases that you upgrade concurrently,
multiplied by that minimum. Failing to allocate sufficient space can
cause the upgrade to fail.

+ Update NUMERIC INITIALIZATION PARAMETERS to meet estimated minimums.

Parameter 12.2.0.1.0 minimum
--------- ------------------
processes 300

RECOMMENDED ACTIONS
===================
+ Remove the EM repository.

- Copy the $ORACLE_HOME/rdbms/admin/emremove.sql script from the target
12.2.0.1.0 ORACLE_HOME into the source 11.2.0.4.0 ORACLE_HOME.
ls /u01/app/oracle/product/12.2.0/db_1/rdbms/admin/emremove.sql
cp /u01/app/oracle/product/12.2.0/db_1/rdbms/admin/emremove.sql /u01/app/oracle/product/11.2.0/db_1/rdbms/admin/emremove.sql
Step 1: If database control is configured, stop EM Database Control,
using the following command

$> emctl stop dbconsole

Step 2: 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.

The database has an Enterprise Manager Database Control repository.

Starting with Oracle Database 12c, the local Enterprise Manager Database
Control does not exist anymore. The repository will be removed from your
database during the upgrade. This step can be manually performed before
the upgrade to reduce downtime.

+ Remove OLAP Catalog by running the 11.2.0.4.0 SQL script
$ORACLE_HOME/olap/admin/catnoamd.sql script.

The OLAP Catalog component, AMD, exists in the database.

Starting with Oracle Database 12c, the OLAP Catalog (OLAP AMD) is
desupported and will be automatically marked as OPTION OFF during the
database upgrade if present. Oracle recommends removing OLAP Catalog
(OLAP AMD) before database upgrade.

+ (AUTOFIXUP) Gather stale data dictionary statistics prior to database
upgrade in off-peak time using:

EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

Dictionary statistics do not exist or are stale (not up-to-date).

Dictionary statistics help the Oracle optimizer find efficient SQL
execution plans and are essential for proper upgrade timing. Oracle
recommends gathering dictionary statistics in the last 24 hours before
database upgrade.

For information on managing optimizer statistics, refer to the 11.2.0.4
Oracle Database Performance Tuning Guide.

+ Directly grant ADMINISTER DATABASE TRIGGER privilege to the owner of the
trigger or drop and re-create the trigger with a user that was granted
directly with such. You can list those triggers using "SELECT OWNER,
TRIGGER_NAME FROM DBA_TRIGGERS WHERE BASE_OBJECT_TYPE=''DATABASE'' AND
OWNER NOT IN (SELECT GRANTEE FROM DBA_SYS_PRIVS WHERE
PRIVILEGE=''ADMINISTER DATABASE TRIGGER'')"

SELECT OWNER,TRIGGER_NAME FROM DBA_TRIGGERS WHERE BASE_OBJECT_TYPE='DATABASE' AND OWNER NOT IN (SELECT GRANTEE FROM DBA_SYS_PRIVS WHERE PRIVILEGE='ADMINISTER DATABASE TRIGGER');

There is one or more database triggers whose owner does not have the
right privilege on the database.

The creation of database triggers must be done by users granted with
ADMINISTER DATABASE TRIGGER privilege. Privilege must have been granted
directly.

INFORMATION ONLY
================
+ Consider upgrading APEX manually, before the database upgrade.

The database contains APEX version 3.2.1.00.12 and will need to be
upgraded to at least version 5.0.4.00.12.

To reduce database upgrade time, you can upgrade APEX manually before
the database upgrade. Refer to My Oracle Support Note 1088970.1 for
information on APEX installation upgrades.

=============
AFTER UPGRADE
=============

Run /postupgrade_fixups.sql to complete all
of the AFTER UPGRADE action items below marked with '(AUTOFIXUP)'.

REQUIRED ACTIONS
================
None

RECOMMENDED ACTIONS
===================
+ Upgrade the database time zone version using the DBMS_DST package.

The database is using timezone datafile version 14 and the target
12.2.0.1.0 database ships with timezone datafile version 26.

Oracle recommends using the most recent timezone data. For further
information, refer to My Oracle Support Note 1585343.1.

+ (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
command:

EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

Oracle recommends gathering dictionary statistics after upgrade.

Dictionary statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. After a
database upgrade, statistics need to be re-gathered as there can now be
tables that have significantly changed during the upgrade or new tables
that do not have statistics gathered yet.

+ Gather statistics on fixed objects two weeks after the upgrade using the
command:

EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

This recommendation is given for all preupgrade runs.

Fixed object statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. Those
statistics are specific to the Oracle Database release that generates
them, and can be stale upon database upgrade.

INFORMATION ONLY
================
+ Check the Oracle documentation for the identified components for their
specific upgrade procedure.

The database upgrade script will not upgrade the following Oracle
components: OLAP Catalog,OWB

The Oracle database upgrade script upgrades most, but not all Oracle
Database components that may be installed. Some components that are not
upgraded may have their own upgrade scripts, or they may be deprecated
or obsolete.

upgrade to Oracle Database 12C

Preupgrade generated files:
/u01/app/oracle/cfgtoollogs/hsql/preupgrade/preupgrade_fixups.sql
/u01/app/oracle/cfgtoollogs/hsql/preupgrade/postupgrade_fixups.sql

5.3 修復存在問題
根據上面執行結果提示，執行以下修復指令碼：
注：請先手工修復後再執行preupgrade_fixups.sql，提高修復效率。

ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE;

$ORACLE_HOME/olap/admin/catnoamd.sql

EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

SELECT OWNER,TRIGGER_NAME FROM DBA_TRIGGERS WHERE BASE_OBJECT_TYPE='DATABASE' AND OWNER NOT IN (SELECT GRANTEE FROM DBA_SYS_PRIVS WHERE PRIVILEGE='ADMINISTER DATABASE TRIGGER');

grant ADMINISTER DATABASE TRIGGER to sysman;

Remove the EM repository
--12C軟體安裝完後DBUA檢查時再執行

Step 1: If database control is configured, stop EM Database Control,

using the following command

$> emctl stop dbconsole

Step 2: Connect to the database using the SYS account AS SYSDBA

SET ECHO ON;

SET SERVEROUTPUT ON;

@emremove.sql

執行指令碼

@/u01/app/oracle/cfgtoollogs/hsql/preupgrade/preupgrade_fixups.sql 檢查預升級項修復

Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) (文件 ID 556610.1)
Connect as sysdba and execute the script

cd
$ sqlplus / as sysdba
sql> alter session set nls_language='American';
sql> @dbupgdiag.sql
sql> exit

6 安裝12C軟體

6.1 環境變數
export ORACLE_SID=hsql
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.2.0/db_1
export PATH=$PATH:$ORACLE_BASE/product/12.2.0/db_1/bin
mkdir –p /u01/app/oracle/product/12.2.0/db_1

6.2 解壓軟體：
unzip V839960-01.zip

6.3 安裝12c軟體

進入解壓目錄執行./runInstaller 開啟12c安裝程式
直接點選Next，下一步開始安裝

選擇upgrade an existing database,next

使用root執行指令碼

修復以上問題

點選：fix & check Again

ls /u01/app/oracle/product/12.2.0/db_1/rdbms/admin/emremove.sql

cp /u01/app/oracle/product/12.2.0/db_1/rdbms/admin/emremove.sql /u01/app/oracle/product/11.2.0/db_1/rdbms/admin/emremove.sql

@ /u01/app/oracle/product/11.2.0/db_1/rdbms/admin/emremove.sql

7 升級後檢查

Gathering Dictionary Statistics After Upgrading

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

Regathering Fixed Objects Statistics with DBMS_STATS

SQL> execute dbms_stats.gather_fixed_objects_stats;

資料庫版本及元件檢查

select banner from v$version;

select substr(comp_id,1,15) comp_id,substr(comp_name,1,30) comp_name,substr(version,1,10) version,status

from dba_registry order by modified;

檢查字符集及時區

select value from nls_database_parameters where parameter='NLS_NCHAR_CHARACTERSET';

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;

select TZ_VERSION from registry$database;

select * from v$timezone_file;

修改COMPATIBLE引數

SQL>show parameter COMPATIBLE

SQL> ALTER SYSTEM SET COMPATIBLE = '12.2.0.1.0' SCOPE=SPFILE;

SQL>startup force

歷時20小時資料庫已順利從11.2.0.4升級至12.2.0.1，各們童鞋升級前請做好資料庫備份，本文件僅供參考，生產環境升級請參考官方升級文件
《Oracle? Database Upgrade Guide 12c Release 2 (12.2) E49634-13》。

Oracle-Database-12c-12.2.0.1升級實戰

相關文章