Oracle 9i 11g历史库升级迁移数据至19c CDB

来源：这里教程网时间：2026-03-03 16:18:29 作者：

Oracle 9i 11g历史库升级迁移数据至19c CDB.docx

l 利用历史库备份集迁移数据至19c多租户环境，归档历史数据

l 涉及9.2.0.7、9.2.0.8、11.2.0.4，Aix、Linux环境数套数据库

l 升级至19c保留源库字符集，涉及ZHS16GBK、US7ASCII两种字符集

l 由于从备份集恢复，无停机时间要求，所以下面方案均不考虑停机时间

（1）需要利用Aix中间环境恢复。（2）数据迁移可以使用先升级至9.2.0.8，然后升级至11.2.0.4，然后dbua升级至19c中间库，最后通过hot clone保留字符集到最终19c库。（3）或者使用dbca建立19c与源库相同字符集中间库，hot clone至19c AL32UTF8字符集的最终库，直接使用exp/imp升级至19c，保留原字符集。

（1）Aix平台利用中间环境恢复，然后使用dbua或者expdp/impdp升级至19c中间库，通过hot clone保留字符集。（2）Linux平台源库，可以直接在19c Linux中间库环境恢复，dbua就地升级至19c，然后通过non-cdb转换方式转换成cdb，最后hot-clone方式克隆到最终19c数据库。

Oracle 12.2版本以后可以PDB与CDB字符集不同，但是在CREATE PLUGGABLE DATABASE语句中并无指定字符集相关语法，需要通过hot-clone方式实现与cdb不同字符集。

l Aix中间机器一台：Aix 7.1 预装9.2.0.8、11.2.0.4两个版本软件。

l CentOS 7.6机器两台

ü A机器作为19c中间库，安装11.2.0.4/19.3 Oracle软件。

ü B作为19c最终库，安装19.3数据库软件。

l 下面敏感字段均做过处理。

l 文档会针对三种场景进行介绍。

l 源库：Aix 9.2.0.7

l Aix中间库: Aix 9.2.0.8

l Linux中间库：CentOS 7.6 19.3

l Linux目标库：CentOS 7.6 19.3

l Aix使用dbms_backup_restore恢复备份集

l exp导出

l 19c中间库导入

l 19c目标库hot clone保留源库字符集迁移

*.background_dump_dest='/oradata/ora9/app/oracle/admin/db1/bdump' *.compatible='9.2.0.0.0' *.control_files='/control01.ctl' *.core_dump_dest='/oradata/ora9/app/oracle/admin/db1/cdump' *.cursor_space_for_time=TRUE *.db_block_size=8192 *.db_cache_size=2147483648 *.db_domain='' *.db_file_multiblock_read_count=32 *.db_keep_cache_size=268435456 *.db_name='db1' *.db_writer_processes=3 *.fast_start_mttr_target=300 *.java_pool_size=0 *.job_queue_processes=12 *.large_pool_size=134217728 *.log_archive_start=TRUE *.log_buffer=10485760 *.log_checkpoint_interval=10000 *.max_dump_file_size='10240'

*.open_cursors=2000

*.partition_view_enabled=TRUE

*.pga_aggregate_target=134217728

*.pre_page_sga=TRUE

*.processes=2000

*.remote_login_passwordfile='EXCLUSIVE'

*.shared_pool_size=536870912

db11.thread=1

db12.thread=2

*.timed_statistics=TRUE

*.undo_management='AUTO'

*.undo_tablespace='UNDO1'

*.user_dump_dest='/oradata/ora9/app/oracle/admin/db1/udump' mkdir -p /oradata/ora9/app/oracle/admin/db1/bdump mkdir -p /oradata/ora9/app/oracle/admin/db1/udump mkdir -p /oradata/ora9/app/oracle/admin/db1/cdump sqlplus /nolog conn / as sysba startup nomount pfile='/tmp/pfile.ora'; rman target / RMAN> restore controlfile from ‘/db1/ctl_bak.rman’; RMAN> alter database mount;

2.2.2 传输备份集，使用dbms_backup_restore恢复

l sftp传输rman备份集

l 由于备份集中控制文件未包含备份集信息，9i catalog命令未提供注册备份集功能，所以只能使用dbms_backup_restore包恢复数据文件。#DBMS_BACKUP_RESTORE恢复数据库：注意：

l 下面备份集名称与下面datafile集合要对应，可以从rman备份日志中查看，如果没有日志，则只能进行组合尝试。

l 也可以一次恢复多个备份集，但是备份集顺序与下面数据文件列表集合顺序要对应，否则会报无法找到某个数据文件备份。 DECLARE V_DEVICE VARCHAR2(100); V_FINISH BOOLEAN; TYPE T_FILENAMETABLE IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER; V_FILENAMETABLE T_FILENAMETABLE; V_MAXPIECES NUMBER :=4; BEGIN V_FILENAMETABLE(1) := '/oradata/ora9/db1rman_final/fulldb_db1_20191025_58106_1'; V_DEVICE := SYS.DBMS_BACKUP_RESTORE.DEVICEALLOCATE(); SYS.DBMS_BACKUP_RESTORE.RESTORESETDATAFILE; SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(56,'/rlvdb1_db1_idx11.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(62,'/rlvdb1_db1_3_12.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(52,'/rlvdb1_db1_1_08.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(42,'/rlvdb1_db1_4_07.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(49,'/rlvdb1_db1_4_10.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(06,'/rlvdb1_db1_1_01.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(10,'/rlvdb1_db1_1_05.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(14,'/rlvdb1_db1_2_04.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(18,'/rlvdb1_db1_3_03.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(22,'/rlvdb1_db1_4_02.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(26,'/rlvdb1_db1_idx1.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(30,'/rlvdb1_xc_def01.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(38,'/rlvdb1_db1_idx6.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(48,'/rlvdb1_db1_def04.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(01,'/rlvdb1_system.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(35,'/rlvdb1_db1_def02.dbf'); FOR I IN 1..V_MAXPIECES LOOP SYS.DBMS_BACKUP_RESTORE.RESTOREBACKUPPIECE(V_FILENAMETABLE(I),V_FINISH); IF V_FINISH THEN SYS.DBMS_BACKUP_RESTORE.DEVICEDEALLOCATE; RETURN; END IF; END LOOP; SYS.DBMS_BACKUP_RESTORE.DEVICEDEALLOCATE; END; / DECLARE V_DEVICE VARCHAR2(100); V_FINISH BOOLEAN; TYPE T_FILENAMETABLE IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER; V_FILENAMETABLE T_FILENAMETABLE; V_MAXPIECES NUMBER :=4; BEGIN V_FILENAMETABLE(1) := '/oradata/ora9/db1rman_final/fulldb_db1_20191025_58107_1'; V_DEVICE := SYS.DBMS_BACKUP_RESTORE.DEVICEALLOCATE(); SYS.DBMS_BACKUP_RESTORE.RESTORESETDATAFILE; SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(57,'/rlvdb1_db1_idx12.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(63,'/rlvdb1_db1_3_08.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(54,'/rlvdb1_db1_idx10.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(02,'/rlvdb1_undotbs1.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(59,'/rlvdb1_db1_def05.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(53,'/rlvdb1_db1_2_07.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(09,'/rlvdb1_db1_1_04.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(13,'/rlvdb1_db1_2_03.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(17,'/rlvdb1_db1_3_02.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(21,'/rlvdb1_db1_4_01.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(25,'/rlvdb1_db1_4_05.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(29,'/rlvdb1_db1_idx4.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(36,'/rlvdb1_db1_idx5.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(43,'/rlvdb1_db1_1_06.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(05,'/rlvdb1_db1_def01.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(04,'/rlvdb1_pub_def1.dbf'); FOR I IN 1..V_MAXPIECES LOOP SYS.DBMS_BACKUP_RESTORE.RESTOREBACKUPPIECE(V_FILENAMETABLE(I),V_FINISH); IF V_FINISH THEN SYS.DBMS_BACKUP_RESTORE.DEVICEDEALLOCATE; RETURN; END IF; END LOOP; SYS.DBMS_BACKUP_RESTORE.DEVICEDEALLOCATE; END; / DECLARE V_DEVICE VARCHAR2(100); V_FINISH BOOLEAN; TYPE T_FILENAMETABLE IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER; V_FILENAMETABLE T_FILENAMETABLE; V_MAXPIECES NUMBER :=4; BEGIN V_FILENAMETABLE(1) := '/oradata/ora9/db1rman_final/fulldb_db1_20191025_58108_1'; V_DEVICE := SYS.DBMS_BACKUP_RESTORE.DEVICEALLOCATE(); SYS.DBMS_BACKUP_RESTORE.RESTORESETDATAFILE; SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(58,'/rlvdb1_db1_idx13.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(61,'/rlvdb1_db1_2_08.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(45,'/rlvdb1_db1_idx9.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(44,'/rlvdb1_db1_1_07.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(03,'/rlvdb1_undotbs2.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(50,'/rlvdb1_db1_4_09.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(07,'/rlvdb1_db1_1_02.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(11,'/rlvdb1_db1_2_01.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(15,'/rlvdb1_db1_2_05.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(19,'/rlvdb1_db1_3_04.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(23,'/rlvdb1_db1_4_03.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(27,'/rlvdb1_db1_idx2.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(31,'/rlvdb1_xc_def02.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(39,'/rlvdb1_db1_4_06.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(37,'/rlvdb1_db1_def03.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(47,'/rlvdb1_pub_def2.dbf'); FOR I IN 1..V_MAXPIECES LOOP SYS.DBMS_BACKUP_RESTORE.RESTOREBACKUPPIECE(V_FILENAMETABLE(I),V_FINISH); IF V_FINISH THEN SYS.DBMS_BACKUP_RESTORE.DEVICEDEALLOCATE; RETURN; END IF; END LOOP; SYS.DBMS_BACKUP_RESTORE.DEVICEDEALLOCATE; END; / DECLARE V_DEVICE VARCHAR2(100); V_FINISH BOOLEAN; TYPE T_FILENAMETABLE IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER; V_FILENAMETABLE T_FILENAMETABLE; V_MAXPIECES NUMBER :=4; BEGIN V_FILENAMETABLE(1) := '/oradata/ora9/db1rman_final/fulldb_db1_20191025_58109_1'; V_DEVICE := SYS.DBMS_BACKUP_RESTORE.DEVICEALLOCATE(); SYS.DBMS_BACKUP_RESTORE.RESTORESETDATAFILE; SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(34,'/rlvdb1_db1_4_11.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(60,'/rlvdb1_db1_1_09.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(64,'/rlvdb1_db1_4_12.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(41,'/rlvdb1_db1_idx8.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(46,'/rlvdb1_db1_4_08.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(51,'/rlvdb1_xc_def03.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(08,'/rlvdb1_db1_1_03.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(12,'/rlvdb1_db1_2_02.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(16,'/rlvdb1_db1_3_01.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(20,'/rlvdb1_db1_3_05.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(24,'/rlvdb1_db1_4_04.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(28,'/rlvdb1_db1_idx3.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(33,'/rlvdb1_db1_3_07.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(40,'/rlvdb1_db1_idx7.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(55,'/rlvdb1_pub_def3.dbf'); SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO(32,'/rlvdb1_db1_2_06.dbf'); FOR I IN 1..V_MAXPIECES LOOP SYS.DBMS_BACKUP_RESTORE.RESTOREBACKUPPIECE(V_FILENAMETABLE(I),V_FINISH); IF V_FINISH THEN SYS.DBMS_BACKUP_RESTORE.DEVICEDEALLOCATE; RETURN; END IF; END LOOP; SYS.DBMS_BACKUP_RESTORE.DEVICEDEALLOCATE; END; /

# 9i使用catalog注册数据文件镜像或者使用alter database rename file '' to ''; catalog datafilecopy '/db1/rlvdb1_db1_1_05.dbf'; … catalog datafilecopy '/db1/rlvdb1_db1_3_07.dbf';

alter database rename file '/dev/rlvdb1_undotbs1'to '/db1/rlvdb1_undotbs1.dbf';

…

alter database rename file '/dev/rlvdb1_db1_4_12' to '/db1/rlvdb1_db1_4_12.dbf';

select 'alter database rename file '''||member||''' TO '''||'/'||member||'.log'||''';' from v$logfile; alter database rename file '/dev/rlvdb1_log11a' TO '/rlvdb1_log11a.log'; alter database rename file '/dev/rlvdb1_log12a' TO '/rlvdb1_log12a.log'; alter database rename file '/dev/rlvdb1_log13a' TO '/rlvdb1_log13a.log'; alter database rename file '/dev/rlvdb1_log14a' TO '/rlvdb1_log14a.log'; alter database rename file '/dev/rlvdb1_log21a' TO '/rlvdb1_log21a.log'; alter database rename file '/dev/rlvdb1_log22a' TO '/rlvdb1_log22a.log'; alter database rename file '/dev/rlvdb1_log23a' TO '/rlvdb1_log23a.log'; alter database rename file '/dev/rlvdb1_log24a' TO '/rlvdb1_log24a.log';

2.2.5 migrate打开数据库升级9.2.0.8

SQL> alter database open resetlogs migrate; Database altered. SQL> SPOOL patch.log SQL> @?/rdbms/admin/catpatch.sql SQL> SPOOL OFF

select 'create tablespace '||tablespace_name||' datafile '''||'/u01/app/oracle/oradata/HASWMID/db1'||file_name||''' size '||bytes/1024/1024||'M;' from dba_data_files order by tablespace_name;

create pluggable database db1 admin user db1 identified by oracle file_name_convert=('/testmid/pdbseed','/testmid/db1');

l 所有表空间都要创建，且大小要与源库至少相同

create tablespace TBS_PUBL_DEF datafile '/u01/app/oracle/oradata/HASWMID/db1/rlvdb1_pub_def1.dbf' size 1000M;

…

alter tablespace TBS_ZZSFP_IDX add datafile '/u01/app/oracle/oradata/HASWMID/db1/rlvdb1_db1_idx4.dbf' size 8000M;

l 9.2.0.8源库生成语句 select 'create user '||username||' identified by oracle default tablespace '||default_tablespace||' temporary tablespace '||temporary_tablespace||';' from dba_users order by temporary_tablespace;

l pdb创建临时表空间 create temporary tablespace TBS_ZZSFP_TMP tempfile '/u01/app/oracle/oradata/HASWMID/db1/TBS_ZZSFP_TMP01.dbf' size 32767M; create user test1 identified by oracle default tablespace test_tmp temporary tablespace TBS_ZZSFP_TMP; … create user test2 identified by oracle default tablespace SYSTEM temporary tablespace TEMP;

l 9.2.0.8生成赋权语句脚本

l 生成语句之后，目标库执行即可

l 建议对所有用户单独赋予unlimited tablespace权限，防止导入失败 for c in user1 user2 do ./sqlplus -S system/oracle >> sys_priv_grants.log <<EOF col PRIVILEGE for a30 col GRANTEE for a20 col ADMIN_OPT for a15 set lines 180 set pagesize 1000 set echo off set heading off set feedback off SELECT 'GRANT '||a.PRIVILEGE||' TO '||'${c}'||';' FROM ( SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = '${c}' UNION ALL SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE IN (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = '${c}' AND granted_role not in('EXP_FULL_DATABASE','IMP_FULL_DATABASE')) ) a; exit; EOF done

l 9.2.0.8 exp export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

nohup ./exp system/oracle file=/oradata/ora9/app/oradata/db1exp/db1_$(date +%Y%m%d_%H%M%S).dmp parfile=/oradata/ora9/app/oradata/db1exp/db1.par log=/oradata/ora9/app/oradata/db1exp/db1_exp_$(date +%Y%m%d_%H%M%S).log &

db1.par

COMPRESS=n

FEEDBACK=10000

DIRECT=y

STATISTICS=none

RECORDLENGTH=65536

FULL=y

l 19c pdb导入

nohup imp userid=system/oracle@db1 BUFFER=1073741824 FILE=/u01/expdmp/db1_20201126_104224.dmp IGNORE=y STATISTICS=NONE FEEDBACK=10000 FULL=y LOG=/u01/expdmp/IMP_db1_$(date +%Y%m%d_%H%M%S).log &

l 导入完成赋予对象权限

ü 9.2.0.8源库生成对象权限脚本 for c in user1 user2 do ./sqlplus -S system/oracle >> object_grants.log <<EOF set lines 180 set pagesize 1000 set echo off set heading off set feedback off col grantee for a15 col owner for a15 col table_name for a30 col grantor for a15 SELECT 'GRANT '||a.PRIVILEGE||' ON '||a.owner||'."'||a.table_name||'" TO '||'${c}'||';' FROM ( SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = '${c}' UNION ALL SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE IN (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = '${c}' and granted_role not in('EXP_FULL_DATABASE','IMP_FULL_DATABASE')) ) a; exit; EOF done

l 如果导入失败，批量删除用户脚本 for c in user1 user2 do ./sqlplus -S system/oracle >>drop_user.log <<EOF DROP USER ${c} cascade; exit; EOF done

l 目标库dbca字符集必须AL32UTF8

l 建议打开中间库19c归档模式，否则hot clone有机率由于最后refresh不在当前几个redo组导致hot clone失败

create user c##clone identified by oracle container=all; grant dba to c##clone container=all; grant create pluggable database to c##clone container=all;

grant create session,connect,resource,cdb_dba,sysoper to c##clone container=all;

SQL> create public database link clone_link connect to c##clone identified by oracle using 'HASWMID';

SQL> create pluggable database db1 from db1@clone_link file_name_convert=('/u01/app/oracle/oradata/source/db1','/u01/app/oracle/oradata/target/db1');

SQL> alter pluggable database db1 open;

SQL> alter system set job_queue_processes=0;

SQL> alter pluggable database db1 save state;

# 由于字符集不同，所以会报告警如下 SQL> select * from PDB_PLUG_IN_VIOLATIONS TIME NAM CAUSE TYPE ERROR_NUMBER LINE MESSAGE STATUS ACTION CON_ID -------------------- --- ---------- --------- ------------ ---------- ---------------------------------------- --------- ---------------------------------------- ---------- 27-NOV-20 10.09.16.8 db1 PDB not Un WARNING 0 1 Character set mismatch: PDB character se PENDING Oracle recommends using Unicode (AL32UTF 3 42492 AM icode t ZHS16GBK. CDB character set AL32UTF8. 8) character set for the database. Consi der migrating the database to Unicode.

l 9i源库 for c in user1 user2 do ./sqlplus -S system/oracle >> Users_object_count.log <<EOF set echo off set heading off set lines 180 set pagesize 1000 SELECT DISTINCT OBJECT_TYPE,COUNT(*) FROM dba_objects where owner='${c}' GROUP BY OBJECT_TYPE ORDER BY 1; exit; EOF done

l 19c目标库 for c in user1 user2 do sqlplus -S system/oracle@db1 >> Users_object_count.log <<EOF set echo off set heading off set lines 180 set pagesize 1000 SELECT DISTINCT OBJECT_TYPE,COUNT(*) FROM dba_objects where owner='${c}' GROUP BY OBJECT_TYPE ORDER BY 1; exit; EOF done

l 源端目标端均执行，然后可以将源库table_hash导入目标库通过语句校验。 # 创建存储表数据量 table_hash 表 sqlplus -S system/oracle <<EOF CREATE TABLE table_hash(owner varchar2(100),table_name varchar2(100),tc number,primary key(owner,table_name)); EXIT; EOF # 计算所有表数据量 for c in user1 user2 do sqlplus -S system/oracle@jh >> user_count.log <<EOF DECLARE v_cc number; CURSOR v_tbl IS SELECT owner,table_name FROM dba_tables WHERE OWNER='${c}'; BEGIN FOR c IN v_tbl LOOP EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||c.owner||'.'||'"'||c.table_name||'"' INTO v_cc; INSERT INTO TABLE_HASH VALUES(c.owner,c.table_name,v_cc); END LOOP; COMMIT; END; / EXIT; EOF done

l 校验 COL OWNER FOR A20 COL TABLE_NAME FOR A40 SET LINES 200 PAGES 2000 SELECT DEST.OWNER,DEST.TABLE_NAME,DEST.TC,SOURCE.TC FROM TABLE_HASH DEST, SOURCE.TABLE_HASH SOURCE WHERE DEST.OWNER = SOURCE.OWNER AND DEST.TABLE_NAME = SOURCE.TABLE_NAME AND DEST.TC != SOURCE.TC; SELECT * FROM SOURCE.TABLE_HASH SOURCE MINUS SELECT * FROM TABLE_HASH DEST;

l Linux 11.2.0.4恢复备份集

l dbua升级至19c non-cdb

l 转换non-cdb至19c cdb中间库

l 目标库19c hot clone完成保留字符集迁移

*.audit_file_dest='/u01/app/oracle/admin/db2/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/u01/app/oracle/product/11.2.0/dbhome_1/oradata/db2/control01.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='db2' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=dzfpXDB)' *.memory_target=8G *.open_cursors=300 *.processes=2000 *.remote_login_passwordfile='exclusive' *.sessions=610 *.undo_tablespace='UNDOTBS1' *.job_queue_processes=0 SQL> startup nomount pfile='/tmp/11.txt'; ORACLE instance started. RMAN> restore controlfile from '/u01/db2rman_final/c-c-2343833572-20191025-00'; RMAN> alter database mount; RMAN> crosscheck backup; RMAN> delete noprompt expired backup; RMAN> crosscheck archivelog all; RMAN> delete noprompt expired archivelog all; RMAN> catalog start with '/u01/db2rman_final';

RMAN> run{ allocate channel c1 type disk; allocate channel c2 type disk; allocate channel c3 type disk; allocate channel c4 type disk; allocate channel c5 type disk; allocate channel c6 type disk;

set newname for datafile 1 to '/u01/app/oracle/product/11.2.0/dbhome_1/oradata/db2/system.dbf';

…

set newname for datafile 116 to '/u01/app/oracle/product/11.2.0/dbhome_1/oradata/db2/lvsksk_i_js_jsp_02.dbf'; restore database; switch datafile all; release channel c1; release channel c2; release channel c3; release channel c4; release channel c5; release channel c6; }

select 'alter database rename file '''||member||''' TO ''/u01/app/oracle/product/11.2.0/dbhome_1/oradata/db2'||'/'||member||'.log'||''';' from v$logfile; alter database rename file '+DATA/db2/onlinelog/group_2.275.918765543' TO '/u01/app/oracle/product/11.2.0/dbhome_1/oradata/db2/group_2.log'; … alter database rename file '+DATA/db2/onlinelog/group_4.302.918765673' TO '/u01/app/oracle/product/11.2.0/dbhome_1/oradata/db2/group_4.log';

3.2.4 打开数据库添加temp文件

SQL> alter database open resetlogs;

SQL> alter tablespace temp add tempfile '/u01/app/oracle/product/11.2.0/dbhome_1/oradata/db2/temp01.dbf' size 32767m;

SQL> alter tablespace temp01 add tempfile '/u01/app/oracle/product/11.2.0/dbhome_1/oradata/db2/temp01_01.dbf' size 32767m;

SQL> alter tablespace temp drop tempfile '+DATA/db2/tempfile/temp.305.918765545';

SQL> alter tablespace temp01 drop tempfile '+DATA/db2/datafile/temp01.dbf';

l 空间允许尽量设置大，否则可能会由于闪回区空间不足导致dbua升级失败

SQL> alter system set db_recovery_file_dest_size=200g;

SQL> alter system set db_recovery_file_dest='/u01/app/oracle/product/11.2.0/dbhome_1/oradata/db2/fra';

# dbua 会有使用 19c 预升级脚本进行检查 11.2.0.4 库，不符合条件或者警告，需要进行配置修改，然后继续运行 dbua $ dbua

l non-cdb需要read only： SQL> startup read only; BEGIN DBMS_PDB.DESCRIBE( pdb_descr_file => '/u01/db2ncdb.xml' ); END; /

l 19c中间库 cdb检查是否可以plug in SET SERVEROUTPUT ON DECLARE compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY( pdb_descr_file => '/u01/db2ncdb.xml', pdb_name => 'DB2') WHEN TRUE THEN 'YES' ELSE 'NO' END; BEGIN DBMS_OUTPUT.PUT_LINE(compatible); END; /

l 如果输出结果为NO，查看下面视图解决对应问题 SELECT * FROM PDB_PLUG_IN_VIOLATIONS; # 由于 Aplex 无法升级，所以在升级 19c 之前需要移除 Apex 组件

l 使用11g apxremov.sql移除apex组件，重新执行上面命令 SQL> @apxremov.sql

l 可以转换之后，19c中间库进行non-cdb插入cdb操作 CREATE PLUGGABLE DATABASE db2 USING '/u01/db2ncdb.xml' COPY FILE_NAME_CONVERT = ('/u01/app/oracle/product/11.2.0/dbhome_1/oradata/db2','/u01/app/oracle/oradata/test/db2');

l 执行noncdb_to_pdb.sql脚本 SQL> alter session set container=db2; SQL> @?/rdbms/admin/noncdb_to_pdb.sql

l 第一次必须以read write 打开 SQL> alter pluggable database db2 open;

l 报错如下 *** 2020-12-01T17:26:12.013790+08:00 (CDB$ROOT(1)) Unexpected error 4088 in job slave process <error barrier> at 0x7ffe839acec0 placed kkj.c@1175 ORA-04088: error during execution of trigger 'SYS.LOGOFF_AUDIT_TRIGGER' ORA-00604: error occurred at recursive SQL level 1 ORA-01427: single-row subquery returns more than one row ORA-06512: at line 5 ORA-12012: error on auto execute of job "SYS"."CLEANUP_NON_EXIST_OBJ" ORA-04088: error during execution of trigger 'SYS.LOGOFF_AUDIT_TRIGGER' ORA-00604: error occurred at recursive SQL level 1 ORA-01427: single-row subquery returns more than one row ORA-06512: at line 5 ORA-06512: at line 5 2020-12-02T07:44:17.085945+08:00 Errors in file /u01/app/oracle/diag/rdbms/test/test/trace/test_j008_19026.trc: ORA-04088: error during execution of trigger 'SYS.LOGOFF_AUDIT_TRIGGER' ORA-00604: error occurred at recursive SQL level 1 ORA-01427: single-row subquery returns more than one row ORA-06512: at line 5 ORA-12012: error on auto execute of job "SYS"."CLEANUP_NON_EXIST_OBJ" ORA-04088: error during execution of trigger 'SYS.LOGOFF_AUDIT_TRIGGER' ORA-00604: error occurred at recursive SQL level 1 ORA-01427: single-row subquery returns more than one row ORA-06512: at line 5 2020-12-02T07:44:17.087638+08:00 Errors in file /u01/app/oracle/diag/rdbms/test/test/trace/test_j006_19022.trc: ORA-04088: error during execution of trigger 'SYS.LOGOFF_AUDIT_TRIGGER' ORA-00604: error occurred at recursive SQL level 1 ORA-01427: single-row subquery returns more than one row ORA-06512: at line 5 ORA-12012: error on auto execute of job "SYS"."PURGE_LOG" ORA-04088: error during execution of trigger 'SYS.LOGOFF_AUDIT_TRIGGER' ORA-00604: error occurred at recursive SQL level 1 ORA-01427: single-row subquery returns more than one row ORA-06512: at line 5 2020-12-02T07:47:24.277315+08:00

l 解决

根据相关trace文件，以及打开10046追踪open pdb过程，发现报错为trigger插入相关stat$基表违反唯一约束导致，truncate相关统计信息基表，再次测试重启pdb，报错不再发生。

create pluggable database db2 from db2@clone_link file_name_convert=('/u01/app/oracle/oradata/test/db2','/u01/app/oracle/oradata/TEST/db2');

*.audit_file_dest='/u01/app/oracle/admin/db3/adump' *.audit_trail='db' *.control_files='/u01/app/oracle/product/11.2.0/dbhome_1/oradata/db3/control01.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='db3' *.compatible='11.2.0.4.0' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=dzfpXDB)' *.memory_target=8G *.open_cursors=300 *.processes=2000 *.remote_login_passwordfile='exclusive' *.sessions=610 *.undo_tablespace='UNDOTBS1' *.job_queue_processes=0 SQL> startup nomount pfile='/tmp/11.txt'; SQL> create spfile from pfile='/tmp/11.txt'; RMAN> restore controlfile from '/u01/db3rman_final/db3_09uf6rdp_1_1';

RMAN> alter database mount; RMAN> catalog start with '/u01/db3rman_final'; run{ allocate channel c1 type disk; allocate channel c2 type disk; allocate channel c3 type disk; allocate channel c4 type disk; allocate channel c5 type disk; allocate channel c6 type disk;

set newname for datafile 1 to '/u01/app/oracle/product/11.2.0/dbhome_1/oradata/db3/system01.dbf';

…

set newname for datafile 45 to '/u01/app/oracle/product/11.2.0/dbhome_1/oradata/db3/indx11.dbf'; restore database; switch datafile all; release channel c1; release channel c2; release channel c3; release channel c4; release channel c5; release channel c6; }

select 'alter database rename file '''||member||''' TO ''/u01/app/oracle/product/11.2.0/dbhome_1/oradata/db3/'||'/'||member||'.log'||''';' from v$logfile; alter database rename file '/u01/app/oracle/oradata/db3/redo07.log' TO '/u01/app/oracle/product/11.2.0/dbhome_1/oradata/db3/redo07.log.log'; … alter database rename file '/u01/app/oracle/oradata/db3/redo02.log' TO '/u01/app/oracle/product/11.2.0/dbhome_1/oradata/db3/redo02.log.log'; SQL> alter database open resetlogs;

nohup ./expdp system/oracle dumpfile=expdp_db3_%U.dmp directory=expdp parallel=8 full=y exclude=statistics &

SQL> create pluggable database db3 admin user db3 identified by oracle file_name_convert=('/u01/app/oracle/oradata/test/pdbseed','/u01/app/oracle/oradata/test/db3');

l 11.2.0.4源库生成语句并修改

select 'create tablespace '||t.name||' datafile '''||'/u01/app/oracle/product/11.2.0/dbhome_1/oradata/db3/'||t.name||d.file#||'.dbf'||''' size '||d.bytes/1024/1024||'M;' from v$datafile d,v$tablespace t where d.ts# = t.ts# order by t.name;

l 中间库pdb创建表空间

create tablespace testdatafile '/u01/app/oracle/product/11.2.0/dbhome_1/oradata/db3/HEAD7.dbf' size 32766M;

alter tablespace test_IND add datafile '/u01/app/oracle/product/11.2.0/dbhome_1/oradata/db3/WSBS_IND21.dbf' size 20480M;

l 修改表空间均修改为自动扩展 select 'ALTER DATABASE DATAFILE '''||file_name||''' AUTOEXTEND ON;' from dba_data_files where tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS1','USERS') AND AUTOEXTENSIBLE='NO';

nohup impdp system/oracle dumpfile=expdp_db3_%U.dmp directory=expdmp parallel=8 &

SQL> create pluggable database db3 from db3@clone_link file_name_convert=('/u01/app/oracle/oradata/HASWMID/db3','/u01/app/oracle/oradata/TEST/db3','/u01/app/oracle/product/11.2.0/dbhome_1/oradata/db3','/u01/app/oracle/oradata/TEST/db3');