Dontcheff

What is Preview Versions for Autonomous Database

In Autonomous, Cloud, DBA, Init.ora, New features, Oracle database on October 18, 2019 at 12:14

A first preview is not exactly a pleasant experience for producers, directors and actors. In the IT world, it is exactly the opposite: DBAs can use the new version of the database before it becomes mainstream.

The “Preview Versions for Autonomous Database” has been available since mid-October 2019. The current preview version of Autonomous Database is Oracle Database 19c. More precisely, the version is 19.4.0.

Databases provisioned or cloned with a preview version display the end date of the preview period at the top of the Autonomous Database details page in the Oracle Cloud Infrastructure Console.

Note that the preview for my database below will end on December 2, 2019. All resources created with the preview will be terminated at the end of the preview period.

The basis details of preview versions for Autonomous Database can be found in the Oracle documentation.

Connecting to the preview database is as to a standard ADB:

Here are some of the default settings in Oracle Database Preview Version 19.4.0 for Autonomous Database:

Real-Time Statistics: Enabled by default
– Oracle automatically gathers online statistics during conventional DML operations
– By gathering some statistics automatically during DML operations, the database augments the statistics gathered by DBMS_STATS
– Fresh statistics enable the optimizer to produce more optimal plans
– EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; forces the database to write optimizer statistics to the data dictionary
– NO_GATHER_OPTIMIZER_STATISTICS prevents the collection of real-time statistics

High-Frequency Automatic Optimizer Statistics Collection: Enabled by default
– High-Frequency Automatic Optimizer Statistics Collection complements the standard automatic statistics collection job
– By default, the high-frequency statistics collection occurs every 15 minutes 
– Turn on/off: EXEC DBMS_STATS.SET_GLOBAL_PREFS(‘AUTO_TASK_STATUS’,’ON’);
– Change the default period: EXEC DBMS_STATS.SET_GLOBAL_PREFS(‘AUTO_TASK_INTERVAL’,’300’);
– Monitor: DBA_AUTO_STAT_EXECUTIONS
– The high-frequency automatic statistics task will not start during the maintenance window

SQL Quarantine: Disabled and not supported in Autonomous Database
– Check ORA-56955: quarantined plan used for details on SQL Quarantine

High-Frequency SQL Plan Management Evolve Advisor Task: Enabled by default
– High-Frequency Automatic Optimizer Statistics Collection complements the standard automatic statistics collection job
– By default, the high-frequency statistics collection occurs every 15 minutes 
– Turn on/off: EXEC DBMS_STATS.SET_GLOBAL_PREFS(‘AUTO_TASK_STATUS’,’ON’);
– Change the default period: EXEC DBMS_STATS.SET_GLOBAL_PREFS(‘AUTO_TASK_INTERVAL’,’300’);
– Monitor: DBA_AUTO_STAT_EXECUTIONS
– The high-frequency automatic statistics task will not start during the maintenance window

Automatic Indexing: Disabled by default
– Check Automatic Indexing in 19c for more details on Automatic Indexing

New 19c spfile parameters
– All 6 new init.ora parameters in 19c are set to their default values (click on the links below to see the values):

ADG_REDIRECT_DML
DATA_GUARD_MAX_IO_TIME
DATA_GUARD_MAX_LONGIO_TIME
ENABLE_IMC_WITH_MIRA
LOB_SIGNATURE_ENABLE
MAX_DATAPUMP_PARALLEL_PER_JOB

Preview is not free of charge. Preview provides you test/development capacity and capabilities so it is charged identically to production environments. Also, preview is not available for free service instances.

Preview instances are fully supported but should not be used for production purposes.

At the end, you may want to check out the new static data dictionary views in 19c:

• ALL_TRIGGERS_AE, DBA_TRIGGERS_AE, and USER_TRIGGERS_AE
• DBA_AUTO_INDEX_CONFIG
• DBA_AUTO_STAT_EXECUTIONS
• DBA_PDB_SNAPSHOTFILE
• DBA_RAT_CAPTURE_SCHEMA_INFO
• DBA_REGISTRY_BACKPORTS
• DBA_SQL_QUARANTINE

and also the new dynamic performance views in 19c:

• GV$AQ_PARTITION_STATS and V$AQ_PARTITION_STATS
• GV$ASM_ACFSAUTORESIZE and V$ASM_ACFSAUTORESIZE
• GV$ASM_DBCLONE_INFO and V$ASM_DBCLONE_INFO
• GV$MEMOPTIMIZE_WRITE_AREA and V$MEMOPTIMIZE_WRITE_AREA
• GV$SQL_TESTCASES and V$SQL_TESTCASES

The Power of Autonomous Database Security

In Autonomous, Cloud, Data, DBA, Security and auditing on September 9, 2019 at 13:24

“The most secure database is the one having no users connected.” You can quote me on that, I have learned it the hard way – from experience.

Database security in short means protecting the data. Markus Strauss’ article on Traditional Database Security reveals exactly the same.

Let us look into 3 recent examples of where data was not very well protected (to put it mildly), covered by CNN and CNBC:

An entire nation just got hacked: records of more than 5 million Bulgarians got stolen by hackers from the country’s tax revenue office.

A hacker gained access to 100 million Capital One credit card applications and accounts: in one of the biggest data breaches ever, a hacker gained access to more than 100 million Capital One customers’ accounts and credit card applications earlier this year.

Marriott says its Starwood database was hacked for approximately 500 million guests: “The Marriott just revealing a massive data breach involving guest reservation database at its Starwood brand. Marriott says it was unauthorized access since 2014. This includes up to 500 million guests… For approximately 327 million of these guests, the information that was exposed includes a combination of name, mailing address, phone number, email address, you ready for this – passport number… Starwood preferred guest account information, date of birth, gender, arrival and departure information… including reservation dates and communication preference. As you know, when you go to a hotel, especially internationally, they take your passport. Often times, they take a copy of your passport.”

So, granted traditional database security does not protect data well, how about looking into something new, innovative and at the same time something which has been developed and improved for more than 40 years? The Oracle Autonomous Database might be the answer (arguably is the answer). Tom Haunert’s interview with Vipin Samar (SVP of Database Security) gives an excellent overview of what Autonomous Database Security is all about.

Here is a list of 10 security benefits of Oracle’s Autonomous Database (benefits over any other database for all it matters):

1. There is no DBA access, no root access, no Operating System access… Still you can create users, roles, etc. just as before. But certain the commands are blacklisted.
2. There are no customer-managed keys: Oracle manages the keys.
3. Oracle automatically applies all security updates/patches to ensure data is not vulnerable to known attack vectors.
4. All data is encrypted using transparent data encryption.
5. Still database security features such as Virtual Private Database and Data Redaction are available.
6. Network connections from clients to the Autonomous Database are also encrypted using the client credentials wallet.
7. Data is encrypted everywhere: SQL*Net traffic, data in tablespaces and data in backups.
8. It is now possible to specify an access control list that blocks all IP addresses that are not in the list from accessing the database.
9. Oracle has been engaging with external assessment entities and independent auditors to meet a broad set of international and industry-specific compliance standards for service deployments in Oracle Cloud such as ISO 27001, SOC1, SOC2, PCI DSS, HIPAA/HITECH, and FedRAMP.
10. All operations are being audited.

The first one above is rather controversial point of debate among the DBA community. In order to ensure the security and the performance of the Autonomous Database, some SQL commands are restricted: ADMINISTER KEY MANAGEMENT, ALTER PROFILE, ALTER TABLESPACE, CREATE DATABASE LINK, CREATE PROFILE, CREATE TABLESPACE, DROP TABLESPACE. For DB links, you should use DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK to create database links in ADB.

Several DBA statements are restricted: ALTER PLUGGABLE DATABASE, ALTER DATABASE, ALTER SYSTEM, ALTER SESSION, ALTER USER, ALTER TABLE, CREATE TABLE and CREATE USER. To ensure the security and the performance of Autonomous Database, some Oracle XML DB features are also restricted. Same holds for Oracle Text, Oracle Spatial and Graph and APEX.

Oracle ADB is a database which is both Autonomous and Secure and as Mike Faden says: from self-securing database cloud services to the new cloud perimeter, Oracle technology protects your most valuable investment—your data.

And here are the 4 Areas of Self-Securing of Autonomous Database:

– Self-securing starts with the security of the Oracle Cloud infrastructure and database service. Security patches are automatically applied every quarter or as needed, narrowing the window of vulnerability. Patching includes the full stack: firmware, operating system [OS], clusterware, and database. There are no steps required from the customer side.
– Oracle encrypt customer data everywhere: in motion, at rest, and in backups. The encryption keys are managed automatically, without requiring any customer intervention. And encryption cannot be turned off.
– Administrator activity on Oracle Autonomous Data Warehouse Cloud is logged centrally and monitored for any abnormal activities. Oracle have enabled database auditing using predefined policies so that customers can view logs for any abnormal access: UNIFIED_AUDIT_TRAIL
– Built upon Oracle Database Vault, unique to Oracle Cloud, operations personnel have privilege to do all administrative tasks without any ability to ever see any customer data.

And finally something about the passwords in the Oracle Autonomous Database. They still have to be carefully chosen. Because as people say, “passwords are like underwear: make them personal, make them exotic, and change them on a regular basis.”

Oracle Autonomous Database: Dedicated vs Serverless

In Autonomous, Databases, DBA, Exadata, Oracle database, Oracle Engineered Systems on July 22, 2019 at 11:45

This blog post describes the 5 main differences between ATP-D & ATP-S and the 5 main ATP-D physical characteristics and constraints.

Autonomous Transaction Processing Dedicated (ATP-D) is a deployment choice that enables us to provision autonomous databases into dedicated Exadata cloud infrastructure instead of a shared infrastructure with other tenants.

ATP-D can be used for both OLTP or hybrid workloads for databases of any size. ATP-D is specifically good when you need highest governance, consistent performance and operational control.

For now, dedicated infrastructure means a Quarter Exadata Rack. Half and Full will be soon available too.

Besides complete physical storage isolation, ATP-D provides private IP networking, secure connections using transport layer security (TLS) credentials, and customization of software image lifecycle to align with application lifecycle.

The Fleet Administrator (think of a DBA for Autonomous Cloud) needs to create first the Exadata Infrastructure, then the CDB and only at the end the PDB. Recall that for ATP-S, you directly create the PDB.

These are the 5 main differences between ATP-D and ATP-S:

– Private IPs are not yet supported for serverless ADB deployments but they are on the short-term roadmap
– Private IPs are supported with ATP-Dedicated

– Serverless edition has no minimums or maximums for terms of usage
– Dedicated edition has a minimum term of one month and the minimum OCPU purchase is 1 OCPU per database node and up to the maximum number of OCPUs per rack: $26.88 per hours which means about $645 per day

– Loading data from object stores via DBMS_CLOUD is the recommended method for loading large data sets
– DBMS_CLOUD to load data is not applicable for ATP-D because DBMS_CLOUD is not available on ATP-D

– In ATP-D, the database version is 19c which is required for Auto-Indexing which is on by default
– Support for 19c / Auto-Indexing on ATP-S is on the roadmap

– ADB (serverless) does have auto-scaling – you can select auto scaling during provisioning or later using the Scale Up/Down button on the Oracle Cloud Infrastructure console
– ATP-D does not have auto-scaling support

Here are the 5 main ATP-D physical characteristics and constraints:

1. Quarter rack X7 Exadata Infrastructure:
– 2 severs: 92 OCPUs and 1.44TB RAM
– 3 Storage Servers: 76.8TB Flash and 107TB Disk

2. Cluster / Virtual Cloud Network:
– 1 Cluster per quarter rack

3. Autonomous Container Database:
– Maximum of 4 CDBs per Cluster
– The default data and temporary tablespaces for the database are configured automatically
– The name of the default data tablespace is DATA
– The database character set is Unicode AL32UTF8
– Compression is not enabled by default – use the table_compression clause if needed

4. Autonomous Database:
– High Availability SLA – Maximum 200 DBs
– Extreme Availability SLA – Maximum 25 DBs
– Placement Logic – Open on 1 server < 16 OCPU

5. Types of Users: Fleet Admin, Database Admin and Database User as Fleet Admin activities separated from DB Admin using IAM privileges

Note that now, there are 2 tabs for possible database connections – the serverless style DB connection and application connection:

Fleet Administrator: Fleet administrators create, monitor and manage Autonomous Exadata Infrastructure and Autonomous Container Database resources – a fleet administrator must be an Oracle Cloud user whose permissions permit the management of these resources and permit the use of the networking resources that need to be specified when creating these resources

Database Administrator: DBAs create, monitor and manage Autonomous Databases. Additionally, they create and manage Oracle Database users within these databases, and provide others the information necessary access the database – when creating an Autonomous Database resource, the DBA defines and gains access to the ADMIN administrative user account for the database

Database User: Database users are the developers who write applications that connect to and use an Autonomous Database to store and access the data. Database users do not need Oracle Cloud accounts: they gain network connectivity to and connection authorization information for the database from the database administrator

Few useful links:

A Using Oracle Database Features in Autonomous Transaction Processing
FAQs for Autonomous Transaction Processing – Dedicated
Data Center Regions for PaaS and IaaS
Oracle broadens the audience for Automated Transaction Database
2 Ways Oracle’s Autonomous Database Just Got More Useful

Bottom line: even with Autonomous, DBAs will be still needed!