Upgrading GN on Oracle database produces lots of errors

[igea-jure]

Describe the bug
When using Oracle DB, there are a number of SQL statements in GN upgrade scripts that won't run against the database.

To Reproduce
Steps to reproduce the behavior:

1. Have an existing older GN instance running on Oracle DB.
2. Start a newer version of GN, which will trigger an upgrade process.
3. See errors in log.

Expected behavior
Upgrade DB objects without errors.

Additional context
Trying to upgrade GN on Oracle DB from version 3.8.2 to 4.4.3, had to fix a lot of minor issues related to Oracle DB.

One of the first errors when upgrading (or installing, for that matter) GN on Oracle is ORA-01000: maximum open cursors exceeded. This is caused by an unreasonly high (for me at least) setting jdbc.basic.maxOpenPreparedStatements=1200 in file web/src/main/webResources/WEB-INF/config-db/jdbc.properties. On a default installation of Oracle DB, each session is limited to 300 open cursors.

Another small issue is Oracle JDBC connection string. In GN, this connection string still uses database SID. Nowadays most connections to the database are made with service name. Connection string differs slightly:

• SID: jdbc:oracle:thin:@${host}:${port}:${sid}
• Service name: jdbc:oracle:thin:@${host}:${port}/${service_name}

I will make a pull request with all changes that I had to make in order to upgrade the database.

[Bear-LB]

With version 4.2.5 I attempted a fresh install with Oracle and i had to change SID to SERVICE and increase open cursors.
I then got a working homepage. But Geonetwork didn't create the Metadatastatus table, i manually had to create that.
The metadata was inserted into the database, but elasticsearch couldn't rebuild or index it. My elasticsearch setup was not faulty because once i changed jdbc.properties to look to my Postgres server instead, everything worked.
Do/did you have the same issue ?
#7298

[igea-jure]

With version 4.2.5 I attempted a fresh install with Oracle and i had to change SID to SERVICE and increase open cursors. I then got a working homepage. But Geonetwork didn't create the Metadatastatus table, i manually had to create that. The metadata was inserted into the database, but elasticsearch couldn't rebuild or index it. My elasticsearch setup was not faulty because once i changed jdbc.properties to look to my Postgres server instead, everything worked. Do/did you have the same issue ? #7298

While trying to upgrade, I had the same problem regarding SID/SERVICE NAME, cursors and Users table not being able to handle empty password for user nobody. TBH I was only testing database side of things, nothing elasticsearch related.

Today we have made progress with a fresh install of GN on Oracle, I will post findings and solutions to #7298.