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)