TDE and Tls Encryption for Oracle database in 19C

Thought of documenting this since on exacc we have pluggable database and it uses tde. Having this article will help as one stop to get insight of Tde   wallets management 

For  both TDE and TlS  we create wallet .  In case of TDE wallet contains encryption keys and TLS store contains certificates 

Exacc   there is also 1 more external wallet  called as  Secure External Password Store (SEPS) for  storing sys password which is managed by mkstore  .  Will cover SEPS  in another  Blog 

This Blog is dedicated to  Tde and Tls 

################################################################

Encryption of Data at Rest (TDE)

################################################################

-- Encryption of the Data Files on Disk

Main Steps For TDE Include 

• TDE DB Parameter Changes
• Configure a Software KeyStore
• Create Master Key
• Create Auto-login KeyStore
• Encryption of Tablespaces
• Backup Keys

 

When we  talk about encryption  it could be either  columnar encryption or Tablespace encryption . Here we are going discuss Tablespace encryption . 

Wallet Types ; 

- United – TDE master encryption key for CDB and all PDBs are in same keystore

- Isolated – TDE master encryption key for CDB and PDB’s are in individual PDB’s keystores

Oracle database 12c introduced a new way to manage keystores, encryption keys and secrets using the ADMINISTER KEY MANAGEMENT command. 

This replaces the ALTER SYSTEM SET ENCRYPTION KEY and ALTER SYSTEM SET ENCRYPTION WALLET commands for key and wallet administration from previous releases.

KeyStroke File can  be placed as File or Under Hsm .  TDE_CONFIGURATION parameter is used to control location of key storoke file .

If using Oracle 18c or later, the sqlnet.ora file is officially deprecated

and you should use the WALLET_ROOT and TDE_CONFIGURATION parameters.

New parameters to Set in the Database

 ENCRYPT_NEW_TABLESPACES - specifies whether to encrypt newly created user tablespaces (AES128)

 WALLET_ROOT - specifies the path to the root of a directory tree containing the wallet ❋

 TDE_CONFIGURATION - specifies the Key Store Type being used (Hardware / Software)

 _TABLESPACE_ENCRYPTION_DEFAULT_ALGORITHM ❋

 - specifies default algorithm to use for encryption

❋ WALLET_ROOT parameter was introduced in 19c, prior to this the wallet location was specified in the   sqlnet.ora file. Eg:

 ENCRYPTION_WALLET_LOCATION =

 (SOURCE =

 (METHOD = FILE)

 (METHOD_DATA =

 (DIRECTORY = /u01/app/oracle/product/12.1.0.2/db_1/network/admin/$ORACLE_SID/wallet) ) )

 ❋ In 19c this is an underscore parameter. The regular parameter without the underscore was introduced in 20c. See Doc ID 2654121.1

 Patch 30398099 must be installed to use this parameter (Patch 30398099 has been integrated into the April 2021 RU for Oracle Database  release 19c (19.11))

TDE DB Parameter Changes

1. Set the Parameters

SQL> ALTER SYSTEM SET ENCRYPT_NEW_TABLESPACES = 'ALWAYS' SCOPE = SPFILE SID = '*';

SQL> ALTER SYSTEM SET WALLET_ROOT = '+DATA/PRMYDB-RAC' SCOPE = SPFILE SID ='*';

SQL> ALTER SYSTEM SET "_tablespace_encryption_default_algorithm" = "AES256" SCOPE=SPFILE SID='*’;

2. Bounce the Database

$ srvctl stop database –d prmydb-rac

$ srvctl start database –d prmydb-rac

3. Set the type of Key Store

SQL>  ALTER SYSTEM SET TDE_CONFIGURATION = "KEYSTORE_CONFIGURATION=HSM|FILE" SCOPE=BOTH SID='*';

Configure a Software KeyStore

The CDBs keystore is used to store encryption keys for all the associated PDBs, but they each need their own master encryption key. The master encryption key for the PDB must be exported before an unplug operation, so it can be imported after a subsequent plugin operation.

 SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '+DATA/PRMYDB-RAC/TDE' IDENTIFIED BY "<passwd>";

 SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN FORCE KEYSTORE IDENTIFIED BY "<passwd>";     --> Open wallet 

 SQL> select wrl_type,wrl_parameter,status from gv$encryption_wallet;

-->  For Pluggable database execute same On Pdb  

 SQL> ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE  IDENTIFIED BY "<passwd>" WITH BACKUP;    --> Amend  wallet   (master encryption key) 

 SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '+DATA/PRMYDB-RAC/TDE' IDENTIFIED BY "<tde-passwd  --> Create the Master Key for TDE Encryption on the Database

 SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '+DATA/PRMYDB-RAC/TDE’ IDENTIFIED BY "<passwd>"; --> Create the KeyStore to be an Auto-Login Software KeyStore

$ srvctl stop database –d PRMYDB-RAC

$ srvctl start database –d PRMYDB-RAC

 

SQL> select wrl_type,wrl_parameter,status from gv$encryption_wallet;

--> Copy Key Store file on Standby 

orapki wallet display -wallet /u01/app/oracle/admin/${DB_UNIQUE_NAME}/wallet/tde

orapki wallet display -wallet ewallet.p12 -summary

You need to create and activate a master key in the root container and one in each of the pluggable databases. Using the CONTAINER=ALL clause does it in a single step. If the CONTAINER=ALL clause is omitted, it will only be done in the current container and will need to be done again for each PDB individually. Information about the master key is displayed using the V$ENCRYPTION_KEYS view.

Eg 

ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY myPassword WITH BACKUP CONTAINER=ALL;

-- Open

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY myPassword CONTAINER=ALL;

-- Close

ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY myPassword CONTAINER=ALL;

Enable TDE using  Isolated method  for Newly Plugged Database . 

Step 1 : Configure init.ora parameter to enable file based wallet. 

sqlplus /nolog <<!

conn /as sysdba

Ensure PDB is not in restricted mode

-- switch to the target PDB

alter session set container = PDBNAME;

show con_name

show parameter tde_configuration

alter system set tde_configuration='KEYSTORE_CONFIGURATION=FILE';

Step 2 Create MEK ( Master encryption key ) using key management service

administer key management create keystore identified by "passsword";  

administer key management set key force keystore identified by "passsword" with backup;

Step 3: Create AOW ( Auto wallet login)

-- identify the wallet location

col wrl_parameter new_value wallet_location

select wrl_parameter from v$encryption_wallet;

-- create Auto Wallet login

--update the wallet location with output of the previous command [ LN01103U is CDB and 7ABB7DFB3F0A0F0DE0531B5CF40A0FF4 is GUID of PDB ]

--eg  wallet_location '/var/opt/oracle/dbaas_acfs/LN01103U/wallet_root/7ABB7DFB3F0A0F0DE0531B5CF40A0FF4/tde'

administer key management create auto_login keystore from keystore 'wallet_location' identified by "password";

Step 4 : Enable AOW ( either close/open wallet or bounce PDB if we have downtime )

set lines 200 pages 300

col WRL_PARAMETER for a30

col wrl_type for a8

select * from gv$encryption_wallet order by inst_id, con_id;

NB: here WALLET_TYPE output shows as PASSWORD.. so we need to set as auto login without putting container down

administer key management set keystore close identified by "password";

administer key management set keystore open identified by "password";

--follow step 6 if we get downtime for this pdb

Step 5: Verify wallets are AUTOLOGIN and OPEN state

set lines 200 pages 300

col WRL_PARAMETER for a30

col wrl_type for a8

select * from gv$encryption_wallet order by inst_id, con_id;

NB: here WALLET_TYPE output shows as AUTOLOGIN..

Step 6: Bounce PDB if possible & repeat step 5 for verification .

incase if you get downtime

[ BEGIN ]

alter pluggable database PDBNAME close immediate instances = all;

alter pluggable database PDBNAME open instances = all;  

[ END ]

Tablespace Encryption : 

Online Encryption of the Tablespaces done on the Primary Only.

The Standby DB is Encrypted using the Data Guard Redo Stream.

The parameter TABLESPACE_ENCRYPTION = MANUAL_ENABLE (default)

This allows you to encrypt Tablespaces if the DB is licensed for Advanced Security.

AUTO_ENABLE = All TS are encrypted by default (Cloud) cannot decrypt.

DECRYPT ONLY = Cannot encrypt TS, prevent licence violatio

You can set the ENCRYPT_NEW_TABLESPACES database initialization

parameter to automatically encrypt future tablespaces that you

create.

➢ SQL> alter system set "_tablespace_encryption_default_algorithm" = 'AES256' scope =

both;

➢ alter system set encrypt_new_tablespaces = ALWAYS scope = both;

--> Online Ecryption : 

SQL> select t.name,e.ENCRYPTIONALG,e.ENCRYPTEDTS,e.STATUS from V$ENCRYPTED_TABLESPACES e,v$tablespace t where t.ts#=e.ts#(+);

 

SQL> ALTER TABLESPACE APP_TS1 ENCRYPTION ONLINE USING 'AES256' ENCRYPT ;

SQL> ALTER TABLESPACE TOOLS ENCRYPTION ONLINE USING 'AES256' ENCRYPT ;

SQL> ALTER TABLESPACE USERS 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#(+);

 

SQL> CREATE TABLESPACE TEST_NEW_ENC_TS DATAFILE SIZE 1G ENCRYPTION USING 'AES256' DEFAULT STORAGE (ENCRYPT);  --> Creation of a New Tablespace (Primary)

--> Offline Tablespace Encryption on the Primary Database 

 

SQL> ALTER TABLEPACE APP_TS1 OFFLINE;

SQL> select t.name as ts_nme,d.name, d.file# from v$tablespace t, v$datafile d where t.ts# = d.ts# and t.name = 'APP_TS1' order by file#;

SQL> alter database datafile 2 encrypt;

SQL> ALTER TABLEPACE APP_TS1 ONLINE;

 For smaller tablespaces we encrypted at the Tablespace level

 SQL> ALTER TABLESPACE USERS OFFLINE;

 SQL> ALTER TABLESPACE USERS ENCRYPTION OFFLINE ENCRYPT;

 SQL> ALTER TABLESPACE USERS ONLINE;

--> Decryption of a Tablespace

SQL> ALTER TABLESPACE APP_TS1 ENCRYPTION ONLINE DECRYPT;

SQL> ALTER TABLESPACE APP_TS1 OFFLINE;

SQL> ALTER TABLESPACE APP_TS1 ENCRYPTION OFFLINE DECRYPT;

SQL> ALTER TABLESPACE APP_TS1 ONLINE;

Recovering forgot Oracle Database TDE Wallet Password. 

Ideally There is no staright  way to recover Tde password there seems some indirect  shared online which is shared below using wallet merge 

Below are some of schenario where we  need TDE Wallet Password Incase of Auto-Login Configured:

To perform REKEY operation

To generate new master key

To export key during PDB migration

During PDB Remote Cloning

To Migrated file based wallet to OKV

Wallet Merge Steps 

1.Take backup of current wallet files

2.Connect to database

3.Create brand new TDE wallet/Keystore

4.perform merge operation

5.perform validation using orapki/mkstore

6.Create Auto-Login

7.Copy New files to wallet_root parameter location

8.close and open wallet from database level.

Take Backup

cp /u01/app/oracle/admin/<DB_UNIQUE_NAME>/tde /backup/wallet_backup_01AUG2023/

Create New Wallet:

sqlplus / as sysdba

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '<NEW_LOCATION>' IDENTIFIED BY Welcome;

Merge TDE Wallet:

ADMINISTER KEY MANAGEMENT MERGE KEYSTORE '<ORIGINAL_LOCATION_FILES>' INTO EXISTING KEYSTORE '<NEW_LOCATION>'  IDENTIFIED BY "<NEW_KEYSTORE_PASSWORD>" WITH BACKUP;

Perform Validation:

orapki wallet display -wallet <NEW_LOCATION>

Note: above command will ask password, enter the new wallet password. post that it will display master keys.

Creating Auto-Login:

ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '<NEW_LOCATION' IDENTIFIED by Welcome;

Copy both files to original Location of tde wallet files and also copy to other nodes, as well as standby databases.

*** DONE ***

Views : 

set pages 300  lines 250 feedback off 

col wrl_parameter format a60 

col wrl_type format a5

col name format a8 

col status format a10 

select  inst_id , con_id , name , wrl_type , wrl_parameter , status , keystore_mode , wallet_type , wallet_order from gv$encryption_wallet natural join gv$containers order by wrl_type, inst_id , con_id , name ; 

-- Masterkey details 

set lines 300 

col name format a15 

select inst_id , name , masterkeyid  from  gv$containers natural join  gv$database_key_info ; 

 select con_id, mkloc from x$kcbdbk   ;

set lines 300

col name for a15

col wrl_type for a10

col status for a20

select p.con_id, p.name, p.open_mode, ew.wrl_type, ew.wallet_type, ew.status 

from v$pdbs p join v$encryption_wallet ew on (ew.con_id = p.con_id)

order by p.con_id;

set lines 300

col name for a10

col key_id for a60

col creation_time for a40

select p.con_id, p.name, p.open_mode, ek.key_id --, ek.creation_time, ek.KEY_USE

from v$pdbs p left outer join v$encryption_keys ek on (ek.con_id = p.con_id)

order by p.con_id;

select p.con_id, p.name, p.open_mode, ew.wrl_type, ew.wallet_type, ew.status 

from v$pdbs p join v$encryption_wallet ew on (ew.con_id = p.con_id)

where p.name = 'PDB2';

select * from v$encrypted_tablespaces ; 

select tablespace_name , encrypted from  dba_tablespaces  ; 

select file# , encrypted from  v$datafile_header ; 

set linesize 250 pagesize 250

column name format a40

column masterkeyid_base64 format a60

select  name,utl_raw.cast_to_varchar2( utl_encode.base64_encode('01'||substr(mkeyid,1,4))) || utl_raw.cast_to_varchar2( utl_encode.base64_encode(substr(mkeyid,5,length(mkeyid)))) masterkeyid_base64  FROM (select t.name, RAWTOHEX(x.mkid) mkeyid from v$tablespace t, x$kcbtek x where t.ts#=x.ts#);

select con_id, wallet_type,wrl_parameter, status from v$encryption_wallet;

select t.name,e.ENCRYPTIONALG,e.ENCRYPTEDTS,e.STATUS from V$ENCRYPTED_TABLESPACES e, v$tablespace t where t.ts#=e.ts#(+);

exit

References : 

https://docs.oracle.com/en/database/oracle/oracle-database/19/asoag/managing-keystore-and-tde-master-encryption-key.html#GUID-0C080CA8-1812-4DCD-AC30-FE47534C7962

################################################################

Encryption of Data In-Transit (TLS1.2)

################################################################

-- Encryption of data over the Network Layer

Main Steps For TLS Include 

• Certificate Creation
• Signing of Certificates (CA)
• Wallets
• Add Certificates into a Wallet
• Network Parameter Changes
• Database Parameter Changes
• Application Connection 

• Components:

– Certificate Authority (CA): a trusted third party that certifies the identity of entities,

such as users, databases, administrators, clients, and servers.

– Certificates: created when an entity's public key is signed by a trusted certificate

authority (CA).

– Wallet: a container that stores authentication and signing credentials, including

private keys, certificates, and trusted certificates SSL

– Certificates revocation lists: validity of CA signed certificates 

Certificate Authority (CA)

• A third party trusted by both of the communicating parties (e.g.Verisign)

• Validates, identities and issue/revoke certificates

• The CA uses its private key to encrypt a message

• The CA public key is well known and does not have to be authenticated each time it is accessed (browsers, wallets, etc.)

• Organization can use in-house CA (e.g. MS Certificate services)

Publicly Trusted CAs

• A trusted third party (TTP) used as CA for the certificates

• Commercial

– Verisign, Digicert, GoDaddy

• Web browsers includes by default public keys of TTPs CA

• De-facto standard for websites, as certificates from non trusted CAs are reported by default as dangerous

Signature

• One-way hash of the data (certificate) encrypted with signer’s private key – it cannot be reversed

• Receiver validates the integrity of the data:

– Receiver gets the data and signature

– Data is decrypted using sender’s public key

– Signature is decrypted using sender’s public key

– New signature is created using same algorithm

– Both new and received signature should match if data was not tampered

Wallet

• A file storing authentication and signing credentials, including private keys,

certificates, and trusted certificates SSL needs.

• Oracle server and client using SSL needs a wallet file

– configured in sqlnet.ora, listener.ora, optional in tnsnames.ora (instead of sqlnet)

– Must be auto-login

• Managed with Oracle Wallet Manager and orapki tool

Protocol Year

SSL 1.0 No

SSL 2.0 1995

SSL 3.0 1996

TLS 1.0 1999

TLS 1.1 2006

TLS 1.2 2008

TLS 1.3 2018

We will use an Oracle Wallet to securely store and retrieve credentials such as certificates, certificate requests, and private keys. The wallet will be created in a directory of your choice on your database server. In examples that follow, we use the placeholder <server_wallet_directory> to represent that directory name.

The use of PKI (orapki) encryption with Transparent Data Encryption is deprecated.

Use the ADMINISTER KEY MANAGEMENT SQL statement. 

We create the wallet using the Oracle Public Key Infrastructure (orapki) utility. The wallet will be created with two additional options.

The auto_login_local option allows the database to read the values of the wallet without requiring interactive password authentication. Additionally, it enforces that the wallet may only be opened by the user that created the wallet on the machine where the wallet was created. Because of this security requirement you will need to create the wallet as the same user that your database executes as.

The wallet also requires that a password be specified for it. We will use the placeholder <server_wallet_password> in the examples to represent that value.

Connections use SSL or TLS depending on the cipher suites selected. Place the ciphers in the strongest-to-weakest order in the list.

_____________________________________________

Wallet  Configuration  Steps 

___________________________________________

1) 

Set the parameters common_user_prefix, os_authent_prefix and remote_os_authent to NULL in the database.

alter system set common_user_prefix='' scope=spfile;

alter system set os_authent_prefix='' scope=spfile;

alter system set remote_os_authent=FALSE scope=spfile;

2) Update server sqlnet.ora settings:

WALLET_LOCATION =

   (SOURCE =

     (METHOD = FILE)

     (METHOD_DATA = (DIRECTORY = /u01/app/oracle/pki/server_wallet)     )

  )  

SQLNET.AUTHENTICATION_SERVICES= (TCPS,IPC,BEQ) 

SSL_CLIENT_AUTHENTICATION = TRUE   ( or false ) 

SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_GCM_SHA384,SSL_RSA_WITH_AES_128_CBC_SHA256,SSL_RSA_WITH_AES_256_CBC_SHA256,SSL_RSA_WITH_AES_128_CBC_SHA256,SSL_RSA_WITH_AES_128_CBC_SHA256,SSL_RSA_WITH_AES_128_CBC_SHA,SSL_RSA_WITH_3DES_EDE_CBC_SHA,SSL_RSA_WITH_RC4_128_SHA,SSL_RSA_WITH_RC4_128_MD5) 

SSL_VERSION = 1.2 or 1.1

sqlnet.crypto_checksum_client = ACCEPTED    ( or  requested ) 

sqlnet.crypto_checksum_server = ACCEPTED  ( or  requested ) 

sqlnet.crypto_checksum_types_server=(SHA356, SHA1) 

3) Update server listener.ora settings:

WALLET_LOCATION =( SOURCE=(METHOD=FILE) ( EMOTHOD_DATA=(DIRECTORY= /u01/app/oracle/pki/server_wallet ))) 

SSL_CLIENT_AUTHENTICATION=FALSE

SSL_VERSION=1.2

4)  Create the Server Wallet and Certificate Signing Request (CSR). In this example we are using this password: Welcome1234.

mkdir /u01/app/oracle/pki/server_wallet

cd /u01/app/oracle/pki/server_wallet

orapki wallet create -wallet /u01/app/oracle/pki/server_wallet -auto_login -pwd Welcome123

orapki wallet display -wallet /u01/app/oracle/pki/server_wallet/

5) Create a self-signed certificate and load it into the wallet.

orapki wallet add -wallet /u01/app/oracle/pki/server_wallet -dn "CN=oracle12c.eem.com" -keysize 2048 -sign_alg sha256 -pwd Welcome1234

parameter value

dn A user specified distinguished name. For example: "CN=ORCLCDB,O=testsecurity,C=US"

keysize The certificates keysize must be one of the 3 values, either (512|1024|2048)

validity The number of days the certificate should be valid for

self_signed Self-sign the certification

6) Export the CSR into a file.

Finally we will export the server certificate from the wallet to a file to use later with the client wallet.

orapki wallet export -wallet /u01/app/oracle/pki/server_wallet -dn "CN=oracle12c.eem.com" -request devserver.txt

orapki wallet export -wallet <server_wallet_directory> -pwd <server_wallet_password> -dn "CN=ORCLCDB,O=testsecurity,C=US" -cert /tmp/oracle-db-certificate.crt

$ cat  /tmp/oracle-db-certificate.crt

 

7)  If the Certificate Authority (CA) is unable to process a CSR with MD5 algorithm then the below commands need to be run (Oracle MOS Note 1448161.1).

openssl pkcs12 -in ewallet.p12 -nodes -out nonoracle_wallet.pem

openssl req -new -key nonoracle_wallet.pem -out devserver.csr

8)  (LOAD TRUESTED CERT)   Submit the CSR to the CA for them to generate a certificate.Once the CA provides the server  certificate, upload the certificate chain to the wallet.

orapki wallet add -wallet /u01/app/oracle/pki/server_wallet -trusted_cert -cert /u01/app/oracle/pki/server_wallet/ca_cert.crt -pwd Welcome1234

orapki wallet add -wallet /u01/app/oracle/pki/server_wallet -trusted_cert -cert /u01/app/oracle/pki/server_wallet/chain.crt -pwd Welcome1234

orapki wallet add -wallet /u01/app/oracle/pki/server_wallet -user_cert -cert /u01/app/oracle/pki/server_wallet/devserver.crt -pwd Welcome1234

orapki wallet display -wallet /var/opt//oracle/wallets/

orapki cert display  -cert /var/opt//oracle/wallets/ -summary 

9)  Modify endpoints  in scan listener and restart  listeners 

[oracle@myserver ~]$ crsctl stat res -p |grep ENDPOINTS

[oracle@myserver ~]$ srvctl modify listener -p "TCP:1521/TCPS:2484"

[oracle@myserver ~]$ srvctl modify scan_listener -p "TCP:1521/TCPS:2484"

[oracle@myserver ~]$ crsctl stat res -p |grep -B20 ENDPOINTS | grep -e ENDPOINTS -e "^NAME="

New TCPS listener should be added to the list of listeners in the LOCAL_LISTENER database parameter

– Static registration can be used for single instances instead

ALTER SYSTEM SET LOCAL_LISTENER='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)...) (ADDRESS=(PROTOCOL=TCPS)...)))' scope=both; 

$ srvctl stop scan_listener ; srvctl start scan_listener ; srvctl status scan_listener

$ srvctl stop listener ; srvctl start listener ; srvctl status listener

For Rac :

1. Update the listener.ora at $GRID_HOME/network/admin (WALLET_LOCATION parameter is the location of the server wallet. In the examples below, we are using the shared server wallet location for Oracle RAC.)

The file listener.ora must be updated to enable a port for secure communication and specify the location of the Oracle wallet.

Please note, when specifying the value for the DIRECTORY parameter make sure to use a fully qualified path to the <server_wallet_directory> location.

For now, we are going to continue to use username/password authentication, so we will disable SSL client authentication on the server by setting the SSL_CLIENT_AUTHENTICATION property to FALSE.

Next, we will add the key SECURE_PROTOCOL_LISTENER and set it to use the more secure Oracle IPC (which only allows communication with other processes on the system) rather than the TCP protocol. By setting this we are instructing the listener to only accept administration and registration requests from the IPC protocol.

Lastly, update the listener entry to enable support for TCPS traffic by changing the protocol from TCP to TCPS and the PORT to 2484.

SSL_CLIENT_AUTHENTICATION = FALSE

SECURE_PROTOCOL_LISTENER=(IPC)

WALLET_LOCATION =

  (SOURCE =

   (METHOD = FILE)

    (METHOD_DATA =

     (DIRECTORY = /acfs/oracle/pki_wallet/server_wallet)

    )

  )

2. Update sqlnet.ora at $GRID_HOME/network/admin

The file sqlnet.ora must also be updated to enable secure communications. Like the changes we made to the listener.ora file, the SSL_CLIENT_AUTHENTICATION and WALLET_LOCATION need to be set.

Once again, when specifying the value for DIRECTORY, make sure to use a fully qualified path to the <server_wallet_directory> location you specified.

Lastly, we need to specify the property SSL_CIPHER_SUITES to define which encryption algorithms the server should use/require to encrypt network traffic.

SQLNET.ENCRYPTION_SERVER = required

The parameter ENCRYPTION_SERVER has the following options:

REQUESTED – to enable the security service if the client allows it 

REQUIRED – to enable the security service and disallow the connection if the client is not enabled for the security service 

ACCEPTED – to enable the security service if required or requested by the client 

REJECTED – to disable the security service, even if required by the client

WALLET_LOCATION =

  (SOURCE =

    (METHOD = FILE)

    (METHOD_DATA =

      (DIRECTORY = /acfs/oracle/pki_wallet/server_wallet)

    )

  )

SQLNET.ENCRYPTION_TYPES_SERVER = (AES256,AES192)

SQLNET.ENCRYPTION_TYPES_CLIENT = (AES256,AES192)

SQLNET.CRYPTO_CHECKSUM_CLIENT = ACCEPTED

SQLNET.CRYPTO_CHECKSUM_SERVER = ACCEPTED

SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA256,SHA1,SHA384)

SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (SHA256,SHA1,SHA384)

###############  SSL  ########################

SQLNET.AUTHENTICATION_SERVICES= (TCPS, BEQ, IPC)

SSL_CLIENT_AUTHENTICATION = TRUE

SSL_CIPHER_SUITES = (SSL_RSA_WITH_3DES_EDE_CBC_SHA,SSL_RSA_WITH_AES_256_CBC_SHA,SSL_RSA_WITH_AES_128_CBC_SHA,SSL_RSA_WITH_3DES_EDE_CBC_SHA)

SSL_VERSION = 1.2 or 1.1

3. Update sqlnet.ora at $ORACLE_HOME/network/admin

WALLET_LOCATION =

  (SOURCE =

    (METHOD = FILE)

    (METHOD_DATA =

      (DIRECTORY = /acfs/oracle/pki_wallet/server_wallet)

    )

  )

SQLNET.ENCRYPTION_TYPES_SERVER = (AES256,AES192)

SQLNET.ENCRYPTION_TYPES_CLIENT = (AES256,AES192)

SQLNET.CRYPTO_CHECKSUM_CLIENT = ACCEPTED

SQLNET.CRYPTO_CHECKSUM_SERVER = ACCEPTED

SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA256,SHA1,SHA384)

SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (SHA256,SHA1,SHA384)

4. Restart the database(s) and all listeners so that the new settings take effect.

5. Connect via sqlplus using the TCPS tnsnames entry.

6. Toad: the username should be EXTERNAL and the password field is left blank.

7. SQL Developer: Requires version 17 of SQL Developer and perform the following steps:

a. Select Tools-> Preferences-> Database-> Advanced

b. Check – ‘Use Oracle Client’ and ‘Use OCI/Thick Driver’

c. Set Oracle Homes and TNS Names Directory

d. Create New Connection (Select the TCPS TNS Names Entry)

e. Select OS Authentication

f. Once connected, run the below queries to verify the connection.

Select user from dual;

SELECT SYS_CONTEXT ('USERENV', 'AUTHENTICATED_IDENTITY') FROM DUAL;

SELECT SYS_CONTEXT ('USERENV', 'AUTHENTICATION_METHOD') FROM DUAL; 

SELECT SYS_CONTEXT ('USERENV', 'NETWORK_PROTOCOL') FROM DUAL;

 
Client Side TLS Wallet Configuration 
  

Now that we have the server set up for encrypted communication, we also need to set up encryption in the client as well. The client will also use an Oracle Wallet to store the server security certificate to encode and decode communication with the server.

As noted in the assumptions section, this article assumes you have a machine with the Oracle database client installed. We will create another Oracle Wallet (this time on the client) with the following command, again setting the wallet to enable the auto_login_local option and setting a password. As above we will use a placeholder <client_wallet_directory> in examples that follow to represent the directory name you have chosen.

orapki wallet create -wallet <client_wallet_directory> -auto_login_local -pwd <client_wallet_password>

1) Create a new auto-login wallet.

orapki wallet create -wallet "c:\app\oracle\wallet" -pwd WalletPasswd123 -auto_login_local

2) Create a self-signed certificate and load it into the wallet.

orapki wallet add -wallet "c:\app\oracle\wallet" -pwd WalletPasswd123 -dn "CN=%computername%" -keysize 1024 -self_signed -validity 3650

3)  Now we need to add the server security certificate (the file oracle-db-certificate.crt we generated from the database server) to the client wallet to facilitate encrypted communication. For example

orapki wallet add -wallet <client_wallet_directory> -pwd <client_wallet_password> -trusted_cert -cert oracle-db-certificate.crt

orapki wallet add -wallet "c:\app\oracle\wallet" -pwd WalletPasswd123 -trusted_cert -cert c:\ol7-121.localdomain-certificate.crt

orapki wallet display -wallet "c:\app\oracle\wallet" -pwd WalletPasswd1234

4)   Update the client side sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES=(TCPS,BEQ)
NAMES.DIRECTORY_PATH= (TNSNAMES)
SSL_CLIENT_AUTHENTICATION = TRUE
SSL_VERSION = 1.1
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_GCM_SHA384,SSL_RSA_WITH_AES_128_CBC_SHA256,SSL_RSA_WITH_AES_256_CBC_SHA256,SSL_RSA_WITH_AES_128_CBC_SHA256,SSL_RSA_WITH_AES_128_CBC_SHA256,SSL_RSA_WITH_AES_128_CBC_SHA,SSL_RSA_WITH_3DES_EDE_CBC_SHA,SSL_RSA_WITH_RC4_128_SHA,SSL_RSA_WITH_RC4_128_MD5)

WALLET_LOCATION = (SOURCE = (METHOD=MCS))
Or
WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = <server_wallet_directory>)
    )
  )


4)   Update the client tnsnames.ora file to include an entry for the TCPS connection.

First, we will need to update the file tnsnames.orain your client to register the new TCPS listener on your database server. You should replace SERVER_ADDRESS with the IP Address or FQDN of the server hosting your database.
Update the net_service_name entry (in this example named SERVER) to enable support for TCPS traffic by changing the protocol from TCP to TCPS and the PORT to 2484.


testdb_TCPS=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCPS)
      (HOST=oracle12c.aem.com)
      (PORT=1522)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=testdb.aem.com)
    )
  )



6)  Update the database user account to be identified by the certificate (this will be different for every user based on Distinguished Name of their certificate).

alter user ggarrison identified externally as 'CN=lastname.firstname.middlename,OU=PKI,C=US';

 
To check that encryption is effectively taking place, execute the following SQL query on the database server side:
 

SQL> select network_service_banner from v$session_connect_info
 where sid in (select distinct sid from v$mystat);

7)   Exchange Cert:     Export   client  cert and  import in  server   (  This  is not needed for OU class Cert ) 

Export the certificate so we can load it into the server later.

c:\>orapki wallet export -wallet "c:\app\oracle\wallet" -pwd WalletPasswd123 -dn "CN=%computername%" -cert c:\%computername%-certificate.crt

Load the client certificate into the server wallet.

$ orapki wallet add -wallet "/u01/app/oracle/wallet" -pwd WalletPasswd123  -trusted_cert -cert /tmp/my-computer-certificate.crt

orapki wallet display -wallet "/u01/app/oracle/wallet" -pwd WalletPasswd123

SSL Troubleshooting Guide (Doc ID 166492.1)

• ORA-12560: TNS:protocol adapter error

• ORA-28862: SSL connection failed

– 28759, 00000, "Failed to open file"

– 28859, 00000, "SSL negotiation failure"

– ntzCreateConnection: failed with error 549

• ORA-29024:Certificate Validation Failure

• ORA-29143: Message 29143 not found

• ORA-29106: Can not import PKCS # 12 wallet

• ORA-28860: Fatal SSL error

• ORA-29263: HTTP protocol error

• ORA-28868: certificate chain check failed • ORA-28750: unknown error  

• ORA-28865: SSL connection closed

• ORA-01004: Default username feature not supported; log denied

• ORA-28864: SSL connection closed gracefully

• ORA-01017: invalid username/password; logon denied

• alert.log: "SSL Client: Server DN doesn't contain expected SID name"

• ORA-29113: Cannot access attributes from a PKCS #12 key bag.

• ORA-29002: SSL transport detected invalid or obsolete server certificate

• ORA-29003: SSL transport detected mismatched server certificate

• ORA-28857: Unknown SSL Error

Reference : 

https://docs.oracle.com/en/cloud/paas/data-safe/admds/create-wallet-or-certificates-tls-connection.html#TASK_QZ2_XRQ_ZHB

https://2019.hroug.hr/eng/content/download/20367/433976/file/Nelson+Calero+-+SSL+certificates+in+the+Oracle+Database+without+surprises.pdf

https://docs.oracle.com/en/database/oracle/oracle-database/18/dbseg/configuring-secure-sockets-layer-authentication.html

 

https://blogs.oracle.com/dev2dev/ssl-connection-to-oracle-db-using-jdbc,-tlsv12,-jks-or-oracle-wallets

################################################################

################################################################

   

  

Oracle Sql performance Analyzer using Sql tuning sets for testing Optimizer version change .

Since  between  Migrations we were required  to compare sql performance  with different optimizer modes .   Here old  tool  SPA  helped us    .  

Documenting handy steps of myself and will help  others Too . 

The steps involved in the SQL PERFORMANCE Analyzer are

1) Create the Sql Tuning Set (STS).

2) Create a task to run Sql Performance Analyzer.

3) Execute Before Change TEST EXECUTE (Pre-Change SQL Trial).

         Make a change that needs to tested.

4) Execute After Change TEST EXECUTE (Post-Change SQL Trial).

5) Comparing SQL Trials.

6) Generate Compare report.

You can use the SQL Performance Analyzer to analyze the SQL performance impact of any type of  system change. Examples of common system changes include:

•Database upgrades

•Configuration changes to the operating system, hardware, or database

•Database initialization parameter changes

•Schema changes, such as adding new indexes or materialized views

•Gathering optimizer statistics

•SQL tuning actions, such as creating SQL profiles

Check  what metrics  SPA compare performance of : 

SQL> SELECT metric_name FROM v$sqlpa_metric;

METRIC_NAME   

-------------------------

PARSE_TIME               

ELAPSED_TIME             

CPU_TIME                 

USER_IO_TIME             

BUFFER_GETS              

DISK_READS               

DIRECT_WRITES            

OPTIMIZER_COST           

IO_INTERCONNECT_BYTES  

 

9 rows selected.

Report generation Options : 

DBMS_SQLPA.REPORT_ANALYSIS_TASK(

  task_name      IN VARCHAR2,

  type           IN VARCHAR2 := 'text',

  level          IN VARCHAR2 := 'typical',

  section        IN VARCHAR2 := 'summary',

  object_id      IN NUMBER   := NULL,

  top_sql        IN NUMBER   := 100,

  task_owner     IN VARCHAR2 := NULL,

  execution_name IN VARCHAR2 := NULL)

RETURN CLOB;

Level	Description
BASIC	Same as typical
TYPICAL (default)	Information about all statements
ALL	Details of all SQL
IMPROVED	Only improved SQL
REGRESSED	Only regressed SQL
CHANGED	SQL with changed performance
UNCHANGED	Opposite of CHANGED
CHANGED_PLANS	Only SQL with plan changes
UNCHANGED_PLANS	Opposite of above
ERRORS	SQL with errors only

SPA  Steps :

1)  create a STS

exec DBMS_SQLTUNE.create_sqlset (sqlset_name=> '94rn6s4ba24wn') ;

DECLARE

  cursor1 DBMS_SQLTUNE.SQLSET_CURSOR;

BEGIN

  OPEN cursor1 FOR SELECT VALUE(p)

  FROM TABLE(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''94rn6s4ba24wn''')) p;

 

  DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'STS01', populate_cursor => cursor1);

END;

/

2)  create a SPA task and associate the STS with it 

DECLARE

  task_name VARCHAR2(64);

  sts_task  VARCHAR2(64);

BEGIN

  task_name := 'Task01';

 

  sts_task:=DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => 'STS01', task_name => task_name, description => 'Task for sql_id 94rn6s4ba24wn');

END;

/

3) Execute CONVERT SQLSET Before Change TEST EXECUTE 

SQL> EXEC DBMS_SQLPA.RESET_ANALYSIS_TASK('Task01');

SQL> EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'Task01', execution_type => 'CONVERT SQLSET', execution_name => 'convert_sqlset');

 

Generate the report with a SQL statement like this:

SQL> SET LONG 999999 longchunksize 100000 linesize 200 head off feedback off echo off

SQL> spool task01_before_change.html

SQL> SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('Task01', 'HTML', 'ALL', 'ALL') FROM dual;

SQL> spool off

result file that with this particular execution all different plans are displayed, while in compare performance objective SPA is taking only one plan (the one it is currently parsing with information currently available). In any case comparing all plans of same sql_id would provide very complex reports that would probably be not usable…

4) Create Tets execute task ( pre and post changes  ) 

SQL> EXEC DBMS_SQLPA.RESET_ANALYSIS_TASK('Task01');

SQL> EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'Task01', execution_type => 'TEST EXECUTE', execution_name => 'before_change');

 SET LONG 999999 longchunksize 100000 linesize 200 head off feedback off echo off

SQL> spool task01_before_change.html

SQL> SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('Task01', 'HTML', 'ALL', 'ALL') FROM dual;

SQL> spool off

SQL> ALTER SESSION SET optimizer_features_enable='11.2.0.2';

SQL> EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'Task01', execution_type => 'TEST EXECUTE', execution_name => 'after_change');

 SET LONG 999999 longchunksize 100000 linesize 200 head off feedback off echo off

SQL> spool task01_after_change.html

SQL> SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('Task01', 'HTML', 'ALL', 'ALL') FROM dual;

SQL> spool off

5)  Run Compare Performance : 

SQL> EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'Task01', execution_type => 'COMPARE PERFORMANCE', execution_name => 'compare_performance');

 

PL/SQL PROCEDURE successfully completed.

Or 

BEGIN

  DBMS_SQLPA.execute_analysis_task(

    task_name        => :v_task,

    execution_type   => 'compare performance', 

    execution_params => dbms_advisor.arglist(

                          'execution_name1', 

                          'before_change', 

                          'execution_name2', 

                          'after_change')

    );

END;

/

  SET LONG 999999 longchunksize 100000 linesize 200 head off feedback off echo off

SQL> spool task01_compare_performance.html

SQL> SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('Task01', 'HTML', 'ALL', 'ALL') FROM dual;

SQL> spool off

Sample Compare report is  published below :

https://abdul-hafeez-kalsekar-tuning.blogspot.com/2024/02/spa-sample-compare-report.html

Views : 

select sql_id, plan_hash_value, buffer_gets, elapsed_time, substr(sql_text,1, 30

) text, executions from dba_sqlset_statements 

where sqlset_name = :sts_name

order by sql_id, plan_hash_value;

SQL> SET lines 200

SQL> col description FOR a30

SQL> SELECT * FROM dba_sqlset;

SELECT task_name, status

FROM dba_advisor_tasks

WHERE task_name = :tname;

select execution_name,status, execution_end

from DBA_ADVISOR_EXECUTIONS where task_name='my_sqlpa_demo_task';

SQL> SELECT execution_name, execution_type, TO_CHAR(execution_start,'dd-mon-yyyy hh24:mi:ss') AS execution_start,

     TO_CHAR(execution_end,'dd-mon-yyyy hh24:mi:ss') AS execution_end, status

     FROM dba_advisor_executions

     WHERE task_name='Task01';

SQL> SELECT execution_name, execution_type, TO_CHAR(execution_start,'dd-mon-yyyy hh24:mi:ss') AS execution_start,

     TO_CHAR(execution_end,'dd-mon-yyyy hh24:mi:ss') AS execution_end, advisor_name, status

     FROM dba_advisor_executions

     WHERE task_name='Task01';

SQL> SELECT last_execution,execution_type,TO_CHAR(execution_start,'dd-mon-yyyy hh24:mi:ss') AS execution_start,

     TO_CHAR(execution_end,'dd-mon-yyyy hh24:mi:ss') AS execution_end,status

     FROM dba_advisor_tasks

     WHERE task_name='Task01';

SQL> col EXECUTION_NAME FOR a15

SQL> SELECT execution_name, plan_hash_value, parse_time, elapsed_time, cpu_time,user_io_time,buffer_gets,disk_reads,direct_writes,

     physical_read_bytes,physical_write_bytes,rows_processed

     FROM dba_advisor_sqlstats

     WHERE task_name='Task01';

SQL> col PLAN FOR a140

SQL> SET pages 500

SQL> SELECT p.plan_id, RPAD('(' || p.ID || ' ' || NVL(p.parent_id,'0') || ')',8) || '|' ||

     RPAD(LPAD (' ', 2*p.DEPTH) || p.operation || ' ' || p.options,40,'.') ||

     NVL2(p.object_owner||p.object_name, '(' || p.object_owner|| '.' || p.object_name || ') ', '') ||

     'Cost:' || p.COST || ' ' || NVL2(p.bytes||p.CARDINALITY,'(' || p.bytes || ' bytes, ' || p.CARDINALITY || ' rows)','') || ' ' ||

     NVL2(p.partition_id || p.partition_start || p.partition_stop,'PId:' || p.partition_id || ' PStart:' ||

     p.partition_start || ' PStop:' || p.partition_stop,'') ||

     'io cost=' || p.io_cost || ',cpu_cost=' || p.cpu_cost AS PLAN

     FROM dba_advisor_sqlplans p

     WHERE task_name='Task01'

     oder BY p.plan_id, p.id, p.parent_id;

SQL> col message FOR a80

SQL> col FINDING_NAME FOR a30

SQL> col EXECUTION_NAME FOR a20

SQL> SELECT execution_name,finding_name,TYPE,impact,message FROM dba_advisor_findings WHERE task_name='Task01';

Reference : 

SQL Performance Analyzer Example (Doc ID 455889.1)

https://docs.oracle.com/en/database/oracle/oracle-database/19/admqs/analyzing-database-changes-sql-performance.html#GUID-28869D5B-ECCD-4A89-8391-116AE4C6A7D4

Retention and Purging Unified Audit record data from Oracle database

I have   documented in below   article how to  enable  auditing . However  we need to  also understand  how to retain and  purge audit data 

Ideally audit data   resides  in sysaix    tablespace .  We need to move   same to other tablespace 

How to enable audit : 

https://abdul-hafeez-kalsekar.blogspot.com/2020/05/oracle-database-auditing.html

Below   steps are will be used to execute  following s for unified audit data 

1) Changing Unified Audit table location 

2)  Changing Unified Audit table partition range 

3)  Changing Unified audit write mode 

4) Purging Unified audit  data 

5)  Taking backup of Unified audit data 

Audit Location : 

Changing location of AUD$UNIFIED

begin

  dbms_audit_mgmt.set_audit_trail_location(

    audit_trail_type => dbms_audit_mgmt.audit_trail_unified,

    audit_trail_location_value => 'NEW_TSPACE');

end;

/

begin

  dbms_audit_mgmt.set_audit_trail_location(

    audit_trail_type => dbms_audit_mgmt.audit_trail_db_std,

    audit_trail_location_value => 'NEW_TSPACE');

end;

/

SQL> col owner format a10

SQL> col table_name format a15

SQL> col tablespace_name format a12

SQL> col interval format a20     

SQL> set lines 200

          

SQL> select owner,table_name,interval,partitioning_type,partition_count,def_tablespace_name from dba_part_Tables where owner=’AUDSYS’;

Change Audit table partition range : 

By default aud$unified  table are monthly partitioned  which we  will be  converting to  daily .  

Please note  this  will take effect  from next month    as current data is already enabled with monthly partition . 

 

begin 

dbms_audit_mgmt.alter_partition_interval   ( 1 , 'DAY') ;

END ; 

/ 

Write Mode : 

 Default  wrote mode for  unified audit   is queued mode  where  there is  chance   we will loose audit data   as  audit data is written to memory first and then   to  disk 

 we will be changing  write mode to  immediate 

Confirm from the DBMS_AUDIT_MGMT.GET_AUDIT_TRAIL_PROPERTY_VALUE function.

SET SERVEROUTPUT ON

DECLARE

  value NUMBER;

BEGIN

  value := DBMS_AUDIT_MGMT.GET_AUDIT_TRAIL_PROPERTY_VALUE(

    audit_trail_type            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,

    audit_trail_property        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE);

  CASE value

    WHEN DBMS_AUDIT_MGMT.AUDIT_TRAIL_QUEUED_WRITE

      THEN DBMS_OUTPUT.PUT_LINE(value||':QUEUED WRITE MODE');

    WHEN DBMS_AUDIT_MGMT.AUDIT_TRAIL_IMMEDIATE_WRITE

      THEN DBMS_OUTPUT.PUT_LINE(value||':IMMEDIATE WRITE MODE');

    ELSE DBMS_OUTPUT.PUT_LINE('UNKNOWN MODE');

  END CASE;

END;

/

Change the write mode:

Change to queued write mode.

BEGIN

  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(

    audit_trail_type            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,

    audit_trail_property        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE,

    audit_trail_property_value  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_QUEUED_WRITE);

END;

/

Change to immediate write mode.

BEGIN

  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(

    audit_trail_type            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,

    audit_trail_property        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE,

    audit_trail_property_value  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_IMMEDIATE_WRITE);

END;

/

SELECT PARAMETER_VALUE FROM DBA_AUDIT_MGMT_CONFIG_PARAMS WHERE PARAMETER_NAME='AUDIT WRITE MODE';

Purging  Unified audit data : 

Purging Unified audit trail can be achieved  either using last_archive_timestamp  or through  scheduled job 

Set Purge  Archive  timestamp   and Manually  Purge Archive based on  Last_archive_timestamp 

Begin 

  dbms_audit_mgmt.set_last_archive_timestamp (

 DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED , 

trunc (SYSTIMESTAMP) -10 ) ; 

end ;

/

Begin 

  dbms_audit_mgmt.clean_audit_trail  ( 

  audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED , 

  use_last_archive_timestamp => true   ) ; 

end ;

/

BEGIN

  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(

   AUDIT_TRAIL_TYPE           =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,

   USE_LAST_ARCH_TIMESTAMP    =>  TRUE,

   CONTAINER                 =>  DBMS_AUDIT_MGMT.CONTAINER_CURRENT );

END;

/ 

Or 

BEGIN

DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,last_archive_time => TO_TIMESTAMP('10-SEP-0714:10:10.0','DD-MON-RRHH24:MI:SS.FF'));

END;

/

BEGIN

DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(

   audit_trail_type         =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,

   use_last_arch_timestamp  =>  TRUE);

END;

/

Purging audit data by Adding Purge schedule 

BEGIN

DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(

audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,

audit_trail_purge_interval => 24 /* hours */,

audit_trail_purge_name => 'CLEANUP_AUDIT_TRAIL_UNIFIED',

use_last_arch_timestamp => TRUE);

END;

/

BEGIN

DBMS_SCHEDULER.create_job (

job_name => 'audit_last_archive_time',

job_type => 'PLSQL_BLOCK',

job_action => 'BEGIN

DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, TRUNC(SYSTIMESTAMP)-10);

END;',

start_date => SYSTIMESTAMP,

repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;',

end_date => NULL,

enabled => TRUE,

comments => 'Automatically set audit last archive time.');

END;

/

OR 

begin 

DBMS_SCHEDULER.CREATE_JOB ( 

JOB_NAME => 'UNIFIED_AUDIT_TRAIL_PURGE' ,

JOB_TYPE  => 'PLSQL_BLOCK' , 

job_action =>  'begin     dbms_audit_mgmt.set_last_archive_timestamp ( DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED ,  trunc (SYSTIMESTAMP) -10 ) ; end ;

  Begin   dbms_audit_mgmt.clean_audit_trail  (  audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED ,  use_last_archive_timestamp => true   ) ; 

end ;  ',

enabled => true  ,

end_date => NULL,

reepat_interval  => 'FREQ=DAILY ; BYHOUR=1 ; BYTIME=0 ; BYSECOND=0 ; '  ,

START_DATE =>  SYSTIMESTAMP ,

comment  => 'UNIFIED AUDIT PURGE'   ) ; 

END ; 

/ 

Taking export backup of   Unified audit data 

from 19c onwards . 

expdp system 

full=y 

directory=aud_dp_dir 

logfile=audexp_log.log 

dumpfile=audexp_dump.dmp 

version=18.02.00.02.00 

INCLUDE=AUDIT_TRAILS

https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/administering-the-audit-trail.html#GUID-8140CCBF-77EE-4F86-A055-B9F9AB8B4573

References : 

How To Prevent The Unified Audit Trail From Being Created in SYSAUX, And Change Its Default Partitioning Behavior (Doc ID 2548804.1)

12.1 upgrade to 19c: DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION does not move AUD$UNIFIED table to a different tablespace (Doc ID 2816457.1)

How To Purge The UNIFIED AUDIT TRAIL (Doc ID 1582627.1)

How To Purge The UNIFIED AUDIT TRAIL (Doc ID 1582627.1)

How to confirm/change write mode in unified auditing (Doc ID 2520310.1)

Recover Oracle Standby / Dataguard Database From Service

 

Though there are many documents  online for recovering standby using service , i thought of documenting mine  to have handy steps for myself and  help others too 

Before  we take decision  to recover from service . try  below troubleshooting steps  first 

-- take new archive from primary .  This is ensure  current log on standby is not corrupted 

-- restart standby  database 
-- take new standby controlfile from primary 
--  try to start  mrp  with no real time apply 
--  try to start  mrp   in mount 

--  try to start  mrp without parallel 

--  try to start  mrp on another rac node 

 

If there are any nologging changes we can  use  below doc to fix  nologging changes . 

Rolling a Standby Forward using an RMAN Incremental Backup To Fix The Nologging Changes (Doc ID 958181.1)

Recovery  Steps : 

1. Get the latest SCN from standby:

select to_char(current_scn) from v$database;

 

CURRENT_SCN#

------------------

644203931

2. Stop Managed Recovery and Redo Transport  via Data Guard Broker 

DGMGRL> edit database <primary> set state=TRANSPORT-OFF;

Succeeded.

DGMGRL> edit database <standby> set state=APPLY-OFF;

Succeeded.

3. Take  backup of location  of files 

select name from v$datafile  

union all 

select member from v$tempfile 

union all 

select memeber from   v$logfile ;

4.  Restore Standby Controlfile 

$ srvctl stop database -d <standbydb> -o immediate

$ rman target / nocatalog 

RMAN> startup nomount

RMAN> restore standby controlfile from service <tns alias for primary database>;

RMAN> alter database mount;

RMAN> catalog start with '<DATA DISKGROUP>/<standby db_unique_name/';

RMAN> catalog start with '<RECO DISKGROUP>/<standby db_unique_name>/';

5. Restore Any Missing Files  that are added  recently after gap 

RMAN> select file# from v$datafile where creation_change# >= 644203931;   -- from step 1 .  ( this is  run on primary ) 

Below are  on standby 

RMAN> run {

allocate channel c1 type disk;

allocate channel c2 type disk;

allocate channel c3 type disk;

allocate channel c4 type disk;

allocate channel c5 type disk;

allocate channel c6 type disk;

allocate channel c7 type disk;

allocate channel c8 type disk;

set newname for database to NEW;

restore datafile <comma separate list of files> from service <tns alias for primary database> section size <section size>;

}

RMAN> switch database to copy;

6. Clear Online Redo Logs and Standby Redo Logs  .

Execute the following query in SQL*PLUS on the standby to create new logfiles.

SQL> begin

for log_cur in ( select group# group_no from v$log )

loop

execute immediate 'alter database clear logfile group '||log_cur.group_no;

end loop;

end;

/

SQL> begin

for log_cur in ( select group# group_no from v$standby_log )

loop

execute immediate 'alter database clear logfile group '||log_cur.group_no;

end loop;

end;

7.  Restart All Instances to Mount and Re-Enable Redo Transport 

$ srvctl stop database -db <dbname> -o immediate

$ srvctl start database -db <dbname> -o mount

DGMGRL> edit database <primary> set state=TRANSPORT-ON;

8 Run Recover From Service 

Dont use noredo    or  "recover standby database"  because  in some cases the RECOVER DATABASE NOREDO command does not apply the incremental changes to the standby database and it just completes in few seconds.

The NOREDO clause specifies that the archived redo log files must not be applied during recovery. Its mainly use  when Olr are lost on standby . 

The RECOVER STANDBY DATABASE command restarts the standby instance, refreshes the control file from the primary database, and automatically renames data files, temp files, and online logs. It restores new data files that were added to the primary database and recovers the standby database up to the current time.

When you use the RECOVER STANDBY DATABASE command to refresh a standby database, you specify either a FROM SERVICE clause or a NOREDO clause. The FROM SERVICE clause specifies the name of a primary service. The NOREDO clause specifies that backups should be used for the refresh, which allows a standby to be rolled forward to a specific time or SCN

.

If you wat to  prefer to use  "RECOVER STANDBY DATABASE"    method its documented in   2431311.1 .  However  we might face  Implicit Crosschecking and Cataloging issue  using  "RECOVER STANDBY DATABASE"    and  we have to set     db_recovery_file_dest=''  as documented in  Doc  1489027.1

$ rman target sys/<password>  <- It is necessary to connect with the password

RMAN > run {

allocate channel c1 type disk connect '/@<standby instance 1 SID_NAME>';

allocate channel c2 type disk connect '/@<standby instance 1 SID_NAME>';

allocate channel c3 type disk connect '/@<standby instance 1 SID_NAME>';

allocate channel c4 type disk connect '/@<standby instance 1 SID_NAME>';

allocate channel c5 type disk connect '/@<standby instance 2 SID_NAME>';

allocate channel c6 type disk connect '/@<standby instance 2 SID_NAME>';

allocate channel c7 type disk connect '/@<standby instance 2 SID_NAME>';

allocate channel c8 type disk connect '/@<standby instance 2 SID_NAME>';

recover database from service '<primary unique name>' section size <section size> using compressed backupset;  

}

select INST_ID,SID,SERIAL#,OPNAME, (sofar/totalwork)*100 as "%complete", ELAPSED_SECONDS, TIME_REMAINING

from gv$session_longops

where sofar<>totalwork

and totalwork<>0

and SID||SERIAL# in (select SID||SERIAL# from v$session);

From the primary:

SQL> alter system archive log current;

Then from SQL*PLUS on the standby issue the command below.  The UNTIL CONSISTENT clause cannot be used in RMAN.

SQL> recover automatic standby database until consistent;

select inst_id,GROUP#,TYPE,MEMBER from gv$logfile    -->  Verify    log mapped to right path 

Verify standby_file_management  on standby 

9.  Re-Enable Flashback Database

SQL> alter database flashback on;

10.  Restart the Standby and Managed Recovery

$ srvctl stop database -db <dbname> -o immediate

$ srvctl start database -db <dbname> -o 'read only'

DGMGRL> edit database <standby> set state=APPLY-ON;

 

Reference : 

How to Roll Forward a Standby Database Using Recover Database From Service (Doc ID 2850185.1)

Roll Forward a Physical Standby Database Using RMAN Incremental Backups 'Recover Database Noredo' Does Nothing (Doc ID 841765.1)

Rolling a Standby Forward using an RMAN Incremental Backup To Fix The Nologging Changes (Doc ID 958181.1)

Roll Forward Physical Standby Using RMAN Incremental Backup in Single Command (Doc ID 2431311.1)