Statspack batch install

If you don’t have Oracle Diagnostics pack or is running Oracle Standard Edition, just go for statspack !

In this blog post I will show you how to install it in batch mode.

It is actually very simple, you just need to define three variables:

connect / as sysdba

define default_tablespace='perfstat'
define temporary_tablespace='temp01'
define perfstat_password='YourComplexStatspackPassword'

then run


Simple, isn’t ?

I would also recommend you to take a look at Franck’s way of improving statspack here.

AWS cli for rds reports

Another quick blog post on AWS stuff.

You can query your RDS metadata information using aws cli.

This is a very useful approach when you manage hundreds of servers and need to build a report.

Here is the command line I’ve got to retrieve the database name, license model, DB engine and DB version.

aws rds describe-db-instances --region us-east-1 --query "*[].{DBInstanceIdentifier:DBInstanceIdentifier,LicenseModel:LicenseModel,Engine:Engine,EngineVersion:EngineVersion}" --output table

You can find other rds cli options here.

AWS Nitro – volume id and device name

Hi all,

Quick blog post about EBS on AWS nitro instances.

When working with AWS Nitro instances your EBS volumes will be exposed as NVMe block devices, i.e nvme0n1/nvme1n1, etc, regardless what your input is when provisioning them.

But you can use the nvme tool to map the NVMe device name to the actual name you have provided:

[ec2-user ~]$ sudo nvme id-ctrl -v /dev/nvme1n1
NVME Identify Controller:
vid : 0x1d0f
ssvid : 0x1d0f
sn : vol01234567890abcdef
mn : Amazon Elastic Block Store
0000: 2f 64 65 76 2f 73 64 6a 20 20 20 20 20 20 20 20 "/dev/sdf…"

So in this case I’ve used sdf as block volume name and ended up with nvme1n1 on my instance.

I highly recommend you to read the docs here and watch a cool video.

OCI CLI authentication for federated users

In this short blog post I will explain how to authenticate using a federated user instead of a local one.

Install your oci cli then run:

oci session authenticate

Inform the region your tenant is subscribed to, login to the console and define profile name that better suits you.

A config file will be saved on your computer.

You can test your access running:

oci iam region list --config-file <config> --profile <name> --auth security_token

The default token TTL is set to 1 hour before it expires and can be refreshed within the validity period up to 24 hours. To refresh the token run:

oci session refresh --profile <name>

Hope it helps !

OCI – Patching a DB System

On this blog post I will write detailed steps on how to apply patches on bare metal/virtual machine DB systems and database homes by using DBCLI.

Yes, I enjoy the black screen 🙂

No, this is not the only option. You can also use Console and API’s.

This procedure is not applicable for Exadata DB systems.


1) Access to the Oracle Cloud Infrastructure Object Storage service, including connectivity to the applicable Swift endpoint for Object Storage. Oracle recommend using Service Gateway to enable this access.

2) /u01 FS with at least 15Gb of free space

3) Clusterware running

4) All DB system nodes running


Backup your database prior to the patch event.

Non Prod first

Test this patch on non prod (or test) server first


1) Update the CLI

 cliadm update-dbcli

2) Wait the job to complete

dbcli list-jobs

3) Check for installed and available patches

dbcli describe-component

4) Display the latest patch versions available

dbcli describe-latestpatch

5) Run pre check

dbcli update-server --precheck


run describe-job to check job status:

dbcli describe-job -i <jobId>


6) Update the server components. This step will patch GI.

dbcli update-server


Once successfully completed proceed with DB home patching.

7) List db homes

dbcli list-dbhomes

8) Run update home on select OH

dbcli update-dbhome -i <Id>


And check status with describe-job command:


You can find the DCS Logs at:


Under the hood dbcli relies on opatchauto so you can also check $ORACLE_HOME/cfgtoollogs/opatchauto directory for logs.

There is also a nice doc about troubleshooting in case something goes wrong.

OCI – Unknown error when running DB system patch pre-check

This week I’ve been working on a DB system Patch for an Oracle 12.2 system.

Task prerequisites are as follow:

1) Access to the Oracle Cloud Infrastructure Object Storage service, including connectivity to the applicable Swift endpoint for Object Storage.

2) /u01 FS with at least 15Gb of free space

3) Clusterware running

4) All DB system nodes running

With all prerequisites checked, I then ran, at the console, a pre-check option to ensure patch can be successfully applied.

Example below:

But I’ve got an unknown error as you can see in the images from work requests page:

Suck an error does not help at all but if run the same pre-check command from dbcli you will get another and actually meaningful error:

[root@node ~]# dbcli update-server --precheck
DCS-10214:Minimum Kernel version required is: 4.1.12-124.20.3.el6uek.x86_64 Current Kernel version is: 4.1.12-112.16.7.el6uek.x86_64.

Much better, right ?

So in order to apply the DB system patch I will need to first update the OS kernel.

This information should be on the prerequisites list but it is not 😦

So from now on I would suggest you to run pre-check from dbcli instead of OCI console but default or at least when facing unexpected behaviour.

Full documentation on patch DB systems here and to update the kernel you can check OCI docs here.

Hope this helps !

OGB Appreciation Day: parallel ops #ThanksOGB

Time is money so let’s get things done faster with parallel operations like:

  • Expdp/Impdp:
expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR parallel=4 dumpfile=SCOTT_%U.dmp logfile=expdpSCOTT.log

impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR parallel=4 dumpfile=SCOTT_%U.dmp logfile=impdpSCOTT.log

Must read note here.

  • RMAN

RMAN always allocates the number of channels specified in PARALLELISM, using specifically configured channels if you have configured them and generic channels if you have not. Note that if you configure specific channels with numbers higher than the parallelism setting, RMAN will not use these channels.
  • Parallel Execution of SQL statments

Nice Oracle Paper here and of course Oracle docs.

  • Parallel Upgrade Utility (
$ORACLE_HOME/bin/dbupgrade -n 4

-n options specifies the number of processes to use for parallel operations.

Non-CDBs: The -n parameter specifies the number of SQL processes to use when upgrading the database.

Multitenant architecture databases (CDBs): The number of PDBs upgraded concurrently is controlled by the value of the -n parameter. Multiple PDB upgrades are processed together. Starting in Oracle Database 12c, the default value for multitenant architecture databases is the number of CPUs on your system. A cpu_count equal to 24 equates to a default value of 24 for -n.

Values for the -n parameter:

Non-CDBs: The maximum value for -n is 8. The minimum value is 1. The default value is 4.

Multitenant architecture databases (CDBs): The maximum value for -n is unlimited. The minimum value is 4. The maximum PDB upgrades running concurrently is the value of -n divided by the value of -N.

-N option specifies the number of SQL processors to use when upgrading PDBs.

For non-CDBs, this parameter is ignored.
For CDBs, the maximum value is 8. The minimum value is 1. The default value is 2.

So without specifying values for the parameters -n and -N (that is, accept the default value of -N, which is 2, and accept the default value of -n as the CPU_COUNT parameter value, which is 24). 

The following parallel processing occurs:

12 PDBs are upgraded in parallel (CPU_COUNT divided by 2)
2 parallel processes run for each PDB

That is what came from the top of my head so it should have more parallel options 🙂

Thanks Tim to get the community together every year ! #ThanksOGB

-ocmrf is no longer needed

I was patching an Oracle 12.1 Restart with latest bundle patch this week and realized that the -ocmrf option is no longer needed.

This enhancement started with Opatch so now you have one more reason to update your Opatch 🙂

Don’t even bother looking for emocmrsp binary … it is no longer there !

So things just got easier ! All you have to do is run:

opatchauto apply <UNZIPPED_PATCH_LOCATION>/<BUG NO> 

Example for 12.1 BP:

export PATH=$PATH:/u01/app/
opatchauto apply /home/oracle/stage/29176139/29141038

Reference: MOS note 2161861.1, 1591616.1 and readme.html for patch 29176139.

error DIA-49803 on adrci purge

I am a big fan of automation. I don’t like to do the same thing over and over, so after reading the post about purge/rotate oracle trace and log files from Fred Denis I started testing his scripts to implement them.

Everything was perfect until I run adrci purge to clean rdbms old files from Oracle 12.1 and got error DIA-49803.

MOS to the rescue and after digging I found note:
2341825.1 -Purge Does Not Work In 12.2 If ADRCI Is Used To Access An Older ADR Home, which is exactly my case.

So the recommendation is simple: use the version of ADRCI that is shipped with the version of the database ($ORACLE_HOME/rdbms/bin/adrci).

For the record: This server has Grid 12.2 and RDBMS 12.1 and 12.2.

OPatch update for EMGC

Download the latest version from here.

For EMGC 13.1 and above , select the version as “” (Release- OPatch Unzip the file into a staging directory like /u01/stage/.

Stop OMS with $ORACLE_HOME/emctl stop oms -all where OH is the middleware home.

Run: java -jar <STAGE_DIR>/6880880/opatch_generic.jar -silent oracle_home=$ORACLE_HOME

To validate the installation run $ORACLE_HOME/OPatch/opatch version

The output should match the value from the readme file.