| Previous | Table of Contents | Next |
Managing The Listener Process
The listener is a software program that runs on each remote node and “listens” for any incoming database requests. When a request is detected, the listener may direct the request to:
Note that the configuration of an Oracle listener is a direct result of the parameters that are specified in the startup deck for the Oracle database. This parameter file is called init.ora, and will contain the following parameters to define the multithreaded server and listener:
# ---------------------- # Multi-threaded Server # ---------------------- MTS_DISPATCHERS = "tcp,3" MTS_LISTENER_ADDRESS = "(ADDRESS=(PROTOCOL=tcp) (HOST=seagull) (PORT=1521))" MTS_MAX_DISPATCHERS = 5 MTS_MAX_SERVERS = 20 # -------------------------- # Distributed systems options # -------------------------- DISTRIBUTED_LOCK_TIMEOUT = 60 DISTRIBUTED_RECOVERY_CONNECTION_HOLD_TIME = 200 DISTRIBUTED_TRANSACTIONS = 6
Miscellaneous Management Tips For SQL*Net
Just as the /etc/oratab file for SQL*Net version 1.0 is interpreted, the tnsnames.ora file is also interpreted. This means that you can change it at any time without fear of bouncing anything. However, changes to listener.ora require that the listener be reloaded with LSNRCTL RELOAD.
When a database is accessed remotely via a database link, SQL*Net uses the temporary tablespace on the destination database, regardless of the processor invoking the task or the original database location. The moral? SQL*Net will use the temporary tablespace on the destination database—not on the initiating database. In other words, applications on one processor that accessed another processor with a database link will use the temporary tablespaces on the terminal processor—not the processor that contains the link.
Always remember to change your $ORACLE_HOME/bin/oraenv file to unset ORACLE_SID and set TWO_TASK=sid.
Three logs appear in SQL*Net:
Three levels of tracing are found in SQL*Net:
It is possible to run two listeners, one for version 1 and another listener for version 2. If a version connect string is sent, a version listener (TCPCTL) will be used. Conversely, if a TNS connect description is sent, the version listener (LSNRCTL) will be used. A connect description is the name of a database (such as @mydata), which maps to the tnsnames.ora on the sending side, and listener.ora on the receiving side.
It is essential to note that the functions of the oracle_sid and two_task variables have changed. To use the MTS while you are “local” to the database, you should unset the oracle_sid variable and set the two_task to the SID name (EXPORT two_task=mydb). If the oracle_sid is active, you will still be able to connect—although you will not be able to take advantage of the MTS. You must change all login scripts and ORACLE_HOME/bin/oraenv files to reflect this new functionality.
We now have three ways to establish distributed database communications with MTS. We can choose from a shared service name (sqlplus /@ram2db) or a dedicated service name (sqlplus /@d_ram2db—prefixing the SID with d_ will direct the listener to spawn a dedicated process for your program). We can also use a (two_task) server connect string (sqlplus /@t:host:sid). This latter approach will bypass the MTS and use a dedicated process.
When a distributed update (or delete) has finished processing, SQL*Net will coordinate COMMIT processing, which means that the entire transaction will roll back if any portion of the transaction fails. The first phase of this process is a prepare phase to each node, followed by the COMMIT, and then terminated by a forget phase.
If a distributed update is in the process of issuing the 2PC and a network connection breaks, Oracle will place an entry in the dba_2pc_pending table. The recovery background process (RECO) will then roll back or commit the good node to match the state of the disconnected node to ensure consistency. You can activate RECO via the ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY command.
The dba_2pc_pending table contains an advise column that directs the database to either COMMIT or ROLLBACK the pending item. You can use the ALTER SESSION ADVISE syntax to direct the 2PC mechanism. For example, to force the completion of an INSERT, you could enter the following:
ALTER SESSION ADVISE COMMIT; INSERT INTO payroll@london . . . ;
When a 2PC transaction fails, you can query the dba_2pc_pending table to check the state column. You can enter SQL*DBA and use the “recover in-doubt transaction” dialog box to force either a rollback or a commit of the pending transaction. If you do this, the row will disappear from dba_p2c_pending after the transaction has been resolved. If you force the transaction the wrong way (for example, rollback when other nodes committed), RECO will detect the problem, set the MIXED column to yes, and the row will remain in the dba_2pc_pending table.
Internally, Oracle examines the init.ora parameters to determine the rank that the commit processing will take. The commit_point_strength init.ora parameter determines which of the distributed databases is to be the commit point site. In a distributed update, the database with the largest value of commit_point_strength will be the commit point site. The commit point site is the database that must successfully complete before the transaction is updated at the other databases. Conversely, if a transaction fails at the commit point site, the entire transaction will be rolled back at all of the other databases. In general, the commit point site should be the database that contains the most critical data.
Listing 8.5 shows a script that will identify two-phase commit transactions that have failed to complete.
| Previous | Table of Contents | Next |