How to Setup DG4Tera (Oracle Database Gateway for Teradata) on 32bit Windows operating systems
1) The listener needs a new SID entry like:
2) The tnsnames.ora (of the Oracle database that wants to use the gateway) needs an entry for the DG4TERA alias:
3) init.ora of the gateway:
4) Configuring the Oracle database
APPLIES TO:
Oracle Database Gateway for Teradata - Version 11.1.0.6.0 to 11.2.0.4
Microsoft Windows (32-bit)
Microsoft Windows 2000Microsoft Windows XPMicrosoft Windows Server 2003
GOAL
This note describes the setup of DG4TERA for Windows 32bit operating systems. Since 11.2 there's also a DG4TERA version available for 64bit Windows. There's a separate note describing the setup on 64bit Windows.
Prerequsites for DG4TERA are:
- Teradata CLIENT
- Teradata GSS
- Teradata TDICU
You can check the prerequisites by opening the Add/Remove programs tab of the control panel. All components should be listed there like:
Shared ICU libraries for Teradata 12.0
Teradata Cliv2 12.0
Teradata GSS client nt-i386 12.0
The Teradata ODBC driver is NOT longer needed as DG4TERA is now coming with all required components.
This gateway is certified also with older Oracle releases 9.2.0.8, 10.1.0.5, or 10.2.0.3/4. But please be aware Oracle databases releases up to 10.2.0.3 require a patch to work properly with V11 Gateways.
The patch can be found on My Oracle Support by performing a simple search using the 'Patches & Updates' tab at the top of the page. Choose 'Simple Search' and enter 5965763 in the block for the patch number and select the appropriate platform from the Platform or Language list.
If there is not a patch available for your database version (i.e. 9.2.0.8, 10.1.0.5, or 10.2.0.3) for the platform you selected, please log a Service Request with Oracle Support requesting a backport for 'unpublished' Bug 5965763 to your platform.
This fix is automatically included in the 10.2.0.4 patchset.
FIX
How to Setup DG4Tera (Oracle Database Gateway for Teradata) on 32bit Windows operating systems
The Oracle Database Gateway for Teradata comes on a separate CD. It can be installed either into an existing 11g database Oracle_Home (please pay attention that if the Oracle_Home contains an already patched release of the database; then you MUST apply this patchset again. The reason for this is that the gateway installation might overwrite already patched libraries with the base version as delivered on the CD. To get a proper environment again an already applied patchset needs to be reapplied) or into its own stand alone ORACLE_HOME.
After the installation the following items must be configured:
1) listener (in the ORACLE_HOME where the gateway software is installed)
2) tnsnames (in the ORACLE_HOME where the Oracle database software is installed)
3) init<SID>.ora of the hs subsystem (in the ORACLE_HOME where the gateway software is installed)
4) Oracle database
1) The listener needs a new SID entry like:
The teradata shared libraries and client libs installation directory depends on the version of the Teradata client. Older versions installed the libraries into the windows\system32 directory; newer installations prompt for a program location.
Thus it is recommend to change the listener to make sure the correct libraries are found and add the PATH environment setting to the listener like:
(SID_DESC =
(PROGRAM = DG4TERA)
(SID_NAME = DG4TERA)
(ORACLE_HOME = D:\oracle\product\11.1.0)
(ENVS=PATH="C:\Program Files\NCR\Teradata Client\cliv2";"C:\Program Files\NCR\Common Files\Shared ICU Libraries for Teradata\lib")
)
In the example above the Teradata libraries are located in C:\Program Files\NCR\Common Files\Shared ICU Libraries for Teradata\lib and C:\Program Files\NCR\Teradata Client\cliv2. If you are not sure you can search for the libraries icudttd34l.dll and wincli32.dll (file names might differ depending on the TeraDta version being used).
Please correct the ORACLE_HOME entry and the ENVS entry according to your installation.
After adding ENVS parameter to the listener, please STOP and START the listener.
So a listener.ora file with a listener listening on port 1511 might look like:
SID_LIST_LISTENER =
(SID_DESC =
(PROGRAM = DG4TERA)
(SID_NAME = DG4TERA)
(ORACLE_HOME = D:\oracle\product\11.1.0)
(ENVS=PATH="C:\Program Files\NCR\Teradata Client\cliv2\lib";"C:\Program Files\NCR\Common
Files\Shared ICU Libraries for Teradata\lib")
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <hostname of the Oracle Gateway Server>) (PORT = 1511))
)
)
)
The listener must be stopped and started after changing the listener.ora file!
2) The tnsnames.ora (of the Oracle database that wants to use the gateway) needs an entry for the DG4TERA alias:
dg4tera.de.oracle.com =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=<hostname of the Oracle Gateway Gateway Server>)(PORT=1511))
(CONNECT_DATA=(SID=dg4tera))
(HS=OK)
)
The domain of the tns alias can differ from the one used above (de.oracle.com), depending on the parameter in the sqlnet.ora:
NAMES.DEFAULT_DOMAIN = de.oracle.com
But the important entry is the (HS=OK) key word. (HS=) is also a valid entry, but DBCA and NetCA will only recognize (HS=OK) entries and remove any (HS=) entries.
After adding the tnsnames alias and restarting the listener, a connectivity check is to use tnsping <alias>.
tnsping dg4tera
should come back with a successfull message.
3) init.ora of the gateway:
There are some restrictions how to name the SID (described in the Net Administrators Guide in detail). At this place only a short note: don't use dots in the SID and keep it short!
The SID is also relevant for the initialisation file of the gateway. The name of the file is init<SID>.ora. In this example it is called initdg4tera.ora. The file is located at $ORACLE_HOME/dg4tera/admin. It should contain at least the connect details:
The initdg4tera.ora file should contain at least:
HS_FDS_CONNECT_INFO=192.168.2.1:1025/gateway
#HS_FDS_TRACE_LEVEL=debug
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
HS_FDS_CONNECT_STRING ="EnableLOBs=No"
Please use the IP Address of the Teradata host and make sure the Teradata database is listening on port 1025. If you don't want to specify the IP address you have to follow the Teradata client config instructions by using an alias (with 8 or less characters) and add it to the hosts file located in \windows\system32\drivers\etc\ including the cop1 suffix as required by Teradata.
For example using an alias called TDzku118 requires to add into the hosts file:
192.168.2.1 Teradata.de.oracle.com TD118cop1
For Teradata databases listening on a different port then 1025 it is not enough to just change the port number in the HS_FDS_CONNECT_INFO section. It would require an additional set parameter:
SET TDMSTPORT= <port number of the Teradata database>
SIDE NOTE:
If you use the hostname without following the Teradata client configuration instructions the misleading error message might be raised::
[Oracle][ODBC Teradata Client driver][Teradata]CLI2: BADLOGON(303): Invalid
logon string.
[Oracle][ODBC Teradata Client driver]Invalid attribute in connection string:
ArraySize.[Oracle][ODBC Teradata Client driver]Invalid attribute in connection
string: SupportNumericPrecisionGreaterThan38.
ORA-02063: vorherige 3 lines von DG4TERA
So for a quick check, please use the IP address of the Teradata host instead of the name.
"gateway" is the name of the database you want to connect to.
4) Configuring the Oracle database
Here a database link needs to be defined:
First connect with an Oracle user having sufficient rights to create a database link (i.e. system) to the Oracle db.
The syntax to create a db link is:
create [public] database link <name> connect to <UID> identified by <pwd> using '<tnsalias>';
In other words, to connect to the Teradata database configured in the last steps, the syntax must be:
CREATE DATABASE LINK teradata CONNECT TO "terant" IDENTIFIED BY "terant" USING 'dg4tera';
The db link name is teradata. Username and password must be in double quotes, because usernames and passwords are case sensitive for the Teradata database. 'dg4tera' points to the alias in the tnsnames.ora file that calls the gateway.
If everything is configured well, a select of a Teradata table should be successful:
select * from "table"@teradata;
Please take care and surround table and column names with double quotes as Teradata table and column names are case sensitive.
Related
1) The listener needs a new SID entry like:
2) The tnsnames.ora (of the Oracle database that wants to use the gateway) needs an entry for the DG4TERA alias:
3) init.ora of the gateway:
4) Configuring the Oracle database
APPLIES TO:
Oracle Database Gateway for Teradata - Version 11.1.0.6.0 to 11.2.0.4
Microsoft Windows (32-bit)
Microsoft Windows 2000Microsoft Windows XPMicrosoft Windows Server 2003
GOAL
This note describes the setup of DG4TERA for Windows 32bit operating systems. Since 11.2 there's also a DG4TERA version available for 64bit Windows. There's a separate note describing the setup on 64bit Windows.
Prerequsites for DG4TERA are:
- Teradata CLIENT
- Teradata GSS
- Teradata TDICU
You can check the prerequisites by opening the Add/Remove programs tab of the control panel. All components should be listed there like:
Shared ICU libraries for Teradata 12.0
Teradata Cliv2 12.0
Teradata GSS client nt-i386 12.0
The Teradata ODBC driver is NOT longer needed as DG4TERA is now coming with all required components.
This gateway is certified also with older Oracle releases 9.2.0.8, 10.1.0.5, or 10.2.0.3/4. But please be aware Oracle databases releases up to 10.2.0.3 require a patch to work properly with V11 Gateways.
The patch can be found on My Oracle Support by performing a simple search using the 'Patches & Updates' tab at the top of the page. Choose 'Simple Search' and enter 5965763 in the block for the patch number and select the appropriate platform from the Platform or Language list.
If there is not a patch available for your database version (i.e. 9.2.0.8, 10.1.0.5, or 10.2.0.3) for the platform you selected, please log a Service Request with Oracle Support requesting a backport for 'unpublished' Bug 5965763 to your platform.
This fix is automatically included in the 10.2.0.4 patchset.
FIX
How to Setup DG4Tera (Oracle Database Gateway for Teradata) on 32bit Windows operating systems
The Oracle Database Gateway for Teradata comes on a separate CD. It can be installed either into an existing 11g database Oracle_Home (please pay attention that if the Oracle_Home contains an already patched release of the database; then you MUST apply this patchset again. The reason for this is that the gateway installation might overwrite already patched libraries with the base version as delivered on the CD. To get a proper environment again an already applied patchset needs to be reapplied) or into its own stand alone ORACLE_HOME.
After the installation the following items must be configured:
1) listener (in the ORACLE_HOME where the gateway software is installed)
2) tnsnames (in the ORACLE_HOME where the Oracle database software is installed)
3) init<SID>.ora of the hs subsystem (in the ORACLE_HOME where the gateway software is installed)
4) Oracle database
1) The listener needs a new SID entry like:
The teradata shared libraries and client libs installation directory depends on the version of the Teradata client. Older versions installed the libraries into the windows\system32 directory; newer installations prompt for a program location.
Thus it is recommend to change the listener to make sure the correct libraries are found and add the PATH environment setting to the listener like:
(SID_DESC =
(PROGRAM = DG4TERA)
(SID_NAME = DG4TERA)
(ORACLE_HOME = D:\oracle\product\11.1.0)
(ENVS=PATH="C:\Program Files\NCR\Teradata Client\cliv2";"C:\Program Files\NCR\Common Files\Shared ICU Libraries for Teradata\lib")
)
In the example above the Teradata libraries are located in C:\Program Files\NCR\Common Files\Shared ICU Libraries for Teradata\lib and C:\Program Files\NCR\Teradata Client\cliv2. If you are not sure you can search for the libraries icudttd34l.dll and wincli32.dll (file names might differ depending on the TeraDta version being used).
Please correct the ORACLE_HOME entry and the ENVS entry according to your installation.
After adding ENVS parameter to the listener, please STOP and START the listener.
So a listener.ora file with a listener listening on port 1511 might look like:
SID_LIST_LISTENER =
(SID_DESC =
(PROGRAM = DG4TERA)
(SID_NAME = DG4TERA)
(ORACLE_HOME = D:\oracle\product\11.1.0)
(ENVS=PATH="C:\Program Files\NCR\Teradata Client\cliv2\lib";"C:\Program Files\NCR\Common
Files\Shared ICU Libraries for Teradata\lib")
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <hostname of the Oracle Gateway Server>) (PORT = 1511))
)
)
)
The listener must be stopped and started after changing the listener.ora file!
2) The tnsnames.ora (of the Oracle database that wants to use the gateway) needs an entry for the DG4TERA alias:
dg4tera.de.oracle.com =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=<hostname of the Oracle Gateway Gateway Server>)(PORT=1511))
(CONNECT_DATA=(SID=dg4tera))
(HS=OK)
)
The domain of the tns alias can differ from the one used above (de.oracle.com), depending on the parameter in the sqlnet.ora:
NAMES.DEFAULT_DOMAIN = de.oracle.com
But the important entry is the (HS=OK) key word. (HS=) is also a valid entry, but DBCA and NetCA will only recognize (HS=OK) entries and remove any (HS=) entries.
After adding the tnsnames alias and restarting the listener, a connectivity check is to use tnsping <alias>.
tnsping dg4tera
should come back with a successfull message.
3) init.ora of the gateway:
There are some restrictions how to name the SID (described in the Net Administrators Guide in detail). At this place only a short note: don't use dots in the SID and keep it short!
The SID is also relevant for the initialisation file of the gateway. The name of the file is init<SID>.ora. In this example it is called initdg4tera.ora. The file is located at $ORACLE_HOME/dg4tera/admin. It should contain at least the connect details:
The initdg4tera.ora file should contain at least:
HS_FDS_CONNECT_INFO=192.168.2.1:1025/gateway
#HS_FDS_TRACE_LEVEL=debug
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
HS_FDS_CONNECT_STRING ="EnableLOBs=No"
Please use the IP Address of the Teradata host and make sure the Teradata database is listening on port 1025. If you don't want to specify the IP address you have to follow the Teradata client config instructions by using an alias (with 8 or less characters) and add it to the hosts file located in \windows\system32\drivers\etc\ including the cop1 suffix as required by Teradata.
For example using an alias called TDzku118 requires to add into the hosts file:
192.168.2.1 Teradata.de.oracle.com TD118cop1
For Teradata databases listening on a different port then 1025 it is not enough to just change the port number in the HS_FDS_CONNECT_INFO section. It would require an additional set parameter:
SET TDMSTPORT= <port number of the Teradata database>
SIDE NOTE:
If you use the hostname without following the Teradata client configuration instructions the misleading error message might be raised::
[Oracle][ODBC Teradata Client driver][Teradata]CLI2: BADLOGON(303): Invalid
logon string.
[Oracle][ODBC Teradata Client driver]Invalid attribute in connection string:
ArraySize.[Oracle][ODBC Teradata Client driver]Invalid attribute in connection
string: SupportNumericPrecisionGreaterThan38.
ORA-02063: vorherige 3 lines von DG4TERA
So for a quick check, please use the IP address of the Teradata host instead of the name.
"gateway" is the name of the database you want to connect to.
4) Configuring the Oracle database
Here a database link needs to be defined:
First connect with an Oracle user having sufficient rights to create a database link (i.e. system) to the Oracle db.
The syntax to create a db link is:
create [public] database link <name> connect to <UID> identified by <pwd> using '<tnsalias>';
In other words, to connect to the Teradata database configured in the last steps, the syntax must be:
CREATE DATABASE LINK teradata CONNECT TO "terant" IDENTIFIED BY "terant" USING 'dg4tera';
The db link name is teradata. Username and password must be in double quotes, because usernames and passwords are case sensitive for the Teradata database. 'dg4tera' points to the alias in the tnsnames.ora file that calls the gateway.
If everything is configured well, a select of a Teradata table should be successful:
select * from "table"@teradata;
Please take care and surround table and column names with double quotes as Teradata table and column names are case sensitive.
Related
No comments:
Post a Comment