Neste artigo vamos constatar um pr\u00e9-requisito necess\u00e1rio no ambiente OCI para a cria\u00e7\u00e3o de novas tablespaces\/PDBs em um DB-System, que seria a utiliza\u00e7\u00e3o do recurso de TDE (Transparent Data Encryption), conforme cen\u00e1rio a seguir.<\/p>\n\n\n\n
Validando ambiente vigente:<\/p>\n\n\n
\n[opc@luxor ~]$ sudo su - oracle\nLast login: Tue May 18 08:19:12 UTC 2021\n[oracle@luxor ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 12.2.0.1.0 Production on Tue May 18 08:19:19 2021\n \nCopyright (c) 1982, 2016, Oracle. All rights reserved.\n \n \nConnected to:\nOracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production\n \nSQL> SELECT NAME,OPEN_MODE,CDB FROM V$DATABASE;\n \nNAME OPEN_MODE CDB\n--------- -------------------- ---\nCORTEX READ WRITE YES\n \n[oracle@luxor ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 12.2.0.1.0 Production on Tue May 18 08:20:44 2021\n \nCopyright (c) 1982, 2016, Oracle. All rights reserved.\n \n \nConnected to:\nOracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production\n \nSQL> SELECT NAME,OPEN_MODE FROM V$PDBS;\n \nNAME\n--------------------------------------------------------------------------------\nOPEN_MODE\n----------\nPDB$SEED\nREAD ONLY\n \nHIPOFISE1\nREAD WRITE\n<\/pre><\/div>\n\n\nCriando um novo PDB, definindo que sua tablespace ser\u00e1 a USERS:<\/p>\n\n\n
\nSQL> CREATE PLUGGABLE DATABASE HIPOFISE2 ADMIN USER BSS IDENTIFIED BY "Teste#123" STORAGE (MAXSIZE 2G) DEFAULT TABLESPACE USERS DATAFILE '+DATA' SIZE 10M AUTOEXTEND ON;\n \nPluggable database created.\n<\/pre><\/div>\n\n\nValidando status do novo PDB e abrindo-o:<\/p>\n\n\n
\nSQL> col PDB_NAME format a10\nSQL> SELECT PDB_ID,PDB_NAME,STATUS FROM CDB_PDBS;\n \n PDB_ID PDB_NAME STATUS\n---------- ---------- ----------\n 3 HIPOFISE1 NORMAL\n 2 PDB$SEED NORMAL\n 4 HIPOFISE2 NEW\n \nSQL> ALTER PLUGGABLE DATABASE HIPOFISE2 OPEN;\n \nPluggable database altered.\n \nSQL> SELECT PDB_ID,PDB_NAME,STATUS FROM CDB_PDBS;\n \n PDB_ID PDB_NAME STATUS\n---------- ---------- ----------\n 3 HIPOFISE1 NORMAL\n 2 PDB$SEED NORMAL\n 4 HIPOFISE2 NORMAL\n \nSQL> ALTER PLUGGABLE DATABASE HIPOFISE2 SAVE STATE;\n \nPluggable database altered.\n<\/pre><\/div>\n\n\nPor\u00e9m, ao checar os seus datafiles, percebemos que a tablespace USERS n\u00e3o foi criada:<\/p>\n\n\n
\nSQL> ALTER SESSION SET CONTAINER=HIPOFISE2;\n \nSession altered.\n \nSQL> SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES;\n \nTABLESPACE_NAME\n------------------------------\nFILE_NAME\n--------------------------------------------------------------------------------\nSYSTEM\n+DATA\/CORTEX_PHX1HB\/C2AA53D540381FFEE0539501000ACE3B\/DATAFILE\/system.276.1072940\n197\n \nSYSAUX\n+DATA\/CORTEX_PHX1HB\/C2AA53D540381FFEE0539501000ACE3B\/DATAFILE\/sysaux.277.1072940\n197\n \nUNDOTBS1\n \nTABLESPACE_NAME\n------------------------------\nFILE_NAME\n--------------------------------------------------------------------------------\n+DATA\/CORTEX_PHX1HB\/C2AA53D540381FFEE0539501000ACE3B\/DATAFILE\/undotbs1.278.10729\n40197\n<\/pre><\/div>\n\n\nVamos tentar criar a tablespace de forma isolada para ver a mensagem que \u00e9 apresentada:<\/p>\n\n\n
\nSQL> CREATE TABLESPACE USERS DATAFILE '+DATA';\nCREATE TABLESPACE USERS DATAFILE '+DATA'\n*\nERROR at line 1:\nORA-28374: typed master key not found in wallet\n<\/pre><\/div>\n\n\nPara ajustar essa quest\u00e3o, precisaremos usar o utilit\u00e1rio DBCLI, com o usu\u00e1rio root. Primeiro, vamos coletar as informa\u00e7\u00f5es m\u00ednimas do ambiente:<\/p>\n\n\n
\n[oracle@luxor ~]$ exit\nlogout\n[opc@luxor ~]$ sudo su -\nLast login: Tue May 18 08:24:43 UTC 2021\n[root@luxor ~]# dbcli list-databases\n \nID DB Name DB Type DB Version CDB Class Shape Storage Status DbHomeID \n---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ----------------------------------------\n4c906765-68ea-4e05-ba3f-c54152f912cf CORTEX Si 12.2.0.1.201020 true Oltp ASM Configured 8bc7f0a5-6230-4b99-b568-b73cf0038525\n<\/pre><\/div>\n\n\nUsando o ID do passo anterior, podemos criar uma chave para o PDB HIPOFISE2, passando uma senha do DTE. Este processo gerar\u00e1 um “job” da cria\u00e7\u00e3o da chave e atualiza\u00e7\u00e3o do Keystore:<\/p>\n\n\n
\n[root@luxor ~]# dbcli update-tdekey -i 4c906765-68ea-4e05-ba3f-c54152f912cf -p -n HIPOFISE2\nTDE Admin wallet password:\n{\n "jobId" : "abb714a5-67c4-4b76-a78a-96496e7aacc7",\n "status" : "Created",\n "message" : null,\n "reports" : [ ],\n "createTimestamp" : "May 19, 2021 07:17:46 AM UTC",\n "resourceList" : [ ],\n "description" : "TDE update CORTEX - PDBs: [HIPOFISE2]",\n "updatedTime" : "May 19, 2021 07:17:49 AM UTC",\n "percentageProgress" : "0%"\n}\n<\/pre><\/div>\n\n\nGostaria de pontuar que \u00e9 importante informar a senha correta da DTE, para que n\u00e3o ocorra o erro abaixo (que eu experimentei e fiquei bons minutos para descobrir que a senha estava errada):<\/p>\n\n\n
\nJob details\n----------------------------------------------------------------\n ID: 8a727e50-00be-4956-8ef2-a965479e8be7\n Description: TDE update CORTEX - PDBs: [HIPOFISE2]\n Status: Failure\n Created: May 19, 2021 6:59:17 AM UTC\n Progress: 71%\n Message: DCS-10101:Failed to rotate TDE key: CORTEXADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE identified by ******** WITH BACKUP USING 'OdaRotateKey' container=current\n*\nERROR at line 1:\nORA-46627: keystore password ********\n<\/pre><\/div>\n\n\nUma vez que utilizamos a senha DTE correta, com o comando abaixo, conseguimos coletar o JOB ID do processo que acabamos de disparar (que por sua vez j\u00e1 rodou com sucesso):<\/p>\n\n\n
\n[root@luxor ~]# dbcli list-jobs -tl 1\n \nID Description Created Status\n---------------------------------------- --------------------------------------------------------------------------- ----------------------------------- ----------\nabb714a5-67c4-4b76-a78a-96496e7aacc7 TDE update CORTEX - PDBs: [HIPOFISE2] May 19, 2021 7:17:46 AM UTC Success\n<\/pre><\/div>\n\n\nCom este JOB ID, podemos verificar os steps envolvidos no processo, o que pode ajudar muito em uma situa\u00e7\u00e3o de investiga\u00e7\u00e3o:<\/p>\n\n\n
\n[root@luxor ~]# dbcli describe-job -i abb714a5-67c4-4b76-a78a-96496e7aacc7 -l Verbose\n \nJob details\n----------------------------------------------------------------\n ID: abb714a5-67c4-4b76-a78a-96496e7aacc7\n Description: TDE update CORTEX - PDBs: [HIPOFISE2]\n Status: Success\n Created: May 19, 2021 7:17:46 AM UTC\n Progress: 100%\n Message:\n \nTask Name Start Time End Time Status\n------------------------------------------------------------------------ ----------------------------------- ----------------------------------- ----------\ntask:TaskSequential_253 May 19, 2021 7:17:49 AM UTC May 19, 2021 7:17:49 AM UTC Running\ntask:TaskZLockWrapper_239 May 19, 2021 7:17:49 AM UTC May 19, 2021 7:18:12 AM UTC Success\ntask:TaskZJsonRpcExt_240 May 19, 2021 7:17:49 AM UTC May 19, 2021 7:18:02 AM UTC Success\ntask:TaskZJsonRpcExt_242 May 19, 2021 7:18:02 AM UTC May 19, 2021 7:18:05 AM UTC Success\ntask:TaskZJsonRpcExt_244 May 19, 2021 7:18:05 AM UTC May 19, 2021 7:18:08 AM UTC Success\ntask:TaskParallel_246 May 19, 2021 7:18:08 AM UTC May 19, 2021 7:18:08 AM UTC Success\nSave password in wallet May 19, 2021 7:18:08 AM UTC May 19, 2021 7:18:08 AM UTC Success\nRotate Master Key May 19, 2021 7:18:08 AM UTC May 19, 2021 7:18:11 AM UTC Success\nCleaning password May 19, 2021 7:18:12 AM UTC May 19, 2021 7:18:12 AM UTC Success\n \n[root@luxor ~]#\n<\/pre><\/div>\n\n\nAgora podemos logar em nosso PDB e checar se a wallet est\u00e1 aberta (caso n\u00e3o esteja, devemos executar o comando “ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED \u00a0BY \u201c\u201d;\u201d):<\/p>\n\n\n
\n[root@luxor ~]# exit\nlogout\n[opc@luxor ~]$ sudo su - oracle\nLast login: Wed May 19 07:24:28 UTC 2021\n[oracle@luxor ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 12.2.0.1.0 Production on Wed May 19 07:25:26 2021\n \nCopyright (c) 1982, 2016, Oracle. All rights reserved.\n \n \nConnected to:\nOracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production\n \nSQL> SELECT STATUS FROM V$ENCRYPTION_WALLET;\n \nSTATUS\n------------------------------\nOPEN\n \nSQL> ALTER SESSION SET CONTAINER=HIPOFISE2;\n \nSession altered.\n \nSQL> SELECT STATUS FROM V$ENCRYPTION_WALLET;\n \nSTATUS\n------------------------------\nOPEN\n<\/pre><\/div>\n\n\nAgora podemos criar nossa tablespace no PDB:<\/p>\n\n\n
\nSQL> CREATE TABLESPACE USERS DATAFILE '+DATA';\n \nTablespace created.\n \nSQL> SELECT TABLESPACE_NAME,STATUS,ENCRYPTED FROM DBA_TABLESPACES;\n \nTABLESPACE_NAME STATUS ENC\n------------------------------ --------- ---\nSYSTEM ONLINE NO\nSYSAUX ONLINE NO\nUNDOTBS1 ONLINE NO\nTEMP ONLINE NO\nUSERS ONLINE YES\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":"
Neste artigo vamos constatar um pr\u00e9-requisito necess\u00e1rio no ambiente OCI para a cria\u00e7\u00e3o de novas tablespaces\/PDBs em um DB-System, que seria a utiliza\u00e7\u00e3o do recurso de TDE (Transparent Data Encryption), conforme cen\u00e1rio a seguir. Validando ambiente vigente: Criando um novo PDB, definindo que sua tablespace ser\u00e1 a USERS: Validando status do novo PDB e abrindo-o: […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8,9],"tags":[],"class_list":["post-4727","post","type-post","status-publish","format-standard","hentry","category-multitenant","category-oci"],"_links":{"self":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/4727","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=4727"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/4727\/revisions"}],"predecessor-version":[{"id":9132,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/4727\/revisions\/9132"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=4727"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=4727"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=4727"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}