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

Oracle RMAN Cold Backup

rman target /

CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
configure controlfile autobackup format for device type disk to 'F:\ora_backup\%F';
configure channel device type disk format 'F:\ora_backup\%U' maxpiecesize 8 G;
run 
{
  shutdown immediate
  startup mount
  backup database;
  alter database open;
}

Check Flashback Recovery Area Size

show parameter db_recovery

NAME                       TYPE        VALUE                  
-------------------------- ----------- ---------------------- 
db_recovery_file_dest      string      F:\flash_recovery_area 
db_recovery_file_dest_size big integer 900G     

show parameter db_flashback

NAME                          TYPE    VALUE 
----------------------------- ------- ----- 
db_flashback_retention_target integer 720 

select space_used/(1024*1024*1024) gb_used, space_limit/(1024*1024*1024) gb_limit from v$recovery_file_dest;

   GB_USED   GB_LIMIT
---------- ----------
427.937144        900

select * from v$flash_recovery_area_usage;

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                             0                         0               0          0
REDO LOG                                 0                         0               0          0
ARCHIVED LOG                           .03                       .01              13          0
BACKUP PIECE                         46.85                         0              13          0
IMAGE COPY                               0                         0               0          0
FLASHBACK LOG                          .68                       .65             122          0
FOREIGN ARCHIVED LOG                     0                         0               0          0
AUXILIARY DATAFILE COPY                  0                         0               0          0

8 rows selected. 

Set FRA limit to be 90% of the drive size

alter system set db_recovery_file_dest_size=900G SCOPE=BOTH;

Proxy User and Connect Through

Assuming we have a user called CHIUP and we want to connect to LIB1 without knowing the password, we could do the following.

P.S. This method works for Forms 12c but not Forms 10g.

As DBA

ALTER USER LIB1 GRANT CONNECT THROUGH CHIUP;

Connect via SQL*Plus

CONN CHIUP[LIB1]/xxxxxxx

Connect via SQL Developer
Hit "Advanced..." button in the connection setup window


Revoke Access
As DBA

ALTER USER LIB1 REVOKE CONNECT THROUGH CHIUP;

Reset Password By Values in 12c

select u.username,
'alter user '||u.username||' identified by values '''||s.spare4||''';' cmd
from dba_users u
join sys.user$ s
on u.user_id = s.user#
where u.username = upper('user1');

Flashback Data Archive

Setup FDA

CREATE TABLESPACE fda_data DATAFILE SIZE 1G AUTOEXTEND ON NEXT 1G;
CREATE flashback archive default FDA_1YEAR tablespace fda_data retention 1 year;
exec dbms_flashback_archive.set_context_level(level=> 'ALL');

SELECT
flashback_archive_name,
flashback_archive#,
tablespace_name,
quota_in_mb
FROM
dba_flashback_archive_ts
ORDER BY
flashback_archive_name;

SELECT
owner_name,
flashback_archive_name,
flashback_archive#,
retention_in_days,
TO_CHAR(create_time,'DD-MON-YYYY HH24:MI:SS') AS create_time,
TO_CHAR(last_purge_time,'DD-MON-YYYY HH24:MI:SS') AS last_purge_time,
status
FROM
dba_flashback_archive
ORDER BY
owner_name,
flashback_archive_name;

Add Tables to FDA

BEGIN
DBMS_FLASHBACK_ARCHIVE.register_application(
application_name       => 'PARIS_APP',
flashback_archive_name => 'FDA_1YEAR');
END;
/
BEGIN
DBMS_FLASHBACK_ARCHIVE.add_table_to_application (
application_name => 'PARIS_APP',
table_name       => 'APP_DIRECTORIES_AND_PARMS',
schema_name      => 'OPTIT');
END;
/

SELECT
a.appname,
b.faname
FROM
sys_fba_app a
JOIN sys_fba_fa b ON a.fa# = b.fa#;

SELECT
a.appname,
c.owner AS table_owner,
c.object_name AS table_name
FROM
sys_fba_app a
JOIN sys_fba_app_tables b ON a.app# = b.app#
JOIN dba_objects c ON b.obj# = c.object_id
ORDER BY
1,
2,
3;

Enable FDA by Application

BEGIN
DBMS_FLASHBACK_ARCHIVE.enable_application(
application_name => 'PARIS_APP');
END;
/

SELECT
owner_name,
table_name,
flashback_archive_name,
archive_table_name,
status
FROM
dba_flashback_archive_tables
ORDER BY
owner_name,
table_name;

Purge FDA

alter flashback archive FDA_1YEAR purge ALL;

Disable FDA by Application

BEGIN
DBMS_FLASHBACK_ARCHIVE.disable_application(
application_name => 'PARIS_APP');
END;
/

Grant FDA read access to a user

grant EXECUTE ON DBMS_FLASHBACK_ARCHIVE to USER1;
grant FLASHBACK any TABLE to USER1;

Show all Changes

SELECT
    adap.versions_startscn,
    adap.versions_starttime,
    adap.versions_endscn,
    adap.versions_endtime,
    adap.versions_xid,
    adap.versions_operation,
    CASE
            WHEN adap.versions_xid IS NOT NULL THEN dbms_flashback_archive.get_sys_context(adap.versions_xid,'USERENV','SESSION_USER')
        END
    session_user,
    CASE
            WHEN adap.versions_xid IS NOT NULL THEN dbms_flashback_archive.get_sys_context(adap.versions_xid,'USERENV','HOST')
        END
    host,
    CASE
            WHEN adap.versions_xid IS NOT NULL THEN dbms_flashback_archive.get_sys_context(adap.versions_xid,'USERENV','MODULE')
        END
    module,
    CASE
            WHEN adap.versions_xid IS NOT NULL THEN DBMS_FLASHBACK_ARCHIVE.get_sys_context(adap.versions_xid,'USERENV','CLIENT_IDENTIFIER')
        END
            client_identifier,
    adap.*
FROM
    optit.app_directories_and_parms VERSIONS BETWEEN TIMESTAMP minvalue AND maxvalue adap
WHERE
    adap.app_database = 'PARIS'
    AND   adap.app_alias = 'BIPub'
    AND   adap.app_dir_parm_no = 4
ORDER BY
    adap.versions_startscn NULLS LAST;

Flashback Database

Enable Flashback Database

ALTER DATABASE FLASHBACK ON;
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=20160; #14 days or 20160 mins

Verify Flashback Database

SELECT FLASHBACK_ON FROM V$DATABASE; 

Before you do anything drastic

CREATE RESTORE POINT RP1 GUARANTEE FLASHBACK DATABASE;

Rollback to Restore Point

SHUTDOWN IMMEDIATE
STARTUP MOUNT EXCLUSIVE
FLASHBACK DATABASE TO RESTORE POINT RP1;
ALTER DATABASE OPEN RESETLOGS;

Rollback to Timestamp

SHUTDOWN IMMEDIATE
STARTUP MOUNT EXCLUSIVE
FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('2023-01-30 11:25:00','YYYY-MM-DD HH24:MI:SS');
ALTER DATABASE OPEN RESETLOGS;

Rollback to Restore Point - CDB/PDB

alter pluggable database PDB1 close immediate;
flashback pluggable database PDB1 to restore point RP1;
alter pluggable database PDB1 open resetlogs;

Remove Restore Point

DROP RESTORE POINT RP1;

How far back can I flashback

SELECT v.oldest_db_fb,
         EXTRACT (DAY FROM oldest_db_fb_interval) * 24
       + EXTRACT (HOUR FROM oldest_db_fb_interval) oldest_db_fb_hours
  FROM (SELECT CAST (
                   FROM_TZ (CAST (oldest_flashback_time AS TIMESTAMP),
                            DBTIMEZONE)
                       AT TIME ZONE 'US/Eastern'
                       AS DATE)
                   oldest_db_fb,
               ROUND ((SYSDATE - oldest_flashback_time) * 24, 1)
                   oldest_db_fb_hours,
                 SYSTIMESTAMP
               - FROM_TZ (CAST (oldest_flashback_time AS TIMESTAMP),
                          DBTIMEZONE)
                     AT TIME ZONE 'US/Eastern'
                   oldest_db_fb_interval
          FROM v$flashback_database_log) v;

List Restore Point

SELECT
    database_incarnation# AS incar,
    scn,
    name,
    time,
    storage_size,
    guarantee_flashback_database
FROM
    v$restore_point
ORDER BY
    4;

Enable HTTP/2 for Tomcat 9

It is easier than I thought.

First, ensure you are using APR/native connector. You can download tomcat native from here. Put the tcnative-1.dll in the tomcat lib directory.

Second, change protocol to org.apache.coyote.http11.Http11AprProtocol and add the UpgradeProtocol tag.

$CATALINA_HOME\conf\server.xml

<Connector port="8443"
  protocol="org.apache.coyote.http11.Http11AprProtocol"
  maxThreads="150" SSLEnabled="true">
    <UpgradeProtocol overheadDataThreshold="0" compression="on" className="org.apache.coyote.http2.Http2Protocol" />
    <SSLHostConfig honorCipherOrder="false">
        <Certificate certificateKeyFile="conf/ca.key"
          certificateFile="conf/ca.crt"
          type="RSA" />
    </SSLHostConfig>
</Connector>

Restart tomcat and we are done.

Check if HTTP/2 is enabled
https://tools.keycdn.com/http2-test

Enable Oracle 12c Unified Auditing - Pure Mode

Why we want Pure Mode?
The first is the audit trails are no longer written to their traditional pre-12c audit locations. Auditing is consolidated into the Unified Audit views and stored using Oracle SecureFiles. Oracle Secured Files use a proprietary format which means that Unified Audit logs cannot be viewed using editors such vi and may preclude or affect the use of third party logging solutions such as Splunk or HP ArcSight.

Operations done by SYS are also recorded.

Unified Auditing comes standard with Oracle Enterprise Edition; no additional license is required. It is installed by default, but not fully enabled by default.

In Command window

rename %ORACLE_HOME%/bin/orauniaud12.dll.dbl file %ORACLE_HOME%/bin/orauniaud12.dll

In sqlplus as SYSDBA

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

In Command window as Administrator

sc stop OracleService<sid>
sc start OracleService<sid>

Now pure mode unified auditing is enabled. Let's check.
In sqlplus as SYSDBA

SQL*Plus: Release 12.1.0.2.0 Production on Sat Dec 9 01:03:46 2017

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics,
Real Application Testing and Unified Auditing options

SQL> SELECT value FROM v$option WHERE parameter = 'Unified Auditing';

VALUE
----------------------------------------------------------------
TRUE

Check out the audit output

SELECT * FROM unified_audit_trail
ORDER BY event_timestamp DESC;

For performance reason, you may want to use queued-write method
In sqlplus as SYSDBA

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;
/

Check configuration

SELECT * FROM dba_audit_mgmt_config_params
order by audit_trail, parameter_name;

Check what is being auditing out of the box

SELECT * FROM audit_unified_policies
ORDER BY policy_name,
         audit_option;

SELECT * FROM AUDIT_UNIFIED_ENABLED_POLICIES;

Setup how many days of audit records we want to keep

BEGIN
DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type     => DBMS_AUDIT_MGMT.audit_trail_unified,
last_archive_time    => SYSTIMESTAMP-90
);
END;
/

Check archive setting

SELECT audit_trail,
last_archive_ts
FROM   dba_audit_mgmt_last_arch_ts;

We have to run this for the very first time

BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.audit_trail_all,
DEFAULT_CLEANUP_INTERVAL => 24 /*hours*/
);
END;
/

Let's purge manually

SELECT COUNT(*) FROM unified_audit_trail;
BEGIN
DBMS_AUDIT_MGMT.clean_audit_trail(
audit_trail_type        => DBMS_AUDIT_MGMT.audit_trail_unified,
use_last_arch_timestamp => TRUE);
END;
/
SELECT COUNT(*) FROM unified_audit_trail;

Let's automate this purging process by setting up scheduled job

DBMS_SCHEDULER.create_job (
job_name        => 'audit_last_archive_time',
job_type        => 'PLSQL_BLOCK',
job_action      => 'DECLARE
l_days NUMBER := 90;
BEGIN
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.audit_trail_unified, TRUNC(SYSTIMESTAMP)-l_days);
  DBMS_AUDIT_MGMT.clean_audit_trail(
    audit_trail_type        => DBMS_AUDIT_MGMT.audit_trail_unified,
    use_last_arch_timestamp => TRUE);
END;',
start_date      => SYSTIMESTAMP,
repeat_interval => 'freq=daily; byhour=1; byminute=0; bysecond=0;',
end_date        => NULL,
enabled         => TRUE,
comments        => 'Automatically set audit last archive time.');
END;
/