Oracle+9i+11g歷史庫升級遷移數據至19c+CDB

1、目錄Oracle 9i/11g歷史庫升級遷移數據至19c CDB21 背景介紹21.1 需求21.2 方案選擇21.2.1 對于 Aix到Linux，21.2.2 對于11204升級至19c21.2.3 對于保留字符集說明31.3 環(huán)境說明32 AIX 升級遷移Linux 7.6 19c32.1 恢復步驟概述42.2 Aix中間庫恢復備份集42.2.1 編寫pfile建立相關目錄啟動實例42.2.2 傳輸備份集，使用dbms_backup_restore恢復52.2.3 切換控制文件記錄數據文件位置102.2.4 修改redo聯(lián)機日志位置102.2.5 migrat

2、e打開數據庫升級102.3 exp/imp遷移至19c中間庫112.3.1 庫生成批量創(chuàng)建表空間語句112.3.2 19c中間庫創(chuàng)建同名pdb并創(chuàng)建表空間112.3.3 19c中間庫pdb創(chuàng)建用戶112.3.4 用戶賦系統(tǒng)權限122.3.5 exp/imp遷移數據至中間庫122.4 19c目標庫克隆中間庫142.4.1 19c中間庫配置以便熱克隆142.4.2 目標庫19c創(chuàng)建中間庫19c db link142.4.3 熱克隆142.5 數據比對152.5.1 對象數量校驗152.5.2 數據行數比對163 Linux dbua升級并保留字符集遷移

3、19c173.1 db2恢復步驟概述173.2 利用備份集恢復173.2.1 啟動實例恢復控制文件173.2.2 恢復數據文件183.2.3 重命名redo聯(lián)機日志193.2.4 打開數據庫添加temp文件193.3 dbua升級至non-cdb 19c203.3.1 修改閃回區(qū)203.3.2 19c dbua203.3.3 non-cdb轉換pdb203.3.4 open報錯解決213.4 19c目標庫熱克隆224 Linux expdp遷移升級至19c234.1 利用備份集恢復234.1.1 啟動實例恢復控制文件234.1.2 恢復數據文件234.1.3 重命名redo聯(lián)

4、機日志244.2 源庫expdp導出244.3 中間庫創(chuàng)建pdb244.4 中間庫庫創(chuàng)建表空間254.5 中間庫impdp導入254.6 目標庫熱克隆保留字符集25Oracle 9i/11g歷史庫升級遷移數據至19c CDB1 背景介紹1.1 需求l 利用歷史庫備份集遷移數據至19c多租戶環(huán)境，歸檔歷史數據l 涉及、、，Aix、Linux環(huán)境數套數據庫l 升級至19c保留源庫字符集，涉及ZHS16GBK、US7ASCII兩種字符集l 由于從備份集恢復，無停機時間要求，所以下面方案均不考慮停機時間1.2 方案選擇1.2.1 對于 Aix到

5、Linux，（1）需要利用Aix中間環(huán)境恢復。（2）數據遷移可以使用先升級至，然后升級至，然后dbua升級至19c中間庫，最后通過hot clone保留字符集到最終19c庫。（3）或者使用dbca建立19c與源庫相同字符集中間庫，hot clone至19c AL32UTF8字符集的最終庫，直接使用exp/imp升級至19c，保留原字符集。1.2.2 對于11204升級至19c（1）Aix平臺利用中間環(huán)境恢復，然后使用dbua或者expdp/impdp升級至19c中間庫，通過hot clone保留字符集。（2）Linux平臺源庫，可以直接在19c Linux中間庫環(huán)

6、境恢復，dbua就地升級至19c，然后通過non-cdb轉換方式轉換成cdb，最后hot-clone方式克隆到最終19c數據庫。1.2.3 對于保留字符集說明Oracle 12.2版本以后可以PDB與CDB字符集不同，但是在CREATE PLUGGABLE DATABASE語句中并無指定字符集相關語法，需要通過hot-clone方式實現與cdb不同字符集。1.3 環(huán)境說明l Aix中間機器一臺：Aix 7.1 預裝、兩個版本軟件。l CentOS 7.6機器兩臺ü A機器作為19c中間庫，安裝/19.3 Oracle軟件。ü B

7、作為19c最終庫，安裝19.3數據庫軟件。l 下面敏感字段均做過處理。l 文檔會針對三種場景進行介紹。2 AIX 升級遷移Linux 7.6 19cl 源庫：Aix l Aix中間庫: Aix l Linux中間庫：CentOS 7.6 19.3l Linux目標庫：CentOS 7.6 19.32.1 恢復步驟概述l Aix使用dbms_backup_restore恢復備份集l exp導出l 19c中間庫導入l 19c目標庫hot clone保留源庫字符集遷移2.2 Aix中間庫恢復備份集2.2.1 編寫pfile建立相關目錄啟動實例*.backgr

8、ound_dump_dest='/oradata/ora9/app/oracle/admin/db1/bdump'*.compatible='.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=''

9、*.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_s

10、ize='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=536870912db11.thread=1db12.thread=2*.timed_statistics=TRUE*.undo_management='AUTO'*.undo_tab

11、lespace='UNDO1'*.user_dump_dest='/oradata/ora9/app/oracle/admin/db1/udump'mkdir -p /oradata/ora9/app/oracle/admin/db1/bdumpmkdir -p /oradata/ora9/app/oracle/admin/db1/udumpmkdir -p /oradata/ora9/app/oracle/admin/db1/cdumpsqlplus /nologconn / as sysbastartup nomount pfile='/tmp/pf

12、ile.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 下面?zhèn)浞菁Q與下面datafile集合要對應，可以從rman備份日志

13、中查看，如果沒有日志，則只能進行組合嘗試。l 也可以一次恢復多個備份集，但是備份集順序與下面數據文件列表集合順序要對應，否則會報無法找到某個數據文件備份。DECLAREV_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;BEGINV_FILENAMETABLE(1) := '/oradata/ora9/db1

14、rman_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.DB

15、MS_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'

16、);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.

17、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_d

18、b1_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 LOOPSYS.DBMS_BACKUP_RESTORE.RESTOREBACKUPPIECE(V_FILENAMETABLE(I),V_FINISH);IF V_FINISH THENSYS.DBMS_BACK

19、UP_RESTORE.DEVICEDEALLOCATE;RETURN;END IF;END LOOP;SYS.DBMS_BACKUP_RESTORE.DEVICEDEALLOCATE;END;/DECLAREV_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;BEGINV_FILENAMETABLE(1) := &#

20、39;/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

21、_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,'/rl

22、vdb1_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,

23、'/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.RESTOREDATAFI

24、LETO(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 LOOPSYS.DBMS_BACKUP_RESTORE.RESTOREBACKUPPIECE(V_FILENAMETABLE(I),V_FINISH);IF V_F

25、INISH THENSYS.DBMS_BACKUP_RESTORE.DEVICEDEALLOCATE;RETURN;END IF;END LOOP;SYS.DBMS_BACKUP_RESTORE.DEVICEDEALLOCATE;END;/DECLAREV_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;BEGINV

26、_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.RESTOREDATAFILET

27、O(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.RESTORED

28、ATAFILETO(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.

29、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_

30、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 LOOPSYS.DBMS_BACKUP_RESTORE.RESTOREBACKUPPIECE(V_FILENAMETABL

31、E(I),V_FINISH);IF V_FINISH THENSYS.DBMS_BACKUP_RESTORE.DEVICEDEALLOCATE;RETURN;END IF;END LOOP;SYS.DBMS_BACKUP_RESTORE.DEVICEDEALLOCATE;END;/DECLAREV_DEVICE VARCHAR2(100);V_FINISH BOOLEAN;TYPE T_FILENAMETABLE IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;V_FILENAMETABLE T_FILENAMETABLE;V_MAXPIE

32、CES NUMBER :=4;BEGINV_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_REST

33、ORE.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_BAC

34、KUP_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.

35、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

36、9;);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 LOOPSYS.DBMS_BACKUP_RESTORE.RESTOREBACKUPP

37、IECE(V_FILENAMETABLE(I),V_FINISH);IF V_FINISH THENSYS.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_

38、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聯(lián)機日志位置select 'alter database

39、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_l

40、og12a.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 fil

41、e '/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打開數據庫升級SQL> alter database open resetlogs

42、 migrate;Database altered.SQL> SPOOL patch.logSQL> ?/rdbms/admin/catpatch.sqlSQL> SPOOL OFF2.3 exp/imp遷移至19c中間庫2.3.1 庫生成批量創(chuàng)建表空間語句select 'create tablespace '|tablespace_name|' datafile '''|'/u01/app/oracle/oradata/HASWMID/db1'|file_name|'''

43、size '|bytes/1024/1024|'M;' from dba_data_files order by tablespace_name;2.3.2 19c中間庫創(chuàng)建同名pdb并創(chuàng)建表空間create pluggable database db1 admin user db1 identified by oracle file_name_convert=('/testmid/pdbseed','/testmid/db1');l 所有表空間都要創(chuàng)建，且大小要與源庫至少相同create tablespace TBS_PUBL_DEF

44、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創(chuàng)建用戶l 源庫生成語句select 'create user '|username|' identified by

45、oracle default tablespace '|default_tablespace|' temporary tablespace '|temporary_tablespace|'' from dba_users order by temporary_tablespace;l pdb創(chuàng)建臨時表空間create temporary tablespace TBS_ZZSFP_TMP tempfile '/u01/app/oracle/oradata/HASWMID/db1/TBS_ZZSFP_TMP01.dbf' size 32767

46、M;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 用戶賦系統(tǒng)權限l 生成賦權語句腳本l 生成語句之后，目標庫執(zhí)行即可l 建議對所有用戶單獨賦予unlimited tablespace權限，防止導入失敗for c in user1 us

47、er2do./sqlplus -S system/oracle >> sys_priv_grants.log <<EOFcol PRIVILEGE for a30col GRANTEE for a20col ADMIN_OPT for a15set lines 180set pagesize 1000set echo offset heading offset feedback offSELECT 'GRANT '|a.PRIVILEGE|' TO '|'$c'|''FROM(SELECT *FROM DB

48、A_SYS_PRIVSWHERE GRANTEE = '$c'UNION ALLSELECT *FROM DBA_SYS_PRIVSWHERE 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;EOFdone2.3.5 exp/imp遷移數據至中間庫l expexport NLS

49、_LANG=AMERICAN_AMERICA.ZHS16GBKnohup ./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.parCOMPRESS=nFEEDBACK=10000DIRECT=ySTATISTIC

50、S=noneRECORDLENGTH=65536FULL=yl 19c pdb導入nohup imp userid=system/oracledb1 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 導入完成賦予對象權限ü 源庫生成對象權限腳本for c in user1 user2do./sql

51、plus -S system/oracle >> object_grants.log <<EOFset lines 180set pagesize 1000set echo offset heading offset feedback offcol grantee for a15col owner for a15col table_name for a30col grantor for a15SELECT 'GRANT '|a.PRIVILEGE|' ON '|a.owner|'."'|a.table_name|

52、'" TO '|'$c'|''FROM(SELECT *FROM DBA_TAB_PRIVSWHERE GRANTEE = '$c'UNION ALLSELECT *FROM DBA_TAB_PRIVSWHERE GRANTEE IN (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = '$c' and granted_role not in('EXP_FULL_DATABASE','IMP_FULL_DATABASE&

53、#39;) a;exit;EOFdonel 如果導入失敗，批量刪除用戶腳本for c in user1 user2do./sqlplus -S system/oracle >>drop_user.log <<EOFDROP USER $c cascade;exit;EOFdone2.4 19c目標庫克隆中間庫l 目標庫dbca字符集必須AL32UTF8l 建議打開中間庫19c歸檔模式，否則hot clone有機率由于最后refresh不在當前幾個redo組導致hot clone失敗2.4.1 19c中間庫配置以便熱克隆create user c#clone identi

54、fied 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創(chuàng)建中間庫19c db linkSQL> create public database link clone_link connect to c#clone identi

55、fied by oracle using 'HASWMID'2.4.3 熱克隆SQL> create pluggable database db1 from db1clone_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

56、=0;SQL> alter pluggable database db1 save state;# 由于字符集不同，所以會報告警如下SQL> select * from PDB_PLUG_IN_VIOLATIONSTIME 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 Oracl

57、e recommends using Unicode (AL32UTF 342492 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 user2do./sqlplus -S system/oracle >> Users_object_count.log <<EOFset echo of

58、fset heading offset lines 180set pagesize 1000SELECT DISTINCT OBJECT_TYPE,COUNT(*) FROM dba_objects where owner='$c' GROUP BY OBJECT_TYPE ORDER BY 1;exit;EOFdonel 19c目標庫for c in user1 user2dosqlplus -S system/oracledb1 >> Users_object_count.log <<EOFset echo offset heading offset lines 180set pagesize 1000SELECT DISTINCT OBJECT_TYPE,COUNT(*) FROM dba_objects where owner='$c' GROUP BY OBJECT_TYPE ORDER BY 1;exit;EOFdone2.5.2 數據行數比對l 源端目標端均執(zhí)行，然后可以將源庫table_hash導入目標庫通過語句校驗。# 創(chuàng)建存儲表數據量table_