Install Orafce for AlloyDB Omni

AlloyDB Omni does not include the Orafce extension, but you can manually add it to an existing AlloyDB Omni installation by following the instructions on this page to provide additional functions and operators from the Oracle database.

Before you begin

Ensure that you've installed AlloyDB Omni on your system.

Single-server

Add Orafce to your AlloyDB Omni installation

To add the Orafce extension to your AlloyDB Omni installation, follow these steps:

  1. Find your installed AlloyDB Omni version labels:

    Docker

    docker run --rm -it  google/alloydbomni cat VERSION.txt

    Podman

    podman run --rm -it  google/alloydbomni cat VERSION.txt

    The output is similar to the following:

    AlloyDB Omni version: 15.5.5

    Take note of the AlloyDB Omni version number; you need it in the next step.

  2. Set the OMNI_VERSION environment variable: 
    OMNI_VERSION=VERSION

    Replace VERSION with the complete database server version from the previous step—for example, 15.5.5.

  3. Create a new AlloyDB Omni container that includes Orafce:

    Docker

      mkdir ~/alloydb-omni-orafce
  tee -a ~/alloydb-omni-orafce/Dockerfile << EOF
  ARG OMNI_VERSION

  FROM postgres:15-bookworm AS postgres

  RUN apt-get update && \
  apt-get install -y --no-install-recommends \
  postgresql-15-orafce && \
  apt-get purge -y --auto-remove && \
  rm -rf /var/lib/apt/lists/*

  FROM google/alloydbomni:${OMNI_VERSION}

  COPY --from=postgres /usr/lib/postgresql/15/lib/orafce.so /usr/lib/postgresql/15/lib/orafce.so
  COPY --from=postgres /usr/share/postgresql/15/extension/orafce* /usr/lib/postgresql/15/share/extension/

  COPY --from=postgres /usr/lib/x86_64-linux-gnu/ /usr/lib/x86_64-linux-gnu/
  COPY --from=postgres /lib/x86_64-linux-gnu/ /lib/x86_64-linux-gnu/
  EOF
  cd ~/alloydb-omni-orafce
  sudo docker build --build-arg OMNI_VERSION=$OMNI_VERSION --tag google/alloydbomni-with-orafce:$OMNI_VERSION .

    Podman

      mkdir ~/alloydb-omni-orafce
  tee -a ~/alloydb-omni-orafce/Dockerfile << EOF
  ARG OMNI_VERSION

  FROM postgres:15-bookworm AS postgres

  RUN apt-get update && \
  apt-get install -y --no-install-recommends \
  postgresql-15-orafce && \
  apt-get purge -y --auto-remove && \
  rm -rf /var/lib/apt/lists/*

  FROM google/alloydbomni:${OMNI_VERSION}

  COPY --from=postgres /usr/lib/postgresql/15/lib/orafce.so /usr/lib/postgresql/15/lib/orafce.so
  COPY --from=postgres /usr/share/postgresql/15/extension/orafce* /usr/lib/postgresql/15/share/extension/

  COPY --from=postgres /usr/lib/x86_64-linux-gnu/ /usr/lib/x86_64-linux-gnu/
  COPY --from=postgres /lib/x86_64-linux-gnu/ /lib/x86_64-linux-gnu/
  EOF
  cd ~/alloydb-omni-orafce
  sudo podman build --build-arg OMNI_VERSION=$OMNI_VERSION --tag google/alloydbomni-with-orafce:$OMNI_VERSION .

    Docker

      mkdir ~/alloydb-omni-orafce
  tee -a ~/alloydb-omni-orafce/Dockerfile << EOF
  ARG OMNI_VERSION

  FROM postgres:15-bookworm AS postgres

  RUN apt-get update && \
  apt-get install -y --no-install-recommends \
  postgresql-15-orafce && \
  apt-get purge -y --auto-remove && \
  rm -rf /var/lib/apt/lists/*

  FROM google/alloydbomni:${OMNI_VERSION}

  COPY --from=postgres /usr/lib/postgresql/15/lib/orafce.so /usr/lib/postgresql/15/lib/orafce.so
  COPY --from=postgres /usr/share/postgresql/15/extension/orafce* /usr/lib/postgresql/15/share/extension/

  COPY --from=postgres /usr/lib/aarch64-linux-gnu/ /usr/lib/aarch64-linux-gnu/
  COPY --from=postgres /lib/aarch64-linux-gnu/ /lib/aarch64-linux-gnu/
  EOF
  cd ~/alloydb-omni-orafce
  sudo docker build --build-arg OMNI_VERSION=$OMNI_VERSION --tag google/alloydbomni-with-orafce:$OMNI_VERSION .

    Podman

      mkdir ~/alloydb-omni-orafce
  tee -a ~/alloydb-omni-orafce/Dockerfile << EOF
  ARG OMNI_VERSION

  FROM postgres:15-bookworm AS postgres

  RUN apt-get update && \
  apt-get install -y --no-install-recommends \
  postgresql-15-orafce && \
  apt-get purge -y --auto-remove && \
  rm -rf /var/lib/apt/lists/*

  FROM google/alloydbomni:${OMNI_VERSION}

  COPY --from=postgres /usr/lib/postgresql/15/lib/orafce.so /usr/lib/postgresql/15/lib/orafce.so
  COPY --from=postgres /usr/share/postgresql/15/extension/orafce* /usr/lib/postgresql/15/share/extension/

  COPY --from=postgres /usr/lib/aarch64-linux-gnu/ /usr/lib/aarch64-linux-gnu/
  COPY --from=postgres /lib/aarch64-linux-gnu/ /lib/aarch64-linux-gnu/
  EOF
  cd ~/alloydb-omni-orafce
  sudo podman build --build-arg OMNI_VERSION=$OMNI_VERSION --tag google/alloydbomni-with-orafce:$OMNI_VERSION .
  4. Create a new container with AlloyDB Omni named my-omni-orafce:

    Docker

    docker run --name my-omni-orafce  -e POSTGRES_PASSWORD=NEW_PASSWORD  -d google/alloydbomni-with-orafce:OMNI_VERSION

    Podman

    podman run --name my-omni-orafce  -e POSTGRES_PASSWORD=NEW_PASSWORD  -d google/alloydbomni-with-orafce:OMNI_VERSION
  5. Connect to your database with the Orafce extension:

    Docker

    docker exec -it my-omni-orafce psql -h localhost -U postgres

    Podman

    podman exec -it my-omni-orafce psql -h localhost -U postgres
  6. Enable Orafce: 
    CREATE EXTENSION IF NOT EXISTS ORAFCE;
  7. Confirm that Orafce is installed and enabled: 
    SELECT oracle.sysdate();
     The output looks similar to the following: 
    postgres=# SELECT oracle.sysdate();
     sysdate
---------------------
2024-06-10 16:36:30
(1 row)