Creating a new PDB by cloning from Non-CDB (using DBLINK)

Multitenant / 14/07/2022

Nos artigos anteriores, pude explorar a criação de PDB a partir do PDB SEED ou até mesmo clonando de outros PDBs (seja remoto ou local). Agora, vou simular a criação de um a partir de um banco de dados de perfil Non-CDB, utilizando o clone remoto, usando DB_LINK, e posteriormente fazendo a sua conversão para PDB. Para isso, criarei uma string de conexão no destino que aponte para o banco de origem:

BANCO ORIGEM (NON-CDB)BANCO DESTINO (CDB$ROOT)
RMANDBTALAMO
[oracle@oel8 admin]$ pwd
/oracle/18.0.0/product/network/admin
[oracle@oel8 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/18.0.0/product/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
 
TALAMO =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oel8.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TALAMO.localdomain)
    )
  )
 
LISTENER_TALAMO =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oel8.localdomain)(PORT = 1521))
 
 
RMANDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.107)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = RMANDB)
    )
  )
[oracle@oel8 admin]$ tnsping RMANDB
 
TNS Ping Utility for Linux: Version 18.0.0.0.0 - Production on 20-MAR-2021 04:51:20
 
Copyright (c) 1997, 2018, Oracle.  All rights reserved.
 
Used parameter files:
/oracle/18.0.0/product/network/admin/sqlnet.ora
 
 
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.107)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = RMANDB)))
OK (0 msec)

Antes de criar o DB_LINK, vou providenciar o usuário de interface que será usado na origem. O privilégio necessário para o mesmo é o CREATE PLUGGABLE DATABASE, para evitar o erro abaixo:

SQL> CREATE PLUGGABLE DATABASE RMANDB FROM RMANDB@BSS;
CREATE PLUGGABLE DATABASE RMANDB FROM RMANDB@BSS
*
ERROR at line 1:
ORA-17627: ORA-28002: the password will expire within 7 days
ORA-17629: Cannot connect to the remote database server

SQL> SELECT NAME,CDB FROM V$DATABASE;
 
NAME      CDB
--------- ---
RMANDB    NO
 
SQL> CREATE USER TESTE IDENTIFIED BY TESTE ACCOUNT UNLOCK;
 
User created.
 
SQL> GRANT DBA TO TESTE;
 
Grant succeeded.
 
SQL> GRANT CREATE PLUGGABLE DATABASE TO TESTE;
 
Grant succeeded.

Criando o DB_LINK do CDB$ROOT apontando para o banco origem (RMANDB):

[oracle@oel8 admin]$ sqlplus / as sysdba
 
SQL*Plus: Release 18.0.0.0.0 - Production on Sat Mar 20 04:45:41 2021
Version 18.3.0.0.0
 
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
 
SQL> CREATE DATABASE LINK BSS CONNECT TO TESTE IDENTIFIED BY TESTE USING 'RMANDB';
 
Database link created.
 
SQL> SELECT * FROM DUAL@BSS;
 
D
-
X

Caso o recurso de OMF não esteja habilitado (o que não é meu caso), é importante mapear o diretório de todos os datafiles do banco de origem:

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES@BSS ORDER BY 1;
 
FILE_NAME
--------------------------------------------------------------------------------
/oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf
/oracle/dados/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf
/oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf
/oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf
/oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf

Criando o PDB via db_link:

SQL> CREATE PLUGGABLE DATABASE RMANDB FROM RMANDB@BSS;
 
Pluggable database created.

Para fazer a conversão do banco Non-CDB para PDB, devemos rodar o script abaixo, no PDB criado:

SQL> ALTER SESSION SET CONTAINER=RMANDB;
 
Session altered.
 
SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 100
SQL> SET VERIFY OFF
SQL>
SQL> -- save settings
SQL> STORE SET ncdb2pdb.settings.sql REPLACE
Wrote file ncdb2pdb.settings.sql
SQL>
SQL> SET TIME ON
05:53:16 SQL> SET TIMING ON
05:53:16 SQL>
05:53:16 SQL> WHENEVER SQLERROR EXIT;
05:53:16 SQL>
05:53:16 SQL> DOC
05:53:16 DOC>#######################################################################
05:53:16 DOC>#######################################################################
05:53:16 DOC>   The following statement will cause an "ORA-01403: no data found"
05:53:16 DOC>   error if we're not in a PDB.
05:53:16 DOC>   This script is intended to be run right after plugin of a PDB,
05:53:16 DOC>   while inside the PDB.
05:53:16 DOC>#######################################################################
05:53:16 DOC>#######################################################################
05:53:16 DOC>#
05:53:16 SQL>
05:53:16 SQL> VARIABLE cdbname VARCHAR2(128)
05:53:16 SQL> VARIABLE pdbname VARCHAR2(128)
05:53:16 SQL> BEGIN
05:53:16   2    SELECT sys_context('USERENV', 'CDB_NAME')
05:53:16   3      INTO :cdbname
05:53:16   4      FROM dual
05:53:16   5      WHERE sys_context('USERENV', 'CDB_NAME') is not null;
05:53:16   6    SELECT sys_context('USERENV', 'CON_NAME')
05:53:16   7      INTO :pdbname
05:53:16   8      FROM dual
05:53:16   9      WHERE sys_context('USERENV', 'CON_NAME') <> 'CDB$ROOT';
05:53:16  10  END;
05:53:16  11  /
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.00
05:53:16 SQL>
05:53:16 SQL> @@?/rdbms/admin/loc_to_common0.sql
05:53:16 SQL> Rem
05:53:16 SQL> Rem $Header: rdbms/admin/loc_to_common0.sql /main/5 2017/04/25 08:09:06 thbaby Exp $
05:53:16 SQL> Rem
05:53:16 SQL> Rem loc_to_common0.sql
05:53:16 SQL> Rem
05:53:16 SQL> Rem Copyright (c) 2015, 2017, Oracle and/or its affiliates.
05:53:16 SQL> Rem All rights reserved.
05:53:16 SQL> Rem
05:53:16 SQL> Rem    NAME
05:53:16 SQL> Rem      loc_to_common0.sql - <one-line expansion of the name>
05:53:16 SQL> Rem
05:53:16 SQL> Rem    DESCRIPTION
05:53:16 SQL> Rem      <short description of component this file declares/defines>
05:53:16 SQL> Rem
05:53:16 SQL> Rem    NOTES
05:53:16 SQL> Rem      <other useful comments, qualifications, etc.>
05:53:16 SQL> Rem
05:53:16 SQL> Rem    BEGIN SQL_FILE_METADATA
05:53:16 SQL> Rem    SQL_SOURCE_FILE: rdbms/admin/loc_to_common0.sql
05:53:16 SQL> Rem    SQL_SHIPPED_FILE: rdbms/admin/loc_to_common0.sql
05:53:16 SQL> Rem    SQL_PHASE: LOC_TO_COMMON0
05:53:16 SQL> Rem    SQL_STARTUP_MODE: NORMAL
05:53:16 SQL> Rem    SQL_IGNORABLE_ERRORS: NONE
05:53:16 SQL> Rem    SQL_CALLING_FILE:
05:53:16 SQL> Rem    END SQL_FILE_METADATA
05:53:16 SQL> Rem
05:53:16 SQL> Rem    MODIFIED   (MM/DD/YY)
05:53:16 SQL> Rem    thbaby      04/21/17 - Bug 25940936: set _enable_view_pdb
05:53:16 SQL> Rem    sankejai    01/22/16 - 16076261: session parameters scoped to container
05:53:16 SQL> Rem    pyam        12/22/15 - 21927236: rename pdb_to_fedpdb to pdb_to_apppdb
05:53:16 SQL> Rem    pyam        09/22/15 - 20959267: check for version mismatch
05:53:16 SQL> Rem    pyam        07/15/15 - Created
05:53:16 SQL> Rem
05:53:16 SQL>
05:53:16 SQL> SET ECHO ON
05:53:16 SQL> SET FEEDBACK 1
05:53:16 SQL> SET NUMWIDTH 10
05:53:16 SQL> SET LINESIZE 80
05:53:16 SQL> SET TRIMSPOOL ON
05:53:16 SQL> SET TAB OFF
05:53:16 SQL> SET PAGESIZE 100
05:53:16 SQL>
05:53:16 SQL> COLUMN pdbname NEW_VALUE pdbname
05:53:16 SQL> COLUMN pdbid NEW_VALUE pdbid
05:53:16 SQL>
05:53:16 SQL>
05:53:16 SQL> alter session set "_enable_view_pdb"=false;
 
Session altered.
 
Elapsed: 00:00:00.00
05:53:16 SQL> select :pdbname pdbname from dual;
 
PDBNAME
--------------------------------------------------------------------------------
RMANDB
 
1 row selected.
 
Elapsed: 00:00:00.00
05:53:16 SQL>
05:53:16 SQL> select TO_CHAR(con_id) pdbid from v$pdbs where name='&pdbname';
 
PDBID
----------------------------------------
5
 
1 row selected.
 
Elapsed: 00:00:00.00
05:53:16 SQL>
05:53:16 SQL> -- save pluggable database open mode
05:53:16 SQL> COLUMN open_state_col NEW_VALUE open_sql;
05:53:16 SQL> COLUMN restricted_col NEW_VALUE restricted_state;
05:53:16 SQL> SELECT decode(open_mode,
05:53:16   2                'READ ONLY', 'ALTER PLUGGABLE DATABASE &pdbname OPEN READ ONLY',
05:53:16   3                'READ WRITE', 'ALTER PLUGGABLE DATABASE &pdbname OPEN',
05:53:16   4                'MIGRATE', 'ALTER PLUGGABLE DATABASE &pdbname OPEN UPGRADE', '')
05:53:16   5           open_state_col,
05:53:16   6         decode(restricted, 'YES', 'RESTRICTED', '')
05:53:16   7           restricted_col
05:53:16   8         from v$pdbs where name='&pdbname';
 
OPEN_STATE_COL                                 RESTRICTED
---------------------------------------------- ----------
 
 
1 row selected.
 
Elapsed: 00:00:00.00
05:53:16 SQL>
05:53:16 SQL> alter session set container=CDB$ROOT;
 
Session altered.
 
Elapsed: 00:00:00.01
05:53:16 SQL>
05:53:16 SQL> -- if pdb was already closed, don't exit on error
05:53:16 SQL> WHENEVER SQLERROR CONTINUE;
05:53:16 SQL> alter pluggable database "&pdbname" close immediate instances=all;
alter pluggable database "RMANDB" close immediate instances=all
*
ERROR at line 1:
ORA-65020: pluggable database RMANDB already closed
 
 
Elapsed: 00:00:00.00
05:53:16 SQL> WHENEVER SQLERROR EXIT;
05:53:16 SQL>
05:53:16 SQL> alter pluggable database "&pdbname" open upgrade;
alter pluggable database "RMANDB" open upgrade
*
ERROR at line 1:
ORA-44303: service name exists
ORA-44775: Pluggable database service cannot be created.
 
 
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
[oracle@oel8 admin]$

Já foi possível perceber que a conversão não foi bem sucedida, e a tentativa de abertura do PDB só enfatiza mais isso.

SQL> ALTER PLUGGABLE DATABASE RMANDB OPEN;
ALTER PLUGGABLE DATABASE RMANDB OPEN
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00060: deadlock detected while waiting for resource

Após uma pesquisa no MOS, vi que este problema ocorre devido o BUG 28030160, que é reportado no Oracle Note “ORA-604 Signalled During: Alter Pluggable Database “<dbname>” Open Upgrade (Doc ID 2679604.1)“. Link AQUI. Nele, é sugerido que o patch 28030160 seja aplicado, mas como está frequente os eventos de bug em meus testes, decidi aplicar o último PSU JAN21 no meu Oracle (descrito NESTE artigo). Após isso, dropei o PDB e tentei o processo novamente:

SQL> ALTER PLUGGABLE DATABASE RMANDB CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE RMANDB CLOSE IMMEDIATE
*
ERROR at line 1:
ORA-65020: pluggable database RMANDB already closed
 
 
SQL> DROP PLUGGABLE DATABASE RMANDB INCLUDING DATAFILES;
 
Pluggable database dropped.

Criando novamente:

SQL> CREATE PLUGGABLE DATABASE RMANDB FROM RMANDB@BSS;
 
Pluggable database created.

Mesmo assim, a mensagem abaixo é reportada:

Elapsed: 00:00:00.00
11:12:33 SQL> WHENEVER SQLERROR EXIT;
11:12:33 SQL>
11:12:33 SQL> alter pluggable database "&pdbname" open upgrade;
alter pluggable database "RMANDB" open upgrade
*
ERROR at line 1:
ORA-44303: service name exists
ORA-44775: Pluggable database service cannot be created.

Percebi que realmente já existe um service criado com este nome:

[oracle@oel8 admin]$ lsnrctl service
 
LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 20-MAR-2021 11:18:37
 
Copyright (c) 1991, 2020, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel8.localdomain)(PORT=1521)))
Services Summary...
Service "TALAMO.localdomain" has 1 instance(s).
  Instance "TALAMO", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "TALAMOXDB.localdomain" has 1 instance(s).
  Instance "TALAMO", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: oel8.localdomain, pid: 10416>
         (ADDRESS=(PROTOCOL=tcp)(HOST=oel8.localdomain)(PORT=15677))
Service "bdd9ab0055da2c52e0536b00a8c09fce.localdomain" has 1 instance(s).
  Instance "TALAMO", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "bdf964dbf4e43ff4e0536a00a8c01d8e.localdomain" has 1 instance(s).
  Instance "TALAMO", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "hipofise2.localdomain" has 1 instance(s).
  Instance "TALAMO", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "rmandb.localdomain" has 1 instance(s).
  Instance "TALAMO", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully

Desse modo, e até ciente do Oracle Note “Bug 25872127 – opening a pluggable database fails with “ORA-44303: service name exists” & ORA-44775 (Doc ID 25872127.8)“, vou dropar o banco e criá-lo com um nome diferente, para que um novo serviço também seja criado (já que eu não consigo remover o atual):

SQL> DROP PLUGGABLE DATABASE RMANDB INCLUDING DATAFILES;
 
Pluggable database dropped.

Criando:

SQL> CREATE PLUGGABLE DATABASE RMANDB777 FROM RMANDB@BSS;
 
Pluggable database created.

Convertendo (finalmente com sucesso):

SQL> ALTER SESSION SET CONTAINER=RMANDB777;
 
Session altered.
 
SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 100
SQL> SET VERIFY OFF
SQL>
SQL> -- save settings
SQL> STORE SET ncdb2pdb.settings.sql REPLACE
Wrote file ncdb2pdb.settings.sql
SQL>
SQL> SET TIME ON
11:28:26 SQL> SET TIMING ON
11:28:26 SQL>
11:28:26 SQL> WHENEVER SQLERROR EXIT;
11:28:26 SQL>
11:28:26 SQL> DOC
11:28:26 DOC>#######################################################################
11:28:26 DOC>#######################################################################
11:28:26 DOC>   The following statement will cause an "ORA-01403: no data found"
11:28:26 DOC>   error if we're not in a PDB.
11:28:26 DOC>   This script is intended to be run right after plugin of a PDB,
11:28:26 DOC>   while inside the PDB.
11:28:26 DOC>#######################################################################
11:28:26 DOC>#######################################################################
11:28:26 DOC>#
11:28:26 SQL>
11:28:26 SQL> VARIABLE cdbname VARCHAR2(128)
11:28:26 SQL> VARIABLE pdbname VARCHAR2(128)
11:28:26 SQL> BEGIN
11:28:26   2    SELECT sys_context('USERENV', 'CDB_NAME')
11:28:26   3      INTO :cdbname
11:28:26   4      FROM dual
11:28:26   5      WHERE sys_context('USERENV', 'CDB_NAME') is not null;
11:28:26   6    SELECT sys_context('USERENV', 'CON_NAME')
11:28:26   7      INTO :pdbname
11:28:26   8      FROM dual
11:28:26   9      WHERE sys_context('USERENV', 'CON_NAME') <> 'CDB$ROOT';
11:28:26  10  END;
11:28:26  11  /
 
PL/SQL procedure successfully completed.
 
... 
 
12:04:38 SQL> set sqlblanklines OFF
12:04:38 SQL> set sqlcase MIXED
12:04:38 SQL> set sqlcontinue "> "
12:04:38 SQL> set sqlnumber ON
12:04:38 SQL> set sqlpluscompatibility 18.0.0
12:04:38 SQL> set sqlprefix "#"
12:04:38 SQL> set sqlprompt "SQL> "
12:04:38 SQL> set sqlterminator ";"
12:04:38 SQL> set statementcache 0
12:04:38 SQL> set suffix "sql"
12:04:38 SQL> set tab OFF
12:04:38 SQL> set termout ON
12:04:38 SQL> set time OFF
SQL> set timing OFF
SQL> set trimout ON
SQL> set trimspool ON
SQL> set underline "-"
SQL> set verify OFF
SQL> set wrap ON
SQL> set xmloptimizationcheck OFF
SQL>
SQL> SQL> SQL> SQL>
SQL>

Abrindo o PDB:

SQL> ALTER SESSION SET CONTAINER=CDB$ROOT;
 
Session altered.
 
SQL> ALTER PLUGGABLE DATABASE RMANDB777 OPEN;
 
Pluggable database altered.

Realizando validações:

SQL> SELECT PDB_NAME, STATUS FROM DBA_PDBS;
 
PDB_NAME
--------------------------------------------------------------------------------
STATUS
----------
HIPOFISE1
UNPLUGGED
 
PDB$SEED
NORMAL
 
HIPOFISE2
NORMAL
 
RMANDB777
NORMAL
 
 
4 rows selected.

Obs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laboratório pessoal para fins de aprendizado.

1 thought on “Creating a new PDB by cloning from Non-CDB (using DBLINK)”

  1. Pingback: ALL options for creating a PDB using Create Puggable Database command – Bruno Santos da Silva

Leave a Comment

Your email address will not be published.

pt Portuguese
en Englishpt Portuguesees Spanish