Chapter 2 provided the insight needed to design and plan a Streams environment and "chart" the course of our distributed environment. This chapter takes the next step of preparing your "vessels", also known as databases and servers. The basic source and target server and database configurations for Streams are the same. In this chapter, we address the verification and configuration of the following:
- Network connectivity
- Database parameters and logging settings
- Stream Administrator user and privileges
- Data Pump and Instantiation preparation
- Optional creation of the Oracle Example schemas
At this point, the assumption is that you have already created your databases at each site involved in the Distributed Environment. This could have been accomplished with the Oracle software installation, or later using the Database Creation Assistant, or your own custom scripts. It is also assumed that you are able to connect to each database as SYSDBA.
Note
For more information on creating an Oracle Database, please reference the Creating and Configuring an Oracle Database chapter in the Oracle Database Administrator's Guide.
We mentioned earlier in Chapter 1 and Chapter 2, how important having a stable and reliable network is to the Streams environment. Without this comlink you just have a Source and Target with no ability to "stream" the data over the wire. Knowing this, having a good working relationship with the Network Administrator is vital. Network Administrators have additional tools and methods that can influence the performance of Streams. The Streams Administrator or DBA does not know the network path taken by the data from Source to Target unless a situation causes the question to come up. Be proactive, work with the Network Administrators, and ask the following questions:
- How does this data packet move from Source to Target?
- What is the typical network speed along that path?
- Is this a dedicated path?
- Is the network shared?
- What are the usage patterns of the network?
- Are there times when we can expect the network to be slower?
These questions are not the fun ones that most Network Administrators like to hear. However, by being proactive and knowing about your network performance, you will avoid headaches in the future. Just think of it this way: you can have a perfect Streams setup, but the data still has to cross the network and it is difficult to move a mountain of data through a straw-size network. Believe us; we have tried it too many times!
Streams uses the network in the Propagation process. When we define Propagation we will reference a dblink. That dblink uses the destination database global_name, and either an entry in the tnsnames.ora file or a tns descriptor. We will provide full examples of this configuration in Chapter 4, Single-Source Configuration,. For now, it should suffice to say, that the performance of Streams is dependent on the performance of the network. As Streams Administrators or DBAs, we do not usually get to tune the network, so work with your Network Administrators. We take ours out to lunch to stay on his good side!
The use of basic network tools such as ping and tracert can be used to determine network capabilities. Here are some examples of ping and tracert that should be run from the Source host (to the destination):
ping <IP ADDRESS> tracert <IP ADDRESS>
where<IP ADDRESS> is the IP address of the Target host. You may also want to run the same test going from Target host to Source host. Please also check with your Network Administrator as he/she may have additional tools and techniques to measure network performance that are already being used.
The following is a ping from IP 129.193.117.13 to 129.193.117.14 on Linux/Unix:
[oracle@dev-db01 ~]$ ping 129.193.117.14
PING 129.193.117.14 (129.193.117.14) 56(84) bytes of data.
64 bytes from 129.193.117.14: icmp_seq=1 ttl=64 time=0.103 ms
64 bytes from 129.193.117.14: icmp_seq=2 ttl=64 time=0.091 ms
64 bytes from 129.193.117.14: icmp_seq=3 ttl=64 time=0.089 ms
64 bytes from 129.193.117.14: icmp_seq=4 ttl=64 time=0.088 ms
64 bytes from 129.193.117.14: icmp_seq=5 ttl=64 time=0.089 ms
64 bytes from 129.193.117.14: icmp_seq=6 ttl=64 time=0.091 ms
64 bytes from 129.193.117.14: icmp_seq=7 ttl=64 time=0.088 ms
64 bytes from 129.193.117.14: icmp_seq=8 ttl=64 time=0.089 ms
64 bytes from 129.193.117.14: icmp_seq=9 ttl=64 time=0.090 ms
64 bytes from 129.193.117.14: icmp_seq=10 ttl=64 time=0.090 ms
64 bytes from 129.193.117.14: icmp_seq=11 ttl=64 time=0.088 ms
64 bytes from 129.193.117.14: icmp_seq=12 ttl=64 time=0.088 ms
64 bytes from 129.193.117.14: icmp_seq=13 ttl=64 time=0.087 ms
64 bytes from 129.193.117.14: icmp_seq=14 ttl=64 time=0.089 ms
64 bytes from 129.193.117.14: icmp_seq=15 ttl=64 time=0.089 ms
64 bytes from 129.193.117.14: icmp_seq=16 ttl=64 time=0.088 ms
64 bytes from 129.193.117.14: icmp_seq=17 ttl=64 time=0.088 ms
64 bytes from 129.193.117.14: icmp_seq=18 ttl=64 time=0.088 ms
64 bytes from 129.193.117.14: icmp_seq=19 ttl=64 time=0.089 ms
64 bytes from 129.193.117.14: icmp_seq=20 ttl=64 time=0.090 ms
64 bytes from 129.193.117.14: icmp_seq=21 ttl=64 time=0.088 ms
64 bytes from 129.193.117.14: icmp_seq=22 ttl=64 time=0.089 ms
--- 129.193.117.14 ping statistics ---
22 packets transmitted, 22 received, 0% packet loss, time 21000ms
rtt min/avg/max/mdev = 0.087/0.089/0.103/0.009 ms
Pay particular attention to the summary results at the end.
The result of running tracert from 172.26.12.12 to 172.26.13.23 shows two "hops " in the network between two servers.
[root@db01 ~]# tracert 172.26.13.23
traceroute to 172.26.13.23 (172.26.13.23), 30 hops max, 40 byte packets
1 172.26.12.1 (172.26.12.1) 0.948 ms 1.220 ms 1.462 ms
2 db02.apgtech.com (172.26.13.23) 0.157 ms 0.171 ms 0.171 ms
The following shows examples of the ping and tracert commands on Windows:
C:>ping 209.191.92.52
Pinging 209.191.92.52 with 32 bytes of data:
Reply from 209.191.92.52: bytes=32 time=53ms TTL=52
Reply from 209.191.92.52: bytes=32 time=54ms TTL=52
Reply from 209.191.92.52: bytes=32 time=52ms TTL=52
Reply from 209.191.92.52: bytes=32 time=54ms TTL=52
Ping statistics for 209.191.92.52:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 52ms, Maximum = 54ms, Average = 53ms
C:>tracert 209.191.92.52
Tracing route to f20.www.mud.yahoo.com [209.191.92.52]
over a maximum of 30 hops:
(Notice this IP takes over 30 hops! What gets output is the starting IP, and then the last 13 hops. The Request timed out on hop 2, and this is the one-liner catch-all for all the other hops)
1 <1 ms <1 ms <1 ms <ip>
2 * * * Request timed out.
3 9 ms 11 ms 9 ms ge-2-3-ur01.....net [<ip>]
4 12 ms 11 ms 11 ms te-0-2-0-1-ar02.....net [<ip>]
5 10 ms 11 ms 11 ms <ip>
6 25 ms 26 ms 26 ms pos-0-3-0-0-cr01...net [<ip>]
7 46 ms 43 ms 51 ms pos-1-7-0-0-cr01...net [<ip>]
8 53 ms 53 ms 53 ms <ip>
9 44 ms 42 ms 44 ms <ip>
10 52 ms 54 ms 52 ms <ip>
11 51 ms 52 ms 52 ms <ip>
12 52 ms 53 ms 52 ms ae2-p111.msr2.mud.yahoo.com [<ip>]
13 58 ms 53 ms 52 ms te-9-2....yahoo.com [<ip>`1]
14 54 ms 52 ms 52 ms UNKNOWN...yahoo.com [<ip>]
15 53 ms 52 ms 52 ms f20.www.mud.yahoo.com [<ip>]
Trace complete.
Here are some suggestions on how to establish a baseline on the network that you have.
Next, make sure that your Oracle Net files are configured to allow connectivity to and between the databases.
TNSNAMES.ORA:This file provides aliases that can be used for client connections. The aliases can be for local and remote databases.Special considerations: In many cases, the aliases contained in the
tnsnames.orafiles are configured with multiple addresses in the address list to accommodate high availability client failover. In the case of a Streams connection, we do not want to redirect our connection to a different database. So, if needed, add a single addresstnsnamesalias to thetnsnames.orato be used by Streams connections.Example of basic
tnsaliasentries:STRM2 = (DESCRIPTION = (ADDRESS = (PROTOCOL=TCP)(HOST=DB_SRV2)(PORT=1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = STRM2) ) STRM1 = (DESCRIPTION = (ADDRESS = (PROTOCOL=TCP)(HOST=DB_SRV1)(PORT=1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = STRM1) ) )
SQLNET.ORA:This file provides client/server connection parameters that control client access and database connectivity.Special considerations: If your server side
SQLNET.orais set up withINVITED_NODES, make sure to add the server IP or DNS name of all servers that host databases that will be connecting to the local database(s), to the invited nodes list. Otherwise, SQLNET will not allow the database links to connect to the database.Example of
sqlnet.oraentries:SQLNET.AUTHENTICATION_SERVICES = (NTS) --Windows only AUTOMATIC_IPC = OFF TRACE_LEVEL_CLIENT = OFF NAMES.DEFAULT_DOMAIN = mydomain.com #SQLNET.CRYPTO_SEED = "12101751101259991325" NAMES.DIRECTORY_PATH = (TNSNAMES) TCP.VALIDNODE_CHECKING = YES TCP.EXCLUDED_NODES= (138.3.33.33, NODB.mydomain.com) TCP.INVITED_NODES=(localhost, DB_SRV2.mydomain.com)
LISTENER.ORA:This file provides connection information to the local databases on the server. All client connections to a database come in through a listener process (with the exception of bequeath connections, but we don't use those here).Special considerations: Make sure listener processes are configured to start automatically if the server is rebooted. Otherwise, Streams (and other client) connections cannot be established with the database, even though the database may be up and running after reboot.
Example of Basic
Listener.oraentries:SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = strm1) (SID_NAME = strm1) ) ) LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL=TCP)(HOST=DB_SRV1)(PORT=1521)) )
Note
For more information on configuring Oracle Net Services, please reference the Oracle Database Net Services Administrator's Guide.