Deploy Oracle Graph on OCI with Autonomous Database without a Database Wallet

In this article, I will show you how to install Oracle Graph Server, using the Oracle Cloud Marketplace image, with the Autonomous Database. For this use case, we will securely connect to the Autonomous Database without a wallet, using TLS. Instructions on configuring TLS will follow but for more information, see this blog post.

Prerequisites

• You will need to provide the SSH key, Virtual Cloud Network and Subnet information during the deployment process.
• This blog assumes you have followed these steps to deploy and Autonomous Database with a Graph enabled user.

Deploy the Graph Server & Client Marketplace Image

Locate your ADB connection string

1. Go to your Cloud Console and navigate to your Autonomous Database instance.
2. Find the label “Access Control List” and select Edit.

3. For now, add your IP address, and save changes. The database will take a moment to update before you are able to continue.

Note: When the Access Control List is enabled, all clients connecting to this Autonomous Database instance have to be in the ACL.

4. Find the label “Mutual TLS (mTLS) Authentication” and select Edit.

5. Uncheck the box to require mutual TLS, and save changes. The database will take a moment to update before you are able to continue.

6. Select DB Connection. The Database Connection console will appear. Ensure that the TLS Authentication is set to TLS, then copy any of the connection strings displayed in the console.

7. Paste the connection string into a notepad for later use.

Locate the Graph Server and Client in the Oracle Cloud Marketplace

Oracle Cloud Marketplace is an online platform which offers Oracle and partner software as click-to-deploy solutions that are built to extend Oracle Cloud products and services. Oracle Cloud Marketplace stacks are a set of Terraform templates that provide a fully automated end-to-end deployment of a partner solution on Oracle Cloud Infrastructure.

1. Go to your Cloud Console. Navigate to the Marketplace tab and enter Graph Server and Client in the search bar. Click on the Oracle Graph Server and Client stack.

2. Select the stack and then review the System Requirements and Usage Instructions. Then select the latest version and choose a compartment and click on Launch Stack.

3. Most of the defaults are perfect for our purposes. However, you will need to choose, or provide the following:

• Select a VM shape.
• Paste your public SSH key. This is used when you ssh into the provisioned compute later, if necessary.
• Choose an existing virtual cloud network.
• Select a subnet compartment and subnet.
• Enter the JDBC URL for the ADB instance, formatted as jdbc:oracle:thin:@<connection string copied from the ADB instance>

4. Click Next to initiate the Resource Manager Job for the stack. The job will take 2-3 minutes to complete.

Add Graph Server and Client Compute Instance to Access Control List

When your Resource Manager Job is successful, copy the public IP address of the Graph Compute instance, so we can add it to the database’s Access Control List. This will allow the graph server to connect to the database without using the ADB Wallet.

1. Log in to the Oracle Cloud console and select your ADB instance to view the instance details. In the details, find the section titled Network and click on Edit next to Access Control List.

2. In the Edit Access Control List dialog, choose IP Address and paste the public IP of the Compute Instance created by the Resource Manager Job.

3. Click Save Changes.

Access Graph Studio

In a browser, navigate to https://<public_ip of compute instance>:7007/ui/ and log in using the graph user credentials. Here you will be able to query from any graphs that have been created and loaded into memory.

From this screen, you can select Advanced Options, Select the Database tab, enter the JDBC url that we made earlier, and login with your graph user credentials. Here you will be able to query from any graphs you have created, even if they have not been loaded into memory.

You should now see this screen, and can get started visualizing your graphs.

Next Steps

The Graph Visualization UI is a useful tool to query existing graphs. If you have not created any graphs, you can get started follow this example.

Troubleshooting

If you see a 500 Error when trying to log in, follow these steps

1. ssh opc@<public_ip of compute instance> .
2. Add jdbc url to /etc/oracle/graph add the jdbc url in pgx.conf .
3. Restart PGX by running sudo systemctl restart pgx.
4. Go to https://<public_ip of compute instance>:7007/ui/ after waiting a few minutes to restart.

If you see an unauthorized error when trying to log in, follow these steps

1. Navigate to your Autonomous Database and access Database Actions as the ADMIN user.
2. Select SQL Query .
3. Run the following statements:

DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
   role_exists EXCEPTION;
   PRAGMA EXCEPTION_INIT(role_exists, -01921);
   TYPE graph_roles_table IS TABLE OF VARCHAR2(50);
   graph_roles graph_roles_table;
 BEGIN
   graph_roles := graph_roles_table(
     'GRAPH_DEVELOPER',
     'GRAPH_ADMINISTRATOR',
     'PGX_SESSION_CREATE',
     'PGX_SERVER_GET_INFO',
     'PGX_SERVER_MANAGE',
     'PGX_SESSION_READ_MODEL',
     'PGX_SESSION_MODIFY_MODEL',
     'PGX_SESSION_NEW_GRAPH',
     'PGX_SESSION_GET_PUBLISHED_GRAPH',
     'PGX_SESSION_COMPILE_ALGORITHM',
     'PGX_SESSION_ADD_PUBLISHED_GRAPH');
   FOR elem IN 1 .. graph_roles.count LOOP
   BEGIN
     dbms_output.put_line('create_graph_roles: ' || elem || ': CREATE ROLE ' || graph_roles(elem));
     EXECUTE IMMEDIATE 'CREATE ROLE ' || graph_roles(elem);
   EXCEPTION
     WHEN role_exists THEN
       dbms_output.put_line('create_graph_roles: role already exists. continue');
     WHEN OTHERS THEN
       RAISE;
     END;
   END LOOP;
 EXCEPTION
   when others then
     dbms_output.put_line('create_graph_roles: hit error ');
     raise;
END;
/
GRANT PGX_SESSION_CREATE TO GRAPH_USER;
GRANT PGX_SERVER_GET_INFO TO GRAPH_USER;
GRANT PGX_SERVER_MANAGE TO GRAPH_USER;
GRANT PGX_SESSION_CREATE TO GRAPH_USER;
GRANT PGX_SESSION_NEW_GRAPH TO GRAPH_USER;
GRANT PGX_SESSION_GET_PUBLISHED_GRAPH TO GRAPH_USER;
GRANT PGX_SESSION_MODIFY_MODEL TO GRAPH_USER;
GRANT PGX_SESSION_READ_MODEL TO GRAPH_USER;

4. Log in again in a fresh window.