Ignoring Hints

One of the small changes (and, potentially big but temporary, threats) in 18.3 is the status of the “ignore hints” parameter. It ceases to be a hidden (underscore) parameter so you can now officially set parameter optimizer_ignore_hints to true in the parameter file, or at the system level, or at the session level. The threat, of course, it that some of your code may use the hidden version of the parameter (perhaps in an (more...)

API Security

I’ve started to subscribe to the APISecurity.io news letter. The news letter includes the analysis of recent API based security breaches along with other useful API related news. Some of the details of the breaches make for interesting reading and provide some good examples of what not to do. It is rather surprising how regularly the lack of the application of good practises is, including:

  • Checking the payload is valid to the definition,
  • Checking (more...)

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...)

Timestamp Oddity

[Editorial note: this is something I started writing in 2013, managed to complete in 2017, and still failed to publish. It should have been a follow-on to another posting on the oddities of timestamp manipulation.]

Just as national language support used to be, timestamps and time-related columns are still a bit of a puzzle to the Oracle world – so much so that OEM could cripple a system if it was allowed to do (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...)

Video : Multitenant : Refreshable PDB Switchover in Oracle Database 18c Onward

Today’s video is a run through the Refreshable PDB Switchover feature introduced in Oracle 18c.

I also have a Multitenant YouTube playlist.

If you prefer reading over watching a video, you can find all the information and more here.

The star of today’s video is Kamran Agayev A. When he’s not working with Oracle technology and helping people to pass their OCM (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...)