Infrastructure at your Service

Oracle Team

Cloning a PDB from a standby database

By Franck Pachot

.
Great events like IOUG Collaborate is a good way to meet experts we know through blogs, twitter,etc. Yesterday evening, with nice music in the background, I was talking with Leighton Nelson about cloning PDB databases. Don’t miss his session today if you are in Las Vegas. The big problem with PDB cloning is that the source must be read-only. The reason is that it works like transportable tablespaces (except that it can transport the datafiles through database link and that we transport SYSTEM as well instead of having to import metadata). There is no redo shipping/apply here, so the datafiles must be consistent.
Obviously, being read-only is a problem when you want to clone from production.
But if you have a standby database, can you open it read-only and clone a pluggable database from there? From what we know, it should be possible, but better to test it.

Here is my source – a single tenant standby database opened in read-only:

SQL> connect sys/[email protected]//192.168.78.105/STCDB as sysdba
Connected.
SQL> select banner from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
STCDB     READ ONLY            PHYSICAL STANDBY

SQL> select name,open_mode from v$pdbs;

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

(updated on JUL-2017 thanks to Balaji comment. It was showing MOUNT for the source PDB but it must be opened READ ONLY.)

Then from the destination, I define a database link to it:

SQL> connect sys/[email protected]//192.168.78.113/CDB as sysdba
Connected.
SQL> select banner from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
CDB       READ WRITE

SQL> select name,open_mode from v$pdbs;

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

SQL>
SQL> create database link DBLINK_TO_STCDB connect to system identified by oracle using '//192.168.78.105/STCDB';

Database link created.

and create a pluggable database from it:

SQL> create pluggable database STDB2 from [email protected]_TO_STCDB;

Pluggable database created.

SQL> alter pluggable database STDB2 open;

Pluggable database altered.

So yes. This is possible. And you don’t need Active Data Guard for that. As long as you can stop the apply for the time it takes to transfer the datafiles, then this is a solution for cloning. Of course, just do one clone and if you need others then you can do it from that first clone. And within the same PDB they can be thin clones if you can use snapshots.
Ok, It’s 5 a.m here. As usual, the jetlag made me awake a bit early, so that was a good occasion to test what we have discussed yesterday…

10 Comments

  • Foued says:

    Thanks for the post Franck

  • Balaji says:

    Hi Fanck,

    I tried this but getting below error

    create pluggable database test from [email protected]_TO_STCDB
    ERROR at line 1:
    ORA-17628: Oracle error 65036 returned by remote Oracle server
    ORA-65036: pluggable database not open in required mode

    Standby :

    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
     
    NAME OPEN_MODE DATABASE_ROLE
    --------- -------------------- ----------------
    CDB1 READ ONLY PHYSICAL STANDBY
     
    SQL> select name,open_mode from v$pdbs;
     
    NAME OPEN_MODE
    ------------------------------ ----------
    PDB$SEED READ ONLY
    ORCL MOUNTED

  • Emir Vallejo Vélez says:

    Hi, I have a question, is it possible to clone a standby database that is not in a container and move it to a containerized one without deleting the source?

  • Hi Emir, I’ve never tested that, but I think you can stop the apply, open the standby read-write, dbms_pdb.describe it, and get the files to plug them as a new PDB somewhere.

  • karthik says:

    Hi Frank,
    As per your post , When we move Physical standby to READ_ONLY mode it becomes Active Dataguard .. can you provide some strong examples. how this is not a active standby dataguard. also i am trying to do the cloning of Production PDB 5.8 TB from standby Physical standby , it fails , i have converted the same to snashot standby and stopped the sync. still it fails. PDB is in read-write mode in snapshot standby .

  • karthik says:

    I get this error when i do cloning from snashot standby 19c . (both source and destiantion are pdb and 19c)

    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of Duplicate PDB command at 06/14/2021 14:28:20
    RMAN-05501: aborting duplication of target database
    RMAN-05513: cannot duplicate, control file is not current or standby

  • Hi Karthik,
    ADG is not used if the apply is stopped. Regular Data Guard can apply xor open read only, but not at the same time. Note that the definitions have changed in 18c.
    For the duplicate error, a snapshot standby should have a current controlfile, but I don’t think I tested that as we don’t need to open it read-write to clone a PDB.
    Franck.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Oracle Team
Oracle Team