Generic Oracle
On This Page
Oracle database is a cross-platform Relational Database Management System (RDBMS) that can run on various hardware across operating systems including Windows Server, Unix, and various distributions of GNU/Linux. It is commonly referred to as Oracle database, OracleDB, or simply Oracle. The database software endorses transaction processing, business intelligence and different analytics applications, and used by both small and large enterprises to store and manage data.
Prerequisites
-
Oracle database version is 11 or above.
-
Redo Log-based replication is enabled, for Pipeline mode as Redo Log, along with SYSDBA privileges for the database user.
Perform the following steps to configure your Generic Oracle Source:
Set up Redo Logs for Replication
Note: You must have SYSDBA privileges to perform the following steps for setting up Redo logs.
Connect to your Oracle server in SQL Developer or any SQL client tool and run the following commands:
1. Enable ARCHIVELOG
archival mode
Verify that the current archiving mode is ARCHIVELOG
:
SELECT LOG_MODE FROM V$DATABASE
If the archive mode is NOARCHIVELOG
, enable it using the following commands:
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
2. Configure Recovery Manager
Recovery Manager (RMAN) settings determine how long the database holds backlogs and archive logs.
-
Connect to the RMAN by executing the following commands on the command prompt:
RMAN CONNECT TARGET <username>` -- (to connect to your database) SHOW ALL; -- (to check the default settings)
-
Configure the log retention policy to 7 days:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
3. Enable supplemental logging
Supplemental logging ensures that columns are logged in Redo log files which are required by LogMiner to read the activity history for a database.
-
Enable supplemental logging at a database level:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
-
Enable supplemental logging at a table level:
ALTER TABLE <SCHEMA_NAME>.<TABLE_NAME> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
4. Grant permissions to run LogMiner
LogMiner is used to read the archived Redo logs.
Run the following commands to grant the required permissions to it:
GRANT SELECT ON SYS.V_$DATABASE TO <username>;
GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO <username>;
GRANT SELECT ON SYS.V_$LOGMNR_CONTENTS TO <username>;
GRANT EXECUTE ON DBMS_LOGMNR TO <username>;
GRANT EXECUTE ON DBMS_LOGMNR_D TO <username>;
GRANT SELECT ANY TRANSACTION TO <username>;
GRANT EXECUTE_CATALOG_ROLE TO <username>;
If you are using Oracle database version 12, grant the following additional permission:
GRANT LOGMINING TO <username>
Create a Database User and Grant Privileges
1. Create a database user (optional)
In order to create a database user, you must have the CREATE
privilege.
To create a user, open your Oracle server in SQL Developer or any SQL client tool and enter the following command:
CREATE USER hevo IDENTIFIED BY <password>;
GRANT CREATE SESSION TO hevo; --permission needed to connect to the database
2. Grant privileges to the User
The database user specified in the Hevo Pipeline must have the SELECT
privileges.
To assign this privilege, open your Oracle server in SQL Developer or any SQL client tool and enter the following commands:
-
Check current privileges assigned to the user:
select * from DBA_TAB_PRIVS where grantee = 'username';
-
Grant access to
all_views
including system tables:GRANT SELECT ON all_views to <username>;
-
Grant access to schemas of selected tables:
GRANT SELECT ON <schema_name>.<table_name> to <username>;
Retrieve the Service Name
Service name represents an alias of the unique Oracle database to which Hevo connects.
To retrieve the Service Name, open your Oracle server in any SQL client tool as a database user with SYSDBA
privilege and enter the following command:
select name from v$database;
Connecting to a Local Database
Refer to this section for steps to connect to your local database. For detailed information and troubleshooting help, read Connecting to a Local Database.
Prerequisites
-
Oracle service is running on your local machine.
-
Data to be loaded to the Destination is available in your Oracle database.
-
You have an account on ngrok and an installed ngrok utility on your local machine. To run ngrok on your local machine, follow these one-time steps:
-
Extract the ngrok utility:
-
On Linux or MacOS, unzip ngrok from a terminal:
unzip /path/to/ngrok.zip
-
On Windows, double-click ngrok.zip to extract it.
-
-
Authenticate ngrok in your local machine:
./ngrok authtoken <your_auth_token>
You can get the auth token from your ngrok dashboard. For example, in the image below, the auth_token starts with
1t7uGaJshUvnh2Fg1MApl
.
-
Connect to the local database
Perform the following steps to connect to the local database:
-
Log in to your database server.
-
Start a TCP tunnel forwarding to your database port.
./ngrok tcp <your_database_port>
For example, the port address for Oracle is 3306. Therefore, the command would be:
./ngrok tcp 3306
-
Copy the public IP address (hostname and port number) for your local database and port. For example, in the image below,
8.tcp.ngrok.io
is the database hostname and19789
is the port number. -
Paste the hostname and port number into the Database Host and Database Port fields respectively.
-
Specify all other settings and click TEST & CONTINUE.
Limitations
- Redo Log does not support user-defined data types. Therefore, fields with such data types are not captured in the log and are lost.
See Also
- Connecting Through Reverse SSH Tunnel
- Oracle User-Defined Types
- Redo Log
- Pipeline failure due to Redo Log expiry
Revision History
Refer to the following table for the list of key updates made to this page:
Date | Release | Description of Change |
---|---|---|
Jun-16-2022 | NA | Added section, Connecting to a Local Database. |
Dec-06-2021 | 1.77 | Added a See Also link to the Pipeline failure due to Redo Log expiry page. |
Nov-22-2021 | NA | Updated the Limitations section. |
Feb-22-2021 | 1.57 | Added sections: - Create a User and Grant Privileges - Retrieve the Service Name |