Pickleball spielen

Hot cloning PDBs is new in 12.2, so you don’t have to put the source PDB into READ ONLY mode before the cloning if you have it in local undo mode. I suppose shared undo mode will become very unpopular. Another 12.2 New Feature is the option to clone a PDB that can be refreshed from the source PDB. I will show both features with this article, but you may of course do hot cloning without a later refresh. In this case, just leave out the REFRESH MANUAL clause and you don’t have to OPEN READ ONLY the cloned PDB afterwards. On a high level, what I demonstrate is this:

Hot cloning & refreshing a PDB

Now let’s see that with details:

SQL> connect sys/oracle_4U@cdb1 as sysdba
Connected.
SQL> select name,open_mode from v$pdbs;

NAME	   OPEN_MODE
---------- ----------
PDB$SEED   READ ONLY
PDB1	   READ WRITE

I have prepared a tnsnames.ora with connect descriptors for pdb1 and the future pdb2:

PDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = uhesse)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb1)
    )
  )
PDB2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = uhesse)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb2)
    )
  )

Now I create the clone user inside of the source PDB and the database link inside the destination CDB:

SQL> connect sys/oracle_4U@pdb1 as sysdba
Connected.
SQL> grant create session,create pluggable database to cloner identified by cloner;

Grant succeeded.

SQL> connect sys/oracle_4U@cdb2 as sysdba
Connected.
SQL> select name,open_mode from v$pdbs;

NAME	   OPEN_MODE
---------- ----------
PDB$SEED   READ ONLY

SQL> create database link clone_link connect to cloner identified by cloner using 'pdb1';

Database link created.

SQL> select * from session_privs@clone_link;

PRIVILEGE
----------------------------------------
CREATE PLUGGABLE DATABASE
CREATE SESSION

The steps so far are the same as with 12.1. My databases are single instances running on the same Linux server without ASM.

SQL> connect sys/oracle_4U@cdb2 as sysdba

SQL> host mkdir /u01/app/oracle/oradata/pdb2
SQL> create pluggable database pdb2 from pdb1@clone_link file_name_convert=('pdb1','pdb2')
     refresh mode manual;  

Pluggable database created.

SQL> alter pluggable database pdb2 open read only;

Pluggable database altered.

The source pdb1 remains OPEN READ WRITE all the time during the demo. Now I change something there and refresh pdb2 afterwards:

SQL> connect adam/adam@pdb2
Connected.
SQL> select count(*) from sales;

  COUNT(*)
----------
    748999

SQL> connect adam/adam@pdb1
Connected.
SQL> delete from sales where rownum<1000; 

999 rows deleted. 

SQL> commit;

Commit complete.

SQL> select count(*) from sales;

  COUNT(*)
----------
    748000

SQL> connect sys/oracle_4U@cdb2 as sysdba
Connected.
SQL> alter pluggable database pdb2 close immediate;

Pluggable database altered.

Notice that you need to perform the refresh from inside the cloned PDB, else you get this error message:

SQL> alter pluggable database pdb2 refresh;
alter pluggable database pdb2 refresh
*
ERROR at line 1:
ORA-65118: operation affecting a pluggable database cannot be performed from
another pluggable database

So I connect into the cloned PDB to do the refresh there:

SQL> connect sys/oracle_4U@pdb2 as sysdba
Connected.
SQL> alter pluggable database refresh;

Pluggable database altered.

SQL> alter pluggable database open read only;

Pluggable database altered.

SQL> select count(*) from adam.sales;

  COUNT(*)
----------
    748000

Fortunately, you are not allowed to open this cloned PDB in normal READ WRITE mode because of the REFRESH clause added to the CREATE PLUGGABLE DATABASE command:

SQL> shutdown immediate
Pluggable Database closed.
SQL> connect sys/oracle_4U@cdb2 as sysdba
Connected.
SQL> alter pluggable database pdb2 open;
alter pluggable database pdb2 open
*
ERROR at line 1:
ORA-65341: cannot open pluggable database in read/write mode

Another possible implementation would have been to go to OPEN READ ONLY silently like with a Physical Standby. Don’t believe it, test it! 🙂

Watch me explaining and demonstrating the above on YouTube: