The user that is used for the Oracle GoldenGate Extract process and the user that is used to enable supplemental login need different sets of privileges.

See the following topics in the Oracle GoldenGate documentation for instructions:

• For the Extract user for Microsoft SQL Server, see only the Extract user section in Extract and Replicat Users for SQL Server.
• For the user that enables supplemental login, see User that Enables Supplemental Logging and Other Features.

The Manager process can run as a Microsoft Windows service, or it can run interactively as the current user.

The Manager process requires the following:

• Full control permissions over the files and folders within the Oracle GoldenGate directories.
• Full control permissions over the trail files, if they're stored in a location other than the Oracle GoldenGate directory.
• Membership in the server's local administrators group (on all nodes in a cluster).
• If you're running the Manager process as a Windows service with an Extract that is connected to a remote database using Windows Authentication, the process attempts to log in to the database with the account that the Manager process is running under. Ensure that the Manager's service account has the correct access to the remote SQL Server instance.

The programs that capture data for the Extract run under the Manager account and inherit the Manager's operating system privileges.

Create a file named GLOBALS.txt in the root folder of Oracle GoldenGate.

Add a new schema in the database to be used by Oracle GoldenGate objects that may get created in the database. Open the GLOBALS.txt file and write GGSCHEMA <schema_name>. Use the GGSCHEMA parameter to specify the name of the schema that contains the database objects that are owned by Oracle GoldenGate, such as those that support data definition language (DDL) replication for trigger-based replication, those that are a part of the heartbeat table implementation, and those that are part of the SQL Server CDC Capture and Cleanup implementation. After creating the GLOBALS file, remove the .txt extension. The schema name mentioned under GGSCHEMA is treated as a system object, and table names with wildcards under GGSCHEMA are excluded from the Extract. If you need to capture in GGSCHEMA, don't use wildcards and make sure that you explicitly map the respective table names.

Open the command prompt in the location of the GoldenGate folder and run ggsci.exe in the command prompt, or you can directly run this as an administrator.

After running ggsci.exe, the GoldenGate command prompt appears.

Run the following command at the GGSCI command prompt:

create subdirs

To create a Manager process, use the following steps:

1. Enter the following command in GGSCI:

   edit params mgr

   Notepad or a similar editor opens the parameter file.

2. In the parameter file, enter the port number for the Manager process. It can be any port number except the well-known port numbers. Use the following format: port <port_number>

   For example: port 3456

3. Enter the following command:

   start mgr

   This starts the Manager process and only enables communication between the Manager process and the local Oracle GoldenGate instance. For more information about the parameters and configuring other types of network communications for the Manager, see Configuring Manager and Network Communications in the Oracle GoldenGate documentation.

To see if the manager process is running, enter the following command:

info all

(Optional) To add a Manager process as a Windows service, run the following commands. You'll receive a warning or error message if the Manager process is already running as a Windows service. In that case, you don't need to add the Manager process as a Windows service.

stop mgr

shell install addservice

start mgr

The Extract connects to a source SQL Server database through an Open Database Connectivity (ODBC) connection.

To create this connection, set up a data source name (DSN) through the Data Sources (ODBC) control panel. For instructions, see Configuring an Extract Database Connection in the Oracle GoldenGate documentation.

To create a CDC Capture process, you enable supplemental logging and create an Oracle GoldenGate CDC cleanup job.

See the following instructions:

• Enabling CDC Supplemental Logging
• Purging the CDC Staging Data

This section discusses the steps to initiate the CDC Extract process.

Before creating a parameter file for CDC Extract, make sure that you're already logged in to the database through GGSCI, supplemental logging and Oracle GoldenGate's CDC Cleanup job are enabled, and the Manager process is running. The following file is a sample parameter file for the CDC Extract process. For more detailed information on the fields in the parameter file, see Valid and Invalid Parameters for CDC Capture in the Oracle GoldenGate documentation.

To create and save a new Extract parameter file, enter the following command in GGSCI:

edit params <extract_name>

For example:

edit params exta

Notepad or a similar editor opens for you to add the required parameters. The following example parameter file has the minimum required parameters:

EXTRACT <extract_name>
SOURCEDB <dsn> USERID <username> PASSWORD <password>
OUTPUTFORMAT XML _AUDIT_VAULT
EXTTRAIL .\dirdat\{Any combination of two alphabets indicating prefix of trail file e.g. ab, bc, ea, sn.....etc}
TABLE owner.table_name;

The following example parameter file is for a single table. Here the dirdat folder will contain the trail files that Oracle GoldenGate generates.

EXTRACT exta
SOURCEDB GGDB USERID sa PASSWORD passwd
OUTPUTFORMAT XML _AUDIT_VAULT
EXTTRAIL .\dirdat\ea
TABLE dbo.employee;

To add the Extract process, run the following commands in GGSCI:

add extract exta, tranlog, begin now

add exttrail .\dirdat\ea, extract exta

Make sure that the Manager process is already running, and then start the Extract with the following commands in GGSCI:

start extract exta

info all

This starts the Extract process. From this point onward, every DML operation on the tables that are monitored by the Extract will be captured and entered in the trail file in the dirdat folder. To learn more about the Extract process, the naming conventions, creating trail, and so on, see Configuring Online Change Synchronization.

To stop the Extract process, enter the following command:

stop exta

The user that is used for the Oracle GoldenGate Extract process and the user that is used to enable supplemental login need different sets of privileges.

See the following topics in the Oracle GoldenGate documentation for instructions:

• For the Extract user for Microsoft SQL Server, see only the Extract user section in Extract and Replicat Users for SQL Server.
• For the user that enables supplemental login, see User that Enables Supplemental Logging and Other Features.

The Extract connects to a source SQL Server database through an Open Database Connectivity (ODBC) connection.

To create this connection, set up a data source name (DSN) through the Data Sources (ODBC) control panel. For instructions, see Configuring an Extract Database Connection in the Oracle GoldenGate documentation.

Configure the database credentials and TRANDATA information for Oracle GoldenGate.

1. Open the Administration Service page in the Oracle GoldenGate Service Manager console.
2. In the navigation menu for the Administration Service, click Configuration.
3. Click the Database tab.
4. Click the plus button next to Credentials to add the database credentials.
5. Enter the domain name in the Credential Domain field.
6. Enter the alias in the Credential Alias field.
7. Enter the data source name (DSN) (which you created in Preparing the System for Oracle GoldenGate) in the DSN field.
8. Enter the user ID and password.
9. Click Submit.

10. Click the Connect to database icon for the new credential to ensure that the newly created credential can connect to the target database.

    After you test the database connection, the TRANDATA Information section appears below the table of credentials.

11. Click the plus button next to TRANDATA Information.
12. Select Table and add the table name in the Table Name field.
13. Edit the columns as needed.
14. Select nowait in the Prepare CSN Mode drop-down list.
15. Click Submit.

To create a CDC Capture process, you enable supplemental logging and create an Oracle GoldenGate CDC cleanup job.

See the following instructions:

• Enabling CDC Supplemental Logging
• Purging the CDC Staging Data

Use these steps to create and run the CDC capture for Microsoft SQL Server.

1. Open the Administration Service page in the Oracle GoldenGate Service Manager console.
2. Click the plus button next to Extracts.
3. Select Change Data Capture Extract for the extract type and click Next.
4. Enter the process name in the Process Name field.
5. Select Unidirectional in the Intent field.
6. In the Credential Alias drop-down list, select the credential alias that you created in Configuring the Database for Oracle GoldenGate.
7. In the Begin drop-down list, select Now.
8. Enter a two-character Trail Name.

9. If you need to customize the trail subdirectory, enter the full path of the directory in the Trail Subdirectory field.

   This can be any directory, and it must already exist in the file system.

10. Enter the trail size in MB in the Trail Size field.

    If the record generation rate of GoldenGate is low (less than 50 records per second), then Oracle recommends that you set the trail size to a lower value, such as 100 MB.

    Note:

    You can leave all other fields unchanged because they're optional.
11. Click Next.

12. In the Parameter File section, enter the following parameters:

    EXTRACT <extract_name>
    SOURCEDB <DSN_name> USERIDALIAS <user_alias>, DOMAIN <domain_name>
    OUTPUTFORMAT XML _AUDIT_VAULT
    EXTTRAIL <subdirectory>/<trail_name>
    TABLE <schema>.<trail_name>;

    For example:

    EXTRACT exta
    SOURCEDB odbc1 USERIDALIAS sql, DOMAIN OracleGoldenGate
    OUTPUTFORMAT XML _AUDIT_VAULT
    EXTTRAIL dirdat/ea
    TABLE dbo.employee;

    Note the following parameter guidelines:

    • Include a space between XML and _AUDIT_VAULT in the OUTPUTFORMAT parameter.
    • Include the OUTPUTFORMAT parameter before the EXTTRAIL parameter in the parameter file. Otherwise, the XML files are not generated.
    • Ensure that the TABLE command always ends with a semicolon (;).

    • Ensure that the sequence of all the parameters is in the exact same order as the preceding example.

    • For the TABLE command, specify the tables for which DML changes need to be captured.
    • For more information about Oracle GoldenGate parameters, see Oracle GoldenGate Parameters.

13. Click Create and Run to start the CDC Extract process.

    The newly created CDC Extract appears in the Extracts section on the Administration Service page.

14. To view the status of the CDC Extract:

    1. Click the Actions button for the extract.
    2. Select Details.

    3. Click Report tab to view the diagnostic messages.

       If the extract process fails, this report displays the relevant errors.