SYS User Account Password Expiry Date Not Updated After Password Change

Due to the bug 28538439 the sys user password expiry date does not get updated on the dba_users view after a password change. This could be reproduced as shown below (DB version 19.13).

SQL> select USERNAME,ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE,CREATED,PROFILE from dba_users where username = 'SYS';

USERNAME   ACCOUNT_ST LOCK_DATE EXPIRY_DA CREATED   PROFILE
---------- ---------- --------- --------- --------- ----------
SYS        OPEN                 07-NOV-21 11-MAY-21 DEFAULT

Change the sys user password

alter user sys identified by hello_1234;

User altered.

The expiry date for sys user is not updated.

USERNAME   ACCOUNT_ST LOCK_DATE EXPIRY_DA CREATED   PROFILE
---------- ---------- --------- --------- --------- ----------
SYS        OPEN                 07-NOV-21 11-MAY-21 DEFAULT

The bug first started on 12.2 (refere 2518310.1 and 2482400.1) and patch for bug 28538439 does exists. For 19c this is already included in DBRU (checked on 19.10, 19.12 and 19.13)

$ORACLE_HOME/OPatch/opatch lsinv | grep 28538439
     28534475, 28534493, 28535127, 28537481, 28538439, 28541606, 28542455

Even with the patch inplace the hidden parameter "_enable_ptime_update_for_sys" must be set to true. By default this parameter has the value false. Use query here to check hidden parameter values.
After the parameter is set to true restart the database

alter system set "_enable_ptime_update_for_sys"=true scope=spfile;

After the restart the expiry column would still show the old expiry date even though password was reset.

USERNAME   ACCOUNT_ST LOCK_DATE EXPIRY_DA CREATED   PROFILE
---------- ---------- --------- --------- --------- ----------
SYS        OPEN                 07-NOV-21 11-MAY-21 DEFAULT

Resetting the password again will show the updated value.

 alter user sys identified by hello_1234

USERNAME   ACCOUNT_ST LOCK_DATE EXPIRY_DA CREATED   PROFILE
---------- ---------- --------- --------- --------- ----------
SYS        OPEN                 01-MAY-22 11-MAY-21 DEFAULT

It seems that value on expiry date column has no influence on the usage if password is set before its life time ends (this was later confirmed by MOS). Below is an output from a DBCM VM DB created with LVM as the storage option.

select USERNAME,ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE,CREATED,PROFILE from dba_users where username='SYS';
NAME                           VALUE      DEFLT     TYPE                 DESCRIPTION
------------------------------ ---------- --------- -------------------- --------------------------------------------------
_enable_ptime_update_for_sys   FALSE      TRUE      boolean              Enable/Disable update of user$ for sys


USERNAME   ACCOUNT_ST LOCK_DATE EXPIRY_DA CREATED   PROFILE
---------- ---------- --------- --------- --------- ----------
SYS        OPEN                 16-JUN-19 17-APR-19 DEFAULT

Hidden parameter is not set and sys password is already expired. Even though database was created recently.

SQL> select created from v$database;

CREATED
---------
02-NOV-21

There's no issue in connecting to the DB as sys.

sqlplus  sys@TESTPW_LHR19D as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 2 13:07:45 2021
Version 19.12.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Enter password:


Connected to:
Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL>

Expiring the password manually seem to update the expiry date value.

SQL> alter user sys password expire;

User altered.

SQL> select USERNAME,ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE,CREATED,PROFILE from dba_users where username='SYS';

USERNAME   ACCOUNT_ST LOCK_DATE EXPIRY_DA CREATED   PROFILE
---------- ---------- --------- --------- --------- ----------
SYS        EXPIRED              02-NOV-21 17-APR-19 DEFAULT

But setting the password again seem to set the expiry date to (created date + PASSWORD_LIFE_TIME) which could be again in the past depending on the created date.

SQL> SELECT RESOURCE_NAME,LIMIT from dba_profiles where profile='DEFAULT' and RESOURCE_NAME='PASSWORD_LIFE_TIME';

RESOURCE_NAME                    LIMIT
-------------------------------- ------------------------------
PASSWORD_LIFE_TIME               60


SQL>  alter user sys identified by hello_1234;

User altered.

SQL>  select USERNAME,ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE,CREATED,PROFILE from dba_users where username='SYS';

USERNAME   ACCOUNT_ST LOCK_DATE EXPIRY_DA CREATED   PROFILE
---------- ---------- --------- --------- --------- ----------
SYS        OPEN                 16-JUN-19 17-APR-19 DEFAULT

SQL> select EXPIRY_DATE-CREATED from dba_users where username='SYS';

EXPIRY_DATE-CREATED
-------------------
                 60

Useful MOS Docs
After Changing SYS Password, DBA_USERS.EXPIRY_DATE Not Updated For SYS User [ID 2518310.1]
Sys Password Reset Is Not Reflecting In Sys.user$ PASSWORD_CHANGE_DATE [ID 2482400.1]

Encrypting / Decrypting Tablespaces Online / Offline in a Data Guard Configuration

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 mode
Once 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:
SUCCESS

If 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 mode
Similar 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 access

Offline 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 access

Stopping 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-only

Only 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

Enabling Database Management on DBCS VM DB

Database management service could be used to monitor both external and cloud databases. This post shows the steps for enabling database management on DBCS VM DB.
Before enabling DB management there are few pre-req steps that must be completed. DB managment service uses private endpoint to establish connection to the DB. This private endpoint is different to the private endpoint used for data safe. Infact any private endpoints created for data safe won't be visible in the drop down list of the enable DB management window. Therefore, create a database management specific endpoint via Observability & Management -> Administration (under Database Management) -> Private Endpoints.

As the next allow the network where private endpoint reside to communicate with the network where the database reside. Allowing ingress and egress access on port 1521 for private endpoint network is the easiest way to accomplish this. Below is the entries added to the security list associated with the subnet used for database.

Database management need a username and password to connect to the database. The password used must be stored in an OCI vault. The database management service access the vault and get the password to connect to the database. For this case the password for dbsnmp user is stored in the vault secrets and called dbsnmp_secret.

Also connected to vault, the DB managemnt service called "dpd" must granted permission to access the secret in the vault. This could be done by creating a policy which allows dpd service to access the vault.

DBCS VM DB has dbsnmp user precreated as a common user. But the account is in locked status. Unlock the user account and grant following privileges and roles. In this case dbsnmp is used. It is possible to use a different user as well.

alter user dbsnmp account unlock;
alter user dbsnmp identified by pwd_here;

GRANT CREATE PROCEDURE TO dbsnmp container=all;
GRANT SELECT ANY DICTIONARY, SELECT_CATALOG_ROLE TO dbsnmp container=all;
GRANT ALTER SYSTEM TO dbsnmp container=all;
GRANT ADVISOR TO dbsnmp container=all;
GRANT EXECUTE ON DBMS_WORKLOAD_REPOSITORY TO dbsnmp container=all;

That concludes the pre-reqs.



The documentation states that certain features are not available on PDBs but testing showed otherwise.

It could be that documentation is not updated with the latest capabilities and features).
Enabling of DB management could be done via Observability & Management -> Administration (under Database Management) or clicking the enable button in the database detail page.

This will open the enable database management dialog and will be pre-filled with details of the database. By default the service name is populated with the service name of the cdb$root (however, it is possible give the service name of the PDB as well and enabling DB managment at PDB level.

The main consideration is which option to choose. Full management gives lot more capabilities at a cost whereas basic appears to be free.

Once the enable database managment button is clicked, montior the enabling of database management via work requests.

Once enabled, the DB management will populate the metrics charts.

The DB Management service will automatically install necessary PL/SQL packages needed the very first time performance hub is used. This is not done in subsequent uses of performance hub.

Could also view the ASH Analytics and SQL monitoring sections. This is true even when PDB service name was used for enabling (document states not available for PDBs).

If full option was selected, then there would be additional tabs such as addm and blocking sessions.

If cdb service was used during the enabling process then the SQL monitor will show both pdb and cdb related SQLs.

On the otherhand if PDB service name was used during enabling then only PDB related SQLs are shown.

DB management enable with basic could be later changed to full and vice versa. Also a connection to the cdb could be changed to PDB and vice versa.

With the full option additonal capabilities are enabled and could be access via Observability & Management -> Fleet Summary. Below screenshots show Fleet summary showing PDB related details (when PDB service name was used for enabling)

Below shows the fleet summary page when CDB service was used for enabling.

When CDB is viewed on the fleet summary, no PDB related information is shown. It could be this fact that the documentation states when it says "currently not available for PDBs".
As a final note, when performance hub is used, every time a monitoring SQL runs it generates an audit record if auditing policy includes "select any dictionary" (CIS audit policy requires this). Below is a sample of such audit records.

EVENT_TIMESTAMP                ACTION_NAM OBJECT_NAME      DBUSERNAME OBJECT_SCH OS_USERNAM SQL_TEXT
------------------------------ ---------- ---------------- ---------- ---------- ---------- --------------------------------------------------------------------------------
20-OCT-21 02.16.08.785885 PM   SELECT     GV_$SQL_PLAN     DBSNMP     SYS        odosvc     select /*+ opt_param('parallel_execution_enabled',
20-OCT-21 02.16.08.790501 PM   SELECT     X$KQLFXPL        DBSNMP     SYS        odosvc     SELECT /*+ opt_param('parallel_execution_enabled','false') */
20-OCT-21 02.16.08.790909 PM   SELECT     GV$SQL_PLAN      DBSNMP     SYS        odosvc     SELECT /*+ opt_param('parallel_execution_enabled','false') */
20-OCT-21 02.16.08.791373 PM   SELECT     GV_$SQL_PLAN     DBSNMP     SYS        odosvc     SELECT /*+ opt_param('parallel_execution_enabled','false') */
20-OCT-21 02.16.08.800590 PM   SELECT     X$KQLFXPL        DBSNMP     SYS        odosvc     select /*+ opt_param('parallel_execution_enabled',

This has been mentioned in a previous post as well. Take neccessary actions to avoid rapid growth of audit records due to the use of performance hub.