Oracle Integration
In this article, we’ll integrate a Oracle Database data source with Y42.
This integration is powered by Singer's Oracle tap and supports Oracle 11g or above.
Oracle setup requirements:
- Create a tap_oracle database user
- Check if you have all the required credentials for replicating data from Oracle
- Configure Log-based Incremental Replication with LogMiner
Overview
Authentication
Your database credentials allow you to log into your database, either locally or remotely. A credential is a record that contains the authentication information. This information is used internally by server hosting the SQL database. The credentials entail the host address, the IP hosting the server/database, the port number, the default port on Oracle Database is 1521, the user and the password.
You may also use SSH tunneling or use a Y42 IP for whitelisting.
SSH tunneling is a method of transporting arbitrary networking data over an encrypted SSH connection. The SSH connection is used by the application to connect to the application server. With tunneling enabled, the application contacts to a port on the local host that the SSH client listens on. The SSH client then forwards the application over its encrypted tunnel to the server. See the configuration example page for detailed configuration instructions. The SSH command line options and SSH server configuration file pages may also be helpful.
Meanwhile, whitelisting means taking manual steps to ensure that a certain IP address isn't blocked from accessing your application by some automated security process. Whitelisting IP addresses enables you to control who can (or cannot) access your database. You can add an IP to Whitelist on the security configurations of your database.
Import Settings
This integration has no import settings.
Schema
There is no predefined schema for this integration. The integration will dynamically load the tables & columns which are defined within your specific system.
Updating your data
You have the option of scheduling Full imports or Incremental Imports by the month, weeks, days, and even by the hour. With the incremental import you will only add the new rows to that table, while a full import will actually re-import the entire table completely.
Incremental imports significantly increase the import speed, we import your table incrementally based on the last imported state e.g. id - If the table does not have a state, a full import will be triggered - This is done automatically.
Oracle setup requirements
Step 1. Create a tap_oracle database user
You may create a dedicated database user for Oracle Integration. Create a new user and grant the required permissions on the database, schema and tables that you want to replicate:
CREATE USER tap_oracle IDENTIFIEDBY <password>
GRANT CONNECT TO tap_oracle
GRANT CREATE SESSION TO tap_oracle
GRANT UNLIMITED TABLESPACE TO tap_oracle
GRANT USAGE ON SCHEMA <schema_name> TO tap_oracle
GRANT SELECT ON <schema_name>.<table_name> TO tap_oracle
(Repeat the last grant statement on every table that you want to replicate)
Step 2: Check if you have all the required credentials for replicating data from Oracle
Access to V$DATABASE and V_$THREAD performance views. These are required to verify setting configuration while setting up your Oracle database and to retrieve the database’s Oracle System ID.
Step 3: Configure Log-based Incremental Replication with LogMiner
This step is only required if you use Log Based replication method.
Step 3.1: Verify the database’s current archiving mode
To check the database’s current mode, run:
SELECT LOG_MODE FROM V$DATABASE
If the result is ARCHIVELOG , archiving is enabled and no further action is required. Skip to Step 3.3 to configure RMAN backups.
Step 3.2: Enable ARCHIVELOG mode
Shut down the database instance. The database and any associated instances must be shut down before the database’s archiving mode can be changed.
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
SQL> ALTER DATABASE ARCHIVELOG
SQL> ALTER DATABASE OPEN
Step 3.3: Set retention period by RMAN
RMAN> CONNECT TARGET /
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
Note: To ensure that archive log files don’t consume all of your available disk space, you should also set the DB_RECOVERY_FILE_DEST_SIZE parameter to a value that agrees with your available disk quota. Refer to for more info about this parameter.
SQL ALTER SYSTEM SET
DB_RECOVERY_FILE_DEST_SIZE = 1G
SCOPE=BOTH SID='*';
Step 3.4: Enable supplemental logging
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS
Note: Alternatively to enable supplemental logging at the table level, run for every table you want to replicate:
ALTER TABLE <SCHEMA_NAME>.TABLE_NAME> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS
Verify that supplemental logging was successfully enabled by running the following query:
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE
If the returned value is YES or IMPLICIT, supplemental logging is enabled.
Warning: If you want to use Log-based Incremental Replication, you’ll also need to grant additional permissions to tap_oracle user:
GRANT EXECUTE_CATALOG_ROLE TO tap_oracle
GRANT SELECT ANY TRANSACTION TO tap_oracle
GRANT SELECT ANY DICTIONARY TO tap_oracle
GRANT EXECUTE ON DBMS_LOGMNR TO tap_oracle
GRANT EXECUTE ON DBMS_LOGMNR_D TO tap_oracle
GRANT SELECT ON SYS.V_$DATABASE TO tap_oracle
GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO tap_oracle
GRANT SELECT ON SYS.V_$LOGMNR_CONTENTS TO tap_oracle
If you’re using version 12 of Oracle, you’ll also need to grant the LOGMINING privilege to the tap_oracle user:
GRANT LOGMINING TO tap_oracle
Oracle Database Setup Guide:
- On Integrate, click on "Add..." to search for Oracle and select it.
- Name your integration.
- Authorize access with your database credentials. Use SSH tunnel(s) to connect or use a Y42 IP for whitelisting.
- After authentication, you are good to go and start importing your tables.
- Select the tables you need and click import. You can start accessing the tables once the status is “Ready”.