Create refreshable PDB on 19C

Goal : create refreshable clone from remote source pdb to local target side.

 

Remote Source CDB : A

Local       Target CDB: B

 

• On  CDB A and B: Create common user 

     create user c##adminpdb identified by adminpdb container=all;
     grant dba, sysoper, create session, create pluggable database to c##adminpdb container=all;

• On CDB B: Create dblink  (dblink will point to CDB A)

create public database link cdb_a_link connect to c##adminpdb identified by adminpdb using 'db_a_link';    

• On CDB B all nodes: Add cdb_a_dblink tns entry which is point to CDB A, not point to source PDB.  (or will hit TNS error)

echo "db_a_link=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$hostA)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=A.us.oracle.com)))" >>       ${ORACLE_HOME}/network/admin/tnsnames.ora

• On CDB B:Create refreshable clone PDB

     create pluggable database $targetpdb from $sourcepdb@cdb_a_link REFRESH MODE MANUAL keystore identified by "${Wallet_keystore_passwd}";
     alter pluggable database $targetpdb open ;  <===It will open to read only mode
     alter session set container=$pdbname;
     select con_id,NAME,OPEN_MODE,restricted from v$PDBS where NAME='$targetpdb';

• On CDB B: Refresh newly clone

     alter pluggable database $targetpdb close;
     alter pluggable database $targetpdb refresh;
     alter pluggable database $targetpdb open instances=all;
     select con_id,NAME,OPEN_MODE,restricted from gv$PDBS where NAME='$targetpdb';

Or 

 

Use user system 

 

1)On both source side and target side: Grant permission

—————————————————————————

grant execute on sys.DBMS_SQL to system container=all;

grant select any table to system container=all;

grant sysdba to system CONTAINER=ALL;

GRANT CREATE SESSION, RESOURCE, CREATE ANY TABLE, UNLIMITED TABLESPACE TO system CONTAINER=ALL;

GRANT CREATE PLUGGABLE DATABASE TO system CONTAINER=ALL;

GRANT SYSOPER TO system CONTAINER=ALL;

 

 

2)On target RAC: create refresh pdb clone from remote source side

—————————————————————————

Sqlplus /nolog  

 

SQL>   connect sys/syspassword@orcl as sysdba

SQL>   drop database link cdb1_link;

SQL>   create database link cdb1_link connect to system identified by <systempassword> using 'remote_sourceCDB’;

SQL>   create pluggable database pdbdebug from spdb1@cdb1_link refresh mode manual;   

SQL>   alter pluggable database  pdbdebug open read only;   

 

Verify the whether the PDB can refresh data from source side:

 

3)On source RAC: create test table on source PDB 

—————————————————————————

SQL>   alter session set container=spdb1; 

SQL>   create table testtable (id number);

SQL>   Insert into testtable values(333);

SQL>   Commit;

SQL>   Select * from testtable;

 

4)On target RAC: Refresh PDB clone manually

—————————————————————————

SQL>   alter pluggable database pdbdebug close ;

SQL>   alter pluggable database pdbdebug refresh;

 

5)On target RAC: Query test datata

—————————————————————————

SQL>   alter session set container=pdbdebug;

SQL>   select * from testtable;

 

 

Alert log on Target RAC instance 1:

==========================================================================================

...

2020-11-13T01:35:13.366475-08:00

 create pluggable database pdbdebug from spdb1@cdb1_link refresh mode manual

2020-11-13T01:35:13.375653-08:00

kpdbHashTableIns: add: ht=2 lk=0x488b79970 ele=0x488b797c0 id=9 uid=2552699574 name=PDBDEBUG

kpdbHashTableIns: add: ht=1 lk=0x488b799a0 ele=0x488b797c0 id=9 uid=2552699574 name=PDBDEBUG

2020-11-13T01:35:37.320270-08:00

Opatch validation is skipped for PDB PDBDEBUG (con_id=9)