Database

This is my submission for JoelKallmanDay, a community day planned by Tim every year on October 15th as a tribute to Joel Kallman. It is an old story that I am sharing. There was a customer that was using a Loan Management application and the back end was an Oracle database (a single instance, if I remember correctly). The hardware they were using was nearing EOL and they got an Exadata and moved the database there. They had high expectations and were expecting the application to be blazingly fast; it was a brand new Exadata, after all. But the experience was rather disappointing. There were application hangs, slowness and user complaints. When we (as a team) looked at the AWS/ASH reports from the system, there were things that needed immediate fixes. There were queries doing full tables scans and returning only a few rows. Then there were sequences where the cache size was set to zero. It appeared that the application was never tested with a RAC database. Creating the missing indexes and adjusting the cache sizes of the frequently used sequences appeared to be a low hanging fruit and it actually was. Fixing these two things gave a reasonable amount of relief. Of course, there was more that needed to be done to bring things on the track. ...

(Image from here) In part2, I summarized the Exadata platform based options available for DB deployment on OCI. In this post I will talk about the options available for deploying MySQL on OCI. MySQL from Oracle is available in two different flavours: Community Edition which is Open Source and two types of paid editions Standard and the Enterprise Edition. Enterprise Edition comes with additional features like TDE, Enterprise backup, HA etc. Both the Standard and the Enterprise Editions come with Oracle Premier Support. As it is with the Oracle database, MySQL can also be run as an IaaS option where you provision a compute instance, attach some block storage and install/configure the edition you need. There are options available on OCI Marketplace as well. ...

I was thinking about this while sitting in Vivek’s session at OCYatra in Gurgaon. He was talking about the techniques one can use to optimize badly performing SQLs. But there does exist the other side of this story i.e. throwing hardware at a performance problem to fix it. Of course, it works till a point only and it can’t solve every damn performance problem out there. But it is relatively easier to do (costs money, though) and depending upon where you are hosting your workloads, can be quicker too. You see CPU usage going up or users complaining, simple thing to do is to throw more cores at the database. If the system is CPU starved, it is going to give some temporary relief and you can control the situation. Same can be the case with the storage IOPS. In some extreme cases, you could even migrate the database to a more powerful hardware. With systems like Exadata being around which can make even a bad SQL do fairly well, this lever becomes even more powerful. But is it sustainable or is it the right way to deal with these kind of issues? ...

In part1, I summarized the options available for DB deployment on OCI and described the Base Database Service. In this post I will talk about the second group i.e. Exadata platform based services in detail and explore the possible deployment scenarios. When it comes to Exadata based options, it get a little complex as there are multiple ways to do it. To reiterate, there are two ways the deployment can be done. One is in the public cloud and another one is the private cloud (Exadata Cloud@Customer aka ExaCC) where the hardware is deployed in the customer’s data center. Let’s now explore the different offerings. ...

So, you are looking to run a database on Oracle Cloud Infrastructure (OCI), right ? Great ! But then you login, and suddenly you are faced with a whole range of services that can feel a bit…overwhelming. It’s not always obvious which one’s the right fit, especially for someone new to the OCI database landscape. That’s why I wanted to start this series of posts where we will walk through all the options that OCI throws at us for deploying databases. ...

Last week, got this issue reported by a DBA that he wasn’t able to su to oracle user from root on a Oracle Base Database VM in OCI. The login of opc user worked fine and he could do sudo su to root but he couldn’t su to oracle. When he did it just came back to root shell. [root@xxx ~]# su - oracle Last login: Fri Jan 12 10:20:38 UTC 2023 [root@xxx ~]# There was nothing relevant in /var/log/messages or /var/log/secure. I tried it for some other user and it worked fine. Then I suspected something with the profile of oracle user and voila ! The .bashrc looked like this ...

I was installing Database Firewall version 12.2.0.11.0 on a Dell x86 machine (with 5 * 500 GB local HDDs configured in RAID 10) and it got successfully installed. Later on, I came to know that this version doesn’t support host monitor functionality on Windows hosts. The latest version that supports that is 12.2.0.10.0. So that was the time to download and install 12.2.0.10.0. The installation started fine but it failed with an error: ...

It is actually a dumb one. I was disabling triggers in a schema and ran this SQL to generate the disable statements. (Example from here) HR@test> select 'alter trigger '||trigger_name|| ' disable;' from user_triggers where table_name='PRODUCT'; 'ALTERTRIGGER'||TRIGGER_NAME||'DISABLE;' -------------------------------------------------------------------------------- alter trigger PRICE_HISTORY_TRIGGERv1 disable; HR@test> alter trigger PRICE_HISTORY_TRIGGERv1 disable; alter trigger PRICE_HISTORY_TRIGGERv1 disable * ERROR at line 1: ORA-04080: trigger 'PRICE_HISTORY_TRIGGERV1' does not exist HR@test> WTF ? It is there but the disable didn’t work. I was in hurry, tried to connect through SQL developer and disable and it worked ! Double WTF ! Then i spotted the problem. Someone created it with one letter in the name in small. So to make it work, we need to use double quotes. ...

This is an Exadata machine running GI version 18.3.0.0.180717 and DB version 12.1.0.2.180717. On one of the DB nodes while running dbca, it doesn’t list the diskgroups. it works fine on the other node. I cheked the dbca trace and found that the kfod command was failing. I tried to run it manually and got the same error: [oracle@exadb01 ~]$ /u01/app/18.0.0.0/grid/bin/kfod op=groups verbose=true KFOD-00300: OCI error [-1] [OCI error] [Could not fetch details] [-105777048] KFOD-00105: Could not open pfile 'init@.ora' [oracle@exadb01 ~]$ I ran it with strace then: ...

Scenario : Setting up a physical standby from Exadata to a non-Exadata single instance. tnsping from standby to primary works fine but tnsping from primary to standby fails with: [sql]TNS-12543: TNS:destination host unreachable[/sql] I am able to ssh standby from primary, can ping as well but tnsping doesn’t work. From the error description we can figure out that something is blocking the access. In this case it was iptables that was enabled on the standby server. ...