Create the common user in both the source as well as target CDB’s
This user credentials will be used in the database link between CDB1 and CDB2 and between CDB2 and CDB1.
Note the roles and permissions which are granted to this common user.
CDB1 and CDB2
SQL> CREATE USER c##link_user IDENTIFIED BY oracle; User created. SQL> GRANT CREATE SESSION, RESOURCE, CREATE ANY TABLE, UNLIMITED TABLESPACE TO c##link_user CONTAINER=ALL; Grant succeeded. SQL> GRANT CREATE PLUGGABLE DATABASE TO c##link_user CONTAINER=ALL; Grant succeeded. SQL> GRANT SYSOPER TO c##link_user CONTAINER=ALL; Grant succeeded.
Create the database link
CDB1
SQL> create database link cdb2_link 2 connect to c##link_user identified by oracle 3 using 'cdb2'; Database link created. SQL> select * from dual@cdb2_link; D - X
CDB2
SQL> create database link cdb1_link 2 connect to c##link_user identified by oracle 3 using 'cdb1'; Database link created. SQL> select * from dual@cdb1_link; D - X
Create the test table in ORCLPDB1 pluggable database on CDB1
CDB1
SQL> conn / as sysdba Connected. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORCLPDB1 READ WRITE NO SQL> alter session set container=orclpdb1; Session altered. SQL> create table system.myobjects as select * from all_objects; Table created. SQL> select count(*) from myobjects; COUNT(*) ---------- 68326
Create the refreshable pluggable database on CDB2
CDB2
SQL> create pluggable database orclpdb1 from orclpdb1@cdb1_link
file_name_convert=('/u02/app/oracle/oradata/ORCLCDB/','/u03/app/oracle/oradata/CDB2/')
refresh mode manual; 2 3
Pluggable database created.
Refresh the pluggable database (manually)
CDB2
SQL> alter pluggable database orclpdb1 close immediate; Pluggable database altered. SQL> alter pluggable database refresh; Pluggable database altered. SQL> alter pluggable database orclpdb1 open read only; Pluggable database altered.
Note the rows are visible now in the target pluggable database on CDB2
SQL> select count(*) from system.myobjects; COUNT(*) ---------- 68326
Make some changes to the MYOBJECTS table in the source pluggable database on CDB1
CDB1
SQL> alter session set container=orclpdb1; Session altered. SQL> truncate table system.myobjects; Table truncated.
Refresh the pluggable database on CDB2 and note that the MYOBJECTS table now has 0 rows
CDB2
SQL> alter pluggable database orclpdb1 close immediate; Pluggable database altered. SQL> alter pluggable database refresh; Pluggable database altered. SQL> alter pluggable database orclpdb1 open read only; Pluggable database altered. SQL> select count(*) from system.myobjects; COUNT(*) ---------- 0
Encountered the error “ORA-12754: Feature PDB REFRESH SWITCHOVER is disabled due to missing capability”
This error was seen in the early days when Oracle 18c was first released only as a Cloud and Engineered Systems offering and we were trying to use the software for some on-premise testing.
Thought this had been fixed now in the on-premise 18c software version – looks like we still have an issue. Will raise an SR and provide feedback.
CDB1
SQL> alter pluggable database orclpdb1 2 refresh mode manual 3 from orclpdb1@cdb2_link 4 switchover; alter pluggable database orclpdb1 * ERROR at line 1: ORA-12754: Feature PDB REFRESH SWITCHOVER is disabled due to missing capability
Note: had to enable this hidden parameter _exadata_feature_on
SQL> alter system set "_exadata_feature_on"=true scope=spfile; System altered. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; ORACLE instance started. Total System Global Area 1560280912 bytes Fixed Size 8896336 bytes Variable Size 1023410176 bytes Database Buffers 520093696 bytes Redo Buffers 7880704 bytes Database mounted. Database opened. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORCLPDB1 MOUNTED SQL> alter pluggable database orclpdb1 open; Pluggable database altered.
Perform a switchover at the pluggable database level on CDB1
SQL> alter pluggable database orclpdb1 refresh mode manual from orclpdb1@cdb2_link switchover; 2 3 4 Pluggable database altered.
Note now the pluggable database is in a MOUNTED state on CDB1
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORCLPDB1 MOUNTED
On CDB2 after the switchover, the pluggable database is now open in READ WRITE mode.
CDB2
SQL> conn / as sysdba Connected. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORCLPDB1 READ WRITE NO
Make some changes to the (new) source pluggable database on CDB2
CDB2
SQL> alter session set container=orclpdb1; Session altered. SQL> insert into system.myobjects 2 select * from all_objects; 71799 rows created. SQL> commit; Commit complete.
Refresh the (new) refreshable pluggable database on CDB1
CDB1
SQL> conn / as sysdba Connected. SQL> alter pluggable database orclpdb1 close immediate; Pluggable database altered. SQL> alter pluggable database orclpdb1 refresh; Pluggable database altered. SQL> alter pluggable database orclpdb1 open read only; Pluggable database altered. SQL> alter session set container=orclpdb1; Session altered.
Note the changes made on CDB2 pluggable database are also visible in the pluggable database on CDB1
CDB1
SQL> select count(*) from system.myobjects; COUNT(*) ---------- 71799
