【Oracle】oracle升级方案-DBUA

Oracle11.2.0.4升级到oracle12.2.0.1-DBUA图形工具

源库：11.2.0.4，OS：Linux6，单机
目标库：12.2.0.1，OS：Linux6，单机

参考官方文档：
Complete Checklist for Upgrading to Oracle Database 12c Release 2 (12.2) using DBUA (Doc ID 2189854.1)

一、升级前规划考虑

（1）版本生命周期
（2）升级路线图
（3）Database Upgrade Assistant (DBUA)

•	Database Upgrade Assistant (DBUA) interactively steps you through the upgrade process. configures the database for the new Oracle Database 12c release 2. It is the recommended method for performing a major release upgrade or patchset release upgrade.
•	DBUA automates the upgrade process by performing all of the tasks. DBUA makes appropriate recommendations for configuration options and then you can act on these recommendations.
•	DBUA provides support for Oracle Real Application Clusters (Oracle RAC) databases. In an Oracle RAC environment, DBUA upgrade all the database and configuration files on all nodes in the cluster.
•	DBUA, graphical user interface must be invoked within the new Oracle home where the Oracle Database 12c R2 software has been installed.
For windows, Only an Administrator or Installed owner should invoke DBUA for Windows systems.
•	DBUA starts the Pre-Upgrade Tool, which automatically fixes some configuration settings to the values required for the upgrade. For example, the Pre-Upgrade Tool can change initialization parameters to values required for the upgrade. The Pre-Upgrade Tool also provides you with a list of items that you need to fix manually before you can continue with the upgrade.
•	It also gives certain recommendations on certain areas belonging to the database. The recommendations can then be acted on making the upgrade process user friendly and easy.
•	Once, you address / fix the pre-upgrade recommendation / warnings /errors and continue with the upgrade, DBUYA shows the progress of the upgrade for each component of source database.
•	As with previous releases of DBUA, 12c DBUA restricts the carry over of hidden parameters since Oracle recommends not to have hidden parameters other than those suggested via support during the upgrade.
To view existing hidden parameters execute the following command while connected AS SYSDBA:
SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\';
•	DBUA performs some of the checks before actually starting the database upgrade. Some of the checks can be done manually to reduce downtime for the upgrade.
•	DBUA provides below options:
- Upgrade timezone. The default timezone vetrsion in 12.2.0.1 is 26.
- Gather dictionary statistics before upgrade.
- Make user tablespaces read only.
- Take RMAN backup before upgrade.
- Restore database backup to rollback upgrade
- Option to execute Custom scripts before and after upgrade
- show the location of DBUA logs and Alert log files.
- Option to upgrade existing listener to 12c home or create a new listener in 12.2 target home.
•	Starting with Oracle Database 12c release 2 (12.2), you can upgrade the database without disabling Oracle Database Vault. However, if you disabled Oracle Database Vault, then you must enable it manually after an upgrade.

二、预检查

1、源端基本信息采集

1. 近期性能趋势图
2. 是否开启归档、force logging
3. 用户数个数、对象个数及状态
4. CPU、内存物理信息
5. 数据库组件信息
6. 数据库参数信息、尤其是隐藏参数
7. TNS、Crontab信息
8. 存储配置信息

2、更新前注意事项

1、 实例要启动
2、 system表空间要足够
3、 归档空间要足够，若不足够，先关归档
4、 内存要足够
5、 job_queue_processes改为非0
6、 cluster_database改为true（RAC）
7、 glogin.sql中添加的东西要删除
8、 OLAP组件要卸载
9、 EM要卸载
10、 无效对象要处理（重新编译或删除）
11、 确保datafile online
12、 收集字典信息
13、 清理回收站

更新前收集字典信息
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

3、数据静态检查

1. Verifying Materialized View Refreshes are Complete Before Upgrade
2. Verifying all forgroud session close eg LOCAL=NO、Job、crontab
3. Verifying there are no transaction need rollback
4. Resolve Outstanding Distributed Transactions Before Upgrading

三、 升级步骤

1、 安装12c

1、 解压linuxx64_12201_database.zip到/oracle/database12c下
2、 设环境变量
export ORACLE_SID=orcl11g
export ORACLE_BASE=/oracle/app12c/oracle
export ORACLE_HOME=/oracle/app12c/oracle/product/12.2.0/db_1
export LD_LIBRARY_PATH=/oracle/app12c/oracle/product/12.2.0/db_1/lib
export PATH=/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin:/root/dba:/oracle/app12c/oracle/product/12.2.0/db_1/bin:/bin:/usr/bin:/usr/sbin:/usr/local/sbin:/usr/local/bin:/usr/X11R6/bin:/root/dba:/sbin
umask 022
export TNS_ADMIN=/oracle/app12c/oracle/product/12.2.0/db_1/network/admin

alias sql=‘sqlplus / as sysdba’
alias net=‘cd $ORACLE_HOME/network/admin’

3、 设置db.rsp
4、 安装12c
/oracle/database12c/database/runInstaller -ignorePrereq -silent -force -responseFile /home/oracle/db.rsp

5、设置环境变量
su - oracle
export ORACLE_HOME=/oracle/app12c/oracle/product/12.2.0/db_1
export PATH=$ORACL{E}_{H}OME/bin:$PATH

注意将/etc/oratab中11g的配置解除注释：
[root@rhel75 ~]# cat /etc/oratab
orcl11g:/oracle/app/oracle/product/11.2.0/db_1:N
orcl11g:/oracle/app12c/oracle/product/12.2.0/db_1:N

–执行dbua：
cd /oracle/app12c/oracle/product/12.2.0/db_1/bin
./dbua

依次处理上面告警：

（1） 删掉OLAP组件、AMD
SQL中执行11g的$ORACLE_HOME/olap/admin/catnoamd.sql
（2） 重新编译sys/system下无效对象
先查看失效对象：

SQL> set serveroutput on
SQL> set line 500
SQL> execute dbms_preup.invalid_objects;
SYS/SYSTEM INVALID OBJECTS
OWNER                         |OBJECT_NAME                                                                    |OBJECT_TYPE
--------------------------------------------------------------------------------------------------------------------------------
NON SYS/SYSTEM INVALID OBJECTS
OWNER                         |OBJECT_NAME                                                                    |OBJECT_TYPE
--------------------------------------------------------------------------------------------------------------------------------
U1                            INSERT_SJBJ                                                                     PROCEDURE
U1                            INSERT_T1                                                                       PROCEDURE

PL/SQL procedure successfully completed.
---重新编译：
alter procedure u1.INSERT_SJBJ compile;
alter procedure u1.INSERT_T1 compile;

--或无法编译成功，确认无用后直接删除：
drop procedure u1.INSERT_SJBJ;
drop procedure u1.INSERT_T1;

（3） 归档空间不足，暂无法扩展空间，故先关闭11g的归档
（4） password_versons先不处理

SQL> select username,password_versions from dba_users;

USERNAME                       PASSWORD
------------------------------ --------
U1                             10G
U3                             10G
U2                             10G
T4                             10G 11G
ANGEL                          10G
SCOTT                          10G
SYSTEM                         10G 11G
SYS                            10G 11G
DBSNMP                         10G 11G
SYSMAN                         10G 11G
SPATIAL_WFS_ADMIN_USR          10G 11G

USERNAME                       PASSWORD
------------------------------ --------
SPATIAL_CSW_ADMIN_USR          10G 11G
APEX_PUBLIC_USER               10G 11G
DIP                            10G 11G
MDDATA                         10G 11G
XS$NULL                        11G
ORACLE_OCM                     10G 11G
OLAPSYS                        10G 11G
SI_INFORMTN_SCHEMA             10G 11G
OWBSYS                         10G 11G
ORDPLUGINS                     10G 11G
XDB                            10G 11G

USERNAME                       PASSWORD
------------------------------ --------
ANONYMOUS
CTXSYS                         10G 11G
ORDDATA                        10G 11G
OWBSYS_AUDIT                   10G 11G
APEX_030200                    10G 11G
APPQOSSYS                      10G 11G
WMSYS                          10G 11G
EXFSYS                         10G 11G
ORDSYS                         10G 11G
MDSYS                          10G 11G
FLOWS_FILES                    10G 11G

USERNAME                       PASSWORD
------------------------------ --------
OUTLN                          10G 11G

34 rows selected.

（5）
alter system set job_queue_processes=5;
PURGE DBA_RECYCLEBIN;

创建一个新的监听

四、更新后检查

1、检查组件和对象有效性
COMP_NAME STATUS VERSION

---

JServer JAVA Virtual Machine VALID 12.2.0.1.0
OLAP Analytic Workspace VALID 12.2.0.1.0
OLAP Catalog REMOVED 11.2.0.4.0
Oracle Application Express VALID 5.0.4.00.1
Oracle Database Catalog Views VALID 12.2.0.1.0
Oracle Database Java Packages VALID 12.2.0.1.0
Oracle Database Packages and Types VALID 12.2.0.1.0
Oracle Multimedia VALID 12.2.0.1.0
Oracle OLAP API VALID 12.2.0.1.0
Oracle Text VALID 12.2.0.1.0
Oracle Workspace Manager VALID 12.2.0.1.0
Oracle XDK VALID 12.2.0.1.0
Oracle XML Database VALID 12.2.0.1.0
Spatial VALID 12.2.0.1.0

14 rows selected.

SQL> select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type from dba_objects where status=‘INVALID’ order by owner,object_type;

no rows selected

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

SQL>

下载dbupgdiag.sql脚本检查
若有无效对象 ，执行sql> @?/rdbms/admin/utlrp.sql

五、客户端连接

ORA-28040 no matching authentication protocol

参考：
After a Database Upgrade to 12c for E-Business Suite, JDeveloper Connections Fail With Error “ORA-28040 no matching authentication protocol” (Doc ID 2125856.1)

方案：
在12c中，SQLNET.ALLOWED_LOGON_VERSION已过期，被如下两个参数替换：
SQLNET.ALLOWED_LOGON_VERSION_SERVER
SQLNET.ALLOWED_LOGON_VERSION_CLIENT
Note 1304142.1 - 11g and Older: How To Use the Parameter SQLNET.ALLOWED_LOGON_VERSION Correctly (Doc ID 1304142.1)

解决：

1. Review the sqlnet_ifile.ora file and confirm the following entries are present:
   SQLNET.ALLOWED_LOGON_VERSION_SERVER
   SQLNET.ALLOWED_LOGON_VERSION_CLIENT
2. Update the sqlnet_ifile.ora settings for the above parameters to the lowest version level that is required in your environment. For example:
   A. If the initialization parameter SEC_CASE_SENSITIVE_LOGON is set to FALSE:
   SQLNET.ALLOWED_LOGON_VERSION_SERVER = 8
   B. If SEC_CASE_SENSITIVE_LOGON is set to TRUE
   SQLNET.ALLOWED_LOGON_VERSION_SERVER = 10 or 11(depending on the db and client version)