In 12c, we can create a PDB by copying an existing PDB (the source PDB) and then plugging the copy into the CDB. The files associated with the source PDB are copied to a new location and the copied files are associated with the new PDB. This operation is called cloning a PDB.
Prerequisites
– You must be connected to a CDB and the current container must be the root.
– You must have the CREATE PLUGGABLE DATABASE system privilege.
– The CDB in which the PDB is being created must be in READ WRITE mode.
– If source PDB is in the same CDB, then you must have the CREATE PLUGGABLE DATABASE system privilege in
- the root of the CDB in which the new PDB will be created and
- in the PDB being cloned.
I will demonstrate cloning of a PDB as a non-sys common user c##sys.
Current scenario:
Name of container database : CDB1
Name of source PDB in CDB1 : PDB1
Name of the PDB to be cloned from PDB1: PDB2 in CDB1
Overview:
— create a common user c##sys
— grant permissions to c##sys in all the containers in the CDB
— Put source PDB (PDB1) in read only mode
— Connect as user c##sys to cdb$root and create pluggable database pdb2 from pdb1
Implementation:
– check current pdb’s in cdb1 = pdb@seed and pdb1
SQL> select name, open_mode from v$pdbs; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY PDB1 READ WRITE
– create a common user c##sys
SQL> create user c##sys identified by oracle;
– grant permissions to c##sys in all the containers in the CDB
— Note that this step is to be carried out when the PDB to be cloned
is in read write mode so that user’s entry is made in the source
PDB’s data dictionary
SQL> grant connect, resource, create pluggable database to c##sys container=all;
– check that user c##sys is listed in souce PDB PDB1’s data dictionary
– set current container to PDB1
SQL> alter session set container=pdb1;
– check that current container = PDB1 and current user = SYS
SQL> sho con_name CON_NAME ------------------------------ PDB1 SQL> sho user USER is "SYS"
– check that user c##sys has an entry in the source PDB’s data dictionary
SQL> select username from dba_users where username like '%##%'; USERNAME -------------------------------------------------------------------------------- C##SYS
– Put source PDB (PDB1) in read only mode
SQL> conn / as sysdba sho con_name CON_NAME ------------------------------ CDB$ROOT SQL> alter pluggable database pdb1 close; alter pluggable database pdb1 open read only;
– Check that source PDB PDB1 is in read only mode
SQL> select con_id, name,open_mode from v$pdbs;
CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
2 PDB$SEED READ ONLY
3 PDB1 READ ONLY
– Find out names of datafiles of source PDb PDB1 (needed for FILE_NAME_CONVERT)
SQL> select name from v$datafile where con_id = 3; NAME -------------------------------------------------------------------------------- /u02/app/oracle/oradata/cdb1/pdb1/system01.dbf /u02/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf /u02/app/oracle/oradata/cdb1/pdb1/SAMPLE_SCHEMA_users01.dbf /u02/app/oracle/oradata/cdb1/pdb1/example01.dbf
– Connect as user c##sys to cdb$root and create pluggable database
pdb2 from pdb1
SQL> conn c##sys/oracle sho con_name CON_NAME ------------------------------ CDB$ROOT
– Try to create pluggable database pdb2 from pdb1 without specifying FILE_NAME_CONVERT – gives error
SQL> create pluggable database pdb2 from pdb1; create pluggable database pdb2 from pdb1 * ERROR at line 1: ORA-65016: FILE_NAME_CONVERT must be specified
– Try to create pluggable database pdb2 from pdb1 specifying
FILE_NAME_CONVERT – succeeds
SQL> create pluggable database pdb2 from pdb1 file_name_convert=('/u02/app/oracle/oradata/cdb1/pdb1','/u02/app/oracle/oradata/cdb1/pdb2');
Pluggable database created.
– connect as sys user to cdb1 and check that newly created pdb (PDB2) is in mounted state
SQL>conn sys/oracle@cdb1 as sysdba
select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB1 READ ONLY
PDB2 MOUNTED
– Open PDB2 and verify that it is open in read write mode
SQL> alter pluggable database pdb2 open;
select con_id, name,open_mode from v$pdbs;
CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
2 PDB$SEED READ ONLY
3 PDB1 READ ONLY
4 PDB2 READ WRITE
– check that datafiles for PDB2 are in the location as was specified
in file_name_convert clause of create pluggable database statement
SQL> select name from v$datafile where con_id = 4; NAME -------------------------------------------------------------------------------- /u02/app/oracle/oradata/cdb1/pdb2/system01.dbf /u02/app/oracle/oradata/cdb1/pdb2/sysaux01.dbf /u02/app/oracle/oradata/cdb1/pdb2/SAMPLE_SCHEMA_users01.dbf /u02/app/oracle/oradata/cdb1/pdb2/example01.dbf
— Check that a new default service has been created for the PDB. The service has the same name as the PDB and can be used to access the PDB.
SQL> col name for a10
col pdb for a10
select pdb, name from cdb_services where pdb = ‘PDB2′;
PDB NAME
———- ———-
PDB2 pdb2
– set current container to PDB2
SQL> alter session set container=pdb2; sho con_name CON_NAME ------------------------------ PDB2 SQL> sho user USER is "SYS"
– check that the data dictionary of PDB2 has automatically got entry for common user c##sys as the user was granted permissions for all the containers in CDB1
SQL> col username for a15 select username, common from dba_users where username like '%C##%'; USERNAME COM --------------- --- C##SYS YES
– check that user c##sys can connect to PDB2 as the user was granted permissions for all the containers in CDB1
SQL> conn c##sys/oracle@em12c:1522/pdb2 sho con_name CON_NAME ------------------------------ PDB2 SQL> sho user USER is "C##SYS"
References:
http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_6009.htm
http://docs.oracle.com/cd/E16655_01/server.121/e17636/cdb_plug.htm#CEGHCJDB
https://forums.oracle.com/message/11117312#11117312
—————————————————————————————
Related Links:
What’s the point of creating this account if it cannot enable the new database via alter database open? You are still dependent on sys so nothing is really gained for automation or delegation.