oracle pdb从12.1迁移到19.20

oracle pdb从12.1迁移到19.20

1 unplug （12c的环境执行）

SQL> alter pluggable database VINCENT_TEST close immediate;
SQL> alter pluggable database VINCENT_TEST unplug into '/u01/backup/temp_20240401/VINCENT_TEST.xml';

2 plug （19c的环境执行）

SQL> create pluggable database VINCENT_TEST using '/u01/backup/temp_20240401/VINCENT_TEST.xml' nocopy tempfile reuse;

问题：
plug进19c时会报错。

3 升级数据字典（19c的环境执行） Grand Total Upgrade Time: [0d:0h:16m:53s]

$ORACLE_HOME/bin/dbupgrade -c VINCENT_TEST

4 检查pdb_plug_in_violations

SQL> alter pluggable database VINCENT_TEST open;

set line 200
col name for a15
col message for a120
select name,message,type,STATUS from pdb_plug_in_violations where STATUS<>'RESOLVED' and name='VINCENT_TEST'  order by time;


SQL> alter session set container=VINCENT_TEST;


--问题一
SQL> exec dbms_registry.OPTION_OFF('RAC');

--问题二
alter session set "_oracle_script"=true;
@/u01/app/oracle/product/12.1.0/dbhome_1/apex/apxremov1.sql
@/u01/app/oracle/product/12.1.0/dbhome_1/apex/apxremov2.sql 

alter pluggable database VINCENT_TEST close immediate;
alter pluggable database VINCENT_TEST open;

5 检查失效对象

--查看失效对象
set lines 200 pages 100
col OBJECT_NAME for a60
col owner for a15  
select owner,OBJECT_NAME,OBJECT_TYPE,to_char(CREATED,'yyyy-mm-dd hh24:mi:ss') CREATED,to_char(LAST_DDL_TIME,'yyyy-mm-dd hh24:mi:ss') LAST_DDL_TIME,STATUS from dba_objects where status='INVALID'  order by 1,3;

--编译失效对象
@?/rdbms/admin/utlrp.sql

--查询失效对象的时候，发现有很多失效的PUBLIC同义词，这些同义词关联的对象都是APEX_040200用户的。当前处理是把失效的同义词删除了。
set line 100
set pagesize 200
select OBJECT_NAME from dba_objects where status='INVALID' and owner='PUBLIC' and object_type='SYNONYM' ;

谨记：心存敬畏，行有所止。