Oracle 12c Relocate PDB

在oracle 12.2中可以對pdb執行relocate失蹤，下面的例子將介紹對遠端CDB中將名為jypdb的PDB進行遷移操作。假設滿足以下條件:
.當前使用者在被遷移PDB所在的CDB的root容器中有create pluggable database系統許可權

.目標CDB連線PDB當前CDB的dblink名為jycdb_link。dblink的建立語句如下:

create public database link jycdb_link connect to c##yyl
identified by "yyl" using 'jy';

PDB的當前CDB中的公共使用者c##yyl有sysoper管理許可權與create pluggable database系統許可權

.不指定path_prefix子句

.不指定file_name_convert與create_file_dest子句。當啟用OMF或設定pdb_file_name_convert引數時，基於OMF的配置或引數的設定檔案會被移動到新目錄。

.對PDB不指定儲存限制。因此不指定storage子句

.在目標目錄中不存在相同名字的temp檔案，那麼新的temp檔案會被建立。因此不指定tempfile reuse子句。

.連線會被自動從源PDB遷移到遷移後的PDB。因此指定availability max子句。

執行以下語句來將PDB(jypdb)從遠端CDB遷移到當前CDB中:
1.在遠端CDB中建立公共使用者c##yyl

SQL> create user c##yyl identified by "yyl" container=all;

User created.

SQL> grant sysoper,connect,resource,create pluggable database to c##yyl container=all;

Grant succeeded.

2.檢查遠端CDB是否使用本地undo與歸檔

SQL> COLUMN property_name FORMAT A30
SQL> COLUMN property_value FORMAT A30
SQL>
SQL> SELECT property_name, property_value
  2  FROM   database_properties
  3  WHERE  property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED             TRUE


SQL> SELECT log_mode FROM   v$database;

LOG_MODE
------------
ARCHIVELOG

因為遠端CDB使用本地undo與歸檔，因此不需要將遠端資料庫設定為只讀模式

3.在本地CDB(jy)中建立指定遠和CBD(jy)的dblink。連線串中要包含(SERVER=DEDICATED)條目錄，否則會收到 "ORA-01031: insufficient privileges"錯誤

SQL> create public database link jycdb
  2    connect to c##yyl identified by "yyl"
  3    using '(DESCRIPTION =
  4      (ADDRESS_LIST =
  5        (ADDRESS = (PROTOCOL = TCP)(HOST =10.138.130.173)(PORT = 1521))
  6      )
  7      (CONNECT_DATA =
  8        (SERVER = DEDICATED)
  9        (SERVICE_NAME =jy)
 10      )
 11    )';

Database link created.


SQL> select * from dual@jycdb;

D
-
X

4.檢查本地CDB是否使用了本地undo與歸檔

SQL> COLUMN property_name FORMAT A30
SQL> COLUMN property_value FORMAT A30
SQL>
SQL> SELECT property_name, property_value
  2  FROM   database_properties
  3  WHERE  property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED             TRUE

SQL> SELECT log_mode FROM   v$database;

LOG_MODE
------------
ARCHIVELOG

5.在本地CDB執行下面的語句來克隆可重新整理的PDB

SQL> create pluggable database jypdb from jypdb@jycdb relocate availability max;

Pluggable database created.
SQL> select name,open_mode from v$pdbs;

NAME                                                                                                                             OPEN_MODE
-------------------------------------------------------------------------------------------------------------------------------- ----------
PDB$SEED                                                                                                                         READ ONLY
JYPDB                                                                                                                            READ WRITE

以read write方式來開啟PDB以完成遷移操作

SQL> alter pluggable database jypdb open;

Pluggable database altered.

SQL> alter session set container=jypdb;

Session altered.

SQL> set long 200
SQL> set linesize 200
SQL> select name,open_mode from v$pdbs;

NAME                                                                                                                             OPEN_MODE
-------------------------------------------------------------------------------------------------------------------------------- ----------
JYPDB                                                                                                                            READ WRITE

刪除公共dblink

SQL> drop public database link jycdb;

Database link dropped.

檢查遠端PDB可以看到已經被刪除了