Add a Replicat for Snowflake

Learn to add a Replicat process for a Snowflake target in an OCI GoldenGate data replication.

Before you begin

Verify that the target tables exist on the Snowflake database.
You must have Amazon Web Services, Google Cloud Platform, or Azure cloud accounts set up if you intend to use any of the external stage locations such as, S3, ADLS Gen2, or GCS.
Snowflake JDBC driver connection string
The database user used for replicating into Snowflake has to be granted the following privileges:
- INSERT, UPDATE, DELETE, and TRUNCATE on the target tables.
- CREATE and DROP on Snowflake named stage and external stage.
- If using external stage (S3, ADLS, GCS), CREATE, ALTER, and DROP external table.
Review Configuration Considerations and Best Practices for Snowflake

Add a Replicat (26ai)

Ensure that you review prerequisites specific to this target type.

In the OCI GoldenGate deployment console navigation menu, select Replicat.
On the Replicat page, select Add Replicat.
In the Add Replicat panel, on the Replicat Information page, complete the fields as needed, and then select Next:
- Select a Replicat Type:
  - Classic Replicat
  - Coordinated Replicat
- Enter a Process Name, no more than 5 characters long.
- Enter a Description, to help distinguish this process from others.
On the Replicat Options page, complete the fields as needed, and then select Next:
1. For Replicat Trail:
  - Enter a Name.
  - Enter a Subdirectory, if you expect the Trail file to be in a location other than the default.
  - Select an Encryption profile. If an encryption profile wasn't created, the Local Wallet is selected by default.
  - For Begin, select the starting point for data processing:
    - Position in Log (default)
    - Now
    - Custom Time
2. For Trail Position:
  - Enter the Sequence Number.
  - Enter the RBA Offset.
3. For Target, select Snowflake from the dropdown, and then select one of the following:
  1. Streaming
  2. Stage and Merge, and then select an Available staging location
4. For Target Credentials:
  - Select a Domain.
  - Select an Alias within the specified Domain.
  - Select a Checkpoint Table. Leave the default selection or select the Checkpoint table you created for the target deployment.
  - Select the Max Threads Number (Coordinated Replicats only).
On the Managed Options page, complete the optional fields as needed, and then select Next:
- Select a Profile Name, if one is available.
- Enable Critical to deployment health.
- Enable and configure Auto Start settings.
On the Parameter File page, you can add parameters to further configure your Replicat. For example:
```
table source.table1;
```
Note:

GoldenGate uses Greenwich Mean Time (GMT) by default. Use SETENV to override the default setting for the Replicat. This change doesn’t apply to deployments. For example:
```
setenv (TZ="US/Eastern")
setenv (TZ="GMT+5")
```
(Optional) On the Properties File page, review the property:
- jvm.bootoptions: Increase the -Xmx value if you need to add more memory to the Replicat JVM (-Xmx16g to use up to 16 GB of memory, -Xmx32g for up to 32 GB, and so on).
  Note: A known issue related Snowflake Streaming Handler fails with the error, Exception: java.lang.StackOverflowError thrown from the UncaughtExceptionHandler in thread "process reaper". To workaround this issue, add the following to your Replicat properties file:
```
jvm.bootoptions= -Djdk.lang.processReaperUseDefaultStackSize=true
```
For information on this target's properties and learn the differences between Stage and Merge and Streaming Handlers, see Snowflake in the Oracle GoldenGate for Distributed Applications and Analytics guide.
Select Create and Run. If you select Create, then you can manually start the Replicat later from the Replicats page.

Add a Replicat (21c)

To add a Replicat for Snowflake in Oracle GoldenGate 21c:

In the OCI GoldenGate deployment console, on the Administration Service Overview page, select Add Replicat (plus icon).
On the Add Replicat page, select a Replicat type, and then select Next.

The types of Replicats are:
- Classic Replicat
- Coordinated Replicat
On the Replicat Options page, complete the following fields, and then select Next:
1. For Process Name, enter a name for the Replicat process.
2. For Trail Name, enter two-character trail name.
3. For Target, select the target Snowflake connection from the dropdown.
4. For Available Aliases, select your alias from the dropdown.
5. (Optional) Enable external storage to select an available staging location from the dropdown.
On the Replicat Parameters page, add the necessary mappings, and then select Next:
Note:
- If using Coordinated Replicat, add the following parameters on the second line of the Parameter File:
```
TARGETDB LIBFILE libggjava.so SET property=/u02/Deployment/etc/conf/ogg/.properties
```
Using coordinated replicat will result in multiple files created. If omitted, you will encounter the following error:
```
OGG-01091 Oracle GoldenGate Delivery, RSNOW.prm: Unable to open file "/u02/Deployment/etc/conf/ogg/001.properties" (error 2, No such file or directory).
```
- If there are long-running merge operations in Snowflake and a high number of locks are observed, ensure that no other tables are modifying the same table. Also, use coordinated replicat with caution as it may lead to a high number of table locks.
- GoldenGate uses Greenwich Mean Time (GMT) by default. Use SETENV to override the default setting for the Replicat. This change doesn't apply to deployments. For example:
```
setenv (TZ="US/Eastern")
setenv (TZ="GMT+5")
```
Learn more about SETENV.
(Optional) On the Properties File page, review the following properties:

Note: These properties are set when you created the connection and shouldn’t be modified here.
- ggs.eventhandler.snowflake.connectionURL: JDBC URL to connect to Snowflake.
- ggs.eventhandler.snowflake.UserName: Snowflake database username.
- ggs.eventhandler.snowflake.Password: Password associated with the Snowflake database user.
- ggs.eventhandler.snowflake.storageIntegration: The credential for Snowflake data warehouse to access the respective Object store files. For more information, see Snowflake storage integration.
- jvm.bootoptions: Increase the -Xmx value if you need to add more memory to the Replicat JVM (-Xmx16g to use up to 16 GB of memory, -Xmx32g for up to 32 GB, and so on.)
Select Create and Run. If you select Create, then you can manually start the Replicat later from the Administration Service Overview page.

You return to the Administration Service Overview page where you can view the creation of the Replicat process and access Replicat Actions.

Snowflake target details

Known issues

Bug 37518857: OGGDAA GGS Snowflake Streaming Replicat error

Snowflake Streaming Handler fails with: Exception: java.lang.StackOverflowError thrown from the UncaughtExceptionHandler in thread "process reaper"

Workaround: Add the following to your Replicat Properties file:

jvm.bootoptions= -Djdk.lang.processReaperUseDefaultStackSize=true