rlwrap with #GoldenGate GGSCI

Since I published posts on how to retrieve command history within GGSCI using the Oracle provided commands of “history” and “fc”, I was pinged a couple of times by some friends asking about “rlwrap” usage with GoldenGate. The answer is a simple yes, rlwrap can be used with Oracle GoldenGate.

What exactly is “rlwrap”?

According to the readme file at http://utopia.knoware.nl/~hlub/uck/rlwrap/#rlwrap, rlwrap is a ‘read-one wrapper’. (more...)

Edit #GoldenGate commands from GGSCI

Ever get tired of typing the same command over and over again in Oracle GoldenGate GGSCI? Well, Oracle has provided a history recall function into GGSCI, I talked about his in an earlier post. This post on history recall can be found here.

In this post, lets take the command recall and how these commands can be edited. In Oracle GoldenGate 12c, Oracle has provided an GGSCI command called “FC”. The “FC” command is used (more...)

To TRANDATA or To SCHEMATRANDATA? … That is the #GoldenGate questions of the day!

If you are familiar with using Oracle GoldenGate, you know that on the source side of the equation you have to enable supplemental logging and sometimes force logging on the database. I traditionally do both just to make sure that I capture as much as I can into the redo stream from the transactions on the database. For Oracle GoldenGate purposes, this is not the only thing you need to turn on to ensure all (more...)

Oracle 12c: Indexing JSON in the Database Part II (Find A Little Wood)

In Part I, we looked at how you can now store JSON documents within the Oracle 12c Database. For efficient accesses to JSON documents stored in the Oracle database, we can either create a function-based index based on the JSON_VALUE function or on JSON .dot notation. These indexes are useful for indexing specific JSON attributes, […]

Oracle 12c: Indexing JSON In The Database Part I (Lazarus)

One of the very cool new features introduced in Oracle Database 12c Rel 1 is the ability to store JavaScript Object Notation (JSON) documents within the database. Unlike XML which has its own data type, JSON data can be stored as VARCHAR2, CLOB or BLOB data types, but with a JSON check constraint to ensure the […]

How To Configure Exadata Database Machine in Enterprise Manager Cloud Control 13c (OEM13c)

I have followed the steps in Oracle Documentation link: https://docs.oracle.com/cd/E63000_01/EMXIG/ch2_deployment.htm#EMXIG215 to configure Exadata Database Machine in OEM13c. If you want to configure your Exadata in OEM13c you have to follow the above mentioned link. In this post I will share the mandatory steps for configuration, and some of the issues which I faced while configuring the Exadata…

Outer Join with OR and Lateral View Decorrelation

Use of ANSI SQL is a personal thing.

Historically I have not been a fan apart from where it makes things easier/possible.

This reticence was mainly due to optimizer bugs and limitations in the earlier days.

Recently I have been using it much more because I find that the developers I interact with prefer it / understand it better.

You might/should be aware that Oracle will rewrite ANSI SQL to an Oracle syntax representation, this (more...)

How To Avoid Exadata Cell Node Reboot While Modifying NTP Service

Recently, we had a hardware failure on our NTP server which caused its crash. Due to this, the ntpd daemon on compute nodes and cell nodes went offline. After fixing the hardware issue on NTP server, starting ntpd service using "service ntpd start" on compute nodes was successful but when I started the ntp service…

Storing Date Values As Numbers (The Numbers)

In my last couple of posts, I’ve been discussing how storing date data in a character based column is a really really bad idea. In a follow-up question, I was asked if storing dates in NUMBER format was a better option. The answer is that it’s probably an improvement from storing dates as strings but it’s […]

EM Express Login – Get Flash

I have 12c setup on OEL 6.7 on my VM and while trying to access EM express, I was receiving the error

em_express_error

Just for the notes, a user can log into EM Express using URL

https://<hostname>:<port>/em

and port details can be found using

lsnrctl status | grep HTTP
or  
select dbms_xdb_config.getHttpsPort() from dual;

Clicking on the “Get Flash” takes you to abode flash page from where you can download the rpm for flash.

 (more...)

Storing Date Values As Characters Part II (A Better Future)

In the previous post, I discussed how storing date values within a character data type is a really really bad idea and illustrated how the CBO can easily get its costings totally wrong as a result. A function-based date index helped the CBO get the correct costings and protect the integrity of the date data. During […]

Fixed Table x$ktfbue has not statistics

While playing around 12.1.0.2 db, I ran my usual script to check tablespace usage details and it took ~59 secs (Elapsed: 00:00:59.02), which was high.
Ran the sql using “gather_plan_statistics” hint and below lines in the execution plan were of interest –

|  34 |      HASH GROUP BY                     |                    |      1 |      6 |      3 |00:00:58.68 |     129K|  54801 |  1160K|  1160K|  758K (0)|
|  35 |       VIEW                             | DBA_FREE_SPACE      (more...)

INSERT ALL caveat

Why you might want to think twice about using INSERT ALL.

One of those things I knew and then forgot.

So, let’s say you’ve got three tables or a partitioned table or something like that.

Let’s use regional tables for simplicity.

drop table t1_r1;
drop table t1_r2;
drop table t1_r3;

create table t1_r1
(col1 varchar2(2) not null
,col2 number not null
,check( col1 in ('R1')));

create table t1_r2
(col1 varchar2(2) not null
,col2 number not  (more...)

Storing Date Values As Characters (What’s Really Happening)

For something that’s generally considered an extremely bad idea, I’ve lost count of the number of times I’ve come across applications that insist on storing date values as characters within the database. We’ve all seen them … I recently got called in to assist a customer who was having issues with a POC in relation […]

Oracle ASMLib

This blog post is more of a note for myself on configuring ASMLib.

ASMLib is an optional utility that can be used on Linux systems to manage Oracle ASM devices.

ASMLib consists of the following components:

An open source (GPL) kernel module package: kmod-oracleasm
An open source (GPL) utilities package: oracleasm-support
A closed source (proprietary) library package: oracleasmlib

On my Oracle VMBox, I performed the below steps –

1. Check the installed packages related to (more...)

12cR1 RAC Installation on OEL7

To build Oracle Clusterware Database at Home, I believe , RAC ATTACK is the best place to learn. Its is a free curriculum and platform for hands-on learning labs related to Oracle RAC. While reviewing the article, I thought to perform 12cR1 RAC installation on OEL 7.2.

Attached is the document :- 12c_RAC_on_OEL7

The attached article is inspired by

RAC ATTACK :- https://en.wikibooks.org/wiki/RAC_Attack_-_Oracle_Cluster_Database_at_Home/RAC_Attack_12c

Tim Hall’s article :- https://oracle-base.com/articles/12c/oracle-db-12cr1-rac-installation-on-oracle-linux-7-using-virtualbox 

Deploying Oracle RAC (more...)

#GoldenGate #Cloud Service (#GGCS) … what to expect?

As I sit here working on performing some GoldenGate migrations to AWS for a client, I’ve been thinking about the glimpse of GoldenGate Cloud Service (GGCS) that was provided to me earlier this week. That glimpse has helped me define what and how GGCS is going to work within the Oracle Cloud space. Ever since this service was announced back at Oracle Open World 2015, I’ve been wanting to get my hands on this cloud (more...)

Side-effect of ALTER SESSION SET CURRENT_SCHEMA

For most of our database set-ups we use a different TEMP space for application users than for end-user/support/developer/reporting usage.
The intention is to minimise the potential impact of a rogue ad-hoc query on the main applicaiton.

However, turns out this is ineffective IF you use:

ALTER SESSION SET CURRENT_SCHEMA = main_app_schema;

This is documented but I wasn’t previously aware.
No surprise that this learning opportunity was presented as a direct result of a rogue query (more...)

Dead Connection Detection in Oracle Database 12c

In my earlier post I discussed what Dead Connection Detection is and why you should use it – read more here Oracle TNS-12535 and Dead Connection Detection

The pre-12c implementation of DCD used TNS packages to “ping” the client and relied on the underlying TCP stack which sometimes may take longer. Now in 12c this has changed and DCD probes are implemented by TCP Stack. The DCD probes will now use the TCP KEEPALIVE socket (more...)

Veridata and boot.properties file

I’m spending some time building out an Oracle WebLogic/Veridata system at home. This is mostly for testing and general understanding purposes. One of the things I’ve noticed with the WebLogic server requires the username and password before the WebLogic server will start. After providing the name and password, the terminal window has to stay open because, closing it will kill the WebLogic server that is running. This leads to two interesting question and simple resolutions.

(more...)