Loading Data to ATP from Object Storage

There are serveal ways to load data to ATP from object storage. This post shows examples of few of those options.

Copying data from object storage to a table in ATP
1. The copy_data method in dbms_cloud package is used to achieve this. Before the data could be copied over the required policies, permissions and credentails should be craeted. The necessary permissions and credentails creation steps are same as the ones needed to export/import to/from object storage. Implement step 1 to 8 in the earlier post. Even though not directly used in the package, access to data_pump_dir is expected. Without it the copy_data method result in an error as below. So sure to grant read,write access on the data_pump_dir to the user schema.

begin
DBMS_CLOUD.COPY_DATA ('TEST','ATP_TABLES','https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/#namespace#/tabledata/test2.csv','ASANGA',format => json_object('type' VALUE 'CSV','skipheaders' value '1'));
end;
Error report -
ORA-20000: ORA-06564: object DATA_PUMP_DIR does not exist
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 1285
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 4290
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 4311
ORA-06512: at line 2
20000. 00000 -  "%s"
*Cause:    The stored procedure 'raise_application_error'
           was called which causes this error to be generated.
*Action:   Correct the problem as described in the error message or contact
           the application administrator or DBA for more information.

2. A table called test already exists in the ATP database with three columns.

desc test
Name Type         
---- ------------ 
A    NUMBER       
B    VARCHAR2(10) 
C    NUMBER

3. A csv file exists in the object storage bucket with the data that needs to be copied into the table. First few lines of this file is shown below.

"A","B","C"
998,"abc 998",20958
999,"abc 999",20979
1000,"abc 1000",21000
1,"abc 1",21
2,"abc 2",42
3,"abc 3",63

4. Execute copy data procedure

begin
DBMS_CLOUD.COPY_DATA (table_name => 'TEST',
                    credential_name => 'ATP_TABLES',
                    file_uri_list => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/#namespace#/tabledata/test2.csv',
                    schema_name => 'ASANGA',
                    format => json_object('type' VALUE 'CSV','skipheaders' value '1'));
end;
/

5. Check the data is available in the table. If for whatever reason the copying of data fails then a log table is shown as below which could be looked up to find the reason for failure.

ORA-20003: Reject limit reached, query table "ASANGA"."COPY$11_LOG" for error details

Creating external table in ATP
1. Another way to load data is to create an external table. It is assumed that all necessary permissions and credentails are in place.
2. Use create_external_table procedure to create the extenral table. The external data file used for this external table resides in the object storage.

BEGIN  
   DBMS_CLOUD.CREATE_EXTERNAL_TABLE(   
      table_name =>'test_ex_table',   
      credential_name =>'ATP_TABLES',   
      file_uri_list => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/#namespace#/tabledata/test2.csv',
      format => json_object('type' value 'csv', 'skipheaders' value '1'),   
      field_list => 'A,B,C',   
      column_list => 'A number,   
                     B varchar2(20),
                     C number');
   END;
/

3. This will create an external table in the ATP.

desc test_ex_table
Name Type         
---- ------------ 
A    NUMBER       
B    VARCHAR2(20) 
C    NUMBER

4. The external table could be validated with validate_external_table procedure.

exec DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE (table_name =>'test_ex_table', 	schema_name => 'ASANGA');

If there are issues in the data file used for the external table the procedure would give an output similar to below. The validation_log table could be refered to find out the reason for failure.

Error starting at line : 53 in command -
BEGIN DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE (table_name =>'test_ex_table', 	schema_name => 'ASANGA'); END;
Error report -
ORA-20003: Reject limit reached, query table "ASANGA"."VALIDATE$14_LOG" for error details
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 1259

5. The external table could be queried as any other

select * from test_ex_table;

Once all verification steps are run, the data from external table could be loaded to a ATP "internal" table with create as statement.

create table test as select * from test_ex_table;

Creating external table with dump file
1. Only difference between this method and previous method is the source of the data. In previous case it was a csv whereas here it will be a dump file. The dumpfile must be created using ORACLE_DATAPUMP access driver. In this example the following table's data is exported to dump file using the access driver as below.

SQL> desc DATETEST
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 VERSION                                            VARCHAR2(20)
 INDATE                                             DATE

SQL> CREATE TABLE datetest_ex
    ORGANIZATION EXTERNAL
    (
      TYPE ORACLE_DATAPUMP
      DEFAULT DIRECTORY dumpdir
      LOCATION ('datetest.dmp')
    )
    AS SELECT * FROM datetest;

Above SQL will generate a dump file in the database directory dumpdir. Upload this to the object storage.

2. Create the external table using the dump file. For the dump files the type is specified as datapump.

BEGIN
   DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
    table_name =>'test_dmp_table',
    credential_name =>'ATP_TABLES',
    file_uri_list =>'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/#namespace#/tabledata/datetest.dmp',
    format => json_object('type' value 'datapump', 'rejectlimit' value '1'),
    column_list => 'VERSION VARCHAR2(20), INDATE date' );
END;
/

3. The validation of the external table could be done the same way as before with validate_external_table procedure.
4. Query and move data to internal table as needed.

select * from test_dmp_table;

Creating external partitioned table in ATP
Variant of the external table is the creation of external partition table. In this example the two csv files contains data for the table. Each file contains a specific range of values. The file test_1000.csv has data from 1 to 1000 in column A while file test_2000.csv has values from 1001 to 2000. Range partitions are created encapsulating this.
1. The create command for external partitioned table is shown below.

BEGIN  
   DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE(
      table_name =>'test_ex_part_table',  
      credential_name =>'ATP_TABLES',
      format => json_object('type' value 'csv', 'skipheaders' value '1'), 
      column_list => 'A number, B varchar2(20), C number',
      partitioning_clause => 'partition by range (A)
								(
                                partition p1 values less than (1000) location (''https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/#namespace#/tabledata/test_1000.csv''),
                                partition p2 values less than (2000) location (''https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/#namespace#/tabledata/test_2000.csv'')
                                 )'
     );
   END;
/

2. Validate the external partitioned table with

exec DBMS_CLOUD.VALIDATE_EXTERNAL_PART_TABLE (table_name => 'test_ex_part_table',	schema_name => 'ASANGA');

Similar to external table, if validation encounters any errors an validation_log table will be shown.
3. Query the external paritioned table. As with normal partitioned tables, the partition pruning would happened depending on the predicates used.

Creating hybrid partitioned table in ATP
Hybrid partitioned table has data sources that are internal and external. For example few partitions could be created for external data files (similar to external tables) while another parition may store its data internally in the table itself. The external location data sources are specified with the key words "external location". Internal paritions do not have any reference to location. 1. Below hybrid partitioned table use the same external data file as previous case and has an additional internal parition for values between 2001-3000.

BEGIN  
   DBMS_CLOUD.CREATE_HYBRID_PART_TABLE(
      table_name =>'test_hyb_part_table',  
      credential_name =>'ATP_TABLES',
      format => json_object('type' value 'csv', 'skipheaders' value '1'), 
      column_list => 'A number, B varchar2(20), C number',
      partitioning_clause => 'partition by range (A)
                                (
                                partition p1 values less than (1000) external location (''https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/#namespace#/tabledata/test_1000.csv''),
                                 partition p2 values less than (2000) external location (''https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/#namespace#/tabledata/test_2000.csv''),
                                 partition p3 values less than (3000)
                                 )'
     );
   END;
/

2. Validation of the hybrid partitioned table could be done with

exec DBMS_CLOUD.VALIDATE_HYBRID_PART_TABLE (table_name => 'test_hyb_part_table',	schema_name => 'ASANGA');

3. Similar to partitioned table, based on predicate partition pruning will happen when queried.

DBMS_CLOUD Package in DBCS VM DB

The dbms_cloud package comes built in in autonomous databases. Starting with 19.9 the dbms_cloud package could be installed in other databases as well. This has been documented in 2748362.1. There were no issues encountered while setting up the package in DBCS VM DB following the 2748362.1. The downloaded ssl files were stored in commonstore.

define sslwalletdir=/opt/oracle/dcs/commonstore/wallets/ssl

What was noticable is that unlike in autonomous database certain functionality has been restricted.
Files could be uploaded to storage bucket from the DBCS VM DB.

SQL> exec dbms_cloud.put_object('BACKUP_CRED','https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/#namespace#/tabledata/exptest.dmp','DATA_PUMP_DIR','exp.dmp');

PL/SQL procedure successfully completed.

The files in a bucket could be listed as well.

SQL> select object_name, bytes from dbms_cloud.list_objects('BACKUP_CRED','https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/#namespace#/tabledata');

OBJECT_NAME BYTES
----------- ---------
exptest.dmp 2932736

The package allows getting files from storage bucket.

SQL> exec dbms_cloud.get_object('BACKUP_CRED','https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/#namespace#/tabledata/exptest.dmp','DATA_PUMP_DIR','gotfrombucketexp.dmp');

PL/SQL procedure successfully completed.

ls /u01/app/oracle/admin/ORCL/dpdump/BBAE40CB362D1F2CE0535406F40A0C73/
gotfrombucketexp.dmp

However, unlike in ATP importing and exporting from and to storage bucket fails.
Importing

impdp asanga@uatpdb credential=BACKUP_CRED dumpfile='https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/#namespace#/tabledata/exptest.dmp' directory=data_pump_dir logfile=impnew.log logtime=all metrics=yes

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/#namespace#/tabledata/exptest.dmp" for read
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

Exporting

expdp  asanga@uatpdb  dumpfile='DEFAULT_CREDENTIAL:https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/#namespace#/tabledata/expnew.dmp' directory=data_pump_dir logfile=newexp.log exclude=STATISTICS logtime=all metrics=yes

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39088: file name cannot contain a path specification

After an SR oracle support stated that this feature is only available for autonomous and not available for other databases. Even if, now dbms_cloud package allows creation of credentials and 19c version of expdp/impdp allow credentails.

Another difference is the creation of external tables. It is possible to create external tables using csv files.

SQL>    DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
          table_name =>'test_ex_table',
          credential_name =>'BACKUP_CRED',
          file_uri_list => 'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/#namespace#/tabledata/test2.csv',
          format => json_object('type' value 'csv', 'skipheaders' value '1'),
		  field_list => 'A,B,C',
          column_list => 'A number,
                          B varchar2(20),
                     C number');
       END;
      /

PL/SQL procedure successfully completed.

SQL> select * from test_ex_table;

         A B                             C
---------- -------------------- ----------
       998 abc 998                   20958
       999 abc 999                   20979
      1000 abc 1000                  21000
         1 abc 1                        21
         2 abc 2                        42
         3 abc 3                        63

However, querying an external table created using datapump file fails.

SQL> BEGIN
   DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
    table_name =>'test_dmp_table',
        credential_name =>'BACKUP_CRED',
    file_uri_list =>'https://swiftobjectstorage.uk-london-1.oraclecloud.com/v1/#namespace#/tabledata/datetest.dmp',
        format => json_object('type' value 'datapump', 'rejectlimit' value '1'),
    column_list => 'VERSION VARCHAR2(20), INDATE date' );
    END;
/ 

PL/SQL procedure successfully completed.

SQL> select * from test_dmp_table;
select * from test_dmp_table
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-11010: unable to open at least one dump file for fetch

Same dumpfile and bucket works for ATP.

Enabling MFA for IDCS User Accounts in OCI

OCI has two types of user accounts IAM native and IDCS (also refered to as federated user accounts). For IAM accounts user has to enable MFA for their own account. Admin cannot enable MFA for another account. For federated accounts either tenancy admin or a user in IDCS_Administrators group must first setup MFA. Afterwards users can enroll themselves for MFA. This post shows steps for setting up MFA on IDCS.
The steps beging with the assumption that user (tenancy admin or user in IDCS_Administrators group) has already login to the IDCS console.
First step is to configure the factors used for MFA. This include deciding on the hashing algorithm used for passcode generation, how long a passcode is valid and etc. During the initial setup MFA with mobile app is setup (it's best practice to have two different type of MFA. Email based MFA will be setup afterwards).
On the IDCS console select the top left menu and expand Security and select Factors menu item. Select the mobile app tab and configure mobile app MFA related properties.

Next select MFA menu item under security menu and check the mobile app passcode. Both mobile app passcode and notification are selected by default. Click save to confirm the changes.

Third step is to enforce the MFA. This is done via a Sign-On policies. There's a default sign-on policy already created. It is possible to edit the existing one or add a new one.

For this post a new policy is created for MFA. Click add to begin the wizard. First item is the policy name. Click next to proceed to creating a sign-on rule.

A sign-on rule is created such that if a login is done using username and password then additional authentication factors are prompted. Since currently only mobile app passcode and notification are setup these options are selected as additonal factors. MFA prompt is set for everytime a login is done and enrollment is set for optional. This is useful until MFA setup is completed and verified.

The saved sign-on rule will be listed as below.

Next step is to add an app to the sign-on policy. For this select the automatically created SAML app which has the OCI-V2-App"tenancy name" as the name. Oracle doc describe this as

In order for MFA to be prompted this app must be selected. Without it no MFA will be prompted. In the apps tab select assign and search for the above app (applicable to the current tenancy) and select.

At this stage the sign-on rule is in inactive state. Select the menu next to the rule and activate it.

Next login to OCI console as a federated user. Even though enrollment was set to optional the console does prompt user to enable MFA (it is always possible to skip this and enable MFA later via IDCS console. Shown later in the post). To enable MFA enable secure verification button.

This will present a QR code that is compatible with Oracle mobile authenticator (OMA) app. If not alreayd done, download OMA app to phone and scan the QR code to add the account (i.e enroll to MFA). This QR code will not work with google authenticator. If google authenticator is desired then select use another authenticator app.

As mentioned earlier it is possible to enroll to MFA later on via IDCS console. Below screenshots show these steps. Login to IDCS console as the user that need MFA enabling. Select My Profile and then the security tab. Click enable button for 2-step verification.

Since only mobile app is enabled for MFA, the 2-step vericiation list mobile app. Click the mobile app button and scan the QR code as mentioned before.

Once done the device will be listed under mobile app.

Next time when the IDCS (federated) user login the MFA is prompted. Could use both mobile app notification (select allow on the mobile phone) or entering the passcode (alternative method).

As mentioned in the begining it is considered best practice to have two MFA methods. If only mobile app based MFA is configured then loss of the phone means unable to access the account and have to go through the recovery option. Below steps shows how to add email based MFA after adding the mobile app bsaed MFA (it is possible to setup both these options at the same time as well. No need to do one after another).
In the IDCS console expand security and then select factors. In the email tab configure MFA related properties.

Select MFA and check the email checkbox. Since mobile app is already configured those would have been already checked.

Next need to edit the sign-on policy created earlier. Select the policy menu and click on edit.

Select the sign-on rule inside the policy and select edit.

In the rule select email as an additional factor for authentication. This will already have mobile app selected.

To enroll email based MFA login as the user enabling MFA to the IDCS console (My profile -> security) and select configure on email option.

This will send a passcode to the email in the account. Enter the passcode to verify the email address. Once verified email will be added to MFA.

Next time when user login email is also presented as an alternative login method. When selected passcode is sent to the email.

Update 26 September 2023
With the recent changes to IDCS and IAM domains the above MFA setup will not work. To get MFA working again enable the "Security Policy for OCI Console" policy. This is provided by Oracle and will be in disable state.

Inside are two rules, one for admins and one for all users. If needed tweak them if not leave as it is.

This policy has the "OCI Console" as the app. No need to change it leave it as it is. "MFA Policy" created earlier could be deactivated now. All the previously registered MFA methods (mobile app, email) will work as before without re-regise

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 where 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.