Oracle Autonomous Database: A DBA Perspective

When more people started using Autonomous Database services, more Admin tools are made available as well. When ADW was introduced, all the DBA had for monitoring or diagnosis or utilization was only the Service Console. Here are some administrative enhancements in ATP/ADW we saw in the past 1+ year.

  • July 2018: SQL Developer 18.2.0 and later allows for setting up connections without the need for Keystore passwords.
  • September 2018: Autonomous Data Warehouse allows users to create partitioned tables, indexes, and materialized views. 
  • March 2019: Autonomous Database provides cloning where you can choose to clone either the full database or only the database metadata. 
  • April 2019: A simple way to secure your autonomous database instances using network access control lists. Specifying an access control list blocks all IP addresses that are not in the list from accessing the database. 
  • May 2019: Oracle Management Cloud supports monitoring Autonomous Databases via its Oracle Database Management console. This provides both monitoring and alerting for your Autonomous Database instances. 
  • May 2019: It is possible to create a database link from an ADW instance to any database that is accessible from an ADW/ATP including other ADW/ATP instances.
  • June 2019: You can select auto-scaling during provisioning or later using the Scale-Up/Down button on the Oracle Cloud Infrastructure console.
  • June 2019: Oracle Rest Data Services (ORDS) is included with Autonomous Database. ORDS maps HTTP(S) verbs (GET, POST, PUT, DELETE, etc.) to database operations and returns any results formatted using JSON. 
  • June 2019: Oracle SQL Developer Web provides a browser-based integrated development environment and administration interface for Oracle Autonomous Database. 
  • June 2019: Autonomous Database includes Oracle’s premiere low-code development platform: Application Express (APEX). 
  • July 2019: Customers can get a real-time and historical view of performance data directly on the OCI console using Performance Hub.
  • August 2019: Autonomous Database supports multiple directories which makes it even easier for customers to migrate their existing applications to ADB.

Oracle Open World 2019 announced that there are two autonomous databases included in the Always Free tier! Great news!! Now, DBAs and developers have no reason not to get into Autonomous Database and start playing. Maybe you can develop an application using APEX and ATP for your favorite local charity 🙂

I will be comparing the traditional DBA activities against the activities you can do on ATP or ADW of Autonomous Database service in my talk “Autonomous Database: What’s the Admin’s Role?” on Sep 27th at the Arizona User Group meeting.

Oracle Autonomous Database “What’s the Admin Role?” & “Let’s Talk AI, ML, & DL”

Friday, Sep 27, 2019, 12:00 PM

Republic Services – 2nd Floor Conference Rooms
14400 N 87th St (AZ101 & Raintree) Scottsdale, AZ

13 AZORAS Attending

REGISTER NOW FOR AZORA’S FALL MEETING — with Oracle Ace Director Biju Thomas! Learn about Oracle’s Autonomous Database focusing on the Admin’s role, talk a bit more about AI, ML and DL and get the highlights from Oracle Open World #OOW 19 — all of this in one afternoon with lunch. AZORA’s Meeting Agenda: 12:00pm -1:00pm Lunch – Sponsored by OneNe…

Check out this Meetup →

ODC Appreciation Day: Two Useful Parameters in 18c #ThanksODC

Happy to be part of the #ThanksODC campaign in 2018. Often I learn something new related to Oracle from a forum or blog or a social media post or an article written by someone who loves to help the Oracle community. The Oracle community has helped me tremendously to do my job better. Kudos to the Oracle Development Community! Keep growing!!

I like two parameters introduced in Oracle Database 18c -optimizer_ignore_hints and  optimizer_ignore_parallel_hints. Maybe I have seen too many SQL statements with hints when trying to fix performance issues after upgrading to a newer release of the database!

In earlier releases the optimizer_ignore_hints parameter was an underscore parameter (_optimizer_ignore_hints). Now we can set the parameter at the database level (ALTER SYSTEM) or at the session level (ALTER SESSION) to disable hints. This parameter makes the Oracle optimizer ignore all embedded hints in the SQL statement. By default, the value for this parameter is FALSE, meaning, all embedded hints will be recognized in 18c. 

The related parameter is optimizer_ignore_parallel_hints. Here the optimizer only ignores the PARALLEL hints. 

Properties for both OPTIMIZER_IGNORE_HINTS and OPTIMIZER_IGNORE_PARALLEL_HINTS

My preference would be to set optimizer_ignore_parallel_hints in the init file (at the database level) for OLTP databases if the application uses many SQLs with hints embedded. If there is a need to set optimizer_ignore_hints, I would set it for the applications (or users) using a logon trigger at the session level (ALTER SESSION).