一、前言

由于11G已经被Oracle淘汰，不再更新补丁，官方建议升级到最新稳定版 19C，因此了解下升级方式。

本文将介绍其中一种方式：DBUA

Complete Checklist to Upgrade the Database to 11gr2 Using DBUA(Doc ID 870814.1)?? ?
Complete Checklist for Upgrading to Oracle Database 12c Release 1 using DBUA(Doc ID 1516557.1)?? ?
Complete Checklist for Upgrading to Oracle Database 12c Release 2 (12.2) using DBUA(Doc ID 2189854.1)
Oracle 18c - Complete Checklist for Upgrading to Oracle Database 18c (18.x) using DBUA(Doc ID 2418576.1)
Oracle 19c - Complete Checklist for Upgrading to Oracle Database 19c (19.x) using DBUA(Doc ID 2545064.1)

参考MOS文档：Oracle 19c - Complete Checklist for Upgrading to Oracle Database 19c (19.x) using DBUA (Doc ID 2545064.1)?? ?

DBUA也支持静默方式：Oracle 19c - DBUA In Silent Mode (Doc ID 2548985.1)?? ?

参考文章：使用DBUA升级 Oracle 11.2.0.4到Oracle 19C的问题记录

Database Upgrade Assistant (DBUA) provides a graphical user interface to guide you through the upgrade of Oracle Database.

Oracle对主机版本支持：

11GR2：

19C：

Notes：19C对操作系统支持最低要求为Linux7，如果11GR2主机版本为Linux 6，则需要一台新主机Linux7安装19C。

升级路线：

Direct Upgrade to 19.x:

Indirect Upgrade to 19.x:

二、环境准备

本次测试尽量按照生产环境升级进行模拟，故而使用2台主机进行测试：

注意：源库为生产环境，目标库为升级环境，需要通过rman的将生产环境实例备份恢复到目标库，保留生产环境用于失败回退。

测试环境安装过程忽略，可参考：

10分钟！一键部署Oracle 11GR2单机

源库

11GR2：

cd /soft
./OracleShellInstall.sh -i 10.211.55.100 `#Public ip`\
-n dbua11g `# hostname`\
-o lucifer `# oraclesid`\
-op oracle `# oracle user password`\
-b /u01/app `# install basedir`\
-s AL32UTF8 `# characterset`\
-opa 31537677 `# oracle psu number`

cd /soft
./OracleShellInstall.sh -i 10.211.55.100 `#Public ip`\
-n dbua19c `# hostname`\
-o lucifer `# oraclesid`\
-op oracle `# oracle user password`\
-b /u01/app `# install basedir`\
-s AL32UTF8 `# characterset`\
-opa 31537677 `# oracle psu number` \
-w Y

cp /home/oracle/.bash_profile /home/oracle/.bash_profile_11G
cd /soft
./OracleShellInstall.sh -i 10.211.55.100 `#Public ip`\
-n dbua19c `# hostname`\
-o lucifer `# oraclesid`\
-op oracle `# oracle user password`\
-b /u01/app `# install basedir`\
-s AL32UTF8 `# characterset`\
-m Y

##解压替换OPatch包
unzip -o p6880880_190000_Linux-x86-64.zip -d /u01/app/oracle/product/19.3.0/db/
chown -R oracle:oinstall /u01/app/oracle/product/19.3.0/db/OPatch

##解压DB RU补丁
unzip p32545013_190000_Linux-x86-64.zip
chown -R oracle:oinstall 32545013

##安装19c Oracle软件
cd $ORACLE_HOME
./runInstaller -applyRU /soft/32545013/

#!/bin/sh
source ~/.bash_profile
backtime=`date +"20%y%m%d%H%M%S"`
rman target / log=/backup/level0_backup_${backtime}.log<<EOF
run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
crosscheck backup;
crosscheck archivelog all; 
sql"alter system switch logfile";
delete noprompt expired backup;
delete noprompt obsolete device type disk;
backup incremental level 0 database include current controlfile format '/backup/backlv0_%d_%T_%t_%s_%p';
backup archivelog all DELETE INPUT;
release channel c1;
release channel c2;
}
EOF

##源端生成pfile参数文件
create pfile='/backup/11gpfile.ora' from spfile;
##拷贝备份至目标库
scp * 10.211.55.100:/backup/

##恢复至目标端11GR2中
mkdir -p /u01/app/oracle/fast_recovery_area/lucifer
mkdir -p /u01/app/oracle/admin/lucifer/adump
mkdir -p /oradata/lucifer/
chown -R oracle:oinstall /u01/app/oracle/fast_recovery_area
chown -R oracle:oinstall /u01/app/oracle/admin/lucifer/adump
chown -R /oradata/lucifer/
##启动数据库实例到nomount状态
su - oracle
source .bash_profile_11G
sqlplus / as sysdba
startup nomount pfile='/backup/11gpfile.ora'

##rman进行恢复
restore controlfile from '/backup/backlv0_LUCIFER_20210518_1072888737_3_1';
alter database mount;
##注册备份目录
catalog start with '/backup/';

##恢复数据
restore database;

##将源库开启到readonly模式
alter system switch logfile;
shutdown immediate
startup mount
alter database open read only

##拷贝归档日志到目标端
cd /archivelog
scp * 10.211.55.100:/backup

##注册归档日志
catalog start with '/backup/';
recover database;

##打开数据库
alter database open resetlogs;

--检查无效对象和组件
select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from dba_registry order by comp_name;
 
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;
 
select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type ;
 
BEGIN
  dbms_preup.invalid_objects;
END;
 
 
--ReBuild
sqlplus / as sysdba
@$ORACLE_HOME/rdbms/admin/utlrp.sql

##1.解压APEX包
unzip apex_19.2.zip
chown -R oracle:oinstall /soft/apex

##2.查看当前APEX版本
SELECT comp_name,
       status,
       version
  FROM dba_registry
 WHERE comp_name = 'Oracle Application Express';

--3.2.1.00.12

##3.进入APEX解压目录进行升级
cd /soft/apex
sqlplus / as sysdba @apexins.sql SYSAUX SYSAUX TEMP /i/

##4.升级完后查看APEX版本

--19.2.0.00.18

##5.编译无效对象
sqlplus / as sysdba
@$ORACLE_HOME/rdbms/admin/utlrp.sql

SELECT version FROM v$timezone_file;

--14

run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
crosscheck backup;
crosscheck archivelog all; 
sql"alter system switch logfile";
delete noprompt expired backup;
delete noprompt obsolete device type disk;
backup database include current controlfile format '/backup/backlucifer_%d_%T_%t_%s_%p';
backup archivelog all DELETE INPUT;
release channel c1;
release channel c2;
}

ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=SPFILE;

su - oracle
export Earlier_release_Oracle_home=/u01/app/oracle/product/11.2.0/db
export New_release_Oracle_home=/u01/app/oracle/product/19.3.0/db/
$Earlier_release_Oracle_home/jdk/bin/java -jar $New_release_Oracle_home/rdbms/admin/preupgrade.jar TERMINAL TEXT DIR /soft

alter system set processes=1500 scope=spfile;
alter system set sga_max_size=4G scope=spfile;
alter system set sga_target=2G scope=spfile;

shutdown immediate
startup

select name from v$datafile;
select name from v$tempfile;

alter tablespace SYSAUX add datafile '/oradata/lucifer/sysaux02.dbf' size 200M autoextend off;

alter tablespace SYSTEM add datafile '/oradata/lucifer/system02.dbf' size 600M autoextend off;

alter tablespace UNDOTBS1 add datafile '/oradata/lucifer/undotbs02.dbf' size 300M autoextend off;

alter tablespace TEMP add tempfile '/oradata/lucifer/temp02.dbf' size 200M autoextend off;

sqlplus / as sysdba @?/rdbms/admin/catnoexf.sql

cd /soft
sqlplus / as sysdba @/soft/preupgrade_fixups.sql

SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;

select owner, mview_name from all_mviews where staleness = 'STALE';

select owner, mview_name from all_mviews where staleness not in ('FRESH', 'STALE', 'UNKNOWN') or compile_state not in ('VALID');

SELECT u.name owner, o.name mview_name FROM sys.obj$ o, sys.user$ u, sys.sum$s WHERE o.type# = 42 AND o.owner# = u.user# and s.obj# = o.obj# and bitand(s.mflags, 8) = 8;

show parameter compatible

--11.2.0.4.0

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

--Execute below query to check for the status of the backup:

SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

--Ensure that no files require media recovery:

SQL> SELECT * FROM v$recover_file;

PURGE DBA_RECYCLEBIN;

##停止em
emctl stop dbconsole

##将以下脚本脚本从新安装的Oracle主目录（19c）复制到需要升级的数据库的Oracle主目录（11.2）
ORACLE_HOME/rdbms/admin/olspreupgrade.sql
ORACLE_HOME/rdbms/admin/emremove.sql
ORACLE_HOME/olap/admin/catnoamd.sql

##执行脚本
sqlplus / as sysdba
@?/rdbms/admin/emremove.sql

##执行脚本
sqlplus / as sysdba
@?/rdbms/admin/catnoamd.sql

--如果有10g版本，建议您参考Oracle文档来修复10g版本，如果不这样做，升级完成后具有LOCKED的用户帐户。
SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS;

--查看隐含参数_optimizer_cartesian_enabled，确保设置为TRUE

SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\';

alter system set "_optimizer_cartesian_enabled"=TRUE;

sqlplus / as sysdba @?/rdbms/admin/utlrp.sql

echo $ORACLE_HOME
/u01/app/oracle/product/19.3.0/db

# dbua

cat <<EOF>> /etc/oratab
lucifer:/u01/app/oracle/product/11.2.0/db:N
EOF

mkdir -p /u01/app/oracle/admin/lucifer/dpdump
chown -R oracle:oinstall /u01/app/oracle/admin/lucifer/dpdump

sqlplus / as sysdb @/soft/postupgrade_fixups.sql

chown -R oracle:oinstall /soft/dbupgdiag.sql

su - oracle
cd /soft

sqlplus / as sysdba @dbupgdiag.sql

--1.填写输出目录：/soft

##检查无效对象
select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from dba_registry order by comp_name;
 
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;
 
select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type ;

##编译无效对象
sqlplus / as sysdba @?/rdbms/admin/utlrp.sql

rm -rf /home/oracle/.bash_profile_11G
rm -rf /u01/app/oracle/product/11.2.0/db

##关闭11G监听
ps -ef|grep tns

kill -9 $spid

##修改LOCAL_LISTENER
alter system reset local_listener;
shutdown immediate
startup

##Notes:如果不将LOCAL_LISTENER设置为空，动态监听无法注册。

lsnrctl stop
cd $TNS_ADMIN

##修改listener.ora文件：
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbua19c)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

lsnrctl start

cd $TNS_ADMIN
cat <<EOF >>sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
EOF

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;

ALTER SYSTEM SET COMPATIBLE="19.0.0" SCOPE=SPFILE;
shutdown immediate
startup