Security and Compliance with Oracle Database 12c

Oracle Key Vault

Master Key in Vault – Transparent Data Encryption.

– Validate encryption keys

– Create encrypted tablespace

– Move data into encrypted tablespace

– Verify encryption

– Drop unencrypted tablespace without dropping database files

– Securely delete unencrypted database files

Audit Vault

– Separate instance

– Audit data sent to this instance

Database Firewall

– Only allow certain sql statements

– Can use unique so predicate values do not result in duplicate entries

– Can first capture and login data

– Can select whitelist policy (Pass,Logging Level Unique)

– Does not pickup local connections as these are not over the network

– New SQL Statements go to default rule and can be blocked.

– Default rule can also substitute the statement.

– Assigned Firewall Policy to secured target.

– Create dba user set

– Add exception to policy for dba user set

Database Firewall works at OSI Application (SQL*Net) layer.

Multiple Database Firewalls can connect to 1 Audit Server.

Privileged Account Management – proxy server bob->oracle

Database Vault

– Realms, transparently Block DBA privileges from accessing data

– Command Rules – grant with admin option – can filter

– Installed by default with 12c, just need to enable.

– DBA, Account Administrator, Security Administrator (Database Vault,Realms,Command Rules)

– Oracle 12c adds Mandatory Realm, even table owner cannot access table unless part of the realm.

– Oracle 12c adds privilege analysis, run db in training mode.

– Realm Audit Report shows attempted violations

– Need to enable OLS (Oracle Label Security) to use Database Vault

– Still need traditional object privileges access as well.

– Can specify that a user can only access a realm during business hours

– Can use PL/SQL procedure to apply restrictions

– User can be in multiple realms and all realms must allow access.

– PL/SQL function DVSYS.SQL_TEXT is parsed sql text without comments.

Dask Masking and Subsetting

– Change but keep checksum the same

– Keep primary/foreign keys in sync.

Oracle Internet Directory, Oracle Virtual Directory, Now Oracle Unified Directory consume user/group information from Active Directory, do not need to change AD schema. Roles for users done via groups in AD.

Oracle Real Application Testing and Database Replay.

Database Replay

Capture->Workload->Replay.

Capture with real load,timing,concurrency.

Replay honours commit order.

If mask when transfer to replay server then must also mask the capture files.

All external client requests captured in binary files.

Capture supported for 9i and 10g, replay was not back ported.

Capture files are platform independent.

Capture can be filtered by user,program,module,action,service,session id.

Filters can also be applied at replay time.

10.2.0.4 have patch for replay.

Both capture and replay side require Real Application Testing license.

Estimate capture file size 2*bytes from SQL*Net from AWR report or estimate from small capture window.

Run Workload Analyzer on captured workload to see how many database requests were not complete at the end of the capture window

Replay Workload, Oracle will tell you how many replay clients are needed.

During replay the ‘think’ time can be reduced.

Connections can be remapped e.g. for database links.

Query part of workload can be scaled up during replay for load testing.

11.2.0.2+ allow consolidated replay, workloads from different databases can be replayed together e.g. when testing a move to 12c multitenancy with pluggable databases Start times for each replay can be moved to allow capacity planning for colliding peak workloads.

Workload folding, run multiple parts of a single capture at the same time.

Analysis and Reporting allows checking for divergence – error,data,performance (ADDM,AWR,ASH).

SQL Performance Analyser (SPA) integrated with Resource Governor so can potentially run on production,integrated with tuning and diagnotics pack.

Oracle ODI and Goldengate

Goldegate vs Advanced Replication/Streams

Streams is Oracle only.

Active Data Guard is readonly, Golden Gate can write.

Golden gate links into Log Miner API (11.2.0.3+ specific patch) now and multi threaded (11.2.0.4).

Classic Log Capture still needed for remote databases and integration with Active Data Guard.

Oracle DR still Data Guard.

Goldengate license includes Active Data Guard.

Goldengate linked to Java Message Bus.

Golden gate allows subsetting at schema,table,column,row.

Golden gate low impact, knowledge module for integration.

Goldengate apply to staging area, or JMS queue to ETL tool (chargeable option) or send to flat files for consumption by Etl Tools (chargeable option).

Conflict resolution out of the box. Can also consume from Java Message Bus.

Script check for setup of Goldengate – database check and health check for integrated components.

Oracle Goldengate Coherence – real time.

Goldengate supports off box capture – do not need to install on vendor host.

Trail files used to store changes, universal Trail Format, no endian issues.

Can batch operations, can call to the database procedures as well. Simple transformation, calculations, no aggregations.

Non-oracle databases, reads transaction log.

Capture->Trail file ->Pump (transaction not complete files)->Trail file->Delivery.

Replication Hub possible.

Pump process checkpoint in source/target trail files.

Manager process will restart pump process if network going down.

Rman knows about goldengate now, qill not discard archived logs.

Bounded recovery – can checkpoint partial long running transactions.

RAC – use DBFS for trail files, Goldengate integrated into cluster software – grid control.

Trail files, checkpoint and bounded recovery files.

Can replay trail files from archive logs or source trail files.

If cannot install on source machine use oracle stub database on downstream machine.

Integrated Extract only 1 target can send to downstream machine directly, rest have to be via archived logs.

GoldenGate 12c adds integrated delivery and co-ordinated delivery for non-oracle databases (co-ordinator thread).

Streams can be migrated to Goldengate.

12c added capture for Informix.

Integrated components must be used for Oracle 12c multi-tenant databases.

SQL Server configure DSN,username,password.

Parameter file per Goldengate process.

Capture can be started from SCN or date/time or BEGIN NOW.

Audit tables – ignore deletes, convert all transactions to inserts.

Install Goldengate, start capture,backup.restore, start apply from specific SCN.

Can added metadata via GETENV function and convert all changes to inserts only for audit.

Goldengate Veridata compare source/target.

Veridata 12.1.3 – can fix as well, use literal primary key+hash of non-primary key columns.

October 16 OTN Developer Day – 12c InMemory Database Highlights

Mike Appleyard presentation.

Can be used to change from batch processing to more realtime processing.

In memory is supported with multi tenant.

12c option goals

– 100x Faster Queries – Real-time Analytics

– Faster mixed workload oltp

– Transparent – no application changes

Transactions run faster on row format, analytics run faster on column format.

Both row and column format at the same time and transactional consistent.

2x to 20x compression for in-memory

In memory columnar format can be enabled at table or partition level and per column level by excluding columns.

Loading into the in memory area is first fit and once full there is no aging/LRU algorithm.

In memory enabled tables/partitions have priorties – critical is loaded before accessed.

Complex OLTP slowed by Analyic Indexes – potentially can remove indexes used only for analytical queries depends on your workload – less io/storage needed.

In-memory is supported with RAC.- 2 modes, either duplicate data on each node (engineered systems only) or partitional across nodes with multi-instance parallel query.

In-memory is supported with Dataguard but not Active Data Guard.

Flashback query/flashback transaction?

Instance Parameters

–  inmemory_query enabled/disable

– inmemory_clause_default

– inmemory_force

Use v$im_segments to monitor in-memory.

In-memory is a licensable option.

5 types of compression of in-memory, can be configured with alter table on a per partition level.

Currently release in-memory does query/bloom filtering but aggregation is done in the pga.

In-memory storage indexes – built on the fly in-memory segments which contain min/max values per compression units.

This are not the same as storage indexes for exadata which are per 1MB chunk.

Optimizer hint can be used to turn on/off im-memory.

Bloom filter – filters on multiple predicates in one pass rather than hash join on 1 predicate and rescanning the same data 4 times to apply each predicate.

RAC leaf nodes – no ORACLE_HOME or voting disk just compute nodes.thin install.- no locking LMS/LMON. readonly but transactional consistent in 11gr2.

September 17 Oracle 12c event at Oracle uk

This time a specific Oracle 12c database event at Oracle uk.

This included varous upgrade scenarios including the fact that upgrades are done in upgrade mode which is similar to mount mode where the datafiles are NOT touched, only system catalogs.

This also covered using data guard to reduce downtime during upgrades and using Goldengate which can even replicate between very old versions and the later versions to reduce downtime during upgrades.

August 7 eDBA Oracle 12c 12.2.0.4 in memory event.

Interesting event with eDBA on August 7 covering Oracle 12c 12.2.0.4 which adds in-memory features.

This Oracle in-memory offering is columar and NOT persisted on disk, instead a new memory area is used to store the in-memory version of data.