Oracle FDW set up

Note

The following steps were written to set up oracle fdw on an M1 Mac

System Prerequisites

Set up postgresql

1. To set up the Postgres-Oracle connection locally, the Postgres version you have installed must be above 14.4. Run the command
   1. brew info postgresql
2. To update the version-
   1. Stop all Postgres containers, dBeaver, anything else using the db or postgres
   2. brew remove postgresql
   3. brew install postgresql

Clear caches

To avoid any caching issues in Docker, run the following steps

1. Stop the caseflow-db postgres Container in Docker
2. Remove the caseflow-db postgres Container
3. Remove the caseflow_postgresdata Volume

Verify the xcode version

1. If you’re on a Mac, ensure that you’re running the terminal with the latest version of the xcode command line tools.
2. If installing for the first time, they can be installed via the Self Service Portal
3. If already installed, make sure there are no pending software updates by going to the
   1. System Settings -> General -> Software Updates and running the updates as needed

Setting up Oracle FDW

1. The latest version of the Oracle instantclient must be installed.
   1. You can set it up by going through the steps in the Oracle InstantClient files section of the Caseflow Apple M1 M2 setup link
2. After setting up the Oracle InstantClient add the ORACLE_HOME environment variable with the command
   1. export ORACLE_HOME=~/Downloads/instantclient_{IC_VERSION} make install export
3. Set up the oracle_fdw repo locally
   1. In the terminal, run the command git clone https://github.com/laurenz/oracle_fdw.git
4. Install Oracle FDW
   1. Change directory to the oracle_fdw repo
      1. cd oracle_fdw
   2. Run the following command in the oracle_fdw repo
      1. make install

Recreate the development databases

Change directories to where Caseflow is installed locally and run the following commands

1. make up-m1
   1. If there are errors here try running it again
2. Recreate the development and test databases

RAILS_ENV=development bundle exec rake db:create:primary db:schema:load:primary db:create:etl db:schema:load:etl

RAILS_ENV=test bundle exec rake db:create:primary db:schema:load:primary db:create:etl db:schema:load:etl

Create the foreign tables

1. Start up Caseflow and the docker containers using the commands in the Running Caseflow section of the Apple M1/M2 set up wiki
2. In dBeaver, execute the setup_oracle_fdw.sql SQL script

Important

Make sure to update all the placeholder variables with the correct values for the development environment

3. To verify the foreign tables have been successfully created, run the following SQL query in dBeaver
   1. select * from information_schema.foreign_tables