{"id":5596,"date":"2021-06-25T08:43:17","date_gmt":"2021-06-25T08:43:17","guid":{"rendered":"https:\/\/swiv.com.br\/producing-transparent-mode-encrypted-backups\/"},"modified":"2026-05-27T20:02:31","modified_gmt":"2026-05-27T19:02:31","slug":"producing-transparent-mode-encrypted-backups","status":"publish","type":"post","link":"https:\/\/swiv.com.br\/index.php\/2021\/06\/25\/producing-transparent-mode-encrypted-backups\/","title":{"rendered":"Producing Transparent-mode Encrypted Backups"},"content":{"rendered":"

Para que este recurso seja utilizado, precisamos que o TDE (Transparent Data Encryption) esteja habilitado no banco de dados. Fizemos essa etapa NESTE<\/a> artigo, e vamos explorar neste artigo as 2 maneiras de implementar o Transparent-encryption mode.<\/p>\n\n\n

Password-based software Keystore<\/h2>\n\n\n\n

Neste m\u00e9todo, o keystore \u00e9 aberto manualmente pelo DBA para que a opera\u00e7\u00e3o via RMAN seja poss\u00edvel. Veremos em breve que, sempre que o banco de dados \u00e9 reiniciado, essa chave n\u00e3o \u00e9 aberta automaticamente, exigindo assim que o DBA a abra para poder utilizar.<\/p>\n\n\n\n

A respeito de par\u00e2metros persistentes do RMAN, temos os 2 itens abaixo, referentes ao uso de criptografia e o algoritmo utilizado:<\/p>\n\n\n

\n[oracle@oel8 admin]$ rman target \/\n \nRecovery Manager: Release 18.0.0.0.0 - Production on Fri Jun 25 05:26:07 2021\nVersion 18.13.0.0.0\n \nCopyright (c) 1982, 2018, Oracle and\/or its affiliates.  All rights reserved.\n \nconnected to target database: RMANDB (DBID=3825250984)\n \nRMAN> SHOW ENCRYPTION FOR DATABASE;\n \nusing target database control file instead of recovery catalog\nRMAN configuration parameters for database with db_unique_name RMANDB are:\nCONFIGURE ENCRYPTION FOR DATABASE OFF; # default\n \nRMAN> SHOW ENCRYPTION ALGORITHM;\n \nRMAN configuration parameters for database with db_unique_name RMANDB are:\nCONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default\n<\/pre><\/div>\n\n\n
Mas tamb\u00e9m podemos definir o uso de criptografia em tempo de execu\u00e7\u00e3o, conforme o exemplo abaixo:<\/p>\n\n\n
\nRMAN> SET ENCRYPTION ON;\n \nexecuting command: SET encryption\n \nRMAN> BACKUP TABLESPACE USERS TAG 'ENCRYPTED_USERS';\n \nStarting backup at 2021-06-25:05:27:21\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=113 device type=DISK\nchannel ORA_DISK_1: starting full datafile backup set\nchannel ORA_DISK_1: specifying datafile(s) in backup set\ninput datafile file number=00007 name=\/oracle\/dados\/RMANDB\/datafile\/o1_mf_users_h8nyrkn7_.dbf\nchannel ORA_DISK_1: starting piece 1 at 2021-06-25:05:27:21\nchannel ORA_DISK_1: finished piece 1 at 2021-06-25:05:27:24\npiece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_06_25\/o1_mf_nnndf_ENCRYPTED_USERS_jfc4v9xl_.bkp tag=ENCRYPTED_USERS comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:03\nFinished backup at 2021-06-25:05:27:24\n \nStarting Control File and SPFILE Autobackup at 2021-06-25:05:27:24\npiece handle=\/oracle\/fra\/RMANDB\/autobackup\/2021_06_25\/o1_mf_s_1076131644_jfc4vfpf_.bkp comment=NONE\nFinished Control File and SPFILE Autobackup at 2021-06-25:05:27:27\n<\/pre><\/div>\n\n\n
No comando LIST, n\u00e3o \u00e9 poss\u00edvel identificar que o backup foi realizado com o recurso de criptografia:<\/p>\n\n\n
\nRMAN> LIST BACKUPSET TAG 'ENCRYPTED_USERS';\n \n \nList of Backup Sets\n===================\n \n \nBS Key  Type LV Size       Device Type Elapsed Time Completion Time\n------- ---- -- ---------- ----------- ------------ -------------------\n339     Full    13.25M     DISK        00:00:01     2021-06-25:05:27:22\n        BP Key: 368   Status: AVAILABLE  Compressed: NO  Tag: ENCRYPTED_USERS\n        Piece Name: \/oracle\/fra\/RMANDB\/backupset\/2021_06_25\/o1_mf_nnndf_ENCRYPTED_USERS_jfc4v9xl_.bkp\n  List of Datafiles in backup set 339\n  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name\n  ---- -- ---- ---------- ------------------- ----------- ------ ----\n  7       Full 2977458    2021-06-25:05:27:21              NO    \/oracle\/dados\/RMANDB\/datafile\/o1_mf_users_h8nyrkn7_.dbf\n<\/pre><\/div>\n\n\n
Para termos certeza, podemos executar a consulta abaixo:<\/p>\n\n\n
\nRMAN> SELECT S.RECID AS "BS_REC", P.RECID AS "BP_REC", P.ENCRYPTED FROM V$BACKUP_PIECE P, V$BACKUP_SET S WHERE P.SET_STAMP = S.SET_STAMP AND P.SET_COUNT = S.SET_COUNT AND P.TAG ='ENCRYPTED_USERS';\n \n    BS_REC     BP_REC ENC\n---------- ---------- ---\n       339        368 YES\n<\/pre><\/div>\n\n\n
Agora, vamos reiniciar o nosso banco de dados:<\/p>\n\n\n
\nRMAN> SHUTDOWN IMMEDIATE;\n \ndatabase closed\ndatabase dismounted\nOracle instance shut down\n \nRMAN> STARTUP;\n \nconnected to target database (not started)\nOracle instance started\ndatabase mounted\ndatabase opened\n \nTotal System Global Area    1610612016 bytes\n \nFixed Size                     8658224 bytes\nVariable Size                402653184 bytes\nDatabase Buffers            1191182336 bytes\nRedo Buffers                   8118272 bytes\n<\/pre><\/div>\n\n\n
Ao tentar executar o banco com criptografia, um erro \u00e9 reportado pois nosso keystore est\u00e1 fechado:<\/p>\n\n\n
\nRMAN> SET ENCRYPTION ON;\n \nexecuting command: SET encryption\n \nRMAN> BACKUP TABLESPACE users TAG 'ENCRYPTED_USERS';\n \nStarting backup at 2021-06-25:05:31:13\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=76 device type=DISK\nchannel ORA_DISK_1: starting full datafile backup set\nchannel ORA_DISK_1: specifying datafile(s) in backup set\ninput datafile file number=00007 name=\/oracle\/dados\/RMANDB\/datafile\/o1_mf_users_h8nyrkn7_.dbf\nchannel ORA_DISK_1: starting piece 1 at 2021-06-25:05:31:13\nRMAN-00571: ===========================================================\nRMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============\nRMAN-00571: ===========================================================\nRMAN-03009: failure of backup command on ORA_DISK_1 channel at 06\/25\/2021 05:31:14\nORA-19914: unable to encrypt backup\nORA-28365: wallet is not open\n<\/pre><\/div>\n\n\n
Desse modo, vamos abrir manualmente o keystore:<\/p>\n\n\n
\nRMAN> sql 'ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY oracle';\n \nsql statement: ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY oracle\n<\/pre><\/div>\n\n\n
Agora nosso backup rodou com sucesso:<\/p>\n\n\n
\nRMAN> SET ENCRYPTION ON;\n \nexecuting command: SET encryption\n \nRMAN> BACKUP TABLESPACE users TAG 'ENCRYPTED_USERS';\n \nStarting backup at 2021-06-25:05:32:34\nusing channel ORA_DISK_1\nchannel ORA_DISK_1: starting full datafile backup set\nchannel ORA_DISK_1: specifying datafile(s) in backup set\ninput datafile file number=00007 name=\/oracle\/dados\/RMANDB\/datafile\/o1_mf_users_h8nyrkn7_.dbf\nchannel ORA_DISK_1: starting piece 1 at 2021-06-25:05:32:34\nchannel ORA_DISK_1: finished piece 1 at 2021-06-25:05:32:35\npiece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_06_25\/o1_mf_nnndf_ENCRYPTED_USERS_jfc552rq_.bkp tag=ENCRYPTED_USERS comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01\nFinished backup at 2021-06-25:05:32:35\n \nStarting Control File and SPFILE Autobackup at 2021-06-25:05:32:35\npiece handle=\/oracle\/fra\/RMANDB\/autobackup\/2021_06_25\/o1_mf_s_1076131955_jfc5549m_.bkp comment=NONE\nFinished Control File and SPFILE Autobackup at 2021-06-25:05:32:38\n<\/pre><\/div>\n\n\n
Auto-login Software Keystore<\/h2>\n\n\n\n
Nesse m\u00e9todo, o Keystore sempre se mant\u00e9m aberto, sem a necessidade de interven\u00e7\u00e3o de um DBA. Para isso, vamos criar um Auto-Login Keystore com o comando abaixo (onde um novo arquivo chamado cwallet.sso ser\u00e1 criado em nosso diret\u00f3rio):<\/p>\n\n\n
\n[oracle@oel8 admin]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 18.0.0.0.0 - Production on Fri Jun 25 05:38:12 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> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '\/oracle\/dados\/RMANDB\/keystore' IDENTIFIED BY oracle;\n \nkeystore altered.\n \nSQL> !ls -la \/oracle\/dados\/RMANDB\/keystore\ntotal 12\ndrwxr-xr-x. 2 oracle oinstall   85 Jun 25 05:38 .\ndrwxr-x---. 9 oracle oinstall  126 Jun 25 04:57 ..\n-rw-------. 1 oracle oinstall 4040 Jun 25 05:38 cwallet.sso\n-rw-------. 1 oracle oinstall 2555 Jun 25 05:06 ewallet_2021062508065797_RMAN.p12\n-rw-------. 1 oracle oinstall 3995 Jun 25 05:06 ewallet.p12\n<\/pre><\/div>\n\n\n
Reiniciando o banco de dados para termos certeza que mesmo o seu restart n\u00e3o exige abrirmos a keystore:<\/p>\n\n\n
\nSQL> SHU IMMEDIATE;\nDatabase closed.\nDatabase dismounted.\nORACLE instance shut down.\nSQL> STARTUP;\nORACLE instance started.\n \nTotal System Global Area 1610612016 bytes\nFixed Size                  8658224 bytes\nVariable Size             520093696 bytes\nDatabase Buffers         1073741824 bytes\nRedo Buffers                8118272 bytes\nDatabase mounted.\nDatabase opened.\n<\/pre><\/div>\n\n\n
Realizando o backup com criptografia, onde o erro de que a wallet est\u00e1 fechada n\u00e3o \u00e9 reportado:<\/p>\n\n\n
\n[oracle@oel8 admin]$ rman target \/\n \nRecovery Manager: Release 18.0.0.0.0 - Production on Fri Jun 25 05:41:33 2021\nVersion 18.13.0.0.0\n \nCopyright (c) 1982, 2018, Oracle and\/or its affiliates.  All rights reserved.\n \nconnected to target database: RMANDB (DBID=3825250984)\n \nRMAN> SET ENCRYPTION ON;\n \nexecuting command: SET encryption\nusing target database control file instead of recovery catalog\n \nRMAN> BACKUP TABLESPACE users TAG 'BSS';\n \nStarting backup at 2021-06-25:05:41:45\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=68 device type=DISK\nchannel ORA_DISK_1: starting full datafile backup set\nchannel ORA_DISK_1: specifying datafile(s) in backup set\ninput datafile file number=00007 name=\/oracle\/dados\/RMANDB\/datafile\/o1_mf_users_h8nyrkn7_.dbf\nchannel ORA_DISK_1: starting piece 1 at 2021-06-25:05:41:45\nchannel ORA_DISK_1: finished piece 1 at 2021-06-25:05:41:47\npiece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_06_25\/o1_mf_nnndf_BSS_jfc5pb1h_.bkp tag=BSS comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:02\nFinished backup at 2021-06-25:05:41:47\n \nStarting Control File and SPFILE Autobackup at 2021-06-25:05:41:47\npiece handle=\/oracle\/fra\/RMANDB\/autobackup\/2021_06_25\/o1_mf_s_1076132507_jfc5pcnz_.bkp comment=NONE\nFinished Control File and SPFILE Autobackup at 2021-06-25:05:41:48\n<\/pre><\/div>\n\n\n
Validando que realmente o backup foi criptografado:<\/p>\n\n\n
\nRMAN> SELECT S.RECID AS "BS_REC", P.RECID AS "BP_REC", P.ENCRYPTED FROM V$BACKUP_PIECE P, V$BACKUP_SET S WHERE P.SET_STAMP = S.SET_STAMP AND P.SET_COUNT = S.SET_COUNT AND P.TAG ='BSS';\n \n    BS_REC     BP_REC ENC\n---------- ---------- ---\n       343        372 YES\n<\/pre><\/div>\n\n\n
Obs: 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":"
Para que este recurso seja utilizado, precisamos que o TDE (Transparent Data Encryption) esteja habilitado no banco de dados. Fizemos essa etapa NESTE artigo, e vamos explorar neste artigo as 2 maneiras de implementar o Transparent-encryption mode. Password-based software Keystore Neste m\u00e9todo, o keystore \u00e9 aberto manualmente pelo DBA para que a opera\u00e7\u00e3o via RMAN […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-5596","post","type-post","status-publish","format-standard","hentry","category-backup-recovery"],"_links":{"self":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/5596","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=5596"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/5596\/revisions"}],"predecessor-version":[{"id":9093,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/5596\/revisions\/9093"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=5596"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=5596"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=5596"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}