Dontcheff

Archive for the ‘Cloud’ Category

On Kafka, JSON, PL/SQL and Advanced Queuing in Oracle Database 20c

In Autonomous, Cloud, Data, DBA, New features, PL/SQL on June 1, 2020 at 05:50

Oracle Corp. starts today (June 1st, 2020) a new fiscal year as the Oracle Database is slowly moving towards version 20c (still in preview mode only) which comes with almost 500 init.ora parameters and 5326 hidden/underscore parameters. There is a new one for 20c called kafka_config_file. But let us first take a step back and see its connection with Advanced Queuing.


Advanced Queuing is available in all editions of Oracle database, including XE. Since Oracle Advanced Queuing is implemented in database tables, all the operational benefits of high availability, scalability, and reliability are applicable to queue data.

Advanced Queuing can be accessed through the several interfaces: PL/SQL, Visual Basic, Java, Java Message Service, JDBC, ODP.NET, OCI (do not read Oracle Cloud Infrastructure – think of C language), etc.

Using PL/SQL to access Oracle Advanced Queuing is probably the most common method: using the PL/SQL packages DBMS_AQADM and DBMS_AQ.

Reading all that, one might think of Kafka. Kafka is a distributed, partitioned, replicated commit log service providing the functionality of a messaging system, but with a unique design. The aim here is not position Kafka and Oracle AQ against each other but show what is new in Oracle Database 20c that brings Kafka and Oracle together and what is important from DBA point of view.

Todd Sharp explained recently how to use Kafka Connect wth Oracle Streaming Service and Autonomous Database. Let us now see what is new in Oracle Database 20c:

Oracle Database 20c introduces Transactional Event Queues (TEQ), which are partitioned message queues that combine the best of messaging, streaming, direct messages, and publish/subscribe. TEQ operates at scale on the Oracle database. TEQ provides transactional event streaming, and runs in the database in a scale of 10s to 100s of billions of messages per day on 2-node to 8-node Oracle RAC databases, both on-premise and on the cloud. TEQ has Kafka client compatibility, which means, Kafka producer and consumer can use TEQ in the Oracle database instead of a Kafka broker. Check Advanced Queuing in 20c for more details.

1. Advanced Queuing: Kafka Java Client for Transactional Event Queues

Kafka Java Client for Transactional Event Queues (TEQ) enables Kafka application compatibility with Oracle database. This provides easy migration of Kafka applications to TEQ.

You do not have to manage a separate Kafka infrastructure and this new feature simplifies the event-driven application architectures with an Oracle converged database that now includes events data. Starting from Oracle Database 20c, Kafka Java APIs can connect to Oracle database server and use Transactional Event Queues (TEQ) as a messaging platform. Developers can migrate an existing Java application that uses Kafka to the Oracle database. A client side library allows Kafka applications to connect to Oracle database instead of Kafka cluster and use TEQ messaging platform transparently.

Two levels of configuration are required to migrate Kafka application to TEQ messaging platform:

– Database level configuration
– Application level configuration

Kafka application needs to set certain properties which will allow OKafka library to locate the Oracle Database. This is analogous to how Kafka application provides zoo keeper information. These connection properties can be set in the following two ways:

– using database user and password provided in plain text
– using JDBC wallet

The following are the prerequisites for configuring and running Kafka Java client for TEQ in an Oracle Database. Create a database user. Grant the following user privileges:

grant connect, resource to user;
grant execute on dbms_aq to user;
grant execute on dbms_aqadm to user;
grant execute on dbms_aqin to user;
grant execute on dbms_aqjms to user;
grant select_catalog_role to user;

Next, set the correct database init.ora parameter to use TEQ:

streams_pool_size=512M

Set the local listener too:

LOCAL_LISTENER= (ADDRESS=(PROTOCOL=TCP)(HOST= )(PORT=))

2. Advanced Queuing Support for JSON Data Type and PL/SQL

Oracle Database Advanced Queuing now supports the JSON data type.

Many client application and micro-services which use Advanced Queuing for messaging have better performance if they use JSON data type to handle JavaScript Object Notation (JSON) messages.

In this aspect, PUBLIC is granted EXECUTE privilege on all these types:

AQ$_AGENT, AQ$_AGENT_LIST_T, AQ$_DESCRIPTOR, AQ$_NTFN_DESCRIPTOR, AQ$_NTFN_MSGID_ARRAY, AQ$_POST_INFO, AQ$_POST_INFO_LIST, AQ$_PURGE_OPTIONS_T, AQ$_RECIPIENT_LIST_T,
AQ$_REG_INFO, AQ$_REG_INFO_LIST, AQ$_SUBSCRIBER_LIST_T, DEQUEUE_OPTIONS_T, ENQUEUE_OPTIONS_T, QUEUE_PROPS_T, SEEK_INPUT_T, , EK_OUTPUT_T, SYS.MSG_PROP_T, MESSAGE_PROPERTIES_T, MESSAGE_PROPERTIES_ARRAY_T, MSGID_ARRAY_T

Regarding the new features of PL/SQL in 20c, check first the interesting example of Steven Feuerstein on extension of loop iterators.

Check the New Features in Release 20c for Oracle Database PL/SQL for more details on PL/SQL extended iterators, PL/SQL qualified expressions enhancements, SQL macros, the new JSON data type and the new pragma SUPPRESSES_WARNING_6009.

3. Advanced Queuing: PL/SQL Enqueue and Dequeue Support

The following features are new in this release:

– Kafka Java Client for Transactional Event Queues (TEQ) which enables Kafka application compatibility with Oracle Database and thus providing easy migration of Kafka applications to TEQ
– PL/SQL Enqueue and Dequeue Support for JMS Payload and non-JMS Payload in Transactional Event Queues
– Transactional Event Queues for Performance and Scalability
– Simplified Metadata and Schema in Transactional Event Queues
– Support for Message Retention and Seekable Subscribers
– Advanced Queuing Support for JSON Data Type

For all the details, check the Changes in Oracle Database Advanced Queuing Release 20c.

In terms of performance and scalability, Oracle Transactional Event Queues have their Queue tables partitioned in 20c into multiple Event Streams which are distributed across multiple RAC nodes for high throughput messaging and streaming of events.

Remember that in 10.1, AQ was integrated into Oracle Streams and thus Oracle AQ was called “Oracle Streams AQ”. But in 12.1, Oracle Streams got deprecated and AQ was again named just “Oracle AQ”.

And finally: here is the 546 page long Transactional Event Queues and Advanced Queuing User’s Guide along with few good additional articles:

Oracle + Kafka = Better Architecture by Jonathan Wallace
Streaming data from Oracle into Kafka by Robin Moffatt
Extending Oracle Streaming with Kafka Compatibility by Somnath Lahiri

SYSDATE and Time Zones in the Autonomous Database

In Autonomous, Cloud, DBA, Oracle database on May 4, 2020 at 06:16

In the Oracle database, SYSDATE is one of the most popular functions, arguably the most popular.

PL/SQL based applications use quite extensively the SYSDATE function. What will happen when you migrate their data to Autonomous Cloud (or build a new application on Autonomous) as by default the Time Zone is set to Coordinated Universal Time (= UTC) in an OCI environment?

You are allowed to change the database and session timezones in ADB, but this doesn’t change the SYSDATE and SYSTIMESTAMP in the timezones. So, the PL/SQL packages, procedure and functions and in particular all SQL using SYSDATE and SYSTIMESTAMP might not return what you expect. You need to check for instance what happened during the last one hour (sysdate-1/24) – and you might be surprised – you will get the result for the past one hour but it might not be your last hour.

Currently, it is not possible to change the timezone of SYSDATE and SYSTIMESTAMP since they are coming from the operating system. The SYSDATE and SYSTIMESTAMP functions simply performs a system-call to the server Operating System to get the time – the so called “gettimeofday” call. The server OS (Unix) timezone settings influences the time that the OS will pass on to Oracle and returned by SYSDATE and SYSTIMESTAMP.

Moreover, there is no Oracle database parameter/init.ora setting that will impact the SYSDATE and SYSTIMESTAMP timezone (= give a result that is in an other timezone than the OS timezone setting). Also, SYSDATE and SYSTIMESTAMP do not use the Oracle timezone information (= Oracle RDBMS dst patches) in the database and are NOT affected by the used DBTIMEZONE / SESSIONTIMEZONE settings.

On a side note, in the Oracle database there is a dynamic init.ora parameter called FIXED_DATE which enables you to set a constant date that SYSDATE will always return instead of the current date.

As far as I know, there is a project going on inside Oracle to provide a parameter to make SYSDATE return date in a database timezone, but there is no ETA for it.

So, how can we bypass this issue?

The general recommendation is to use CURRENT_DATE and CURRENT_TIMESTAMP which will return the date in session timezone. If you cannot modify the applications, then there is a workaround to use the SQL Translation Framework functionality (Hello Kerry!) to change SYSDATE to CURRENT_DATE in the queries on the fly. This would require recreating the PL/SQL procedures, so that they can be replaced as well. It also requires creating a schema level logon rigger to enable the Translation Framework. This needs to be carefully tested, but in general it works.

UTC is the recommended time zone to use, and of course, in theory at least, this makes a lot of sense in a global distributed database environment. In reality, everyone like his/her own time zone!

These 4 links below are worth reading if you are interested in timestamp and time zone issues and functionalities:

How to change the Time Zone in Oracle Database hosted in OCI with an Example: Doc ID 2459830.1
Timestamps and time zones – Frequently Asked Questions: Doc ID 340512.1
How to Change the “Database Time” (SYSDATE and SYSTIMESTAMP) to another Time/Timezone: Doc ID 1988586.1
Datetime Data Types and Time Zone Support

The following are some points to note related to time zone upgrade in a multitenant environment:

– Updating the time zone version of a CDB does not change the time zone version of the PDBs
– Updating the time zone version of a PDB does not change the time zone version of the other PDBs
– A new PDB is always assigned the time zone version of PDB$SEED
– PDB$SEED is always assigned the time zone version at the time of CDB creation
– The time zone version of PDB$SEED cannot be changed

And if you plan on running ALTER DATABASE SET TIME_ZONE, have in mind that:

– The ALTER DATABASE SET TIME_ZONE statement returns an error if the database contains a table with a TIMESTAMP WITH LOCAL TIME ZONE column and the column contains data
– The change does not take effect until the database has been shut down and restarted
– You can find out the database time zone by entering the following query: SELECT dbtimezone FROM DUAL;

SET TIME_ZONE can be done also on session level if needed:

alter session set time_zone='-03:00';

And please note once more that SYSDATE and SYSTIMESTAMP have nothing to with the DATABASE TIMEZONE (DBTIMEZONE).

On a final note: the information above is valid for all flavors of Autonomous: ADW, ATP, ADW-D and ATP-D.

===

Years ago, I saw this quiz on dbasupport.com – we have the following two PL/SQL blocks:

 

BEGIN
  WHILE sysdate = sysdate LOOP
    NULL;
  END LOOP;
END;
/
 
DECLARE
  x DATE;
BEGIN
  LOOP
    BEGIN
      SELECT null INTO x FROM dual WHERE sysdate = sysdate;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN EXIT;
    END;
  END LOOP;
END;
/

Question is: what happens after you run them? Are the loops above both finite, both infinite or is it so that one of them is finite and the other one infinite?

If you cannot answer the question just run them in SQL*Plus, etc. Then, try to explain why – the reason for being finite or infinite.

The 20c Database Upgrade Mystery

In Cloud, Databases, DBA, Oracle database on April 27, 2020 at 14:31

Oracle Database 20c is available only for preview. It is not available for production use. Upgrades to or from Oracle Database 20c are not supported.

So, upgrades are not supported but there exists an Oracle Database 20c Upgrade Guide. The 20c upgrade pdf file is 461 pages long!

Let me shed some light on this “mystery”.

As of today, April 27th 2020 (Oracle Database 19c is today 1 year old on Linux), Oracle 20c is only available from the Oracle Public Cloud in preview mode. To try out this 20c preview, you will need an Oracle Public Cloud tenancy with cloud credits (paid or promotional), as the DBCS instance will require an OCI Compute VM. And, by the way, 20c does *not* run on Exadata.

Once 20c becomes generally available also in non-preview only mode, upgrades will be also available.

You can perform a direct upgrade to the new release from the following releases:

• 19c
• 18c
• 12.2.0

Here is what we need to know about compatibility:

– Before upgrading to Oracle Database 20c, you must set the COMPATIBLE initialization parameter to at least 12.2.0
– In Oracle Database 20c, when the COMPATIBLE initialization parameter is not set in your parameter file, the COMPATIBLE parameter value defaults to 20.0.0
– Installing earlier releases of Oracle Database on the same computer that is running Oracle Database 20c can cause issues with client connections

The most important point about the 20c architecture is that starting with the first release of Oracle Database 20c, non-CDB Oracle Database upgrades to non-CDB architecture are desupported. Meaning you need a container and your data will be in a pluggable database.

Starting with Oracle 19c you can have up to 3 pluggable databases (PDBs) per container database (CDB) without requiring additional multitenant license. See page 11 of Oracle Database Licensing Information User Manual for all the details (it is a 361 page pdf file).

So, when upgrading to 20c, we have 2 options:

Option 1: Convert the non-CDB to a PDB before upgrade

With this option, you plug in the non-CDB Oracle Database release to the same release CDB. For example, plug in a non-CDB Oracle Database Release 19c into an Oracle Database 19c release CDB. Finish converting the non-CDB Oracle Database to a PDB. Then, upgrade the entire CDB, with its PDBs, to Oracle Database 20c.

Option 2: Plug in the non-CDB, upgrade, and finish converting the non-CDB to a PDB after upgrade

With this option, you plug in a non-CDB Oracle Database release to an OracleDatabase 20c CDB. Upgrade the plugged-in non-CDB Oracle Database to Oracle Database 20c. Then, finish converting the non-CDB Oracle Database to a PDB.

Here is what you need to know about the DBUA and the Oracle home:

– Starting with Oracle Database 20c, Database Upgrade Assistant (DBUA) is replaced by the AutoUpgrade utility
– Starting with Oracle Database 20c, the default network administration directory changes from the previous default in the local Oracle home, Oracle_home/network (for example, /u01/app/oracle/product/19.1.0/dbhome_1/network), to a new location.
– The new default location is the shared Oracle Base Home, in the path ORACLE_BASE/ homes/HOME_NAME/network/admin
– Starting with Oracle Database 20c, an Oracle Database installation configures all Oracle Database homes in read-only mode by default

And here is what you need to know before the upgrade about security and the init.ora parameters:

– Starting with Oracle Database 20c, the data types DBMS_CRYPTO_TOOLKIT_TYPES and package DBMS_CRYPTO_TOOLKIT are desupported
– The init.ora parameters UNIFIED_AUDIT_SGA_QUEUE_SIZE, UNIFIED_AUDIT_SGA_QUEUE_SIZE, AUDIT_FILE_DEST, AUDIT_SYS_OPERATIONS, AUDIT_SYSLOG_LEVEL an d AUDIT_TRAIL have been desupported
– Desupport of IGNORECASE parameter for passwords: starting in Oracle Database 20c, the IGNORECASE parameter for the orapwd file is desupported and all newly created password files are case-sensitive
– Desupport of DISABLE_DIRECTORY_LINK_CHECK: the DISABLE_DIRECTORY_LINK_CHECK parameter is desupported, with no replacement

Further info and links were listed in detail by the King of Database Upgrades Mike Dietrich – his blog is dedicated to Oracle Database upgrades!

Now, how about long term support? Do you care only about LTS database releases when planning upgrades? As Tim Hall says: “It’s important to check out what is happening in the 20c release, because it may alter how you use the earlier releases now. There is no point launching into a new development using a feature that is about to disappear. Remember Oracle Streams anyone?” Yes, indeed Tim: Streams, Advanced Replication… I rather be on the latest release and use the new features – live and learn…

Oracle 20.2.0 new features for DBAs

In Autonomous, Cloud, Databases, New features, Oracle database on February 25, 2020 at 09:38

Oracle 20c is now available in preview mode from the Oracle Public Cloud. Preview version databases are not intended for production use and have limited functionality.

After testing some of the new features of 20c, here is what might be of interest for most DBAs:

1. Provisioning the database is relatively simple. You need an SSH Public key, create a VCN (Virtual Cloud Network) and a client subnet in your compartment. A hostname prefix is also mandatory. Note that the administrator password must be 9 to 30 characters and contain at least 2 uppercase, 2 lowercase, 2 special, and 2 numeric characters. The special characters must be _, #, or -. You cannot bypass that. The shape type must be “Virtal Machine” and the SMS (Storage Management Software) must be “Logical Volume Manager”.

Once provisioned you need the IP address which is under “Nodes” (bottom left, just under “Resources”). As you can see, it is no longer under “General Information”:

And … you can stop the database only manually (as of today, February 25th, 2020) – there is no button for stopping the node. Just terminate in case you are on a tight budget.

2. Blockchain tables

Blockchain tables are append-only tables in which only insert operations are allowed. Deleting rows is either prohibited or restricted based on time. Rows in a blockchain table are made tamper-resistant by special sequencing & chaining algorithms. Users can verify that rows have not been tampered. Have a look at an example I used to create a blockchain table:

Here is how to manage blockchain tables.

Most important is to specify the Retention Period for the Blockchain Table by using the NO DROP clause in the CREATE BLOCKCHAIN TABLE statement. Also specify the Retention Period for Rows in the Blockchain Table: use the NO DELETE clause in a CREATE BLOCKCHAIN TABLE statement.

3. A multitenant container database is the only supported architecture in Oracle Database 20c. While the documentation is being revised, legacy terminology may persist. In most cases, “database” and “non-CDB” refer to a CDB or PDB, depending on context. In some contexts, such as upgrades, “non-CDB” refers to a non-CDB from a previous release. Check the changes in Oracle 20c for Oracle Multitenant.

4. Data Pump

– Oracle Data Pump 20c can include and exclude objects in the same export or import operation meaning that now, Oracle Data Pump commands can include both INCLUDE and EXCLUDE parameters in the same operation. By enabling greater specificity about what is being migrated, this enhancement makes it easier to migrate to Oracle Cloud, or to another on-premises Oracle Database.

Note: when you include both parameters in a command, Oracle Data Pump processes the INCLUDE parameter first, and includes all objects identified by the parameter. Then it processes the EXCLUDE parameters, eliminating the excluded objects from the included set. Here is an example of including only 2 tables but excluding all indexes except the PKs (real use case: you want to enable Oracle Auto Indexing in ADB and while importing the data you need to drop all indexes except the PKs):

expdp julian SCHEMAS=JULIAN DUMPFILE=julian.dmp REUSE_DUMPFILES=YES 
INCLUDE=TABLE:\"IN \(\'CLIENTS\',\'SALES\'\)\" 
EXCLUDE=INDEX:\"LIKE \'IDX\%\'\"

– Oracle Data Pump 20c resumes transportable tablespace export and import jobs that are stopped

– Oracle Data Pump 20c supports parallel export and import operations for Transportable Tablespace (TTS) metadata

– Oracle Data Pump 20c supports optional index compression on imports, including for Oracle Autonomous Database

– Oracle Data Pump 20c supports adding, changing and eliminating table compression

– Oracle Database 20c supports index compression as well by introducing a new TRANSFORM parameter clause, INDEX_COMPRESSION_CLAUSE

– Oracle Data Pump 20c can perform exports from Oracle Autonomous Database into dump files in a cloud object store

– Starting with Oracle Database 20c, a checksum is now added to the dumpfile – you can use the checksum to help to confirm that the file is valid after a transfer to or from the object store and also after saving dumpfiles on on-premises and that it has no
accidental or malicious changes

5. Small improvements and changes in 20c:

– The IGNORECASE parameter for the orapwd file is desupported – all newly created password files are case-sensitive

– A new dynamic view called V$PMEM_FILESTORE displays information about Persistent Memory Filestores

– Certain predefined columns of unified audit records from common unified audit policies can be written to the UNIX SYSLOG destination – to enable this feature, you set UNIFIED_AUDIT_COMMON_SYSTEMLOG, a new CDB level init.ora parameter (added in Oracle 19c (19.3) but not included in the References)

– You now can set the TABLESPACE_ENCRYPTION_DEFAULT_ALGORITHM dynamic parameter to define the default encryption algorithm for tablespace creation operations

– Database Vault: a DV_OWNER common user in the CDB root can prevent local users from creating Oracle Database Vault controls on common objects in a PDB

– AutoShrink: Oracle Automatic Storage Management Cluster File System (Oracle ACFS) automatic shrinking automatically shrinks an Oracle ACFS file system based on policy, providing there is enough free storage in the volume

– The Oracle Grid Infrastructure feature Automatic Storage Management Cluster File System (Oracle ACFS) is desupported with Microsoft Windows

– An Oracle Database installation configures all Oracle Database homes in read-only mode by default

– Traditional auditing is deprecated in Oracle Database 20c thus Oracle recommend that we use unified auditing, which enables selective and more effective auditing inside Oracle Database

– The package DBMS_OBFUSCATION_TOOLKIT is desupported, and replaced with DBMS_CRYPTO

– Older encryption and hashing algorithms contained within DBMS_CRYPTO are deprecated

– The Large Object (LOB) features DBMS_LOB.LOADFROMFILE and LOB buffering are desupported

– You can configure database clients to maintain multiple Secure Sockets Layer (SSL) sessions using different SSL certificates

– In the DBMS_ROLLING.set_parameter(), there is a new parameter, called BLOCK_UNSUPPORTED – by default, BLOCK_UNSUPPORTED
is set to 1 [YES], indicating that operations performed on tables that are unsupported by Transient Logical Standby will be blocked on the primary database. If set to 0 [OFF], then the DBMS_ROLLING package does not block operations on unsupported tables

– In order to coordinate with the Oracle GoldenGate feature OGG EXTRACT, the LOGICAL_REPLICATION clause now provides support for automatic extract of tables

– Two new views, DBA_OGG_AUTO_CAPTURED_TABLES, and USER_OGG_AUTO_CAPTURED_TABLES, provide you with tools to query which tables are enabled for Oracle GoldenGate automatic capture

6. Finally, her are the 7 new init.ora parameters in Oracle 20.2.0:

DBNEST_ENABLE (DbNest is OS resource and file system isolation for PDBs)
DBNEST_PDB_FS_CONF
DIAGNOSTICS_CONTROL (meant to be used with Oracle Support)
MAX_IDLE_BLOCKER_TIME (maximum number of minutes before a blocking session is automatically terminated)
PMEM_FILESTORE
TABLESPACE_ENCRYPTION_DEFAULT_ALGORITHM
OPTIMIZER_REAL_TIME_STATISTICS

The DBMS_CLOUD and UTL_SMTP packages in the Autonomous Database

In Autonomous, Cloud, DBA, PL/SQL on January 16, 2020 at 16:07

New Autonomous Database features are being added all the time. For now, ADB for shared infrastructure supports 18c while 19c can be used only in preview mode.

The preview period for ADB 19c ended yeaterday: January 15th, 2020. From now on, it is no longer possible to provision new preview instances nor clone existing instances to a preview instance. However, existing preview instances will remain available until January 30th when the final termination process will happen. And now, we are awaiting for the Oracle 20c preview version.

So, what else is new in the Autonomous Database (Shared Infrastructure):

The DBMS_CLOUD REST API functions provide a generic API that lets you call any REST API with the following supported cloud services:

Oracle Cloud Infrastructure
Amazon Web Services
Azure Cloud
Oracle Cloud Infrastructure Classic

DBMS_CLOUD supports GET, PUT, POST, HEAD and DELETE HTTP methods. The REST API method to be used for an HTTP request is typically documented in the Cloud REST API documentation.

Check also the summary of the DBMS_CLOUD_ADMIN package and especially the CREATE_DATABASE_LINK procedure as this is the supported way to create a database link in ADB.

To run DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK with a user other than ADMIN you need to grant EXECUTE and CREATE DATABASE LINK privileges to that user. For example, run the following command as ADMIN to grant privileges to JULIAN:

GRANT EXECUTE ON DBMS_CLOUD_ADMIN TO JULIAN;
GRANT CREATE DATABASE LINK TO JULIAN;

Behind the curtains, Oracle runs C##CLOUD$SERVICE.DBMS_CLOUD_DBLINK_INTERNAL.

Note that packages like DBMS_CLOUD, DBMS_CLOUD_ADMIN, DBMS_CLOUD_CORE, DBMS_CLOUD_DBLINK, etc. are owned by C##CLOUD$SERVICE (owns 23 packages), not by SYS!

In ADB, there are few restrictions for some PL/SQL packages. Oracle have removed the UTL_TCP package.

UTL_HTTP Restrictions:

Connections through IP addresses are not allowed
– Only HTTPS connections are allowed (HTTP and HTTP_PROXY are disallowed)
– The only allowed ports are 443 and 8443
– The SET_WALLET and SET_AUTHENTICATION_FROM_WALLET APIs are disallowed
– The WALLET_PATH and WALLET_PASSWORD arguments for the CREATE_REQUEST_CONTEXT, REQUEST, and REQUEST_PIECES APIs are ignored

UTL_SMTP Restrictions:

– The only supported email provider is Oracle Cloud Infrastructure Email Delivery service
– Mail with an IP address in the host name is not allowed
– The only allowed ports are 25 and 587

DBMS_NETWORK_ACL_ADMIN Restrictions:

– Granting ACL privileges on IP addresses is not allowed
– The http_proxy and use_passwords ACL privileges are not allowed

This Oracle example is using DBMS_CLOUD.SEND_REQUEST in order to create and delete an Oracle Cloud Infrastructure Object Storage bucket.

Here are 2 new additions to the ADB feature list:

1. Access Control Lists Honored By ADB Built-in Tools

You can control and restrict access to your Autonomous Database by setting network access control lists (ACLs). When you provision your Autonomous Database you can either choose the database to be accessible from all IP addresses or you can restrict access to your database to a whitelisted set of clients. You can change your decision after provisioning and set or change the access rules.

Here are 3 links for additional details:

1. VCNs and Subnets for details on Virtual Cloud Networks (VCN).
2. Oracle Services: Service Gateway Access for details on setting up a Service Gateway.
3. Transit Routing: Private Access to Oracle Services for details on Transit Routing.

2. Send Emails from ADB using UTL_SMTP

There are 4 steps you must follow in order to send an email from ADB:

1. Configure Email Delivery Service
2. Allow SMTP Access for ADMIN via an Access Control Entry (ACE)
3. Create a PL/SQL Procedure to Send Email
4. Send a Test Email

Check How to Send an Email using UTL_SMTP in Autonomous Database by Can Tuzla for all the details and examples.

At the end, note that now we have the “Next Maintenance” field which shows the date and time for the upcoming maintenance window. All ADB instances are automatically assigned to a maintenance window and different instances can have different maintenance windows.

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.”

Amazon’s Aurora and Oracle’s Autonomous ATP

In Autonomous, Cloud, DBA, PostgreSQL on August 29, 2018 at 09:26

Databases are very much like wine, cheese and trees: they get better as they age.

Amazon Aurora exists since 2015. The word aurora comes Latin, means dawn. The name was borne by the Roman mythological goddess of dawn and by the princess in the fairy tale Sleeping Beauty.

Both Amazon’s “dawn” Aurora and Oracle’s ATP are typical cloud OLTP systems.

The question is: what are their differences, which one is better and meant exactly for my needs?

Oracle ATP is based on Oracle’s database and Exadata, here are all the innovations adopted from both systems:

Amazon’s Aurora has 2 flavors: Amazon Aurora MySQL and Amazon Aurora PostgreSQL.

Amazon Aurora MySQL is compatible with MySQL 5.6 using the InnoDB storage engine. Certain MySQL features like the MyISAM storage engine are not available with Amazon Aurora. Amazon Aurora PostgreSQL is compatible with PostgreSQL 9.6. The storage layer is virtualized and sits on a proprietary virtualized storage system backed up by SSD. And you pay $0.20 per 1 million IO requests.

Oracle’s Autonomous database comes also in 2 flavors: Oracle ADW and Oracle ATP. Check Franck Pachot’s article ATP vs ADW – the Autonomous Database lockdown profiles to see the differences of both cloud databases.

In general, one can compare Oracle ADW with Amazon Redshift and Oracle ATP with Amazon Aurora.

One way to compare is to look at the ranking provided by DB-Engines: Amazon Aurora vs. Oracle. No-brainer who the leader is: score of 1300 vs score of 5 in favor of Oracle.

Another interesting comparison comes from Amalgam Insights. Check how Oracle Autonomous Transaction Processing lowers barriers to entry for data-driven business. Check out the DBA labor cost involved: 5 times less in favor of Oracle ATP compared to Amazon! All the routine DBA tasks have been totally eliminated.

The message from them is very clear: “Oracle ATP could reduce the cost of cloud-based transactional database hosting by 65%. Companies seeking to build net-new transactional databases to support Internet of Things, messaging, and other new data-driven businesses should consider Oracle ATP and should do due diligence on Oracle Autonomous Database Cloud for reducing long-term Total Cost of Ownership.”

This month (August 2018), there was an interesting article by Den Howlett entitled Oracle introduces autonomous transaction processing database – pounds on AWS. Here are 2 interesting and probably correct statements/quotes from there:

1. It really is hard to get off an established database, even one that can be as expensive as Oracle can turn out to be.
2. Some of the very largest workloads will not go to the public cloud anytime soon. Maybe never which in internet years is after 2030.

As a kind of proof of how reliable and fast Oracle’s Autonomous Transaction Processing database is consider the following OLTP workload running non-stop in a balanced way without any major spikes and without a single queued statement!

No human labor, no human error, and no manual performance tuning!

The DBA profession beyond autonomous: a database without a DBA is like a tree without roots

In Autonomous, Cloud, Databases, DBA on May 30, 2018 at 19:41

“To make a vehicle autonomous, you need to gather massive streams of data from loads of sensors and cameras and process that data on the fly so that the car can ‘see’ what’s around it” Daniel Lyons

Let me add that the data must be stored somewhere, analyzed by some software, monitored and backed up by someone, and so on and so on…

Top 5 Industry Early Adopters Of Autonomous Systems are: (1) Information Technology: Oracle’s Autonomous Data Warehouse Cloud, (2) Automotive, (3) Manufacturing, (4) Retail and (5) Healthcare.

Being an early adopter of ADWC, I must say that it is probably the best product created by Oracle Corporation. For sure part of Top Five.

This month (May 2018), ComputerWeekly published an article quoting Oracle CEO Mark Hurd that the long-term future of database administrators could be at risk if every enterprise adopts the Oracle 18c autonomous database.

“Hurd said it could take almost a year to get on-premise databases patched, whereas patching was instant with the autonomous version. If everyone had the autonomous database, that would change to instantaneous.”

So where does that leave Oracle DBAs around the world? Possibly in the unemployment queue, at least according to Hurd.

“There are hundreds of thousands of DBAs managing Oracle databases. If all of that moved to the autonomous database, the number would change to zero,” Hurd said at an Oracle media event in Redwood Shores, California.

If you are interested in more detail on this subject, I suggest you read the following articles in the order below:

The Robots are coming by James Anthony: “But surely we’ve been here before? Indeed, a quick Google search brings up the following examples of white papers by Oracle with a reference to the database being self-managing all the way back to 2003.”

Oracle Autonomous Database and the Death of the DBA by Tim Hall: “Myself and many others have been talking about this for over a decade. ”

Death of the DBA, Long Live the DBA by Kellyn Pot’Vin-Gorman: “With DBAs that have been around a while, we know the idea that you don’t need a DBA has been around since Oracle 7, the self-healing database.”

No DBA Required? by Tim Hall: “It will be interesting to see what Oracle actually come up with at the end of all this…”

Self-Driving Databases are Coming: What Next for DBAs? by Maria Colgan: “It’s also important for DBAs to remember that the transition to an autonomous environment is not something that will occur overnight.”

Death of the Oracle DBA (again) by Johanthan Stuart: “Twenty years later I run Claremont’s Managed Services practice and the DBA group is our largest delivery team.”

Don’t Fall For The “Autonomous Database” Distraction by Greg McStravick: a totally different point of view on autonomous databases.

Now, “a picture is worth a thousand words”. Here are 5 screenshots from the Autonomous Data Warehouse Cloud documentation:

1. Who will be creating external tables using the DBMS_CLOUD package?

2. Who will run “alter database property set.. ” in order to create credentials for the Oracle Cloud Infrastructure?

3. Who will restore and recover the database in case of any type of failure? Or failures never happen, right?

4. Who will manage run away SQL with cs_resource_manager and run “alter system kill session”?

5. Who will manage the CBO statistics and add hints?

As of today, we have 4 Exadata choices with Autonomous being by far the best. For data warehouse loads for now. As explained by Alan Zeichick, Autonomous Capabilities Will Make Data Warehouses — And DBAs — More Valuable. “No need for a resume writer: DBAs will still have plenty of work to do.”

So still: a database without a DBA is like a tree without roots.

P.S. Check out the book Human + Machine: Reimagining Work in the Age of AI by Paul R. Daugherty and H. James (Jim) Wilson.

DBA Internals of the Oracle Autonomous Database

In Cloud, DBA, Oracle database, Oracle internals on March 28, 2018 at 07:11

First things first: the word autonomous come from the Greek word autónomos which means “with laws of one’s own, independent”.

After starting using the Autonomous Data Warehouse Cloud, I must say I am pleasantly surprised to see something totally new, simple, uncomplicated and effortless, with no additional tuning or re-architecturing of the Oracle databases needed – the underlying Oracle Cloud Infrastructure is super fast and highly reliable.

1. You may connect to ADWC by either using the web interface as you can see above or as a client (I use SQL Developer 17.4) but for the client connection type choose Cloud PDB and not TNS. Your configuration file is a zip file and not a plain text file to what DBAs are used to.

2. You cannot create indexes on columns, you cannot partition tables, you cannot create materialized views, etc. Not even database links. You will get an error message: “ORA-00439: feature not enabled: Partitioning” or “ORA-01031: insufficient privileges”.

ADWC lets you create primary keys, unique keys and a foreign key constraints in RELY DISABLE NOVALIDATE mode which means that they are not enforced. These constraints can be created also in enforced mode, so technically you can create constraints as in a non-autonomous Oracle database.

Note that in execution plans primary keys and unique keys will only be used for single table lookups by the optimizer, they will not be used for joins.

But … you can run alter system kill session!

3. The Oracle Autonomous Data Warehouse interface contains all necessary capabilities for a non-professional database user to create its own data marts and run analytical reports on the data. You can even run AWR reports.

4. You do not have full DBA control as Oracle (in my opinion) uses lockdown profiles in order to make the database autonomous. As ADMIN user, you have 25 roles including the new DWROLE which you would normally grant to all ADWC users created by you. Among those 25 roles, you have GATHER_SYSTEM_STATISTICS, SELECT_CATALOG_ROLE, CONSOLE_ADMIN, etc. You have access to most DBA_ and GV_$ views. Not to mention the 211 system privileges.

5. ADWC configures the database initialization parameters based on the compute and storage capacity you provision. ADWC runs on dozens of non-default init.ora parameters. For example:

parallel_degree_policy = AUTO
optimizer_ignore_parallel_hints = TRUE
result_cache_mode = FORCE
inmemory_size = 1G

You are allowed to change almost no init.ora parameters except few NLS_ and PLSQL_ parameters.

And the DB block size is 8K!

6. I can see 31 underscore parameters which are not having default values, here are few:

_max_io_size = 33554432 (default is 1048576)
_sqlmon_max_plan = 4000 (default is 0)
_enable_parallel_dml = TRUE (default is FALSE)
_optimizer_answering_query_using_stats = TRUE (default is FALSE)

One of the few alter session commands you can run is “alter session disable parallel dml;”

7. Monitoring SQL is easy:

But there is no Oracle Tuning Pack: you did not expect to have that in an autonomous database, did you? There is no RAT, Data Masking and Subsetting Pack, Cloud Management Pack, Text, Java in DB, Oracle XML DB, APEX, Multimedia, etc.

8. Note that this is (for now) a data warehousing platform. However, DML is surprisingly fast too. I managed to insert more than half a billion records in just about 3 minutes:

Do not try to create nested tables, media or spatial types, or use LONG datatype: not supported. Compression is enabled by default. ADWC uses HCC for all tables by default, changing the compression method is not allowed.

9. The new Machine Learning interface is easy and simple:


You can create Notebooks where you have place for data discovery and analytics. Commands are run in a SQL Query Scratchpad.

10. Users of Oracle Autonomous database are allowed to analyze the tables and thus influence on the Cost Based Optimizer and hence on performance – I think end users should not be able to influence on the laws (“νόμος, nomos”) of the database.

Conclusion: The Autonomous Database is one of the best things Oracle have ever made. And they have quite a portfolio of products….

Finally, here is a live demo of the Oracle Autonomous Data Warehouse Cloud: