ORACLE 19C 使用热克隆PDB实现 PDB的创建

ORACLE 19C 使用热克隆PDB实现 PDB的创建

环境：
    本地：cdb2
    远端：cdb1

目标：
   把远端cdb1 中的PDB1 热克隆到本地CDB2中，自动刷新，或者手动刷新

要求：
   1.两端 CDB必须开启归档

   2.两端CDB必须使用LOCAL UNDO 

    3.源端用于DBLINK的用户，必须具有CREATE PLUGGABLE DATABASE的权限

操作步骤：
    1.远端CDB中，创建有CRTEATE PLUGGABLE DATABASE 权限的通用用户，并通用授权
         [oracle@dbserver ~]$ sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Oct 22 14:57:25 2023
Version 19.20.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

@ >conn / as sysdba
Connected.
SYS@cdb1 >

SYS@cdb1 >create user c##remote_user identified by oracle_4U;

SYS@cdb1 >grant create session,create pluggable database 
  2         to c##remote_user
  3        container=all;

SYS@cdb1 >
               
  2.在本地创建DBLINK 指向远端的CDBROOT

[oracle@dbserver ~]$ . oraenv
ORACLE_SID = [cdb2] ? 
The Oracle base remains unchanged with value /u01/app/oracle

  [oracle@dbserver ~]$ sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Oct 22 14:59:40 2023
Version 19.20.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

@ >conn / as sysdba
Connected.
SYS@cdb2 >create database link link_cdb1 
  2        connect to c##remote_user identified by oracle_4U
  3        using 'cdb1';

Database link created.

--确认DBLINK OK。
SYS@cdb2 >
SYS@cdb2 >select * from dual@link_cdb1;

D
-
X

SYS@cdb2 >

3.确认两端都必须是 LOCAL UNDO

  SYS@cdb2 >select CON_ID,PROPERTY_NAME,PROPERTY_VALUE from CDB_PROPERTIES where PROPERTY_NAME like '%UNDO%';

    CON_ID PROPERTY_NAME                  PROPERTY_VALUE
---------- ------------------------------ ------------------------------
         3 LOCAL_UNDO_ENABLED             TRUE
         1 LOCAL_UNDO_ENABLED             TRUE

  SYS@cdb1 >select CON_ID,PROPERTY_NAME,PROPERTY_VALUE from CDB_PROPERTIES where PROPERTY_NAME like '%UNDO%'

    CON_ID PROPERTY_NAME                  PROPERTY_VALUE
---------- ------------------------------ ------------------------------
         3 LOCAL_UNDO_ENABLED             TRUE
         1 LOCAL_UNDO_ENABLED             TRUE

SYS@cdb1 >

4.创建PDB3，每隔5分钟刷新
 
SYS@cdb2 >create pluggable database pdb3
  2         from pdb1@link_cdb1
  3        refresh mode every 5 minutes;

Pluggable database created.

SYS@cdb2 >

SYS@cdb2 >show pdbs

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

5.确认刷新

SYS@cdb2 >r
  1* select PDB_NAME,STATUS,REFRESH_MODE  ,REFRESH_INTERVAL from cdb_pdbs

PDB_NAME                       STATUS     REFRES REFRESH_INTERVAL
------------------------------ ---------- ------ ----------------
PDB2                           NORMAL     NONE
PDB$SEED                       NORMAL     NONE
PDB3                           REFRESHING AUTO                  5

6.只读模式打开PDB3

SYS@cdb2 >alter pluggable database pdb3 open;
alter pluggable database pdb3 open
*
ERROR at line 1:
ORA-65341: cannot open pluggable database in read/write mode

--只能只读模式打开

SYS@cdb2 >alter pluggable database pdb3 open read only;

Pluggable database altered.

SYS@cdb2 >show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB2                           READ WRITE NO
         4 PDB3                           READ ONLY  NO
SYS@cdb2 >

7.远端 PDB1 模拟制造数据

SYS@cdb1 >SHOW pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
SYS@cdb1 >select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
22-OCT-23 03.22.39.579214 PM +08:00

SYS@cdb1 >alter session set container=pdb1;

Session altered.

SYS@cdb1 >show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PDB1                           READ WRITE NO
SYS@cdb1 >create table refresh_tab (empid number);

Table created.

SYS@cdb1 >insert into refresh_tab values(100);

1 row created.

SYS@cdb1 >insert into refresh_tab values(101);

1 row created.

SYS@cdb1 >commit;

Commit complete.

SYS@cdb1 >

8.在本地确认数据同步

SYSTIMESTAMP
---------------------------------------------------------------------------
22-OCT-23 03.49.02.723158 PM +08:00

SYS@cdb2 >show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 PDB3                           MOUNTED
SYS@cdb2 >alter pluggable database open read only;

Pluggable database altered.

SYS@cdb2 >show pdbs

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

SYS@cdb2 >select * from refresh_tab;
select * from refresh_tab
              *
ERROR at line 1:
ORA-00942: table or view does not exist

看不到数据，主要是自动刷新，不能在OPEN READ ONLY下刷新，报警日志中有记录

。。。
2023-10-22T15:44:03.145812+08:00
PDB3(4):alter pluggable database refresh
PDB3(4):PDB3(4):ERROR:PDB needs to be closed for auto refresh
PDB3(4):Completed: alter pluggable database refresh

。。。

所有需要关闭PDB3
SYS@cdb2 >alter pluggable database pdb3 close;

--等下一次刷新后，再只读打开，就可以看到变化了

。。。
PDB3(4):alter pluggable database refresh
2023-10-22T15:49:04.827946+08:00
Applying media recovery for pdb-4099 from SCN 3021634 to SCN 3022317
Remote log information: count-1
thr-1,seq-36,logfile-/u01/app/oracle/fast_recovery_area/CDB1/foreign_archivelog/PDB1/2023_10_22/o1_mf_1_36_lm9nzjkq_.arc,los-2984979,nxs-18446744073709551615,maxblks-155410
PDB3(4):Media Recovery Start
2023-10-22T15:49:04.829769+08:00
PDB3(4):Serial Media Recovery started
PDB3(4):max_pdb is 4
2023-10-22T15:49:04.908399+08:00
PDB3(4):Media Recovery Log /u01/app/oracle/fast_recovery_area/CDB1/foreign_archivelog/PDB1/2023_10_22/o1_mf_1_36_lm9nzjkq_.arc
2023-10-22T15:49:05.087621+08:00
PDB3(4):Incomplete Recovery applied until change 3022317 time 10/22/2023 15:49:04
2023-10-22T15:49:05.090089+08:00
PDB3(4):Media Recovery Complete (cdb2)
PDB3(4):Completed: alter pluggable database refresh
。。。

再次打开，就可以看到数据了。

SYS@cdb2 >alter pluggable database open read only;

SYS@cdb2 >select * from refresh_tab;

     EMPID
----------
       100
       101
       200

SYS@cdb2 >

9.想正常打开PDB3

SYS@cdb2 >ALTER PLUGGABLE DATABASE REFRESH mode none;

Pluggable database altered.

SYS@cdb2 >show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 PDB3                           MOUNTED
SYS@cdb2 >conn / as sysdba
Connected.
SYS@cdb2 >select PDB_NAME,STATUS,REFRESH_MODE  ,REFRESH_INTERVAL from cdb_pdbs;

PDB_NAME                       STATUS     REFRES REFRESH_INTERVAL
------------------------------ ---------- ------ ----------------
PDB$SEED                       NORMAL     NONE
PDB3                           NEW        NONE

SYS@cdb2 >alter pluggable database pdb3 open;

Pluggable database altered.

SYS@cdb2 >show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 PDB3                           READ WRITE NO
SYS@cdb2 >