Oracle Manual Standby – Applying Log

If you are running Oracle EE, there are many books on Data Guard. However, for Oracle SE and manual standby, have fun searching.

I wanted to get a better understanding of registering logfile.

Depending on how the standby environment is monitored, not registering logfile may yield incorrect results when checking standby lag.

When logfile is not registered, v$archived_log is not updated.

STANDBY: check status

[oracle@armor:hawk:/home/oracle]
$ sqlplus / as sysdba @stby.sql

SQL*Plus: Release 11. (more...)

The Database Protection Series- Vulnerability Assessments

This is the fourth article of a series that focuses on database security.  In my introduction, I provide an overview of the database protection process and what is to be discussed in future installments.   In last month’s article, we finished our discussion of the most common threats and vulnerabilities.  In this latest installment, we’ll review the database vulnerability assessment process.  We’ll begin by learning how to perform an initial database vulnerability (more...)

Learning for free – UK User Group Meetings Coming Up

There are a few user group meetings coming up in the UK over the next week or two.

Note, you need to register to attend any of these, follow the links.

First is Club Oracle London, which are evening sessions held in London with 3 talks plus free beer and pizza. The next meeting is Thursday Feb 26th at 103a Oxford Street, kicking off at 18:30. You can register for this free event here and (more...)

12c Parallel Execution New Features: Hybrid Hash Distribution – Part 2

In the second part of this post (go to part 1) I want to focus on the hybrid distribution for skewed join expressions.

2. Hybrid Distribution For Skewed Join Expressions

The HYBRID HASH distribution allows to some degree addressing data distribution skew in case of HASH distributions, which I've described in detail already in the past. A summary post that links to all other relevant articles regarding Parallel Execution Skew can be found here (more...)

Oracle "read by other session" Wait Event

When a session needs to read data from disk into the Oracle buffer cache, it may have to wait for another session to finish doing the same thing. Time spent doing this is recorded as a read by other session event. I decided to reproduce this in an Oracle 12 database. First, in session 1, in red, I set up a user called Fred to create a table:

SQL> conn / as sysdba
Connected.

How Many Values Can You Have in an IN List?

I have often wondered how many values you could have following an IN and I have just found out. I loaded some new data into a name and address table in an Oracle 11 database over the weekend. On Monday, a user sent me an Excel spreadsheet containing a list of almost 18000 meter point references to search for in the table. I exported them into a file, copied it to the server and used (more...)

Moving InnoDB Table Between Servers

This is a small article telling how to move a innoDB table from one MySQL server to another.

Moving MyISAM table from one server to another is very straight forward process. MyISAM table generates 3 files at file system level.

  • .frm file contains table structure/definition
  • .MYD file contains table data
  • .MYI file contains index data

We can simply copy these 3 files to another MySQL server installation under desired database and it will show up (more...)

opatch lsinventory gives “line 384: [: =: unary operator expected”

I noticed the error message when running lsinventory against a  12.1.0.2 Oracle_Home. As the command worked I didn’t think anymore of it until on the same server against an 11.2.0.1 home I got the same error message.

opatch lsinventory
 tr: extra operand `y'
 Try `tr --help' for more information.
 /app/oracle/product/11.2.0.1/dbhome_1/OPatch/opatch: line 384: [: =: unary operator expected

There is a Mos note which provides a solution (more...)

Test MySQL on AWS quickly

Using sysbench to performance test AWS RDS MySQL hardware is an easy three step  operation. 

Sysbench creates synthetic tests and they are done on a 1 mil row 'sbtest' table that sysbench creates in the MySQL database you indicate. The test doesn't intrude with your database schema, and it doesn't use your data, so it is quite safe. The test is an OLTP test trying to simulate event operations in the database as it (more...)

255 columns

Here’s a quick note, written and some strange time in (my) morning in Hpng Kong airport as I wait for my next flight – all spelling, grammar, and factual errors will be attributed to jet-lag or something.

And a happy new year to my Chinese readers.

You all know that having more than 255 columns in a table is a Bad Thing ™ – and surprisingly you don’t even have to get to 255 to (more...)

Latest updates to PerfSheet4, a tool for Oracle AWR data mining and visualization

Topic: This post is about the latest updates to PerfSheet4 v3.7 (February 2015). PerfSheet4 is a tool aimed at DBAs and Oracle performance analysts. It provides a simplified interface to extract and visualize AWR time series data using Excel pivot charts.


Why PerfSheet4: PerfSheet4 is aimed at querying and displaying time-series data from AWR repository tables. This is very a rich source of information to analyze database workloads and trends in the context of performance analysis or (more...)

12c Migrate Database from non-ASM to ASM using online relocation of data files

There are many articles explaining how to migrate database from file system into ASM. You could use RMAN to create an image copy of the database into ASM and switch to the database copy, restore database from backup sets into ASM or create duplicate database.

All of these RMAN features are available on Oracle versions before 12c.

In this post I will use slightly different approach - using online relocation of data files into ASM.

(more...)

Logical I/O

Except a few special cases, optimizing SQL is about minimizing I/O. And by “I/O” we normally mean “physical I/O”, because everybody knows that logical I/O (LIO) is much, much faster. But how much faster exactly? For a long time, this question has been bothering me. It looks like there has been little research in this area. Basically the only thorough investigation I managed to find on the subject was one by Cary Millsap and co-authors. (more...)

Post Agent Upgrade Tasks

The typical EM agent upgrade leaves the old Oracle Homes in place on your hosts.  That includes the old home for the agent binaries and also the homes for each upgraded plug-in.

You can clean all of them up through the same Agent Upgrade Console that you used to push new agent out.  Select the same Upgrade Agents from the drop-down.

post_upgrade01

Select the Post Agent Upgrade Tasks tab to expose the Clean-up Agents functionality

post_upgrade02

Search for (more...)

Understanding Flash: Fabrication, Shrinkage and the Next Big Thing

Semiconductor Fabrication Plant (picture courtesy of SemiWiki.com)

Semiconductor Fabrication Plant (picture courtesy of SemiWiki.com)

Before I draw this series on Understanding Flash to a close, I wanted to briefly touch on the subject of manufacturing. Don’t worry, I’ve taken heed of the kind feedback I had after my floating gate transistor blog post (“Please stop talking about electrons!“) and will instead focus on the commercial aspects, because ultimately they affect the price you will be paying for your flash-based (more...)

Travelling in a Wheelchair

It’s that time of the year again, when one of the best grass roots Oracle user group conferences takes place, the Rocky Mountain Oracle User Group Training Days in Denver Colorado. I’ve been privileged enough to present at this conference several times in the past, and always have an absolute blast at it, so I try to submit a few papers each year. This year, I had three papers accepted. Two of them were designed (more...)

Latest updates to PerfSheet4, a tool for Oracle AWR data mining and visualization

Topic: This post is about the latest updates to PerfSheet4 v3.7 (February 2015). PerfSheet4 is a tool aimed at DBAs and Oracle performance analysts. It provides a simplified interface to extract and visualize AWR time series data using Excel pivot charts.

 

A new index on a small table makes a big difference

A few weeks back on the weekend just before I went on call we got a complaint about slowness on an important set of reports.  I worried that the slowness of these reports would continue during my support week so I tried to figure out why they were slow.  I reviewed an AWR report for the 24 hours when the reports were running and found a simple query against a tiny table at (more...)

12c Parallel Execution New Features: Hybrid Hash Distribution – Part 1

In this blog post I want to cover some aspects of the the new HYBRID HASH adaptive distribution method that I haven't covered yet in my other posts.As far as I know it serves two purposes for parallel HASH and MERGE JOINs, adaptive broadcast distribution and hybrid distribution for skewed join expressions. In the first part of this post I want to focus on former one.

1. Adaptive Broadcast Distribution For Small Left Row (more...)

Using rsync to clone Goldengate installation

You may be thinking, why clone Goldengate and why now just download it?
The exact version and patch level might not be available.
Too lazy to search for it and many other reasons you can come up with.

Why use rsync and not tar – scp? I wanted to refresh memory of using rsync.

Commands used:

local source /u01/app/ggs01/ and remote target arrow:/u01/app/ggs03/

rsync -avh --delete --dry-run --exclude 'dirdatold' /u01/app/ggs01/ arrow:/u01/app/ggs03/
rsync -avh --delete --exclude  (more...)