Outro recurso legal disponibilizado na arquitetura multitenant \u00e9 o Refreshable PDB, que nos permite atualizar as informa\u00e7\u00f5es de um PDB (que fica em read only) atrav\u00e9s de outro PDB de origem remota. Podemos usar este novo tipo de PDB por exemplo para rodar relat\u00f3rios pesados, trazendo mais f\u00f4lego para a opera\u00e7\u00e3o no pdb original.<\/p>\n\n\n\n
Para isso, precisaremos de um DB_LINK p\u00fablico no CDB de destino apontando para o CDB de origem. Como eu j\u00e1 tenho um criado por conta de outros artigos, vou simular que o mesmo est\u00e1 funcionando:<\/p>\n\n\n
\n[oracle@oel8 dados]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 18.0.0.0.0 - Production on Tue Mar 30 05:07:45 2021\nVersion 18.13.0.0.0\n \nCopyright (c) 1982, 2018, Oracle. All rights reserved.\n \n \nConnected to:\nOracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production\nVersion 18.13.0.0.0\n \nSQL> select instance_name,status from v$instance;\n \nINSTANCE_NAME STATUS\n---------------- ------------\nSINAPSE OPEN\n \nSQL> SELECT * FROM DUAL@BSS;\n \nD\n-\nX\n<\/pre><\/div>\n\n\nCriando um PDB na origem apenas para este nosso teste:<\/p>\n\n\n
\n[oracle@quiasma OPatch]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 18.0.0.0.0 - Production on Tue Mar 30 05:14:28 2021\nVersion 18.13.0.0.0\n \nCopyright (c) 1982, 2018, Oracle. All rights reserved.\n \n \nConnected to:\nOracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production\nVersion 18.13.0.0.0\n \nSQL> SELECT INSTANCE_NAME,STATUS FROM V$INSTANCE;\n \nINSTANCE_NAME STATUS\n---------------- ------------\nTALAMO OPEN\n \nSQL> CREATE PLUGGABLE DATABASE BRUNO\nADMIN USER BSS IDENTIFIED BY BSS\nDEFAULT TABLESPACE USERS\nSTORAGE (MAXSIZE 2G); 2 3 4\n \nPluggable database created.\n \nSQL> ALTER PLUGGABLE DATABASE BRUNO OPEN;\n \nPluggable database altered.\n \nSQL> ALTER PLUGGABLE DATABASE BRUNO SAVE STATE;\n \nPluggable database altered.\n<\/pre><\/div>\n\n\nCriando o Refreshable PDB no CDB de destino:<\/p>\n\n\n
\nSQL> ALTER SESSION SET DB_CREATE_FILE_DEST='\/oracle\/SINAPSE\/dados';\n \nSession altered.\n \nSQL> CREATE PLUGGABLE DATABASE BRUNOREPORT FROM BRUNO@BSS REFRESH MODE MANUAL;\n \nPluggable database created.\n<\/pre><\/div>\n\n\nComo dito, este tipo de PDB deve estar no modo Read Only (perceba que tentar abr\u00ed-lo provoca uma mensagem de erro):<\/p>\n\n\n
\nSQL> ALTER PLUGGABLE DATABASE BRUNOREPORT OPEN READ ONLY;\n \nPluggable database altered.\n \nSQL> col PDB_NAME format a10\nSQL> SELECT CON_ID, PDB_NAME, STATUS, REFRESH_MODE FROM CDB_PDBS WHERE PDB_NAME='BRUNOREPORT';\n \n CON_ID PDB_NAME STATUS REFRES\n---------- ---------- ---------- ------\n 5 BRUNOREPOR REFRESHING MANUAL\n T\n \n 5 BRUNOREPOR REFRESHING MANUAL\n T\n<\/pre><\/div>\n\n\nSQL> ALTER PLUGGABLE DATABASE BRUNOREPORT CLOSE IMMEDIATE;\n \nPluggable database altered.\n \nSQL> ALTER PLUGGABLE DATABASE BRUNOREPORT OPEN;\nALTER PLUGGABLE DATABASE BRUNOREPORT OPEN\n*\nERROR at line 1:\nORA-65341: cannot open pluggable database in read\/write mode\n \n \nSQL> ALTER PLUGGABLE DATABASE BRUNOREPORT OPEN READ ONLY;\n \nPluggable database altered.\n \nSQL> ALTER PLUGGABLE DATABASE BRUNOREPORT SAVE STATE;\n \nPluggable database altered.\n<\/pre><\/div>\n\n\nPercebemos que automaticamente j\u00e1 foi criado um servi\u00e7o para o novo PDB. Assim, podemos realizar um teste de conex\u00e3o:<\/p>\n\n\n
\n[oracle@oel8 dados]$ lsnrctl service\n \nLSNRCTL for Linux: Version 18.0.0.0.0 - Production on 30-MAR-2021 05:28:59\n \nCopyright (c) 1991, 2020, Oracle. All rights reserved.\n \nConnecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))\nServices Summary...\nService "SINAPSE.localdomain" has 1 instance(s).\n Instance "SINAPSE", status READY, has 1 handler(s) for this service...\n Handler(s):\n "DEDICATED" established:5 refused:0 state:ready\n LOCAL SERVER\nService "SINAPSEXDB.localdomain" has 1 instance(s).\n Instance "SINAPSE", status READY, has 1 handler(s) for this service...\n Handler(s):\n "D000" established:0 refused:0 current:0 max:1022 state:ready\n DISPATCHER <machine: oel8.localdomain, pid: 16750>\n (ADDRESS=(PROTOCOL=tcp)(HOST=oel8.localdomain)(PORT=16771))\nService "bdd9ab0055da2c52e0536b00a8c09fce.localdomain" has 1 instance(s).\n Instance "SINAPSE", status READY, has 1 handler(s) for this service...\n Handler(s):\n "DEDICATED" established:5 refused:0 state:ready\n LOCAL SERVER\nService "beaa0d2dc3c85bebe0536b00a8c05826.localdomain" has 1 instance(s).\n Instance "SINAPSE", status READY, has 1 handler(s) for this service...\n Handler(s):\n "DEDICATED" established:5 refused:0 state:ready\n LOCAL SERVER\nService "bebd7f6a46cb09e9e0536b00a8c077a5.localdomain" has 1 instance(s).\n Instance "SINAPSE", status READY, has 1 handler(s) for this service...\n Handler(s):\n "DEDICATED" established:5 refused:0 state:ready\n LOCAL SERVER\nService "brunoreport.localdomain" has 1 instance(s).\n Instance "SINAPSE", status READY, has 1 handler(s) for this service...\n Handler(s):\n "DEDICATED" established:5 refused:0 state:ready\n LOCAL SERVER\nService "hipofise2.localdomain" has 1 instance(s).\n Instance "SINAPSE", status READY, has 1 handler(s) for this service...\n Handler(s):\n "DEDICATED" established:5 refused:0 state:ready\n LOCAL SERVER\nService "hipoproxy.localdomain" has 1 instance(s).\n Instance "SINAPSE", status READY, has 1 handler(s) for this service...\n Handler(s):\n "DEDICATED" established:5 refused:0 state:ready\n LOCAL SERVER\nThe command completed successfully\n[oracle@oel8 dados]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 18.0.0.0.0 - Production on Tue Mar 30 05:29:27 2021\nVersion 18.13.0.0.0\n \nCopyright (c) 1982, 2018, Oracle. All rights reserved.\n \n \nConnected to:\nOracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production\nVersion 18.13.0.0.0\n \nSQL> CONN system\/oracle@\/\/oel8.localdomain:1521\/brunoreport.localdomain\nConnected.\nSQL>\n<\/pre><\/div>\n\n\nPara simular o funcionamento deste refreshable pdb, vamos criar uma nova tablespace, schema e tabela no ambiente de origem, conforme abaixo:<\/p>\n\n\n
\n[oracle@quiasma OPatch]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 18.0.0.0.0 - Production on Tue Mar 30 05:33:24 2021\nVersion 18.13.0.0.0\n \nCopyright (c) 1982, 2018, Oracle. All rights reserved.\n \n \nConnected to:\nOracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production\nVersion 18.13.0.0.0\n \nSQL> ALTER SESSION SET CONTAINER=BRUNO;\nCREATE TABLESPACE TBS_BRUNO;\nSession altered.\n \nSQL>\n \nTablespace created.\n \nSQL> CREATE USER DATAMART IDENTIFIED BY DATAMART DEFAULT TABLESPACE TBS_BRUNO QUOTA UNLIMITED ON TBS_BRUNO;\n \nUser created.\n \nSQL> GRANT CREATE SESSION, CREATE TABLE TO DATAMART;\n \nGrant succeeded.\n \nSQL> CREATE TABLE DATAMART.TESTE ( RID NUMBER );\n \nTable created.\n \nSQL> INSERT INTO DATAMART.TESTE VALUES (1);\n \n1 row created.\n \nSQL> COMMIT;\n \nCommit complete.\n \nSQL>\n<\/pre><\/div>\n\n\nJ\u00e1 no PDB de destino, vemos que as informa\u00e7\u00f5es ainda n\u00e3o foram replicadas:<\/p>\n\n\n
\nSQL> CONN system\/oracle@\/\/oel8.localdomain:1521\/brunoreport.localdomain\nConnected.\nSQL> SELECT NAME FROM V$TABLESPACE WHERE NAME='TBS_BRUNO';\n \nno rows selected\n \nSQL> SELECT * FROM DATAMART.TESTE;\nSELECT * FROM DATAMART.TESTE\n *\nERROR at line 1:\nORA-00942: table or view does not exist\n<\/pre><\/div>\n\n\nRealizando o refresh do PDB de destino:<\/p>\n\n\n
\n[oracle@oel8 dados]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 18.0.0.0.0 - Production on Tue Mar 30 05:44:17 2021\nVersion 18.13.0.0.0\n \nCopyright (c) 1982, 2018, Oracle. All rights reserved.\n \n \nConnected to:\nOracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production\nVersion 18.13.0.0.0\n \nSQL> CONN sys\/oracle@\/\/oel8.localdomain:1521\/brunoreport.localdomain as sysdba\nConnected.\nSQL> ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;\n \nPluggable database altered.\n \nSQL> !pwd\n\/oracle\/SINAPSE\/dados\n \nSQL> ALTER SESSION SET DB_CREATE_FILE_DEST='\/oracle\/SINAPSE\/dados';\n \nSession altered.\n \nSQL> ALTER PLUGGABLE DATABASE REFRESH;\n \nPluggable database altered.\n \nSQL> ALTER PLUGGABLE DATABASE OPEN READ ONLY;\n \nPluggable database altered.\n<\/pre><\/div>\n\n\nAgora podemos ver que as informa\u00e7\u00f5es de origem foram replicadas ao destino:<\/p>\n\n\n
\nSQL> SELECT NAME FROM V$TABLESPACE WHERE NAME='TBS_BRUNO';\n \nNAME\n------------------------------\nTBS_BRUNO\n \nSQL> SELECT * FROM DATAMART.TESTE;\n \n RID\n----------\n 1\n<\/pre><\/div>\n\n\nInteressante vermos o alert.log reportando a opera\u00e7\u00e3o de refresh:<\/p>\n\n\n
\n2021-03-30 05:44:58.178000 -03:00\nALTER PLUGGABLE DATABASE REFRESH\n2021-03-30 05:44:59.475000 -03:00\nApplying media recovery for pdb-4099 from SCN 3011587 to SCN 3015297\nRemote log information: count-1\nthr-1, seq-35, logfile-\/oracle\/archives\/parlog_1_35_f6398a0_1066113021.arc, los-2930063, nxs-18446744073709551615\nMedia Recovery Start\nSerial Media Recovery started\nmax_pdb is 5\nMedia Recovery Log \/oracle\/archives\/parlog_1_35_f6398a0_1066113021.arc\n2021-03-30 05:45:02.192000 -03:00\nSuccessfully added datafile 21 to media recovery\nDatafile #21: '\/oracle\/SINAPSE\/dados\/SINAPSE\/BEBD7F6A46CB09E9E0536B00A8C077A5\/datafile\/o1_mf_tbs_brun_j65s8dhp_.dbf'\nResize operation completed for file# 18, old size 378880K, new size 389120K\nIncomplete Recovery applied until change 3015297 time 03\/30\/2021 05:44:58\nMedia Recovery Complete (SINAPSE)\nCompleted: ALTER PLUGGABLE DATABASE REFRESH\n2021-03-30 05:45:08.895000 -03:00\nALTER PLUGGABLE DATABASE OPEN READ ONLY\nAutotune of undo retention is turned on.\nEndian type of dictionary set to little\nUndo initialization finished serial:0 start:91007274 end:91007274 diff:0 ms (0.0 seconds)\nDatabase Characterset for BRUNOREPORT is AL32UTF8\nOpening pdb with no Resource Manager plan active\nPluggable database BRUNOREPORT opened read only\nCompleted: ALTER PLUGGABLE DATABASE OPEN READ ONLY\n<\/pre><\/div>\n\n\nObs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laborat\u00f3rio pessoal para fins de aprendizado.<\/p>\n","protected":false},"excerpt":{"rendered":"
Outro recurso legal disponibilizado na arquitetura multitenant \u00e9 o Refreshable PDB, que nos permite atualizar as informa\u00e7\u00f5es de um PDB (que fica em read only) atrav\u00e9s de outro PDB de origem remota. Podemos usar este novo tipo de PDB por exemplo para rodar relat\u00f3rios pesados, trazendo mais f\u00f4lego para a opera\u00e7\u00e3o no pdb original. Para […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[],"class_list":["post-3333","post","type-post","status-publish","format-standard","hentry","category-multitenant"],"_links":{"self":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/3333","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/comments?post=3333"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/3333\/revisions"}],"predecessor-version":[{"id":9196,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/3333\/revisions\/9196"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=3333"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=3333"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=3333"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}