Preventing standby databases opening in Active DataGuard mode + chopt !!!

It is long been a concern of mine that it is relatively easy to open a standby database  inadvertently and then be potentially liable to a licensing cost as usage is shown in the DBA_FEATURE_USAGE_STATISTICS view.

In fact we logged an SR on the subject based on experiences on failing over using DataGuard Broker

On dgmgrl switchover the Oracle restart database ‘start option’ property is not modified to ‘mount’ database even though dgmgrl modifies the (more...)

Metering and Chargeback

In the past few posts, I’ve covered setting up PDBaaS, using the Self Service portal with PDBaaS, setting up Schema as a Service, and using the Self Service Portal with Schema as a Service, all of these using Enterprise Manager Cloud Control 12c release 12.1.0.4. Now I want to move on to an area where […]

Avoid UTL_FILE_DIR Security Weakness – Use Oracle Directories Instead

Integrigy:

The UTL_FILE database package is used to read from and write to operating system directories and files. By default, PUBLIC is granted execute permission on UTL_FILE. Therefore, any database account may read from and write to files in the directories specified in the UTL_FILE_DIR database initialization parameter [...] Security considerations with UTL_FILE can be mitigated by removing all directories from UTL_FILE_DIR and using the Directory functionality instead.


© Eddie Awad's Blog, (more...)

#GoldenGate Bound Recovery

Every once in awhile when I restart an extract, I see entries in the report file that reference “Bounded Recovery”.  What exactly is “Bounded Recovery”?

First, keep in mind that “Bounded Recovery” is only for Oracle databases!

Second, according to the documentation, “Bounded Recovery” is a component of the general extract checkpointing facility.  This component of the extract guarantees an efficient recovery after an extract stops for any reason, no matter how many uncommitted transactions (more...)

Analogy – 2

I suggested a little while ago that thinking about the new in-memory columnar store as a variation on the principle of bitmap indexes was quite a good idea. I’ve had a couple of emails since then asking me to expand on the idea because “it’s wrong” – I will follow that one up as soon as I can, but in the meantime here’s another angle for connecting old technology with new technology:

It is a feature of in-memory column (more...)

RMAN Pet Peeves

Do you validate your backup and what command do you use?

Lately, I have been using restore database validate preview summary to kill 2 birds with 1 stone.

The issue is RMAN will skip validation of archived log backupset when archived log exists.

Does this seem wrong to you?

Please take a look at a test case here

What do you think?


Are You Using BULK COLLECT and FORALL for Bulk Processing Yet?

Steven Feuerstein was dismayed when he found in a PL/SQL procedure a cursor FOR loop that contained an INSERT and an UPDATE statements.

That is a classic anti-pattern, a general pattern of coding that should be avoided. It should be avoided because the inserts and updates are changing the tables on a row-by-row basis, which maximizes the number of context switches (between SQL and PL/SQL) and consequently greatly slows the performance of the code. Fortunately, (more...)

Oracle 12c Database In-Memory option generally available

On the 22nd of july, patchset 12.1.0.2 for Oracle 12c database was made generally available for Linux x86-64 and Solaris.

This patchset includes the eagerly awaited In-memory option which promises an application transparent performance boost by using column-format data storage in memory.

Database In – Memory uses an In-Memory column store (IM column store), which is a new component of the Oracle Database System Global Area (SGA), called the In-Memory Area. Data (more...)

When is a Table Populated into the In-Memory Column Store?

I was playing with in-memory feature of Oracle 12c, and wondered when a table is loaded/populated into in-memory buffer (of course I’m talking about a table which is enabled for in-memory). In “the Oracle Database In-Memory blog”, it says Oracle typically populates the table after it has been accessed for the first time. It’s possible to check V$IM_SEGMENTS to see the memory segments of in-memory tables, so we can easily see when the table is (more...)

How Accurate Are Wearables for Running? Why Not Test It for Real?

How accurate are wearable devices for fitness? And does it matter?

There’s a lot of research out there to suggest there are issues with actual accuracy of what’s being recorded, and variances between different devices and what they measure too. If you use different fitness bands simultaneously you will no doubt have noticed some of this.

I’m a runner, and into tracking and measuring stuff, so I decided to test out my own wearables and (more...)

Enterprise Manager 12c: Failed Jobs

After I added some database targets to my Enterprise Manager 12c, an incident opened for failed jobs on a newly added database. We have some jobs scheduled to run every 5 minutes on that database, and of course we have some error-handling and alerting mechanism which will notify us if any of them fails, so there shouldn’t be any failed job. I checked run logs of all the scheduler jobs to see if there are (more...)

Using the Self Service Portal with Schema as a Service in EM 12.1.0.4

In a previous blog post, I covered setting up Schema as a Service for schema level consolidation. In this post I’m going to cover how to use the Self Service Portal with Schema as a Service in EM 12.1.0.4. Just as it was in my posting on using the Self Service Portal with DBaaS, our […]

Plan Instability

There seems to me to be a relatively simple choice.

Either you except that the Oracle Optimizer has a wealth of complicated strategies and, in this complex effort to get the best executions it can, will inevitably either get it wrong sometimes (or take some extra executions to realise it’s wrong).

Or you stick your head in the sand and raise a bug for every unexpected poor execution or plan flip.

But let’s say that (more...)

BUCKET_WIDTH: Calculating the size of the bucket

Some time ago I had some blog posts introducing some of the basic Statistical function available in Oracle. Here are the links to these.

Define Your Own Role for Database Target Access in EM12c

Scenario

  1. Enterprise Manager 12c (EM) installed and agents rolled out to database servers
  2. Access to EM offered to development teams with the primary purpose of allowing them to investigate application related database performance issues

Documentation

The EM documentation covers a selection of privileges you might want to grant to users in database targets in order to allow them to be used for accessing EM functionality. The privileges mentioned are:

  1. SELECT ANY DICTIONARY
  2. CREATE SESSION
  3. EXECUTE on (more...)

Parallel Plans

I’ve popped this note to the top of the stack because I’ve added an index to Randolf Geist’s series on parallel execution skew, and included a reference his recent update to the XPLAN_ASH utility.

This is the directory for a short series I wrote discussing how to interpret parallel execution plans in newer versions of Oracle.

Oracle 12.1.0.2 ASM Filter Driver: First Impressions

This is a very quick post, because I’m about to log off and take an extended summer holiday (or vacation as my crazy American friends call it… but then they call football  “soccer” too). Before I go, I wanted to document my initial findings with the new ASM Filter Driver feature introduced in this week’s 12.1.o.2 patchset.

Currently a Linux-only feature, the ASM Filter Driver (or AFD) is a replacement for ASMLib (more...)

Database upgrade using pluggable databases and how catctl.pl fails if you have custom login.sql

I started to write this blog post on how 12c gave us new multitenant architecture (pluggable databases) and with that also a new possibility to do database upgrades. But during writing I discovered how catctl.pl can fail the upgrade if you have modified the SQL plus prompt with custom login.sql.

I'm starting with these already created databases:

SIDHomeDescription
cdb1/u01/app/oracle/product/12.1.0.1/dbThe initial container with version 12.1.0. (more...)

Oracle’s In-Memory Database: The True Cost Of Licensing

At last, the long-awaited release of Oracle Database 12.1.0.2 has arrived, including the highly anticipated In-Memory Database Option. I have had the pleasure of being involved in the early beta stages of this feature and I have say: I'm impressed. This is truly game changing technology, and has been extremely well designed and implemented by […]

How to find out session info about session that comes from remote database through dblink

It is well known thing and you can even find it on MOS, but I have a little more simple script for it, so I want to show little example.

First of all we need to start script on local database:

SQL>                                                                                                                                                                   
SQL> @transactions/global.sql
Enter filters(empty for any)...
Sid           :
Globalid mask :
Remote_db mask:

 INST_ID  SID    SERIAL# USERNAME REMOTE_DB REMOTE_DBID TRANS_ID         DIRECTION   GLOBALID                                           EVENT                      
-------- ----  (more...)