I like the Oracle Multitenant architecture which was introduced in 12.1. But the concept to clone a source database to multiple copies in a small step had one big problem. In Oracle 12.1, to clone a pluggable database the source database had to be in state read-only.
In 12.2 is it not longer necessary to set the source pluggable database in state read-only, the source database has not to be modified to create a clone. Oracle calls it in the documentation “Hot Clone”.
Link to the official Oracle documentation: https://docs.oracle.com/database/122/ADMIN/creating-and-removing-pdbs-with-sql-plus.htm#ADMIN13584
Here is a clone of the pluggable database PDB1 into PDB2 in the Oracle Database Cloud Service.
Verify existing Pluggable Databases and States – PDB1 is in state READ-WRITE
SQL> SHOW pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
Verify if Oracle Managed Files are in use
Using Oracle Managed Files makes the file name convert much easier. You don’t have to care about files and directories. Like the feature says, Oracle is managing that for you.
SQL> show parameter db_create_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string /u02/app/oracle/oradata
Create TEMP directory
Is it a bug or is it a feature? The directory for the temporary datafile will not be created, we have to do it manually.
SQL> !mkdir /u04/app/oracle/oradata/BERGER6/PDB2
Error message when the directory is not created in advanced when a clone is started:
ERROR at line 1: ORA-01119: error in creating database file '/u04/app/oracle/oradata/BERGER6/PDB3/temp012016-10-04_11-34-07-330-AM.dbf' ORA-27040: file create error, unable to create file Linux-x86_64 Error: 2: No such file or directory Additional information: 1 ORA-06512: at line 2
Clone PDB1 into PDB2
SQL> BEGIN
2 EXECUTE IMMEDIATE 'CREATE PLUGGABLE DATABASE "PDB2" FROM "PDB1"
3 STORAGE UNLIMITED
4 TEMPFILE REUSE
5 FILE_NAME_CONVERT=(''PDB1'',''PDB2'')
6 KEYSTORE IDENTIFIED BY "<my_cloud_sys_password>"';
7 END;
8 /
PL/SQL procedure successfully completed.
Attention: Databases in the Oracle Database Cloud Service are created “Secure by Default”, that means that Transparent Data Encryption (TDE) is enabled. This is why I need the KEYSTORE INDENTIFIED BY command in line 6.
Verify PDB2 state
The cloned PDB2 is in state MOUNTED after the clone procedure.
SQL> SHOW pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PDB2 MOUNTED
Open PDB2
Now the pluggable database PDB2 is ready to use, just open it. For more information how TDE works in a multitenant environment (export key etc.) take a look here: http://docs.oracle.com/database/122/ASOAG/using-transparent-data-encryption-with-other-oracle-features.htm#ASOAG10353
SQL> ALTER PLUGGABLE DATABASE PDB2 OPEN READ WRITE;