After installing the gateway, perform the following tasks to configure the gateway for Teradata:
Perform the following tasks to configure the Oracle Transparent Gateway for Teradata Administrator's Guide.
The gateway system identifier (SID) is an alphanumeric character string that identifies a gateway instance. You need one gateway instance, and therefore one gateway SID, for each Teradata database you are accessing. The SID is used as part of the file name for the initialization parameter file. The default SID is tg4tera
.
You can define a gateway SID, but using the default of tg4tera
is easier because you do not need to change the initialization parameter file name. However, if you want to access two Teradata databases, you need two gateway SIDs, one for each instance of the gateway. If you have one Teradata database and want to access it sometimes with one set of gateway parameter settings, and other times with different gateway parameter settings, you can do that by having multiple gateway SIDs for the single Teradata database.
The initialization parameter file must be available when the gateway is started. During installation, the following default initialization parameter file is created:
$ORACLE_HOME/tg4tera/admin/inittg4tera.ora
Where $ORACLE_HOME is the directory under which the gateway is installed.
If you are not using tg4tera
as the gateway SID, you must rename the initialization parameter file using the SID you chose in Task 1. This default initialization parameter file is sufficient for starting the gateway, verifying a successful installation, and running the demonstration scripts.
In the initialization parameter file, specify the Teradata connection as follows:
HS_FDS_CONNECT_INFO=dsn HS_FDS_SHAREABLE_NAME=odbc_installation_path/lib/libodbc.so set ODBCINI = full_path_to_the_odbcini_file
Where:
dsn is the Data Source Name defined in the odbc.ini file.
full_path_to_the_odbcini_file is the full path location of the odbc initialization file.
odbc_installation_path is the path where the NCR Teradata ODBC Driver is installed.
Note that the set statement is optional as long as it is specified in the working account.
Note: In the ODBC DSN set up, make sure that the DateTimeFormat parameter is set to AAA |
A number of initialization parameters can be used to modify gateway behavior. You might want to change the initialization parameter file later to meet system requirements.
See Also: Appendix D, "Heterogeneous Services Initialization Parameters" and the Oracle Database Heterogeneous Connectivity Administrator's Guide for more information about customizing the initialization parameter file. |
The gateway requires Oracle Net Services to provide transparent data access. After configuring the gateway, configure Oracle Net Services to work with the gateway.
Oracle Net Services uses the TNS listener to receive incoming connections from a Oracle Net Services client. The TNS listener and the gateway must reside on the same machine.
The TNS listener listens for incoming requests from the Oracle database server. For the TNS listener to listen for the gateway, information about the gateway must be added to the TNS listener configuration file, listener.ora
. This file is located in $ORACLE_HOME/network/admin
, where $ORACLE_HOME is the directory under which the gateway is installed.
Note: If Oracle Net Services is reinstalled, the originallistener.ora file is renamed and a new listener.ora file is put into the $ORACLE_HOME/network/admin directory. |
The following entries must be added to the listener.ora
file:
A list of Oracle Net Services addresses on which the TNS listener listens.
The gateway that the TNS listener starts in response to incoming connection requests.
The Oracle database server accesses the gateway using Oracle Net Services and the TCP/IP protocol adapter. The following is the syntax of the connect descriptor entry in the listener.ora
file:
LISTENER= (ADDRESS= (PROTOCOL=TCP) (HOST=host_name) (PORT=port_number))
Where host_name is the name of the machine on which the gateway is installed. port_number specifies the port number used by the TNS listener. If you have other listeners running on host_name, the value of port_number must be different from the other listeners' port numbers.
To direct the TNS listener to start the gateway in response to incoming connection requests, add an entry to the listener.ora
file with the following syntax:
SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (SID_NAME=gateway_sid) (ORACLE_HOME=oracle_home_directory) (PROGRAM=tg4tera) (ENVS=SHLIB_PATH=odbc_installation_path/lib: oracle_home_directory/lib32) ) )
Table 2-1 defines the parameters in the entry.
Table 2-1 SID_LIST_LISTENER Parameters
Variable | Description |
---|---|
gateway_sid | Specifies the SID of the gateway. Matches the gateway SID specified in the connect descriptor entry in the tnsnames.ora file. |
oracle_home_directory | Specifies the Oracle home directory where the gateway resides. |
tg4tera |
Specifies the Oracle Transparent Gateway for Teradata Administrator's Guide. |
odbc_installation_path | Specifies the Oracle Transparent Gateway for Teradata Administrator's Guide. |
If you are already running a TNS listener that listens on multiple database SIDs, add only the following syntax to SID_LIST in the existing listener.ora
file:
SID_LIST_LISTENER= (SID_LIST= (SID_DESC=. . ) (SID_DESC=. . ) (SID_DESC= (SID_NAME=gateway_sid) (ORACLE_HOME=oracle_home_directory) (PROGRAM=tg4tera) (ENVS=SHLIB_PATH=odbc_installation_path/lib: oracle_home_directory/lib32) ) )
See Also: Oracle Net Services Administrator's Guide for information about changing thelistener.ora file. |
The TNS listener must be started to initiate the new settings, as follows:
Set the PATH environment variable to access the commands in the directory $ORACLE_HOME/bin
where the gateway is installed. If you have the Bourne or Korn Shell, enter the following:
$ PATH=$ORACLE_HOME/bin:$PATH;export PATH $ SHLIB_PATH=$ORACLE_HOME:lib:$SHLIB_PATH; export SHLIB_PATH
If you have the C Shell, enter the following:
$ setenv PATH $ORACLE_HOME/bin:$PATH $ setenv SHLIB_PATH $ORACLE_HOME/lib:$SHLIB_PATH
If the listener is already running, use the lsnrctl command to stop the listener and then start it with the new settings, as follows:
$ lsnrctl stop$ lsnrctl start
Check the status of the listener with the new settings, as follows:
$ lsnrctl status
The following is an example of output from a lsnrctl status check:
LSNRCTL for HPUX: Version 10.2.0.1.0 - Production on 01-JUN-2005 09:28:13Copyright (c) 1991, 2004, Oracle. All rights reserved.Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=204.179.99.15)(PORT=1551))STATUS of the LISTENER----------------------Alias listenerVersion TNSLSNR for HPUX: Version 10.2.0.1.0 - ProductionStart Date 28-APRIL-2005 15:38:59Uptime 33 days 17 hr. 49 min. 22 secTrace Level offSecurity OFFSNMP OFFListener Parameter File /users/oracle/gateway/network/admin/listener.oraListener Log File /users/oracle/gateway/network/log/listener.logListening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=204.179.99.15)(PORT=1551)))Services Summary...Service "tg4tera" has 1 instance(s). Instance "tg4tera", status UNKNOWN, has 1 handler(s) for this service...The command completed successfully
In this example, tg4tera
is the default SID value assigned during installation. You can use any valid ID for the SID, or keep the default.
Note: You must use the same SID value in thetnsnames.ora file, the listener.ora file. |
Before you use the gateway to access Teradata data you must configure the Oracle database server to enable communication with the gateway over Oracle Net Services.
To configure the server you add connect descriptors to the tnsnames.ora
file. You cannot use the Oracle Net Services Assistant or the Oracle Net Services Easy Config tools to configure the tnsnames.ora
file. You must edit the file manually.
See Also: Oracle Database Administrator's Guide for information about editing thetnsnames.ora file. |
For the Oracle database server to access the gateway, it needs a service name entry or a connect descriptor name entry in the tnsnames.ora
file to tell the Oracle database server where to make connections. By default, this file is in $ORACLE_HOME/network/admin
, where $ORACLE_HOME is the directory in which the Oracle database server is installed. The tnsnames.ora
file is required by the Oracle database server accessing the gateway, but not by the gateway.
Edit the tnsnames.ora
file to add a connect descriptor for the gateway. The following is an example of the Oracle Net Services entries using TCP/IP protocol needed for the Oracle database server to access the gateway:
connect_descriptor= (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=host_name) (PORT=port_number) ) (CONNECT_DATA= (SID=gateway_sid)) (HS=OK))
Table 2-2 defines the parameters used in the connect descriptor.
Table 2-2 Parameters for Gateway Connect Descriptor
Variable | Description |
---|---|
connect_descriptor | The description of the object to connect to as specified when creating the database link, such as tg4tera .
Check the names.directory_path = (TNSNAMES) Note: The |
TCP |
The TCP protocol used for TCP/IP connections. |
host_name | Specifies the machine where the gateway is running. |
port_number | Matches the port number used by the Oracle Net Services TNS listener that is listening for the gateway. The TNS listener's port number can be found in the listener.ora file used by the TNS listener. See "Example of Address to Listen On in listener.ora File". |
gateway_sid | Specifies the SID of the gateway and matches the SID specified in the listener.ora file of the TNS listener that is listening for the gateway. See "Task 1: Configure Oracle Net Services TNS Listener for the Gateway" for more information. |
(HS=OK) |
Specifies that this connect descriptor uses the Oracle Heterogeneous Services option. |
Any Oracle client connected to the Oracle database server can access Teradata data through the gateway. The Oracle client and the Oracle database server can reside on different machines. The gateway accepts connections only from the Oracle database server.
A connection to the gateway is established through a database link when it is first used in an Oracle session. In this context, a connection refers to the connection between the Oracle database server and the gateway. The connection remains established until the Oracle session ends. Another session or user can access the same database link and get a distinct connection to the gateway and Teradata database.
Database links are active for the duration of a gateway session. If you want to close a database link during a session, you can do so with the ALTER SESSION statement. The database and application administrators of a distributed database system are responsible for managing the necessary database links that define paths to the Teradata database.
See Also: Oracle Database Administrator's Guide and Oracle Database Heterogeneous Services Administrator's Guide for more information about using database links. |
The gateway uses userids and passwords to access the information in the remote database. Some userids and passwords must be defined in the Gateway Initialization File to handle functions such as resource recovery. In the current security conscious environment, having plain-text passwords that are accessible in the Initialization File is deemed insecure. The tg4pwd encryption utility has been added as part of Heterogeneous Services' generic connectivity to help make this more secure. This utility is accessible by this gateway. The initialization parameters which contain sensitive values can be stored in an encrypted form.
See Also: Oracle Database Heterogeneous Connectivity Administrator's Guide for more information about using this utility. |
The tasks for configuring the gateway to access multiple Teradata databases are similar to the tasks for configuring the gateway for a single database. The configuration example assumes the following:
The gateway is installed and configured with the default SID of tg4tera
The ORACLE_HOME environment variable is set to the directory where the gateway is installed.
The gateway is configured for one Teradata database named db1.
Two Teradata databases named db2 and db3 on a server are being added.
Configuring the gateway for additional Teradata databases is similar to configuring it for one database, and involves the following:
Configuring the gateway.
Configuring Oracle Net Services for the gateway and the Oracle database server.
Choose Two System IDs for Each Teradata Database
A separate instance of the gateway accesses the different Teradata databases. Each instance needs its own gateway System ID (SID). For this example, the gateway SIDs are chosen for the instances that access the Teradata databases:
tg4tera2
for the gateway accessing database db2.
tg4tera3
for the gateway accessing database db3.
Create Two Initialization Parameter Files
Create an initialization parameter file for each instance of the gateway by copying the original initialization parameter file: $ORACLE_HOME/tg4tera/admin/inittg4tera.ora
, twice, naming one with the gateway SID for db2 and the other with the gateway SID for db3:
$ cd $ORACLE_HOME/tg4tera/admin$ cp inittg4tera.ora inittg4tera2.ora$ cp inittg4tera.ora inittg4tera3.ora
Change the value of the HS_FDS_CONNECT_INFO parameter in the new files.
For init
tg4tera
2.ora
, enter the following:
HS_FDS_CONNECT_INFO=dsn2
For init
tg4tera
3.ora
, enter the following:
HS_FDS_CONNECT_INFO=dsn3
Note: If you have multiple gateway SIDs for the same Teradata database because you want to use different gateway parameter settings at different times, follow the same procedure. You create several initialization parameter files, each with different SIDs and different parameter settings. |
Add two new entries to the TNS listener configuration file, listener.ora
. You must have an entry for each gateway instance, even when multiple gateway instances access the same database.
The following example shows the entry for the original installed gateway first, followed by the new entries:
SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (SID_NAME=tg4tera) (ORACLE_HOME=oracle_home_directory) (PROGRAM=tg4tera) (ENVS=SHLIB_PATH=odbc_installation_path/lib: oracle_home_directory/lib32) ) (SID_DESC= (SID_NAME=tg4tera2) (ORACLE_HOME=oracle_home_directory) (PROGRAM=tg4tera) (ENVS=SHLIB_PATH=odbc_installation_path/lib: oracle_home_directory/lib32) ) (SID_DESC= (SID_NAME=tg4tera3) (ORACLE_HOME=oracle_home_directory) (PROGRAM=tg4tera) (ENVS=SHLIB_PATH=odbc_installation_path/lib: oracle_home_directory/lib32) ) )
If the listener is already running, use the lsnrctl command to stop the listener and then start it with the new settings, as follows:
$ lsnrctl stop$ lsnrctl start
Add two connect descriptor entries to the tnsnames.ora
file. You must have an entry for each gateway instance, even if the gateway instances access the same database.
The following Teradata example shows the entry for the original installed gateway first, followed by the two entries for the new gateway instances:
old_db_using=(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (PORT=1541) (HOST=gtwhost)) (CONNECT_DATA= (SID=tg4tera)) (HS=OK)) new_db2_using=(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (PORT=1541) (HOST=gtwhost)) (CONNECT_DATA= (SID=tg4tera2)) (HS=OK)) new_db3_using=(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (PORT=1541) (HOST=gtwhost)) (CONNECT_DATA= (SID=tg4tera3)) (HS=OK))
The value for PORT
is the TCP/IP port number of the TNS listener that is listening for the gateway. The number can be found in the listener.ora
file used by the TNS listener. The value for HOST
is the name of the machine on which the gateway is running. The name also can be found in the listener.ora
file used by the TNS listener.
Enter the following to create a database link for the tg4tera2
gateway:
SQL> CREATE PUBLIC DATABASE LINK TERA2 CONNECT TO 2 user2 IDENTIFIED BY password2 USING 'new_db2_using';
Enter the following to create a database link for the tg4tera3
gateway:
SQL> CREATE PUBLIC DATABASE LINK TERA3 CONNECT TO 2 user3 IDENTIFIED BY password3 USING 'new_db3_using';
Note: To encrypt the initialization parameters that would normally be stored in the initialization file in plain text, you must use the tg4pwd utility, as described in Oracle Database Heterogeneous Connectivity Administrator's Guide. |
After the database links are established you can query the new Teradata databases, as in the following:
SQL> SELECT * FROM ALL_USERS@TERA2;
Or
SQL> SELECT * FROM ALL_USERS@TERA3;
You can perform the following configuration tasks:
The gateway supports the following transaction capabilities:
COMMIT_CONFIRM
READ_ONLY
SINGLE_SITE
By default, the gateway runs in COMMIT_CONFIRM transaction mode. When the Teradata database is updated by a transaction, the gateway becomes the commit point site. The Oracle database server commits the unit of work in the Teradata database after verifying that all Oracle databases in the transaction have successfully prepared the transaction. Only one gateway can participate in an Oracle two-phase commit transaction as the commit point site.
See Also: Oracle Database Heterogeneous Connectivity Administrator's Guide for information about the two-phase commit process. |
To enable the COMMIT_CONFIRM transaction mode, create a recovery account and password and create a log table. The log table, called HS_TRANSACTION_LOG, is where two-phase commit transactions are recorded.
For the gateway to recover distributed transactions, a recovery account and password must be set up in the Teradata database. By default, both the user name of the account and the password are RECOVER
. The name of the account can be changed with the gateway initialization parameter HS_FDS_RECOVERY_ACCOUNT. The account password can be changed with the gateway initialization parameter HS_FDS_RECOVERY_PWD.
Note: Oracle Corporation recommends that you do not use the default value RECOVER for the user name and password. Moreover, storing plain-text as user name and password in the initialization file is not a good security policy. There is now a utility called tg4pwd, that should be used for encryption. Refer to Chapter 4, 'Encrypting Initialization parameters' in the Heterogeneous Connectivity Administration Guide for further details. |
Set up a user account in the Teradata database. Both the user name and password must be a valid Teradata user name and password.
In the initialization parameter file, set the following gateway initialization parameters:
HS_FDS_RECOVERY_ACCOUNT to the user name of the Teradata user account you set up for recovery.
HS_FDS_RECOVERY_PWD to the password of the Teradata user account you set up for recovery.
See Also: "Task 2: Customize the Initialization Parameter File" for information about editing the initialization parameter file. For information about HS_FDS_RECOVERY_ACCOUNT and HS_FDS_RECOVERY_PWD, see Appendix D, "Heterogeneous Services Initialization Parameters". |
When configuring the gateway for two-phase commit, a table must be created in the Teradata database for logging transactions. The gateway uses the transaction log table to check the status of failed transactions that were started at the Teradata database by the gateway and registered in the table.
Note: Updates to the transaction log table cannot be part of an Oracle distributed transaction. |
Note: The information in the transaction log table is required by the recovery process and must not be altered. The table must be used, accessed, or updated only by the gateway. |
The table, called HS_TRANSACTION_LOG, consists of two columns, GLOBAL_TRAN_ID, data type CHAR(64) and TRAN_COMMENT, data type CHAR(255).
You can use another name for the log table, other than HS_TRANSACTION_LOG, by specifying the other name using the HS_FDS_TRANSACTION_LOG initialization parameter.
See Also: Appendix D, "Heterogeneous Services Initialization Parameters" for information about the HS_FDS_TRANSACTION_LOG initialization parameter. |
Create the transaction log table in the user account you created in "Task 1: Create a Recovery Account and Password". Because the transaction log table is used to record the status of a gateway transaction, the table must reside at the database where the Teradata update takes place. Also, the transaction log table must be created under the owner of the recovery account.
Note: To utilize the transaction log table, users of the gateway must be granted privileges on the table. |
To create a transaction log table use the tg4tera_tx.sql
script, located in the directory $ORACLE_HOME/tg4tera/admin
, where $ORACLE_HOME is the directory under which the gateway is installed.