1 环境说明
源库：12.1.0.2
SQL> select * from v$version rownum =1;
BANNER CON_ID
---------------------------------------------------------------- ----------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
要升级的版本：12.2
具体的升级到12c的路线请参考：
10G R2升级到12cR1：http://www.cndba.cn/Expect-le/article/2021
2 准备工作
官方文档：
http://docs.oracle.com/database/122/UPGRD/checklists-for-oracle-database-upgrade.htm#UPGRD-GUID-5FAA2A12-87C5-4D65-B7AE-C9D67FA4F05F
源库(升级前的库)：
Task | Description |
Clean up the database | Empty the recycle bin Check for INVALID objects in SYS and SYSTEM Check for duplicate objects in SYS and SYSTEM |
Check components | Check for INVALID components Check for mandatory components Remove obsolete components |
Check materialized views | Check the status of all materialized views (MV), and refresh any materialized views that are not fresh. Check the size of your materialized view logs. If any materialized view logs have non-zero rows, then refresh the base table materialized views. Check the size of direct loader logs and PMOP logs (partition maintenance operation logs). If any direct loader logs or PMOP logs have non-zero rows, then refresh the MVs indicated by the logs. |
Obtain performance baseline | Preserve performance statistics Check network performance |
Perform system integrity checks | Perform your own integrity checks Run Pre-Upgrade Information Tool (preupgrade.jar) |
Check Time Zone setting | Default time zone version for Oracle Database 12c聽release 2 (12.2) is V26 If the source database Time Zone version is higher than the target database, then apply the Time Zone patch on the target database to match the source database version. Time Zone defaults are in located in the path$ORACLE_HOME/oracore/zoneinfo. |
Gather dictionary statistics | To decrease Oracle Database downtime, gather optimizer statistics. |
Back up the database | Back up the database, create a guaranteed flashback restore point, or both. Test your fallback strategies at least once before your upgrade window. Ensure that you have fallback strategies for issues both during upgrade, and after upgrade. |
目标库(升级后的库)：
Task | Description |
Run postinstallation SQL scripts | $ORACLE_HOME/rdbms/admin/utlrp.sql $ORACLE_HOME/rdbms/admin/utlu122s.sql $ORACLE_BASE/cfgtoollogs/SID/preupgrade/postupgrade_fixups.sql |
Review upgrade logs and trace files | $ORACLE_BASE/cfgtoollogs/DBUA/upgradeTimestamp (Look under $ORACLE_HOME if $ORACLE_BASE is not set). Also, folders with the system identifier (SID) of individual database are in this timestamp folder. The SID folders contain files for individual databases for the preupgrade and upgrade process. alert_SID.log |
Verify upgrade status for CATALOG and CATPROC components | select substr(comp_name, 1, 45) as comp_name, substr(version,1,8) as version , substr(status,1,8) as status from dba_registry Look for version and status changes. |
Run Oracle Database 12.2 Post-Upgrade Status Tool (utlu122s.sql) | /rdbms/admin/utlu122s.sql |
Update time zone settings | If Time Zone version at source database is lower than the target database, then run Time Zone conversion after the upgrade. Time Zone defaults are in the path$ORACLE_HOME/oracore/zoneinfo |
Create Oracle Database system files | Create an SPFILE from the PFILE Gather new Oracle Cost-Based Optimizer (CBO) statistics Back up the database |
总结：上面的表格里都有说到
1. 升级前一定要备份数据库:最好冷备
2. 所有的对象必须都是有效的(如果有无效对象需运行$ORACLE_HOME/rdbms/admin/utlrp.sql)
3. SYS和SYSTEM用户下没有重复的对象
4. 禁用所有用户的触发器
3 安装新版本数据库软件
创建新的ORACLE_HOME:/u01/app/oracle/product/12.2.0/db_1
[[email protected] ~]$ mkdir -p /u01/app/oracle/product/12.2.0/db_1
当前的ORACLE_HOME:/u01/app/oracle/product/12.1.0/db_1
[[email protected] ~]$Export $ORACLE_HOME=/u01/app/oracle/product/12.2.0/db_1
[[email protected] ~]$ ./runInstaller
--执行脚本root.sh
[[email protected] database]# /u01/app/oracle/product/12.2.0/db_1/root.sh
Performing root user operation.
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/oracle/product/12.2.0/db_1
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying dbhome to /usr/local/bin ...
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying coraenv to /usr/local/bin ...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Do you want to setup Oracle Trace File Analyzer (TFA) now ? yes|[no] : y
Installing Oracle Trace File Analyzer (TFA).
Log File: /u01/app/oracle/product/12.2.0/db_1/install/root_db10_2017-07-27_15-47-56-747542956.log
Finished installing Oracle Trace File Analyzer (TFA
软件安装完毕，开始运行脚本
3.1 源库创建测试数据
SQL> create user cndba identified by cndba;
User created.
SQL> create table cndba.test as select * from dba_tables;
create table cndba.test as select * from dba_tables
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'
SQL> grant resource,connect,dba to cndba;
Grant succeeded.
SQL> create table cndba.test as select * from dba_tables;
Table created.
SQL> select count(*) from cndba.test;
COUNT(*)
----------
1545
3.2 用于检测环境是否满足升级的脚本
MOS上提供了脚本用于检测源数据库是否满足升级的条件，非常方便。MOS文档How to Download and Run Oracle's Database Pre-Upgrade Utility (文档 ID 884522.1)，当然在新ORACLE_hOME也有想过脚本。
3.2.1 切换到源库环境
--指向源库的环境
[[email protected] ~]$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
[[email protected] ~]$ export ORACLE_BASE=/u01/app/oracle
[[email protected] ~]$ export ORACLE_SID=cndba
[[email protected] ~]$ export PATH=.:$ORACLE_HOME/bin:$PATH
[[email protected] ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Thu Jul 27 15:06:55 2017
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter pluggable database all open;
3.2.2 脚本存放路径
脚本使用方法和12c R1有所不同。
Preupgrade.jar在新的ORACLE_HOME：New_Oracle_home/rdbms/admin/preupgrade.jar
[[email protected] software]$ ll /u01/app/oracle/product/12.2.0/db_1/rdbms/admin/preupgrade.jar
-rw-r--r-- 1 oracle oinstall 547766 Jan 26 08:45 /u01/app/oracle/product/12.2.0/db_1/rdbms/admin/preupgrade.jar
3.2.2.1 运行 Preupgrade.jar
注意：使用新的ORACLE_HOME下的preupgrade.jar在源库的环境中运行
[[email protected] software]$ /u01/app/oracle/product/12.1.0/db_1/jdk/bin/java -jar /u01/app/oracle/product/12.2.0/db_1/rdbms/admin/preupgra/
Preupgrade generated files:
/software/preupgrade.log --一定要查看该log文件，里面记录了当前数据库环境的一些检查信息以及处理建议 /software/preupgrade_fixups.sql /software/postupgrade_fixups.sql
3.2.2.2 查看输出log文件
由于这个库由两个PDB，所以输出信息很多。基本上都是重复的。
[[email protected] software]$ cat /software/preupgrade.log 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: ZHIXIN Container Name: CDB$ROOT Container ID: 1 Version: 12.1.0.2.0 Compatible: 12.1.0.2.0 Blocksize: 8192 Platform: Linux x86 64-bit Timezone File: 18 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 Real Application Clusters [to be upgraded] OPTION OFF Oracle Workspace Manager [to be upgraded] VALID OLAP Analytic Workspace [to be upgraded] VALID Oracle Label Security [to be upgraded] VALID Oracle Database Vault [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 Oracle Application Express [to be upgraded] VALID Oracle OLAP API [to be upgraded] VALID ============== BEFORE UPGRADE ============== Run /preupgrade_fixups_CDB_ROOT.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 600 MB ENABLED 1507 MB None SYSTEM 790 MB ENABLED 1296 MB None TEMP 60 MB ENABLED 150 MB None UNDOTBS1 145 MB ENABLED 400 MB None 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 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 Oracle OLAP API [to be upgraded] VALID ============== BEFORE UPGRADE ============== Run /preupgrade_fixups_CNDBA_PDB.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 360 MB ENABLED 574 MB None SYSTEM 830 MB ENABLED 1250 MB None TEMP 20 MB ENABLED 150 MB None UNDOTBS1 0 Bytes DISABLED 400 MB Extend Fixup Check Name Status Further DBA Action ---------- ------ ------------------ purge_recyclebin Passed None dictionary_stats Passed None apex_upgrade_msg Failed Manual fixup recommended. PL/SQL procedure successfully completed. Session altered.
3.2.3 查看无效对象
--CDB下运行
SQL> select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type;
no rows selected
3.2.4 打开归档（可选）
是为了升级失败可以闪回到升级之前的正常状态
SQL> show parameter db_recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
SQL> alter system set db_recovery_file_dest_size=5G scope=both;
System altered.
SQL> alter system set db_recovery_file_dest='/archive/zhixin' scope=both;
System altered.
SQL> alter database flashback on;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
3.3 设置环境变量到新的ORACLE_HOME(12.2)
[[email protected] software]$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0/db_1
[[email protected] software]$ export PATH=$ORACLE_HOME/bin:$PATH
[[email protected] software]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib
[[email protected] software]$ cd $ORACLE_HOME/bin
[[email protected] bin]$ pwd
/u01/app/oracle/product/12.2.0/db_1/bin
3.3.1 运行DBUA
同样要等待一会。过了两三个小时，
终于好了，太慢了。比10g升12c R1蛮多了，主要是由于硬件的性能问题。正常情况，1-2小时就可以了。
至此数据库已经升级完毕了，下面的工作就是善后检查工作了。
3.3.2 检查版本
SQL> select * from v$version where rownum=1;
BANNER CON_ID
------------------------------------------------------------- -------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
3.3.3 执行脚本
3.3.3.1 执行postupgrade_fixups.sql脚本
切换到新的ORACLE_HOME下执行sqlplus
$ORACLE_HOME=$ORACLE_BASE/product/12.2.0/db_1; export ORACLE_HOME
$PATH=$ORACLE_HOME/bin:$PATH; export PATH
$LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
SQL> @/software/postupgrade_fixups.sql
Session altered.
PL/SQL procedure successfully completed.
Session altered.
PL/SQL procedure successfully completed.
Package created.
No errors.
Package body created.
No errors.
Package created.
No errors
Package body created.
No errors.
Executing Oracle POST-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 12.2.0.1.0 Build: 1
Generated on: 2017-07-27 23:08:01
For Source Database: ZHIXIN
Source Database Version: 12.1.0.2.0
For Upgrade to Version: 12.2.0.1.0
Executing in container: CDB$ROOT
Fixup
Check Name Status Further DBA Action
---------- ------ ------------------
old_time_zones_exist Passed None
post_dictionary Passed None
PL/SQL procedure successfully completed.
Session altered.
3.3.3.2 运行utlu122s.sql脚本
显示了升级后数据的一些关键组件的状态信息。
SQL> @$ORACLE_HOME/rdbms/admin/utlu122s.sql
Oracle Database 12.2 Post-Upgrade Status Tool 07-29-2017 09:01:20
[CDB$ROOT]
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
Oracle Server VALID 12.2.0.1.0 00:14:42
JServer JAVA Virtual Machine VALID 12.2.0.1.0 00:06:25
Oracle Real Application Clusters OPTION OFF 12.2.0.1.0 00:00:00
Oracle Workspace Manager VALID 12.2.0.1.0 00:00:44
OLAP Analytic Workspace VALID 12.2.0.1.0 00:00:21
Oracle OLAP API VALID 12.2.0.1.0 00:00:16
Oracle Label Security VALID 12.2.0.1.0 00:00:09
Oracle XDK VALID 12.2.0.1.0 00:01:16
Oracle Text VALID 12.2.0.1.0 00:00:41
Oracle XML Database VALID 12.2.0.1.0 00:01:33
Oracle Database Java Packages VALID 12.2.0.1.0 00:00:13
Oracle Multimedia VALID 12.2.0.1.0 00:02:06
Spatial VALID 12.2.0.1.0 00:05:44
Oracle Application Express VALID 5.0.4.00.12 00:12:45
Oracle Database Vault VALID 12.2.0.1.0 00:00:25
Final Actions 00:03:35
Post Upgrade 00:00:42
Post Compile 00:10:44
Total Upgrade Time: 01:02:46 [CDB$ROOT]
Database time zone version is 26. It meets current release needs.
Summary Report File = /u01/app/oracle/cfgtoollogs/dbua/upgrade2017-07-27_11-23-10-PM/zhixin/upg_summary.log
3.3.3.3 如果有无效对象，执行utlrp.sql
$ORACLE_HOME/rdbms/admin/utlrp.sql
3.3.4 检查timezone
SQL> select * from v$timezone_file;
FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_26.dat 26 0
3.3.5 检查组件状态
SQL> select comp_id,status,version from dba_registry;
COMP_ID STATUS VERSION
------------------------------ ----------- ------------------------------
CATALOG VALID 12.2.0.1.0
CATPROC VALID 12.2.0.1.0
JAVAVM VALID 12.2.0.1.0
XML VALID 12.2.0.1.0
CATJAVA VALID 12.2.0.1.0
APS VALID 12.2.0.1.0
RAC OPTION OFF 12.2.0.1.0
XDB VALID 12.2.0.1.0
OWM VALID 12.2.0.1.0
CONTEXT VALID 12.2.0.1.0
ORDIM VALID 12.2.0.1.0
COMP_ID STATUS VERSION
------------------------------ ----------- ------------------------------
SDO VALID 12.2.0.1.0
XOQ VALID 12.2.0.1.0
OLS VALID 12.2.0.1.0
APEX VALID 5.0.4.00.12
DV VALID 12.2.0.1.0
16 rows selected.
3.3.6 检查maintained类型的表
maintained是12c中的一个新特性，在dba_objects新增一个字段maintained，是Y表示，这个对象是使用Oracle脚本自动创建的对象。不要手动修改该字段。
SELECT DISTINCT owner, table_name
FROM dba_tab_cols
WHERE data_upgraded = 'NO'
ORDER BY 1,2;
如果上面查询中有数据吗，那么需要执行下面这个脚本
@$ORACLE_HOME/rdbms/admin/utluptabdata.sql
Rem This script should be run after an upgrade to a new release
Rem to assure that all customer tables have been upgraded to the
Rem latest versions of Oracle-Maintained types. In a CDB, it Rem should be run in each PDB.
需要在每个PDB中执行该脚本
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 CNDBA_PDB MOUNTED
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> alter session set container=pdb1;
Session altered.
SQL> @$ORACLE_HOME/rdbms/admin/utluptabdata.sql
PL/SQL procedure successfully completed.
SQL> alter session set container=cndba_pdb;
Session altered.
SQL> @$ORACLE_HOME/rdbms/admin/utluptabdata.sql
PL/SQL procedure successfully completed.
--再次执行查询
SQL> SELECT DISTINCT owner, table_name
FROM dba_tab_cols
WHERE data_upgraded = 'NO'
ORDER BY 1,2; 2 3 4
no rows selected
到此升级已全部完成
3.3.7 禁用闪回和删除还原点（可选）
如果前面启用了闪回了，和创建了还原点，那么要是不需要闪回功能，则可以禁用。
语法：
alter database flashback off;
select * from v$restore_point;
drop restore point ;
3.3.8 修改oracle用户的bash_profile文件
修改ORACLE_HOME即可
3.3.9 检查数据
SQL> alter session set container=cndba_pdb;
Session altered.
SQL> select count(*) from cndba.test;
COUNT(*)
----------
1545
