Dontcheff

DBA Productivity and Oracle Database 12.2

In Cloud, DBA, Oracle database on February 9, 2017 at 15:15

“Technology can be our best friend, and technology can also be the biggest party pooper of our lives. It interrupts our own story, interrupts our ability to have a thought or a daydream, to imagine something wonderful, because we’re too busy bridging the walk from the cafeteria back to the office on the cell phone.” Steven Spielberg

busy

The DBA profession was recently rated as #6 among the Best Technology Jobs. Good for all of us who are in this line of business. But notice the stress level: Above Average!

DBAs are often busy people. Is that good or bad? Is “busy the new stupid”?

Automation is not a luxury for the DBAs but it is a way in which DBAs execute their job. Of course, there is one thing that cannot be automated and that is quality but the best DBAs automate almost everything.

Automating the database is a Win-Win for DBAs and DevOps. The mindset of the Enterprise DBA should be focused on harnessing the power of automation.

The following data shows what tasks are mostly and least automated:

dba_automation

Look at the last row above. I still wonder why Automatic SQL Tuning is so underestimated. It was so powerfully helping the DBA team of Nokia…

Oracle Database 12cR2 is out. And 12.2 comes with yet another new set of database automation related features:

– Oracle Data Guard now supports multiple failover targets in a fast-start failover configuration. Previous functionality allowed for only a single fast-start failover target. Multiple failover targets increase high availability by making an automatic failover more likely to occur if there is a primary outage.

– Oracle automatically synchronizes password files in Data Guard configurations: when the passwords of SYS, SYSDG, and so on, are changed, the password file at the primary database is updated and then the changes are propagated to all standby databases in the configuration.

– Online table move: nonpartitioned tables can be moved as an online operation without blocking any concurrent DML operations. A table move operation now also supports automatic index maintenance as part of the move.

– Automatic deployment of Oracle Data Guard: deployment is automatic for Oracle Data Guard physical replication between shards with Oracle Data Guard fast-start failover (automatic database failover): automatic database failover provides high availability for server, database, network, and site outages.

– Automatically set user tablespaces to read-only during upgrade: the new -T option for the parallel upgrade utility (catctl.pl) can be used to automatically set user tablespaces to read-only during an upgrade, and then back to read/write after the upgrade.

– The Oracle Trace File Analyzer (TFA) collector provides the option to automatically collect diagnostic information when TFA detects an incident.

– Oracle Data Guard support for Oracle Diagnostics Pack: this enables you to capture the performance data to the Automatic Workload Repository (AWR) for an Active Data Guard standby database and to run Automatic Database Diagnostic Monitor (ADDM) analysis on the AWR data.

– Automatic Workload Repository (AWR) support for pluggable databases: the AWR can be used in a PDB. This enables the capture and storage of performance data in the SYSAUX tablespace of the PDB.

– The new ENABLE_AUTOMATIC_MAINTENANCE_PDB initialization parameter can be used to enable or disable the running of automated maintenance tasks for all the pluggable databases (PDBs) in a multitenant container database (CDB) or for individual PDBs in a CDB.

– Automatic Data Optimization Support for In-Memory Column Store: Automatic Data Optimization (ADO) enables the automation of Information Lifecycle Management (ILM) tasks. The automated capability of ADO depends on the Heat Map feature that tracks access at the row level (aggregated to block-level statistics) and at the segment level.

– Automatic Provisioning of Kerberos Keytab for Oracle Databases: the new okcreate utility automates the registering of an Oracle database as a Kerberos service principal, creating a keytab for it, and securely copying the keytab to the database for use in Kerberos authentication.

– Role-Based Conditional Auditing: auditing for new users with the DBA role would begin automatically when they are granted the role.

– Automatic Locking of Inactive User Accounts: within a user profile, the new INACTIVE_ACCOUNT_TIME parameter controls the maximum time that an account can remain unused. The account is automatically locked if a log in does not occur in the specified number of days.

12_2_automation

Database Magic with Oracle Database 12c

In Database options, Oracle database on November 28, 2016 at 09:40

“Science is magic that works” Kurt Vonnegut

magic

Have a look at the following series of commands.

A query on the SALES table takes normally more than 2 minutes but setting the database_performance parameter to SUPER_FAST makes it … as expected super fast: less than 1 second. Setting the database_performance parameter to SUPER_SLOW makes the query hang. Again “as expected”.

sqltf

So, how come all this is possible?

Before blogging it, I showed this “magical trick” to 100s of people: at Oracle OpenWorld, at some Oracle User Group events and conferences and to many DBAs. Here is the explanation.

Behind the curtains, I am using the Oracle SQL Translation Framework.

exec dbms_sql_translator.create_profile('OOW');

select object_name, object_type from dba_objects where object_name like 'OOW';

exec dbms_sql_translator.register_sql_translation('OOW',
'SELECT max(price) most_expensive_order from sales',
'SELECT max(price) most_expensive_order from julian.sales')
/

exec dbms_sql_translator.register_sql_translation('OOW',
'alter session set database_performance="SUPER_FAST"',
'alter session set inmemory_query="ENABLE"')
/

exec dbms_sql_translator.register_sql_translation('OOW',
'alter session set database_performance="RATHER_SLOW"',
'alter session set inmemory_query="DISABLE"')
/

exec dbms_sql_translator.register_sql_translation('OOW',
'alter session set database_performance="SUPER_SLOW"',
'begin uups; end;')
/

The procedure uups is just helping us mimic a never ending loop:

create or replace procedure uups
as  
 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;
/

Then, once connected to the database as SYS you run the following commands:

set timing on

alter session set sql_translation_profile = OOW
/

alter session set events = '10601 trace name context forever, level 32'
/
 

The last question now is probably what changes the performance? But this should be clear from the usage of the inmemory_query parameter. I am simply keeping the SALES table in memory. So yes, the in-memory option can be 137 times faster! 133.22/0.97 ~ 137

Here is something more to read if you find the topic interesting:

SQL Translation Framework in Oracle Database 12c by Okcan Yasin Saygili
SQL Translation Framework by Kerry Osborne
Oracle 12c Security – SQL Translation and Last Logins by Pete Finnigan

Oracle Bare Metal Cloud in Action

In Cloud, DBA, IaaS on October 15, 2016 at 08:56

Bare Metal Cloud means non-virtualized physical compute servers, i.e., no hypervisor running to create virtual machines!

capturebmc

The aim of this blog post is to show you how simple it is to provision the Oracle BMC. But first here are few links on the subject that you may find useful:

What’s Inside Oracle’s AWS-Killing Bare Metal Cloud by Craig Matsumoto
Oracle’s infrastructure business focuses on bare metal to go after AWS by Blair Hanley Frank
Does Oracle have a shot in the public cloud vs. Amazon and Microsoft? by Brandon Butler
Virtual or Bare Metal Dedicated Cloud: Which Option is Right for You? by Ashar Baig
Oracle IaaS Generation 2 by Marcel van den Berg
– Documentation: Oracle Bare Metal Cloud Services
– The Bare Metal Cloud Service on oracle.com

This is how you create a Bare Metal Cloud machine in the Oracle Infrastructure Cloud (took me less than 15 minutes to connect as root from scratch):

1. First page:

capture1

2. Create the VCN (Virtual Cloud Network):

capture2a

3. Launch the instance:

capture3a

4. In progress (provisioning):

capture4a

5. Created (36 CPUs):

capture5a

6. Details:

capture6a

7. Install MongoDB:

mongodb

The Oracle Bare Metal Cloud is based on totally new architectural concepts, modern hardware, it is easy to provision and use and most importantly reliable and secure. In addition, Oracle BMC is supposed to be 11 times faster and 20 percent cheaper than the fastest solution offered by the competition.