Fala galera. Aqui vou mostrar uma visão geral do que acontece quando você usa o novo recurso Online Tablespace Encryption do Oracle Database 12cR2 
.
What’s up guys? Here just a quick overview of what happens when you use the new feature Online Tablespace Encryption of Oracle Database 12cR2 .
Só quero mostrar o que o Oracle faz quando está criptografando uma tablespace existente de maneira online. Então vamos ver o exemplo.
I just want to show what Oracle does when encrypting an existing tablespace online. So let us see the example.
Conecte-se ao banco de dados:
Connect to the database:
[oracle@oracle-srv ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Sun Nov 26 20:51:21 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Como estamos trabalhando com o TDE precisamos ver o status da wallet:
As we are dealing with TDE we have to check the wallet status:
SQL> SET LIN 1000 SQL> COL WRL_PARAMETER FORM A60 SQL> COL STATUS FORM A20 SQL> SELECT WRL_PARAMETER,STATUS,WALLET_TYPE FROM V$ENCRYPTION_WALLET; WRL_PARAMETER STATUS WALLET_TYPE ------------------------------------------------------------ -------------------- -------------------- /u01/app/oracle/product/12.2.0.1/ OPEN PASSWORD
A wallet está aberta no nível do CDB, mas a tablespace que eu quero alterar está num PDB, então vou alternar minha sessão para o SOEPDB:
It is open at the CDB level, but the tablespace I want to change is on a PDB, so I will alternate my session to SOEPDB:
SQL> alter session set container=soepdb; Session altered.
Agora vamos tentar criptografar a tablespace:
Let us try to encrypt the tablespace:
SQL> ALTER TABLESPACE tsdsoe ENCRYPTION ONLINE USING 'AES192' ENCRYPT; ALTER TABLESPACE tsdsoe ENCRYPTION ONLINE USING 'AES192' ENCRYPT * ERROR at line 1: ORA-28374: typed master key not found in wallet
Não funcionou, pois eu ainda não possuo uma keystore para este PDB.
It did not work, since I did not have a keystore for this PDB yet.
Então vamos criar a keystore:
So let’s create the keystore:
SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "oracle" WITH BACKUP USING 'mkey_bkp' ; keystore altered.
Agora vamos tentar criptografar novamente:
Now we try to encrypt again:
SQL> ALTER TABLESPACE tsdsoe ENCRYPTION ONLINE USING 'AES192' ENCRYPT; Tablespace altered.
Joia, funcionou. Vamos consultar a dba_tablespaces para verificar:
Fine, it worked. Let’s query dba_tablespaces to check:
SQL> select TABLESPACE_NAME, STATUS, CONTENTS, ENCRYPTED from dba_tablespaces; TABLESPACE_NAME STATUS CONTENTS ENC ------------------------------ --------- --------------------- --- SYSTEM ONLINE PERMANENT NO SYSAUX ONLINE PERMANENT NO UNDOTBS1 ONLINE UNDO NO TEMP ONLINE TEMPORARY NO TSDSOE ONLINE PERMANENT YES 5 rows selected.
Aqui vem a parte interessante… Se verificarmos o alert.log vamos ver o que acabou de acontecer. Vou dividir para explicar melhor.
Here comes the interesting part… If we check the alert.log we will see what just happened. I will split it to explain better.
Aqui é a parte que deu erro devido a keystore que estava faltando:
Here the error due to the missing keystore:
2017-11-26T20:51:42.178245+01:00 SOEPDB(3):ALTER TABLESPACE tsdsoe ENCRYPTION ONLINE USING 'AES192' ENCRYPT SOEPDB(3):ORA-28374 signalled during: ALTER TABLESPACE tsdsoe ENCRYPTION ONLINE USING 'AES192' ENCRYPT...
Aqui temos a keystore que acabou de ser criada:
Here we have the keystore that was just created:
2017-11-26T20:52:10.014414+01:00 SOEPDB(3):Creating new database key for new master key and wallet SOEPDB(3):Creating new database key with the new master key SOEPDB(3):Retiring: ena 2 flag 6 mkloc 0 SOEPDB(3): encrypted key 2b1a5e2f3558f388cbfb462baade6d4d00000000000000000000000000000000 SOEPDB(3): mkid a143b7686a934fedbfdcf2064294430e SOEPDB(3):Creating: ena 2 flag e mkloc 1 SOEPDB(3): encrypted key e219426db356adfcaf17b19e958ea28300000000000000000000000000000000 SOEPDB(3): mkid 88b8e3272f964f41bf3dacd75e230de2 SOEPDB(3):New database key and new master key created successfully
E aqui o procedimento de criptografia propriamente dito. Observe que o Oracle copiou os dados de um existente datafile para um novo:
And here is the encryption procedure itself. Observe that Oracle copied data from an existing datafile to a new one:
2017-11-26T20:52:21.577789+01:00 SOEPDB(3):ALTER TABLESPACE tsdsoe ENCRYPTION ONLINE USING 'AES192' ENCRYPT 2017-11-26T20:52:21.584722+01:00 SOEPDB(3):About to encrypt tablespace TSDSOE (tsn 3/4) SOEPDB(3):Rekeying datafile /u02/oradata/CDB2/58542FF64D541288E0530F0FA8C0C065/datafile/o1_mf_tsdsoe_dtsdhmr8_.dbf (12) to /u02/oradata/CDB2/58542FF64D541288E0530F0FA8C0C065/datafile/o1_mf_tsdsoe_%u_.dbf 2017-11-26T20:52:26.449204+01:00 SOEPDB(3):Rekey operation committed for file /u02/oradata/CDB2/58542FF64D541288E0530F0FA8C0C065/datafile/o1_mf_tsdsoe_f1p6vonj_.dbf
E depois excluiu o datafile não criptografado:
And then deleted the non encrypted datafile:
2017-11-26T20:52:28.462284+01:00 SOEPDB(3):About to zero out original file "/u02/oradata/CDB2/58542FF64D541288E0530F0FA8C0C065/datafile/o1_mf_tsdsoe_dtsdhmr8_.dbf" 2017-11-26T20:52:30.658380+01:00 SOEPDB(3):Successfully zero'ed out original file "/u02/oradata/CDB2/58542FF64D541288E0530F0FA8C0C065/datafile/o1_mf_tsdsoe_dtsdhmr8_.dbf" SOEPDB(3):Successfully deleted original file "/u02/oradata/CDB2/58542FF64D541288E0530F0FA8C0C065/datafile/o1_mf_tsdsoe_dtsdhmr8_.dbf" SOEPDB(3):Completed rekey for tablespace TSDSOE (tsn 3/4) from key version 0 to 1. SOEPDB(3):Completed: ALTER TABLESPACE tsdsoe ENCRYPTION ONLINE USING 'AES192' ENCRYPT 2017-11-26T20:53:41.656374+01:00
Então quando você quiser criptografar online uma tablespace tenha certeza de que você possui espaço audiente para dobrar o tamanho da tablespace para que esta operação conclua com sucesso.
So when you want to online encrypt a tablespace make sure you have enough room to double the size of this tablespace for this operation to be successfully concluded.
Espero que tenham gostado!
Hope you enjoyed it!
Abraços e até mais,
Best regards,
Franky