RAC database commands

Database level

srvctl stop database -d orcl
srvctl start database -d orcl

PDB level

alter pluggable database PDB1 open  instances=all;
alter pluggable database PDB1 close  instances=all;

SELECT * FROM gv$pdbs;
SELECT * FROM dba_pdb_saved_states;

Oracle 12.2 and Transparent Data Encryption

TDE Setup

Add these lines to sqlnet.ora

ENCRYPTION_WALLET_LOCATION=
(SOURCE=(METHOD=FILE)(METHOD_DATA=
(DIRECTORY=E:\encrypted_wallet\)))

Bounce Database

shutdown immediate;
startup;

Setup auto-login Wallet

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE 'E:\encrypted_wallet\' IDENTIFIED BY "wve6Wq54IUhg39XY";
ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE 'E:\encrypted_wallet\' IDENTIFIED BY "wve6Wq54IUhg39XY";

Checkout Setting

select * FROM V_$ENCRYPTION_WALLET;
select * FROM V_$ENCRYPTION_KEYS;
SELECT * FROM v$rman_encryption_algorithms ORDER BY algorithm_name;

Create the master key in all containers

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "wve6Wq54IUhg39XY" CONTAINER=ALL;
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "wve6Wq54IUhg39XY" WITH BACKUP CONTAINER=ALL;

Migrate existing tablespaces to encrypted tablespace

Ensure compatibility is 12.2.0

alter system set compatible = '12.2.0' scope=spfile;
show parameter compatible;

Do this for each tablespace. After all tablespaces are migrated to the encrypted one, we need to manual delete the unencrypted datafiles. Oracle supposed to remove them, but they are not doing a good job.

SELECT
    'ALTER TABLESPACE '
    || tablespace_name
    || ' ENCRYPTION ONLINE USING ''AES256'' ENCRYPT;' v_sql
FROM
    dba_tablespaces
WHERE
    encrypted = 'NO'
    AND contents IN (
        'PERMANENT'
    )
    AND tablespace_name NOT IN (
        'SYSTEM',
        'SYSAUX'
    )
ORDER BY
    1;

RMAN Encrypted Backup

RMAN Configuration

CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE ENCRYPTION FOR DATABASE ON;
CONFIGURE ENCRYPTION ALGORITHM 'AES256';

RCV file

show all;
set encryption on;
crosscheck backupset;
crosscheck archivelog all;
delete noprompt expired backupset;
delete noprompt expired archivelog all;
delete noprompt obsolete;
run {  
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
}
delete noprompt expired backupset;
delete noprompt expired archivelog all;
delete noprompt obsolete;
list backupset;
exit

Datapump Encrypted Backup

Add these options to expdp

ENCRYPTION=ALL
ENCRYPTION_MODE=TRANSPARENT 
ENCRYPTION_ALGORITHM=AES256

Oracle Database Security Assessment Tool (DBSAT)

The Oracle Database Security Assessment Tool is a stand-alone command line tool that accelerates the assessment and regulatory compliance process by collecting relevant types of configuration information from the database and evaluating the current security state to provide recommendations on how to mitigate the identified risks.

To Run

SET ZIP_CMD=%ORACLE_HOME%\bin\zip.exe 
SET UNZIP_CMD=%ORACLE_HOME%\bin\unzip.exe

dbsat collect chiup@paris12c paris12c

dbsat report paris12c