> ## Documentation Index
> Fetch the complete documentation index at: https://docs.nx1cloud.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Ingest an Oracle database

> Deploy an Oracle database in the cloud and ingest its datasets into NexusOne.

Oracle is one of the database vendors supported on NexusOne. In this guide, you'll deploy
an Oracle database instance in a public cloud using Terraform, add datasets into the database,
and ingest the database into NexusOne.

## Prerequisites

* An existing AWS account
* Appropriate NexusOne permission: `nx1_ingest`, `nx1_monitor`, `nx1_s3_admin`, `airflow_user`,
  `superset_user`, `spark_sql`, and `trino_admin`
* [AWS](https://docs.aws.amazon.com/cli/latest/userguide/getting-started-install.html) command
  line tool installed
* [sqlplus + oracle instant client](https://www.oracle.com/database/technologies/instant-client/downloads.html)
  command line tool installed
* Terraform installed

## Authenticate to your public cloud

Use your public cloud's command line tool to authenticate to your cloud account.
This allows Terraform to interact with your cloud account.

The following AWS command requests that you enter the following AWS credentials:

* Access key ID
* Secret access key
* Default region

```bash theme={null}
# User account login
aws configure
```

To create an access key ID and secret access key, refer to
[How IAM users can manage their own access keys](https://docs.aws.amazon.com/IAM/latest/UserGuide/access-key-self-managed.html) in the AWS documentation.

## Define a Terraform and cloud provider

After authenticating to your cloud account using the command line tool, create the following
Terraform file.

<CodeGroup>
  ```terraform aws_provider.tf theme={null}
  # Terraform provider
  terraform {
    required_providers {
      aws = {
        source  = "hashicorp/aws"
        version = ">=6.32.0"
      }
    }
  }

  # AWS provider
  provider "aws" {
    region = "us-west-2"
  }
  ```
</CodeGroup>

The defined cloud provider automatically uses the credentials you defined when interacting
with the cloud provider's command line tool.

## Define an Oracle instance

Create the following Terraform file with the Oracle instance resources defined.

<CodeGroup>
  ```terraform aws_oracle.tf theme={null}
  # Create the database instance
  resource "aws_db_instance" "oracle" {
    allocated_storage   = 20
    storage_type        = "gp2"
    engine              = "oracle-se2"
    license_model       = "bring-your-own-license"
    instance_class      = "db.t3.small"
    username            = "oracleadmin"
    password            = "NexusOracle!"
    skip_final_snapshot = true
    publicly_accessible = true
  }

  # Output the database address
  output "database_address" {
    value = aws_db_instance.oracle.address
  }
  ```
</CodeGroup>

<Warning>
  Public access to the database is for demonstration purposes only.
</Warning>

## Deploy the Oracle instance

After defining the Oracle Terraform resource specific to your cloud provider, deploy the Terraform resources.

1. Initialize the Terraform configuration.

   ```bash theme={null}
   terraform init
   ```

2. Create the Oracle instance.

   ```bash theme={null}
   terraform apply --auto-approve
   ```

Once the resources are successfully created, Terraform outputs the database address.

## Add datasets to the database

To ingest datasets from the database into NexusOne, these datasets need to exist first.
Take the following steps to add datasets to the database:

1. Authenticate to the database using the sqlplus command line tool.

   ```bash theme={null}
   sqlplus oracleadmin@//<database_address>/ORCL
   ```

2. When prompted, enter your password, `NexusOracle!`.

3. Create a schema.

   ```sql theme={null}
   CREATE USER tutorial IDENTIFIED BY "NexusOracle!"
   DEFAULT TABLESPACE users
   TEMPORARY TABLESPACE temp
   QUOTA UNLIMITED ON users;
   ```

   <Note>In Oracle, `USER` is also the schema name.</Note>

4. Create a table inside the `tutorial` schema.

   ```sql theme={null}
   CREATE TABLE tutorial.test (
     id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
     name VARCHAR2(4000)
   );
   ```

5. Insert data into the table.

   ```sql theme={null}
   INSERT INTO tutorial.test (name) VALUES ('Nexus');
   INSERT INTO tutorial.test (name) VALUES ('Cognitive');
   ```

6. View all rows in the `test` table.

   ```sql theme={null}
   SELECT * FROM tutorial.test;
   ```

7. Permanently save the changes to make the data visible to NexusOne.

   ```sql theme={null}
   COMMIT;
   ```

## Ingest the database into NexusOne

Now that a dataset is available on the Oracle database, you can ingest the database
into NexusOne using the following steps:

1. Log in to NexusOne.
2. From the NexusOne homepage, navigate to **Ingest > Database > From Table**.
3. Enter the table and schema name:
   * **Source Schema**: `tutorial`
   * **Source Table**: `test`
4. Add connection details:
   * **Database URL**: `jdbc:oracle:thin:@//<database_address>:1521/ORCL`
   <Note>
     Port 1521 is Oracle's default port number.
   </Note>
   * **Username**: `oracleadmin`
   * **Password**: `NexusOracle!`
5. Add ingest details:
   * **Name**: `ingest_database`
   * **Schema**: `oracle_schema`
   * **Table**: `oracle_table`
   * **Schedule**: `Run Once`
   * **Mode**: `append`
   * **Tags**: Don't add any tags.
6. After configuring all fields, click **Ingest** to submit the job.
   Wait for a few minutes until you see a success message appear with an
   option to click **View Jobs**.

## Monitor job

When you ingest the database, this creates an Airflow job. To monitor the status of the job,
use the following steps:

1. Click **View Jobs**, or navigate to the NexusOne homepage and then click **Monitor**.
2. Find your job name, `ingest_database`, in the list, and watch its current status.
3. Wait for a few minutes, and then refresh your browser until the status changes to `Completed`.

## Visualize your dataset

Use the following steps to visualize your dataset:

1. On the NexusOne homepage, click **Discover** to launch Superset.
2. Hover your mouse over **SQL**, and then select **SQL Lab**.
3. Enter the following command in the query box:

```sql theme={null}
SELECT * FROM oracle_schema.oracle_table
```

<Card img="https://mintcdn.com/nexusone-4c77570d/U8sbVqp-9SWEIz2F/images/tutorials/ingest/database/visualize-oracle-v4-1-2.png?fit=max&auto=format&n=U8sbVqp-9SWEIz2F&q=85&s=05d4907102ea366e5f8bb25647cd8a43" width="994" height="494" data-path="images/tutorials/ingest/database/visualize-oracle-v4-1-2.png">
  Visualize your dataset
</Card>

## Delete the schema and Terraform resources

After completing the tutorial, delete the created schema and Oracle instance
using the following steps:

1. Delete the schema.

   ```sql theme={null}
   DROP USER tutorial CASCADE;
   ```

2. Destroy the Terraform resources.

   ```terraform theme={null}
   terraform destroy --auto-approve
   ```

## Additional resources

* To install sqlplus on macOS using Homebrew, refer to [this Stack Exchange answer](https://dba.stackexchange.com/a/273152/360277).
  You can copy either a `zip` or `dmg` file to the `homebrew-instantclient` directory.
* To create an Oracle database on Azure, refer to [Create an Oracle Database in an Azure VM](https://learn.microsoft.com/en-us/azure/virtual-machines/workloads/oracle/oracle-database-quick-create#create-the-database)
* To create an Oracle database on Google Cloud, refer to [Oracle Database@Google Cloud overview](https://docs.cloud.google.com/oracle/database/docs/overview)
* To get an overview of what database ingestion is, refer to [Data ingestion overview](/documentation/data-pipeline/overview/ingest#how-database-ingestion-works).
* For more information about roles or permissions, refer to [Govern Overview](/documentation/govern/overview).