Supported versions

Tonic Structural supports Oracle versions 12.1 and above.

Table mode limitations

Oracle workspaces cannot use the Incremental table mode.

No output to container artifacts

For Oracle workspaces, you cannot write the destination data to container artifacts.

Creating the source database user

This is the Oracle user that connects to the source database.

The source destination user can be the same as the schema of the data. However, we recommend that you create a Tonic Structural-specific user that has more restricted or read-only access.

The details for providing the required access are slightly different based on whether you use database links. By default, Structural assumes that database links are not enabled. If database links are enabled, then set the environment setting TONIC_ORACLE_DBLINK_ENABLED to true for both the worker and the web server. You can configure this environment setting from Structural Settings. For more information, go to Configuring environment settings.

TONIC_ORACLE_DBLINK_ENABLED is false (default)

If TONIC_ORACLE_DBLINK_ENABLED is false, then to create the source database user, you can use one of the following options to create the source database user and grant the required access:

• #oracle-source-user-dblink-false-select-any-dictionary

• #oracle-source-user-dblink-false-select-catalog-role

• #oracle-source-user-dblink-false-all-views

Grant the SELECT ANY DICTIONARY privilege

One way to grant the required access is to grant the SELECT ANY DICTIONARY privilege.

If you choose that option, then to create the source database user:

--create a user
CREATE USER TONIC IDENTIFIED BY "<tonic_password>";

--give the user the required access
GRANT CREATE SESSION TO TONIC;
GRANT SELECT ON SESSION_ROLES TO TONIC;
GRANT EXECUTE ON DBMS_METADATA TO TONIC;
GRANT SELECT ANY DICTIONARY TO TONIC;

--give the user access to tables in your preferred schema
BEGIN
    FOR x IN (SELECT owner, table_name FROM all_tables WHERE owner = '<source_schema>')
    LOOP
        EXECUTE IMMEDIATE 'GRANT SELECT ON "' || x.owner || '"."' || x.table_name || '" to TONIC';
    END LOOP;
END;

Grant the SELECT_CATALOG_ROLE role

Instead of the SELECT ANY DICTIONARY privilege, you can grant the SELECT_CATALOG_ROLE role.

If you choose that option, then to create the source database user:

--create a user
CREATE USER TONIC IDENTIFIED BY "<tonic_password>";

--give the user the required access
GRANT CREATE SESSION TO TONIC;
GRANT SELECT ON SESSION_ROLES TO TONIC;
GRANT EXECUTE ON DBMS_METADATA TO TONIC;
GRANT SELECT_CATALOG_ROLE TO TONIC;

--give the user access to tables in your preferred schema
BEGIN
    FOR x IN (SELECT owner, table_name FROM all_tables WHERE owner = '<source_schema>')
    LOOP
        EXECUTE IMMEDIATE 'GRANT SELECT ON "' || x.owner || '"."' || x.table_name || '" to TONIC';
    END LOOP;
END;

Grant access to the ALL_ views (not recommended)

If for security reasons you cannot use either of the previous options, Structural can use the ALL_* views that are provided automatically in Oracle.

By default, an Oracle installation uses a GRANT to PUBLIC to grant all users access to all tables that start with ALL_. For example:

GRANT SELECT ALL_TABLES TO PUBLIC
GRANT SELECT ALL_COLUMNS TO PUBLIC
...

If you do not revoke the PUBLIC access to the ALL_* views, then Structural can use this access to connect to the source database. To create the source database user:

--create a user
CREATE USER TONIC IDENTIFIED BY "<tonic_password>";

--give the user the required access
GRANT CREATE SESSION TO TONIC;
GRANT SELECT ON SESSION_ROLES TO TONIC;
GRANT EXECUTE ON DBMS_METADATA TO TONIC;
GRANT SELECT DBA_SEGMENTS TO TONIC;
GRANT SELECT DBA_LOBS TO TONIC;

--give the user access to tables in your preferred schema
BEGIN
    FOR x IN (SELECT owner, table_name FROM all_tables WHERE owner = '<source_schema>')
    LOOP
        EXECUTE IMMEDIATE 'GRANT SELECT ON "' || x.owner || '"."' || x.table_name || '" to TONIC';
    END LOOP;
END;

If you do revoke the PUBLIC access to the ALL_* views, then you must specifically grant access to the source database user for the required ALL_* views. To create the source database user:

--create a user
CREATE USER TONIC IDENTIFIED BY "<tonic_password>";

--give the user the required access
GRANT CREATE SESSION TO TONIC;
GRANT SELECT ON SESSION_ROLES TO TONIC;
GRANT EXECUTE ON DBMS_METADATA TO TONIC;
GRANT SELECT DBA_SEGMENTS TO TONIC;
GRANT SELECT DBA_LOBS TO TONIC;
GRANT SELECT ALL_COLL_TYPES TO TONIC;
GRANT SELECT ALL_CONS_COLUMNS TO TONIC;
GRANT SELECT ALL_CONSTRAINTS TO TONIC;
GRANT SELECT ALL_DEPENDENCIES TO TONIC;
GRANT SELECT ALL_IND_COLUMNS TO TONIC;
GRANT SELECT ALL_INDEXES TO TONIC;
GRANT SELECT ALL_MVIEW_LOGS TO TONIC;
GRANT SELECT ALL_MVIEWS TO TONIC;
GRANT SELECT ALL_NESTED_TABLES TO TONIC;
GRANT SELECT ALL_OBJECTS TO TONIC;
GRANT SELECT ALL_PROCEDURES TO TONIC;
GRANT SELECT ALL_TAB_COLS TO TONIC;
GRANT SELECT ALL_TAB_COLUMNS TO TONIC;
GRANT SELECT ALL_TAB_PRIVS TO TONIC;
GRANT SELECT ALL_TABLES TO TONIC;
GRANT SELECT ALL_TYPE_ATTRS TO TONIC;
GRANT SELECT ALL_TYPES TO TONIC;
GRANT SELECT ALL_USERS TO TONIC;

--give the user access to tables in your preferred schema
BEGIN
    FOR x IN (SELECT owner, table_name FROM all_tables WHERE owner = '<source_schema>')
    LOOP
        EXECUTE IMMEDIATE 'GRANT SELECT ON "' || x.owner || '"."' || x.table_name || '" to TONIC';
    END LOOP;
END;

TONIC_ORACLE_DBLINK_ENABLED is true

If TONIC_ORACLE_DBLINK_ENABLED is true, then to create the source database user:

--create a user
CREATE USER TONIC IDENTIFIED BY "<tonic_password>";

--give the user the required access
GRANT CREATE SESSION TO TONIC;
GRANT SELECT ON SESSION_ROLES TO TONIC;
GRANT EXP_FULL_DATABASE TO TONIC;
GRANT EXECUTE ON DBMS_METADATA TO TONIC;

--give the user access to tables in your preferred schema
BEGIN
    FOR x IN (SELECT owner, table_name FROM all_tables WHERE owner = '<source_schema>')
    LOOP
        EXECUTE IMMEDIATE 'GRANT SELECT ON "' || x.owner || '"."' || x.table_name || '" to TONIC';
    END LOOP;
END;

If TONIC_ORACLE_DBLINK_ENABLED is true, then Structural must be able to create a database link between the destination database and the source database. Structural uses the network link to process a data generation job.

Creating the destination database user and schema

This is the Oracle user that connects to the destination database. This user cannot be the same user as the output schema of the data.

Structural does not create the schema or the tablespace in the destination database. You must create the schemas and tablespaces before you generate data.

--create a new user
CREATE USER TONIC IDENTIFIED BY "<tonic_password>";

--this user must be granted more extensive privileges
GRANT ALL PRIVILEGES, IMP_FULL_DATABASE to TONIC;

--create the destination schema
CREATE USER <OUTPUT_NAME> IDENTIFIED BY "<OUTPUT_PASSWORD>";

Configuring whether Structural creates the destination database schema

By default, during each data generation job, Structural creates the database schema for the destination database tables, then populates the database tables based on the workspace configuration.

If you prefer to manage the destination database schema yourself, then set the environment setting TONIC_ORACLE_SKIP_CREATE_DB to true. You can add this setting manually to the Environment Settings list on Structural Settings.

When TONIC_ORACLE_SKIP_CREATE_DB is true, then Structural does not create the destination database schema. Before you run data generation, you must create the destination database with the full schema.

During data generation, Structural deletes the data from the destination database tables, except in the following cases:

• Tables that use Preserve Destination mode

• Upsert data generation

It then populates the tables with the new destination data.

Oracle is a relational database management system. Tonic Structural can read source data from and write destination data to an Oracle database.

The following limitations apply when Tonic Structural creates the destination database:

• For Oracle advanced queues:

  • Structural does not copy messages from the source database to the destination database.

  • Structural does not copy queue subscribers to the destination database. You must add them to the destination database manually.

• In the destination database, Structural creates external tables as relational tables.

• Structural does not create database links in the destination database. You must create any required database links manually.

In the workspace configuration, select Oracle as the connection type.

Connecting to the source database

In the Source Settings section, provide the details about the source database:

1. In the Server field, provide the server where the database is located.

2. In the Service Name field, provide the name of the service for the source database.

3. In the Port field, provide the port to use to connect to the database.

4. In the Username field, provide the username for the account to use to connect to the database.

5. In the Password field, provide the password for the specified username.

6. In the Schema field, provide the schema for the source database.

7. To use TCPS instead of TCP:

   1. Toggle Enable TCPS to the on position.

   2. Select the Oracle Wallet file to use.

8. To test the connection to the source database, click Test Source Connection.

Blocking data generation on all schema changes

By default, data generation is not blocked as long as schema changes do not conflict with your workspace configuration.

To block data generation when there are any schema changes, regardless of whether they conflict with your workspace configuration, toggle Block data generation on schema changes to the on position.

Indicating whether to use source database file preferences in the destination database

By default, the database file preferences such as tablespaces and filegroups for the destination database are configured and stored in the destination database.

To instead use database file preferences from the source database, toggle Preserve source database disk storage preferences in destination database to the on position.

Connecting to the intermediate database for upsert

Oracle supports the upsert process. When you enable upsert for the workspace, the data generation process initially writes the transformed data to an intermediate database.

After the initial data generation is complete, the upsert job writes new records to the destination database, and updates existing records in the destination database. It does not touch any other records that are in the destination database.

In the Upsert section, when you enable upsert, you are prompted to configure the upsert processing and to provide the connection information for the intermediate database.

If the intermediate database is in the same location as the source database, then you can copy the connection and authentication details from the source database.

Copying the connection and authentication details from the source database

To copy the connection and authentication details from the source database:

1. Click Copy Settings from Source.

2. In the Password field, provide the password.

3. To test the connection to the destination database, click Test Intermediate Connection.

Providing the connection details

To provide the connection details for the intermediate database:

1. In the Server field, provide the server where the database is located.

2. In the Service Name field, provide the name of the service for the source database.

3. In the Port field, provide the port to use to connect to the database.

4. In the Username field, provide the username for the account to use to connect to the database.

5. In the Password field, provide the password for the specified username.

6. In the Schema field, provide the schema for the source database.

7. To use TCPS instead of TCP:

   1. Toggle Enable TCPS to the on position.

   2. Select the Oracle Wallet file to use.

8. To test the connection to the intermediate database, click Test Intermediate Connection.

Connecting to the destination database

In the Destination Settings section, provide the connection details for the destination database.

Copying the connection details from the source database

To copy the connection details from the source database:

1. Click Copy Settings from Source.

2. In the Password field, provide the password.

3. To test the connection to the destination database, click Test Destination Connection.

Providing destination database connection details

If you do not copy the source connection details, then to specify the connection information for the destination database:

1. In the Server field, provide the server where the database is located.

2. In the Service name field, provide the name of the service for the destination database.

3. In the Port field, provide the port to use to connect to the database.

4. In the Username field, provide the username for the account to use to connect to the database.

5. In the Password field, provide the password for the specified username.

6. In the Schema field, provide the schema for the destination database.

7. To use TCPS instead of TCP:

   1. Toggle Enable TCPS to the on position.

   2. Select the Oracle Wallet file to use.

8. To test the connection to the destination database, click Test Destination Connection.

How Tonic Structural uses the connection details to populate the connection string

When connecting to the source or destination database, Structural uses the connection details to populate the connection string as follows:

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=<SERVER>)(PORT=<PORT>)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=<SERVICE NAME>)))

Where:

• <SERVER> is the value from the Server field.

• <PORT> is the value from the Port field.

• <SERVICE NAME> is the value from the Service name field.