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.
No comments:
Post a Comment