Oracle-单个PDB迁移升级到19c

1 迁移方式:

2 迁移流程:

3 迁移前环境检查

3.1 版本检查

4.2 源端上传pre-upgrade工具

4.3 源端使用pre-upgrade工具进行检查

4.4 源端检查pre-upgrade工具输出日志

4.5 源端PDB执行更新前的修复脚本preupgrade_fixups.sql

4.6 检查修复脚本执行完成的日志

4.7 将pre-upgrade工具的输出文件拷贝到目标主机

6.2 创建relocate并进行数据同步

6.3 监控relocate的数据同步进度

6.4 relocate恢复日志

7 refresh刷新增量数据

7.1 查看刷新配置

7.2 手动执行refresh，确认是否正常

8.5 目标端以升级模式打开pdb testdb

8.6 查看目标库pdb状态

9 目标端pdb testdb版本更新到19c(应用不可访问)

9.6 执行更新后的修复脚本postupgrade_fixups.sql

11.1 relocate命令或者refresh命令执行失败

11.2 正式迁移升级过程失败或者应用验证不通过

前言:

本文章主要描述如何将单个12.2版本的PDB数据库以较少的停机时间，安全的迁移升级到19c版本数据库的实施操作步骤以及相关回退步骤。

PS:文章篇幅较长，需要花费一定时间阅读。

1 迁移方式:

采用relocate+refresh pdb方式通过dblink从12.2源端在线热克隆pdb到目标端，并且通过refresh pdb从源端定时投递redo到目标端pdb进行增量恢复，从而减少应用正式迁移的停机时间。

再将pdb迁移到目标端19c之后，使用catupgrd.sql脚本方式将pdb升级到19c。

2 迁移流程:

迁移前环境准备:解决迁移的前提条件
迁移配置(应用可以访问):

通过dblink从12.2源端在线热克隆pdb数据文件到目标端

通过refresh pdb从源端定时投递redo到目标端pdb进行增量恢复

正式迁移(应用不可访问)

停应用，关闭源端pdb，数据不再写入

通过refresh pdb从源端刷新最后一次redo到目标端pdb进行增量恢复

启动源端pdb进行upgrade，升级为19c

3 迁移前环境检查

3.1 版本检查


---源端和目标端版本需要12.2以上，目标版本建议在19c以上
select * from v$version;
$ORACLE_HOME/OPatch/opatch lspatches

3.2 字节序与以及字符集检查


---源端和目标端需要要相同的endian type以及字符集
set linesize 400
set pagesize 400
select a.platform_id,a.platform_name,a.endian_format
from v$transportable_platform a,v$database b
where  a.platform_id=b.platform_id;
col parameter for a40
col value for a50
select * from  v$nls_parameters;

3.3 归档模式检查


---源端必须启动归档模式
archive log list

3.4 补丁检查

relocation相关问题补丁


源端和目标端对应版本建议应用相关补丁解决潜在的relocation bug问题
Patch 29469563 – Fixes an issue where PDB hot clones fail with ORA-15001, included in 19.9 and later
Patch 26001677 – Implements REFRESH MODE for PDB relocate, included in 19.8 and later

refresh相关问题补丁


源端和目标端对应版本建议应用相关补丁解决潜在的refresh bug问题
Patch 28374604 – Deletes partial redo logs created during refresh operations, available 19.1 and later

preupgrade相关问题补丁

Patch 32242034 – Corrects screen output of Oracle Preupgrade Information tool for instructions on running fixup scripts, available in Build 10 of the preupgrade.jar file from How to Download and Run Oracle’s Database Pre-Upgrade Utility Document 884522.1

TDE相关问题补丁


使用 Transparent Data Encryption (TDE)，建议应用以下补丁解决潜在的bug问题Patch 29175638 - Adds support for INCLUDING SHARED KEY clause to avoid ORA-46659 errors.
Patch 32220709 - Ensures shared PDB master keys display in v$encryption_keys, included in 19.14 and later.

3.5 数据库组件


---确保源端和目标端数据库软件具备相同的组件
set linesize 400
set pagesize 400
Col Comp_name Format a60
Col Status Format a12
Select Comp_name, status, Version
From Dba_Registry
Order by Comp_name;

3.6 本地undo检查


---源端和目标端CDB,PDB都要设置本地undo
col property_name for a50
col property_value for a50
set linesize 400
set pagesize 400  
select property_name,property_value
   from database_properties
   where property_name='LOCAL_UNDO_ENABLED'

3.7 目标端备库检查

使用standby=none的选项，目标端的数据库的备库不会自动同步新的pdb，需要在升级迁移之后，手动进行同步

3.8 目标端资源检查

空间检查


---当前asm磁盘或者本地数据目录可以容纳迁移PDB的数据量
asmcmd lsdg
df -h

进程数检查


---确认目标端的进程数可以容纳源端pdb的进程数
---确认源端pdb的使用进程数
select a.inst_id,b.name,count(*)
from gv$session a,v$pdbs b
where a.con_id=b.con_id
group by a.inst_id,b.name;
---查看目标端cdb的剩余进程数
select INITIAL_ALLOCATION-MAX_UTILIZATION
from gv$resource_limit
where resource_name like '%process%'

内存资源检查


---确认目标端的SGA,PGA内存可以容纳源端pdb的SGA,PGA内存使用
---查询源端pdb与目标端cdb的内存资源使用情况
col begin_time for a30
col end_time for a30
col pdb_name for a10
SELECT r.snap_id,
       r.con_id,
       p.pdb_name,
       r.begin_time,
       r.end_time,
       r.sga_bytes/1024/1024,
       r.pga_bytes/1024/1024,
       r.buffer_cache_bytes/1024/1024,
       r.shared_pool_bytes/1024/1024
FROM   dba_hist_rsrc_pdb_metric r,
       cdb_pdbs p
WHERE  r.con_id = p.con_id
ORDER BY r.begin_time;
---查看目标端cdb的内存资源配置
show parameter  sga_target
show parameter pga_

cpu资源检查


---确认目标端的cpu使用可以容纳源端pdb的高峰dbtime
---查询源端与目标端的服务器CPU资源使用情况
top
---查询源端与目标端dbtime使用情况
set linesize 200 ;
set pagesize 20000 ;
col DATE_TIME  for a45 ;
col STAT_NAME  for a10 ;
WITH sysstat AS (
  SELECT
    ss.instance_number  id,
    sn.begin_interval_time begin_interval_time,
    sn.end_interval_time end_interval_time,
    ss.stat_name stat_name,
    ss. VALUE e_value,
    lag (ss. VALUE, 1) over (ORDER BY ss.snap_id) b_value
  FROM
    DBA_HIST_SYS_TIME_MODEL ss,
    dba_hist_snapshot sn
  WHERE
    trunc (sn.begin_interval_time) >= sysdate - 14
  AND ss.snap_id = sn.snap_id
  AND ss.dbid = sn.dbid
  AND ss.instance_number = sn.instance_number
  AND ss.dbid = (SELECT dbid FROM v$database)
  AND ss.instance_number = (SELECT instance_number FROM v$instance)
  AND ss.stat_name = 'DB time'
) 
SELECT
  id,
  to_char (
    BEGIN_INTERVAL_TIME,
    'yyyy-mm-dd hh24:mi'
  ) || to_char (
    END_INTERVAL_TIME,
    ' hh24:mi'
  ) date_time,
  stat_name,
  round(
    (e_value - nvl(b_value, 0))/60/1000/1000 ,
    2
  ) dbtime_value
FROM
  sysstat
WHERE
  (e_value - nvl(b_value, 0)) > 0
AND nvl (b_value, 0) > 0;

3.9 下载pre-upgrade工具

从官网下载最新的pre-upgrade工具See How to Download and Run Oracle’s Database Pre-Upgrade Utility Document 884522.1

4 upgrade检查

4.1 源端创建upgrade日志目录

mkdir /tmp/preupgrade_log

4.2 源端上传pre-upgrade工具


---将下载的pre-upgrade上传到源端服务器
mkdir /tmp/preupgrade
cd /tmp/preupgrade
unzip /tmp/preupgrade_19_cbuild_13_lf\ .zip 
---输出内容
Archive:  /tmp/preupgrade_19_cbuild_13_lf .zip
  inflating: preupgrade_package.sql  
  inflating: preupgrade_driver.sql   
  inflating: dbms_registry_extended.sql  
  inflating: parameters.properties   
  inflating: preupgrade_messages.properties  
  inflating: components.properties   
  inflating: preupgrade.jar

4.3 源端使用pre-upgrade工具进行检查


---检查命令
#<Source Version Oracle Home>/jdk/bin/java –jar preupgrade.jar dir <directory location for the output logs of the tool> -c <pdbname>
su – oracle
$ORACLE_HOME/jdk/bin/java -jar /tmp/preupgrade/preupgrade.jar dir /tmp/upgrade dir  /tmp/preupgrade_log -c TESTDB
###############################脚本输出样例
==================
PREUPGRADE SUMMARY
==================
  /tmp/preupgrade_log/preupgrade.log
  /tmp/preupgrade_log/preupgrade_fixups.sql
  /tmp/preupgrade_log/postupgrade_fixups.sql
Execute fixup scripts across the entire CDB:
Before upgrade:
1. Execute preupgrade fixups with the below command
$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp/preupgrade_log/ -b preup_testdb -c 'TESTDB' /tmp/preupgrade_log/preupgrade_fixups.sql
2. Review logs under /tmp/preupgrade_log/
After the upgrade:
1. Execute postupgrade fixups with the below command
$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp/preupgrade_log/ -b postup_testdb -c 'TESTDB' /tmp/preupgrade_log/postupgrade_fixups.sql
2. Review logs under /tmp/preupgrade_log/
Preupgrade complete: 2022-11-05T16:08:17

4.4 源端检查pre-upgrade工具输出日志

检查日志:/tmp/preupgrade_log/preupgrade.log

4.5 源端PDB执行更新前的修复脚本preupgrade_fixups.sql


---执行pre-upgrade检查工具里面输出的命令即可
#$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l <directory location for the output logs of the tool> -b preup_<CDB unique name> -c '' <directory location for the output logs of the tool>/preupgrade_fixups.sql
---执行以下命令
$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp/preupgrade_log/ -b preup_testdb -c 'TESTDB' /tmp/preupgrade_log/preupgrade_fixups.sql

4.6 检查修复脚本执行完成的日志

more  /tmp/preupgrade_log//preup_testdb0.log

4.7 将pre-upgrade工具的输出文件拷贝到目标主机


---因为目标端upgrade之后，需要执行post_upgrade脚本，所以把产生的日志脚本拷贝到目标主机
scp -rp /tmp/preupgrade_log/ oracle@168.168.xx.xxx:/tmp

5 relocate前期准备

5.1 配置TNS


---在源端和目标端的tnsnames.ora都配置
#注意服务名用的是cdb的服务名
#RAC IP用的是scan ip
dest_testdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 162.168.xx.xxx)(PORT = 1532))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testdb)
    )
  )
source_testdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 162.168.xx.xxx)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testdb)
    )
  )

5.2 创建同步用户


---在源端cdb$root创建同步用户，用于后面pdb的迁移
create user c##repuser identified by "REPuser";
grant create session, resource to c##repuser container=all;
grant create pluggable database to c##repuser container=all;
grant sysoper to c##repuser container=all;
alter user c##repuser set container_data=all container=current;
grant select on cdb_pdbs to c##repuser;

5.3 创建DBLINK


---在目标端的CDB里面创建DBLINK，
create database link source_dblink connect to c##repuser identified by "REPuser" using 'source_testdb';
---验证DBLINK是否正常
select count(*) from dual@source_dblink;
COUNT(*)
----------
1

5.4 配置service到目标数据库


---查询pdb服务名
alter session set container=esdb;
select name from dba_services where upper(name) <> 'TESTDB'  and upper(name) not like 'SYS.%';
---pdb配置情况  
srvctl config service –d testdb -pdb testdb -service testdb_srv
---在目标库里面添加pdb
srvctl add service –d testdb -pdb testdb -service testdb_srv

6 relocate拷贝数据

6.1 relocate配置说明


1 此阶段将数据文件从源复制到目标,完成relocate的时间取决于PDB的大小和并行进程的数量。
  注意，PDB在此期间保持打开和可访问状态。
2 多个PDB relocate可以在不同的会话中并发运行。如果选择并发运行多个PDB relocate，对于RAC环境，可以在多个实例上平衡会话。
3 relocate命令会一直执行将数据文件从源CDB复制到目标CDB，数据文件拷贝使用来自目标CDB的并行查询(PQ)slave自动并行执行。拷贝将遵循正常的优先级分配给PQ slaves，不可能调整PQ slaves的访问优先级。
  注意，PARALLEL子句可以用于RELOCATE命令，只能用于限制文件复制操作的并行进程数量。并且不能超过目标CDB中增PARALLEL_MAX_SERVERS的设置大小。
4 强烈建议使用REFRESH MODE子句。这允许PDB的目标副本定期自动刷新(从源CDB投递redo并应用到relocate PDB的数据文件)，直到准备好完成最后的迁移为止。REFRESH模式可以显著缩短在目标数据库打开relocate PDB时所需的应用程序停机时间。建议将REFRESH MODE值设置为30分钟，以平衡资源利用率(传送和应用redo)和停机时间(PDB打开时所需应用的redo)。
5 透明数据加密(TDE)密钥的处理。通过在relocate命令上指定目标CDB密钥存储库密码，PDB的密钥将从源CDB密钥存储库传输到目标CDB密钥存储库，作为迁移过程的一部分。即使对于AUTOLOGIN密钥存储库，也需要提供密钥存储库密码。对于不使用TDE的环境，不要指定KEYSTORE IDENTIFIED BY " include SHARED KEY "子句。

6.2 创建relocate并进行数据同步


1 命令将导致属于源CDB上的PDB的文件被复制到目标CDB，使用任何可用的PQ slaves来执行复制。复制的过程类似于RMAN SECTIONSIZE处理，因为多个PQ slaves可以操作一个文件，命令将决定分段的大小。在所有文件拷贝完成之前，该命令不会返回控制。在relocate过程中，应用程序和最终用户活动仍然可以访问源PDB
2 要限制命令使用的PQ slave的数量，可以在命令末尾添加PARALLEL < PQ slave count>子句。任何大于目标CDB中设置的PARALLEL_MAX_SERVERS初始化参数的的值都将被忽略，该命令将默认返回PARALLEL_MAX_SERVERS。
3 当PDB打开时会触发必须处理的错误时，例如PDB在打开时升级PDB，则不允许使用AVAILABILITY MAX子句。由于这个限制，在relocate过程中不建议保存PDB (ALTER PLUGGABLE DATABASE SAVE state INSTANCES=ALL)的状态。
4 指定REFRESH MODE EVERY NN MINUTES允许后台进程定期(每NN分钟)检索源CDB生成的redo，并将源PDB的redo应用到目标PDB的文件副本上。当阶段1和阶段2之间有很大的时间间隔时，使用REFRESH MODE可以显著减少完成阶段2所需的时间。
5 在Data Guard环境中，使用standbys =NONE子句延迟PDB的恢复。目前无法在PDB relocate操作期间自动维护备用数据库,因此需要在relocate完成之后再对备库手动进行PDB恢复。
6 目标端设置db_create_file_dest路径，relocate的文件通过omf进行存放，即使源端和目标端的路径不一致，也可以通过omf在目标端设置, 没启用的话，克隆时需要指定fille_name_convert参数Alter system set db_create_file_dest='+DATADG';


---创建relocate命令
#create pluggable database <pdbname> from <pdbname>@<link name> relocate keystore identified by "" including shared key refresh mode every 30 minutes standbys=none parallel "x";
#在目标端创建relocate pdb,使用8个并行进程，增量备份每30分钟执行一次
#拷贝文件可能时长较长，写成脚本放后台执行
cat exec_relocate_TESTDB.sh
##############################脚本内容
export ORACLE_SID=db1
sqlplus / as sysdba <<EOF
set timing on 
create pluggable database TESTDB from TESTDB@source_dblink refresh mode every 30 minutes   standbys=none parallel 8 ;
exit;
EOF
#############################
#############################放后台执行
nohup sh exec_relocate_TESTDB.sh > exec_relocate_TESTDB.log &
注意:数据文件复制完成之后，命令才会执行返回

6.3 监控relocate的数据同步进度


---查看relocate的进度
set linesize 400
set pagesize 400
col opname for a30
col message for a50
select inst_id,opname, sofar, totalwork, time_remaining, message from gv$session_longops where time_remaining > 0 order by inst_id;

6.4 relocate恢复日志

可以通过数据库的alert日志查看数据恢复的情况

7 refresh刷新增量数据

7.1 查看刷新配置


---查看刷新配置,自动刷新是每30分钟一次
set linesize 400
set pagesize 400
col pdb_name for a20
col refresh_mode for a20
col refresh_interval for 99999
select pdb_id, pdb_name, status, refresh_mode,refresh_interval from cdb_pdbs;

7.2 手动执行refresh，确认是否正常


---目标端手动执行刷新
alter session set container=TESTDB;
alter pluggable database TESTDB refresh;
##############################后台日志media recovery输出样例
2022-11-07T17:44:56.360197+08:00
TESTDB(5):alter pluggable database TESTDB refresh
2022-11-07T17:45:01.277544+08:00
Applying media recovery for pdb-4099 from SCN 2448766 to SCN 2452802
Remote log information: count-4
thr-1, seq-4, logfile-+ARCH/TESTDB/ARCHIVELOG/2022_11_06/thread_1_seq_4.264.1120066983, los-2422261, nxs-2451552
thr-2, seq-4, logfile-+ARCH/TESTDB/ARCHIVELOG/2022_11_06/thread_2_seq_4.263.1120066971, los-2410992, nxs-2451505
thr-2, seq-5, logfile-+ARCH/TESTDB/partial_archivelog/2022_11_06/thread_2_seq_5.265.1120067099, los-2451505, nxs-18446744073709551615

7.3 启动到只读验证数据(可选)


---启动有报错，暂时忽略
SQL> alter pluggable database TESTDB open read only;
alter pluggable database TESTDB open read only
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "PQ_TIMEOUT_ACTION": invalid identifier
SQL> show pdbs;
    CON_ID CON_NAME        OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
   2 PDB$SEED        READ ONLY  NO
   3 PDB          READ WRITE NO
   4 TESTDB        READ ONLY  YES

7.4 恢复同步(可选)


---启动到mount，恢复同步刷新
SQL> alter pluggable database close;
Pluggable database altered.
SQL> alter pluggable database TESTDB refresh;
Pluggable database altered.

8 正式数据迁移(应用不可访问)

8.1 通知应用停机


通知应用停机  
---关闭testdb的服务
srvctl stop service –d testdb -pdb testdb -service testdb_srv

8.2 目标端刷新增量数据


---目标端刷新 PDB检索自上次刷新以来在源 CDB 生成的redo，并将其应用于目标端PDB。
alter session set container=TESTDB;
alter pluggable database TESTDB refresh;

8.3 源端关闭pdb testdb


---关闭前可以手动插入一条测试数据进行验证
create table test20221106(insert_date date);
insert into test20221106 select sysdate from dual;
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select * from test20221106;
#关闭源CDB中的PDB，数据库完全无法写入，数据保持静止
alter pluggable database TESTDB close immediate instances=all;
#确认两个节点的pdb已经mount,没有写入
show pdbs
SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 TESTDB                          MOUNTED

8.4 目标端刷新最后一次增量数据


---目标端端刷新PDB检索在源CDB生成的最后一个redo，以应用于目标端PDB，确保源端和目标端的数据相同,这是从阶段 2 开始到源 PDB 关闭期间生成的redo。
alter session set container=TESTDB;
alter pluggable database TESTDB refresh;

8.5 目标端以升级模式打开pdb testdb


---目标端refresh pdb关闭刷新模式
alter pluggable database TESTDB REFRESH  mode none;
---在目标端节点一以升级模式打开 PDB
alter pluggable database TESTDB open upgrade;
注意:此命令将需要一些时间才能执行，具体取决于必须从源 CDB 检索和应用的重做量，以及源和目标CDB之间网络中任何延迟的影响。
注意:在完成 PDB 升级之前，只能在 RAC 环境的一个实例中打开 PDB

8.6 查看目标库pdb状态


---查看pdb testdb是否为migrate
SQL> show pdbs;
    CON_ID CON_NAME        OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
   4 TESTDB        MIGRATE    YES
---查看pdb目前的违规设置，主要是pdb的版本12.2 跟CDB版本19不一致
set linesize 400
col time for a40
col name for a10
col cause for a30
col message for a50
col action for a40
select * from pdb_plug_in_violations where name ='TESTDB' and status <> 'RESOLVED';
TIME             NAME            CAUSE           TYPE      ERROR_NUMBER    LINE MESSAGE              STATUS    ACTION             CON_ID
------------------------------ ------------------------------ ------------------------------ --------- ------------ ---------- -------------------------------------------------- --------- ------------------------------ ----------
05-NOV-22 06.42.14.632730 PM   TESTDB            VSN not match         ERROR      0       1 PDB's version does not match CDB's version: PDB's  PENDING   Either upgrade the PDB or relo      4
                                     version 12.2.0.1.0. CDB's version 19.0.0.0.0.      ad the components in the PDB.

9 目标端pdb testdb版本更新到19c(应用不可访问)

9.1 更新版本到19c


---目标端节点一操作即可
su - oracle
---创建日志目录
mkdir $ORACLE_BASE/cfgtoollogs/upgrade_TESTDB
---对TESTDB进行更新
#$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catctl.pl -d $ORACLE_HOME/rdbms/admin -c '' -l $ORACLE_BASE/cfgtoollogs/<your_log_directory> catupgrd.sql
注意:运行升级所需的时间因 PDB 中包含的数据库产品组件和PDB 字典中的 PL/SQL 包数量所决定。
---更新可能时长较长，写成脚本放后台执行
cat exec_upgrade_TESTDB.sh
#############################脚本内容
#!/bin/bash
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catctl.pl -d $ORACLE_HOME/rdbms/admin -c 'TESTDB' -l $ORACLE_BASE/cfgtoollogs/upgrade_TESTDB catupgrd.sql
#############################
#############################放后台执行
nohup sh exec_upgrade_TESTDB.sh > exec_upgrade_TESTDB.log &
---更新输出日志
Grand Total Time: 4847s 
 LOG FILES: (/u01/app/oracle/cfgtoollogs/upgrade_TESTDB/catupgrd*.log)
Grand Total Upgrade Time:    [0d:1h:20m:47s]
Oracle Database Release 19 Post-Upgrade Status Tool    11-07-2022 22:39:5
Container Database: DB
[CON_ID: 5 => TESTDB]
Component                               Current         Full     Elapsed Time
Name                                    Status          Version  HH:MM:SS
Oracle Server                          UPGRADED      19.3.0.0.0  00:33:14
JServer JAVA Virtual Machine           UPGRADED      19.3.0.0.0  00:04:33
Oracle XDK                             UPGRADED      19.3.0.0.0  00:02:52
Oracle Database Java Packages          UPGRADED      19.3.0.0.0  00:00:27
OLAP Analytic Workspace                UPGRADED      19.3.0.0.0  00:00:50
Oracle Label Security                  UPGRADED      19.3.0.0.0  00:00:36
Oracle Database Vault                  UPGRADED      19.3.0.0.0  00:02:11
Oracle Text                            UPGRADED      19.3.0.0.0  00:00:45
Oracle Workspace Manager               UPGRADED      19.3.0.0.0  00:01:11
Oracle Real Application Clusters       UPGRADED      19.3.0.0.0  00:00:00
Oracle XML Database                    UPGRADED      19.3.0.0.0  00:08:16
Oracle Multimedia                      UPGRADED      19.3.0.0.0  00:01:23
Spatial                                UPGRADED      19.3.0.0.0  00:13:36
Oracle OLAP API                        UPGRADED      19.3.0.0.0  00:00:23
Datapatch                                                        00:04:44
Final Actions                                                    00:05:16
Post Upgrade                                                     00:00:25
Total Upgrade Time: 01:17:47 [CON_ID: 5 => TESTDB]
Database time zone version is 26. It is older than current release time
zone version 32. Time zone upgrade is needed using the DBMS_DST package.
Grand Total Upgrade Time:    [0d:1h:20m:47s]

9.2 启动到open状态


---目标端更新完PDB之后，PDB会变成mount状态，
SQL> show pdbs;
    CON_ID CON_NAME        OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
   2 PDB$SEED        READ ONLY  NO
   3 PDB          READ WRITE NO
   4 TESTDB        MOUNTED
SQL> 
---需要手动进行open
alter pluggable database TESTDB open instances=all;
SQL> show pdbs;
    CON_ID CON_NAME        OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
   2 PDB$SEED        READ ONLY  NO
   3 PDB          READ WRITE NO
   4 TESTDB        READ WRITE NO
---确认pdb的状态正常，在目标CDB里面检查PDB_PLUG_IN_VIOLATIONS确认里面的问题已经解决，没有新的问题
alter session set container=cdb$root;
select * from pdb_plug_in_violations where name ='TESTDB' and status <> 'RESOLVED' and type <> 'WARNING';
SQL> 
no rows selected
SQL>

9.3 检查组件状态为upgrade


---目标端执行utlusts查询当前pdb组件以及状态
#当前组件状态为upgrade
#时区需要更新
alter session set container=TESTDB;
@?/rdbms/admin/utlusts
SQL> Enter value for 1: text
Enter value for 1: text
####################日志输出样例
Oracle Database Release 19 Post-Upgrade Status Tool    11-07-2022 22:46:5
Container Database: DB
[CON_ID: 5 => TESTDB]
Component                               Current         Full     Elapsed Time
Name                                    Status          Version  HH:MM:SS
Oracle Server                          UPGRADED      19.3.0.0.0  00:33:14
JServer JAVA Virtual Machine           UPGRADED      19.3.0.0.0  00:04:33
Oracle XDK                             UPGRADED      19.3.0.0.0  00:02:52
Oracle Database Java Packages          UPGRADED      19.3.0.0.0  00:00:27
OLAP Analytic Workspace                UPGRADED      19.3.0.0.0  00:00:50
Oracle Label Security                  UPGRADED      19.3.0.0.0  00:00:36
Oracle Database Vault                  UPGRADED      19.3.0.0.0  00:02:11
Oracle Text                            UPGRADED      19.3.0.0.0  00:00:45
Oracle Workspace Manager               UPGRADED      19.3.0.0.0  00:01:11
Oracle Real Application Clusters       UPGRADED      19.3.0.0.0  00:00:00
Oracle XML Database                    UPGRADED      19.3.0.0.0  00:08:16
Oracle Multimedia                      UPGRADED      19.3.0.0.0  00:01:23
Spatial                                UPGRADED      19.3.0.0.0  00:13:36
Oracle OLAP API                        UPGRADED      19.3.0.0.0  00:00:23
Datapatch                                                        00:04:44
Final Actions                                                    00:05:16
Post Upgrade                                                     00:00:25
Total Upgrade Time: 01:17:47 [CON_ID: 5 => TESTDB]
Database time zone version is 26. It is older than current release time
zone version 32. Time zone upgrade is needed using the DBMS_DST package.
SQL>

9.4 进行重编译


---目标端执行utlrp重编译pl/sql 包
cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql
####################输出日志样例
[oracle@rac19a admin]$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/utlrp_catcon_115232.lst]
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/utlrp*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/utlrp_*.lst] files for spool files, if any
catcon.pl: completed successfully

9.5 检查组件状态为VALID


#目标端再次utlusts确认组件状态是否为VALID
alter session set container=TESTDB;
@?/rdbms/admin/utlusts
SQL> @?/rdbms/admin/utlusts
Enter value for 1: text
Enter value for 1: text
####################日志输出样例
Oracle Database Release 19 Post-Upgrade Status Tool    11-07-2022 23:05:4
Container Database: DB
[CON_ID: 5 => TESTDB]
Component                               Current         Full     Elapsed Time
Name                                    Status          Version  HH:MM:SS
Oracle Server                             VALID      19.3.0.0.0  00:33:14
JServer JAVA Virtual Machine              VALID      19.3.0.0.0  00:04:33
Oracle XDK                                VALID      19.3.0.0.0  00:02:52
Oracle Database Java Packages             VALID      19.3.0.0.0  00:00:27
OLAP Analytic Workspace                   VALID      19.3.0.0.0  00:00:50
Oracle Label Security                     VALID      19.3.0.0.0  00:00:36
Oracle Database Vault                     VALID      19.3.0.0.0  00:02:11
Oracle Text                               VALID      19.3.0.0.0  00:00:45
Oracle Workspace Manager                  VALID      19.3.0.0.0  00:01:11
Oracle Real Application Clusters          VALID      19.3.0.0.0  00:00:00
Oracle XML Database                       VALID      19.3.0.0.0  00:08:16
Oracle Multimedia                         VALID      19.3.0.0.0  00:01:23
Spatial                                   VALID      19.3.0.0.0  00:13:36
Oracle OLAP API                           VALID      19.3.0.0.0  00:00:23
Datapatch                                                        00:04:44
Final Actions                                                    00:05:16
Post Upgrade                                                     00:00:25
Post Compile                                                     00:14:07
Total Upgrade Time: 01:31:55 [CON_ID: 5 => TESTDB]
Database time zone version is 26. It is older than current release time
zone version 32. Time zone upgrade is needed using the DBMS_DST package.

9.6 执行更新后的修复脚本postupgrade_fixups.sql


---执行脚本postupgrade_fixups
alter session set container=TESTDB;
@/tmp/preupgrade_log/postupgrade_fixups_TESTDB.sql
####################输出日志样例
WARNING - This script was generated for database TESTDB.
Executing Oracle POST-Upgrade Fixup Script
Auto-Generated by:       Oracle Preupgrade Script
                         Version: 19.0.0.0.0 Build: 13
Generated on:            2022-11-06 17:21:57
For Source Database:     TESTDB
Source Database Version: 12.2.0.1.0
For Upgrade to Version:  19.0.0.0.0
Executing in container:  TESTDB
Preup                             Preupgrade
Action                            Issue Is
Number  Preupgrade Check Name     Remedied    Further DBA Action
------  ------------------------  ----------  --------------------------------
    6.  old_time_zones_exist      NO          Manual fixup recommended.
    7.  post_dictionary           YES         None.
    8.  post_fixed_objects        NO          Informational only.
                                              Further action is optional.
The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database upgrade is not
fully complete.  To resolve the outstanding issues, start by reviewing
the postupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.
SQL> SQL>

9.7 更新timezone


参考: Scripts to update the RDBMS DST (timezone) version in an 11gR2 or 12c database . (Doc ID 1585343.1)
---pdb需要重启
---查看当前pdb的dst
Alter session set container=TESTDB;
SELECT version FROM v$timezone_file;
---也可以通过以下检查
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
---如果查询出现以下值，需要进行处理
---PROPERTY_NAME VALUE
-- ------------------------------ ------------------------------
-- DST_PRIMARY_TT_VERSION <current DST version> <<<<------ this should match version FROM v$timezone_file if    not make sure the database is open when selecting from v$timezone_file;
-- DST_SECONDARY_TT_VERSION 0 <<<<------ this should be "0" if not then see point 3a) in note 977512.1 (for 11gR2) or note 1509653.1 (for 12c)
-- DST_UPGRADE_STATE NONE <<<<------ this should be "NONE" if not then see point 3a) in note 977512.1 (for 11gR2) or note 1509653.1 (for 12c)
SQL> SELECT version FROM v$timezone_file;
   VERSION
----------
  26
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
  2    3    4  
PROPERTY_NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION
26
DST_SECONDARY_TT_VERSION
0
DST_UPGRADE_STATE
NONE
---查看更新的脚本是否存在，有四个脚本
ls -lrt $ORACLE_HOME/rdbms/admin/utltz_*
rw-r--r-- 1 oracle oinstall  8317 Feb 25  2017 utltz_countstats.sql
-rw-r--r-- 1 oracle oinstall  7423 Feb 25  2017 utltz_countstar.sql
-rw-r--r-- 1 oracle oinstall 33684 Sep  9  2017 utltz_upg_check.sql
-rw-r--r-- 1 oracle oinstall 21526 Sep  9  2017 utltz_upg_apply.sql
--- utltz_countstats.sql脚本通过统计信息查看使用TSTZ column的表
--- utltz_countstar.sql 通过实际count(*) 查看使用TSTZ column的表
通过这两个脚本主要是用于显示当前是否有大的使用tstz的表，这会影响升级timezone的时间，但实际不一定要执行
---执行utltz_upg_check.sql脚本，
---执行脚本时，RAC只能启动一个节点，所以把节点二的pdb关闭
Alter pluggable database TESTDB close immediate;
注意，upg_tzv_check.sql不需要任何参数，它将自动检测已安装的最高DST补丁，并且不需要停机，这可以在实时生产数据库上执行，但它会清除dba_recyclebin
alter session set container=TESTDB;
spool /tmp/utltz_upg_check.log
@?/rdbms/admin/utltz_upg_check.sql
spool off
---输出没有报错
INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues ...
INFO: Database version is 19.0.0.0 .
INFO: This database is a Multitenant database.
INFO: This database is a PDB.
INFO: Current PDB is TESTDB .
INFO: Database RDBMS DST version is DSTv26 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv32 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen ...
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run utltz_upg_apply.sql to do the actual RDBMS DST update.
INFO: Note that the utltz_upg_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.
---执行upg_tzv_apply.sql脚本
注意: upg_tzv_apply.sql将重新启动pdb 2 次
alter session set container=TESTDB;
spool /tmp/utltz_upg_apply.log
@?/rdbms/admin/utltz_upg_apply.sql
spool off
---输出更新完成
INFO: Upgrading all non-SYS TSTZ data.
INFO: It might take time before any further output is seen ...
INFO: Do NOT start any application yet that uses TSTZ data!
INFO: Next is a list of all upgraded tables:
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
Table list: "DVSYS"."SIMULATION_LOG$"
Number of failures: 0
Table list: "DVSYS"."AUDIT_TRAIL$"
Number of failures: 0
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv32 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this SQL*Plus session.
INFO: Do not use it for timezone related selects.
---确认更新为32
SQL> alter session set container=TESTDB;
Session altered.
SQL> SELECT version FROM v$timezone_file;
   VERSION
----------
  32
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
  2    3    4  
PROPERTY_NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION
32
DST_SECONDARY_TT_VERSION
0
DST_UPGRADE_STATE
NONE
---启动节点二的pdb
Alter pluggable database TESTDB open;
SQL> 
SQL> show pdbs;
    CON_ID CON_NAME        OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
   4 TESTDB        READ WRITE NO

9.8 更新版本到19c


alter session set container=TESTDB;
select con_id, owner, object_type, object_name from cdb_objects where status='INVALID' order by 1;

9.9 更新版本到19c


---启动testdb的服务
srvctl start service –d testdb -pdb testdb -service testdb_srv
通知应用升级完成

10 迁移升级完成之后(可选)

10.1 源端删除服务

srvctl remove service –d <source CDB db_unique_name> -service <service name>

10.2 源端删除pdb

drop pluggable database <pdbname> including datafiles;

10.3 目标端删除dblink

drop database link <link name>;

10.4 目标端DG同步新的PDB

For Data Guard environments, follow the instructions in Making Use Deferred PDB Recovery and the STANDBYS=NONE Feature with Oracle Multitenant Document 1916648.1 to enable recovery of the PDB at the destination standby.

11 回退方案

11.1 relocate命令或者refresh命令执行失败


#如果命令执行失败，可以使用以下方式在目标端清理数据，再重新开始执行relocate+refresh
DestinationCDB-SQL> alter session set container=cdb$root;
DestinationCDB-SQL> drop pluggable database <pdbname> including datafiles;

11.2 正式迁移升级过程失败或者应用验证不通过


---关闭目标端环境
srvctl stop service –d testdb -pdb testdb -service testdb_srv
alter pluggable database TESTDB close instances=all;
----启动源端环境
alter pluggable database TESTDB open instances=all;
srvctl start service –d testdb -pdb testdb -service testdb_srv
----应用链接指向源环境

相关阅读:
SQL Server教程 - T-SQL-索引（INDEX）
如何全面升级spring-boot-2.x及Spring-security-oauth2
产品经理入门学习（二）：产品经理问题思考维度
 【Java】List、Set、数据结构、Collections
双链笔记软件 Roam Edit 的优点、缺点、评价及学习资源
 提高效率：VMLogin浏览器免密码同时登录多个Facebook账号
 C++算法竞赛常用编程模板总结
 GCC编译器生成库文件并编译
 图表展示X轴标题展示不全
 一文带你搞定抖音最近最火的情侣微信早报信息推送
原文地址：https://blog.csdn.net/sinat_36757755/article/details/127814526