PL/SQL Blocks, Roles and Definer's and Invoker's Rights

As per Oracle security doc "definer's rights and invoker's rights are used to control access to the privileges necessary during the execution of a user-created procedure, or program unit.
In a definer's rights procedure, the procedure executes with the privileges of the owner. The privileges are bound to the schema in which they were created. An invoker's rights procedure executes with the privileges of the current user, that is, the user who invokes the procedure."
However, due to how roles are treated in pl/sql blocks an odd behaviour could be observed as below. The test case involves creating a user with a custom role that has few privileges.

SQL> create user testuser identified  by test321 default tablespace users quota unlimited on users;

User created.

SQL> create role testrole;

Role created.

SQL> grant create session, create table, create procedure to testrole;

Grant succeeded.

SQL> grant testrole to testuser;

Grant succeeded.

Connect as the testuser and verify user can execute actions using each of the privileges.

sqlplus testuser/test321@devpdb

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 23 20:49:03 2021
Version 19.12.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> create table x  (a number);

Table created.

SQL> create or replace function t return number as
  2  begin
  3  return 1;
  4  end;
  5  /

Function created.

So user can connect, create a table and a function.

Next create a function that dynamically creates a table.

SQL> create or replace function testfunc return number as
begin
    execute immediate 'create table  testtable as select * from x';
    return 1;

end;
/
Function created.

This function executes a creat table as statement. However, calling the function result in an error.

SQL> declare
  2  i number;
  3  begin
  4  i := testfunc;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "TESTUSER.TESTFUNC", line 3
ORA-06512: at line 4

Intresting that it says insufficient privileges. If the create table as statement is run from a sql prompt it runs fine.

SQL> create table  testtable as select * from x;

Table created.

The issue here is that "all roles are disabled in any named PL/SQL block that executes with definer's rights". By default, each procedure is a definer's rights unit.
In this case both the executor of the procedure (invoker) and owner (definer) of the objects referenced by it are the same. Nontheless, because roles are disabled in the named PL/SQL block the function fails as it can't see the create table privilege needed.
This seems strange error to have since failure only happens when invovked through the function. Each statement inside the function would execute without any error when run outside the function on its own.

There are two ways to fix this.
One is to grant the create table privilege directly to the user rather than to the role.

grant create table to testuser;

SQL> declare
    i number;
begin
   i := testfunc;
end;
/

PL/SQL procedure successfully completed.

Second method is to run the function with invoker's right (even though in this case both definer and invoker are the same). Named PL/SQL blocks that execute with invoker's rights and anonymous PL/SQL blocks are executed based on privileges granted through enabled roles (Remember to revoke the create table from user if it was granted in previous method).

create or replace function testfunc return number authid current_user as
begin
    execute immediate 'create table  testtable as select * from x';
    return 1;

end;
/

SQL> declare
    i number;
begin
   i := testfunc;
end;
/

PL/SQL procedure successfully completed.

Another odd behaviour is when PL/SQL object references other objects. Using the same example as above grant execute on dbms_lob to role.

SQL> grant execute on dbms_lob to testrole;

Grant succeeded.

Assuming there's a table with a blob column, the dbms_lob package could be used to get the length of the data in lob column.

SQL> select DBMS_LOB.GETLENGTH(b) from blob_table;

DBMS_LOB.GETLENGTH(B)
---------------------
                    2

But if the call to dbms_lob is included in a function it will fail to compile. Complain is it cannot identify the dbms_lob package.

SQL> CREATE OR REPLACE FUNCTION blobsize RETURN NUMBER AS
i number;
BEGIN
    select DBMS_LOB.GETLENGTH(b) into i from blob_table;
    RETURN i;
END;
/  
Warning: Function created with compilation errors.

SQL> show errors;
Errors for FUNCTION BLOBSIZE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/5      PL/SQL: SQL Statement ignored
4/12     PL/SQL: ORA-00904: "DBMS_LOB"."GETLENGTH": invalid identifier

This situation is different to earlier one. Oracle development guide states that "if the subprogram or package that you create references schema objects, then you must have the necessary object privileges for those objects. These privileges must be granted to you explicitly, not through roles".
Even though it may seems odd, but the behaviour is by design. To fix this grant exeucte on dbms_lob direct to user.

SQL>  grant execute on dbms_lob to testuser;

Grant succeeded.

Afterwards package compiles without error.

SQL> alter function blobsize compile;

Function altered.

Using UR=A to Connect to Databases in Nomount Mode

Time to time there are situation were connecting to a database in nomount mode is needed. Most noteably is the data guard standby creation. When a database is started in nomount mode

startup nomount;

the service on the listener would have a blocke status.

Service "devcdb" has 1 instance(s).
  Instance "devcdb", status BLOCKED, has 1 handler(s) for this service...

Any attempt to connect to the instance using service name will fail.

sqlplus sys@devcdb as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 14 20:27:16 2021
Version 19.12.0.0.0

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

ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections

The TNS entry used is shown below.

DEVCDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = test-4)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = devcdb)
    )
  )

One solution is to create a static listener entries as done when creating data guard configurations.



Other options is to use UR=A in the TNS entry. With the use of UR=A in TNS entry there's no need to create a static listener entry. Dynamic listener would allow connection without any issue.

DEVCDBUR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = test-4-254.codegen.net)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = devcdb)
      (UR = A)
    )
  )
  
sqlplus sys@devcdbur as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 14 20:47:13 2021
Version 19.12.0.0.0

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

Enter password:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL>

Even during normal DG operation use of UR=A in TNS entries used for DG traffic could be useful. During switchover primary could attmept to connect to standby which is in the nomount state (progressing to mount or read only). Without UR=A the primary connection would fail (and succeed later after reattempt, once standby has transitioned out of nomount). To reduce the false alerts occuring in these situation UR=A could be used.
For example without UR=A entry it took nearly 15s for DG broker to connect to the new standby during a switchover (output on a 19.13 system).

DGMGRL> switchover to dgtest
Performing switchover NOW, please wait...
Operation requires a connection to database "dgtest"
Connecting ...
Connected to "dgtest"
Connected as SYSDBA.
New primary database "dgtest" is opening...
Oracle Clusterware is restarting database "dgtest3" ...
[W000 2021-10-25T13:49:46.227+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:47.231+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:48.234+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:49.237+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:50.240+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:51.242+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:52.245+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:53.248+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:54.250+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:55.252+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:56.255+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:57.257+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:58.260+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:49:59.262+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:50:00.265+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:50:01.274+00:00] Failed to attach to dgtest3tns.
Connected to "dgtest3"
Connected to "dgtest3"
Switchover succeeded, new primary is "dgtest"

However, with UR=A in the TNS entry it was able to connect sooner.

DGMGRL> switchover to dgtest
Performing switchover NOW, please wait...
Operation requires a connection to database "dgtest"
Connecting ...
Connected to "dgtest"
Connected as SYSDBA.
New primary database "dgtest" is opening...
Oracle Clusterware is restarting database "dgtest3" ...
[W000 2021-10-25T13:55:24.705+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:55:25.708+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:55:26.710+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:55:27.714+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:55:28.716+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:55:29.718+00:00] Failed to attach to dgtest3tns.
[W000 2021-10-25T13:55:30.722+00:00] Failed to attach to dgtest3tns.
Connected to "dgtest3"
Connected to "dgtest3"
Switchover succeeded, new primary is "dgtest"

UR=A is used in the TNS entries created in DBCS VM DBs that also has data guard enabled.

Related Metalink note
Connections to NOMOUNT/MOUNTED or RESTRICTED Databases Fail [ID 362656.1]

Update 28 January 2022
Entries similar to "[W000 2021-10-25T13:55:24.705+00:00] Failed to attach to dgtest3tns" are due to bug 30870248. Apply the patch if available for the RU.

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.