Cada vez que descubro mais recursos do Multitenant, fico admirado com o número de opções que temos em mãos. O Proxy PDB é um deles. Basicamente usado como um ponto de conexão local que faz referência automática para algum PDB remoto. Neste artigo vou tentar simular a criação deste tipo de PDB em meu ambiente laboratório.
| CDB ORIGEM | CDB DESTINO | PDB ORIGEM | PDB DESTINO |
| TALAMO | SINAPSE | HIPOFISE1 | HIPOPROXY |
Conectando no CDB de destino e realizando teste do DB_LINK público que aponta para o CDB de origem (no meu caso eu já o havia criado em outro artigo):
[oracle@oel8 admin]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Mon Mar 29 05:48:47 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 * from dual@BSS;
D
-
X
Criando o Proxy PDB no ambiente de destino:
SQL> ALTER SESSION SET DB_CREATE_FILE_DEST='/oracle/SINAPSE/dados';
Session altered.
SQL> CREATE PLUGGABLE DATABASE HIPOPROXY AS PROXY FROM HIPOFISE1@BSS;
Pluggable database created.
Ao abrir o PDB, é reportado diferença de versão entre o PDB e CDB. Precisaremos realizar um datapatch. Creio que isso esteja acontecendo pois quando apliquei o PSU no CDB de origem, não fiz o datapatch no SEED:
SQL> ALTER PLUGGABLE DATABASE HIPOPROXY OPEN;
Warning: PDB altered with errors.
SQL> SELECT TIME,CAUSE,MESSAGE,TYPE FROM PDB_PLUG_IN_VIOLATIONS WHERE NAME='HIPOPROXY';
TIME
---------------------------------------------------------------------------
CAUSE
----------------------------------------------------------------
MESSAGE
--------------------------------------------------------------------------------
TYPE
---------
29-MAR-21 05.57.14.755414 AM
Parameter
CDB parameter ddl_lock_timeout mismatch: Previous 666 Current 0
WARNING
TIME
---------------------------------------------------------------------------
CAUSE
----------------------------------------------------------------
MESSAGE
--------------------------------------------------------------------------------
TYPE
---------
29-MAR-21 05.57.15.024817 AM
SQL Patch
'18.13.0.0.0 Release_Update 2012231849' is installed in the CDB but '18.3.0.0.0
Release_Update 1806280943' is installed in the PDB
ERROR
TIME
---------------------------------------------------------------------------
CAUSE
----------------------------------------------------------------
MESSAGE
--------------------------------------------------------------------------------
TYPE
---------
Aplicado com sucesso:
[oracle@oel8 dados]$ cd $ORACLE_HOME/OPatch
[oracle@oel8 OPatch]$ ./datapatch -verbose -pdbs HIPOPROXY
SQL Patching tool version 18.0.0.0.0 Production on Mon Mar 29 06:00:34 2021
Copyright (c) 2012, 2020, Oracle. All rights reserved.
Log file for this invocation: /oracle/18.0.0/base/cfgtoollogs/sqlpatch/sqlpatch_24906_2021_03_29_06_00_34/sqlpatch_invocation.log
Connecting to database...OK
Gathering database info...done
Note: Datapatch will only apply or rollback SQL fixes for PDBs
that are in an open state, no patches will be applied to closed PDBs.
Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
(Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of interim SQL patches:
Interim patch 27923415 (OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415)):
Binary registry: Installed
PDB HIPOPROXY: Applied successfully on 02-MAR-21 06.44.54.948445 AM
Current state of release update SQL patches:
Binary registry:
18.13.0.0.0 Release_Update 2012231849: Installed
PDB HIPOPROXY:
Applied 18.3.0.0.0 Release_Update 1806280943 successfully on 02-MAR-21 06.44.54.938616 AM
Adding patches to installation queue and performing prereq checks...done
Installation queue:
For the following PDBs: HIPOPROXY
No interim patches need to be rolled back
Patch 32204699 (Database Release Update : 18.13.0.0.210119 (32204699)):
Apply from 18.3.0.0.0 Release_Update 1806280943 to 18.13.0.0.0 Release_Update 2012231849
No interim patches need to be applied
Installing patches...
Patch installation complete. Total patches installed: 1
Validating logfiles...done
Patch 32204699 apply (pdb HIPOPROXY): SUCCESS
logfile: /oracle/18.0.0/base/cfgtoollogs/sqlpatch/32204699/24011084/32204699_apply_SINAPSE_HIPOPROXY_2021Mar29_06_01_32.log (no errors)
SQL Patching tool complete on Mon Mar 29 06:12:00 2021
Reiniciando o PDB:
SQL> ALTER PLUGGABLE DATABASE HIPOPROXY CLOSE;
Pluggable database altered.
SQL> ALTER PLUGGABLE DATABASE HIPOPROXY OPEN;
Pluggable database altered.
SQL> ALTER PLUGGABLE DATABASE HIPOPROXY SAVE STATE;
Pluggable database altered.
Com a consulta abaixo, podemos saber que o PDB é do tipo PROXY:
SQL> SELECT PDB_NAME, CON_ID, IS_PROXY_PDB FROM CDB_PDBS WHERE PDB_NAME='HIPOPROXY';
PDB_NAME
--------------------------------------------------------------------------------
CON_ID IS_
---------- ---
HIPOPROXY
4 YES
Checando o CDB_ID do novo PDB de destino:
SQL> select CON_ID,name from v$pdbs where name='HIPOPROXY';
CON_ID
----------
NAME
--------------------------------------------------------------------------------
4
HIPOPROXY
O interessante é que os únicos datafiles que o mesmo possui são a SYSTEM, SYSAUX e UNDO:
SQL> SELECT T.NAME TABLESPACE_NAME, D.NAME FROM V$TABLESPACE T, V$DATAFILE D WHERE T.TS# = D.TS# AND T.CON_ID=4 AND D.CON_ID=4;
TABLESPACE_NAME
------------------------------
NAME
--------------------------------------------------------------------------------
SYSTEM
/oracle/SINAPSE/dados/SINAPSE/BEAA0D2DC3C85BEBE0536B00A8C05826/datafile/o1_mf_sy
stem_j635k5qk_.dbf
SYSAUX
/oracle/SINAPSE/dados/SINAPSE/BEAA0D2DC3C85BEBE0536B00A8C05826/datafile/o1_mf_sy
saux_j635k5qq_.dbf
UNDOTBS1
TABLESPACE_NAME
------------------------------
NAME
--------------------------------------------------------------------------------
/oracle/SINAPSE/dados/SINAPSE/BEAA0D2DC3C85BEBE0536B00A8C05826/datafile/o1_mf_un
dotbs1_j635k5qq_.dbf
Para testar o recurso de Proxy PDB, vamos criar um schema no ambiente de Origem, com uma tabela:
[oracle@quiasma admin]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Mon Mar 29 06:26: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> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
TALAMO OPEN
SQL> ALTER SESSION SET CONTAINER=HIPOFISE1;
Session altered.
SQL> CREATE USER TESTE IDENTIFIED BY TESTE DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS;
User created.
SQL> GRANT CREATE SESSION, CREATE TABLE TO TESTE;
Grant succeeded.
SQL> CREATE TABLE TESTE.TABELA (DESCRICAO VARCHAR2(100));
Table created.
SQL> INSERT INTO TESTE.TABELA (DESCRICAO) VALUES ('TEXTO NA ORIGEM');
1 row created.
SQL> COMMIT;
Commit complete.
Conectando no PDB Proxy no CDB de destino, e inserindo uma linha nesta mesma tabela de teste:
[oracle@oel8 admin]$ sqlplus system/oracle@HIPOPROXY
SQL*Plus: Release 18.0.0.0.0 - Production on Mon Mar 29 06:31:46 2021
Version 18.13.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Last Successful login time: Mon Mar 29 2021 06:22:03 -03:00
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.13.0.0.0
SQL> INSERT INTO TESTE.TABELA (DESCRICAO) VALUES ('TEXTO NO DESTINO');
1 row created.
SQL> COMMIT;
Commit complete.
Consultando a tabela na origem:
[oracle@quiasma admin]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Mon Mar 29 06:32:43 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 instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
TALAMO OPEN
SQL> ALTER SESSION SET CONTAINER=HIPOFISE1;
Session altered.
SQL> SELECT * FROM TESTE.TABELA;
DESCRICAO
--------------------------------------------------------------------------------
TEXTO NA ORIGEM
TEXTO NO DESTINO
Consultando tabela no destino:
[oracle@oel8 admin]$ sqlplus system/oracle@HIPOPROXY
SQL*Plus: Release 18.0.0.0.0 - Production on Mon Mar 29 06:33:36 2021
Version 18.13.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Last Successful login time: Mon Mar 29 2021 06:31:46 -03:00
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.13.0.0.0
SQL> SELECT * FROM TESTE.TABELA;
DESCRICAO
--------------------------------------------------------------------------------
TEXTO NA ORIGEM
TEXTO NO DESTINO
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
Pingback: ALL options for creating a PDB using Create Puggable Database command – Bruno Santos da Silva