Connection to Oracle, Bueller? Bueller? Anyone?

I know that this is an old post, but I'm currently struggling with the same issues and didn't know if anyone had any guidance?

I'm currently trying to use cx_Oracle both with both AWS MWAA (v2.0.2) and the AWS MWAA Local Runner (v2.2.3). In both cases, I've tried the following:

1. Installed libaio in an Amazon Linux Docker image
2. Downloaded Oracle Instant Client binaries (I've tried both v18.5 & v21.6) to plugins/instantclient_21_6/
3. Copied lib64/libaio.so.1, lib64/libaio.so.1.0.0, and lib64/libaio.so.1.1.1 into plugins/instantclient_21_6/ (I also tried copying /lib64/libnsl-2.26.so and /lib64/libnsl.so.1 like @esloss did above)
4. Created a file plugins/env_var_plugin_oracle.py where I've set the following:

from airflow.plugins_manager import AirflowPlugin
import os

os.environ["LD_LIBRARY_PATH"]='/usr/local/airflow/plugins/instantclient_21_6'
os.environ["ORACLE_HOME"]='/usr/local/airflow/plugins/instantclient_21_6'
os.environ["DPI_DEBUG_LEVEL"]="64"

class EnvVarPlugin(AirflowPlugin):                
        name = 'env_var_plugin' 

5. Set 'core.lazy_load_plugins' to false in docker/confic/airflow.cfg
6. Recreated Docker image

I'm trying to run the example Oracle DAG here:

from airflow import DAG
from airflow.operators.python_operator import PythonOperator
from airflow.utils.dates import days_ago
from datetime import datetime, timedelta
import cx_Oracle

default_args = {
    "owner": "airflow",
    "depends_on_past": False,
    "start_date": datetime(2015, 6, 1),
    "email": ["airflow@airflow.com"],
    "email_on_failure": False,
    "email_on_retry": False,
    "retries": 1,
    "retry_delay": timedelta(minutes=5)
}

def testHook(**kwargs):
    cx_Oracle.init_oracle_client()
    version = cx_Oracle.clientversion()
    print("cx_Oracle.clientversion",version)
    return version

with DAG(dag_id="oracle", default_args=default_args, schedule_interval=timedelta(minutes=1)) as dag:
    hook_test = PythonOperator(
        task_id="hook_test",
        python_callable=testHook,
        provide_context=True 
    )

Every time I get the error:

cx_Oracle.DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library: "/usr/local/airflow/plugins/instantclient_21_6/lib/libclntsh.so: cannot open shared object file: No such file or directory". See https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html for help

However, I did find that if I add the 'lib_dir' flag to the 'cx_Oracle.init_oracle_client()' method like cx_Oracle.init_oracle_client(lib_dir = os.environ.get("LD_LIBRARY_PATH")) I get a different error which makes me think the issues is somehow related to the 'LD_LIBRARY_PATH' not being set correctly:

cx_Oracle.DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library: "libnnz21.so: cannot open shared object file: No such file or directory". See https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html for help

Hi,

Unfortunately the Oracle client library is not yet part of the MWAA Airflow container image, primarily due to the size of the former. The MWAA team is looking for a way to include a slimmed-down version, however in the meantime an external container needs to be used to connect to Oracle such as ECS or EKS (example of the latter at https://docs.aws.amazon.com/mwaa/latest/userguide/mwaa-eks-example.html )

Thanks!

Thanks John,

Do you have an approximate ETA on this being added?

If size is the concern here are some ideas:

1. Have it external to the image, that's how we manage it currently in our in-house container, we just mount it and point the LD_LIBRARY_PATH to the mounted location
2. include it in the plugins.zip and set a static LD_LIBRARY_PATH=$AIRFLOW_HOME/plugins/lib
   Either one of those approaches simplifies the solution and allows the customer to have control of the Oracle client's version.

Regards,
Victor

Thanks Victor! Those are great suggestions. The MWAA team is working to add Oracle support and the ticket has been updated to reflect your suggestions to possibly accelerate the implementation.

Here's a workaround for Oracle support:

1. download https://download.oracle.com/otn_software/linux/instantclient/185000/instantclient-basic-linux.x64-18.5.0.0.0dbru.zip from https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html as plugins.zip

2. add the following to the above as zip -j plugins.zip env_var_plugin.py

from airflow.plugins_manager import AirflowPlugin
import os

os.environ square bracket "LD_LIBRARY_PATH" square bracket='/usr/local/airflow/plugins/instantclient_18_5'
os.environ square bracket "DPI_DEBUG_LEVEL" square bracket ="64"

class EnvVarPlugin(AirflowPlugin):
name = 'env_var_plugin'

3. Extract the following files from libaio-0.3.109-13.amzn2.0.2.x86_64.rpm (for example, by running yum -y install libaio on Amazon Linux and copying from /lib64 folder) and add to the /instantclient_18_5 folder of plugins.zip

libaio.so.1
libaio.so.1.0.0
libaio.so.1.0.1

4. Upload to S3 and select as your MWAA environment's plugins.zip file

5. Test with the following DAG:

from airflow import DAG
from airflow.operators.python_operator import PythonOperator
from airflow.utils.dates import days_ago
import os
import cx_Oracle

DAG_ID = os.path.basename(file).replace(".py", "")
LIB_DIR = '/usr/local/airflow/plugins/instantclient_18_5'

def testHook(**kwargs):
cx_Oracle.init_oracle_client(lib_dir=LIB_DIR)
version = cx_Oracle.clientversion()
print("cx_Oracle.clientversion",version)
return version

with DAG(dag_id=DAG_ID, schedule_interval=None, catchup=False, start_date=days_ago(1)) as dag:
hook_test = PythonOperator(
task_id="hook_test",
python_callable=testHook,
provide_context=True
)

Edited by: JohnJ-AWS on Mar 30, 2021 7:43 PM

Hello John,

I did follow the instructions at Creating a custom plugin with Oracle: https://docs.aws.amazon.com/mwaa/latest/userguide/samples-oracle.html

But I'm having the following issues for instantclient_18_5 and instantclient_12_2:

print("cx_Oracle.version: " + cx_Oracle.version)
print("LD_LIBRARY_PATH: " + os.environ\["LD_LIBRARY_PATH"])
print("ARCH:", platform.architecture())
print("FILES at " _ lib_dir _ ":")
for name in os.listdir(lib_dir):
print(name)
cx_Oracle.init_oracle_client()
INFO - cx_Oracle.version: 8.1.0
INFO - LD_LIBRARY_PATH: /usr/local/airflow/plugins/instantclient_12_2
INFO - ARCH: ('64bit', '')
INFO - FILES at /usr/local/airflow/plugins/instantclient_12_2:
INFO - libons.so
INFO - libaio.so.1
INFO - libclntsh.so
INFO - libmql1.so
...
cx_Oracle.DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library: "libclntsh.so: cannot open shared object file: No such file or directory".

cx_Oracle.init_oracle_client(lib_dir = os.environ\["LD_LIBRARY_PATH"])
cx_Oracle.DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library: "libmql1.so: cannot open shared object file: No such file or directory".

So the environment variable LD_LIBRARY_PATH is set by the EnvVarPlugin and also the folder is valid and contains the instantclient files.

Is it possible that this solution is somehow broken for Airflow v2.0.2?

Thank you!
Kind regards
Martin

Hi!

With Airflow 2.0, issue is that plugins are not loaded until used:
https://airflow.apache.org/docs/apache-airflow/stable/plugins.html#when-are-plugins-re-loaded
This bypasses setting LD_LIBRARY_PATH in the plugin.

Adding the configuration override core.lazy_load_plugins = False should resolve this issue.

Thanks!

Hi There,

I followed the steps from this thread, created the plugins.zip, updated MWAA 2.0.2 env. Added the config - "core.lazy_load_plugins" set to False and still getting the error -
cx_Oracle.DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library: "/usr/local/airflow/plugins/instantclient_18_5/libclntsh.so: cannot open shared object file: No such file or directory". See https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html for help

Anyone was able to resolve the error? Is there any other step required to solve this error.

Thanks.

Hi,

Please ensure that you've packaged libaio correctly per https://docs.aws.amazon.com/mwaa/latest/userguide/samples-oracle.html#samples-oracle-create-pluginszip-steps

Thanks.

If it helps anyone else, I was able to get the plugin functioning with a couple of additions to the linked instructions.

1. Extract /lib64/libnsl-2.26.so and /lib64/libnsl.so.1 from the container into the local instantclient_18_5 folder
2. Add os.environ\["ORACLE_HOME"]='/usr/local/airflow/plugins/instantclient_18_5' to the env_var_plugin_oracle.py

For some reason I wasn't seeing the same error logging in MWAA that I was seeing in local VM testing the cx_Oracle.init_oracle_client, but locally mentioned being unable to load libnsl.so.1 and missing ORACLE_HOME.

Edited by: esloss on Oct 12, 2021 12:22 PM

Edited by: esloss on Oct 12, 2021 1:06 PM

Not a Linux expert, but I did successfully make a plugin following this guide on an Amazon Linux EC2: https://docs.aws.amazon.com/mwaa/latest/userguide/samples-oracle.html

MWAA version: 2.2.2

Some minor changes I made:

Requirements: change the constrain file to be version 2.2.2

-c https://raw.githubusercontent.com/apache/airflow/constraints-2.2.2/constraints-3.7.txt
cx_Oracle
apache-airflow-providers-oracle

Create the custom plugin: I used 3 terminal windows on this step.

Window 1: Download dependencies. In the end it says "Keep this window open for subsequent steps." It just means to keep this window open, but not use it to run any other following commands.

window 2: Download client folder.

window 3: Extract files from Docker and make the plugins.zip. In one of the steps, Run zip -r instantclient_18_5 plugins.zip rather than zip -r ../plugins.zip ./. In the latter case the "client.zip " will be included in the plugins.zip too.

Probably you can combine window 2 and window 3, but like I said, not a Linux expert.

To debug, from my experience, I listed the files in the plugins folder to make sure the path is set correctly like millecker@ did. Sometimes the client folder ended up in /usr/local/airflow/plugins/plugins/ folder.

Then if it still has the error, something might be missing during the copy. I used my local terminal (Mac OS) to create the plugins, which is about 70MB, and gave me the error. However, when I used Amazon Linux EC2, the plugins.zip is about 90MB and it worked.

I don't know exactly why and how, but just hope my post can help someone who is as struggling as I was.