Search

Top 60 Oracle Blogs

Recent comments

April 2017

DOAG Exaday 2017: a preview

I am quite excited to have been accepted to speak at the upcoming DOAG Exaday taking place June 20 in Frankfurt. It is the third time I have been lucky enough to present at the event, having spoken both in Frankfurt in Hamburg in previous years.

As you can probably imagine by looking at this weblog, I am a great supporter of the Oracle Engineered Systems community. My interest is not limited to Germany: I still remember the many great meetings in London in the context of UKOUG’s Exadays. I am also very lucky to work for @enkitec where we have been sharing real-world experience at E4 for years and will do so again in June this year.

Session killin’ time

As developers, sometimes we set something running that we wish we hadn’t Smile  And naturally, we’d like to be good IT citizens and clean up the mess as quick as we can.  (For most of us, this means – cover our tracks before the phone rings about smoke coming out of the server).  But of course, getting an administrator to hand over the trigger to let you have the ALTER SYSTEM KILL SESSION command is probably unlikely because…well… it’s just a bad bad idea.  So here’s a wrapper which might serve as a starting point for you.  It expose the kill system command to you, but in a restricted set of circumstances.

Sample Usage

By default, we report any session that has a status of active or killed. We’ll see the session details, whether it’s running or blocked, plus the SQL ID etc.

12cR2 RMAN> REPAIR

Do you know the RMAN Recovery advisor? It detects the problems, and then you:

RMAN> list failure;
RMAN> advise failure;
RMAN> repair failure;

You need to have a failure detected. You can run Health Check if it was not detected automatically (see https://blog.dbi-services.com/oracle-12c-rman-list-failure-does-not-show-any-failure-even-if-there-is-one/). In 12.2 you can run the repair directly, by specifying what you want to repair.

Version your Oracle ORDS REST API using Edition Based Redefinition

Updated, May 4 2017: New approach One of the last entries in my blog described how I could quickly publish all
random_ninja functions as rest endpoints
. While I am still waiting for the ORDS team at Oracle to come out with automatic
publish of functions and procedures including input parameters, I wanted to add another layer of functionality to my REST endpoints.
Versioning; And for that we can use the cool Oracle feature called Edition Based Redefinition.

Version your Oracle ORDS REST API using Edition Based Redefinition

Updated, May 4 2017: New approach One of the last entries in my blog described how I could quickly publish all
random_ninja functions as rest endpoints
. While I am still waiting for the ORDS team at Oracle to come out with automatic
publish of functions and procedures including input parameters, I wanted to add another layer of functionality to my REST endpoints.
Versioning; And for that we can use the cool Oracle feature called Edition Based Redefinition.

Data Pump LOGTIME, DUMPFILE, PARFILE, DATA_PUMP_DIR in 12c

Data Pump is a powerful way to save data or metadata, move it, migrate, etc. Here is an example showing few new features in 12cR1 and 12cR2.

New parameters

Here is the result of a diff between 12.1 and 12.2 ‘imp help=y’
CaptureDataPump122

But for this post, I’ll show the parameters that existed in 12.1 but have been enhanced in 12.2

LOGTIME

This is a 12.1 feature. The parameter LOGTIME=ALL displays the system timestamp in front of the messages in at the screen and in the logfile. The default is NONE and you can also set it to STATUS for screen only and LOGFILE for logfile only.

SecureFiles on multi-datafiles tablespaces

When we have a tablespace with multiple datafiles, we are used to seeing the datafiles filled evenly, the extents being allocated in a round-robin fashion. In the old time, we used that to maximize performance, distributing the tables to all disks. Today, we use LVM striping, maximum Inter-Policy, ASM even distribution. And we may even use bigfile tablespaces, so that we don’t care about having multiple datafiles.

But recently, during test phase of migration, I came upon something like this:
SecureFile003

Undo Understood

It’s hard to understand all the ramifications of Oracle’s undo handling, and it’s not hard to find cases where the resulting effects are very confusing. In a recent post on the OTN database forum resulted in one response insisting that the OP was obviously updating a table with frequent commits from one session while querying it from another thereby generating a large number of undo reads in the querying session.

The Chicken Before the Egg of Cloud Migrations

For over a year I’ve been researching cloud migration best practices.  Consistently there was one red flag that trips me that I’m viewing recommended migration paths.  No matter what you read, just about all of them include the following high level steps:

Transportable Tablespace–part 2

I did a little demo of sharing a tablespace between two databases a few days back – you can see the details here or by just scrolling down Smile if you’re on the home page.

To avoid clouding the demonstration I omitted something in the details, but I’ll share that now, because it could be critical depending on how you currently use transportable tablespaces.

Let me do the most basic of examples now, transporting a tablespace from one database to another:

First, we make our tablespace read only, and Datapump export out the metadata