This blog is to share Oracle database administration experience about building complex systems, solving problems, and reviewing some technical methods that may be not so popular. This blog describes my thoughts about Oracle that can be not the same as the official Oracle inc. opinion.
Intel CPU were the best for the Oracle workload, but the serie of the performance/security issues are haunting x86 world and Oracle Inc. started looking to the other direction. I didn’t see much AMD processors used for Oracle databases and, with the understandable companies’ policy of forbidding benchmark results share, we don’t know much if it’s any good. I also have a feeling that most of the companies don’t even care of the one-thread performance of the database, the real game starts only when there is a parallelized performance needed. And here Oracle database was always good at, but with the licencing cost, that is huge. With 1 Oracle CPU license you will get 4 real Ampere CPU cores or only 2 real x86 cores running 4 virtual threads, that are not that efficient as the cores without SMT (Hyper-threading for Intel). If you count your money (read as “don’t use ULA”), then you have to start testing Ampere (ARM). And here how we can migrate.
Oracle made this extremely simple with a help of the multitenant option (even the free one) or cross-platform data guard. I was going to find some issues on the way, but I failed to do this. So, basically, I’ll describe some options to do this:
This is just a short note about installing Oracle Database 23c Free in OCI Always Free instance with 1 OCPU and 1 Gb of RAM. For further tests I needed an “outdated” (winks) CPU architecture x86, otherwise I’d go directly with the ARM Ampere available now for free the second instance (the first one I already have).
The main issue with the Always Free instance of 1 Gb RAM and Oracle Database 23c Free (that’s minimal requirement is also 1 Gb of RAM), is that the host 1 Gb is not 1 Gb on the guest VM (it’s about 938Mb for OEL 8), that creates some issue with the “strict” Oracle 23c Free requirement of 1024 Mb.
start from installing oracle-database-preinstall-23c
alter system set db_create_file_dest = ‘/opt/oracle/oradata/FREE’ scope=both;
create pluggable database test admin user admin_user identified by “megaSecretPassw0rd”;
alter pluggable database TEST open;
This should do the trick.
Footnote
I also decreased database parameter processes from 300 to 100, this worked fine and should be enough for my load.
I also tried to change parameter threaded_execution to TRUE, that failed drastically with no meaningful trace files or the error code (theoretically this should’ve reduce resource consumption, but it just didn’t work).
Anyway I’m not going to use this instance, but I’m interested in the software install to create an instance manually, but it will be later. Have a good weekend!
This is the second article describing different containerization options for Oracle Database. The first one was under the “Oracle Database 21c running in Docker container” title. Please get familiar with it as I will omit the detailed description of the steps described there. This time I’ll describe a way to run Oracle Database 21c in the Kubernetes cluster, specifically in its Microsoft Azure version known as AKS. I still don’t pursue any production like load and even use in CI/CD. The main target here is to describe for Oracle DBAs how to deal with a Kubernetes cluster (k8s). This article will also include a few AKS commands for cluster deployment and final destruction as we don’t want to waste our Azure bucks.
This is a short note, that starts a serie of articles related to any sort of containerizations of Oracle Databases. At first this is just a play with Docker, that actually can be used for building development environment, but also already this note will reveal few important concepts.
First and the most importantly you have to go to container-registry.oracle.com sign-in there, go to database section (Database), choose Enterprise section and accept its license agreement. If you did, the database section should look similar to:
If you won’t do this, you won’t be able to download the container image.
You also have to login from Docker (I assume you have it already installed) into the remote repository (“winpty” is being used, because I’m on Windows and tty console is needed):
Now for Docker we need to create a volume, where data will be stored:
docker volume create --driver local orcl1_OracleDBData
In my case I’m using Docker Desktop on Windows, that spawns HyperV VM (won’t work on Windows Home Edition and maybe some others), so the orcl1_OracleDBData actually resides withing the VM. Other hypervisors would bechave similarly.
Now we just create/run a container:
docker run -d --name orcl1 -v orcl1_OracleDBData:/opt/oracle/oradata container-registry.oracle.com/database/enterprise:21.3.0.0
That will show you a progress of container deployment, but after it’s finished go to the container logs and wait for the database get created and opened:
docker logs orcl1
Theoretically the above should give you a sys/system/etc. passwords, but I didn’t find this information. Anyway it’s safer and more controllable to reset password and I will do this not a DBA way, but DRE way and you will understand why:
$ read TARGET_PWD
$ docker exec orcl1 ./setPassword.sh $TARGET_PWD
The Oracle base remains unchanged with value /opt/oracle
SQL*Plus: Release 21.0.0.0.0 - Production on Thu Mar 9 14:36:49 2023
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL>
User altered.
SQL>
User altered.
SQL>
Session altered.
SQL>
User altered.
SQL> Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
“setPassword.sh” changes three passwords (I didn’t check, but my guess is: SYS/SYSTEM/PDB_ADMIN).
Now, let’s restart (actually remove the old one and start a new) the container, to show that changes are preserved (I’ve also added a port parameter to expose listener outside):
And now we can connect from outside with the specified password, proving that changes are persistent and checking if it’s a Multitenant database:
sql sys/$TARGET_PWD@localhost:1522/ORCLCDB as sysdba
SQLcl: Release 22.4 Production on Thu Mar 09 15:55:58 2023
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL> sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
_________ ___________ _____________ _____________
2 PDB$SEED READ ONLY NO
3 ORCLPDB1 READ WRITE NO
This is it for now. I already have note for running this in Kubernetes (AKS), so will post soon.
Recently, after finishing Oracle 23c Beta documentation, I redeveloped my trust in refreshable PDBs. My first attempt to try them failed, due to some specific cases where data files are stored on different tiers of disk groups and refreshable PDB doesn’t handle this automatically including for the standby side (target standby). This time I decided to check how Refreshable PDB works with encryption (TDE).
The idea behind this is the ability to migrate databases. I personally don’t like DataPump, at least when it’s being used for not solving DBA tasks like structure change, partial migrations, rare forms of migrations, and probably it’s it (do you have more?).
It all started from the idea to have our own PDB$SEED like databases, but in one place and preconfigured according to our standards (tablespace, limits, privileges, database links, etc..). The one obvious problem on the way to implement this was the fact that we are using both Enterprise Edition (EE) and Standard Edition (SE) databases and we are lazy enough to repeat the same configuration for twice more places than we would like. The basic idea of cloning EE PDB to the SE CDB is based on the fact, that almost empty database doesn’t use user specific licensed features that can be a problem while moving from one edition to another.
Oracle Enterprise Manager is not very well designed for ignoring repeating incidents from Oracle RDBMS, that are somehow Ok for the application (can be ignored) or even they are not Ok, but the investigation with Oracle support lasts for months and there is no workaround.
There are some tricks however for OEM to limit amount of notifications like: “Associated with incident” and “Associated incident acknowledged”, but they are all based on the fact, that Oracle EM manages to associate event with the existing incident, that is imposible for the incidents caused by Oracle RDBMS scheduler / jobs.
Exadata DB node has wonderful option to send you notification if SMTP configuration is filled with proper information. The problem is that starting from some recent version it started to check ADR RDBMS home for incidents also. For me this is not a job for Exadata checking tools, it must be done by OEM where it can be more or less properly routed and handled. Exadata checking tools doesn’t have any filtering mechanism for its ADR check routine, that leads to unnecessary spam for known or already managing issues.
This type of alerts is generated with “/opt/oracle/dbserver/dbms/deploy/scripts/unix/mscore/get_db_adr_info.sh”. To get rid of RDBMS home checking the following change is needed:
It’s been around for a while and most of you rather don’t need it as you have your own automation for patching, but to not get rusty I tried this approach and found few problem of it.
Well, this is one of the most popular interview question for Oracle we are asking (it’s not this straight forward, but for my PostgreSQL level I’ll keep it simple).
The main goal here is to write down basic commands, so I’m leaving aside all management and most of the monitoring questions.
I’d like to say right away that at first I didn’t manage to build usable standby from Windows to Linux due to the default collation that is being used by PostgreSQL on Windows (English_United States.1252) that doesn’t exist in Linux (building it by myself was a bit out of an option, but also a solution), so as a result the replication did start first time, but I wasn’t able to connect to the standby.
Oracle database experience 