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 完成那一刻的數據，沒有之後的數據
- 手動刷新數據（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;
- 目标端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
