Change TZ on Oracle Restart and Oracle RAC

If you need to change the timezone on a server running an Oracle DB be careful to change it properly for Oracle Restart and Oracle RAC, otherwise you will end up getting incorrect values when connecting via Listener.

To change the TZ on OEL 7 to MST TZ:

timedatectl set-timezone America/Phoenix

Date command on OS will return the correct date:

[oracle@dbserver]$ date
Thu Jun 25 18:56:48 MST 2020

To configure TZ for Oracle Restart/RAC:

srvctl setenv database -d rspaws -t 'TZ=America/Phoenix'
srvctl setenv listener -l LISTENER -t 'TZ=America/Phoenix'

Check new values:

srvctl getenv database -d rspaws
srvctl getenv listener

Then restart the services:

srvctl stop listener -l LISTENER
srvctl start listener -l LISTENER

srvctl stop database -d rspaws
srvctl start database -d rspaws

Connect to the db using using the listener:

sqlplus system@rspaws

alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss';

!date

select sysdate from dual;

Date output should match between OS and Oracle.

If you don’t use Oracle Restart/RAC then changing the TZ at OS level is enough to make Oracle aware of the new date.

I’ve tested this procedure on Oracle 12.1, Oracle 12.2 and Oracle 18c with GI 18c.

Enjoy!

Uploading files to MOS

I want to share a very useful tip on how to upload file to MOS.

I think most people use the browser interface to upload to support.oracle.com website.

But if you want to upload the file from the Linux host itself or file is larger then 2Gb, go with curl instead.

Sintaxe is:

curl -T <file> -u <metalink_id> https://transport.oracle.com/upload/issue/<SR_number>/

example:

curl -T /var/tmp/sosreport-c0329121-3-23234203192-2020-06-02-edpizve.tar.xz -u rogerio.eguchi@velocitycloud.com https://transport.oracle.com/upload/issue/3-23223232323/

You just need to inform your password.

Relying on this approach will save you time as you don’t have to download the file(s) to the laptop then upload to the Oracle Support.

Easier, right ?

Fixing udev asm rules with kfed

I’ve been working on a very interesting project: DRaaS (Disaster Recovery as a service)

Yes, that is it. We are configuring a DR on AWS for a huge on-premises SAP system.

Regarding Oracle, we are relying on two technologies:

AWS CloudEndure

Oracle DataGuard

So during a test cutover using CloudEndure, we realised that the on-premises server was using udev rules and iSCSI protocol to access the storage.

On AWS EBS you don’t this option so a config change must be made for disks to be available to ASM.

Oracle ASM kfed to the rescue !

But what is kfed ?

It is an Oracle ASM tool to gather ASM disk header information when ASM diskgroups can not be mounted.

Script to fix the udev rules:

_kfed="$(grep ^+ASM /etc/oratab | cut -d\: -f 2)/bin/kfed"
for disk in `blkid | grep oracleasm | awk -F\: '{print $1}'`;do
device_name=`echo $disk | sed 's,/dev/,,g'`
echo -n "KERNEL==\"$device_name\", OWNER=\"grid\", GROUP=\"asmadmin\", MODE=\"0664\", SYMLINK+=\"oracleasm/disks/"
$_kfed read $disk 2>/dev/null | grep dskname | awk '{print $2 "\""}'
done > /etc/udev/rules.d/99-asm.rules

After fixing udev asm rule, you must reload the rules with:

udevadm control --reload-rules
udevadm trigger

And check if Oracle ASM disks permissions are correct on /dev

For more information on ASM tools you can check MOS: ASM tools used by Support : KFOD, KFED, AMDU (Doc ID 1485597.1)

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.

ORA-06508: PL/SQL: could not find program unit being called: “SYS.DBMS_CUBE_EXP”

If you get the error above when running expdp you most likely have OLAP objects remaining in data dictionary while OLAP is not installed or was de-installed.

In my situation, this have happened on Oracle 12.2 but it can also happen on 11.2

This can be confirmed if query below return rows:

SELECT * FROM SYS.EXPPKGACT$ WHERE PACKAGE = 'DBMS_CUBE_EXP';

To fix it, just run:

# backup exppkgact$_bck table
create table exppkgact$_bck as select * from exppkgact$;

# delete DBMS_CUBE_EXP from exppkgact$
delete from exppkgact$ where package = 'DBMS_CUBE_EXP' and schema = 'SYS';

commit;

And run expdp again.

DB system – OS patching

When using Oracle Cloud Database Services VM or Bare Metal, customer is responsible for OS updates and GI/DB patches so let’s first go through the steps to update an OL7 server.

This procedure is not applicable for Exadata DB systems.

General Recommendations

1) Don’t touch oraenv or .bash_profile

2) Don’t touch default local firewall rules

Backup

Backup your db prior to the OS update

NonProd first

Test this procedure on non prod server first

OS Update

1) Check if kernel is 4.1.12-124.27.1.el7uek then you need to change the bootefi label before updating the OS.

uname -r

To change bootefi label:

Edit /etc/fstab: Change the label bootefi to BOOTEFI (uppercase), reboot the server and run ls -l /etc/grub2-efi.cfg to check required link was created.

2) Run command below to identify the region server is running:

curl -s http://169.254.169.254/opc/v1/instance/ |grep region

3) Download the repo file and cp to yum dir:

wget https://swiftobjectstorage.<region>.oraclecloud.com/v1/dbaaspatchstore/DBaaSOSPatches/oci_dbaas_ol7repo -O /tmp/oci_dbaas_ol7repo

and copy it to yum dir

cp /tmp/oci_dbaas_ol7repo /etc/yum.repos.d/ol7.repo

4) Download the version lock files and overwrite existing one:

wget https://swiftobjectstorage.<region>.oraclecloud.com/v1/dbaaspatchstore/DBaaSOSPatches/versionlock_ol7.list -O /tmp/versionlock.list

cp /etc/yum/pluginconf.d/versionlock.list /etc/yum/pluginconf.d/versionlock.list-`date+%Y%m%d`
cp /tmp/versionlock.list /etc/yum/pluginconf.d/versionlock.list

5) run YUM update

yum update

6) reboot

7) check new kernel version

uname -r

Hopefully I can rely on OS Management to update this type of server next time 🙂 So stay tuned !

Oracle Gold Image

A gold image is a copy of a software-only, installed Oracle home. It is used to copy an image of an Oracle home to a new host on a new file system to serve as an active, usable Oracle home.

Why should you use it ?

It will allow you to have a “perfect” version of the OH you need to deploy.

I think until now most DBA’s usually installs the base Oracle version then applies the RU needed (or latest).

From now on you can just build an Oracle Gold Image and create your OH’s from it.

Very simple example: Creating a Gold Image for Oracle 19.7

First you need to install Oracle 19.3, then apply patch 30783556.

[oracle@server ~]$ $ORACLE_HOME/OPatch/opatch lspatches
30894985;OCW RELEASE UPDATE 19.7.0.0.0 (30894985)
30869156;Database Release Update : 19.7.0.0.200414 (30869156)

Finally to create the Gold Image:

[oracle@server ~]$ $ORACLE_HOME/runInstaller -silent -createGoldImage -destinationLocation /u01/soft
Launching Oracle Database Setup Wizard…
Successfully Setup Software.
Gold Image location: /u01/soft/db_home_2020-05-22_08-08-26PM.zip

So this zip file db_home_2020-05-22_08-08-26PM.zip will allow you to deploy Oracle 19.7

How ?

Just unzip it and execute runInstaller 🙂

Example:

[oracle@server ~]$ mkdir -p /u01/c0s6/app/oracle/product/19.7.0.0/dbhome_1

[oracle@server ~]$ unzip -q /u01/soft/db_home_2020-05-22_08-08-26PM.zip -d /u01/c0s6/app/oracle/product/19.7.0.0/dbhome_1

export ORACLE_HOME=/u01/c0s6/app/oracle/product/19.7.0.0/dbhome_1
export ORA_INVENTORY=/u01/c0s6/app/oraInventory
export ORACLE_BASE=/u01/c0s6/app/oracle

[oracle@server ~]$ ${ORACLE_HOME}/runInstaller -ignorePrereq -waitforcompletion -silent \
-responseFile ${ORACLE_HOME}/install/response/db_install.rsp \
oracle.install.option=INSTALL_DB_SWONLY \
ORACLE_HOSTNAME=${ORACLE_HOSTNAME} \
UNIX_GROUP_NAME=oinstall \
INVENTORY_LOCATION=${ORA_INVENTORY} \
SELECTED_LANGUAGES=en \
ORACLE_HOME=${ORACLE_HOME} \
ORACLE_BASE=${ORACLE_BASE} \
oracle.install.db.InstallEdition=EE \
oracle.install.db.OSDBA_GROUP=dba \
oracle.install.db.OSOPER_GROUP=dba \
oracle.install.db.OSBACKUPDBA_GROUP=dba \
oracle.install.db.OSDGDBA_GROUP=dba \
oracle.install.db.OSKMDBA_GROUP=dba \
oracle.install.db.OSRACDBA_GROUP=dba \
oracle.install.db.ConfigureAsContainerDB=false \
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \
DECLINE_SECURITY_UPDATES=true
Launching Oracle Database Setup Wizard…

The response file for this session can be found at:
/u01/c0s6/app/oracle/product/19.7.0.0/dbhome_1/install/response/db_2020-05-22_08-54-24PM.rsp
You can find the log of this install session at:
/u01/c0s6/app/oraInventory/logs/InstallActions2020-05-22_08-54-24PM/installActions2020-05-22_08-54-24PM.log
As a root user, execute the following script(s):
1. /u01/c0s6/app/oracle/product/19.7.0.0/dbhome_1/root.sh
Execute /u01/c0s6/app/oracle/product/19.7.0.0/dbhome_1/root.sh on the following nodes:
[server]
Successfully Setup Software.

[root@server tmp]# /u01/c0s6/app/oracle/product/19.7.0.0/dbhome_1/root.sh
Check /u01/c0s6/app/oracle/product/19.7.0.0/dbhome_1/install/root_c0s64150_2020-05-22_20-56-10-578402868.log for the output of root script

[oracle@server ~]$ $ORACLE_HOME/OPatch/opatch lspatches
30894985;OCW RELEASE UPDATE 19.7.0.0.0 (30894985)
30869156;Database Release Update : 19.7.0.0.200414 (30869156)

How cool is that ?

And it gets better with Fleet Provisioning.

ora-16467: switchover target is not synchronized

Quick post about how I fixed ORA-16467 today.

After successfully running a switchover verify command, like below:

alter database switchover to dbname verify;

I end up getting an ORA-16467 error !

Checking the Data Guard config I realised the config LOG_ARCHIVE_CONFIG was missing 😦

I fixed this config and switchover went as expected !

This was an Oracle 12.1 db.

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 !