Oracle Net8

Contents

• Basics
• Links between the configuration files
• SQLNET.ORA
• TNSNAMES.ORA
• The Listener
• Encryption
• National Language Support
• Miscellaneous Notes

notes

Next in Knowledge Xpert: technical knowledge, network management, name resolution.

Basics

connect scott/tiger@connect_id
The connect ID corresponds to a net service name.
It is not needed if the client and the server are on the same machine.

tnsnames.ora on client:
  net_service_name=
    (description=
      (address = <address as in listener.ora> )
      (connect_data = (service_name = <global_dbname of listner.ora> ) )
       or
      (connect_data = (SID = <SID> ) )  -- for db 8.0 or before
    )

Links Between Configuration Files

? link between service_name and what is on server if global_dbname is commented out ?

See Oracle Enterprise Manager for details on agent.

SQLNET.ORA

The naming method tells the client where the connect ID is defined. Local uses tnsnames.ora.  Other methods are host, external, ...

The file is located in network/admin

NAMES.DEFAULT_DOMAIN=mycompany.com

If set, the default domain name is automatically appended to any unqualified net service name or service name.

NAMES.DIRECTORY_PATH = TNSNAMES

Determines the order of the naming methods name resolution lookups.
Default: TNSNAMES, ONAMES, HOSTNAME
Values: TNSNAMES, ONAMES, HOSTNAME, DCE, LDAP, NIS, NOVELL

LOG_DIRECTORY_CLIENT=C:\temp

Destination directory for the client log file
Default: Current directory from which the executable is started

LOG_DIRECTORY_SERVER=/tmp/log

Destination directory for server log file.
Default: Current directory from which the executable is started

LOG_FILE_CLIENT=filename

Name of log file for client.  Default: sqlnet.log

LOG_FILE_SERVER=server.log

Name of log file for server.  Default: sqlnet.log

TRACE_DIRECTORY_SERVER = /tmp/log

Destination directory for tracing

TRACE_FILE_SERVER = server.trc

Trace file name

TRACE_LEVEL_SERVER = ADMIN

Trace level

SQLNET.CLIENT_REGISTRATION=1234

Unique identifier for this client machine. Passed to the listener with any connection request and included in the Audit Trail. Alphanumeric string up to 128 characters long. Default: None.
Example: sqlnet.client_registration 1432

SQLNET.EXPIRE_TIME = 10   # minutes

Dead connection detection (put in sqlnet.ora of the server):

Encryption: in sqlnet.ora on server:

sqlnet.encryption_client = accepted
sqlnet.encryption_server = accepted
sqlnet.encryption_types_server = (RC4_256,RC4_128)
sqlnet.encryption_types_client = (RC4_256,RC4_128)
Note: in the sqlnet.ora on the server, the client (svrmgrl) is configured at the same time as the server.

Typical:

TRACE_LEVEL_CLIENT = OFF
#sqlnet.authentication_services = (NONE)
SQLNET.AUTHENTICATION_SERVICES= (NTS) # windows, not really useful
names.directory_path = (TNSNAMES, HOSTNAME)
names.default_domain = MY-COMP.COM
name.default_zone = MY-COMP.COM
automatic_ipc = off

# the following replaces sqlnet.log:
LOG_DIRECTORY_CLIENT = C:\temp
LOG_FILE_CLIENT = oracle_connection.log # delete file from time to time

TNSNAMES.ORA

In the tnsnames.ora, the net service names (connect id) are resolved to connect descriptors.  This descriptor gives the address of the listener.

The TNSNAMES.ORA file is located in network/admin.

8.0 and before: use SID as service name
8i and later: use SID or service_name
    SID = Instance_name
    Service_name = db_name & db_domain

General syntax:

• Indent wrapped lines by at least one space
• Start with a keyword in the leftmost column
• Not case sensitive
• Keywords without spaces, if spaces in values, then include in ' or "
• # --> comments
• \ is escape character

net_service_name=       # append the default domain (see parameter NAMES.DEFAULT_DOMAIN in sqlnet.ora)
  (description_list=
    (description=
      (failover=    ON | OFF | YES | NO | TRUE | FALSE)  # optional
      (load_balance=ON | OFF | YES | NO | TRUE | FALSE)  # optional
      (sdu=3000)        # optional, packet size, ideally a multiple of 1500 for ethernet
      (source_route=on) # optional, requires net8 to go through all addresses
      (address_list=
        (address=...see below...)
        (address=...see below...)
      (connect_data=...see below... )
    )
    (description=
      ...see above...
    )
  )

Minimum necessary:
net_service_name =
   (description=
       (address= listener protocol address 2 )
       (connect_data= destination )
   )

Address:
(address = (protocol = tcp)  # defines the type of network
           (host     = the host-name or tcpip address)
           (port     = port number, 1521 is default)
)

Address - bequeath:
(address = (protocol = bequeath) 
           (argv0    = service name )
           (args     = source of the connection - local client )
)

See documentation for recommended port numbers.  Port numbers from 1 to 65535, but below 1024 generally reserved on many operating systems.

Address - load balancing:
(address_list= (load_balance = on)
               (address=(protocol=tcp)(host=sales-pc)(port=1521))  # see above
               (address=(protocol=tcp)(host=hr-pc)(port=1521)))    # see above
               (address_list=
                             (address=(protocol=tcp)(host=finance-pc)(port=1521))
               )
)

I did this once when the server name did not resolve to the IP address when connecting from remote:
the_service_name =
(DESCRIPTION = (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = server_name)(PORT = 1526))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.3.70.55 )(PORT = 1526)) )
(CONNECT_DATA = (SID = xyz) (SERVER = DEDICATED) ) )

Connect data:
(connect_data = (service_name without s
                   = name defined in init file under service_names with s ))
                     If global_dbname in listener.ora file is SID           then service_name can be SID or SID.db_domain
                     If global_dbname in listener.ora file is SID.db_domain then service_name has to be SID.db_domain
(connect_data = (sid = sid for db 8.0 or before ))

Example:
connect user/pw@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<IP or name>)(PORT=1521))(CONNECT_DATA=(SID=<SID>)))

Typical (see also diagram)

THE_DB.MY-COMP.COM =
  (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER02)(PORT = 1521))
     )
     (CONNECT_DATA =
       (SERVICE_NAME = THE_DB.SERVER02)
      #OR
      #(SID          = THE_DB         )
     )
   )

TNSNAMES.ORA for Real Application Clusters

For more information on Real Application Clusters, see in backup and restore.
These notes from Oracle 9i documentation.

The following entry is for connection to the database in general. Note that the failover=on parameter is set by default for a list of addresses so it is not necessary to add it here. Use the failover mode (in the connect data) to define how the failover is done.

db.us.acme.com=
(description=
  (load_balance=on)
  (failover=on)
  (address_list=
    (address=(protocol=tcp)(host=db1-server)(port=1521))
    (address=(protocol=tcp)(host=db2-server)(port=1521)))
  (connect_data=
    (service_name=db.us.acme.com)
    (failover_mode=
      (type=select)
      (method=basic)
      (retries=20)
      (delay=15))))

The following entry is used to connect to one of the instances (for startup and shutdown for example).

db1.us.acme.com=
  (description=
    (address=(protocol=tcp)(host=db1-server)(port=1521))
  (connect_data=
    (service_name=db.us.acme.com)
    (instance_name=db1)))

db2.us.acme.com=
  (description=
    (address=(protocol=tcp)(host=db2-server)(port=1521))
  (connect_data=
    (service_name=db.us.acme.com)
    (instance_name=db2)))

This defines the remote listeners for each of the instances. This assumes (NMI&U) the following entry in listeners.ora (?): REMOTE_LISTENERS=listeners_<dbname>

listeners_db.us.acme.com=
  (address=(protocol=tcp)(host=db1-server)(port=1521))
  (address=(protocol=tcp)(host=db2-server)(port=1521))

Put this in the initSID.ora file if a non-default listener is used (NMI&U):

sid.local_listener=listener_sid

Client TNSNAMES.ORA for Real Application Clusters (RAC)

db.us.acme.com=
  (description=
    (load_balance=on)
    (failover=on)
    (address=(protocol=tcp)(host=db1-server)(port=1521))
    (address=(protocol=tcp)(host=db2-server)(port=1521))
  (connect_data=
    (service_name=db.us.acme.com)))

Test the connection:

1) Connect to the database
2) Update a table
3) Connect to the other nodes and check that the update in (2) shows.

The Listener

If I remember correctly, the listener.ora is in network/admin

Stop and start the listener:
lsnrctl stop  [listener_name]
lsnrctl start [listener_name]

Status of listener:
lsnrctl status [listener_name]
Also, test from remote system by connecting (sqlplus username/password@net_service_name)

Stop and start the dbsnmp agent:
lsnrctl dbsnmp_stop
lsnrctl dbsnmp_start

Reserve the port for the listener(s) in the /etc/services file (user root):
listener_name 1521/tcp       #Oracle Net listener

The listener on the server spawns processes.  The databases are defined in the file listener.ora.  They can also be defined by the P_MON process (version 8i).Note that there is only one listener.ora file per server. External procedure are those called by PL/SQL code (generally C)

Address parameter on server must be matched by address parameter on client.

listener=                   -- address is mandatory
  (description=
    (address_list=
      (address=(protocol=tcp)(host=sale-server)(port=1521))
      (address=(protocol=ipc)(key=extproc))
      (address=(protocol=spx)(service=orasrvc1))  # we don't use spx
    )
  )

# for Jserver: new in 8i

listener=
  (description_list=
    (description=
      (address=(protocol=tcp)(host=sales1-server)(port=2481))
      (protocol_stack=(presentation=TTC)(session=NS))         # Two-Task Common, Network Session layer,
                                                              # for connection to instance, instead of
                                                              # address_list=(address=(((protocol=tcp)...) above
      (protocol_stack=(presentation=giop)(session=raw))       # General Inter-Orb Protocol (CORBA)
    )
  )

# undocumented, best to use one description for each address:

listener=
  (description_list=
    (description=
      (address_list=
        (address=(protocol=tcp)(host=sale-server)(port=1521))
        (address=(protocol=tcp)(host=ip-address)(port=1522))
      )
      (protocol_stack=(presentation=TTC)(session=NS))        
    )
  )

List of databases is optional, but if it exists, only one list is allowed.  Note that 8i databases register themselves automatically, but no harm in giving a description

sid_list_ listener_name=  
  (sid_list=
    (sid_desc=
      (global_dbname= global_database_name)  # = DB_NAME & DB_DOMAIN, must be one of the SERVICE_NAMES in init file
      (sid_name= sid)                        # = INSTANCE_NAME param in init file
      (oracle_home= /usr/oracle )
      (prespawn_max=99)          # not on NT
      (prespawn_list=
        (prespawn_desc= (protocol=tcp) (pool_size=10) (timeout=2)  )
        (prespawn_desc=...)
      )
    )

    (sid_desc=
      (sid_name=plsextproc)
      (oracle_home=oracle)
      (program=extproc)
    )
    (sid_desc=...)
  )

External procedure:
Listener= 
  (description=             # 8i only
      (address=(protocol=ipc)(key=extproc0))  # 8i and 8.0
      (protocol_stack =     # 8i only
         (presentation = ttc)
         (session = ns)
      )
  )

sid_desc corresponding to external procedure:
         (sid_desc=
           (sid_name=plsextproc)
           (oracle_home=...)
           (program=extproc)   # extproc is executable in $ORACLE_HOME/bin
         )

extproc_connection_data entry in TNSNAMES:
         (description=
           (address_list=
             (address=(protocol=ipc)(key=extproc0))   # Same as in listener.ora
           )
           (connect_data=
             (SID = PLSExtProc)
           )
         )

N.B. To improve security, start a separate listener as another user with well defined privileges.

If the SID_DESC is not defined, then the listener will not register the database at startup. Instead, the database will register itself when it starts.  However, if the listener is stopped and re-started, the databases that are running will not be re-registered.  The agent will not detect the database if the SID_DESC is not given.

If the global_dbname is not in the SID_DESC, then the default db_domain appears in the OEM.

log_directory_listener_name = /tmp/log
log_file_listener_name = listener.log
startup_wait_time_listener_name = 10
connect_timeout_listener_name = 20
trace_directory_listener_name = /tmp/log
trace_file_listener_name = listener.trc
trace_level_listener_name = OFF

Stop and start the listener after changes:
lsnrctl stop  [listener_name]
lsnrctl start [listener_name]

Listener with a non-default address (not 1521):
In the parameter file of the database:
local_listener= listener_name_alias
Put in the tnsnames.ora file:
listener1=
            (address=(protocol= tcp)(host= sales-server)(port= 1421))

Test:
tnsping net_service_name [count]
        net_service_name must be defined in the tnsnames.ora
        count is the number of tries (optional)

For choice of port numbers, see 
 - /etc/services file on system (C:\WINNT\SYSTEM32\DRIVERS\ETC\SERVICES)
 - Internet Assigned Numbers Authority (IANA)  http://www.iana.org/numbers.html
      According to this list, the ports used:

66, 1525, 1527, 1529, 1571 (remote DB), 1575 (Oracle Names), 1630 (Oracle Net8 Cman), 1748 (Oracle-em1), 1754 (Oracle-em2),  1808 (Oracle-VP2), 1809 (Oracle-VP1), 1830 (CMan Admin), 2005,  2481 (GIOP corba), 2482 (GIOP SSL), 2483 (TTC), 2484 (TTC SSL),

In case of multiple listeners, force a database to register with one particular listener:
LOCAL_LISTENER="(ADDRESS_LIST= \
(ADDRESS=(PROTOCOL=TCP)(PORT=1523)(HOST=donna.gennick.org)) \
)"

If you are running MTS, you may want to specify the listener using the MTS_DISPATCHER parameter’s LISTENER attribute.

If the listener is started AFTER the instance, the listener must be registered. This is done by default every 60 seconds by the PMON. Therefore, do this to force the PMON to register the service immediately: ALTER SYSTEM REGISTER

For Real Application Clusters, see in backup and restore.

Encryption

Four possible values for "sqlnet.encryption_client " or "sqlnet.encryption_server":

• rejected
• accepted
• requested
• required

Example:

On server: sqlnet.encryption_server = requested
On client: sqlnet.encryption_client = requested
On client: sqlnet.encryption_types_client = (RC4_256,RC4_128) --> when connecting to the database, the client "asks for" encryption and the server "accepts" it.
With these settings, the client cannot use SSL (double encryption not allowed). With "requested" and not "required" on the client, connection to other servers that do not implement encryption is still possible.
On SSL clients: sqlnet.encryption_client = rejected
On non-SSL client: sqlnet.encryption_client = accepted

NB: Advanced security must be installed

National Language Support

Windows: the encoding scheme (=Characterset) is specified by a Code Page. From Oracle point of view the terms Code Page and Characterset mean the same.

NLS_LANG = LANGUAGE_TERRITORY.CHARACTERSET parameter that determines the behavior of the Oracle client, where:
LANGUAGE specifies language used for Oracle messages; day and month names
TERRITORY specifies monetary and numeric formats; territory and conventions for calculating week and day numbers
CHARACTERSET controls the character set used by the client application or it matches your Windows code page or it set to UTF8 for an unicode application
See http://otn.oracle.com/pls/db92/db92.docindex?remark=homepage

See NLS_LANG in registry: HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMExx\ (xx corresponds to the content of %ORACLE_HOME%\bin\ORACLE.KEY)
Currently: AMERICAN_AMERICA.WE8ISO8859P1
or
Right-click the 'My Computer' icon -> 'Properties' Select the 'Advanced' Tab -> Click on 'Environment Variables'

See the codepage:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage\
ACP = 1252
OEMCP = 437
OEMHAL = vgaoem.fon
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\language\
default = 0409
Most other entries have "l_intl.nls"
http://www.microsoft.com/globaldev/reference/ (under the REFERENCE tab on the left of the page)
OEM = the command line codepage, ANSI = the gui codepage

The ACP (=ANSI Code Page) is changed in the Control Panel / "Regional Settings" / "Regional Settings" tab. Choose the language and click in "Set as system default locale". In W2000: first add the input-locale in "Regional Options" / "Input Locales" (add a new locale). Then go to the general tab and choose the newly added locale and click the language setting (click more that one if not sure). Then click on default.

[NOTE:150091.1] Globalization Technology (NLS) Library index

check on UNIX:
HOST echo $NLS_LANG or HOST env | grep NLS_LANG

Check on windows:
    host echo %NLS_LANG% > nls_lang.txt
    ed nls_lang.txt
if returns "%NLS_LANG%", then the value not set in environment, so try:
    @.[%NLS_LANG%].
if returns ".[%NLS_LANG%]." then not set in registry, so try:
    SELECT * FROM NLS_SESSION_PARAMETERS;
    SELECT * FROM NLS_DATABASE_PARAMETERS;
    SELECT * FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='NLS_CHARACTERSET';

AL32UTF8 is the Oracle Database character set that is appropriate for XMLType data. Do not use UTF8 for XML data, because it supports only Unicode version 3.1 and earlier.

Set the client character set in .profile with
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

UTF-16: Each character is either 2 or 4 bytes long.
UTF-8: Each character takes 1 to 4 bytes to store

Useful page for explaining Unicode

To see a list of valid locales for a given operating system: locale -a
The character set of the server: env | grep LC_

unistr('\2026'): gives the character for the Unicode code point 2026
asciistr('a string with unicode'): displays the non-ascii characters with the \nnnn notation
ascii(unistr('\2026')): gives the numeric code of the character, here 14844070
chr(14844070): gives the character from its numeric code; equivalent to chr(ascii(unistr('\2026')))
select asciistr('àè –(em dash) “a quote”') from dual; --> \00E0\00E8 \2013(em dash) \201Ca quote\201D
Translate special characters:
select translate ('a string with special characters’, unistr('\2013') || unistr('\201C') || unistr('\201D'), '-""') from dual;
Display a special character:
select unistr('\2013') from dual;

List of common NLS_LANG's used in the Command Prompt (DOS box):

If you have a full unicode application, then you need to set the NLS_LANG to UTF8. Otherwise, it all depends on the codepage of the client.

Set the environment variables NLS_LANG, NLS_DATE_FORMAT, ...
Or set the session parameters with ALTER SESSION ...

Note:60134.1 Subject: Globalization (NLS) - Frequently Asked Questions
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=150091.1 : Globalization Technology (NLS) on data storage, data access and server utilities
Doc ID: Note:158577.1
Subject: NLS_LANG Explained (How does Client-Server Character Conversion Work?)

See also Microsoft Typography

For information, Miscrosoft codepages:

SBCS (Single Byte Character Set) Codepages:

• 1250 (Central Europe)
• 1251 (Cyrillic)
• 1252 (Latin I)
• 1253 (Greek)
• 1254 (Turkish)
• 1255 (Hebrew)
• 1256 (Arabic)
• 1257 (Baltic)
• 1258 (Vietnam)
• 874 (Thai)

DBCS (Double Byte Character Set) Codepages:

• 932 (Japanese Shift-JIS)
• 936 (Simplified Chinese GBK)
• 949 (Korean)
• 950 (Traditional Chinese Big5)

Windows OEM codepages:

• 437 (US)
• 720 (Arabic)
• 737 (Greek)
• 775 (Baltic)
• 850 (Multilingual Latin I)
• 852 (Latin II)
• 855 (Cyrillic)
• 857 (Turkish)
• 858 (Multilingual Latin I + Euro)
• 862 (Hebrew)
• 866 (Russian)

Both Windows ANSI and OEM codepages:

• 874 (Thai)
• 932 (Japanese Shift-JIS)
• 936 (Simplified Chinese GBK)
• 949 (Korean)
• 950 (Traditional Chinese Big5)
• 1258 (Vietnam)

Issue with character set

Issue encountered in 2007

The NLS_LANG environment variable determines the language of the user interface for components such as SQL*Plus, exp, and imp. It declares the character set for entering and displaying data by the client application.

The current client settings on the server are incompatible:

• The database character set is UTF8. This can be seen with:
  select * from database_properties where PROPERTY_NAME='NLS_CHARACTERSET';
• The server's character set is ISO8859-1. This can be seen with:
  env | grep LC_
• The Oracle client (for the user infadmin on the server) is configured to handle the AL32UTF8 character set, which is a Unicode character set. This can be seen with:
  env | grep NLS_LANG

The problem is that the Oracle client is configured to handle Unicode on a server that cannot interpret Unicode, but only the 255 characters of ISO8859. For this reason, the UTF8 characters retrieved from the database are translated into AL32UTF8, the declared character set of the Oracle client. There is no expected loss here because both are Unicode character sets. However, the resulting Unicode characters are treated as ISO8859 characters on the server because the server cannot handle Unicode. This is not a problem for most characters because UTF8 and ASCII are encoded in the same way for English characters. The errors show for some of the special characters for which the server interprets the three bytes of a Unicode character as three separate characters and displays these three characters separately.

The documentation for Oracle explicitly says that the NLS_LANG setting does not have to match the database character set. The following is taken from http://download.oracle.com/docs/cd/B28359_01/server.111/b28298/ch3globenv.htm#i1006280

"The NLS_LANG character set should reflect the setting of the operating system character set of the client. For example, if the database character set is AL32UTF8 and the client is running on a Windows operating system, then you should not set AL32UTF8 as the client character set in the NLS_LANG parameter because there are no UTF-8 WIN32 clients. Instead, the NLS_LANG setting should reflect the code page of the client. For example, on an English Windows client, the code page is 1252. An appropriate setting for NLS_LANG is AMERICAN_AMERICA.WE8MSWIN1252.

"Setting NLS_LANG correctly enables proper conversion from the client operating system character set to the database character set. When these settings are the same, Oracle Database assumes that the data being sent or received is encoded in the same character set as the database character set, so character set validation or conversion may not be performed. This can lead to corrupt data if the client code page and the database character set are different and conversions are necessary."

If the NLS_LANG parameter is modified to AMERICAN_AMERICA.WE8ISO8859P1 (instead of AMERICAN_AMERICA.AL32UTF8), the Oracle client will be working in the same character set as the Solaris server. The change should be done for the infadmin user in the .profile in the user's home. After this, all the Informatica services should be restarted.

Because the NLS_LANG parameter only affects the Oracle client, re-installing the database is not necessary.

2) Translation of special characters

Normally, special characters such as smart quotes, em dashes, and ellipses will be handled by translating them explicitly with something like:
select translate ('a string with special characters’, unistr('\2013') || unistr('\201C') || unistr('\201D'), '-""') from dual;
This example translates the em dash (unistr('\2013')) and smart double quotes (unistr('\201C') and unistr('\201D')) respectively into an en dash and double quotes.

The translate function will solve the issue only for special characters that appear correctly in application and that are stored correctly in the database. It is expected that this is the case for text that is pasted directly into application.

Miscellaneous Notes

Dedicated Server: one process per connection

MTS: pool of processes for the connections.  Useful in case of a lot of idle time.

Oracle Internet Directory is LDAP.  Replaces the TNSNAMES.ORA on each client.  Net8 is still used underneath during the connection to the DB.

Oracle Names is soon to be obsolete.

Connection Manager is like a router.

SDU size (guess work to get the right size): edit the TNSNAMES.ORA and the LISTENER.ORA:

the_sid.world =
   (DESCRIPTION =
     (SDU=4096) #<-- SDU can be set from 512 bytes to 32Kb
     (ADDRESS_LIST = ... )
   )

STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = OFF
SID_LIST_LISTENER = (SID_LIST =
     (SID_DESC =
       (SDU=4096) <---------- SDU has to match the setting in tnsnames
                              otherwise it will negotiate to the lowest of the two sizes
       (SID_NAME = ORCL) ) )