Neste artigo vou simular uma situação que podemos nos deparar ao clonar um PDB de um CDB para outro, onde o PDB de origem clonado possui um common user que o CDB de destino não possui.
Criando um PDB de teste no CDB de origem:
SQL> SELECT NAME,CDB FROM V$DATABASE;
NAME CDB
--------- ---
SINAPSE YES
SQL> CREATE PLUGGABLE DATABASE SPFC ADMIN USER BSS IDENTIFIED BY oracle DEFAULT TABLESPACE USERS;
Pluggable database created.
SQL> ALTER PLUGGABLE DATABASE SPFC OPEN;
Pluggable database altered.
SQL> ALTER PLUGGABLE DATABASE SPFC SAVE STATE;
Pluggable database altered.
SQL> SELECT NAME,OPEN_MODE FROM V$PDBS;
NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
PDB$SEED
READ ONLY
SPFC
READ WRITE
Criando um Common user no CDB de origem:
SQL> CREATE USER C##LIBERTADORES IDENTIFIED BY oracle CONTAINER=ALL;
User created.
SQL> GRANT CREATE SESSION TO C##LIBERTADORES CONTAINER=ALL;
Grant succeeded.
Adicionei a string de conexão do novo PDB no tnsnames.ora:
[oracle@oel8 admin]$ tnsping SPFC
TNS Ping Utility for Linux: Version 18.0.0.0.0 - Production on 15-APR-2021 04:14:30
Copyright (c) 1997, 2020, 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 = SPFC.localdomain)))
OK (0 msec)
Testando a conexão do Common user com o PDB de origem:
[oracle@oel8 admin]$ sqlplus C##LIBERTADORES/oracle@SPFC
SQL*Plus: Release 18.0.0.0.0 - Production on Thu Apr 15 04:15:35 2021
Version 18.13.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.13.0.0.0
SQL> SHOW USER;
USER is "C##LIBERTADORES"
No CDB de destino, me certifico que não temos nenhum PDB chamado SPFC e nem um common user chamado LIBERTADORES:
[oracle@quiasma ~]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Thu Apr 15 04:16:27 2021
Version 18.13.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.13.0.0.0
SQL> select name,cdb from v$database;
NAME CDB
--------- ---
TALAMO YES
SQL> select name,open_mode from v$pdbs where name like '%SPFC%';
no rows selected
SQL> select username from cdb_users where username like '%LIBERTADORES%';
no rows selected
Criando DBLINK no CDB de destino apontando para o CDB de origem:
[oracle@quiasma admin]$ tnsping SINAPSE
TNS Ping Utility for Linux: Version 18.0.0.0.0 - Production on 15-APR-2021 04:24:16
Copyright (c) 1997, 2020, 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 = sinapse.localdomain)))
OK (0 msec)
[oracle@quiasma admin]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Thu Apr 15 04:25:16 2021
Version 18.13.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.13.0.0.0
SQL> CREATE DATABASE LINK DBLINKBSS CONNECT TO system IDENTIFIED BY oracle USING 'SINAPSE';
Database link created.
SQL> select * from dual@DBLINKBSS;
D
-
X
Criando o PDB no destino usando o DB_LINK:
SQL> CREATE PLUGGABLE DATABASE SPFC FROM SPFC@DBLINKBSS;
CREATE PLUGGABLE DATABASE SPFC FROM SPFC@DBLINKBSS
*
ERROR at line 1:
ORA-17628: Oracle error 1031 returned by remote Oracle server
ORA-01031: insufficient privileges
O erro ocorre pois o usuário utilizado no DBLINK deve possuir privilégio de criar PDBs (mesmo sendo o SYSTEM):
[oracle@oel8 admin]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Thu Apr 15 04:27:33 2021
Version 18.13.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.13.0.0.0
SQL> GRANT CREATE PLUGGABLE DATABASE TO SYSTEM CONTAINER=ALL;
Grant succeeded.
PDB criado com sucesso:
SQL> CREATE PLUGGABLE DATABASE SPFC FROM SPFC@DBLINKBSS;
Pluggable database created.
SQL> ALTER PLUGGABLE DATABASE SPFC OPEN;
Pluggable database altered.
SQL> ALTER PLUGGABLE DATABASE SPFC SAVE STATE;
Pluggable database altered.
Adicionado string de conexão do novo PDB no tnsnames.ora:
[oracle@quiasma admin]$ tnsping SPFC
TNS Ping Utility for Linux: Version 18.0.0.0.0 - Production on 15-APR-2021 04:34:27
Copyright (c) 1997, 2020, 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_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = quiasma.localdomain)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SPFC.localdomain)))
OK (0 msec)
Logando no PDB com usuário system:
[oracle@quiasma admin]$ sqlplus system/oracle@SPFC
SQL*Plus: Release 18.0.0.0.0 - Production on Thu Apr 15 04:35:09 2021
Version 18.13.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Last Successful login time: Thu Apr 15 2021 04:18:14 -03:00
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.13.0.0.0
SQL> SHOW USER;
USER is "SYSTEM"
Tentando logar no PDB com o common user. Pior do que o erro que esperávamos (ORA-2800), nos foi reportado um ORA-600:
SQL> conn C##LIBERTADORES/oracle@SPFC;
ERROR:
ORA-00600: internal error code, arguments: [kziaVrfyAcctStatInRootCbk: !user],
[C##LIBERTADORES], [], [], [], [], [], [], [], [], [], []
Warning: You are no longer connected to ORACLE.
No alert log:
2021-04-15 04:35:57.196000 -03:00
Errors in file /oracle/18.0.0/base/diag/rdbms/talamo/TALAMO/trace/TALAMO_ora_3734.trc (incident=67475) (PDBNAME=CDB$ROOT):
ORA-00600: internal error code, arguments: [kziaVrfyAcctStatInRootCbk: !user], [C##LIBERTADORES], [], [], [], [], [], [], [], [], [], []
Incident details in: /oracle/18.0.0/base/diag/rdbms/talamo/TALAMO/incident/incdir_67475/TALAMO_ora_3734_i67475.trc
2021-04-15 04:35:58.750000 -03:00
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Dumping diagnostic data in directory=[cdmp_20210415043558], requested by (instance=1, osid=3734), summary=[incident=67475].
2021-04-15 04:36:57.053000 -03:00
Para tentarmos resolver, vou baixar o PDB:
SQL> ALTER PLUGGABLE DATABASE SPFC CLOSE IMMEDIATE;
Pluggable database altered.
Criando o common user no CDB de destino:
SQL> CREATE USER C##LIBERTADORES IDENTIFIED BY oracle CONTAINER=ALL;
User created.
SQL> GRANT CREATE SESSION TO C##LIBERTADORES CONTAINER=ALL;
Grant succeeded.
Abrindo o PDB e tentando novamente a conexão com o common user:
SQL> ALTER PLUGGABLE DATABASE SPFC OPEN;
Pluggable database altered.
SQL> CONN C##LIBERTADORES/oracle@SPFC;
Connected.
SQL> SHOW USER;
USER is "C##LIBERTADORES"
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.
Portuguese
English
Spanish