The last commit broke the Github Actions about oci-oracle-xe

[gvenzl]

Hey @DeveloperKurt,

Thanks for reporting this!

The last update didn't really do much other than introducing a permissions change for $ORACLE_BASE/oradata, i.e. chmod a+rwx -R "${ORACLE_BASE}"/oradata, and some additional output in the container log.

If you could point me to one of the failed jobs, I can take a look at what did go wrong.

[DeveloperKurt]

Hi! Yes we were quite surprised as well that a seemingly innocent small commit broke it. We have a dependency to this image to run our spring boot IT tests on Github Actions workflow.
So the application has to get up normally as a separate process from the IT tests.

The oracle service was defined under the services: right before the spring boot server and it contained the health check options that is specified in the readme.

        options: >-
          --health-cmd healthcheck.sh
          --health-interval 10s
          --health-timeout 5s
          --health-retries 10

However the app started getting up before the database was opened for connections (the Oracle container is getting up but not being ready for connections) after the last commit. Which caused it to crash.

Not sure how these permission changes could have caused this but was definitely a tricky one :)

[gvenzl]

Hey @DeveloperKurt,

Thanks a lot for that feedback.
I ran several tests and cannot reproduce a situation where healtcheck.sh reports the database ready before it is:

DB archive not yet extracted, instance not started:

[oracle@ffe79333ba19 ~]$ ./healthcheck.sh
+ set -Eeuo pipefail
++ sqlplus -version
++ grep Release
++ awk '{ print $3 }'
+ ORACLE_VERSION=21.0.0.0.0
+ [[ 21.0.0.0.0 = \1\1\.\2* ]]
+ DATABASE=XEPDB1
++ sqlplus -s /
+ db_status='ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Additional information: 4775
Additional information: 1195581107
Process ID: 0
Session ID: 0 Serial number: 0


SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYSKM|SYSRAC}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
      <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYSKM|SYSRAC}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
      <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus'
[oracle@ffe79333ba19 ~]$ echo $?
1

Instance not started:

[oracle@ffe79333ba19 ~]$ ./healthcheck.sh
+ set -Eeuo pipefail
++ grep Release
++ sqlplus -version
++ awk '{ print $3 }'
+ ORACLE_VERSION=21.0.0.0.0
+ [[ 21.0.0.0.0 = \1\1\.\2* ]]
+ DATABASE=XEPDB1
++ sqlplus -s /
+ db_status='ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Additional information: 4775
Additional information: 1195581107
Process ID: 0
Session ID: 0 Serial number: 0


SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYSKM|SYSRAC}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
      <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYSKM|SYSRAC}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
      <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus'
[oracle@ffe79333ba19 ~]$ echo $?
1

Instance started by DB not mounted:

[oracle@8c63edef43aa ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Sat Oct 14 18:19:04 2023
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1610612080 bytes
Fixed Size		    9686384 bytes
Variable Size		  603979776 bytes
Database Buffers	  989855744 bytes
Redo Buffers		    7090176 bytes
SQL> exit
Disconnected from Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
[oracle@8c63edef43aa ~]$ ./healthcheck.sh
+ set -Eeuo pipefail
++ sqlplus -version
++ grep Release
++ awk '{ print $3 }'
+ ORACLE_VERSION=21.0.0.0.0
+ [[ 21.0.0.0.0 = \1\1\.\2* ]]
+ DATABASE=XEPDB1
++ sqlplus -s /
+ db_status='ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0


SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYSKM|SYSRAC}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
      <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYSKM|SYSRAC}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
      <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus'
[oracle@8c63edef43aa ~]$ echo $?
1

Instance started, DB mounted but not opened:

[oracle@8c63edef43aa ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Sat Oct 14 18:19:19 2023
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> alter database mount;

Database altered.

SQL> exit
Disconnected from Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
[oracle@8c63edef43aa ~]$ ./healthcheck.sh
+ set -Eeuo pipefail
++ sqlplus -version
++ grep Release
++ awk '{ print $3 }'
+ ORACLE_VERSION=21.0.0.0.0
+ [[ 21.0.0.0.0 = \1\1\.\2* ]]
+ DATABASE=XEPDB1
++ sqlplus -s /
+ db_status='ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0


SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYSKM|SYSRAC}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
      <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYSKM|SYSRAC}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
      <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus'
[oracle@8c63edef43aa ~]$ echo $?
1

Instance open and ready for service --> OK:

[oracle@8c63edef43aa ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Sat Oct 14 18:19:30 2023
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> alter database open;

Database altered.

SQL> exit
Disconnected from Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
[oracle@8c63edef43aa ~]$ ./healthcheck.sh
+ set -Eeuo pipefail
++ sqlplus -version
++ grep Release
++ awk '{ print $3 }'
+ ORACLE_VERSION=21.0.0.0.0
+ [[ 21.0.0.0.0 = \1\1\.\2* ]]
+ DATABASE=XEPDB1
++ sqlplus -s /
+ db_status=READY
+ '[' READY == READY ']'
+ exit 0
[oracle@8c63edef43aa ~]$ echo $?
0

Instance open but pluggable database closed:

[oracle@8c63edef43aa ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Sat Oct 14 18:19:45 2023
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> alter pluggable database xepdb1 close;

Pluggable database altered.

SQL> exit
Disconnected from Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
[oracle@8c63edef43aa ~]$ ./healthcheck.sh
+ set -Eeuo pipefail
++ sqlplus -version
++ awk '{ print $3 }'
++ grep Release
+ ORACLE_VERSION=21.0.0.0.0
+ [[ 21.0.0.0.0 = \1\1\.\2* ]]
+ DATABASE=XEPDB1
++ sqlplus -s /
+ db_status='
no rows selected'
+ '[' '
no rows selected' == READY ']'
+ exit 1
[oracle@8c63edef43aa ~]$ echo $?
1

I'm afraid without further information, there is not much for me to go on. Can you still reproduce this issue on your side?