Oracle 19c


Install Oracle Linux 9 and Oracle Database 19c on Windows WSL   Recently updated !

Since a few days I’ve a Oracle Database 19c running on my laptop (4 year old i5 processor with 16GB memory) under the Windows Subsystem for Linux with the latest Oracle Linux 9.3. It is working great for testing functionalities. It survives without problem sleep and restart without first shutting down correctly the database.

While there are no snapshot possibility like with Virtualbox, it is possible to export the running image and re-import later.

Below I describe the main steps to quickly install the system.

(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

Upgrade to 19c: only 6 months left with support from Oracle

Oracle updated on 7 June 2023 the Release Schedule of Current Database Releases (Doc ID 742060.1) was updated, clarifying that only until December 2023 is possible to get support from Oracle ACS to perform the Upgrade the databases to the version 19c, the current stable release.

Below the updated slide from Oracle showing the support timelines of each version. Oracle 19c has also “Waived Extended Fee” support until 30 April 2025, even if that is not yet marked in the slide.

Don’t miss this opportunity!


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.


Datapatch and Tablespace Full

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

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

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

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.


Opatchauto does not work without DB created. And Oracle support… well

Few months ago I hit an interesting case. After installing Oracle Grid Infrastructure and Database software on a server, the new Release Update come out and I wanted to use opatchauto to apply it automatically on both homes.

It doesn’t work.

sudo ${ORACLE_HOME}/OPatch/opatchauto apply -phBaseDir /tmp/RU_19c_JAN2021/32126842 -prepare-clone -silent /tmp/RU_19c_JAN2021/clone.properties
...
OPATCHAUTO-72128: Cannot execute out of place patching prepare session.
OPATCHAUTO-72128: Clone home properties /tmp/RU_19c_JAN2021/clone.properties specified has entry for non requested home(s) [/u00/app/oracle/product/19.8.0].
OPATCHAUTO-72128: Please correct properties file and re-run out of place patching prepare operation.
OPatchAuto failed.

The setup is all correct, opatchauto is the latest version (13.9.4.5.0, as of February 2021), the inventory has the two homes:

<HOME NAME="OraGI19Home1" LOC="/u00/app/grid/19.8.0" TYPE="O" IDX="1" CRS="true"/>
<HOME NAME="OraDB19Home1" LOC="/u00/app/oracle/product/19.8.0" TYPE="O" IDX="2"/>

The clone.properties file has the name of these homes:

/u00/app/grid/19.8.0=/u00/app/grid/19.10.0
/u00/app/oracle/product/19.8.0=/u00/app/oracle/product/19.10.0

The only “problem” is that no database is created. In fact, the workaround is just to register a fake DB on the CRS:

srvctl add db XX -oraclehome /u00/app/oracle/product/19.8.0

Then the opatchauto works.

I open a SR with Oracle. Inform about the OPatch bug and ask what is the meaning of the OPATCHAUTO-72128 error, because there is no documentation of Opatchauto.

The normal ping-pong with Oracle support starts, they ask me to run with maximum debug level before they can do something. I say that the complete use case is already described and they can try in-house and can/should open a bug.

No success. The support person says that if the customer doesn’t do more work, Oracle doesn’t care about the bug and problem will remain unfixed. I inform that I will blog about this bug, so that the community is aware.

Final answer from this SR:

“Knowledge content not created because the issue was resolved by the customer with no further information provided. Also no further ODM headings were used for the same reason.”