search

gvenzl / oci-oracle-xe

Build scripts for Oracle Database XE container/docker images
Apache License 2.0
270 stars 76 forks source link

The last commit broke the Github Actions #229

Closed DeveloperKurt closed 10 months ago

DeveloperKurt commented 1 year ago

Hi, the last commit (70d2d6ce640971ad31987fbd79c665bb2d455a51) has been released under the same version which caused our CI/CD to break. We had to create a new image from the previous commit.

Probably it was something about the health check because our server started booting up before the database was open for connections.

gvenzl commented 12 months ago

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 commented 12 months ago

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 commented 11 months ago

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?