Oracle 9i 11g歷史庫升級遷移資料至19c CDB
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;
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';
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';
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');
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31439444/viewspace-2745252/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【資料庫升級】Oracle指令碼升級12c CDB to 19c CDB資料庫Oracle指令碼
- 【Oracle升級】Oracle指令碼升級11g to 19c non-CDBOracle指令碼
- Oracle 9i升級19C 邏輯遷移詳細方法（一）Oracle
- Oracle 9i升級19C 邏輯遷移詳細方法（二）Oracle
- Oracle 9i升級19C 遷移關於失效索引的梳理方法Oracle索引
- 資料庫的升級及遷移資料庫
- Oracle資料庫升級或資料遷移的方法探討Oracle資料庫
- 手工升級9i資料庫到11g資料庫
- Oracle 19c adg全庫遷移資料Oracle
- SQL 遷移資料庫至ORACLE簡易方法SQL資料庫Oracle
- 寫有效的歷史資料遷移sqlSQL
- 儲存所有歷史提交資料下遷移git倉庫Git
- iOS CoreData (二) 版本升級和資料庫遷移iOS資料庫
- 動手為王 | Oracle 資料庫跨版本升級遷移實踐Oracle資料庫
- ZT 寫有效的歷史資料遷移sqlSQL
- oracle 9i升級到oracle9208資料庫Oracle資料庫
- ORACLE資料庫遷移Oracle資料庫
- Oracle 9i/10g/11g資料庫升級路線圖總覽Oracle資料庫
- 資料庫的建立、遷移、升級和流等方面資料庫
- 11g資料庫遷移ASM資料庫ASM
- 資料庫-oracle-資料庫遷移資料庫Oracle
- 【RMAN】Oracle11g使用rman遷移升級資料庫（win_to_linux）Oracle資料庫Linux
- Grafana的版本升級和資料遷移Grafana
- Oracle資料庫資料遷移流程Oracle資料庫
- Oracle 資料庫升級Oracle資料庫
- SQLSERVER遷移資料至ORACLE軟體下載SQLServerOracle
- Oracle 19c - 手動升級 Oracle 12.x, 18c CDB 到 Oracle 19c (19.x)Oracle
- oracle 19c pdb遷移Oracle
- Android 資料庫綜述（一） 資料庫片的升級與資料的遷移操作Android資料庫
- oracle 遷移資料庫到asmOracle資料庫ASM
- iOS 資料庫升級資料遷移解決方案iOS資料庫
- Oracle舊版本透過資料遷移方式升級到新版本Oracle
- Win環境至Linux環境Oracle資料庫遷移全過程LinuxOracle資料庫
- SAP系統升級，如何做資料遷移？
- ERP升級：如何做好資料遷移(轉)
- Oracle資料庫（資料泵）遷移方案（上）Oracle資料庫
- Oracle資料庫（資料泵）遷移方案（下）Oracle資料庫
- cassandra百億級資料庫遷移實踐資料庫