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