Neste artigo vamos constatar um pré-requisito necessário no ambiente OCI para a criação de novas tablespaces/PDBs em um DB-System, que seria a utilização do recurso de TDE (Transparent Data Encryption), conforme cenário a seguir.
Validando ambiente vigente:
[opc@luxor ~]$ sudo su - oracle
Last login: Tue May 18 08:19:12 UTC 2021
[oracle@luxor ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Tue May 18 08:19:19 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production
SQL> SELECT NAME,OPEN_MODE,CDB FROM V$DATABASE;
NAME OPEN_MODE CDB
--------- -------------------- ---
CORTEX READ WRITE YES
[oracle@luxor ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Tue May 18 08:20:44 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production
SQL> SELECT NAME,OPEN_MODE FROM V$PDBS;
NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
PDB$SEED
READ ONLY
HIPOFISE1
READ WRITE
Criando um novo PDB, definindo que sua tablespace será a USERS:
SQL> CREATE PLUGGABLE DATABASE HIPOFISE2 ADMIN USER BSS IDENTIFIED BY "Teste#123" STORAGE (MAXSIZE 2G) DEFAULT TABLESPACE USERS DATAFILE '+DATA' SIZE 10M AUTOEXTEND ON;
Pluggable database created.
Validando status do novo PDB e abrindo-o:
SQL> col PDB_NAME format a10
SQL> SELECT PDB_ID,PDB_NAME,STATUS FROM CDB_PDBS;
PDB_ID PDB_NAME STATUS
---------- ---------- ----------
3 HIPOFISE1 NORMAL
2 PDB$SEED NORMAL
4 HIPOFISE2 NEW
SQL> ALTER PLUGGABLE DATABASE HIPOFISE2 OPEN;
Pluggable database altered.
SQL> SELECT PDB_ID,PDB_NAME,STATUS FROM CDB_PDBS;
PDB_ID PDB_NAME STATUS
---------- ---------- ----------
3 HIPOFISE1 NORMAL
2 PDB$SEED NORMAL
4 HIPOFISE2 NORMAL
SQL> ALTER PLUGGABLE DATABASE HIPOFISE2 SAVE STATE;
Pluggable database altered.
Porém, ao checar os seus datafiles, percebemos que a tablespace USERS não foi criada:
SQL> ALTER SESSION SET CONTAINER=HIPOFISE2;
Session altered.
SQL> SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES;
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
SYSTEM
+DATA/CORTEX_PHX1HB/C2AA53D540381FFEE0539501000ACE3B/DATAFILE/system.276.1072940
197
SYSAUX
+DATA/CORTEX_PHX1HB/C2AA53D540381FFEE0539501000ACE3B/DATAFILE/sysaux.277.1072940
197
UNDOTBS1
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
+DATA/CORTEX_PHX1HB/C2AA53D540381FFEE0539501000ACE3B/DATAFILE/undotbs1.278.10729
40197
Vamos tentar criar a tablespace de forma isolada para ver a mensagem que é apresentada:
SQL> CREATE TABLESPACE USERS DATAFILE '+DATA';
CREATE TABLESPACE USERS DATAFILE '+DATA'
*
ERROR at line 1:
ORA-28374: typed master key not found in wallet
Para ajustar essa questão, precisaremos usar o utilitário DBCLI, com o usuário root. Primeiro, vamos coletar as informações mínimas do ambiente:
[oracle@luxor ~]$ exit
logout
[opc@luxor ~]$ sudo su -
Last login: Tue May 18 08:24:43 UTC 2021
[root@luxor ~]# dbcli list-databases
ID DB Name DB Type DB Version CDB Class Shape Storage Status DbHomeID
---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ----------------------------------------
4c906765-68ea-4e05-ba3f-c54152f912cf CORTEX Si 12.2.0.1.201020 true Oltp ASM Configured 8bc7f0a5-6230-4b99-b568-b73cf0038525
Usando o ID do passo anterior, podemos criar uma chave para o PDB HIPOFISE2, passando uma senha do DTE. Este processo gerará um “job” da criação da chave e atualização do Keystore:
[root@luxor ~]# dbcli update-tdekey -i 4c906765-68ea-4e05-ba3f-c54152f912cf -p -n HIPOFISE2
TDE Admin wallet password:
{
"jobId" : "abb714a5-67c4-4b76-a78a-96496e7aacc7",
"status" : "Created",
"message" : null,
"reports" : [ ],
"createTimestamp" : "May 19, 2021 07:17:46 AM UTC",
"resourceList" : [ ],
"description" : "TDE update CORTEX - PDBs: [HIPOFISE2]",
"updatedTime" : "May 19, 2021 07:17:49 AM UTC",
"percentageProgress" : "0%"
}
Gostaria de pontuar que é importante informar a senha correta da DTE, para que não ocorra o erro abaixo (que eu experimentei e fiquei bons minutos para descobrir que a senha estava errada):
Job details
----------------------------------------------------------------
ID: 8a727e50-00be-4956-8ef2-a965479e8be7
Description: TDE update CORTEX - PDBs: [HIPOFISE2]
Status: Failure
Created: May 19, 2021 6:59:17 AM UTC
Progress: 71%
Message: DCS-10101:Failed to rotate TDE key: CORTEXADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE identified by ******** WITH BACKUP USING 'OdaRotateKey' container=current
*
ERROR at line 1:
ORA-46627: keystore password ********
Uma 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á rodou com sucesso):
[root@luxor ~]# dbcli list-jobs -tl 1
ID Description Created Status
---------------------------------------- --------------------------------------------------------------------------- ----------------------------------- ----------
abb714a5-67c4-4b76-a78a-96496e7aacc7 TDE update CORTEX - PDBs: [HIPOFISE2] May 19, 2021 7:17:46 AM UTC Success
Com este JOB ID, podemos verificar os steps envolvidos no processo, o que pode ajudar muito em uma situação de investigação:
[root@luxor ~]# dbcli describe-job -i abb714a5-67c4-4b76-a78a-96496e7aacc7 -l Verbose
Job details
----------------------------------------------------------------
ID: abb714a5-67c4-4b76-a78a-96496e7aacc7
Description: TDE update CORTEX - PDBs: [HIPOFISE2]
Status: Success
Created: May 19, 2021 7:17:46 AM UTC
Progress: 100%
Message:
Task Name Start Time End Time Status
------------------------------------------------------------------------ ----------------------------------- ----------------------------------- ----------
task:TaskSequential_253 May 19, 2021 7:17:49 AM UTC May 19, 2021 7:17:49 AM UTC Running
task:TaskZLockWrapper_239 May 19, 2021 7:17:49 AM UTC May 19, 2021 7:18:12 AM UTC Success
task:TaskZJsonRpcExt_240 May 19, 2021 7:17:49 AM UTC May 19, 2021 7:18:02 AM UTC Success
task:TaskZJsonRpcExt_242 May 19, 2021 7:18:02 AM UTC May 19, 2021 7:18:05 AM UTC Success
task:TaskZJsonRpcExt_244 May 19, 2021 7:18:05 AM UTC May 19, 2021 7:18:08 AM UTC Success
task:TaskParallel_246 May 19, 2021 7:18:08 AM UTC May 19, 2021 7:18:08 AM UTC Success
Save password in wallet May 19, 2021 7:18:08 AM UTC May 19, 2021 7:18:08 AM UTC Success
Rotate Master Key May 19, 2021 7:18:08 AM UTC May 19, 2021 7:18:11 AM UTC Success
Cleaning password May 19, 2021 7:18:12 AM UTC May 19, 2021 7:18:12 AM UTC Success
[root@luxor ~]#
Agora podemos logar em nosso PDB e checar se a wallet está aberta (caso não esteja, devemos executar o comando “ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY “”;”):
[root@luxor ~]# exit
logout
[opc@luxor ~]$ sudo su - oracle
Last login: Wed May 19 07:24:28 UTC 2021
[oracle@luxor ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Wed May 19 07:25:26 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production
SQL> SELECT STATUS FROM V$ENCRYPTION_WALLET;
STATUS
------------------------------
OPEN
SQL> ALTER SESSION SET CONTAINER=HIPOFISE2;
Session altered.
SQL> SELECT STATUS FROM V$ENCRYPTION_WALLET;
STATUS
------------------------------
OPEN
Agora podemos criar nossa tablespace no PDB:
SQL> CREATE TABLESPACE USERS DATAFILE '+DATA';
Tablespace created.
SQL> SELECT TABLESPACE_NAME,STATUS,ENCRYPTED FROM DBA_TABLESPACES;
TABLESPACE_NAME STATUS ENC
------------------------------ --------- ---
SYSTEM ONLINE NO
SYSAUX ONLINE NO
UNDOTBS1 ONLINE NO
TEMP ONLINE NO
USERS ONLINE YES
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