Oracle 12c R1 Plug/Unplug 遷移PDB實驗（1）：插拔 RAC PDB到 Non-RAC PDB

實驗目标：

将RAC環境的PDB插拔後遷移到非RAC環境

實驗環境：

資料庫：Oracle 12c R1
作業系統：Oracle Linux 7.3

實驗步驟：

1、源端環境準備

為本次實驗克隆一個PDB

克隆步驟可參考https://blog.csdn.net/ogdkevin/article/details/83107092

2、源端拔出PDB[問題1]

在CDB中操作，關閉PDB後，拔出PDB生成XML檔案

SQL>  alter pluggable database pdb2 close immediate;

Pluggable database altered.

SQL> alter pluggable database pdb2 unplug into '/home/oracle/pdb2.xml'; 

Pluggable database altered.

SQL> drop pluggable database pdb2 keep datafiles;

Pluggable database dropped.

3、目标端建立PDB的datafile目錄

#oracle使用者運作
[[email protected] ~]$ mkdir /u01/app/oracle/oradata/pdb2

4、傳輸源端datafile和xml檔案到目标端

源端datafile位置可以檢視unplug生成的xml檔案

5、驗證待插入PDB的相容性

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    v_result BOOLEAN;
  3  BEGIN
  4    v_result := DBMS_PDB.check_plug_compatibility(
  5                  pdb_descr_file => '/home/oracle/pdb2.xml',
  6                  pdb_name       => 'PDB2');
  7   
  8    IF v_result THEN
  9      DBMS_OUTPUT.PUT_LINE('compatible');
 10    ELSE
 11      DBMS_OUTPUT.PUT_LINE('incompatible');
 12    END IF;
 13  END;
 14  /
compatible

PL/SQL procedure successfully completed.

6、插入PDB[問題2]

--PDB名稱可以和原來的不同
--如果datafile都在同一個目錄，隻需要指定目錄
SQL> create pluggable database pdb2 as clone  using '/home/oracle/pdb2.xml'    
  2   source_file_directory = '/u01/app/oracle/oradata/pdb2'
  3  file_name_convert=NONE  NOCOPY tempfile reuse;

Pluggable database created.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           MOUNTED

7、打開PDB并檢查插入的PDB是否有問題，檢視PDB_PLUG_IN_VIOLATIONS表[問題3]

--檢視DB的trace log也能看到相關提示

***************************************************************
WARNING: Pluggable Database PDB2 with pdb id - 4 is
         altered with errors or warnings. Please look into
         PDB_PLUG_IN_VIOLATIONS view for more details.
***************************************************************

SQL> set lin 200
SQL> col name for a10 
SQL> col cause for a20 
SQL> col message for a100 
SQL> select name,cause,type,message,status from pdb_plug_in_violations order by name; 

NAME       CAUSE                TYPE      MESSAGE                                                                                              STATUS
---------- -------------------- --------- ---------------------------------------------------------------------------------------------------- ---------
PDB2       OPTION               WARNING   Database option RAC mismatch: PDB installed version 12.1.0.2.0. CDB installed version NULL.          PENDING
PDB2       Parameter            WARNING   CDB parameter sga_max_size mismatch: Previous 2G Current 1584M                                       RESOLVED
PDB2       Parameter            WARNING   CDB parameter memory_target mismatch: Previous 2G Current 1584M                                      RESOLVED
PDB2       Parameter            WARNING   CDB parameter memory_max_target mismatch: Previous 2G Current 1584M                                  RESOLVED
PDB2       Parameter            WARNING   CDB parameter cluster_database mismatch: Previous TRUE Current FALSE                                 RESOLVED
PDB2       Oracle Opatch        WARNING   Oracle opatch mismatch: opatch 19769480 is missing in the CDB.                                       RESOLVED
PDB2       Oracle Opatch        WARNING   Oracle opatch mismatch: opatch 20299023 is missing in the CDB.                                       RESOLVED
PDB2       Oracle Opatch        WARNING   Oracle opatch mismatch: opatch 20831110 is missing in the CDB.                                       RESOLVED
PDB2       Oracle Opatch        WARNING   Oracle opatch mismatch: opatch 21359755 is missing in the CDB.                                       RESOLVED
PDB2       Oracle Opatch        WARNING   Oracle opatch mismatch: opatch 21948354 is missing in the CDB.                                       RESOLVED
PDB2       Oracle Opatch        WARNING   Oracle opatch mismatch: opatch 22291127 is missing in the CDB.                                       RESOLVED

NAME       CAUSE                TYPE      MESSAGE                                                                                              STATUS
---------- -------------------- --------- ---------------------------------------------------------------------------------------------------- ---------
PDB2       Oracle Opatch        WARNING   Oracle opatch mismatch: opatch 23054246 is missing in the CDB.                                       RESOLVED
PDB2       Oracle Opatch        WARNING   Oracle opatch mismatch: opatch 24006101 is missing in the CDB.                                       RESOLVED
PDB2       Oracle Opatch        WARNING   Oracle opatch mismatch: opatch 24732082 is missing in the CDB.                                       RESOLVED
PDB2       Oracle Opatch        WARNING   Oracle opatch mismatch: opatch 25171037 is missing in the CDB.                                       RESOLVED
PDB2       Oracle Opatch        WARNING   Oracle opatch mismatch: opatch 25755742 is missing in the CDB.                                       RESOLVED
PDB2       Oracle Opatch        WARNING   Oracle opatch mismatch: opatch 26609783 is missing in the CDB.                                       RESOLVED
PDB2       Oracle Opatch        WARNING   Oracle opatch mismatch: opatch 26713565 is missing in the CDB.                                       RESOLVED
PDB2       Oracle Opatch        WARNING   Oracle opatch mismatch: opatch 26925311 is missing in the CDB.                                       RESOLVED
PDB2       Oracle Opatch        WARNING   Oracle opatch mismatch: opatch 27338020 is missing in the CDB.                                       RESOLVED
PDB2       Oracle Opatch        WARNING   Oracle opatch mismatch: opatch 27338041 is missing in the CDB.                                       RESOLVED

21 rows selected.

STATUS狀态RESOLVED的不用處理，隻要看狀态為PENDING，這裡隻有一條為OPTIN的問題。

因為是從RAC環境插拔過來的，目标環境不是RAC，注意隻需要把這個RAC選項關掉。

8、切換到PDB2，關掉RAC選項

SQL> alter session set container=pdb2;

Session altered.

SQL> exec dbms_registry.OPTION_OFF('RAC'); 

PL/SQL procedure successfully completed.

9、重新關閉和啟動，再檢查是否還有問題

SQL> alter pluggable database pdb2 close immediate;

Pluggable database altered.

SQL> alter pluggable database pdb2 open;

Pluggable database altered.

SQL> select name,cause,type,message,status from pdb_plug_in_violations order by name; 

NAME       CAUSE                TYPE      MESSAGE                                                                                              STATUS
---------- -------------------- --------- ---------------------------------------------------------------------------------------------------- ---------
PDB2       OPTION               WARNING   Database option RAC mismatch: PDB installed version 12.1.0.2.0. CDB installed version NULL.          RESOLVED
PDB2       Parameter            WARNING   CDB parameter sga_max_size mismatch: Previous 2G Current 1584M                                       RESOLVED
PDB2       Parameter            WARNING   CDB parameter memory_target mismatch: Previous 2G Current 1584M                                      RESOLVED
PDB2       Parameter            WARNING   CDB parameter memory_max_target mismatch: Previous 2G Current 1584M                                  RESOLVED
PDB2       Parameter            WARNING   CDB parameter cluster_database mismatch: Previous TRUE Current FALSE                                 RESOLVED
PDB2       Oracle Opatch        WARNING   Oracle opatch mismatch: opatch 19769480 is missing in the CDB.                                       RESOLVED
PDB2       Oracle Opatch        WARNING   Oracle opatch mismatch: opatch 20299023 is missing in the CDB.                                       RESOLVED
PDB2       Oracle Opatch        WARNING   Oracle opatch mismatch: opatch 20831110 is missing in the CDB.                                       RESOLVED
PDB2       Oracle Opatch        WARNING   Oracle opatch mismatch: opatch 21359755 is missing in the CDB.                                       RESOLVED
PDB2       Oracle Opatch        WARNING   Oracle opatch mismatch: opatch 21948354 is missing in the CDB.                                       RESOLVED
PDB2       Oracle Opatch        WARNING   Oracle opatch mismatch: opatch 22291127 is missing in the CDB.                                       RESOLVED

NAME       CAUSE                TYPE      MESSAGE                                                                                              STATUS
---------- -------------------- --------- ---------------------------------------------------------------------------------------------------- ---------
PDB2       Oracle Opatch        WARNING   Oracle opatch mismatch: opatch 23054246 is missing in the CDB.                                       RESOLVED
PDB2       Oracle Opatch        WARNING   Oracle opatch mismatch: opatch 24006101 is missing in the CDB.                                       RESOLVED
PDB2       Oracle Opatch        WARNING   Oracle opatch mismatch: opatch 24732082 is missing in the CDB.                                       RESOLVED
PDB2       Oracle Opatch        WARNING   Oracle opatch mismatch: opatch 25171037 is missing in the CDB.                                       RESOLVED
PDB2       Oracle Opatch        WARNING   Oracle opatch mismatch: opatch 25755742 is missing in the CDB.                                       RESOLVED
PDB2       Oracle Opatch        WARNING   Oracle opatch mismatch: opatch 26609783 is missing in the CDB.                                       RESOLVED
PDB2       Oracle Opatch        WARNING   Oracle opatch mismatch: opatch 26713565 is missing in the CDB.                                       RESOLVED
PDB2       Oracle Opatch        WARNING   Oracle opatch mismatch: opatch 26925311 is missing in the CDB.                                       RESOLVED
PDB2       Oracle Opatch        WARNING   Oracle opatch mismatch: opatch 27338020 is missing in the CDB.                                       RESOLVED
PDB2       Oracle Opatch        WARNING   Oracle opatch mismatch: opatch 27338041 is missing in the CDB.                                       RESOLVED

21 rows selected.

全部的狀态都是RESOLVED，檢查trace log也沒用了相關警告！

至此，PDB遷移完成！

可能存在問題及解決辦法：

問題1、unplug出錯：

SQL> alter pluggable database pdb2 unplug into '/home/oracle/pdb2.xml'; 
alter pluggable database pdb2 unplug into '/home/oracle/pdb2.xml'
*
ERROR at line 1:
ORA-65025: Pluggable database PDB2 is not closed on all instances.

解決辦法：需要先關閉PDB才可以unplug。

問題2、如果datafile不在同一目錄，或者要對datafile重新命名的，使用source_file_name_convert指定每一個datafile

create pluggable database pdb2 as clone  using '/home/oracle/pdb2.xml'    
 source_file_name_convert = (
 '+DG_DATA/RACDB/783C262E9F5EC362E0531F05160A3313/DATAFILE/system.278.989576727',
'/u01/app/oracle/oradata/pdb1/system.278.989576727',
 '+DG_DATA/RACDB/783C262E9F5EC362E0531F05160A3313/DATAFILE/sysaux.271.989576727',
'/u01/app/oracle/oradata/pdb1/sysaux.271.989576727',
'+DG_DATA/RACDB/783C262E9F5EC362E0531F05160A3313/DATAFILE/users.276.989576727',
'/u01/app/oracle/oradata/pdb1/users.276.989576727',
'+DG_DATA/RACDB/783C262E9F5EC362E0531F05160A3313/TEMPFILE/temp.279.989576733',
'/u01/app/oracle/oradata/pdb1/temp.276.989576727')
file_name_convert=NONE  NOCOPY tempfile reuse;

問題3、如果不想出現更新檔或者參數這些warning出現，可以修改xml檔案才進行插入操作。

解決辦法：

修改參數值和目标設定的參數值一樣，如下

<parameters>
      <parameter>processes=300</parameter>
      <parameter>sga_max_size=2147483648</parameter>
      <parameter>memory_target=2147483648</parameter>
      <parameter>memory_max_target=2147483648</parameter>
      <parameter>db_block_size=8192</parameter>
      <parameter>compatible='12.1.0.2.0'</parameter>
      <parameter>cluster_database=TRUE</parameter>
      <parameter>open_cursors=300</parameter>
      <parameter>enable_pluggable_database=TRUE</parameter>
    </parameters>

删除目标端不存在的更新檔集，如下：

<opatches>
      <opatch>19769480</opatch>
      <opatch>20299023</opatch>
      <opatch>20831110</opatch>
      <opatch>21359755</opatch>
      <opatch>21948354</opatch>
      <opatch>22291127</opatch>
      <opatch>23054246</opatch>
      <opatch>24006101</opatch>
      <opatch>24732082</opatch>
      <opatch>25171037</opatch>
      <opatch>25755742</opatch>
      <opatch>26609783</opatch>
      <opatch>26713565</opatch>
      <opatch>26925311</opatch>
      <opatch>27338020</opatch>
      <opatch>27338041</opatch>
    </opatches>

參考：

Database Option In The PDB Or The CDB Database Option RAC Mismatch (文檔 ID 2415014.1)

Oracle 12c R1 Plug/Unplug 遷移PDB實驗（1）：插拔 RAC PDB到 Non-RAC PDB

實驗目标：

實驗環境：

實驗步驟：

繼續閱讀

快速删除ORACLE重複記錄

oracle海量資料表删除重複記錄

[引]在Oracle中如何利用Rowid查找和删除表中的重複記錄

oralce與mysql中，如何删除重複記錄

查詢表中重複記錄

ORACLE 表壓縮

oracle中的start with connect by用法

使用JDeveloper Remote debug PLSQL程式

LoadRunner 的多種Vuser類型

性能測試（并發負載壓力）測試分析－簡要篇

賦予使用者dba權限--解釋以上：

Oracle的基本操作

為什麼要選擇UniDAC

windows不能在本地計算機上運作oracleDbConsoleorcl

SQL語言基礎：常用的資料查詢語句

Oracle 批量查詢傳入List 傳回List

Oracle 12c R1 Plug/Unplug 遷移PDB實驗（1）： 插拔 RAC PDB到 Non-RAC PDB

實驗目标：

實驗環境：

實驗步驟：

繼續閱讀

Oracle 12c R1 Plug/Unplug 遷移PDB實驗（1）：插拔 RAC PDB到 Non-RAC PDB