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.

Prerequisites

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

Backup your database prior to the patch event.

Non Prod first

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

Patching

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

Example:

run describe-job to check job status:

dbcli describe-job -i <jobId>

Example:

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

dbcli update-server

Example:

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>

Example:

And check status with describe-job command:

Logs

You can find the DCS Logs at:

/opt/oracle/dcs/log/

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
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.