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
