Monat: Juli 2023

Easy Autoupgrade/Autopatch Oracle Out-of-Place 19.20 within Sphinx Environment

If not already exists a „not used“ Oracle Home. create a new one..

dbsw_install dbs 19.EE 19.20
(19.EE is our 19.16 Prepared Image) 

Now it will create our new Home in 

(it will prompt that you have to make an root commando, please do that)

Replace the OPatch utility with the newest Version

unzip /app_oracle/local/tools/ -d $ORACLE_HOME/

Download the Patches from Oracle with WGET

Find Patchnr here:



Thx WGET its easy , i dont like the Page to download Patchs..

The 19.20 DB+OJW Bundle Patch

wget --http-password=Dontellu --no-check-certificate ""

The 19.19 DBRU Patch (which cannot be applied at the moment)
i have to wait for 19.20

wget --http-password=Dontellu --no-check-certificate ""

Unpack the *.zip into to /app_oracle/product/dbs/19.20-patch/ (or/tmp)

Set the enviroment to the Target new home:

export ORACLE_HOME=/app_oracle/product/dbs/19.20
export PATH=/app_oracle/product/dbs/19.20/OPatch:$PATH
opatch version

cd /app_oracle/product/dbs/19.20-patch/35370174/35320081
opatch apply
cd /app_oracle/product/dbs/19.20-patch/35370174/35354406
opatch apply 
$ORACLE_HOME/OPatch/opatch lspatches

Oracle recommends to use the autoupgrade Tool to switch the home and install all correctly: You have to create an cfg to use autoupgrade correctly.

cd /app_oracle/local/tools/autoupgrade.jar

vi autoupgrade_patch_CDB.cfg


Okay last Check – autoupgrades brings an „analyze“ with it

$ORACLE_HOME/jdk/bin/java -jar autoupgrade.jar \
-config autoupgrade_patch_CDB.cfg \
-mode analyze

cat /app_oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log

looks good -- everthing fine

Okay start the upgrade- now the database is a while unavailable – if it is a singe instance

$ORACLE_HOME/jdk/bin/java -jar /app_oracle/autoupgrade/autoupgrade.jar \
-config /app_oracle/autoupgrade/autoupgrade_patch_CDB.txt \
-mode deploy

cat /app_oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log

Within our Sphinx Environment now its needed to change some things

cd /app_oracle/local/etc
Copy our oraenv specific shellscript too 


change the .EE with the current version
vi -- change .EE mit .20


ln -s

Switch to the current environment an take a look
chenv c10p
sqlplus "sys/sx123 as sysdba"

Also change listener.ora to run on 
instead of 19.EE

-- -- all is good

Worksfine, it can be used for x Databases in one Script


Streamlined Oracle Installation Guide

In this concise blog post, we present a comprehensive Oracle Installation guide for VMWARE users, focusing on the seamless setup of Oracle Linux 8 (OL8) with Oracle Database 19c.

The journey begins with VMWARE ESXi, where we will lay the foundation for creating a robust Oracle environment. With our step-by-step instructions, you’ll find the process smooth and straightforward, allowing you to get your database up and running efficiently.

Stay tuned as we walk you through the entire installation process, unraveling the complexities and highlighting the essential elements to ensure a successful setup. Your Oracle Dataguard 19c implementation on OL8 in VMWARE will soon be a reality with our expert guidance.

Whether you’re a seasoned Oracle administrator or a newcomer to the world of databases, this blog post will provide valuable insights and help you harness the power of Oracle Dataguard for your VMWARE-based Oracle environment. Let’s embark on this journey together and unlock the potential of your data infrastructure!

VMWARE Settings – 2 DISKs – 1 for OS 1 for Database ( i choose 256 GB for this, but 100 is also enough)
  • Install Linux

Upon selecting the OL 8 ISO image on the CD/DVD drive, we are now at the VM’s start screen.

OL 8 Startup in VMWARE ESXI Console

The main setting at this point is Language Support.

Also TIME & Date – Select Timezone Europe/Vienna

To rectify the incorrect selection, change the Network Settings Method to MANUAL.
Ask Tom for DNS Server and Gateway…

The next step is Software Selection.

You can add additional Software, but you dont need in our Setup

The next step is Installation Destination:

After this Process – change root PWD and start the Installationprocess.

After a while open putty and make ssh connection to the new created vm server:

  • Install Sphinx Environment Scripts for Linux + Oracle

Now, log in as root and follow these steps:

a) vi /etc/hosts 
add or change tom
b) vi /etc/hostname - check or rename host
c) vi /etc/sysconfig/network-scripts/i* - check or change ip

Let’s set up the volumes.

Look Logical Volumns
[root@tom]# lsblk

Create Physical Volumn and Volumn Group for sdb (Disk2)
pvcreate /dev/sdb
vgcreate app /dev/sdb

Create Logical Volumns 
lvcreate -L 16G -n app_others app 
lvcreate -L 16G -n app_oracle app
lvcreate -L 16G -n app_oracle_fra app
lvcreate -L 32G -n app_oracle_data app
lvcreate -L 16G -n app_oracle_exp app

Create XFS Filesystem
mkfs.xfs /dev/app/app_others
mkfs.xfs /dev/app/app_oracle
mkfs.xfs /dev/app/app_oracle_fra
mkfs.xfs /dev/app/app_oracle_data
mkfs.xfs /dev/app/app_oracle_exp

Create directorys
mkdir /app
mkdir /app_oracle
mkdir /app_oracle/data
mkdir /app_oracle/exp
mkdir /app_oracle/fra

And add mount points for it
mount /dev/app/app_others /app
mount /dev/app/app_oracle /app_oracle
mount /dev/app/app_oracle_data /app_oracle/data
mount /dev/app/app_oracle_exp /app_oracle/exp
mount /dev/app/app_oracle_fra /app_oracle/fra

Now it looks like this:
[root@tom /]# lsblk
sda 8:0 0 16G 0 disk
├─sda1 8:1 0 1G 0 part /boot
└─sda2 8:2 0 15G 0 part
├─ol-root 252:0 0 13.4G 0 lvm /
└─ol-swap 252:1 0 1.6G 0 lvm [SWAP]
sdb 8:16 0 32G 0 disk
├─app-app_oracle 252:2 0 16G 0 lvm /app_oracle
├─app-app_oracle_fra 252:3 0 4G 0 lvm /app_oracle/fra
├─app-app_oracle_data 252:4 0 5G 0 lvm /app_oracle/data
├─app-app_oracle_exp 252:5 0 756M 0 lvm /app_oracle/exp
└─app-app_others 252:6 0 6G 0 lvm /app

my host

It’s essential to add these mount points to the fstab file.

--now add via vi fstab all mount points to persists on reboot

vi /etc/fstab
/dev/mapper/app-app_others     /app             xfs     defaults        0 0
/dev/mapper/app-app_oracle     /app_oracle             xfs     defaults        0 0
/dev/mapper/app-app_oracle_data    /app_oracle/data             xfs     defaults        0 0
/dev/mapper/app-app_oracle_exp     /app_oracle/exp             xfs     defaults        0 0
/dev/mapper/app-app_oracle_fra     /app_oracle/fra             xfs     defaults        0 0

GitLab is an excellent choice for managing environment scripts.
First we need to install git:

yum install git
mkdir -p /media/software
cd /media/software
git clone
git clone

-- install yum packages and other stuff we need in linux
cd linux

-- install yum packages and other stuff we need for oracle
cd ../oracle_root
./ -i oracle /app_oracle 19

Once the Oracle user is installed, you can proceed with a silent Oracle installation, eliminating the need for the Database Configuration Assistant (DBCA). This streamlined installation method will save time and effort, making the process more efficient.

-- Get our enviromentscripts to handle Oracle easy
su - oracle

git clone local
cd local/etc
ln -s

su - oracle

We are ready to install Oracle Software 12c-23c now

su - oracle 
dbsw_install dbs 19.EE 19.EE 

mv $ORACLE_HOME/Opatch $ORACLE_HOME/Opatch_old

cd /app_oracle/local/tools
unzip -d $ORACLE_HOME/

su - root


We are ready to install Oracle Database 12c-23c

oranetinit [-f] [-L LDAP-server:port[:ssl-port] -L ...] sqlnet-domain wallet-password sys-password rman-password

oranetinit Sx123.45 sx123 sx123

And now DB Environment default settings:
mkoradbenv CDB 19.EE 1521

We ship some Scripts to handle the environment now for example:

chenv CDB -- change the enviroment settings
cd scripts

CAREFULLY: edit and change accordingly

init0.ora (change sqa blocksize aso...) 
changed: db_reco_file_dest to 3G

mkoracdb.par (plugdb add a name and a Pluggable DB will be created within the container and the listener will be notified to add a service_name)
changed: redosize = 100M und logmode = archivelog
mkoracdb.sql (only if required)

Create Database: using the command mkoracdb the database will be created as defined in the scripts from before.
It is advised to run this in a screen session as the process can take quite a while.


Now you have to wait a little but.. until this the Database is installed

You can check the installation with


Be happy (c) Sphinx


Requirement: Every table of any schema should be editable.

  • Choose a schema
  • Choose a table
  • Select any columns
  • Generate an editing mask

So, I would say it’s something dynamic, but it works… see for yourself:

Imagine a scenario where you have a database with multiple schemas, each containing various tables and columns. Now, let’s say you have a requirement where you need to allow users to edit the data in any table of any schema. This can be quite a challenging task, considering the dynamic nature of the database structure. However, with the right approach, it is definitely achievable.

To begin with, the first step is to select the desired schema. This can be done by providing a dropdown or a list of available schemas for the user to choose from. Once a schema is selected, the next step is to choose a specific table within that schema. Again, you can provide a dropdown or a list of tables available in the selected schema for the user to pick from.

Now comes the interesting part. The user should be able to select any columns they want to edit within the chosen table. This can be done by displaying all the columns of the selected table and allowing the user to check or uncheck the ones they wish to include in the editing process. This flexibility gives the user full control over the data they want to modify.

Once the user has made their column selections, it’s time to generate an editing mask. The editing mask is a dynamic interactive grid that displays the selected columns as editable fields. It provides a convenient way for the user to modify the data within those columns. The mask should reflect the data type and constraints of each column, ensuring that the modifications are valid and consistent with the database schema.

By following these steps, you can create a dynamic solution that allows users to edit data in any table of any schema. This kind of flexibility empowers users to manage and manipulate their data efficiently, making your database application more user-friendly and versatile.


© 2023 Sphinx Blog

Theme by Anders NorenUp ↑