30 November, 2021

Refreshable Clone PDB -- 1 Manual Refresh

 The facility of creating Clone PDBs that are Refreshable either "on-demand" or to a set schedule was introduced in 12.2.  The Refreshable PDB can be opened only as a Read-Only database

This is somewhat similar to having a Standby Database that can be opened Read-Only for queries and refreshed as and when on-demand (without using the Active Data Guard License)

Here is a quick demo in 19.12 on Linux

First, on the Source database ORCLCDB I verify that I have the required configuration of Local Undo and ArchiveLog mode and then setup a Common account with the requisite privileges:



oracle19c>sqlplus sys/manager@ORCLCDB as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 30 23:01:41 2021
Version 19.12.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> col property_value format a16
SQL> select property_value, log_mode, force_logging
  2  from database_properties, v$database
  3  where property_name = 'LOCAL_UNDO_ENABLED'
  4  /

PROPERTY_VALUE   LOG_MODE     FORCE_LOGGING
---------------- ------------ ---------------------------------------
TRUE             ARCHIVELOG   YES

SQL>
SQL> alter pluggable database orclpdb1 open  -- verify or open the source pdb
  2  /
alter pluggable database orclpdb1 open  -- verify or open the source pdb
*
ERROR at line 1:
ORA-65019: pluggable database ORCLPDB1 already open


SQL>
SQL> create user c##for_pdb_clones identified by for_pdb_clones
  2  default tablespace users temporary tablespace temp
  3  container=ALL   -- a common user for all PDBs
  4  /

User created.

SQL> grant create session, create pluggable database, sysoper -- privileges that are required (not DBA Role)
  2  to c##for_pdb_clones
  3  container=ALL  -- across all PDBs
  4  /

Grant succeeded.

SQL>
SQL> select con_id, privilege, common
  2  from cdb_sys_privs  -- check across all PDBs
  3  where grantee = 'C##FOR_PDB_CLONES'
  4  order by 1,2
  5  /

    CON_ID PRIVILEGE                                COM
---------- ---------------------------------------- ---
         1 CREATE PLUGGABLE DATABASE                YES
         1 CREATE SESSION                           YES
         3 CREATE PLUGGABLE DATABASE                YES
         3 CREATE SESSION                           YES

SQL>


Next, on the Target Database CDB2 (where I want to create the Clone PDB), I setup a Database Link and then issue the CREATE PLUGGABLE DATABASE command



oracle19c>sqlplus sys/manager@CDB2 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 30 23:06:41 2021
Version 19.12.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> create database link for_pdb_clones  -- create in the Root CDB
  2  connect to c##for_pdb_clones identified by for_pdb_clones using 'ORCLCDB'
  3  /

Database link created.

SQL> create pluggable database ro_pdb  -- the cloned pdb will be Read-Only
  2  from orclpdb1@for_pdb_clones -- create from pluggable database orclpd1
  3  refresh mode manual -- not specifying an auto-refresh interval
  4  -- file_name_convert=('ORCLCDB','CDB2')  -- not required as I am using OMF with db_create_file_dest
  5  /

Pluggable database created.

SQL>
SQL> alter pluggable database ro_pdb open -- this should be opened Read-Only
  2  /
alter pluggable database ro_pdb open -- this should be opened Read-Only
*
ERROR at line 1:
ORA-65341: cannot open pluggable database in read/write mode


SQL>
SQL> alter pluggable database ro_pdb open read only -- this will succeed
  2  /

Pluggable database altered.

SQL>


So, now the Source PDB ORCLPDB1 has been cloned to CDB2 as a new Pluggable Database RO_PDB.

How do I refresh it ?  I will update the Source and then query the RO_PDB at CDB2


SQL> connect hemant/hemant@ORCLPDB1
ERROR:
ORA-28002: the password will expire within 7 days


Connected.
SQL> create table list_of_objects
  2  as
  3  select * from dba_objects
  4  /

Table created.

SQL> select count(*) from list_of_objects
  2  /

  COUNT(*)
----------
     73645

SQL>
SQL> connect hemant/hemant@ro_pdb
ERROR:
ORA-28032: Your password has expired and the database is set to read-only


Warning: You are no longer connected to ORACLE.
SQL>
--- cannot connect to the Read Only database with an expired password
SQL> connect sys/manager@CDB2 as sysdba
Connected.
SQL> alter session set container=RO_PDB;

Session altered.

SQL> select count(*)
  2  from dba_objects
  3  where owner = 'HEMANT'
  4  /

  COUNT(*)
----------
        32

SQL> select max(created)
  2  from dba_objects
  3  where owner = 'HEMANT'
  4  /

MAX(CREAT
---------
04-SEP-21

SQL>
SQL> desc hemant.list_of_objects
ERROR:
ORA-04043: object hemant.list_of_objects does not exist


SQL>


So, the RO_PDB does not have the new table and does not even allow login with an Expired Password. I will first update my password at the source ORCLPDB1 and then refresh RO_PDB and query again.

SQL> connect hemant/hemant@ORCLPDB1
ERROR:
ORA-28002: the password will expire within 7 days


Connected.
SQL> password hemant
Changing password for hemant
Old password:
New password:
Retype new password:
Password changed
SQL>
SQL> connect sys/manager@CDB2 as sysdba
Connected.
SQL> alter pluggable database ro_pdb close;  -- must CLOSE to allow a REFRESH

Pluggable database altered.

SQL> alter pluggable database ro_pdb refresh;  -- REFRESH command

Pluggable database altered.

SQL> alter pluggable database ro_pdb open read only;

Pluggable database altered.

SQL>
SQL> connect hemant/newhemant@ro_pdb
Connected.
SQL> select count(*) from list_of_objects;

  COUNT(*)
----------
     73645

SQL>


The Refresh of the RO_PDB is a simple process. I simply CLOSE it, issue an ALTER ... REFRESH command and then OEPN READ ONLY again.
Now, RO_PDB allows connections with the new Password and has the updated data *as of the time the REFRESH command is issued*.   Any new data populated in the source or any change made in the source are not available in RO_PDB  until a fresh REFRESH is executed.

20 November, 2021