Loading....

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
Please follow and like us:

Last Update: June 22, 2020  

November 4, 2018 160 Gavin Soorma
Total 0 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?