An earlier post showed the steps for
encrypting/decrypting tablespaces both online and offline in a single database.
This post shows the steps for doing the same when there's a data guard configuration.
The data guard configuration consists of three databases.
DGMGRL> show configuration
Configuration - drcp_dg
Protection Mode: MaxAvailability
Members:
testcdb - Primary database
testcdb2 - Physical standby database
testcdb3 - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 51 seconds ago)Each standby has a different open mode. testcdb2 is open in read only mode with apply
SQL> select open_mode,db_unique_name from v$database;
OPEN_MODE DB_UNIQUE_NAME
-------------------- ------------------------------
READ ONLY WITH APPLY testcdb2
while testcdb3 is in mount mode.
SQL> select open_mode,db_unique_name from v$database;
OPEN_MODE DB_UNIQUE_NAME
-------------------- ------------------------------
MOUNTED testcdb3
It's assumed
TDE is setup for all the databases in the DG configuration. For the testing a unencrypted tablespace called "enctest" is created. DB version is 19.12
Online Encryption
Online encryption is pretty straigth fowrad. Encrypt the tablespace on the primary.
select t.name,e.ENCRYPTIONALG,e.ENCRYPTEDTS,e.STATUS from V$ENCRYPTED_TABLESPACES e, v$tablespace t where t.ts#=e.ts#(+) and NAME='ENCTEST';
NAME ENCRYPT ENC STATUS
------------------------------ ------- --- ----------
ENCTEST
SQL> ALTER TABLESPACE ENCTEST ENCRYPTION ONLINE USING 'AES256' ENCRYPT;
SQL> select t.name,e.ENCRYPTIONALG,e.ENCRYPTEDTS,e.STATUS from V$ENCRYPTED_TABLESPACES e, v$tablespace t where t.ts#=e.ts#(+) and NAME='ENCTEST';
NAME ENCRYPT ENC STATUS
------------------------------ ------- --- ----------
ENCTEST AES256 YES NORMAL
Tablespaces on the standby databases (both in mount and active data guard) are automatically encrypted. On the standby alerts log following log entries, related to encryption are shown. On testcdb2 (open read only)
2021-10-12T13:22:41.197253+00:00
(3):Rekeying datafile +DATA/TESTCDB2/9D48FF44CE8A7D32E053F4071FACDFB3/DATAFILE/enctest.296.1085749247 (16) to +DATA
(3):Rekey operation committed for file +DATA/TESTCDB2/9D48FF44CE8A7D32E053F4071FACDFB3/DATAFILE/enctest.297.1085750561
2021-10-12T13:22:44.005273+00:00
(3):About to zero out original file "+DATA/TESTCDB2/9D48FF44CE8A7D32E053F4071FACDFB3/DATAFILE/enctest.296.1085749247"
(3):Successfully zero'ed out original file "+DATA/TESTCDB2/9D48FF44CE8A7D32E053F4071FACDFB3/DATAFILE/enctest.296.1085749247"
(3):Successfully deleted original file "+DATA/TESTCDB2/9D48FF44CE8A7D32E053F4071FACDFB3/DATAFILE/enctest.296.1085749247"
On testcdb3 (open mount)
2021-10-12T13:22:41.470038+00:00
(3):Rekeying datafile +DATA/TESTCDB3/9D48FF44CE8A7D32E053F4071FACDFB3/DATAFILE/enctest.317.1085749247 (16) to +DATA
2021-10-12T13:22:42.781816+00:00
(3):Rekey operation committed for file +DATA/TESTCDB3/9D48FF44CE8A7D32E053F4071FACDFB3/DATAFILE/enctest.315.1085750561
2021-10-12T13:22:44.806025+00:00
(3):About to zero out original file "+DATA/TESTCDB3/9D48FF44CE8A7D32E053F4071FACDFB3/DATAFILE/enctest.317.1085749247"
(3):Successfully zero'ed out original file "+DATA/TESTCDB3/9D48FF44CE8A7D32E053F4071FACDFB3/DATAFILE/enctest.317.1085749247"
(3):Successfully deleted original file "+DATA/TESTCDB3/9D48FF44CE8A7D32E053F4071FACDFB3/DATAFILE/enctest.317.1085749247"
Online Decryption
Online decryption is also similar to encryption. Execute the decryption on primary
ALTER TABLESPACE ENCTEST ENCRYPTION online DECRYPT;
select t.name,e.ENCRYPTIONALG,e.ENCRYPTEDTS,e.STATUS from V$ENCRYPTED_TABLESPACES e, v$tablespace t where t.ts#=e.ts#(+) and NAME='ENCTEST';
NAME ENCRYPT ENC STATUS
------------------------------ ------- --- ----------
ENCTEST
Changes are automatically propergated to standby databases. As before alert log shows the decrption related activities. On testcdb2
2021-10-12T13:27:32.614421+00:00
(3):Rekeying datafile +DATA/TESTCDB2/9D48FF44CE8A7D32E053F4071FACDFB3/DATAFILE/enctest.297.1085750561 (16) to +DATA
(3):Rekey operation committed for file +DATA/TESTCDB2/9D48FF44CE8A7D32E053F4071FACDFB3/DATAFILE/enctest.296.1085750853
2021-10-12T13:27:35.351234+00:00
(3):About to zero out original file "+DATA/TESTCDB2/9D48FF44CE8A7D32E053F4071FACDFB3/DATAFILE/enctest.297.1085750561"
(3):Successfully zero'ed out original file "+DATA/TESTCDB2/9D48FF44CE8A7D32E053F4071FACDFB3/DATAFILE/enctest.297.1085750561"
(3):Successfully deleted original file "+DATA/TESTCDB2/9D48FF44CE8A7D32E053F4071FACDFB3/DATAFILE/enctest.297.1085750561"
On testcdb3
2021-10-12T13:27:32.869097+00:00
(3):Rekeying datafile +DATA/TESTCDB3/9D48FF44CE8A7D32E053F4071FACDFB3/DATAFILE/enctest.315.1085750561 (16) to +DATA
2021-10-12T13:27:34.461534+00:00
(3):Rekey operation committed for file +DATA/TESTCDB3/9D48FF44CE8A7D32E053F4071FACDFB3/DATAFILE/enctest.317.1085750853
2021-10-12T13:27:36.477042+00:00
(3):About to zero out original file "+DATA/TESTCDB3/9D48FF44CE8A7D32E053F4071FACDFB3/DATAFILE/enctest.315.1085750561"
(3):Successfully zero'ed out original file "+DATA/TESTCDB3/9D48FF44CE8A7D32E053F4071FACDFB3/DATAFILE/enctest.315.1085750561"
(3):Successfully deleted original file "+DATA/TESTCDB3/9D48FF44CE8A7D32E053F4071FACDFB3/DATAFILE/enctest.315.1085750561"
Offline Encryption
Offline encryption (and decryption) can happen in any order, meaning primary first or standby first. There's a difference to this in lower versions of 11.2 and 12.1. Read asymmetrical configurations in
this white paper.
There are no special consideration for encryption tablespace in offline mode in primary. Same as
before offline the tablespace and encrypt.
alter tablespace ENCTEST offline normal;
ALTER TABLESPACE ENCTEST ENCRYPTION OFFLINE USING 'AES256' ENCRYPT;
alter tablespace ENCTEST online;
select t.name,e.ENCRYPTIONALG,e.ENCRYPTEDTS,e.STATUS from V$ENCRYPTED_TABLESPACES e, v$tablespace t where t.ts#=e.ts#(+) and NAME='ENCTEST';
NAME ENCRYPT ENC STATUS
------------------------------ ------- --- ----------
ENCTEST AES256 YES NORMAL
There's no entries in the standby alert logs. The tablespace on standby databases will remain unencrypted.
select t.name,e.ENCRYPTIONALG,e.ENCRYPTEDTS,e.STATUS from V$ENCRYPTED_TABLESPACES e, v$tablespace t where t.ts#=e.ts#(+) and NAME='ENCTEST';
NAME ENCRYPT ENC STATUS
------------------------------ ------- --- ----------
ENCTEST
Offline Encryption On Standby Open in Mount modeOnce the tablespace is encrypted on primary same could be done standby as well. This step shows how this is done on standby in mount mode. As it's not open (in mount mode) there's no need to offline tablespaces.
SQL> alter tablespace ENCTEST offline normal;
alter tablespace ENCTEST offline normal
*
ERROR at line 1:
ORA-01109: database not open
However, encryption cannot happen while recovery is active.
SQL> ALTER TABLESPACE ENCTEST ENCRYPTION OFFLINE USING 'AES256' ENCRYPT;
ALTER TABLESPACE ENCTEST ENCRYPTION OFFLINE USING 'AES256' ENCRYPT
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active
As the first step for encryption on standby, stop the log apply.
DGMGRL> edit database testcdb3 set state='apply-off';
Succeeded.
DGMGRL> show database testcdb3
Database - testcdb3
Role: PHYSICAL STANDBY
Intended State: APPLY-OFF
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
testcdb3
Database Status:
SUCCESSIf
DG broker is not used then verify apply is off with
select * from gv$managed_standby where process='MRP0'. This should not any rows if apply is off.
Once the log apply is off run the encrption.
SQL> ALTER TABLESPACE ENCTEST ENCRYPTION OFFLINE USING 'AES256' ENCRYPT;
Tablespace altered.
SQL> select t.name,e.ENCRYPTIONALG,e.ENCRYPTEDTS,e.STATUS from V$ENCRYPTED_TABLESPACES e, v$tablespace t where t.ts#=e.ts#(+) and NAME='ENCTEST';
NAME ENCRYPT ENC STATUS
------------------------------ ------- --- ----------
ENCTEST
The status of the tablespace will not be updated to encrypted=yes until the log apply is enabled. Once enabled the query will show tablespace is encrypted.
DGMGRL> edit database testcdb3 set state='apply-on';
Succeeded.
NAME ENCRYPT ENC STATUS
------------------------------ ------- --- ----------
ENCTEST AES256 YES NORMAL
Alert log will have entries related to the encryption.
Managed Standby Recovery Canceled (testcdb3)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
2021-10-12T14:28:47.715122+00:00
TESTPDB(3): ALTER TABLESPACE ENCTEST ENCRYPTION OFFLINE USING 'AES256' ENCRYPT
2021-10-12T14:28:47.761081+00:00
TESTPDB(3):About to offline encrypt tablespace 8 ..
2021-10-12T14:28:49.179873+00:00
TESTPDB(3):Successfully encrypted tablespace 8 with database key.
TESTPDB(3):Completed: ALTER TABLESPACE ENCTEST ENCRYPTION OFFLINE USING 'AES256' ENCRYPT
2021-10-12T14:29:25.473015+00:00
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT NODELAY
2021-10-12T14:29:25.477314+00:00
Attempt to start background Managed Standby Recovery process (testcdb3)
Starting background process MRP0
2021-10-12T14:29:25.494580+00:00
MRP0 started with pid=25, OS id=25152
2021-10-12T14:29:25.496232+00:00
Background Managed Standby Recovery process started (testcdb3)
Offline Decryption On Standby Open in Mount modeSimilar to encrypt, to decrypt turn off apply and run the decryption command.
DGMGRL> edit database testcdb3 set state='apply-off';
Succeeded.
SQL> ALTER TABLESPACE ENCTEST ENCRYPTION OFFLINE DECRYPT;
Tablespace altered.
SQL> select t.name,e.ENCRYPTIONALG,e.ENCRYPTEDTS,e.STATUS from V$ENCRYPTED_TABLESPACES e, v$tablespace t where t.ts#=e.ts#(+) and NAME='ENCTEST';
NAME ENCRYPT ENC STATUS
------------------------------ ------- --- ----------
ENCTEST AES256 YES NORMAL
Once the apply is enabled the tablespace encryption status will be updated to un-encrypted.
DGMGRL> edit database testcdb3 set state='apply-on';
Succeeded.
SQL> select t.name,e.ENCRYPTIONALG,e.ENCRYPTEDTS,e.STATUS from V$ENCRYPTED_TABLESPACES e, v$tablespace t where t.ts#=e.ts#(+) and NAME='ENCTEST';
NAME ENCRYPT ENC STATUS
------------------------------ ------- --- ----------
ENCTEST
Offline Encryption/Decryption On Standby Open in Read Only mode
Things are bit different for encrypting/decrypting tablespaces on a standby open in read only mode. The tablespace cannot be made offline as DB is open for read only access
SQL> alter tablespace ENCTEST offline normal;
alter tablespace ENCTEST offline normal
*
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only accessOffline encryption fails as DB is open for read only access
SQL> ALTER TABLESPACE ENCTEST ENCRYPTION OFFLINE USING 'AES256' ENCRYPT;
ALTER TABLESPACE ENCTEST ENCRYPTION OFFLINE USING 'AES256' ENCRYPT
*
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only accessStopping the apply process has no effect in above.
Stopping the PDB also has no effect.
QL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 TESTPDB READ ONLY NO
SQL> shutdown immediate;
Pluggable Database closed.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 TESTPDB MOUNTED
SQL> ALTER TABLESPACE ENCTEST ENCRYPTION OFFLINE USING 'AES256' ENCRYPT;
ALTER TABLESPACE ENCTEST ENCRYPTION OFFLINE USING 'AES256' ENCRYPT
*
ERROR at line 1:
ORA-28429: cannot encrypt or decrypt a data file on standby when it is open read-onlyOnly solution is to stop the standby CDB and open it in mount mode and do the encryption/decryption as shown in standby in mount mode case.
Standby First Encryption and Switchover
Online encryption requires additional space compared to offline encryption. As offline encryption requires tablespace to be taken offline (no read/write) which result in application down time. To avoid these limitiations the encryption could be done first on standby in offline mode. Once completed a switchover could be done to encrypt the tablespace on old primary.
Current DG configuration.
DGMGRL> show configuration
Configuration - drcp_dg
Protection Mode: MaxAvailability
Members:
testcdb - Primary database
testcdb2 - Physical standby database
testcdb3 - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 51 seconds ago)Encrypt the tablespace on the standby.
ALTER TABLESPACE ENCTEST ENCRYPTION OFFLINE USING 'AES256' ENCRYPT;
NAME ENCRYPT ENC STATUS
------------------------------ ------- --- ----------
ENCTEST AES256 YES NORMAL
Tablespace encryption status on primary
NAME ENCRYPT ENC STATUS
------------------------------ ------- --- ----------
ENCTEST
Do a switchover to standby with encrypted tablespace
switchover to testcdb3
DGMGRL> show configuration
Configuration - drcp_dg
Protection Mode: MaxAvailability
Members:
testcdb3 - Primary database
testcdb - Physical standby database
testcdb2 - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 59 seconds ago)Offline encrypt the tablespace on old primary.
ALTER TABLESPACE ENCTEST ENCRYPTION OFFLINE USING 'AES256' ENCRYPT;
NAME ENCRYPT ENC STATUS
------------------------------ ------- --- ----------
ENCTEST AES256 YES NORMAL
Online Encryption When Tablespace Encryption Status on Each Standby Differ
A tablespace that is already encrypted cannot be encrypted again. Doesn't matter if it is online or offline the encryption operation will fail. Below shows trying to re-encrypt an already encrypted tablespace
SQL> select t.name,e.ENCRYPTIONALG,e.ENCRYPTEDTS,e.STATUS from V$ENCRYPTED_TABLESPACES e, v$tablespace t where t.ts#=e.ts#(+);
NAME ENCRYPT ENC STATUS
------------------------------ ------- --- ----------
ENCTEST
SQL> ALTER TABLESPACE ENCTEST ENCRYPTION ONLINE USING 'AES256' ENCRYPT;
Tablespace altered.
SQL> select t.name,e.ENCRYPTIONALG,e.ENCRYPTEDTS,e.STATUS from V$ENCRYPTED_TABLESPACES e, v$tablespace t where t.ts#=e.ts#(+) and NAME='ENCTEST';
NAME ENCRYPT ENC STATUS
------------------------------ ------- --- ----------
ENCTEST AES256 YES NORMAL
SQL> ALTER TABLESPACE ENCTEST ENCRYPTION ONLINE USING 'AES256' ENCRYPT;
ALTER TABLESPACE ENCTEST ENCRYPTION ONLINE USING 'AES256' ENCRYPT
*
ERROR at line 1:
ORA-28370: ENCRYPT, DECRYPT or REKEY option not allowed
alter tablespace ENCTEST offline normal;
ALTER TABLESPACE ENCTEST ENCRYPTION OFFLINE USING 'AES256' ENCRYPT;
SQL> ALTER TABLESPACE ENCTEST ENCRYPTION OFFLINE USING 'AES256' ENCRYPT
*
ERROR at line 1:
ORA-28431: cannot encrypt an already encrypted data file ENCTEST
Same happens for decryption as well. A tablespace already decrypted (unencrypted) cannot be decrypted again.
SQL> ALTER TABLESPACE ENCTEST ENCRYPTION online DECRYPT;
Tablespace altered.
SQL> select t.name,e.ENCRYPTIONALG,e.ENCRYPTEDTS,e.STATUS from V$ENCRYPTED_TABLESPACES e, v$tablespace t where t.ts#=e.ts#(+) and NAME='ENCTEST';
NAME ENCRYPT ENC STATUS
------------------------------ ------- --- ----------
ENCTEST
SQL> ALTER TABLESPACE ENCTEST ENCRYPTION online DECRYPT;
ALTER TABLESPACE ENCTEST ENCRYPTION online DECRYPT
*
ERROR at line 1:
ORA-28370: ENCRYPT, DECRYPT or REKEY option not allowed
SQL> alter tablespace ENCTEST offline normal;
ALTER TABLESPACE ENCTEST ENCRYPTION OFFLINE DECRYPT;
alter tablespace ENCTEST online;
Tablespace altered.
SQL> ALTER TABLESPACE ENCTEST ENCRYPTION OFFLINE DECRYPT
*
ERROR at line 1:
ORA-28434: cannot decrypt an unencrypted data file
+DATA/TDETEST/C20D0B518CB34375E053360B1FACB37C/DATAFILE/enctest.317.1085912207
However, it appears, tablespace that are already encrypted or un-encrypted will get rekeyed in a DG configuration if online encryption/decryption is done on primary. At the start the primary (testcdb) has un-encrypted tablespace.
SQL> select t.name,e.ENCRYPTIONALG,e.ENCRYPTEDTS,e.STATUS from V$ENCRYPTED_TABLESPACES e, v$tablespace t where t.ts#=e.ts#(+) and NAME='ENCTEST';
NAME ENCRYPT ENC STATUS
------------------------------ ------- --- ----------
ENCTEST
One of the standby (testcdb2) databases also has un-encrypted tablespace
SQL> select t.name,e.ENCRYPTIONALG,e.ENCRYPTEDTS,e.STATUS from V$ENCRYPTED_TABLESPACES e, v$tablespace t where t.ts#=e.ts#(+) and NAME='ENCTEST';
NAME ENCRYPT ENC STATUS
------------------------------ ------- --- ----------
ENCTEST
Another standby (testcdb3) has an encrypted tablespace
NAME ENCRYPT ENC STATUS
------------------------------ ------- --- ----------
ENCTEST AES256 YES NORMAL
On the primary the the tablespace is encrypted online.
SQL> ALTER TABLESPACE ENCTEST ENCRYPTION ONLINE USING 'AES256' ENCRYPT;
Tablespace altered.
SQL> select t.name,e.ENCRYPTIONALG,e.ENCRYPTEDTS,e.STATUS from V$ENCRYPTED_TABLESPACES e, v$tablespace t where t.ts#=e.ts#(+) and NAME='ENCTEST';
NAME ENCRYPT ENC STATUS
------------------------------ ------- --- ----------
ENCTEST AES256 YES NORMAL
As a result the tablespace that was un-encrypted on a standby (testcdb2) gets encrypted.
SQL> select t.name,e.ENCRYPTIONALG,e.ENCRYPTEDTS,e.STATUS from V$ENCRYPTED_TABLESPACES e, v$tablespace t where t.ts#=e.ts#(+) and NAME='ENCTEST';
NAME ENCRYPT ENC STATUS
------------------------------ ------- --- ----------
ENCTEST AES256 YES NORMAL
Alert log shows following
2021-10-14T10:33:18.996464+00:00
(3):Rekeying datafile +DATA/TESTCDB2/9D48FF44CE8A7D32E053F4071FACDFB3/DATAFILE/enctest.297.1085912727 (21) to +DATA
(3):Rekey operation committed for file +DATA/TESTCDB2/9D48FF44CE8A7D32E053F4071FACDFB3/DATAFILE/enctest.298.1085913199
2021-10-14T10:33:22.026323+00:00
(3):About to zero out original file "+DATA/TESTCDB2/9D48FF44CE8A7D32E053F4071FACDFB3/DATAFILE/enctest.297.1085912727"
(3):Successfully zero'ed out original file "+DATA/TESTCDB2/9D48FF44CE8A7D32E053F4071FACDFB3/DATAFILE/enctest.297.1085912727"
(3):Successfully deleted original file "+DATA/TESTCDB2/9D48FF44CE8A7D32E053F4071FACDFB3/DATAFILE/enctest.297.1085912727"
The tablespace that was already encrypted on a standby (testcdb3) also get rekeyed
2021-10-14T10:33:19.519352+00:00
(3):Rekeying datafile +DATA/TESTCDB3/9D48FF44CE8A7D32E053F4071FACDFB3/DATAFILE/enctest.318.1085912727 (21) to +DATA
2021-10-14T10:33:20.968269+00:00
(3):Rekey operation committed for file +DATA/TESTCDB3/9D48FF44CE8A7D32E053F4071FACDFB3/DATAFILE/enctest.319.1085913199
2021-10-14T10:33:22.987772+00:00
(3):About to zero out original file "+DATA/TESTCDB3/9D48FF44CE8A7D32E053F4071FACDFB3/DATAFILE/enctest.318.1085912727"
(3):Successfully zero'ed out original file "+DATA/TESTCDB3/9D48FF44CE8A7D32E053F4071FACDFB3/DATAFILE/enctest.318.1085912727"
(3):Successfully deleted original file "+DATA/TESTCDB3/9D48FF44CE8A7D32E053F4071FACDFB3/DATAFILE/enctest.318.1085912727"
The same happens for decryption as well. For example primary (testcdb) and one more stnadby (testcdb2) could have encrypted tablespaces while other standby (testcdb3) has an un-encrypted tablespace. Running online decryption results in tablespace in both primary and standby with encrypted tablespace getting decrypted. No error is shown on the standby that had the un-encrypted tablespace and alert log output rekeying of datafiles similar to above.
2021-10-14T10:49:06.058622+00:00
(3):Rekeying datafile +DATA/TESTCDB3/9D48FF44CE8A7D32E053F4071FACDFB3/DATAFILE/enctest.319.1085913199 (21) to +DATA
2021-10-14T10:49:07.523466+00:00
(3):Rekey operation committed for file +DATA/TESTCDB3/9D48FF44CE8A7D32E053F4071FACDFB3/DATAFILE/enctest.318.1085914147
2021-10-14T10:49:09.539791+00:00
(3):About to zero out original file "+DATA/TESTCDB3/9D48FF44CE8A7D32E053F4071FACDFB3/DATAFILE/enctest.319.1085913199"
(3):Successfully zero'ed out original file "+DATA/TESTCDB3/9D48FF44CE8A7D32E053F4071FACDFB3/DATAFILE/enctest.319.1085913199"
(3):Successfully deleted original file "+DATA/TESTCDB3/9D48FF44CE8A7D32E053F4071FACDFB3/DATAFILE/enctest.319.1085913199"
Related Posts
Encrypting / Decrypting Tablespaces Online / Offline
19c Encryption
12c Encryption
Rekeying Master Key in a Data Guard Setup
