PDB Remote Clone in Oracle Datab

1.源库创建用户并授权

[oracle@xag224 dbs]$ sqlplus / as sysdba

SQL> show pdbs;
    CON_ID CON_NAME           OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED           READ ONLY  NO
     3 PDB2               READ WRITE NO

SQL> create user c##xag identified by 123456;

SQL> grant create session,create pluggable database,sysoper to c##xag container=all;

2.目标库编辑TNS

MPCDB224 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xag224)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = MPCDB)
    )
  )

3.目标库创建DBLink

create database link mpcdb224_dblink connect to c##xag identified by "123456" using 'MPCDB224';

select 1 from dual@mpcdb224_dblink;

4 基于手工刷新 REFRESH MODE MANUAL

SQL> !mkdir /home/oradata/MPCDB/pdb2_new

create pluggable database pdb2_new from pdb2@mpcdb224_dblink create_file_dest='/home/oradata/MPCDB/pdb2_new' refresh mode manual;

SQL> show pdbs

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

5.目标库打开可刷新PDB (可刷新PDB为了保证和源库一致，必须只能以 READ ONLY 只读模式打开)

SQL> alter pluggable database pdb2_new open read only;

SQL> show pdbs

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

6.检查数据

--alter session set container=pdb2_new;

sql xag/123456@xag225:1521/pdb2_new;

SET SQLFORMAT ansiconsole

select host_address ,host_name,db_name, to_char(create_time,'hh24:mi:ss')
from my_healt_check order by create_time desc;
查詢結果只有 clone 完成那一刻的數據，沒有之後的數據

7. 手動刷新數據（pdb2_new目标库关闭）

[oracle@xag225 ~]$ sql sys/123456@xag225:1521/pdb2_new as sysdba;

SQL> show pdbs;

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 PDB2_NEW              READ ONLY NO     

SQL> alter pluggable database pdb2_new close immediate;

SQL> show pdbs;
    CON_ID CON_NAME             OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 PDB2_NEW              MOUNTED           

SQL> alter pluggable database pdb2_new refresh;  #刷新後則有同步新的增量數據

SQL> alter pluggable database pdb2_new open read only;


SQL> conn xag/123456@xag225:1521/pdb2_new;

SQL> show user;
USER is "XAG"

SQL> show pdbs;

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 PDB2_NEW              READ ONLY NO     

SET SQLFORMAT ansiconsole

select host_address ,host_name,db_name, to_char(create_time,'hh24:mi:ss')
from my_healt_check order by create_time desc;

8.模拟应用侧停应用，将源端PDB置为read only

[oracle@xag224 ~]$ sql / as sysdba

SQL> show pdbs;
    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED              READ ONLY NO     
         3 PDB2                  READ WRITE NO     

SQL> alter pluggable database pdb2 close immediate;

SQL> alter pluggable database pdb2 open read only;

SQL> show pdbs;

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED              READ ONLY NO     
         3 PDB2                  READ ONLY NO     

conn xag/123456@xag224:1521/pdb2

SET SQLFORMAT ansiconsole

select host_address ,host_name,db_name, to_char(create_time,'yyyy-mm dd hh24:mi:ss')
from my_healt_check order by create_time desc;

9.目标端再次手动刷新，应用最近的增量，观察目志是否正常

[oracle@xag225 ~]$ sql sys/123456@xag225:1521/pdb2_new as sysdba;

SQL> show pdbs;

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 PDB2_NEW              READ ONLY NO     

SQL> alter pluggable database pdb2_new close immediate;

SQL> show pdbs;
    CON_ID CON_NAME             OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 PDB2_NEW              MOUNTED           

SQL> alter pluggable database pdb2_new refresh; 

10. 目标端PDB关闭刷新模式

 ALTER PLUGGABLE DATABASE pdb2_new REFRESH MODE NONE;

11.拉起目标端PDB(讀寫模式)

ALTER PLUGGABLE DATABASE pdb2_new open read write;

SQL> show pdbs;

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 PDB2_NEW              READ WRITE NO   

12.驗證數據

conn xag/123456@xag225:1521/pdb2_new;

SET SQLFORMAT ansiconsole

select host_address ,host_name,db_name, to_char(create_time,'yyyy-mm dd hh24:mi:ss')
from my_healt_check order by create_time desc;
---------------------------------------------------------------------------------------
HOST_ADDRESS   HOST_NAME   DB_NAME    TO_CHAR(CREATE_TIME,'YYYY-MMDDHH24:MI:SS')   
10.0.30.225    xag225      PDB2_NEW   2021-08 31 22:26:44                          
10.0.30.225    xag225      PDB2_NEW   2021-08 31 22:25:44                          
10.0.30.225    xag225      PDB2_NEW   2021-08 31 22:24:44                          
10.0.30.224    xag224      PDB2       2021-08 31 22:09:50                          
10.0.30.224    xag224      PDB2       2021-08 31 22:08:50                          
10.0.30.224    xag224      PDB2       2021-08 31 22:07:50     

SQL> select tablespace_name,file_name,bytes/1024/1024 mb from dba_temp_files 
  2  union all 
  3  select tablespace_name,file_name,bytes/1024/1024 mb from dba_data_files;
TABLESPACE_NAME   FILE_NAME                                                                                                      MB 
TS                /home/oradata/MPCDB/pdb2_new/MPCDB/CAD94C062843687DE053E11E000A22D3/datafile/o1_mf_ts_jlxpm83p_.dbf           192 
TS                /home/oradata/MPCDB/pdb2_new/MPCDB/CAD94C062843687DE053E11E000A22D3/datafile/o1_mf_ts_jlxpm83q_.dbf            64 
SYSTEM            /home/oradata/MPCDB/pdb2_new/MPCDB/CAD94C062843687DE053E11E000A22D3/datafile/o1_mf_system_jlxpm82t_.dbf       270 
SYSAUX            /home/oradata/MPCDB/pdb2_new/MPCDB/CAD94C062843687DE053E11E000A22D3/datafile/o1_mf_sysaux_jlxpm83n_.dbf       340 
UNDOTBS1          /home/oradata/MPCDB/pdb2_new/MPCDB/CAD94C062843687DE053E11E000A22D3/datafile/o1_mf_undotbs1_jlxpm83o_.dbf     200 
US                /home/oradata/MPCDB/pdb2_new/MPCDB/CAD94C062843687DE053E11E000A22D3/datafile/o1_mf_us_jlxpm83r_.dbf            32 
US                /home/oradata/MPCDB/pdb2_new/MPCDB/CAD94C062843687DE053E11E000A22D3/datafile/o1_mf_us_jlxpm83q_.dbf            32