maximum size of ORALCE_SID

Uncategorized
| Dec 13, 2019


The maximum length of ORACLE_SID environment variable seems to be a mystery in many places.
Recently I reviews a document where the max length was defined as 8 characters. I was quite sure this was not right, but I didn't know the correct value. A short ressearch did not find anythign useful in the docs - the Best available there was RAC installation guide for Linux & UNIX:
The Oracle Service Identifier (SID) prefix is the first 8 characters (more...)

glitches on runInstaller attachHome

Uncategorized
| Dec 3, 2019

Cloning ORACLE_HOMEs should be quite well known by now.

Still sometimes there are some glitchs to take care of.
In this case I cloned an existing ORACLE_HOME (dbhome_1) to a new one (dbhome_2) so I can apply a patch and reduce the downtime to <switching ORACLE_HOME> plus <running datapatch>.

Running the runInstaller was straight forward:
./runInstaller -silent -attachHome -invPtrLoc /var/opt/oracle/oraInst.loc \
 ORACLE_HOME="/path_to/oracle/product/18.0.0/dbhome_2" \
 ORACLE_HOME_NAME="OraDB18Home2" CLUSTER_NODES="{MY_NODE}" -local

But when I tried (more...)

Oracle Datapatch – out of place patches simplified

Uncategorized
| Nov 6, 2019
datapatch was introduced with Oracle 12.1 to simplify management of post-patching steps when a patch (or patchset) is applied.
As most software, it was written with best intentions but little knowledge how customers would use it.
One of these unforseen used case was parallel patching of multiple DBs in the same ORACLE_HOME at the same time.
Another problem was missing rollback files due to out of place patching.

Oracle addressed this in Bug 23170620.
(more...)

Oracle patches – a patchfiles anatomy

Uncategorized
| Oct 24, 2019
In my previous post I explained some basics about IDs of platforms, products and similar and where these patches can be obtained.

Today my focus is on the content of some patchfiles.

As I'm basically a DBA, my patchfiles are regarding database binaries. Patchfiles for other software products might differ, but I hope their main structure is similar.

A very simple patch is p26007010_12102180417ProactiveBP_Linux-x86-64.zip
it contains these files:
.
├── 26007010
│   ├── etc
│   (more...)

Oracle patches – some basics and good-to-know information

Uncategorized
| Oct 21, 2019
Oracle Patches can occur very complicated and confusing. Most of the time they are zip files with strange and sometimes inconsistent naming convention.
As these patches are processed by a program called opatch (which also can be obtained as a patch - but it does not follow the same structure as regular patches).

Back to basics: In patches words like product, platform or language are used. They are all IDs and their "data dictionary" (more...)

OGB Appreciation Day : manipulating execution plans

Uncategorized
| Oct 10, 2019
Tims call for OGB Appreciation Day 2019 (#ThanksOGB) comes in time this year.

Even unemployed at the moment, I had a chance to help a friend with some problematic SQL statements.

The statements are not that important at all but he possibilities we had to address the problems were great!


First of all an existing execution plan, together with session stats, ASH/AWR and in one case SQLTRACE. None of these sources of information (more...)

Simple getTracefile

Uncategorized
| Sep 30, 2019
Tracefiles are a very valuable source of information in Oracle databases.
In Versions prior to 12.2 quite complicated objects (with java dependency) were required.
Fortunately, in 12.2 Oracle introduced (& documented) the View V$DIAG_TRACE_FILE_CONTENTS. Unfortunately for some (only good from an internal technical perspective) reasons it does not contain the file itself, or a LOB locator, but several chunks as VARCHAR2. So it's still worth to write some code for a simple interface (more...)

possible changes in Oracle 20

Uncategorized
| Jul 18, 2019
As this post is written in July 2019, it's highly speculative writing about changes in Oracle version 20.
But at least it's based on some information available from Oracle.

A new autoupgrade.jar version is available at AutoUpgrade Tool (Doc ID 2485457.1). In this Note, there is also a BUGS_20190715.txt which contains (among others):
AUPG-955 Disable NONCDB validation when upgrading a DB to 20c
AUPG-975 upgrade from 12.1.0.2 to 20 (more...)

ORA-21700 in data dictionary

Uncategorized
| Jun 7, 2019

This whole story started with a nice little ticket:

When I try to edit a scheduler job in <DB>  with user <USER> I am getting error:
ORA-21700: object does not exist or is marked for delete
even I can see the object and the object is not deleted or marked as delete.

At first I could not reproduce the error, so I asked for something more specific, and I got 2 screenshots. (one shown here)
(more...)

ORADEBUG DOC 19.3

Uncategorized
| Jun 2, 2019
this is the output of ORADEBUG DOC for Version 19c.

SQL> oradebug doc

Internal Documentation
**********************

EVENT Help on events (syntax, event list, ...)
COMPONENT [<comp_name>] List all components or describe <comp_name>

oradebug doc event

SQL> oradebug doc event

Event Help:
***********

Formal Event Syntax
--------------------
<event_spec> ::= '<event_id> [<event_scope>]
[<event_filter_list>]
[<event_parameters>]
[<action_list>]
[off]'

<event_id> ::= <event_name | number>[<target_parameters>]

<event_scope> ::= [<scope_name>: scope_parameters]

<event_filter> ::= {<filter_name>: filter_parameters}

<action> ::= <action_name>(action_parameters)

<action_parameters> ::= <parameter_name> = [<value>|<action>][, (more...)

deleting orphaned CommVault backups

Uncategorized
| Jun 1, 2019

In the current environment Commvault is used as a central backup solution. The setup is not very common, as CommVaults scheduler is not used for any activities. All backups are scheduled by a system which also takes some infrastructural limitations into consideration. These are (among others): shared IO resources of consolidated data­bases on a cluster, shared backup network resources of clusters, shared media agents in different datacenters, and some other constraints as well.
Also retention policies (more...)

invisible IOs

Uncategorized
| May 5, 2019
My team identified an interesting case of invisible IOs this week. During some checks of system statistics graphs, something unexpected ocured:
One of those "small & unimportant" test DBs had an interesting IO pattern:





This was something interesting, so a colleague decided to have a deeper look at it. The next obvious step is to see if AAS shows anything interesting. And it does:


A lot of blue (which is used for IO in all (more...)

limit IOs by instances

Uncategorized
| Mar 30, 2019
This is a follow up to my previous post.

I had to answer if I can limit the (IO) resources a DB instance can utilize. Unfortunately, in a simple instance I can not do so. It can be done in PDBs, but right now PDBs are out of scope.
So a simpler approach was developed: limiting IOs by creating cgroups.

There are 2 steps:

  1. disks
  1. create a proper cgroup
  2. get all related devices for (more...)

shared resources

Uncategorized
| Feb 17, 2019
Some days ago I had one of these calls from an application colleague:
The database was slow yesterday, can you please check what's the problem?
Of course, I had some short discussion if he really means the DB or should it be called the application is slow. Also some other questions needed to be asked first, e.g. if it's a response time or throughput issue, when it was "good" last time, what "bad" and (more...)

connection manager – what happens at startup & shutdown

Uncategorized
| Jan 3, 2019
Oracle Connection Manager (cman) is a great tool to create a gateway between networks which can not be connected, or filter those who can access to a service.
Most of the time, it's a very robust implementation and doesn't need a lot of attention. But if it's required to dig into it, a basic knowledge about it's components can be useful.


In this post I'll show what happens in a simple startup (& (more...)

preupgrade.jar – enforced recommendations

Uncategorized
| Oct 18, 2018


Recently I upgraded a database from version 12.1 to 12.2.
I take upgrades serious - similar (but not as skilled) to Mike Dietrich. So I downloaded latest preupgrade.zip and unzipped it [I made a small error by unzipping it NOT to $ORACLE_HOME/rdbms/admin but to a temporary directory - later more about this].
Beside such errors, I try to follow Oracles recommended Upgrade Process.

At some point Oracle recommends to Gather Dictionary Stats (more...)

SQL Real Time Monitoring pure HTML report (thanks to SQLDeveloper 18.3)

Uncategorized
| Oct 11, 2018
Yesterday (from the writing of this post perspective) SQLDeveloper 18.3 came out.
(it seems SQL Developer does not strict follow Oracle Database Release Number schema, otherwise it must be 18.4 already as it's released in 4th quarter of 2018)

There are many bugs fixed on 18.3 and some nice enhancements there as well.

My favorite enhancement is Real Time SQL Monitoring, HTML exports now available - no flash!

As Tanel Poder wrote (more...)

seing your DB as it was some minutes ago

Uncategorized
| Oct 1, 2018
Last week during a discussion with a colleague we thought if it would make sense to have in SQLDeveloper the possibility to see the system "as it was some minutes ago".
Small errors can happen and also resource control isn't always perfect in every company.

Björn Rost suggested to use DBMS_FLASHBACK.ENABLE_AT_TIME:

TFA reports full dba_feature_usage_statistics to MyOracleSupport

Uncategorized
| Sep 4, 2018
Those who deal with MyOracleSupport (MOS) regularly will have noticed their demand for Trace File Analyzer (TFA) diagnostic collections.
Few will know they are also providing a full content of their DBs DBA_FEATURE_USAGE_STATISTICS view; at least when running the parameters
tfactl diagcollect -database <DB_UNIQUE_NAME>

The report calls tfa_home/resources/sql/db_feature_usage.sql and it's result is stored in the <hostname>.tfa_<timestamp>.zip file.

I did not find any mention about this behavior in the documentation, so it might be (more...)

RBAL process needs to release FORMER disk

Uncategorized
| Sep 4, 2018
Oracles Automatic Storage Management (ASM) has many nice advantages against other volume managers. One of my favorite is to add and remove or rebalance disks without any interruption for the database. Unfortunately the remove of devices after the rebalance is completed is not as straight forward as expected: 
In a current environment (Grid Infrastructure: 12.2.0.1.180417, rdbms: 12.1.0.2.180116 and others) after the ALTER DISKGROUP xxx (more...)