Basic Oracle networking principles in 10 minutes

PROBLEM:

You want to learn some basic Oracle networking principles in 10 minutes:

1. What is Connect-Identifier

2. What is Connect Descriptor (Machine, Port, Protocol, Actual instance/service name)

3. What is Naming methods

• Local Naming (Uses a local configuration file - TNSNAMES.ORA)
• EZCONNECT (Uses a TCP/IP connect string )
• conn user_name/password@host_name:[port_number]/service_name
• LDAP (Uses a Directory Server)
• External Naming (Uses a 3rd party naming service)

4. What is a LISTENER

• Static registration of all Instances in LISTENER.ORA
• Dynamic registration - PMON

5. What is TNSPING

SOLUTION:

First change the SQL prompt to see current login in the following file: C:\app\Administrator\product\11.2.0\dbhome_1\sqlplus\admin\glogin.sql

set sqlprompt "_USER>"

Open SQL*PLUS and connect locally as sysdba

conn / as sysdba

Edit $oracle_home\network\admin\sqlnet.ora

change

SQLNET.AUTHENTICATION_SERVICES=(NTS)

to

#SQLNET.AUTHENTICATION_SERVICES=(NTS)

conn / as sysdba

[ORA-01031: insufficient privileges]

conn sys/oracle as sysdba

Connect locally as any other user

conn hr/hr

change the registry entry for ORACLE_SID

host

set oracle_sid = orc123

sqlplus / nolog

conn hr/hr

[ORA-12560: TNS:protocol adapter error]

Conect remotely:

connect user_name/password@connect_identifier

connect hr/hr@orcl

Naming method - Connect-Identifier => Connect Descriptor

-------------------------------------------------------

Which actual database is ORCL pointing to, and where is it located?

Oracle uses a "Naming Method" to translate the the "Connect-Identifier" to a "Connect Decriptor", which includes:

   * Machine

   * Port

   * Protocol

   * Actual instance/service name

Client naming methods:

   * EZCONNECT (Uses a TCP/IP connect string)

   * Local Naming (Uses a local configuration file - TNSNAMES.ORA)

   * LDAP (Uses a Directory Server)

   * External Naming (Uses a 3rd party naming service)

All Networking files located in:

  %Oracle-Home%\Network\Admin\

  * Usually:

     * Client Side: TNSNAMES.ORA

     * Server Side: LISTENER.ORA

Can also use the environment variable "TNSADMIN" to specify shared network location

for networking files.

TNSNAMES (Local Naming)

-----------------------

Client side uses a TNSNAMES.ORA file to resolve connect identifier.

The file is located in the %Oracle-Home%\NETWORK\ADMIN\

Typical TNSNAMES Entry:

Connect identifier is ORCL11g (Logical name for the client only), which points to the ORCL db, located on the machine OraSRV3. The Listener is listening on its default port 1521, and accepting TCP requests:

ORCL11g =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = OraSRV3)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

Change the connect identifier

[ORA-12154: TNS:could not resolve the connect identifier specified] 

get  ?\NETWORK\ADMIN\TNSNAMES.ora

Change orcl to test11g

edit ?\NETWORK\ADMIN\TNSNAMES.ora

conn hr/hr@orcl

[ORA-12154: TNS:could not resolve the connect identifier specified] 

conn hr/hr@test11g

SELECT instance_name FROM v$instance;

Change the host in thsnames.ora

[ORA-12545: Connect failed because target host or object does not exist]

conn hr/hr@orcl

[ORA-12545: Connect failed because target host or object does not exist]

LISTENER

--------

Server side uses a LISTENER to handle incoming connection requests.

The Listener validates the Username and Password, and connect the client to the requested instance.

The Listener is responsible to create a server process for the user process

When connection between UP and SP established (Handoff), the communication pathway s directly between UP and SP.

The listener must know the DB Service requested by the client:

  * Until 8i - Static registration of all Instances in LISTENER.ORA

  * Since 8i - Dynamic registration - PMON registers the Instance on statup and every few minutes

Stop the listener [ORA-12541: TNS:no listener] 

----------------------------------------------

Manage the listener using the utility "LSNRCTL"

host lsnrctl help

host lsnrctl status

host lsnrctl stop

host lsnrctl start

host lsnrctl reload

host lsnrctl stop

conn hr/hr@orcl

[ORA-12541: TNS:no listener]

host lsnrctl start

Same error will occur when the port number is incorrect

Delete orcl service from the listener

[ORA-12514: TNS:listener does not currently know of service...]

edit ?\NETWORK\ADMIN\listener.ora

conn hr/hr@orcl

[ORA-12514: TNS:listener does not currently know of service requested in connect descriptor]

Dynamic registration will take place in a few minuters or can register manually from the DB:

ALTER SYSTEM REGISTER;

View the LISTENER.ORA file:

host type C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora

Trying to startup the database remotely without a static registration

[ORA-12514: TNS:listener does not currently know of service requested in connect descriptor]

edit ?\NETWORK\ADMIN\listener.ora

conn hr/hr@orcl

ALTER SYSTEM REGISTER;

conn hr/hr@orcl

conn sys/oracle@orcl as sysdba

shutdown immediate

startup

host lsnrctl reload

conn sys/oracle@orcl as sysdba

startup

TNSPING

-------

Tests the Oracle connectivity to a remote service's Listener:

host TNSPING orcl

host TNSPING no_such_service

Note: This utility only tests if the listener is available. It cannot tell if the databases behind the listener is up or not.

EZCONNECT - (10g)

-----------------

Specify the connect descriptor in the CONNECT command

No need for TNSNAMES.ORA in the clients. Uses regular host resolution in the network.

CONNECT username/password@[//][host][:port][/service_name]

Add EZCONNECT to sqlnet.ora

Only when onnecting to an Oracle database across a TCP/IP network

conn user_name/password@host_name:[port_number]/service_name

conn hr/hr@basdb_ins:1521/orcl

conn hr/hr@basdb_ins/orcl

conn hr/hr@basdb_ins:1521/orcl

Directory Naming

----------------

With the directory naming method, connect identifiers are mapped to connect descriptors contained in an LDAP-compliant directory server.

To use directory naming, you must first install and configure a directory server somewhere on your network.

Oracle provides an LDAP server (the Oracle Internet Directory) as part of the Oracle Application Server, but you do not have to use that.

If you already have a copy of Microsoft Active Directory, that will be perfectly adequate.

IBM and Novell also sell directory servers conforming to the LDAP standard.

LDAP - stands for Lightweight Directory Access Protocol.

External Naming

---------------

External naming is conceptually similar to directory naming.

The external naming method stores net service names in a supported non-Oracle naming service

it uses third-party naming services such as Sun’s Network Information Services (NIS+) or the Cell Directory Services

NIS -stands for Network Information Service.

Following steps need to be performed in order to configure your clients against the server (ldap).

In your Oracle Client configure the sqlnet.ora

  * NAMES.DIRECTORY_PATH = (LDAP)

  * NAMES.DEFAULT_DOMAIN = trivadis.com # adjust to your domain

In your Oracle Client configure the ldap.ora

  * DIRECTORY_SERVERS = (oassrv01.trivadis.com:389:636) # adjust to your servername and LDAP ports

  * DEFAULT_ADMIN_CONTEXT = "dc=trivadis,dc=com" # adjust to your Naming Context

  * DIRECTORY_SERVER_TYPE = OID

Specifying Naming Methods

-------------------------

Via NAMES.DIRECTORY_PATH in "sqlnet.ora"

For example: NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

host type C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\sqlnet.ora

Service names

-------------

For Local and Directory naming, we can specify a few network addresses for the same service.

ALTER SYSTEM SET service_names='orcl, reports,orders';

host lsnrctl status

Connecting using EZconnect

conn sys/oracle@RAM/orders

Connecting using TNSnames

reports =

 (DESCRIPTION =

   (ADDRESS_LIST =

     (ADDRESS = (PROTOCOL = TCP)(HOST = RAM)(PORT = 1521))

   )

 (CONNECT_DATA =

   (SERVICE_NAME = reports)

 )

)

conn hr/hr@reports

SELECT instance_name FROM v$instance;

col username for a15

SELECT username, service_name

FROM v$session

WHERE username IS NOT NULL;

DBLINK

------

A database link is a connection between two physical database servers that allows a client to access them as one logical database.

conn sh/sh@reports

SELECT count(*) FROM products;

conn hr/hr@orcl

--drop database link sales_db;

SELECT count(*) FROM products;

SELECT count(*) FROM sh.products;

CREATE DATABASE LINK sales_db

CONNECT TO sh IDENTIFIED BY sh

USING 'reports';

SELECT COUNT(*) FROM products@sales_db;

Disable win firewall

--------------------

Start => Control Panel => Windows Firewall => Exceptions => Add Port => Oracle, 1521 => OK

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!