SQLNET: How does Oracle Client connect with Oracle Server

Lets first try to understand what is Oracle Server and what is Oracle Client:

Oracle Database Server:
Oracle Database Server is usually a physical machine running one or more Oracle Database Instance along with its Database, which also facilitate by providing a connection mechanism to database using listener.

Oracle Client:
Oracle Client is usually a process or a program on a machine, which tries to connect to Oracle Database Server. Oracle Client can exist on any computer usually connected to Oracle Database Server machine by a network. It may be on Same Oracle Database Server also.

Now lets understand what happens when an Oracle Client tries to connect to Oracle Database Server in happy scenario i.e. every thing is up and running and properly configured. Lets try to understand it by what happens when we try to connect Oracle Server by sqlplus as

sqlplus scott/tiger@orcl

Steps / Process Involved
1) Operating System locates “sqlplus” and pass command line parameter “scott/tiger@orcl”
2) sqlplus now asks TNS to connect to “orcl” using user “scott” and password “tiger”
3) TNS now looks into “tnsnames.ora” file to find out the meaning of “orcl”. Default location of the file is “$ORACLE_HOME/network/admin” and most probably has entry for “orcl” like

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ngarg.mydomain.co.in)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.mydomain.co.in)
    )
  )

4) Now it will try to connect Oracle Database Server Machine “ngarg.mydomain.co.in” on port 1521.
5) On Oracle Database Server there will be a listener running on 1521 port, listening to “orcl” service.
6) The listener will assign a server process to client.

General Issues we can face in connecting Oracle Client to a Oracle Database Server:
1) ORA-12154: TNS:could not resolve the connect identifier specified: When there is an issue in TNSNAMES.ORA like connect identifier entry for “ORCL” is not existing or proper in TNSNAMES.ORA.
2) ORA-12545: Connect failed because target host or object does not exist: “ngarg.mydomain.co.in” can not be reached over the network.
3) ORA-12541: TNS:no listener: There is no listener running on “ngarg.mydomain.co.in”
4) ORA-12560: TNS:protocol adapter error: There is no listener running on “ngarg.mydomain.co.in” at port 1521, or listener not properly configured.
5) ORA-12514: TNS:listener does not currently know of service requested in connect descriptor: Listener is running but is not configured for the service “orcl.mydomain.co.in”

Advertisements

How Connect to Oracle when you forgot your Password

Reset Oracle Password

 
Step1: Open SQL Command Line  and enter the following command:

SQL> conn sys/manager as sysdba;
Connected.

Step2: To reset the password of the SYSTEM password (or any other user password), run the following query:

SQL> alter user sys identified by manager;
User altered.

Or

Your password file should be under <orahome>\database\PWD<SID>.ora.

Delete it and run the Oracle password utility from the command prompt:

c\:Oracle\ora92\database>ORAPWD file=PWD<SID>.ora password={password} entries={however many}.

The <password> is your new sys password. After you log in as sys you can change it and create new passwords for system.

ORA-01940: Cannot drop user that is currently connected

SQL> drop user username cascade;
drop user username cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

Solution 

Make sure the user is logged out, then re-execute the command.

If you are permitted to kill the session of that user then find out the connected user sid and serial# by,
SQL> select sid, serial# from v$session where username = ‘USERNAME’;

 SID    SERIAL#
———- ———-
 268       1268
 315       1223
If RAC use GV$SESSION view to get instance#
NOTE  Before killing session you may wish to lock the account for further connection attempts. This is extremely necessary to drop users who automatically establish session like to drop an application user or to drop a user who performs batch jobs.

SQL> Alter user username account lock;

Now kill the connected session.

SQL> alter system kill session ‘268,1268’;           (use @instance# if RAC db)
       System altered

SQL> alter system kill session ‘315,1223’;
      System altered

And then drop the user.

SQL> drop user username cascade;
 User dropped