Tag Archives: 12c clone pdb

CLONE AN EXISTING PDB AS NON-SYS USER

 

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:

Home

Oracle 12c Index

12c: Plug In 12c Non-CDB as PDB