Multitenant - Refreshable PDB Creation from local to remote CDB

CREATE PLUGGABLE DATABASE pdbref FROM PLM4@proxy_lnk  REFRESH MODE MANUAL;

Scenario Preview:

Create Refreshable PDB from one pdb to another , and it is always available in read only mode mainly used fro reporting purpose.

Refresh Mode Type  - Manual | Automatic

We have done practice manual in our case.


Hostname : SRV3 | Local CDB Name : SMRCDB | Local PDB : PLM4

Hostname : SRV4 | Remote CDB Name : STDRAC | Refresh PDB : PDBREF

• Connect remote database STDRAC  and create refreshable database PDBREF from PLM4 PDB database.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
oracle@srv4:/u01/app/oracle/product/12.2.0/dbhome_1/network/admin>

 sqlplus sys/system123@STDRAC as sysdba



SQL> CREATE PLUGGABLE DATABASE pdbref FROM PLM4@proxy_lnk  REFRESH MODE MANUAL;

Pluggable database created.

• By default it is in mounted state  make it into read only mode. We cannot open refreshable pdb in read write mode.

SQL> ALTER PLUGGABLE DATABASE pdbref open read only;

Pluggable database altered.

• Check status of refreshable pdb.

SQL>col PDB_NAME format a10

SQL> SELECT CON_ID, PDB_NAME, STATUS, REFRESH_MODE FROM CDB_PDBS WHERE PDB_NAME='PDBREF';

    CON_ID PDB_NAME   STATUS     REFRES
---------- ---------- ---------- ------
         5 PDBREF     REFRESHING MANUAL

• Connect local pdb PLM4 and create user and table from it. once down shutdown refreshable pdb which is PDBREF and open it in read only mode we will see same  data should be reflected there.

oracle@srv3:/u01/app/oracle/product/12.2.0/dbhome_1/network/admin> sqlplus sys/system123@SMRCDB as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Aug 11 21:01:43 2020

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> ALTER SESSION SET CONTAINER=PLM4;

Session altered.

SQL> CREATE TABLESPACE test_tbs;

Tablespace created.

SQL> CREATE USER testuser IDENTIFIED BY oracle DEFAULT TABLESPACE test_tbs QUOTA UNLIMITED ON test_tbs;

User created.

SQL> GRANT CREATE SESSION, CREATE TABLE TO testuser;

Grant succeeded.

SQL> CREATE TABLE testuser.t1 ( RID NUMBER );

Table created.

SQL> INSERT INTO testuser.t1 VALUES (1);

1 row created.

SQL> COMMIT;

Commit complete.

• Now connect to refreshable PDB 

oracle@srv4:/u01/app/oracle/product/12.2.0/dbhome_1/network/admin> sqlplus sys/system123@STDRAC as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Aug 11 22:43:01 2020

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> conn system/system123@//srv4:1523/PDBREF
Connected.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
oracle@srv4:/u01/app/oracle/product/12.2.0/dbhome_1/network/admin> sqlplus sys/system123@STDRAC as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Aug 11 22:45:22 2020

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TEST1                          READ WRITE NO
         4 PLM1                           READ WRITE NO
         5 PDBREF                         READ ONLY  NO
         6 PROXYPDB                       READ WRITE NO

• Shutdown pdbref and refresh the database using command and open it into read only mode.

SQL> ALTER PLUGGABLE DATABASE PDBREF close immediate;

Pluggable database altered.

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TEST1                          READ WRITE NO
         4 PLM1                           READ WRITE NO
         5 PDBREF                         MOUNTED
         6 PROXYPDB                       READ WRITE NO


SQL> alter session set container=PDBREF;

Session altered.

SQL> ALTER PLUGGABLE DATABASE REFRESH;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE OPEN READ ONLY;

Pluggable database altered.

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         5 PDBREF                         READ ONLY  NO

SQL> SELECT NAME FROM V$TABLESPACE;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
TEST_TBS

6 rows selected.

• Check the content of table.

SQL> SELECT * FROM TESTUSER.T1;

       RID
----------
         1


SQL> ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;

Pluggable database altered.

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         5 PDBREF                         MOUNTED

SQL> ALTER PLUGGABLE DATABASE OPEN read only;

Pluggable database altered.

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         5 PDBREF                         READ ONLY  NO