Configure Extract for Oracle Autonomous Database

Following are the steps to configure an Extract to capture from an Oracle Autonomous Database :

Install Oracle GoldenGate for your Oracle Autonomous Database instance.
Create a deployment for the Oracle GoldenGate environment. This is the deployment where the Extract that captures data from the Oracle Autonomous Database instance will be created. See Add a Deployment.
Obtain Oracle Autonomous Database Client Credentials.

To establish connection to your Oracle Autonomous Database instance, download the client credentials file. To download client credentials, you can use the Oracle Cloud Infrastructure Console or Database Actions Launchpad. See Downloading Client Credentials (Wallets).

Note:

If you do not have administrator access to the Oracle Autonomous Database, you should ask your service administrator to download and provide the credentials files to you.

The following steps use the Database Actions Launchpad to download the client credentials.
1. Log in to your Oracle Autonomous Database account.
2. From the Database Instance page, click Database Actions. This launches the Database Actions Launchpad. The Launchpad attempts to log you into the database as ADMIN. If that is not successful, you will be prompted for your database ADMIN username and password.
3. On the Database Actions Launchpad, under Administration, click Download Client Credentials (Wallets).
4. Enter a password to secure your Client Credentials zip file and click Download.
  
  Note:
  The password you provide when you download the wallet protects the downloaded Client Credentials wallet.
5. Save the credentials zip file to your local system.
The credentials zip file contains the following files:
- cwallet.sso
- ewallet.p12
- keystore.jks
- ojdbc.properties
- sqlnet.ora
- tnsnames.ora
- truststore.jks
- ewallet.pem
- README.txt
Refer and update (if required) the sqlnet.ora and tnsnames.ora files while configuring Oracle GoldenGate to work with the Autonomous Database instance.
Configure the server where Oracle GoldenGate is running to connect to the Autonomous Database instance.
1. Log in to the server where Oracle GoldenGate was installed.
2. Transfer the credentials zip file that you downloaded from Oracle Autonomous database instance to the Oracle GoldenGate server.
3. In the Oracle GoldenGate server, unzip the credentials file into a new directory, for example: /u02/data/adwc_credentials. This is your key directory.
4. To configure the connection details, open your tnsnames.ora file from the Oracle client location in the Oracle GoldenGate instance.
5. Use the connection string with the LOW consumer group dbname_low, for example, graphdb1_low, and move it to your local tnsnames.ora file.
  
  The connections for the default ggadmin user happens only with low, although high is used in the connection string, for example graphdb1_high.
  
  See Local Naming Parameters in the tnsnames.ora File chapter in the Oracle Database Net Services Reference guide.
  Note:
  The tnsnames.ora file provided with the credentials file contains three database service names identifiable as:
```
ADWC_Database_Name_low
ADWC_Database_Name_medium
ADWC_Database_Name_high
```
  Oracle recommends that you use ADWC_Database_Name_low with Oracle GoldenGate. See Predefined Database Service Names for Autonomous Database in the Using Oracle Autonomous Database Serverless guide or Predefined Database Service Names for Autonomous Databases for Oracle Autonomous Database on Dedicated Exadata Infrastructure.
6. Edit the tnsnames.ora file in the Oracle GoldenGate instance to include the connection details available in the tnsnames.ora file in your key directory (the directory where you unzipped the credentials zip file downloaded from the Autonomous Database).
  Note:
  Autonomous Database allows connections on ports 1521 and 1522. However, the Oracle GoldenGate connection only works with the port 1522. This is mandatory. If you use 1521, you get the following error:
```
Login failed. OCI Error ORA (status = 24459-ORA-24459: OCISessionGet() timed out waiting for pool to create new connections\\n)", "code": "OGG-08110"
```
```
Sample Connection String
adw1_low. = (description=
                 (retry_count=20)(retry_delay=3)
                 (address=(protocol=tcps)(port=1522)(host=adb-preprod.us-phoenix-1.oraclecloud.com))
                 (connect_data=(service_name=okd2ybgcz4mjx94_graphdb1_low.adb.oraclecloud.com))
                 (security=(ssl_server_cert_dn="CN=adwc-preprod.uscom-east-1.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US"))
               )
```
  If the database is within a firewall protected environment, you might not have direct access to the database. With an existing HTTP Proxy, you can pass the firewall with the following modifications to the sqlnet.ora and tnsnames.ora:
  - sqlnet parameters
  - address modification of tns_alias
  If Extract becomes unresponsive due to a network timeout or connection loss, then you can add the following into the connection profile in the tnsnames.ora file:
```
(DESCRIPTION = (RECV_TIMEOUT=30) (ADDRESS_LIST =
      (LOAD_BALANCE=off)(FAILOVER=on)(CONNECT_TIMEOUT=3)(RETRY_COUNT=3) (ADDRESS = (PROTOCOL = TCP)(HOST = adb-preprod.us-phoenix-1.oraclecloud.com)(PORT = 1522))
```
7. To configure the wallet, create a sqlnet.ora file in the Oracle client location in the Oracle GoldenGate instance.
```
cd /u02/data/oci/network/admin
ls
sqlnet.ora tnsnames.ora
```
  See Autonomous Database Client Credentials in Using Oracle GoldenGate on Oracle Cloud Marketplace.
8. Edit this sqlnet.ora file to include your key directory.
```
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/u02/data/adwc_credentials"))) 
SSL_SERVER_DN_MATCH=yes
```
Use Admin Client to log into the Oracle GoldenGate deployment, depending on whether you are using Microservices.
Create a credential to store the GGADMIN user and password. This user will be used to connect to the Autonomous Database from the command line, to perform commands that require a database connection. It will also be used in the USERIDALIAS parameter for the Extract database connection.
```
ALTER CREDENTIALSTORE ADD USER 
ggadmin@dbgraph1_low PASSWORD complex_password alias adb_alias
```
You can also use the following command with high in the connection string, but it will still connect with low:
```
ALTER CREDENTIALSTORE ADD USER 
ggadmin@dbgraph1_high PASSWORD complex_password alias adb_alias
```
Connect to the database using DBLOGIN. The DBLOGIN user should be the adb_alias account user.
```
DBLOGIN USERIDALIAS adb_alias
```
Configure supplemental logging on the tables, which you want to capture using ADD TRANDATA or ADD SCHEMATRANDATA. Remember that you are connected directly to the database instance, so there is no need to include the database name in these commands. Here's an exmaple:
```
ADD TRANDATA HR.EMP
```
or
```
ADD SCHEMATRANDATA HR
```
See Enable Minimal Supplemental Logging in Oracle Autonomous Database for Oracle GoldenGate Extract.
Add heartbeat table.
```
ADD HEARTBEATTABLE
```
Add and configure an Extract to capture from the Oracle Autonomous Database instance. See Add an Online Extract for steps to create an Extract.

Oracle GoldenGate Extract is designed to work with the Oracle Autonomous Database instance to ensure that it only captures from a specific database instance. This means that the database instance name is not needed for any TABLE or MAP statements.
The following example creates an Extract (required for capturing from an Oracle Autonomous Database) called exte, and instructs it to begin now.
```
ADD EXTRACT exte, INTEGRATED TRANLOG, BEGIN NOW 
```
To capture specific tables, use the two part object names.. For example, to capture from the table HR.EMP, in your Oracle Autonomous Database instance, use this entry in the Extract parameter file.
```
TABLE HR.EMP;
```
If you want to replicate HR.EMP into COUNTRY.EMPLOYEE, then your map statement would look like this:
```
MAP HR.EMP, TARGET COUNTRY.EMPLOYEE;
```
Register Extract with the Oracle Autonomous Database instance. For example, to register an Extract named exte, use the following command:
```
REGISTER EXTRACT exte DATABASE
```
You can now start your Extract and perform data replication to the Oracle Autonomous Database instance. Here's an example:
```
START EXTRACT exte
```
This completes the process of configuring an Extract for Oracle Autonomous Database and you can use it like any other Extract process.