addNode.sh, failed root.sh and IB listener

So this customer has an Exadata quarter rack and they have an IB listener configured on both DB nodes (for DB connections from a multi-racked Exalogic system). We were adding a new DB node to this rack. So just followed the standard procedure of creating users, directories etc on the new node, setting up ssh equivalence and running addNode.sh. All went fine but root.sh failed. Little looking into the logs revealed that it (more...)

Securefile space

A script hacked together a couple of years ago from a clone of a script I’d been using for checking space usage in the older types of segments. Oracle Corp. eventually put together a routine to peer inside securefile LOBs:

rem
rem     Script:         dbms_space_use_sf.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Dec 2013
rem     Purpose:
rem
rem     Last tested
rem             12.1.0.1
rem             11.2.0.4
rem     Not tested
rem             11.1. (more...)

Oracle Service Secrets: Migrate Transparently

Databases or schemas tend to get moved around between different servers or even datacenters for hardware upgrades, consolidations or other migrations. And while the work that needs to be done is pretty straight forward for DBAs, I find the most annoying aspect of that is updating all client connect strings and tns entries used with new IP addresses and – if not using services – also the SID as the instance name might have changed.

(more...)

Oracle Database – JSON en de In-Memory Database

Op maandag 24 oktober vindt er een bijeenkomst plaats over JSON en de In-Memory Database…

Posts by Alberto Dell’Era On ASH Wait Times And Materialized Views

This is just a short note that links to different articles on Alberto Dell'Era's blog. Alberto is a long time member of the OakTable and has published a number of posts I find very useful and therefore are linked here:

ASH wait times (including an ASH simulator!)

Posts on Materialized Views

Basicfile LOBs 6

One of the nice things about declaring your (basicfile) LOBs as “enable storage in row” is that the block addresses of the first 12 chunks will be listed in the row and won’t use the LOB index, so if your LOBs are larger than 3960 bytes but otherwise rather small the LOB index will hold only the timestamp entries for deleted LOBs. This makes it just a little easier to pick out the information you (more...)

Basicfile LOBS 5

At the end of the last installment we had seen a test case that caused Oracle to add a couple of redundant new extents to a LOB segment after one process deleted 3,000 LOBs and another four concurrent processes inserted 750 LOBs each a few minutes later (after the undo retention period had elapsed). To add confusion the LOBINDEX seemed to show that all the “reusable” chunks had been removed from the index which suggests (more...)

Space Usage

Here’s a simple script that I’ve used for many years to check space usage inside segments.  The comment about freelist groups may be out of date  – I’ve not had to worry about that for a very long time. There is a separate script for securefile lobs.


rem
rem     Script:         dbms_space_use.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Nov 2002
rem     Purpose:
rem
rem     Last tested 
rem             12.1.0.1
rem             11.2. (more...)

How many memory is truly used by my Oracle instance?

There are many posts about the amount of memory that is taken by the Oracle database executables and the database SGA and PGA. The reason for adding yet another one on this topic is a question I recently gotten, and the complexities which surrounds memory usage on modern systems. The intention for this blogpost is to show a tiny bit about page sharing of linux for private pages, then move on to shared pages, and (more...)

Bash for DBAs – Part 2

In this post I’ll continue talking about writing bash scripts for database stuff. I’ll devote this entire part to sqlplus, as there is a lot to say here. Running sqlplus from bash I’ll start with the simplest thing, running sqlplus from bash. sqlplus can get several flags, the most important one for scripting is the … Continue reading Bash for DBAs – Part 2

Bad Coding and 12c Upgrade Will Break

With the introduction of CDB, many views have added column CON_ID.

DB1:(SYS@DB1):PRIMARY> select inst_id inst,sid,serial#,program,machine from gv$session where sid in ((select * from dba_blockers));
select inst_id inst,sid,serial#,program,machine from gv$session where sid in ((select * from dba_blockers))
                                                                              *
ERROR at line 1:
ORA-00913: too many values


DB1:(SYS@DB1):PRIMARY> desc dba_blockers;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 HOLDING_SESSION                                                NUMBER
 CON_ID                                                         NUMBER

DB1:(SYS@DB1):PRIMARY> select inst_id inst,sid,serial#,program,machine from gv$session where sid in ((select HOLDING_SESSION from dba_blockers));

INST        SID    SERIAL# PROGRAM                         (more...)

Installing Pibrella on Raspberry Pi 3 and GPIO Pins

With me traveling to Redwood City next week to visit HQ and then Oracle Open World the week after, I’m busy during many of my off hours preparing a new Raspberry Pi STEAM setup for a maker’s faire the beginning of October.

I’d heard that many were having challenges with installing different HATs on their new RPI 3, especially older ones like the Pibrella.

pibrella1

This is still one (more...)

Oracle Virtual Columns – Can’t use plain column or duplicate expressions

I had a scenario today where I was loading a table and a particular column is known by multiple names in different source systems and thus to different people. In order to make everyone happy on this occasion, I wondered if I could create a normal column for one of the multiple names and then use virtual columns pointing at the normal column, for the other names.

I’m aware there are several ways of skinning (more...)

OEDA–Things to keep an eye on

So if you are filling an OEDA for Exadata deployment there are few things you should take care of. Most of the screens are self explanatory but there are some bits where one should focus little more. I am running the Aug version of it and the screenshots below are from that version.

  1. On the Define customer networks screen, the client network is the actual network where your data is going to flow. So typically (more...)

Another great review

The book has also been reviewed by Rob Zoeteweij who is an Oracle ACE, Enterprise Manager expert, specialist and trainer. He also has his own Linkedin group on Enterprise Manager.

His review is on the following link. Thanks Rob.

https://oemgc.wordpress.com/2016/09/07/i-would-highly-recommend-reading-his-book/

Ask Tom table about NOLOGGING and redo generation

I was googling for things related to NOLOGGING operations and found this useful post on the Ask Tom web site: url

There is a nice table in the post that shows when insert operations generate redo log activity. But it isn’t formatted very well so I thought I would format the table here so it lines up better.

Table Mode    Insert Mode     ArchiveLog           mode result
-----------   -------------   -----------------    -----------
LOGGING       APPEND          ARCHIVE LOG          redo generated
 (more...)

Bash for DBAs – Part 1

Many Oracle databases are running on Linux/UNIX systems, and many DBAs need to write all kind of scripts. Over the years I’ve written probably tens of thousands of script lines (the longest script I remember was about 3000 lines), and almost every time I had the chance to learn something new. Bash is really really … Continue reading Bash for DBAs – Part 1

AIOUG Sangam’16 – India’s largest Annual Oracle Confernece – DON’T MISS IT

AIOUG Sangam'16, India's largest annual Oracle conference, to be held in Bangalore this year during 11th and 12th November.

I am glad and super excited to present and attend the conference. I am going to present the following two sessions:


  • Exadata Migration Recipes - Secret Sauce for a Tasty Migration
Abstract
The purpose of this presentation is to provide real-world augmented database migration procedures that allowed us to successfully achieve the migrations of 54 (more...)

NFRs are easy – what about your NFO’s?

You're used to working on your Non-Functional Requirements (NFR's) - we're used to worrying about how secure our systems are, how many users they will support, how resilient they are to the failure of a component. What we're not used to thinking about are Non-Functional Opportunities - (NFO's) what are the unforeseen benefits that the business will find when they use IT?

The cloud blows the non-functional requirement (NFR) out of the water. You want (more...)

A Bloomin’ Mess


'Why is it,' he said quietly, 'that quite often even the things which are correct just don't seem to be right?'
-- Norton Juster, The Phantom Tollbooth

For some time now Oracle has used bloom filters to speed the processing of select statements; unfortunately in 11.2.0.4 converting a select statement that uses a bloom filter into an ‘insert into … select’ form causes Oracle to abandon the bloom filter altogether. Let’s (more...)