Oracle Maximum Availability Architecture (MAA) Version
19c
General Information
Library Note
Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Purpose
Oracle's definition of of Maximum Availability Architecture (MAA) isn't flawed but it is incomplete. This document is going to first define what MAA encompasses then define the elements required to achieve it.
Where there are multiple ways to meet the MAA objective this document will favor the one included in Oracle's EE license, the one that is most "cost effective" as opposed to "most expensive", the one that is most secure,
and where we think if not obvious we will explain our choice otherwise we trust our readers will (a) read the docs and if that is does not provide sufficient clarity, (b) email or phone us.
To accomplish MAA the following must to into account the three S's.
Stability
Maximum availability requires that a system must support transparent switchover and failover to support routine maintenance activities, infrastructure and software failures, natural disasters, upgrades and migrations
Scalability
Maximum availability requires that a system be able to scale to handle not just nominal loads but season and reasonable changes in the number of connections, the number of simultaneous transactions, and the volume of I/O
Security
It is impossible to provide maximum availability in a system subject a Denial of Service attack
It is impossible to provide maximum availability in a system that is under attack
Each of the below will address one or more of these objectives.
Network Configuration
Physical Infrastructure
???
This section needs to discuss Lights Out Cards, TCP/IP to application servers, TCP/IP for Data Guard instances, Backup Networks, Replication Networks, UDP networks for RAC.
Storage Configuration
Physical Infrastructure
Storage should consist of no fewer than entirely two separate systems supported by no fewer than two entirely separate pieces of physical infrastructure.
For example, a file system on each server hosting an Oracle Home and a file system or array with raw devices on a SAN or NAS.
Later in this document we will refer to the server file system as the "SFS" and the array storage system as the array raw storage system "ARSS." Clearly there was a need to put the word raw in there to protect the innocent
Logical Infrastructure
Both the SFS and ARSS must follow Oracle's near legendary SAME philosophy that translates to "Stripe And Mirror Everything." For the SFS striping and mirror must be performed at the hardware level.
For the ARSS striping must be defined at the hardware level but mirror can be implemented using hardware or Oracle ASM's normal or high redundancy.
Operating System Configuration
Server Name
Do NOT create a server name that defines the location, purpose or intended usage of the server. An attacker that successfully bypasses the firewall will prioritize a server named NTP, DNS, or SQLDB before one named HONEYPOT1.
A server named EBSRAC1 is essentially an invitation to be compromised.
Installation Users
Two separate users are created. The first for the owner of the Oracle Database, the second for the owner of the Oracle Client software.
The database owner should be created based on the Oracle online documentation with associated groups.
The client installation MUST be created using a different name, for example if the database is owned by oracle then the client software might be owned by oracli. The Linux client owner MUST NOT belong to any privileged group created for and assigned to the Oracle Database owner.
Non-RAC Startup Parameters
???
-- NUMA ???
ALTER SYSTEM SET control_file_record_keep_time=32 CONTAINER=CURRENT SID='*' SCOPE=BOTH
COMMENT = 'Set to 31 from <previous_value> by <dba_intials> on <DD-MON-YYYY>';
ALTER SYSTEM SET filesystemio_options=SETALL CONTAINER=ALL SID='*' SCOPE=SPFILE
COMMENT = 'Set to SETALL from <previous_value> by <dba_intials> on <DD-MON-YYYY>';
ALTER SYSTEM SET global_names=TRUE CONTAINER=CURRENT SID='*' SCOPE=BOTH
COMMENT = 'Set to TRUE from FALSE by <dba_intials> on <DD-MON-YYYY>';
ALTER SYSTEM SET remote_login_passwordfile=NONE CONTAINER=ALL SID='*' SCOPE=SPFILE
COMMENT = 'Set to NONE from <previous_value> by <dba_intials> on <DD-MON-YYYY>';
ALTER SYSTEM SET sec_max_failed_login_attempts=1 CONTAINER=ALL SID='*' SCOPE=SPFILE;
COMMENT = 'Set to 1 from <previous_value> by <dba_intials> on <DD-MON-YYYY>';
ALTER SYSTEM SET sec_protocol_error_trace_action=log CONTAINER=CURRENT SID='*' SCOPE=BOTH
COMMENT = 'Set to LOG from <previous_value> by <dba_intials> on <DD-MON-YYYY>';
ALTER SYSTEM SET sec_protocol_error_further_action=3 CONTAINER=ALL SID='*' SCOPE=SPFILE
COMMENT = 'Set to 1 from <previous_value> by <dba_intials> on <DD-MON-YYYY>';
ALTER SYSTEM SET use_large_pages=TRUE CONTAINER=ALL SID='*' SCOPE=SPFILE
COMMENT = 'Set to TRUE from FALSE by <dba_intials> on <DD-MON-YYYY>';
ALTER SYSTEM SET resource_limit=TRUE CONTAINER=ALL SID='*' SCOPE=BOTH
COMMENT = 'Set to TRUE from FALSE by <dba_intials> on <DD-MON-YYYY>';
ALTER SYSTEM SET recyclebin=ON CONTAINER=ALL SID='*' SCOPE=BOTH
COMMENT = 'Set to ON from FALSE by <dba_intials> on <DD-MON-YYYY>';
ALTER SYSTEM SET "_enable_NUMA_support" = TRUE
COMMENT= 'NUMA Support Enabled 15-Mar-2017'
CONTAINER=ALL
SCOPE=SPFILE
SID='*';
ALTER SYSTEM SET "_px_NUMA_support_enabled" = TRUE
COMMENT= 'NUMA PX Support Enabled 15-Mar-2017'
CONTAINER=ALL
SCOPE=SPFILE
SID='*';
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT;
ALTER DATABASE OPEN;
Verification:
-- Step 1: Address any issues with deprecated parameters.
SELECT inst_id, name, value
FROM gv$parameter
WHERE isdeprecated = 'TRUE'
ORDER BY 2,1;
set linesize 141
col PNAME format a40
col PVAL format a20
col PDESC format a60
SELECT a.ksppinm PNAME, c.ksppstvl PVAL, a.ksppdesc PDESC, b.ksppstdf PDFLT
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx
AND a.indx = c.indx
AND LOWER(a.ksppinm) LIKE '%numa%'
ORDER BY 1;
# numactl --show
-- Step 2: Alter parameter values to be consistent with MAA
SELECT ???
RAC Startup Parameters
The parameters, and their values, are based on
optimizing the RAC environment for stability first and performance second.
ALTER SYSTEM SET parallel_force_local=TRUE CONTAINER=ALL SID='*' SCOPE=SPFILE
COMMENT = 'Set to TRUE from FALSE by <dba_intials> on <DD-MON-YYYY>';
CLUSTER_DATABASE_INTERCONNECTS
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT;
ALTER DATABASE OPEN;
Verification:
-- Step 1: Verify parameters on all nodes are identical unless the delta is required.
WITH a AS (SELECT name INST1_NAME, value INST1_VALUE
FROM gv$parameter WHERE inst_id = 1),
b AS (SELECT name INST2_NAME, value INST2_VALUE
FROM gv$parameter WHERE inst_id = 2)
SELECT *
FROM a, b
WHERE a.inst1_name = b.inst2_name
AND a.inst1_value <> b.inst2_value;
-- Step 2: Change the values of the following parameters if not properly set for RAC
SELECT inst_id, name, value
FROM gv$parameter
WHERE name IN ('cluster_database', 'cluster_database_interconnects', 'cluster_interconnects', 'parallel_degree_limit',
'parallel_degree_policy', 'parallel_force_local')
ORDER BY 2,1;
-- Step 1: Verify parameters on all nodes are identical unless the delta is required.
WITH a AS (SELECT name INST1_NAME, value INST1_VALUE
FROM gv$parameter WHERE inst_id = 1),
b AS (SELECT name INST2_NAME, value INST2_VALUE
FROM gv$parameter WHERE inst_id = 2)
SELECT *
FROM a, b
WHERE a.inst1_name = b.inst2_name
AND a.inst1_value <> b.inst2_value;
-- Step 2: Change the values of the following parameters if not properly set for RAC
SELECT inst_id, name, value
FROM gv$parameter
WHERE name IN ('recv_buf_size', 'RECV_BUF_SIZE', 'send_buf_size', 'SEND_BUF_SIZE')
ORDER BY 2,1;
Database Owner profile
Whether you choose to define the database owner's profile using .bash_profile or .bashrc is your choice. But be sure to define the following environment variables so that you can implement a Read Only Oracle Home (ROOH).
Client Owner profile
Whether you choose to define the database owner's profile using .bash_profile or .bashrc is your choice. But be sure to define the following environment variables
???
???
Oracle Home Configuration
ROOH
Let' get this out of the way right now. Create a Read Only Oracle Home
Kernel Parameters
If more than a single Oracle Database will be created using the Oracle Home be sure that the values of the following parameters are calculated per the Oracle installation documents rather than being defined with a default value
Create a Fast Recovery Area (FRA) as the logical location to store one of the control file copies, one member of each redo log group, archived redo logs, and backups and clones created with RMAN.
???
???
???
???
Oracle Listener Configuration
Administration Restrictions
Prevent remote modification of listener.ora using lsnrctl commands
ADMIN_RESTRICTIONS_[listener_name]=<ON | OFF>
Verification:
???
Checksum
???
???
Verification:
???
Compression
???
???
Verification:
???
Encryption
???
???
Verification:
???
Firewall
Enables strict ACL validation (whitelist-based approach) of all connections coming on this endpoint. If no ACLs are configured for a service, all connections are rejected for that service.
Use to rate limited database connection which should be implemented as a defense against a DDOS attack. There are three separate pieces of the rate limiting puzzle all of which are addressed in this section.
-- specifies a global rate across all LISTENER.ORA entries for the named listener.
connection_rate_<listener_name> = <number_of_connections_per_second>
-- specifies rate limit enabling or specific values for each port
for stand-alone:
rate_limit = <NO | YES>
for RAC:
rate_limit = <NO | YES>
-- connection rate is set to 10 per second for ports 1521 and 1522, no limit for 1523
-- connection rate is set to 5 per second for port 1521 and 10 per second for port 1522
LISTENER=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)(RATE_LIMIT=5))
(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1522)(RATE_LIMIT=10))
(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1523)))
Use the Container DataBase (CDB) architecture, new as of 12.1, with a single Pluggable DataBase (PDB) as the initial building block.
If Oracle's multitenant licenses has been purchased add additional PDBs after database creation and configuration steps have been completed.
Multiplex Control Files
Multiplex Control Files such that one is in the file system and one is in an ASM DiskGroup. If you wish to create a third control file put it in a second ASM DiskGroup.
-- add an additional control file
???
-- relocate a control file if it is not placed as recommended
???
Verification:
col name format a120
SELECT inst_id, name, status, is_recovery_dest_file
FROM gv$controlfile
ORDER BY 1, 2;
Redo Log Groups
There should be no fewer than 3 redo log groups ... each with 2 or more members.
Multiplex Redo Logs such that one is in the file system and one is in an ASM DiskGroup. If you wish to create a third member for each log file group put it in a second ASM DiskGroup.
-- if fewer than three redo log groups add an new group
ALTER DATABASE ADD LOGFILE GROUP <group_number>
('<log_member_path_and_name>') SIZE <integer> <K | M | G>;
-- if multiplexing is not as recommended because a second group member does not exist
???
-- if multiplexing is not as recommended because members are not on separate devices
???
Verification:
col member format a120
SELECT group#, status, type, member
FROM v$logfile
ORDER BY 1, 4;
Redo Archiving
Enable redo log archiving
???
Verification:
SELECT log_mode, checkpoint_change#, archivelog_change#
FROM v$database;
ALTER DATABASE SWITCH log file;
SELECT log_mode, checkpoint_change#, archivelog_change#
FROM v$database;
ALTER SYSTEM SET memory_max_target=0 CONTAINER=ALL SID='*' SCOPE=SPFILE
COMMENT = 'ASMM enabled by <dba_intials> on <DD-MON-YYYY>';
ALTER SYSTEM SET memory_target=0 SID='*' SCOPE=SPFILE;
COMMENT = 'ASMM enabled by <dba_intials> on <DD-MON-YYYY>';
ALTER SYSTEM SET pga_aggregate_target=<non-zero_value> SID='*' SCOPE=SPFILE;
COMMENT = 'ASMM enabled by <dba_intials> on <DD-MON-YYYY>';
ALTER SYSTEM SET sga_target=<non-zero_value> SID='*' SCOPE=SPFILE;
COMMENT = 'ASMM enabled by <dba_intials> on <DD-MON-YYYY>';
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT;
ALTER DATABASE OPEN;
Verification:
SELECT inst_id, name, value, update_comment
FROM gv$parameter
WHERE name IN ('memory_max_target', 'memory_target', 'pga_aggregate_target', 'sga_target')
ORDER BY 2,1;
memory_max_target must have a value of 0
memory_target must have a value of 0
pga_aggregate_target must be set to a non-zero value
sga_target must be set to a non-zero value
Audit Management
If auditing to an internal database table is performed the table should not be located in the SYSAUX tablespace.
SELECT file_name
FROM dba_data_files
ORDER BY 1;
CREATE TABLESPACE audit_tbsp
DATAFILE 'c:\u01\orabase19\oradata\orabasexix\audit01.dbf' SIZE 100M
LOGGING FORCE LOGGING ONLINE PERMANENT BLOCKSIZE 8K
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
SEGMENT SPACE MANAGEMENT AUTO;
SELECT file_name, file_id, autoextensible
FROM dba_data_files
ORDER BY 1;
ALTER DATABASE DATAFILE 20 AUTOEXTEND ON MAXSIZE UNLIMITED;
DECLARE
atjn VARCHAR2(20) := 'AUDIT_TRAIL_PURGE';
atje NUMBER := dbms_audit_mgmt.purge_job_enable;
atjt NUMBER := dbms_audit_mgmt.audit_trail_all;
iatt NUMBER := dbms_audit_mgmt.audit_trail_db_std;
ofma NUMBER := dbms_audit_mgmt.os_file_max_age;
ofms NUMBER := dbms_audit_mgmt.os_file_max_size;
ostt NUMBER := dbms_audit_mgmt.audit_trail_os;
uatt NUMBER := dbms_audit_mgmt.audit_trail_unified;
xmlt NUMBER := dbms_audit_mgmt.audit_trail_xml;
BEGIN
BEGIN
dbms_audit_mgmt.drop_purge_job(atjn);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
BEGIN
dbms_audit_mgmt.deinit_cleanup(atjt, dbms_audit_mgmt.container_all);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
Enable Block Change Tracking to support Incremental Level 1 backups
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE <$ORACLE_BASE/fast_recovery_area/<database_name>/bctf01.log';
Verification:
SELECT filename, status, bytes
FROM v$block_change_tracking;
SELECT *
FROM gv$sgastat
WHERE name LIKE '%CTWR%';
SELECT inst_id, sid, program, status
FROM gv$session
WHERE program LIKE '%CTWR%';
PL/SQL Warnings
Enable PL/SQL Warning
ALTER SYSTEM SET plsql_warnings='ENABLE:ALL' CONTAINER=ALL SID='*' SCOPE=BOTH
COMMENT = 'Enabled in all containers by <dba_initials> on <DD-MON-YYYY>';
Verification:
col update_comment format a60
SELECT inst_id, value, update_comment
FROM gv$parameter
WHERE name = 'plsql_warnings'
ORDER BY 1;
UNDO tablespace retention
The Oracle Database's default undo tablespace retention of 900 seconds is inadequate for utilizing Flashback Query, Flashback Table, Flashback Version, and DBMS_FLASHBACK.TRANSACTION_BACKOUT.
The value demonstrated below equates to 12 hour retention.
Be sure undo data files are autoextensible before altering the retention.
-- with a CDB be sure to alter all undo tablespaces not just the one for the CDB.
ALTER DATABASE DATAFILE <datafile_for_undo_tablespace_path_name> AUTOEXTEND ON
MAXSIZE <size | UNLIMITED>;
ALTER SYSTEM SET undo_retention=43200
COMMENT = 'Undo retention changed from <existing_value> to <new_value> by <dba_initials> on <DD-MON-YYYY>';
SELECT ct.con_id, ct.tablespace_name, ct.status, ct.logging, cdf.autoextensible
FROM cdb_tablespaces ct, cdb_data_files cdf
WHERE ct.con_id = cdf.con_id
AND ct.tablespace_name = cdf.tablespace_name
AND ct.contents = 'UNDO'
ORDER BY 1;
SELECT inst_id, con_id, value
FROM gv$parameter
WHERE name IN ('undo_management', 'undo_retention')
ORDER BY 1,2;
Optimize AWR settings
The Oracle Database AWR default takes a snapshot once each hour and retains it for a maximum of 7 days.
This is inadequate for troubleshooting issues that may arise as, by definition, it is impossible to compare a snapshot taken on Monday morning last week with the one taken on Monday morning of this week.
Our recommendation is to set retention to no fewer than 31 days and the snapshot interval to 15 or 20 minutes.
SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc DESCRIPTION
FROM x$ksppi x, x$ksppcv y
WHERE x.inst_id = userenv('Instance')
AND y.inst_id = userenv('Instance')
AND x.indx = y.indx
AND SUBSTR(x.ksppinm,1,1) = '_'
AND lower(x.ksppdesc) like '%awr%restrict%'
ORDER BY 1;
Connection Pooling
???
Sample application server JDBC thin connection: jdbc:oracle:thin:@//<host>:<port>/<service_name>[:POOLED]
jdbc:oracle:thin:@//localhost:1521/orcl:POOLED
DECLARE
cpn VARCHAR2(30) := 'SYS_DEFAULT_CONNECTION_POOL';
BEGIN
dbms_connection_pool.restore_defaults;
