Oracle Connection

This is a step-by-step tutorial for creating an Oracle connection.

Prerequisite

To connect to Oracle, you must whitelist our IPs.

Singletenant vs. Multitenant Database Architecture in Oracle

In Oracle, the architectural approach can significantly impact the efficiency, scalability, and resource utilization of the system. One key decision in this regard is choosing between a Singletenant and Multitenant architecture. This section explores the fundamental differences between these 2 topoligies within the Oracle.

Singletenant Architecture

A Singletenant architecture, also known as a traditional or non-CDB (Container Database) architecture, involves the creation of standalone Oracle databases for each application or business unit. Each database operates independently, with its dedicated set of memory, processes, and data files. In this topologie, there is no shared infrastructure or resources between databases.

Multitenant Architecture

A Multitenant architecture, is built on the concept of a CDB (Container Database) that can host multiple Pluggable Databases (PDBs) within a shared infrastructure. The CDB contains common resources, while each PDB represents a distinct, isolated database within the shared container.

Rivery Support for Oracle Real Application Clusters (RAC)

Rivery extends its support for Oracle's CDB-PDB architecture by providing compatibility with Oracle Real Application Clusters (RAC). Oracle RAC allows multiple instances to access a single database simultaneously, providing high availability, scalability, and workload balancing.

RAC enables continuous database availability by distributing workload across multiple nodes, minimizing downtime and ensuring business continuity.

Considerations for Choosing Between Architectures

When deciding between Singletenant and Multitenant architectures in Oracle, considerations should include factors such as:

• Scalability Requirements: Multitenant architectures are well suited for environments with a large number of databases, offering scalability benefits through resource sharing.

• Isolation Needs: If strict isolation is a primary concern, Singletenant architectures may be more appropriate.

• Operational Efficiency: For streamlined administration and resource efficiency, Multitenant architectures are often preferred.

• Application Compatibility: The choice may also depend on the compatibility of applications with the Multitenant topologie.

Singletenant Configuration

Enabling Archivelog Mode

Before using the Oracle CDC Connector, you need to ensure that the Archivelog mode is enabled in your Oracle Database. If it is not already enabled, follow the steps below to enable it:

1. Log in as a sysdba user using SQL:

sqlplus / as sysdba

2. Verify if archivelog mode is already enabled:

SELECT log_mode FROM v$database;

If the result is "NOARCHIVELOG," the archivelog mode is disabled.

1. To enable archivelog mode, first shut down the database:

SHUTDOWN IMMEDIATE;

2. Start the database in mount mode:

STARTUP MOUNT;

3. Enable the archivelog mode:

ALTER DATABASE ARCHIVELOG;

4. Open the database:

ALTER DATABASE OPEN;

5. Verify that archivelog mode is now enabled:

SELECT log_mode FROM v$database;

Enabling Supplemental Logging

After enabling the archivelog mode, you need to enable supplemental logging, which provides additional information at the column level for update operations. Follow these steps to enable it:

1. Log in as a sysdba user using SQL:

sqlplus / as sysdba

2. Enable supplemental logging for all columns at the database level:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Required Permissions for LogMiner

Rivery retrieves data from LogMiner using Redologs file format.
To use Oracle LogMiner, users must have specific database privileges granted directly to them (roles are not sufficient). The following privileges are necessary:

1. 'SELECT ANY TRANSACTION': This privilege allows users to query the V$LOGMNR_CONTENTS view.

2. 'SELECT_CATALOG_ROLE': This role provides users with the ability to query data dictionary views.

3. 'EXECUTE_CATALOG_ROLE': This role provides users with the ability to execute packages owned by SYS, including the DBMS_LOGMNR package, which is essential for working with LogMiner.

To grant these privileges to a user, execute the following commands as a user with the necessary privileges, such as the SYS user:

GRANT SELECT ANY TRANSACTION TO username;
GRANT SELECT_CATALOG_ROLE TO username;
GRANT EXECUTE_CATALOG_ROLE TO username;

Replace username with the name of the user you want to grant the permissions to.

For details on configuring Multitenant, please refer to the Oracle CDC Configuration document.

Establishing a Connection

1. Type in the Connection Name.
2. Choose your Database Architecture.
   • Single-Tenant
   • Multi-Tenant (CDB/PDB).
3. Enter your Host name.
4. Fill in the Port (typically 1521 is the most common).
5. Input the name of your Database.
6. Select whether to connect using the System Identifier (SID) or Service Name.
   Please be aware of the following details regarding each option:

• System Identifier (SID) - Ensure that you enter the distinct name that uniquely identifies your instance or database in the Database field.
• Service Name - Ensure that you input the TNS alias name used for remote connection to your database in the Database field.

6. Enter your Username and Password.
7. Use the Test Connection function to see if your connection is up to the task. If the connection succeeded, you can now use this connection in Rivery.

SSH (Optional)

Secure Shell Protocol (SSH) is used to provide secure and encrypted access to internal databases from Rivery servers in order to retrieve data.

Configure your SSH Tunnel server in your network to use SSH Tunneling via Rivery. This server will be able to connect to your database server via the internal IP or address, and will accept SSH connections from Rivery IPs. Rivery uses a key file to connect to the SSH server.