Oracle


Swiss OUC – Database Circle Conference 2024

I had the pleasure to assist to the first Swiss Oracle User Community conference in Zurich the last two days. Great event the the Oracle offices in the “Circle” at the Zurich Airport. It was opportunity to see some ex-Trivadis colleagues – Ludo, Frank, Dani and meet all the Oracle conference addicts.

The two parallel sessions were creating me every hour a dilemma: which presentation will I miss.

Here some of the highlights I noticed – this are what for me was important or new, certainly I will not cover all the knowledge that was in the air.

Swiss OUC day 1

Dominic Giles (Oracle) gave an overview of 23c new Generative AI features and more:

  • New datatype VECTOR for AI data models was introduced and delayed the 23c release. It allows to use ML models and calculate distance between vectors using different methods.
  • AI Assist will soon allow to use natural language to create SQL specific for the data model of “our” database. Will also be available on premises – similar but easier than “Select AI” from Autonomous DB
  • SQL Developer extension for VS Code allows using DB connections directly on VS Code – already available:

Martin Klier explained well and visually how networks, subnets, routers work and what happens when the way to the destination is cut. Really good reminder.

Øyvind Isene shared his story of being the sole developer of an DB automation API leaving the following messages:

  • “You don’t have the time to do all the errors again. Learn from other errors.” –> share your errors with others
  • Use Logger when scripting; Use ORDS for DB automation

Neil Chandler reminded some Oracle Security fundamentals, like: always enable Oracle Network Encryption in sqlnet.ora and add Password Verify Function to default database profile.

Again Martin Klier said that Performance is rarely result of an accident. For this, when doing performance analysis, document every action you do, even if will just show that the problem is/was not there; do not use averages and pay attention to skewness. 

Chris Saxon and Jasmin Fluri did a small theater play around SQL version management and explained why they use either use Flyway or Liquidbase (integrated into SQLcl) for that.

Swiss OUC day 2

Chris Saxon on a plenum session did a example based overview of the 23c new features for developers:

  • new DB_DEVELOPER_ROLE – combination of CONNECT+RESOURCE+some other privs.
  • create if not exists, drop if exist DDLs to avoid errors on multiple execution (might hide things with create if exists, but different)
  • No more need of “from dual”
  • Domain datatype extensions – allow named column types with integrated constraints and defaults used schema wide, with extension to have a display and order specific functions (for instance INTERVAL DAY datatype that displays in minutes)
  • Annotations for columns/tables/domains – kind of extension of comments in name-value pair format
  • Multiple insert values in a single command – also helpful to generate data
  • Group by using column alias
  • sum/floor/ceil/avg on interval and other datatypes
  • fuzzy_match function to replace like when looking  for similar results (without need of Oracle Text)
  • json converted to insert
  • boolean usage everywhere (eg: function returns boolean then possible to do “select … from … where func_name(input)”
  • SQL_TRANSPILER parameter injects content of function directly in SQL, allowing the optimizer to better improve query (default false)
  • GRANT … ANY … on Schema
  • Remind to use NOAUTHENTICATION for owner schema and proxy users instead of share passwords.

Mike Dietrich went on with 23c new features on Data Pump:

  • New dumpfile format with special functionalities for OCI Object Storage
  • INCLUDE and EXCLUDE can be combined in the same impdp/expdp
  • All transportable export/import can run now in parallel
  • Checksum for dumpfiles
  • REMAP_TABLESPACE=%:USERS (wildcard usage)
  • Recommendation to gather SYS+SYSTEM schema stats instead of using “gather_dictionary_stats”
  • Always install DATAPUMP merge patch. It is not part of an RU because it it not RAC Rolling installable

Neil Chandler shared the story of a bank that wanted to replace end-of-life Exadata with something cheaper. Neil spend some time investigating all resource usage – mainly the CPU from storage servers + saved IO from cell offloading and then calculating costs. Conclusion: if they want to keep the performance, there is no cheaper option by using commodity hardware or open source DB.

Frank Pachot on a tmux terminal session explained that the ‘load’ high values in the ‘top’ command from Linux do not mean the machine is overloaded. Basically my get ways were :

  • There is in recent kernel the PSI tool, not enabled by default, which can show better weather there is pressure and where this is happening.
  • Different types of IO – specifically a type of direct io can count for the load avg. 

Erik van Roon is a fan of SQLcl and convinced the audience by showing why that is superior to SQL*Plus. Check many of its features here or here or here.

Fernando Simon moved from Exadata to ExaCC and shared his thoughts about the new experience:

  • ExaCC offers good automation, click type of actions for inexperienced DBAs
  • ExaCC lacks basic checks like if there is enough space to backup OH before a patch [why it backups OH?, does not do out-of-place?]
  • No integration of created OH images
  • CPU scale up works well and is immediate, but scale down has a hard minimum wait time of 2h since last scale up
  • There is no documented tool that allows create a DB with all parameters (block size, charset, silent mode with password)
  • Even Zero Downtime Migration tool makes as workaround to create a dummy DB, drop it manually and create manually again using DBCA. DBs created directly with DBCA, will not be seen in the OCI, there is no ‘discovery’ command
  • No possibility to use DBs with different domain in the name in the same ExaCC without unofficial workarounds
  • ExaCC makes all admin operations serial which can be slow

Dani Schneider showed the new SQL Analysis Report and how it allows to get hints of bad sql when running explain plan.

Kamil Stawiarski presented “Jas-min” – a word play with the name of one conference organizer – JSON AWR Statspack Minier. And how this tool can help in cases like you have flashback queries and cursor: pin s wait on x. Basically, do not use use flashback queries in the application if the query will be use concurrently

Thanks a lot Gianni, Jasmin and Julian from Swiss Oracle User Community for this great conference. Hope to see you again next year!


Nice table for Oracle OLVM: VMs, hosts, clusters and vcpu pinning

On my client infrastructure, for improved performance, we need to make sure each VM is pinning to different physical cores.

We do the changes for the moment using the web interface. However, to read easily what the current status is, I’ve wrote a python script that draws a nice table showing where each VM is running and to which cores its vcpu are pinned.

The result is something like this:

(more…)

Get history of Switchover and Failover of an Oracle database

For physical standbys, there is no DBA_* view that I know which tracks the history of switchover and failover. For Logical standby there is DBA_LOGSTDBY_HISTORY.

Ways to find the information are the Dataguard Broker log – when Broker is in use -, or using the database alert log and the following grep command:

$ grep -EB1 '^ALTER DATABASE (SWITCHOVER|FAILOVER) TO' alert_*.log

2023-03-06T14:12:38.905705+01:00
ALTER DATABASE SWITCHOVER TO 'mydb2'
--
2023-03-06T14:37:43.209653+01:00
ALTER DATABASE FAILOVER TO MYDB1
--
2023-03-06T14:38:03.352141+01:00
ALTER DATABASE SWITCHOVER TO PRIMARY (MYDB1)
--
2023-03-11T16:00:22.224218+01:00
ALTER DATABASE SWITCHOVER TO 'mydb2'


My ARDCI cheatsheet

Check last problems

adrci> show problem -last 10
adrci> show incident -last 10

adrci> show incident -p "incident_id=180781" -mode detail

Create Package

adrci> ips create package problem 5
adrci> ips generate package 1 in "/tmp"

Purge

Value for control is hours

adrci> show control
adrci> set control (SHORTP_POLICY = 720)   /* 30 days */
adrci> set control (LONGP_POLICY= 8760)   /* 1 year */

Value for purge is minutes

adrci> purge -age 1440  /* 1 day */

Fix “No ADR base is set”

Sometimes also “DIA-48494: ADR home is not set”

SQL> select value from v$diag_info where name='ADR Base';
VALUE
------------------
/u01/app/oracle

Create a ${ORACLE_HOME}/log/diag/adrci_dir.mif file with:

$ mkdir -p ${ORACLE_HOME}/log/diag/
$ printf "%s\0" "/u01/app/oracle" > ${ORACLE_HOME}/log/diag/adrci_dir.mif

DIA-48448: This command does not support multiple ADR homes

Use set home diag/xxxxx (remove the ORACLE_BASE path)

adrci> show problems -last 10

ADR Home = /u01/app/oracle/diag/rdbms/mydb1/MYDB1:
...

adrci>  set home diag/rdbms/mydb1/MYDB1

Opatch now obfuscates its own backups – the new “opatch util Obfuscate” option explained

With OPatch version 12.2.0.1.36 for databases (and version 13.9.4.2.11 for Middleware), a new utility was included: obfuscate.

This utility was released to workaround the increased security needed around databases servers. We cannot escape having vulnerability scanners to run there. These vulnerability scanners sometimes do not distinguish between used and unused files.

When patching a database, backup copy of the modified files are kept in $ORACLE_HOME/.patch_storage. Their hash sometime trigger the vulnerability scanners and says – 🚨server not patched ⚠️. Which is misleading.

Starting with OPatch 12.2.0.1.36, released together with the January 2023 Release Update, the backup of patch files are automatically obfuscated.

The new “opatch util obfuscate” allows to do the same for older patches. Let’s see how it works.

Read More

New “OPatch util DeleteInactivePatches” tool for reducing the $ORACLE_HOME size

When you are a sensible person, you patch regularly your Oracle database. After a few years, maybe you noticed that the $ORACLE_HOME increased size from new ~7 GB to …. 26 GB!

$ du -hs /u00/app/oracle/product/19.18.0
26G     /u00/app/oracle/product/19.18.0

Looking better, you find the culprit is the hidden directory $ORACLE_HOME/.patch_storage :

$ du -hs /u00/app/oracle/product/19.18.0/.patch_storage
17G     /u00/app/oracle/product/19.18.0/.patch_storage

Today I show how a new option from OPatch allowed me to regain 11 GB of disk space in a couple of minutes. In a supported way.

Last year I had already presented other options to recover space from Oracle Patching. Below I remind them, but todays post is about the latest and improved way that OPatch tool has to keep only the usual needed patch backups.

Read More

How to workaround Oracle Text primary key limitations (and DRG-10528)

One of my clients had a quite easy desire to try Oracle Text for an existing application. Oracle Text allows to use “standard SQL to index, search, and analyze text and documents stored in the Oracle database, in files, and on the web”.

It seemed simple, until we tried to implement on table named after the city where I studied: T_COIMBRA

create table T_COIMBRA (COL_ID timestamp not null, COL_TXT varchar2(100));

create unique index COIMBRA_IDX_ID on T_COIMBRA(COL_ID);

alter table T_COIMBRA add constraint PK_COIMBRA primary key (COL_ID) using index COIMBRA_IDX_ID;

create index COIMBRA_IDX_TXT ON T_COIMBRA(COL_TXT) indextype is ctxsys.context;

Nothing special it seems. But we get an error:

create index COIMBRA_IDX_TXT ON T_COIMBRA(COL_TXT) indextype is ctxsys.context

Error report -
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10528: primary keys of type TIMESTAMP(6) are not allowed
ORA-06512: at "CTXSYS.DRUE", line 186
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 320
29855. 00000 -  "error occurred in the execution of ODCIINDEXCREATE routine"
*Cause:    Failed to successfully execute the ODCIIndexCreate routine.
*Action:   Check to see if the routine has been coded correctly.

Below I show how to workaround this and keep an “unsupported” column as unique identifier of the table.

Read More

Can ChatGPT steal our (Oracle DBA) job?

I would not say it will steal our job, but it can facilitate in some cases to write small pieces of code without having much to think. Well, help first to be lazy, before getting our job. Last example today:

Request ChatGPT to write a trigger that enables BCT after the database opens.

The power is amazing. After looking at the result, you think about a small detail, ask again and it just answers:

Ask ChatGPT to improve so it triggers only when a switchover happened.

But one must always test carefully the answers. Like this case, where the EVENT_TYPE column does not exist on V$DATAGUARD_STATUS table. No idea how he discovers it. I also got non existing syntax and complex solutions that could be easily made simpler.


The OPatch, .patch_storage and its space issues: the solutions!

[a new solution is available as of 2023. Read here about the new OPatch util DeleteInactivePatches option.]

I love database patching and apart of the tiring coordination work or the applications that keep to not automatically reconnect to the database, all is usually perfect and issue free. Well, almost. The most common error are space issues.

You can try to follow the Oracle guidelines and have a 100 GB partition for the $ORACLE_HOME(s). Initially it only uses 7 or 8 GB per home, but after few years you are fighting against the space pressure.

There are several strategies to prevent or act against this space problem when patching:

Solution 1 – Recreate separate Oracle Home from scratch

It is a clean solution, when you make really from scratch, meaning no home cloning, no opatchauto apply -outofplace, and then apply only the latest patch there. This solution is quite easy to do for DB home. However when you have Grid Home this is a bit more hassle.

Solution 2 – Use the opatch hidden “archive” feature

This feature allows to move out from .patch_storage folder some patches in a zip format. It was “documented” by Mike Dietrich in his blog. Unfortunately to have a common archive between different Oracle Homes you need to do some hand work: archive on one Oracle Home, delete the patches from remaining homes and copy the $ORACLE_HOME/OPatch/.patch_storage/.patch_archive_mapping.xml file to the other homes. Of course this works when all Homes have exactly the same features installed and patches. Keep in mind that before rollback you need to use the “unarchive” option and that the rollback procedure will restore the files that were changed, and this can vary depending on the state of the oracle home at the moment of patch. Use opatch util archive -help for more info.

Here we are not saving any space, just moving the problem away. The other partition can be a remote slower location, but the patching will also then be slower, as it will need to copy files there. Use: TARGET=<partition>/patch_storage ; mv $ORACLE_HOME/.patch_storage $TARGET ; ln -s $TARGET $ORACLE_HOME/.patch_storage

Solution 4 – Remove unneeded patches from .patch_storage

In $ORACLE_HOME/.patch_storage the whole history of patches you applied is kept. You can rollback one after the other and bring the Oracle Home several steps behind. However, the most of the cases you are ok to just be able to move one step backward. The older history of the home is past. If that is you case, then there is this nice Python script clean_patch_storage.py which is based on the premise of Oracle Doc ID 550522.1 which states you can “remove all the sub-directories from $ORACLE_HOME/.patch_storage that are not present in the list of installed patches”. The list directories you can delete is exactly what the script do.


Maybe you have other solutions or tricks, please share in the comments.


Useful aliases for ODA Patching

Patching an Oracle Database Appliance can be tiring. The ODA patching commands are quite long and there is always a jobid to check… So I just created a set of alias that make these tasks a bit easier:

# List jobs run today - $ jt
alias jt="odacli list-jobs -o $(date +%Y-%m-%d)"

# Describe one job - $ j <id>
alias j="odacli describe-job -i $1"

# Describe last job created - $ lj
alias lj='odacli describe-job -i $(odacli list-jobs -tl 1 | sed -n 4p | cut -d" " -f1)'

# Describe last prepatch report - $ lpr
alias lpr='odacli describe-prepatchreport -i $(odacli list-jobs -tl 1 | sed -n 4p | cut -d" " -f1)'

# Show free space of / /u01 and /opt - $ dff
alias dff="df -h / /u01 /opt"

# Describe components - $ comp
alias comp="odacli describe-component"

# Tail -f DCS Agent log - $ tal
alias tal="tail -f /opt/oracle/dcs/log/dcs-agent.log"

Maybe you have other suggestions?