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
