Creating a PDB by Cloning a Remote PDB

 

In this post I will be showing a simple example of how to create a PDB database through another PDB remotely.

CDB database: CBD4 with the PDB database named TECHDATABASKET2
CDB database: CBD3 where the TECHDATABASKET2new PDB will be created using the TECHDATABASKET2 PDB of the CDB4 database.

Then here are the examples:
1-The database must be CDB and be connected in the CDB$ROOT:

 
SQL> select instance_name, status from v$instance;

INSTANCE_NAME	 STATUS
---------------- ------------
cdb4		 OPEN


SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

2- Creating the service entry for the TECHDATABASKET2 PDB in TNSNAMES.ORA and performing some tests:

 
SQL> select name, open_mode from v$pdbs;

NAME			       OPEN_MODE
------------------------------ ----------
PDB$SEED		       READ ONLY
PDBCDB4 		       MOUNTED
CDBSEED 		       MOUNTED
CDBSEED2		       MOUNTED
MIG			       MOUNTED
NONCDB			       MOUNTED
TECHDATABASKET		       MOUNTED
TECHDATABASKET2 	       MOUNTED


SQL> alter pluggable database TECHDATABASKET open read write;

Pluggable database altered.

SQL> alter pluggable database TECHDATABASKET2 open read write;

Pluggable database altered.

SQL> select name from v$services;

NAME
----------------------------------------------------------------
techdatabasket2.example.com
techdatabasket.example.com
noncdb.example.com
mig.example.com
cdbseed2.example.com
cdbseed.example.com
pdbcdb4.example.com
cdb4XDB
cdb4.example.com
SYS$BACKGROUND
SYS$USERS

11 rows selected.

– Adding the entry for the service of the PDB TECHDATABASKET2 in TNSNAMES.ORA:

 
TECHDATABASKET2=
  (DESCRIPTION=
   (ADDRESS=(PROTOCOL=tcp)(HOST=dbdg.localdomain)(PORT=1539))
   (CONNECT_DATA=
     (SERVICE_NAME=techdatabasket2.example.com))

— Testing the added entry:

 
[oracle@dbdg admin]$ tnsping TECHDATABASKET2

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 15-MAR-2018 09:47:35

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=dbdg.localdomain)(PORT=1539)) (CONNECT_DATA= (SERVICE_NAME=techdatabasket2.example.com)))
OK (0 msec)
[oracle@dbdg admin]$ 

–Testing the created service connection:

 
SQL> select instance_name, status from v$instance;

INSTANCE_NAME	 STATUS
---------------- ------------
cdb4		 OPEN

SQL> conn system@TECHDATABASKET2
Enter password: 
Connected.
SQL> SHOW CON_NAME

CON_NAME
------------------------------
TECHDATABASKET2
SQL> 

3. In the cdb3 database create the Database Link that will connect to the source database CDB4:

 
SQL> SELECT INSTANCE_NAME, STATUS FROM V$INSTANCE;

INSTANCE_NAME	 STATUS
---------------- ------------
cdb3		 OPEN

SQL> CREATE DATABASE LINK TECHDATABASKET_CDB4 CONNECT TO SYSTEM IDENTIFIED BY oracle4_U USING 'TECHDATABASKET2';

Database link created.


SQL>  SELECT * FROM DUAL@TECHDATABASKET_CDB4;

D
-
X

4- In the CDB4 database configure the PDB TECHDATABASKET2 in READ ONLY mode:

 
SQL>  alter pluggable database TECHDATABASKET2 close;

Pluggable database altered.

SQL> alter pluggable database TECHDATABASKET2 open read only;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME			       OPEN_MODE
------------------------------ ----------
PDB$SEED		       READ ONLY
PDBCDB4 		       MOUNTED
CDBSEED 		       MOUNTED
CDBSEED2		       MOUNTED
MIG			       MOUNTED
NONCDB			       MOUNTED
TECHDATABASKET		       MOUNTED
TECHDATABASKET2 	       READ ONLY

8 rows selected.

5- In the database CBD3 create the new PDB TECHDATABASKET2new and open it in READ WRITE mode:

 
SQL> SELECT INSTANCE_NAME, STATUS FROM V$INSTANCE;

INSTANCE_NAME	 STATUS
---------------- ------------
cdb3		 OPEN


SQL> CREATE PLUGGABLE DATABASE TECHDATABASKET2new FROM TECHDATABASKET2@TECHDATABASKET_CDB4   FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/cdb4/', '/u01/app/oracle/oradata/cdb4/new'); 

Pluggable database created.



SQL> select name, open_mode from v$pdbs;

NAME			       OPEN_MODE
------------------------------ ----------
PDB$SEED		       READ ONLY
CDBSEED300		       READ ONLY
TECHDATABASKET2NEW	       MOUNTED

SQL> ALTER PLUGGABLE DATABASE TECHDATABASKET2NEW OPEN READ WRITE;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

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

Neste post estarei mostrando um simples exemplo de como se criar um banco de dados PDB através de outro PDB remotamente.

Banco de dados CDB: CBD4 com o banco de dados PDB chamado TECHDATABASKET2
Banco de dados CDB: CBD3 onde será criado o PDB TECHDATABASKET2new utilizando o PDB TECHDATABASKET2 do banco de dados CDB4.

Sendo assim, seguem os exemplos:

1-O banco de dados deve ser CDB e estar conectado no CDB$ROOT:

 
SQL> select instance_name, status from v$instance;

INSTANCE_NAME	 STATUS
---------------- ------------
cdb4		 OPEN


SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

2- Criando a entrada de serviço para o PDB TECHDATABASKET2 no TNSNAMES.ORA e efetuando o teste da entrada configurada:

 
SQL> select name, open_mode from v$pdbs;

NAME			       OPEN_MODE
------------------------------ ----------
PDB$SEED		       READ ONLY
PDBCDB4 		       MOUNTED
CDBSEED 		       MOUNTED
CDBSEED2		       MOUNTED
MIG			       MOUNTED
NONCDB			       MOUNTED
TECHDATABASKET		       MOUNTED
TECHDATABASKET2 	       MOUNTED


SQL> alter pluggable database TECHDATABASKET open read write;

Pluggable database altered.

SQL> alter pluggable database TECHDATABASKET2 open read write;

Pluggable database altered.

SQL> select name from v$services;

NAME
----------------------------------------------------------------
techdatabasket2.example.com
techdatabasket.example.com
noncdb.example.com
mig.example.com
cdbseed2.example.com
cdbseed.example.com
pdbcdb4.example.com
cdb4XDB
cdb4.example.com
SYS$BACKGROUND
SYS$USERS

11 rows selected.

— Adicionando a entrada para o serviço do PDB TECHDATABASKET2 no TNSNAMES.ORA:

 
TECHDATABASKET2=
  (DESCRIPTION=
   (ADDRESS=(PROTOCOL=tcp)(HOST=dbdg.localdomain)(PORT=1539))
   (CONNECT_DATA=
     (SERVICE_NAME=techdatabasket2.example.com))

— Efetuando o teste da entrada adicionada:
— Testing the added entry:

 
[oracle@dbdg admin]$ tnsping TECHDATABASKET2

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 15-MAR-2018 09:47:35

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=dbdg.localdomain)(PORT=1539)) (CONNECT_DATA= (SERVICE_NAME=techdatabasket2.example.com)))
OK (0 msec)
[oracle@dbdg admin]$ 

–Testando a conexão do serviço criado:

 
SQL> select instance_name, status from v$instance;

INSTANCE_NAME	 STATUS
---------------- ------------
cdb4		 OPEN

SQL> conn system@TECHDATABASKET2
Enter password: 
Connected.
SQL> SHOW CON_NAME

CON_NAME
------------------------------
TECHDATABASKET2
SQL> 

3- No banco de dados cdb3 crie o Database Link que irá fazer conexão com o banco de dados source CDB4:

 
SQL> SELECT INSTANCE_NAME, STATUS FROM V$INSTANCE;

INSTANCE_NAME	 STATUS
---------------- ------------
cdb3		 OPEN

SQL> CREATE DATABASE LINK TECHDATABASKET_CDB4 CONNECT TO SYSTEM IDENTIFIED BY oracle4_U USING 'TECHDATABASKET2';

Database link created.


SQL>  SELECT * FROM DUAL@TECHDATABASKET_CDB4;

D
-
X

4- No banco de dados CDB4 coloque o PDB TECHDATABASKET2 no modo READ ONLY:

 
SQL>  alter pluggable database TECHDATABASKET2 close;

Pluggable database altered.

SQL> alter pluggable database TECHDATABASKET2 open read only;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME			       OPEN_MODE
------------------------------ ----------
PDB$SEED		       READ ONLY
PDBCDB4 		       MOUNTED
CDBSEED 		       MOUNTED
CDBSEED2		       MOUNTED
MIG			       MOUNTED
NONCDB			       MOUNTED
TECHDATABASKET		       MOUNTED
TECHDATABASKET2 	       READ ONLY

8 rows selected.

5- No banco de dados CBD3, crie o PDB novo chamado de TECHDATABASKET2new e abre o mesmo no modo READ WRITE:

 
SQL> SELECT INSTANCE_NAME, STATUS FROM V$INSTANCE;

INSTANCE_NAME	 STATUS
---------------- ------------
cdb3		 OPEN


SQL> CREATE PLUGGABLE DATABASE TECHDATABASKET2new FROM TECHDATABASKET2@TECHDATABASKET_CDB4   FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/cdb4/', '/u01/app/oracle/oradata/cdb4/new'); 

Pluggable database created.



SQL> select name, open_mode from v$pdbs;

NAME			       OPEN_MODE
------------------------------ ----------
PDB$SEED		       READ ONLY
CDBSEED300		       READ ONLY
TECHDATABASKET2NEW	       MOUNTED

SQL> ALTER PLUGGABLE DATABASE TECHDATABASKET2NEW OPEN READ WRITE;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

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

Related posts

Leave a Comment