Reference for Oracle GoldenGate

Valid For

Manager, Extract, Replicat, DEFGEN.

Supported for

DB2 for i

DB2 LUW

DB2 on z/OS

Oracle

MySQL

SQL Server

Teradata

TimesTen

Description

Use the USERID parameter to specify the database user for an Oracle GoldenGate process used to log in to a database when using database level authentication, and to specify the password and password encryption information. This parameter can be used instead of USERIDALIAS when an Oracle GoldenGate credential store is not being used.

Always use USERID or USERIDALIAS for a primary Extract and for Replicat when using database-level authentication for connectivity. Use USERID or USERIDALIAS for Manager only if using parameters that require Manager to log into the source or target database.

USERID Compared to USERIDALIAS

USERID requires either specifying the clear-text password in the parameter file or encrypting it with the ENCRYPT PASSWORD command and, optionally, storing an encryption key in an ENCKEYS file. USERID supports a broad range of the databases that Oracle GoldenGate supports.

USERIDALIAS enables you to specify an alias, rather than a user ID and password, in the parameter file. The user IDs and encrypted passwords are stored in a credential store. USERIDALIAS supports databases running on Linux, UNIX, and Windows platforms.

General Requirements for USERID

Specify USERID before any TABLE or MAP entries in an Extract or Replicat parameter file. Specify USERID in a Manager parameter file if Manager must access the database and a login is required.

USERID is not always required, nor is PASSWORD always required when USERID is required. In some cases, it is sufficient just to use USERID or even just to use the SOURCEDB or TARGETDB parameter, depending on how authentication for the database is configured.

See "SOURCEDB" and "TARGETDB" for more information.

Note:

The privileges that are required for the USERID user vary by database. See the appropriate Oracle GoldenGate installation guide for your database to determine the privileges that are required for the Oracle GoldenGate database users.

USERID Requirements Per Database Type

The usage of USERID varies depending on the database type.

DB2 for i

Use USERID with PASSWORD to specify the name and password of the user profile assigned to the Oracle GoldenGate process. Use SOURCEDB or TARGETDB with USERID to specify the default DB2 for i database that is identified by the system name (in upper case). See Using Oracle GoldenGate for Heterogeneous Databases for more information.

DB2 for LUW

Use USERID with PASSWORD and preceded by SOURCEDB or TARGETDB for all Oracle GoldenGate processes that connect to a DB2 LUW database using database authentication. You can omit USERID and PASSWORD (and only use SOURCEDB or TARGETDB) if the database is configured allow authentication at the operating-system level.

DB2 for z/OS database

Use USERID with PASSWORD if the user that is assigned to the Oracle GoldenGate process does not have the DB2 privileges that are required for the process to function properly.

MySQL

Use USERID with PASSWORD for all Oracle GoldenGate processes that connect to a MySQL database.

Oracle

Use USERID for Oracle GoldenGate processes that connect to an Oracle database as follows:

To use an operating system login, use USERID with the / argument.
To use a database user name and password, use USERID with PASSWORD.
To use USERID for an Extract group that is configured for integrated capture, the user must have the privileges granted in the dbms_goldengate_auth.grant_admin_privilege procedure, and the user must be the same one that issues DBLOGIN and REGISTER EXTRACT or UNREGISTER EXTRACT for the Extract group that is associated with this USERID.
To support capture from an Oracle container database, the user that is specified with USERID must log into the root container and must be a common user. A connect string must be supplied for this user and must include the required C## prefix of the common user, such as C##GGADMIN@FINANCE. See, Other Requirements for Multitenant Container Databases in Using Oracle GoldenGate for Oracle Database.
If you are connecting a Replicat into a multitenant database that each Replicat connects into a single PDB and if your are applying data into multiple PDBs in the same multitenant database, then Oracle recommends that you use multiple Replicats.
If using a remote Extract or remote Replicat, the USERID should contain the username@connect_string. See Example 3, "Example 3" for syntax.

Use NOUSERID to allow Integrated Extract to run without a connection for fetching or metadata lookups, or any data dictionary calls. Essentially eliminating the need to connect to the source database at all. The NOUSERID option requires an Integrated Dictionary. We should also include that when NOUSERID is used, if the customer has an Active Data Guard Standby, they can set up fetching from that Standby database using the FETCHUSERID parameter. The two can be used in conjunction with NOUSERID. In the event where you are using downstream integrated extract (same caveats below) you can use FETCHUSERID to fetch from the ADG Standby database and NOUSERID to prevent the Extract from making a connection to the source database. This way, if Extract does need to fetch, it can do so.

SQL Server

Use USERID with PASSWORD if the ODBC data source connection that will be used by the Oracle GoldenGate process, is configured to connect using SQL Server authentication.

On a source SQL Server system, also use the SOURCEDB parameter to specify the source ODBC data source.
On a target SQL Server system, also use the TARGETDB parameter to specify the target ODBC data source.

Teradata

Use USERID with PASSWORD for Oracle GoldenGate processes that connect to a Teradata database.

On a target Teradata system, use the TARGETDB parameter to specify the target ODBC data source.

Default

None

Syntax

USERID | NOUSERID {/ | user}[, PASSWORD password]
[algorithm ENCRYPTKEY {key_name | DEFAULT}] [SYSDBA]
[, THREADS (threadID[, threadID][, ...][, thread_range[, thread_range][, ...])]

NOUSERID: NOUSERID is only valid for Oracle TRANLOG Extract (not pump Extract) when downstream integrated Extract is configured.

/

Directs Oracle GoldenGate to use an operating-system login for Oracle, not a database user login. Use this argument only if the database allows authentication at the operating-system level. Bypassing database-level authentication eliminates the need to update Oracle GoldenGate parameter files if application passwords frequently change. To use this option, the correct user name must exist in the database, in relation to the value of the Oracle OS_AUTHENT_PREFIX initialization parameter, as follows:

The value set with OS_AUTHENT_PREFIX is concatenated to the beginning of a user's operating system account name and then compared to the database name. Those two names must match.
If OS_AUTHENT_PREFIX is set to ' ' (a null string), the user name must be created with IDENTIFIED EXTERNALLY. For example, if the OS user name is ogg, you would use the following to create the database user:
```
CREATE USER ogg IDENTIFIED EXTERNALLY;
```
If OS_AUTHENT_PREFIX is set to OPS$ or another string, the user name must be created in the following format:
```
OS_AUTHENT_PREFIX_value OS_user_name
```
For example, if the OS user name is ogg, you would use the following to create the database user:
```
CREATE USER ops$ogg IDENTIFIED BY oggpassword;
```

user

Specifies the name of a database user or a schema, depending on the database configuration. For Oracle, a SQL*Net connect string can be used. Refer to USERID Requirements Per Database Type for additional guidelines.

password

Use when database authentication is required to specify the password for the database user. If the password was encrypted by means of the ENCRYPT PASSWORD command, supply the encrypted password; otherwise, use the clear-text password. If the password is case-sensitive, type it that way.

If either the user ID or password changes, the change must be made in the Oracle GoldenGate parameter files, including the re-encryption of the password if necessary.

algorithm

Specifies the encryption algorithm that was used to encrypt the password with ENCRYPT PASSWORD.

The algorithm can be one of:

AES128

AES192

AES256

BLOWFISH

ENCRYPTKEY {key_name | DEFAULT}

Specifies the encryption key that was specified with ENCRYPT PASSWORD.

ENCRYPTKEY key_name specifies the logical name of a user-created encryption key in the ENCKEYS lookup file. Use if ENCRYPT PASSWORD was used with the KEYNAME key_name option.
ENCRYPTKEY DEFAULT directs Oracle GoldenGate to use a random key. Use if ENCRYPT PASSWORD was used with the KEYNAME DEFAULT option.

SYSDBA

(Oracle) Specifies that the user logs in as sysdba.

THREADS (threadID[, threadID][, ...][, thread_range[, thread_range][, ...])

Valid for Replicat. Links the specified credential to one or more threads of a coordinated Replicat. Enables you to specify different logins for different threads.

threadID[, threadID][, ...]: Specifies a thread ID or a comma-delimited list of threads in the format of threadID, threadID, threadID.
[, thread_range[, thread_range][, ...]: Specifies a range of threads in the form of threadIDlow-threadIDhigh or a comma-delimited list of ranges in the format of threadIDlow-threadIDhigh, threadIDlow-threadIDhigh.

A combination of these formats is permitted, such as threadID, threadID, threadIDlow-threadIDhigh.

TimesTen

Use USERID with PASSWORD for Oracle GoldenGate processes that connect to a TimesTen database.

On a target TimesTen system, use the TARGETDB parameter to specify the target ODBC data source.

Default

None

Syntax

USERID {/ | user}[, PASSWORD password]
[algorithm ENCRYPTKEY {key_name | DEFAULT}] [SYSDBA]
[, THREADS (threadID[, threadID][, ...][, thread_range[, thread_range][, ...])]

/

Directs Oracle GoldenGate to use an operating-system login for Oracle, not a database user login. Use this argument only if the database allows authentication at the operating-system level. Bypassing database-level authentication eliminates the need to update Oracle GoldenGate parameter files if application passwords frequently change. To use this option, the correct user name must exist in the database, in relation to the value of the Oracle OS_AUTHENT_PREFIX initialization parameter, as follows:

The value set with OS_AUTHENT_PREFIX is concatenated to the beginning of a user's operating system account name and then compared to the database name. Those two names must match.
If OS_AUTHENT_PREFIX is set to ' ' (a null string), the user name must be created with IDENTIFIED EXTERNALLY. For example, if the OS user name is ogg, you would use the following to create the database user:
```
CREATE USER ogg IDENTIFIED EXTERNALLY;
```
If OS_AUTHENT_PREFIX is set to OPS$ or another string, the user name must be created in the following format:
```
OS_AUTHENT_PREFIX_value OS_user_name
```
For example, if the OS user name is ogg, you would use the following to create the database user:
```
CREATE USER ops$ogg IDENTIFIED BY oggpassword;
```

user

Specifies the name of a database user or a schema, depending on the database configuration. For Oracle, a SQL*Net connect string can be used. Refer to USERID Requirements Per Database Type for additional guidelines.

password

Use when database authentication is required to specify the password for the database user. If the password was encrypted by means of the ENCRYPT PASSWORD command, supply the encrypted password; otherwise, use the clear-text password. If the password is case-sensitive, type it that way.

If either the user ID or password changes, the change must be made in the Oracle GoldenGate parameter files, including the re-encryption of the password if necessary.

algorithm

Specifies the encryption algorithm that was used to encrypt the password with ENCRYPT PASSWORD.

The algorithm can be one of:

AES128

AES192

AES256

BLOWFISH

ENCRYPTKEY {key_name| DEFAULT}

Specifies the encryption key that was specified with ENCRYPT PASSWORD.

ENCRYPTKEYkey_name specifies the logical name of a user-created encryption key in the ENCKEYS lookup file. Use if ENCRYPT PASSWORD was used with the KEYNAME key_name option.
ENCRYPTKEY DEFAULT directs Oracle GoldenGate to use a random key. Use if ENCRYPT PASSWORD was used with the KEYNAME DEFAULT option.

SYSDBA

(Oracle) Specifies that the user logs in as sysdba.

THREADS (threadID[,threadID][, ...][,thread_range[,thread_range][, ...])

Valid for Replicat. Links the specified credential to one or more threads of a coordinated Replicat. Enables you to specify different logins for different threads.

threadID[,threadID][, ...]: Specifies a thread ID or a comma-delimited list of threads in the format of threadID, threadID, threadID.
[,thread_range[,thread_range][, ...]: Specifies a range of threads in the form of threadIDlow-threadIDhigh or a comma-delimited list of ranges in the format of threadIDlow-threadIDhigh, threadIDlow-threadIDhigh.

A combination of these formats is permitted, such as threadID, threadID, threadIDlow-threadIDhigh.

Examples

Example 1

USERID /

Example 2

USERID ogg

Example 3

USERID ogg@ora1.ora, &
PASSWORD AACAAAAAAAAAAAJAUEUGODSCVGJEEIUGKJDJTFNDKEJFFFTC AES128, &
ENCRYPTKEY securekey1

Example 4

USERID ogg, PASSWORD AACAAAAAAAAAAAJAUEUGODSCVGJEEIUGKJDJTFNDKEJFFFTC &
AES128, ENCRYPTKEY securekey1

Example 5

USERID ogg, PASSWORD AACAAAAAAAAAAAJAUEUGODSCVGJEEIUGKJDJTFNDKEJFFFTC &
BLOWFISH, ENCRYPTKEY DEFAULT

Example 6

USERID ogg, &
PASSWORD AACAAAAAAAAAAAJAUEUGODSCVGJEEIUGKJDJTFNDKEJFFFTC AES128, &
ENCRYPTKEY securekey1 SYSDBA