Can ChatGPT steal our (Oracle DBA) job?

This entry was posted in DataGuard ideas Oracle on by

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.

Root mailbox huge on Exadata after upgrade to OL7

This entry was posted in Engineer Systems Exadata on by

Finally I got to work a bit more with an Exadata. And finding its unknown “features”.

Our monitoring just detected that “/ diskspace 6% free (1.9/23.5 GB) (DiskWarning)”. Looking for the guilty folder or file, I end up here:

# for d in /*; do egrep " ${d} " /proc/mounts > /dev/null || du -sh ${d}; done
...
15G     /var

# cd /var/
# du -hs * | sort -h
...
12G     spool

# cd spool/
# du -hs * | sort -h
...
12G     mail

# cd mail/
# du -hs * | sort -h
...
12G     root

The huge file was /var/spool/mail/root – the mailbox of the root user.

Trying to open it, just created another small problem:

# mail
/tmp: No space left on device

(parenthesis here: on this Exadata VM we have DBFS running. The check done by the CRS for the DBFS – official mount-dbfs.sh script -, also writes to /tmp. If /tmp is full when the DBFS check runs, it fails, and the clusterware moves DBFS to another node. Goldengate, which was using DBFS, just crashed)

The root mailbox is just a text file, so using other tools I saw that is full of emails that come from “0logwatch”. This matches something in cron.daily:

# ls -l /etc/cron.daily/

-rwx------ 1 root root  408 Apr 15  2022 0logwatch

Looking on Metalink, quickly we end up on this note:

Output of Daily Cronjob 0logwatch Sent to Mail instead of Saving to File on Oracle Linux 7 (Doc ID 2564364.1)

The problem comes from an Exadata update, which did not care about syntax change of the /etc/logwatch/conf/logwatch.conf file with OL7. What is a pity, is that later Exadata patches did not seem to care of fixing that.

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

This entry was posted in OPatch Oracle Oracle 19c on by

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.

Solution for ODA 19.13 list-availablepatches error

This entry was posted in Engineer Systems ODA on by

After patching an ODA to 19.13, often we have the error

[root@oda-01 DB1]# odacli list-availablepatches
DCS-10001:Internal error encountered: For input string: "".

The workaround provided by Oracle on the 19.13 “Known Issues” is incomplete and might not solve the problem in many cases.

The complete solution is to change all patchmetadata.xml files where this “targetVersion” is missing. Here is the sed command that creates a backup (extension .bck) and adds the missing bit:

sed -i.bck -E 's/name="DB" repotag="(1[0-9]\.[0-9]{1,2}\.[0-9]\.[0-9]\.[0-9]{6})"><\/component>/name="DB" repotag="\1" targetVersion="\1"><\/component>/g' /opt/oracle/oak/pkgrepos/System/*/patchmetadata.xml

Now, odacli list-availablepatches is back to normal:

[root@oda-01 DB1]# odacli list-availablepatches
-------------------- ------------------------- -------------------------
ODA Release Version  Supported DB Versions     Available DB Versions
-------------------- ------------------------- -------------------------

19.6.0.0.0           19.6.0.0.200114           Clone not available
                     18.9.0.0.200114           Clone not available
                     12.2.0.1.200114           Clone not available
                     12.1.0.2.200114           Clone not available
                     11.2.0.4.200114           Clone not available

19.13.0.0.0          21.4.0.0.211019           Clone not available
                     19.13.0.0.211019          19.13.0.0.211019
                     12.2.0.1.211019           Clone not available
                     12.1.0.2.211019           Clone not available

19.10.0.0.0          19.10.0.0.210119          Clone not available
                     18.13.0.0.210119          Clone not available
                     12.2.0.1.210119           Clone not available
                     12.1.0.2.210119           Clone not available
                     11.2.0.4.210119           Clone not available

18.8.0.0.0           18.8.0.0.191015           Clone not available
                     18.8.0.0.191015           Clone not available
                     12.2.0.1.191015           Clone not available
                     12.1.0.2.191015           Clone not available
                     11.2.0.4.191015           Clone not available

18.5.0.0.0           11.2.0.4.190115           Clone not available
                     12.1.0.2.190115           Clone not available
                     12.2.0.1.190115           12.2.0.1.190115
                     18.5.0.0.190115           Clone not available

18.4.0.0.0           11.2.0.4.181016           Clone not available
                     12.1.0.2.181016           Clone not available
                     12.2.0.1.181016           Clone not available
                     18.4.0.0.181016           Clone not available

18.2.1.0.0           11.2.0.4.180417           Clone not available
                     12.1.0.2.180417           Clone not available
                     12.2.0.1.180417           Clone not available

12.2.1.4.0           11.2.0.4.180417           Clone not available
                     12.1.0.2.180417           Clone not available
                     12.2.0.1.180417           Clone not available

Useful aliases for ODA Patching

This entry was posted in Engineer Systems ODA Oracle on by

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?

gDBClone – Good and easy Oracle cloning tool with potential

This entry was posted in cloning DataGuard Oracle on by

Instead of writing and adapting cloning scripts for each client, I was looked today at gDBClone script, provided by Oracle note gDBClone Powerful Database Clone/Snapshot Management Tool (Doc ID 2099214.1).

Ruggero Citton did an excellent work and I believe that for most of situations and mainly in a development environment with the need of fast DB Snapshots this tool provides an alternative to developing new tools.

I find great that for database cloning it creates a temporary listener, and does not mixup with existing configuration. Another great thing is the possibility to use a pre-created passwordfile to clone remote DBs, without the need to provide any password at run time (or hardcode, or having a wallet).

It is a tool that works with DBs from Oracle 11.2 up, and also with RAC, RAC One node, it can perform upgrades, create standby, it works in ODA, etc.

The long set of pre-checks is very nice:

MacroStep1 - Getting information and validating setup...
INFO: 2022-01-13 15:40:09: Validating environment
INFO: 2022-01-13 15:40:09: Checking superuser usage
INFO: 2022-01-13 15:40:09: Checking if target database name 'xpto' is a valid name
INFO: 2022-01-13 15:40:09: Checking if target database home 'OraHome3' exists
INFO: 2022-01-13 15:40:09: Checking if Oracle Restart
INFO: 2022-01-13 15:40:09: Checking ping to host 'server27'
INFO: 2022-01-13 15:40:09: Getting ORACLE_BASE path from orabase
INFO: 2022-01-13 15:40:09: Checking if target database 'xpto' exists
INFO: 2022-01-13 15:40:09: Checking registered instance 'xpto'
INFO: 2022-01-13 15:40:12: Checking listener on 'server27:1521'
INFO: 2022-01-13 15:40:12: Checking ASM command options
INFO: 2022-01-13 15:40:15: Checking if '+U02' is a valid ASM diskgroup
INFO: 2022-01-13 15:40:15: Checking '+U02' RDBMS compatible
INFO: 2022-01-13 15:40:16: Checking if '+U01' is a valid ASM diskgroup
INFO: 2022-01-13 15:40:16: Checking '+U01' RDBMS compatible
INFO: 2022-01-13 15:40:16: Checking if '+U01' is a valid ASM diskgroup
INFO: 2022-01-13 15:40:17: Checking '+U01' RDBMS compatible
INFO: 2022-01-13 15:40:20: Checking source and target database version
INFO: 2022-01-13 15:40:21: Checking source database size
INFO: 2022-01-13 15:40:23: Checking source database role
INFO: 2022-01-13 15:40:23: Checking source log mode
INFO: 2022-01-13 15:40:24: Checking Flash Cache setting
SUCCESS: 2022-01-13 15:40:24: Environment validation complete

At the client I’m these days however I would be happy with some more flexibility.

  • The database unique name convention include underscores (xptodb_2 for instance). gDBClone only accepts alphanumeric elements to the clone database name.
  • gDBClone needs to run with root privileges. You can (recommended) configure in /etc/sudoers, however this is far too much for my client.
  • Cloning a dataguard protected database configured with broker failed at the end of the duplicate, as the clone automatically started the broker and connected back to the primary, failing then with ORA-16649: possible failover to another database prevents this database from being opened . The half-baked clone needs to be dropped manually.
  • It would be nice to have commands (or be part of clone) to create new ACFS mountpoints

At the end I get the impression that gDBClone was developed for specific customer needs and I’m sure it does it really well. For the needs and restrictions I’ve in different clients unfortunately I still need to use my set of tools. Luckily with the most recent Oracle versions the cloning, snapping, upgrades has become much easier and scripting it does not require many lines of code.

Datapatch and Tablespace Full

This entry was posted in OPatch Oracle Oracle 19c on by

Yesterday on a simple 19.13 patching session, got a nice error during datapatch: ORA-01691: usable to extend lob segment in Tablespace SYSTEM ! Got a bit scared, but simple added more space to the tablespace, run datapatch again and all was ok. Maybe patch/datapatch should check also for free space in the SYSTEM Tablespace ?

Here the error:

DBD::Oracle::st execute failed: ORA-01691: unable to extend lob segment SYS.SYS_LOB0000323098C00008$$ by 128 in tablespace SYSTEM
ORA-06512: at line 2 (DBD ERROR: OCIStmtExecute) [for Statement "BEGIN
           INSERT INTO sys.dba_registry_sqlpatch_ru_info
             (patch_id,
              patch_uid,
              patch_descriptor,
              ru_version,
              ru_build_description,
              ru_build_timestamp,
              patch_directory)
           VALUES
             (:patch_id,
              :patch_uid,
              :patch_descriptor,
              :ru_version,
              :ru_build_description,
              TO_TIMESTAMP(:ru_build_timestamp, 'YYMMDDHH24MISS'),
              :patch_directory);
           COMMIT;
         END;" with ParamValues: :patch_descriptor=OCIXMLTypePtr=SCALAR(0x468b0a0), :patch_directory='PK........▒LOS▒.
▒. ..▒[..
...33192793.xml▒][.۸.~..
▒.v▒▒▒.x.▒▒q*.o▒▒*.'s▒▒▒(.-qL▒l.ji▒▒/@Im        8 .$.I▒<▒0j▒.>\.▒.▒~.▒▒▒K▒vE▒▒▒.~K..▒6▒▒b▒▒▒a▒?=F.▒..▒]▒\▒5▒▒▒▒.?|▒▒y4f▒▒{X▒▒▒..▒"▒▒|?d.▒..I▒.E.▒▒▒▒=▒▒▒▒▒..▒▒▒.▒▒5.Wg▒w.ۺ▒..▒v}Q%}.▒..▒▒.▒▒.-▒▒▒▒▒\%▒▒׿▒vE.▒▒xsU▒▒)%ħa@.r▒▒.▒Kۢ▒.▒}..y▒▒.~j2▒▒â.=▒▒▒.▒▒▒O.▒▒<▒wm▒▒▒d▒.▒vw▒▒▒▒....▒o޼.▒}▒▒▒.I.▒x▒.▒▒.▒▒▒▒.▒▒.▒x▒o▒)▒▒wo.vxۿ{|\▒,.i]5▒6▒▒..▒▒.b.g▒b▒▒7Ew▒▒▒▒./▒.▒▒o.▒/▒.▒.▒▒6▒#▒.▒.▒...▒▒..▒▒▒▒ߵ▒l▒▒▒▒...▒▒.▒.▒▒.a(▒.▒?|▒.▒
X▒▒{+▒▒v▒=.▒{▒▒▒▒9Íui▒▒.H.▒▒▒▒▒▒▒▒S.▒^▒|▒9.▒%▒,▒▒▒SU▒.▒. zZ.▒..c;.▒vحY▒B...▒▒▒▒/▒▒.▒▒▒▒.▒▒▒..H{.7}f.▒..▒j.▒mۿ▒.▒S.▒7▒.:§.▒+p▒%▒..w▒.▒.▒{7▒.▒k▒..▒6▒6▒▒>v▒bwMU▒..▒Y_▒+▒IG▒<.|4▒▒\▒4.▒i▒*▒▒;G▒-V.ε▒▒.p.▒▒▒▒
G̍1▒.▒]▒▒▒.1O▒v▒^.▒▒▒.▒:.Wy._q7▒▒.*▒&▒▒`/▒I߹...pW.
▒▒▒▒C.v▒j▒[▒▒.▒▒~▒▒.▒.bo▒▒.U▒..▒V▒▒
.`n▒▒N7▒.`nyup▒▒.w▒.]▒▒B▒.▒▒5D7▒.w.#w.▒.▒m▒▒ܬ▒.p▒.3p.▒▒.▒.l▒.▒▒Z;▒0.\bm▒J▒...', :patch_id="33192793", :patch_uid="24462514", :ru_build_description="Release_Update", :ru_build_timestamp="211004165050", :ru_version="19.13.0.0.0"] at /u01/oracle/db19s/sqlpatch/sqlpatch.pm line 5876, <LOGFILE> line 110.

New in Oracle 19.12 – Log switch SCN in alertlog

This entry was posted in Oracle 19c Patchset Update and tagged oracle 19c on by

Just patched few DBs and noticed this change:

Before:

2021-08-26T05:30:52.463731+02:00
Thread 1 advanced to log sequence 101101 (LGWR switch)
  Current log# 1 seq# 101101 mem# 0: +U01/ANJODB_SITE1/ONLINELOG/group_1.282.988988971

And with 19.12:

2021-08-27T07:17:16.811288+02:00
Thread 1 advanced to log sequence 101176 (LGWR switch),  current SCN: 132131048333
  Current log# 1 seq# 101176 mem# 0: +U01/ANJODB_SITE1/ONLINELOG/group_1.282.988988971

Nice touch from Oracle. 🙂

GI installation – ASM does not start on 19.12

This entry was posted in ASM Oracle Oracle 19c on by

Today a colleague tells me that installing GI on /u00/app/grid/19.12.0 fails, while it works by changing the ORACLE_HOME location. The error in the logs is with the ASM disk group creation:

INFO:  [Aug 16, 2021 4:06:17 PM] Command /u00/app/grid/19.12.0/bin/asmca -silent -oui_internal -configureASM -diskString '/dev/xvd*' -diskGroupName U01 -diskList /dev/xvdg -redundancy EXTERNAL -au_size 4
INFO:  [Aug 16, 2021 4:06:17 PM] ... GenericInternalPlugIn.handleProcess() entered.
INFO:  [Aug 16, 2021 4:06:17 PM] ... GenericInternalPlugIn: getting configAssistantParmas.
INFO:  [Aug 16, 2021 4:06:17 PM] ... GenericInternalPlugIn: checking secretArguments.
INFO:  [Aug 16, 2021 4:06:17 PM] ... GenericInternalPlugIn: starting read loop.
INFO:  [Aug 16, 2021 4:06:22 PM] SYS_PASSWORD_PROMPT
INFO:  [Aug 16, 2021 4:06:22 PM] Processing: SYS_PASSWORD_PROMPT for argument tag -sysAsmPassword
INFO:  [Aug 16, 2021 4:06:22 PM] Skipping line:*******************
INFO:  [Aug 16, 2021 4:06:22 PM] ASMSNMP_PASSWORD_PROMPT
INFO:  [Aug 16, 2021 4:06:22 PM] Processing: ASMSNMP_PASSWORD_PROMPT for argument tag -asmMonitorPassword
INFO:  [Aug 16, 2021 4:06:22 PM] End of argument passing to stdin
INFO:  [Aug 16, 2021 4:06:22 PM] Skipping line:*******************
INFO:  [Aug 16, 2021 4:06:59 PM] Skipping line:
INFO:  [Aug 16, 2021 4:06:59 PM] [FATAL] [DBT-30056] Labeling of disks failed.
INFO:  [Aug 16, 2021 4:06:59 PM] Skipping line: [FATAL] [DBT-30056] Labeling of disks failed.
INFO:  [Aug 16, 2021 4:06:59 PM] ORA-15227: could not perform label set/clear operation
INFO:  [Aug 16, 2021 4:06:59 PM] Skipping line: ORA-15227: could not perform label set/clear operation
INFO:  [Aug 16, 2021 4:06:59 PM] ORA-15031: disk specification '/dev/xvdg' matches no disks
INFO:  [Aug 16, 2021 4:06:59 PM] Skipping line: ORA-15031: disk specification '/dev/xvdg' matches no disks
INFO:  [Aug 16, 2021 4:06:59 PM] Skipping line:
INFO:  [Aug 16, 2021 4:06:59 PM] [FATAL] [DBT-30002] Disk group U01 creation failed.
INFO:  [Aug 16, 2021 4:06:59 PM] Skipping line: [FATAL] [DBT-30002] Disk group U01 creation failed.
INFO:  [Aug 16, 2021 4:06:59 PM] ORA-15018: diskgroup cannot be created
INFO:  [Aug 16, 2021 4:06:59 PM] Skipping line: ORA-15018: diskgroup cannot be created
INFO:  [Aug 16, 2021 4:06:59 PM] ORA-15031: disk specification 'AFD:U011' matches no disks
INFO:  [Aug 16, 2021 4:06:59 PM] Skipping line: ORA-15031: disk specification 'AFD:U011' matches no disks
INFO:  [Aug 16, 2021 4:06:59 PM] Skipping line:
INFO:  [Aug 16, 2021 4:06:59 PM] Skipping line:
INFO:  [Aug 16, 2021 4:06:59 PM] Skipping line:
INFO:  [Aug 16, 2021 4:07:00 PM] Completed Plugin named: asmca
INFO:  [Aug 16, 2021 4:07:00 PM] ConfigClient.saveSession method called
INFO:  [Aug 16, 2021 4:07:00 PM] Automatic Storage Management Configuration Assistant failed.

While I still did not get the original reason of the problem (might be with the Gold Image creation of the GI), I found that it can be worked around with:

chown -R grid:oinstall $ORACLE_BASE/diag

How to change PDB open mode without disconnecting existing sessions: the ambiguous FORCE

This entry was posted in Multitenant Oracle Oracle 19c on by

When the database is in READ ONLY mode, the way to change it to READ WRITE is by issuing

SQL> ALTER DATABASE OPEN READ WRITE;
Database altered.

However, when working on a PDB, the similar syntax does not work:

SQL> ALTER PLUGGABLE DATABASE PDB1 OPEN READ WRITE;
ALTER PLUGGABLE DATABASE PDB1 OPEN READ WRITE
*
ERROR at line 1:
ORA-65019: pluggable database pdb1 already open

This is strange, and to my surprise, the correct syntax to change a PDB from READ ONLY to READ WRITE is:

SQL> ALTER PLUGGABLE DATABASE PDB1 OPEN READ WRITE FORCE;
Pluggable database altered.

I would expect that the “FORCE” would do the same as on the STARTUP command here:

SQL> STARTUP FORCE OPEN READ WRITE;
ORACLE instance started.

Total System Global Area 2147481656 bytes
Fixed Size                  8898616 bytes
Variable Size             872415232 bytes
Database Buffers         1258291200 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.

On the normal STARTUP command, the FORCE keyword performs a shutdown abort, before starting back the database. It is correctly described in the documentation:

If the database is open, then FORCE shuts down the database with a SHUTDOWN ABORT statement before re-opening it.

https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/starting-up-and-shutting-down.html#GUID-CCA52747-05CA-4ED3-BE6D-E2E684C4D87D

The ALTER PLUGGABLE DATABASE explains the different behaviour of the FORCE keyword in this case:

Specify this keyword [FORCE] to change the open mode of a PDB from READ WRITE to READ ONLY, or from READ ONLY to READ WRITE. The FORCE keyword allows users to remain connected to the PDB while the open mode is changed.

When you specify FORCE to change the open mode of a PDB from READ WRITE to READ ONLY, any READ WRITE transaction that is open when you change the open mode will not be allowed to perform any more DML operations or to COMMIT.

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ALTER-PLUGGABLE-DATABASE.html#GUID-A29491AD-8F0F-4E52-9D94-57FC3FF8FBC7

So, at the end it is good news. It is possible to change the PDB open mode without killing the users’ sessions. Just that “FORCE” keyword seems a bit too strong for my taste.