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
CONFIGURE DEVICE TYPE disk PARALLELISM 2;
CONFIGURE DEVICE TYPE sbt PARALLELISM 2;


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 (catctl.pl/dbupgrade)
$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 12.2.0.1.5 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/12.1.0.2/grid/OPatch/
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 “13.9.0.0.0” (Release- OPatch 13.9.0.0.0). 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.



TFA collections very slow

Working with Oracle you already know that one day you will have to upload a tfa diagcollect log to Oracle Support.

As I always update TFA as soon as a new version comes out it was just a matter of running it. Right now the latest version is 18.4.2 and can be download here.

I did and wow … nothing happened. It hanged forever doing I do not know what.

Logs from the diagcollect were not helpful and neither strace.

I uploaded the files manually following MOS note: 2291539.1 and then filed another SR to support about the TFA issue.

A couple of days later and the analyst came with a workaround: set trimfiles=OFF

If you want to know more about TFA take a look at my OTN Brazil article here.