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

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

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

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

1.2 方案选择

1.2.1 对于9.2.0.7 Aix到Linux，

（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.2.2 对于11204升级至19c

（1）Aix平台利用中间环境恢复，然后使用dbua或者expdp/impdp升级至19c中间库，通过hot clone保留字符集。

（2）Linux平台源库，可以直接在19c Linux中间库环境恢复，dbua就地升级至19c，然后通过non-cdb转换方式转换成cdb，最后hot-clone方式克隆到最终19c数据库。

1.2.3 对于保留字符集说明

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

1.3 环境说明

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 文档会针对三种场景进行介绍。

2 AIX 9.2.0.7升级迁移Linux 7.6 19c

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

2.1 恢复步骤概述

l Aix使用dbms_backup_restore恢复备份集

l exp导出

l 19c中间库导入

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

2.2 Aix中间库恢复备份集

2.2.1 编写pfile建立相关目录启动实例

*.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;

/

2.2.3 切换控制文件记录数据文件位置

# 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';

2.2.4 修改redo联机日志位置

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

2.3 exp/imp迁移至19c中间库

2.3.1 9.2.0.8库生成批量创建表空间语句

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;

2.3.2 19c中间库创建同名pdb并创建表空间

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;

2.3.3 19c中间库pdb创建用户

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;

2.3.4 用户赋系统权限

l 9.2.0.8生成赋权语句脚本

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

l 建议对所有用户单独赋予unlimited tablespace权限，防止导入失败

for c in user1 user2

do

./sqlplus -S system/oracle >> sys_priv_grants.log <

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

2.3.5 exp/imp迁移数据至中间库

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 <

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 <

DROP USER ${c} cascade;

exit;

EOF

done

2.4 19c目标库克隆中间库

l 目标库dbca字符集必须AL32UTF8

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

2.4.1 19c中间库配置以便热克隆

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;

2.4.2 目标库19c创建中间库19c db link

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

2.4.3 热克隆

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.

2.5 数据比对

2.5.1 对象数量校验

l 9i源库

for c in user1 user2

do

./sqlplus -S system/oracle >> Users_object_count.log <

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 <

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

2.5.2 数据行数比对

l 源端目标端均执行，然后可以将源库table_hash导入目标库通过语句校验。

# 创建存储表数据量 table_hash 表

sqlplus -S system/oracle <

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 <

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;

3 Linux 11.2.0.4 dbua升级并保留字符集迁移19c

3.1 db2恢复步骤概述

l Linux 11.2.0.4恢复备份集

l dbua升级至19c non-cdb

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

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

3.2 利用备份集恢复

3.2.1 启动实例恢复控制文件

*.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';

3.2.2 恢复数据文件

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;

}

3.2.3 重命名redo联机日志

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';

3.3 dbua升级至non-cdb 19c

3.3.1 修改闪回区

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';

3.3.2 19c dbua

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

$ dbua

3.3.3 non-cdb转换pdb

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;

3.3.4 open报错解决

l 报错如下

*** 2020-12-01T17:26:12.013790+08:00 (CDB$ROOT(1))

Unexpected error 4088 in job slave process

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

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，报错不再发生。

3.4 19c目标库热克隆

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

4 Linux 11.2.0.4 expdp迁移升级至19c

4.1 利用备份集恢复

4.1.1 启动实例恢复控制文件

*.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';

4.1.2 恢复数据文件

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;

}

4.1.3 重命名redo联机日志

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;

4.2 源库expdp导出

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

4.3 中间库创建pdb

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');

4.4 中间库库创建表空间

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';

4.5 中间库impdp导入

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

4.6 目标库热克隆保留字符集

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');

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