Awarded Oracle Ace

I’m very delighted to announce, I’ve become an Oracle Ace ūüôā

I received notification from the Oracle Ace Program a few weeks back saying:
“I am pleased to inform you that you have been chosen to receive the Oracle ACE award.¬† You have been chosen based on your significant contribution and activity in the Oracle technical community.¬† Like your fellow Oracle ACEs, you have demonstrated a proficiency in Oracle technology as well as a (more...)

Session using a database link hangs on ‚ÄúSQL*Net more data from dblink‚ÄĚ

I have a client who recently move a database server from on-premise to a Cloud provider.  A database on this database server had a database link to their E-business database in the Oracle Cloud.  Since the move, any sessions in the database that use the database link to the E-business database would hang if the query was to return large dataset.

Below is selecting from dual over the database link that worked:

SQL>  (more...)

Index Monitoring in Oracle Database

Applies to ONLY Oracle Database 10gR1 through to 12cR1.
In Oracle Database 12cR2, this feature is replaced.  A new blog post of this feature will be posted in due course.

Since Oracle 10g, you can monitor indexes to see if they are being used or not.¬† Which is very useful as indexes, consume unnecessary CPU and I/O on DML activity if not used.¬† Therefore, it’s recommended to monitor indexes and any unused (more...)

Adding a Datafile to Temp Tablespace

When monitoring Tablespace Usage (see my Tablespace Usage blog post for more info), there comes a point when you need to add a datafile to the temp tablespace to allow for growth and more importantly get below a monitoring threshold for example in OEM or OpsView.

Query to see Current Temp Datafiles State

To see the current state of the temp datafiles:

set pages 999
set lines 400
col FILE_NAME format a75
select d.TABLESPACE_NAME,  (more...)

Stop password for user accounts expiring on Exadata

Depending on how the Oracle Exadata Machine was setup, the password for user accounts can expire thus requiring the password to be changed.

This has the knock on effect of the crontab not accessible and more importantly jobs do not run:

[oracle@v1ex1dbadm01 ~]$ crontab -l

Authentication token is no longer valid; new one required
You (oracle) are not allowed to access to (crontab) because of pam configuration.
[oracle@v1ex1dbadm01 ~]$

You can (more...)

Oracle Exadata Smart Flash Logging

What is Exadata Smart Flash Logging?

In an OLTP environment, it is crucial to have fast response times to redo log writes i.e. low latency.  When multiplexing redo logs for high availability i.e. to protect against hardware failure, redo log writes are only acknowledge when redo is written to all redo log members i.e when the slowest disk completes the write.  By this nature, whenever a disk slows down even (more...)

How to fix queries on DBA_FREE_SPACE that are slow

I found myself in a situation where OpsView a monitoring tool, was having difficulty monitoring the tablespaces for a particular pluggable database.

Upon investigation it was found the queries against the dictionary table DBA_FREE_SPACE were taking a very long time:

SQL> set timing on
SQL> select nvl(sum(dfs.bytes)/1024/1024,0) from dba_free_space dfs where dfs.tablespace_name = 'USERS';


Elapsed: 00:00:10.98

There are 60 tablespaces in this pluggable database, which the time varied (more...)

How to Enable Exadata Write-Back Flash Cache

Please check the following blog post “How to check if Exadata Write-Back Flash Cache is Enabled” for:

  • What is¬†Exadata Write-Back Flash Cache?
  • What are the Performance Benefit of¬†Exadata Write-Back Flash Cache?
  • How to check if Exadata Write-Back Flash Cache is Enabled?
  • Pre-requisites and minimum versions.

You can also get more info from My Oracle Support (MOS) note:
Exadata Write-Back Flash Cache – FAQ (Doc ID 1500257.1)
OTN Article: Oracle Exadata Database (more...)

How to check if Exadata Write-Back Flash Cache is Enabled

What is Exadata Write-Back Flash Cache?

Exadata Write-Back Flash Cache provide the ability to cache not only read I/Os but write I/O to the Exadata’s PCI flash on the storage cells.¬† Exadata storage software or higher and Grid Infrastructure and Database software or higher is required to use Exadata Write-Back Flash Cache, which is persistent across storage cell restarts.

The default since April 2017 for (more...)

How to run on Exadata

Very often when creating a Support Request (SR) for an issue on an Oracle Exadata Database Machine, you’ll need to run the script ““.¬† Which is the “Oracle Exadata Database Machine – Diagnostics Collection Tool“.

More information can be found on My Oracle Support (MOS) Note:
SRDC – EEST Sundiag (Doc ID 1683842.1)

To run ““, is very simple as shown below:

[root@v1ex1dbadm01 ~]#  (more...)