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.
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.