fbpx

Oracle Networking Examples


Lesson 3 - Networking 
----------------------
1. Connect-Identifier 
2. Connect Decriptor (Machine, Port, Protocol, Actual instance/service name)
3. Naming method 
   - 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. LISTENER
   -  Static registration of all Instances in LISTENER.ORA
   -  Dynamic registration - PMON
5. TNSPING

-------------------------------------------------
-- Networking
-------------------------------------------------
-- C:\app\Administrator\product\11.2.0\dbhome_1\sqlplus\admin\glogin.sql
set sqlprompt "_USER>"

-- Connect locally as sysdba :
------------------------------

conn / as sysdba 

-- Edit $oracle_home\network\admin\sqlnet.ora
-- SQLNET.AUTHENTICATION_SERVICES= (NTS) => #SQLNET.AUTHENTICATION_SERVICES= (NTS)

conn / as sysdba 

[ORA-01031: insufficient privileges] 

conn sys/oracle as sysdba

-- Connect locally as any other user : 
--------------------------------------

conn system/password

-- change the registry entry for ORACLE_SID

host 
set oracle_sid = orc123
sqlplus / nolog 

conn system/password

[ORA-12560: TNS:protocol adapter error]


-- Conect remotely:
-------------------

-- connect user_name/password@connect_identifier

connect hr/hr@orcl

-- Naming method - Connect-Identifier => Connect Decriptor
----------------------------------------------------------

-- * 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.

-------------------------------------------------
-- Naming Methods
-------------------------------------------------

-------------------------------
-- 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 pdborcl to test11g : 

edit ?\NETWORK\ADMIN\TNSNAMES.ora 

conn hr/hr@pdborcl

[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@pdborcl 

[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  
--   is direcly 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@pdborcl 

[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@pdborcl

[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@pdborcl

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

UpScale Analytics is one of the largest platforms in the world for learning SQL by doing, consisting over 300 SQL exercises at different levels (including solutions), by topics, across over 100 different datasets. More…