ARCHIVE_LAG_TARGET on Oracle 11.2.0.4

I read about this parameter, which is supposed to force a redo log switch after a certain number of seconds. I decided to try it out in an Oracle 11.2.0.4 database. This is the record from the alert log showing me setting the parameter:

Wed May 25 18:00:39 2016
ALTER SYSTEM SET archive_lag_target=1800 SCOPE=BOTH;
Wed May 25 18:00:39 2016


I expected this to force a redo log switch every 30 minutes but (more...)

User Defined Target Properties

We can create our own target property classifications using EM CLI.  In this example we’ll create a new property named “Product Type”.  In my shop we’ll use that property to identify Oracle RAC and single instance databases, but also noSQL targets that we’re adding to our EM environment.  That custom property will then be used to define Administrative Groups and we’ll map specific Monitoring Templates to each of those groups.

Here’s the syntax for creating (more...)

Quiz Night

Here’s an execution plan from a recent OTN database forum posting:

 
------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT          |                    |     1 |   231 |   160   (6)| 00:00:02 |
|   1 |  UPDATE                   | GS_TABLE           |       |       |            |          |
|*  2 |   HASH JOIN SEMI          |                    |     1 |   231 |   130   (0)| 00:00:02 |
|*  3 |    TABLE  (more...)

Buckle Up, Baby!

So after over two years at Oracle, I’m moving on.  Yes, for those of you who haven’t seen the tweets and the posts, you heard right.

dorothy_appaulled

OK, everyone-  cleansing breath.

I worked with great people and did some awesome things in the community, blogged everything Enterprise Manager and talked over 1/2 the Oracle community into buying and doing projects with Raspberry Pi while I was at it!

Many folks thought I (more...)

Intro to Database Security – Part 2

In part 1 I talked about introduction to the security world and important terms. In this post I will introduce database security, what it means and what we can do about it. Link to the previous post: part 1 – general introduction to security The database is the heart of the organization’s data. People want […]

IPython/Jupyter Notebooks for Oracle

Topic: This post you can find examples of how to use IPython/Jupyter notebooks for running SQL on Oracle

Data Guard “CORRUPTION DETECTED: In redo blocks starting at block…” issues

I’ve been pulling my hair out over this one, so hopefully this post will prove useful to someone else experiencing similar problems with Data Guard traffic. One of our Cloud hosted environments (IaaS) has an Oracle 11.2.0.4 Data Guard (physical standby) setup on Windows.  Recently, the standby database started logging the following errors in it’s alert log: Fri

Read More...

BGOUG Spring Conference 2016 & Twitter Statistics

As I already mentioned in my previous blog post, I attended the BGOUG (Bulgarian Oracle User Group) Spring Conference last week. It was my second time to attend BGOUG conferences, and as usual, it was a great organization. I’m really impressed how Milena Gerova and BGOUG Conference Team can organize such a successful event.

presenting

At the conference, I gave two speeches, one was “Linux Essential Commands for DBAs”, and the other one was “Oracle Enterprise (more...)

plsql datapump and excluding both job types



Today I've spent a few hours dealing with creating an updated version of our streams recreation script. Streams is both wonderful and frustrating at the same time, I have somewhat of a love-hate relationship with it. After a successful day improving the script I thought I'd share one of the most useful aspects i had to amend to get the script to do everything in a nice clean way.

The script at the moment is (more...)

EM13c, Configuration Management and Comparing Targets

How many times have you had maintenance or a release complete and everyone is sure that everything’s been put back the way it should have been, all t’s crossed, all i’s dotted and then you release it to the customers only to find out that NOPE, something was forgotten in the moving parts of technology?  As the Database Administrator, you can do a bit of CYA and not be (more...)

Building a sub $300/month Oracle RAC on AWS – Part II

In the previous post we discussed the network setup required for Oracle RAC. In this post we will explore setting up shared storage  required for Oracle RAC.
Oracle RAC is a shared storage system, every node in the cluster Read/Write to the the same storage system. In enterprise set up this space is  dominated by enterprise storage companies like EMC, NetApp etc. EMC provides block storage for Oracle and NetApp provides Network Attached Storage for (more...)

“It’s … MUTATING!!!!”


“The most important reason for going from one place to another is to see what's in between.” 
― Norton Juster, The Phantom Tollbooth   

The mutating table error (ORA-04091) thrown by Oracle can be a familiar sight throughout a DBA’s career, along with the usual question of ‘Why?’. The error itself is not difficult to understand nor is it hazardous to the table data. Let’s look at why the error is thrown, why it’s (more...)

Merge Precision

This note is about a little detail I hadn’t noticed about the merge command until a question came up on the OTN database forum a few days ago. The question was about the impact of the clustering_factor on the optimizer’s choice of execution plan – but the example supplied in the question displayed an oddity I couldn’t explain. Here’s the code and execution plan as originally supplied:


MERGE INTO gtt_ord t1
    USING X t2 ON  (more...)

Reading Data From Oracle Data Files (without Connecting Database)

I attended the BGOUG Spring Conference last week as a speaker. While in BGOUG Conference, I had a chance to listen Kamil Stawiarski’s session about hacking Oracle. Kamil spoke about potential security issues of high level privileges such as create any index and create directory. At the end of the session, he also showed a tool written by himself. The tool (I do not remember its name right now), reads a datafile, searches for object (more...)

Building a sub $300/month Oracle RAC on AWS – Part I

NOTE: The idea and inspiration of building a cheap Oracle RAC come from Amazon’s documentation (https://aws.amazon.com/articles/7455908317389540)
This setup is best for testing or non-prod as we would not build network and storage resiliency.

This would be a 3 part series where we discuss different components of AWS that are needed to be setup to enable us to install Oracle RAC on AWS. This would involve setting up networking, shared storage and (more...)

Oracle Database Cloud (DBaaS) Performance Consistency

As Oracle ACE Director I got an extended trial license for Oracle's Cloud offerings, in particular the "Database as a Service" offering. As part of the (ongoing) evaluation I try to get an idea how consistent the performance of such an service is, which might be one of the concerns one might have when considering cloud offerings in general.

For my tests I've set up a 11.2.0.4 single instance database using "4 (more...)

Oracle PSU Pain and Predicament

Here is a list of download options for APR2016 (11.2.0.4.160419) PSU.

OJVM PSU (Linux/Unix) 22674697
Combo OJVM + DB PSU 22738777
Combo OJVM + DB SPU 22738732
Combo OJVM + GI PSU 22738793

That’s not enough  and there should be more (sarcasm).

FYI: GI PSU includes DB and DB PSU does not include GI.

There is a thread from MOS – OJVM PSU and RAC: What happened to high availability? and I (more...)

Interesting difference in foreign key locking behavior between heap and index organized tables

I hit this issue by accident, developers wanted to disable inserts to child table so they could perform some one-time maintenance operation, and this maintenance only affected one rown from the parent table (and all it’s children). I started wondering if there is any smaller level impact solution possible than taking a shared read lock on child table.

Database version: 12.1.0.2, but the same behaviour was also present in 11.2.0. (more...)

Installing EM13c on Windows Tips

I just built out my own EM13c environment so I can answer many questions that I won’t be able to ignore just because I no longer work at Oracle.

Alfie2

Tips

Installation of EM13c is different on Windows than Linux or Unix, (no duh!) but it’s good to know a few tips for those that required to use Windows as their hosting or monitoring OS.

win13c1

  1. Ensure you use (more...)

OEM Preferred Connect Strings

You can specify a connection string for database targets in two ways:  fill in the blanks and let OEM create the connection string (my preference) or paste a Preferred Connect String on the bottom of the page.

dedicatedserver_connectstring01

Our default setting in our shop is to use multi-threaded servers. On the rare occasion when we need to change if, we paste the connect string from tnsnames.ora in place and add the SERVER=DEDICATED parameter.

Some things to (more...)