Online Redo Log Switch Frequency Map

A query I find myself often running is the online redo log switch frequency map query, which queries the v$log_history/gv$log_history (for cluster databases) view and show the historical log switch frequency.

Why you might ask? Well it’s important to see how frequent log switches are occurring as Oracle’s rule of thumb is to not switch more then 3 logs per hour (20 minutes of redo) at peak DML activity to prevent excessive checkpoints.  The (more...)

RMAN Incremental Updating Backup & Demo Part 1

This blog post is part of the “RMAN Back to Basics” series, which can be found here.

Incremental Updating Backup

An incremental updating backup is a level 0 incremental backup which is recovered using level 1 incremental backups.  This avoids the overhead of taking level 0 incremental backups, for example in a backup strategy where a level 0 is taken every Sunday and all other days level 1.

An simple example would be, an (more...)

VT-x is not available (VERR_VMX_NO_VMX) on VirtualBox after Windows 10 Update

So in the mist of writing my “RMAN Back to Basics” series, I got the following error when trying to start my Virtual Machine in Oracle’s VirtualBox:
VirtualBox_VERR_VMX_NO_VMX

“Failed to open a session for the virtual machine dc1sbxdb001

VT-x is not available (VERR_VMX_NO_VMX).
Result Code: E_FAIL (0x80004005)
Component: ConsoleWrap
Interface: IConsole {872da645-4a9b-1727-bee2-5585105b9eed}”

I was like, “How’s this possible?  I only just used my Virtual Machines yesterday!“.

Then I remembered Windows 10 (more...)

Workarounds for JPPD with view and table(kokbf$), xmltable or json_table functions

You may know that table() (kokbf$ collection functions), xmltable and json_table functions block Join-Predicate PushDown(JPPD).

Simple example:

DDL

create table xtest(a, b, c) as
select mod(level,1000),level,rpad('x',100,'x')
from dual
connect by level<=1e4
/
create index itest on xtest(a)
/
create or replace view vtest as
select a,count(b) cnt
from xtest
group by a
/
call dbms_stats.gather_table_stats(user,'xtest');
/

[collapse]

select distinct v.* 
from table(sys.odcinumberlist(1,2,3)) c, vtest v
where v.a = c.column_value;

Plan  (more...)

Integrating API Management with the rest of your Development Pipeline

Oracle API Management keeps API policy configuration and management internalized for a number of reasons including security (after all you don’t want your security rules for APIs out in the open).  The Platform does provide simple versioning.  But you need to able to link the policies to the back end implementations – so the policy configuration is aligned to what is implemented. For example you don’t want the policy to accept parameters that (more...)

RMAN Incremental Differential vs Cumulative & Demo

This blog post is part of the “RMAN Back to Basics” series, which can be found here.

Differential Incremental Backups (default)

A differential incremental backup, backs up all the blocks that have changed after the most recent incremental backup, which can be either level 1 or 0.

RMAN determines which is the most recent level 1 backup and then backs up all the blocks changed after that backup, if no level 1 then all the (more...)

RMAN Incremental with Block Change Tracking & Demo

This blog post is part of the “RMAN Back to Basics” series, which can be found here.

Block Change Tracking

Block change tracking (BCT) improves incremental backup performance by recording the changed blocks in each datafile in the block change tracking file, thus avoiding the need to scan every block in the datafile for changes.  Only applicable for level 1 backups.  Block change tracking file is 1/30,000 the size of the data blocks being tracked, (more...)

Oracle 19c for AIX and HP-UX available

After Oracle released the Oracle Database 19c (19.3) for Linux and Solaris, now you can download as well the Oracle Database 19c (19.3) for IBM AIX and HP-UX ia64.

The software can be found under https://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html



Happy download :-)


v$sql_hint.target_level

Today I wanted to give a link to the description of v$sql_hint.target_level to show that no_parallel can be specified for statement or object, and though it’s pretty obvious, but surprisingly I haven’t found any articles or posts about it, so this short post describes it.
v$sql_hint.target_level is a bitset, where
1st bit set to 1 means that the hint can be specified on statement level,
2nd – on query block level,
3rd (more...)

RMAN Incremental & Demo Part 2 (Level 1)

This blog post is part of the “RMAN Back to Basics” series, which can be found here.

Incremental Backup

An incremental backup only backup up those data blocks that have changed since the last backup.

Types of Incremental Backups

There are 2 types of Incremental Backups:

  1. Level 0 are a base for subsequent backups.  Copies all blocks containing data similar to a full backup, with the only difference that full backups are never included (more...)

Transforming Missing Data using Oracle Data Mining

In a previous post I showed how you can normalize data using the in-database machine learning feature using the DBMS_DATA_MINING.TRANSFORM function.  This same function can be used to perform many more data transformations with standardized routines. When it comes to missing data, where you have some case records where the value for an attribute is missing you have a number of options open to you. The first is to evaluate the degree of (more...)

RMAN Incremental & Demo Part 1 (Level 0)

This blog post is part of the “RMAN Back to Basics” series, which can be found here.

Incremental Backup

An incremental backup only backup up those data blocks that have changed since the last backup.

Types of Incremental Backups

There are 2 types of Incremental Backups:

  1. Level 0 are a base for subsequent backups.  Copies all blocks containing data similar to a full backup, with the only difference that full backups are never included (more...)

Oracle JDeveloper: Is this IDE dead? Are there new releases?

Some weeks ago i read the Java Magazine (http://www.javamagazine.mozaicreader.com)

They report from a survay taken place in 2018 about Java. One of the questions was which application server do you use in production:

This was not really amazing.
But the question about the IDE gave a really surprising result:



More developers use IntelliJ than Eclipse?
Not really surprising was the usage of Oracle's JDeveloper: Only 1% of the developers (more...)

RMAN Image Copy & Demo

This blog post is part of the “RMAN Back to Basics” series, which can be found here.

Image Copy

An Image copy backup are exact copies of the datafiles including the free space.  They are not stored in RMAN backup pieces but as actual datafiles, therefore are a bit-for-bit copy.

Image Copy Demo

We take an image copy backup using my script 4_image_copy.sh:

[oracle@dc1sbxdb001 demo]$ ./4_image_copy.sh 
-----------------------
Step 1: Set environment
 (more...)

Find files with errors

My students wanted a quick solution on how to find the log files that contain errors. That’s a simple line of code in Linux if you want any Oracle errors that start with ORA-:

find . -type f | xargs grep -i ora\-

It takes only a moment more to look for errors starting with ORA- or PLS-, like:

find . -type f | xargs grep -i -e ora\- -e pls\-

The latter might return (more...)

Connecting Go Lang to Oracle Database

It seems like more and more people are using Go. With that comes the need to  access a database or databases. This blog will show you how to get connected to an Oracle Database and to perform some basic operations using Go.

The first thing you need is to have Go installed. There are a couple of options for you. The first is go download from the Go Lang website, or if you are (more...)

Using the Secure External Password store with sqlcl

Sometimes it is necessary to invoke a SQL script in bash or otherwise in an unattended way. SQLcl has become my tool of choice because it’s really lightweight and can do a lot. If you haven’t worked with it yet, you really should give it a go.

So how does one go about invoking SQL scripts from the command line these days? There’s an age-old problem with unattended execution: how do you authenticate against the (more...)

API Platform – Plans & Subscriptions

When it comes to Plans and Subscriptions on the Oracle API Platform we have a very flexible set of relationships. When it comes to checking the relationships to ensure a configuration is correct and that the impact of changing a plan or subscription is clear.  I end up having to draw a little diagram, which always leaves me second guessing myself about which way the linkages are. So I created a quick aide memoir, (more...)

When views causes havoc

Views are great. They simplify design, makes code look more elegant and hides complexity. They also enables reuse by putting complex code in just one place instead of in every accessing piece of code.

But…

There once was a large project that has been churning away for a long time. The performance of having more than one user on the system was horrendous. But it was chalked up to misconfiguration.

I am asked to spend (more...)

Exadata and WebLogic CVE-2019-2725

I can still remember the surprise when Frits Hoogland pointed out that Oracle had replaced the old OC4J instance on Exadata storage servers with a WebLogic service. I wasn’t quite sure what to make of this, but it’s become apparent as things move cloudward that it would enable an entire set of REST operations for Exadata on both storage and compute tiers. This becomes interesting due to the recent 0-day vulnerability for WebLogic, CVE-2019-2725. According (more...)