Search

Navigation

Top 60 Oracle Blogs

June 2018

Hacking Profiles

Submitted by Jonathan Lewis on Tue, 2018-06-26 07:25

Saturday’s posting about setting cursor_sharing to force reminded me about one of the critical limitations of SQL Profiles (which is one of those little reason why you shouldn’t be hacking SQL Profiles as a substitute for SQL Plan Baselines). Here’s a demo (taking advantage of some code that I think Kerry Osborne published several years ago) of creating an SQL Profile from the current execution plan of a simple statement – first we create some data and find the sql_id and child_number for a simple query:

DDL for constraints – subtle things

Submitted by connor_mc_d on Mon, 2018-06-25 05:44

The DBMS_METADATA package is very cool. I remember the days of either hand-crafting DDL statements based on queries to the data dictionary, or many a DBA will be familiar with running “imp show=y” or “imp indexfile=…” in order to then laboriously extract the DDL required from the import log file. DBMS_METADATA removed all of those annoyances to give us a simple API to get the true and complete DDL for a database object.

But when extracting DDL from the database using the DBMS_METADATA package, you need to be aware of some subtleties especially if you plan on executing that DDL in the database.

18c: No Active Data Guard required (and detected) when only CDB$ROOT and PDB$SEED are opened in read-only

Submitted by Franck Pachot on Sun, 2018-06-24 19:32

When you clone a PDB on the primary CDB, you want that the same is automatically done on the standby ones. Unfortunately, for this operation, the CDB must be opened read-only. In 12.1 even the PDB needed to be opened read-only (Ludovico Caldara reported this in his blog). This, as far as I know, was fixed in 12.2 where MRP reads the files without the need to have the PDB opened. But another problem comes with online cloning, as reported by Alain Fuhrer in his blog, where the CDB needs to be opened read-only with real-time apply. This again requires the Active Data Guard option, which is then mandatory to use all power from the multitenant option.

Building the Priority 600

Submitted by JonathanGennick on Sun, 2018-06-24 03:24

Following are some photos and thoughts from my unboxing and building of the Priority 600 belt-drive, gearbox-driven bicycle from Priority Bicycles. If you're building the 600, be sure to watch Dave Weiner's Priority 600 Assembly video on YouTube. It's twenty minutes well spent.

Bicycles and Cycling

Cursor_sharing force

Submitted by Jonathan Lewis on Sat, 2018-06-23 11:05

Prompted by a recent ODC (OTN) question I’ve just written up an example of one case where setting the cursor_sharing parameter to force doesn’t work as you might expect. It’s a specific example of what I believe is a theme that can appear in several different circumstances: if your SQL mixes “genuine” bind variable with literals then the literals may not be substituted.

Here’s a simple data set to start with:

Introduction to databases for {Power.Coders} with MySQL

Submitted by Franck Pachot on Fri, 2018-06-22 20:04

OSWatcher, Tracefile Analyzer, and Oracle RAC 12.2

Submitted by martin.bach on Fri, 2018-06-22 15:24

When I started the series about Tracefile Analyzer (TFA) I promised three parts. One for single instance, another one for Oracle Restart and this one is going to be about Real Application Clusters. The previous two parts are published already, this is the final piece.

The environment

I am using a virtualised 2-node cluster named rac122pri with nodes rac122pri1{1,2} based on Oracle Linux 7.4. RAC is patched to 12.2.0.1.180116. I installed a Grid Home and an RDBMS home (Enterprise Edition).

Real Application Clusters

Before starting this discussion it’s worth pointing out that TFA integration in RAC 12.2 works really well. TFA is installed as part of the initial setup of the binaries and documented in the Autonomous Health Framework.

Remote syslog from Linux and Solaris

Submitted by Franck Pachot on Wed, 2018-06-20 15:47

Auditing operations with Oracle Database is very easy. The default configuration, where SYSDBA operations go to ‘audit_file_dest’ (the ‘adump’ directory) and other operations go to the database may be sufficient to log what is done but is definitely not a correct security audit method as both destinations can have their audit trail deleted by the DBA. If you want to secure your environment by auditing the most privileged accounts, you need to send the audit trail to another server.

The death of UTL_FILE

Submitted by connor_mc_d on Wed, 2018-06-20 03:24

In a previous post I covered a technique to improve the performance of UTL_FILE, but concluded the post with a teaser: “you probably don’t need to use UTL_FILE ever again”.

Time for me to back that statement up with some concrete evidence.

UTL_FILE can read and write files. This blog post will cover the writing functionality of UTL_FILE and why I think you probably don’t need UTL_FILE for this. I’ll come back to UTL_FILE to read files in a future post.

When WHEN went faster

Submitted by connor_mc_d on Wed, 2018-06-20 02:20

Yeah…try saying that blog post title 10 times in a row as fast as you can Smile

But since we’re talking about doing things fast, this is just a quick post about a conversation I had a twitter yesterday about the WHEN clause in a trigger.

That is an easy benchmark to whip up – I just need a couple of tables, each with a simple a trigger differing only by their usage of the WHEN clause. Here is my setup: