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
Hi! I am Bruno, a Brazilian born and bred. Former Oracle ACE, Computer Scientist, MSc in Data Science, over ten years of experience in companies such as IBM, Epico Tech, and Playtech based in three different countries (Brazil, Hungary, and Sweden) and joined projects remotely in many others. I am super excited to show you my interest in Databases, Cloud, Data Science, Data Engineering, Bigdata, AI, Programming, Software Engineering, and data in general.
(Continue reading)