Refreshable Clone PDB -- 4 Converting it to a Read Write PDB

 As demonstrated in my first post on Refreshable Clone PDBs, the Clone PDB can be opened in only READ ONLY mode.  This is akin to a Basic Materialized View or a Read Only Standby Database, either of which is updated from the source table(s) / database.

Such a Refreshable Clone PDB is useful as a Reporting Database where you can run queries / extracts -- with data AS OF the last Refresh -- without putting load on the running Production (Transactional, Read Write Database).

But if you want to (finally) open the Clone PDB in Read Write mode (and severe any links with the source, thus preventing further Refresh's) you can do so by changing the Refresh Mode.

SQL> alter pluggable database ro_pdb open read only;

Pluggable database altered.

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

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

SQL> connect / as sysdba
Connected.

SQL> alter pluggable database ro_pdb close;  -- close it so that I can do a REFRESH

Pluggable database altered.

SQL>
SQL> alter pluggable database ro_pdb refresh;  -- execute the REFRES

Pluggable database altered.

SQL> alter pluggable database ro_pdb open ;  -- attempt to OPEN, defaulting to READ WRITE mode
alter pluggable database ro_pdb open
*
ERROR at line 1:
ORA-65341: cannot open pluggable database in read/write mode


SQL> alter pluggable database ro_pdb open read write;
alter pluggable database ro_pdb open read write
*
ERROR at line 1:
ORA-65341: cannot open pluggable database in read/write mode


SQL>
SQL> alter pluggable database ro_pdb open read only;  -- open READ ONLY

Pluggable database altered.

SQL> connect hemant/newhemant@ro_pdb
Connected.
SQL> select count(*) from list_of_objects;  -- verify that the database has been refreshed (new rows visible in the source table)

  COUNT(*)
----------
     83645

SQL>
SQL> connect / as sysdba
Connected.
SQL> alter pluggable database ro_pdb close;  -- close it again

Pluggable database altered.

SQL>
SQL> alter pluggable database ro_pdb refresh mode none ;  -- ***DISABLE FURTHER REFRESH's***

Pluggable database altered.

SQL>
SQL> alter pluggable database ro_pdb open read write;  -- open in READ WRITE mode now !!

Pluggable database altered.

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

  COUNT(*)
----------
     83645

SQL> delete list_of_objects where owner = 'HEMANT';  -- proof that the database is now WRITABLE

32 rows deleted.

SQL> commit;

Commit complete.

SQL>
SQL> alter pluggable database ro_pdb close;

Pluggable database altered.

SQL> alter pluggable database ro_pdb refresh;  -- check if it can be REFRESH'ed from the source
alter pluggable database ro_pdb refresh
*
ERROR at line 1:
ORA-65261: pluggable database RO_PDB not enabled for refresh


SQL>

Thus, to enable the PDB to be WRITABLE, REFRESH has to be disabled. 

 Once REFRESH is disabled, the PDB can no longer be refreshed from the source. It has diverged from the source.

These are the alert log messages when REFRESH is disabled :

2022-01-23T15:35:55.766486+08:00
alter pluggable database ro_pdb refresh mode none
2022-01-23T15:35:55.846041+08:00
RO_PDB(6):Pluggable database RO_PDB pseudo opening
RO_PDB(6):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18
RO_PDB(6):Autotune of undo retention is turned on.
RO_PDB(6):Endian type of dictionary set to little
RO_PDB(6):Undo initialization recovery: Parallel FPTR failed: start:1511350 end:1511367 diff:17 ms (0.0 seconds)
RO_PDB(6):Undo initialization recovery: err:0 start: 1511329 end: 1511464 diff: 135 ms (0.1 seconds)
RO_PDB(6):[6305] Successfully onlined Undo Tablespace 2.
RO_PDB(6):Undo initialization online undo segments: err:0 start: 1511465 end: 1511507 diff: 42 ms (0.0 seconds)
RO_PDB(6):Undo initialization finished serial:0 start:1511329 end:1511509 diff:180 ms (0.2 seconds)
RO_PDB(6):Database Characterset for RO_PDB is AL32UTF8
2022-01-23T15:35:57.144146+08:00
RO_PDB(6):Pluggable database RO_PDB pseudo closing
RO_PDB(6):JIT: pid 6305 requesting stop
RO_PDB(6):Closing sequence subsystem (1512195729).
RO_PDB(6):Buffer Cache flush started: 6
RO_PDB(6):Buffer Cache flush finished: 6
Completed: alter pluggable database ro_pdb refresh mode none

And these are the messages when it is OPENed in READ WRITE mode :

2022-01-23T15:36:56.085938+08:00
alter pluggable database ro_pdb open read write
2022-01-23T15:36:56.088166+08:00
RO_PDB(6):Pluggable database RO_PDB opening in read write
RO_PDB(6):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18
RO_PDB(6):Autotune of undo retention is turned on.
RO_PDB(6):Endian type of dictionary set to little
RO_PDB(6):Undo initialization recovery: Parallel FPTR complete: start:1571711 end:1571732 diff:21 ms (0.0 seconds)
RO_PDB(6):Undo initialization recovery: err:0 start: 1571710 end: 1571732 diff: 22 ms (0.0 seconds)
2022-01-23T15:36:58.249919+08:00
RO_PDB(6):[6305] Successfully onlined Undo Tablespace 2.
RO_PDB(6):Undo initialization online undo segments: err:0 start: 1571732 end: 1573115 diff: 1383 ms (1.4 seconds)
RO_PDB(6):Undo initialization finished serial:0 start:1571710 end:1573161 diff:1451 ms (1.5 seconds)
RO_PDB(6):Deleting old file#9 from file$
RO_PDB(6):Deleting old file#10 from file$
RO_PDB(6):Deleting old file#11 from file$
RO_PDB(6):Deleting old file#12 from file$
RO_PDB(6):Deleting old file#26 from file$
RO_PDB(6):Deleting old file#31 from file$
RO_PDB(6):Deleting old file#32 from file$
RO_PDB(6):Deleting old file#33 from file$
RO_PDB(6):Deleting old file#34 from file$
RO_PDB(6):Adding new file#50 to file$(old file#9).             fopr-1, newblks-48640, oldblks-19200
RO_PDB(6):Adding new file#51 to file$(old file#10).             fopr-1, newblks-61440, oldblks-15360
RO_PDB(6):Adding new file#52 to file$(old file#11).             fopr-1, newblks-58240, oldblks-12800
RO_PDB(6):Adding new file#53 to file$(old file#12).             fopr-1, newblks-46880, oldblks-640
RO_PDB(6):Adding new file#54 to file$(old file#26).             fopr-1, newblks-12800, oldblks-12800
RO_PDB(6):Adding new file#56 to file$(old file#31).             fopr-1, newblks-12800, oldblks-12800
RO_PDB(6):Adding new file#55 to file$(old file#32).             fopr-1, newblks-12800, oldblks-12800
RO_PDB(6):Adding new file#57 to file$(old file#34).             fopr-1, newblks-115200, oldblks-12800
RO_PDB(6):Successfully created internal service RO_PDB at open
2022-01-23T15:36:59.694482+08:00
****************************************************************
Post plug operations are now complete.
Pluggable database RO_PDB with pdb id - 6 is now marked as NEW.
****************************************************************
RO_PDB(6):Pluggable database RO_PDB dictionary check beginning
RO_PDB(6):Pluggable Database RO_PDB Dictionary check complete
RO_PDB(6):Database Characterset for RO_PDB is AL32UTF8
2022-01-23T15:37:05.752451+08:00
RO_PDB(6):JIT: pid 6305 requesting full stop
2022-01-23T15:37:11.910855+08:00
RO_PDB(6):SUPLOG: Set PDB SUPLOG SGA at PDB OPEN, old 0x18, new 0x0 (no suplog)
2022-01-23T15:37:20.655852+08:00
RO_PDB(6):Opening pdb with no Resource Manager plan active
RO_PDB(6):joxcsys_required_dirobj_exists: directory object exists with required path /opt/oracle/product/19c/dbhome_1/javavm/admin/, pid 6305 cid 6
2022-01-23T15:37:22.578807+08:00
Pluggable database RO_PDB opened read write
Completed: alter pluggable database ro_pdb open read write

Note : To understand FILE# values, see my previous post on Datafile Names for Refresh Clone PDB.

(Some of the FILE# values have changed since that post because I have added a new tablespace in the source database and recreated RO_PDB as a Refreshable Clone PDB since that post).