ORA-28547 with php-fpm in docker container

Question

I got a working php-fpm docker container acting as the php backend to a nginx frontend. What I mean by working, is that it renders phpinfo output in the browser as expected. My php-fpm container was produced by php-fpm-7.4 prod of the devilbox docker repo. It has OCI8 enable.

The issue: I keep getting ORA-28547 when trying oci_connect

What I have done:

1--add /usr/lib/oracle/client64/lib to a file inside ld.so.conf.d and run ldconfig -v 2--restart docker container.
3-- Now phpinfo shows ORACLE_HOME=/usr/lib/oracle/client64/lib

4--Add tnsnames.ora to /usr/lib/oracle/client6/lib/network/admin (there is a README.md file inside that folder that even tells you to do that) 5--Restart docker container again. 6-oci_connect still fails with the same error.

What I am missing?

Thank you very much for any pointers, I think I have browsed to the end of the internet and back without finding a solution yet.

----SOLUTION: reinstall instantclient, relink libraries (ldconfig) to use new instantclient libraries. Create modified dockerfile to do it when container is created.

I modified the Dockerfile file of the php-fpm to add new instant client files and not the one that were provided by the original file. I was not able to make it work with them. I have tried a few times rebuilding the image (docker-compose up --build) and this is the file that does the trick:

FROM devilbox/php-fpm:7.4-work

#instantclient.conf content: /opt/instantclient RUN echo "/opt/instantclient" >/etc/ld.so.conf.d/instantclient.conf

WORKDIR /opt

RUN wget https://download.oracle.com/otn_software/linux/instantclient/19800/instantclient-sdk-linux.x64-19.8.0.0.0dbru.zip

RUN wget https://download.oracle.com/otn_software/linux/instantclient/19800/instantclient-sqlplus-linux.x64-19.8.0.0.0dbru.zip

RUN wget https://download.oracle.com/otn_software/linux/instantclient/19800/instantclient-basic-linux.x64-19.8.0.0.0dbru.zip

RUN unzip instantclient-sdk-linux.x64-19.8.0.0.0dbru.zip

RUN unzip instantclient-sqlplus-linux.x64-19.8.0.0.0dbru.zip

RUN unzip instantclient-basic-linux.x64-19.8.0.0.0dbru.zip

RUN mv instantclient_19_8 instantclient

ADD tnsnames.ora /opt/instantclient/network/admin

RUN ldconfig -v

CMD ["php-fpm"]

expose 9000

Answer 1

# Insert following to .bash_profile or .profile of the User starting the php-fpm
export ORACLE_HOME=/usr/lib/oracle/client64
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME/bin
export TNS_ADMIN=$ORACLE_HOME/network/admin
# Test to Ping Remote Db to be connected  by PHP 
tnsping <tns-name of remote DB - i.e. db12c.world>
 # restart here the php Engine 

Answer 2

That's why I have suggested to use tnsping - unfortunaly it is not included in any of the instant client files which is a pity - so you have to pick it up from regular client with matching OS, bitsize and Oracle release. As workaround you could place SQL*Plus package files into container and try to connect with a foo user like

sqlplus foo/foo@\<ip>:\<port>/\<dbname>

which should generate an error - if

• user/password not matching - ORA-1017 i.e. DB & listener running
• listener running - ORA-1034 i.e. DB down
• listener down (no return, or TNS-Errors)

Answer 3

I got it!. It was a firewall issue. I launched a tcpdump capture session and there was nothing wrong with php-fpm, oci8 and instantclient libraries. The traffic was initiated but there was no response from the database. I made it work against a different database where this box has no firewall issues.

I now will try rebuilding the docker image so I can see what I have to manually add if any.

That was incorrect (the firewall as the origin of the problem). Rebuilding the docker file showed me where I had it wrong. See original question for solution.

Answer 4

Can you please check

https://github.com/caffeinalab/php-fpm-oci8/blob/master/Dockerfile

which seems to create a p-fpm-oci8 docker image

the "wget" for

wget -qO- https://raw.githubusercontent.com/caffeinalab/php-fpm-oci8/master/oracle/instantclient-basic-linux.x64-12.2.0.1.0.zip | bsdtar -xvf- -C /usr/local &&
wget -qO- https://raw.githubusercontent.com/caffeinalab/php-fpm-oci8/master/oracle/instantclient-sdk-linux.x64-12.2.0.1.0.zip | bsdtar -xvf- -C /usr/local &&
wget -qO- https://raw.githubusercontent.com/caffeinalab/php-fpm-oci8/master/oracle/instantclient-sqlplus-linux.x64-12.2.0.1.0.zip | bsdtar -xvf- -C /usr/local && \

can be dropped when you place downloaded instant client files into local host dir

/usr/local

and extract them - resulting in

/usr/local/instantcient_12_2 or 18, 19c equivalents

the 4 "ln" commands have to be adjusted to reflect the local host instantclient dir

the tnsnames.ora for instantclient is available from host by VOLUME command

-------------FINAL SOLUTION------------(it was not network related, I had done a couple of changes to the files, and also tried a different database, all at the same time, so it made me think that it was the different database what fixed the issue)

After many trial and errors, I came up with a Dockerfile that creates the correct configuration of files and connects without any issues to the database:

--Dockerfile: (to build php-fpm 7.4 using devilbox image)

Final solution:

I modified the Dockerfile file of the php-fpm to add new instant client files and not the one that were provided by the original file. I was not able to make it work with them. I have tried a few times rebuilding the image (docker-compose up --build) and this is the file that does the trick:

FROM devilbox/php-fpm:7.4-work

ADD instantclient.conf /etc/ld.so.conf.d/

WORKDIR /opt

RUN wget https://download.oracle.com/otn_software/linux/instantclient/19800/instantclient-sdk-linux.x64-19.8.0.0.0dbru.zip

RUN wget https://download.oracle.com/otn_software/linux/instantclient/19800/instantclient-sqlplus-linux.x64-19.8.0.0.0dbru.zip

RUN wget https://download.oracle.com/otn_software/linux/instantclient/19800/instantclient-basic-linux.x64-19.8.0.0.0dbru.zip

RUN unzip instantclient-sdk-linux.x64-19.8.0.0.0dbru.zip

RUN unzip instantclient-sqlplus-linux.x64-19.8.0.0.0dbru.zip

RUN unzip instantclient-basic-linux.x64-19.8.0.0.0dbru.zip

RUN mv instantclient_19_8 instantclient

ADD tnsnames.ora /opt/instantclient/network/admin

RUN ldconfig -v

CMD ["php-fpm"]

expose 9000