TNS and Listener related issues

Oracle TNS issues(tnasnames.ora)

One of the most common error while connecting to Oracle Database is this one. There are plenty of options that needs to be checked and sometime it takes quite a few hours to figure out the exact issue. In my career, I have seen this so many times and each time I have explored new area of this error.

This is mainly due to tns and listener configuration.

There are couple of other error code as well, related to this.

Like : TNS Could not resolve ,ORA-28040: No matching authentication protocol etc..

In this scenario,  we have to look into below possibilities.

•   Need to check $TNS_ADMIN is properly set to folder $ORACLE_HOME/network/admin or $ORACLE_HOME/network/admin/<context_name>
• check tnsnames.ora, listener.ora and sqlnet.ora is configured properly and the content are well formatted
• check lsnrctl status <dbname> is resolving and showing status as active up and running
• check sqlnet.ora file and see the ifile is referring to correct location.
• there are several properties of sqlnet.ora file those needs to be validated.

few example of issue and solution will be added here as well. which will be containing some reference parameters and content of these mentioned files.

Oracle Database User DBA Profile

Working with Oracle DBA Profile

There are several profiles can be created in a database, depending on the business need of any organization. In simple words the default profile has some extended feature which triggers a red light for auditors and it is vulnerable as well. 

Execute the below query to find out the characteristics of any given profile.

select * from dba_profiles where profile='DEFAULT';

It will list up all the resources that are being defined for this profile and its limit. 

Likewise you can create your own profile resource type and limit.

Example:

create profile test_profile_unlimited limit 

SESSIONS_PER_USER UNLIMITED

CPU_PER_SESSION UNLIMITED

  CPU_PER_CALL UNLIMITED

  CONNECT_TIME UNLIMITED

  IDLE_TIME UNLIMITED

  LOGICAL_READS_PER_SESSION UNLIMITED

  LOGICAL_READS_PER_CALL UNLIMITED

  COMPOSITE_LIMIT UNLIMITED

  PRIVATE_SGA UNLIMITED

  FAILED_LOGIN_ATTEMPTS UNLIMITED

  INACTIVE_ACCOUNT_TIME DEFAULT

  PASSWORD_LIFE_TIME UNLIMITED

  PASSWORD_REUSE_TIME UNLIMITED

  PASSWORD_REUSE_MAX UNLIMITED

  PASSWORD_LOCK_TIME UNLIMITED

  PASSWORD_GRACE_TIME UNLIMITED; 

This will create a profile which will be having all the resources set to unlimited.  User having this profile has unlimited attempt for password and user can reuse same password unlimited time. Which is not at all recommended for the system. This could only be used if the user is a service user/ ant AI user / any Bot user . 

If you have any questions regarding user profile please comment or send direct mail. Are you facing any error while creating user profile or assigning any profile to user. Let me know in comment and I will try to reproduce the issue on my test system and fix it. Thanks for your time!!

Create Oracle Database User

Oracle Database User Management

This is the simplest task for a Database Administrator. 

Login to sqlplus session from your OS(Windows/Linux/AIX) using sysdba / a user who has create user privilege. 

sqlplus / as sysdba

alter user <username> identified by <password>;

this will create a user with default tablespace and profile. Depending on the organization's policy and user's requirement you might need to mention few other options in the query. Those are advanced options you can explore more on my advance create user query.

Now user needs some privileges to connect to the database and do some transactions or query any tables.

CONNECT - this is the Oracle-defined default privilege for any user to be able to connect to the database.

grant connect to <username>;

alternatively, grant create session to <username>;  will do the same.

Now, for the user to be able to query any table in the db, one basic privilege is required.

grant select any table to <username>;

This will create a db user in Oracle. 

To see the user is created properly you can validate with below query.

select * from dba_users where username='<username>';

The dba_users is a data dictionary view which eventually being created from all_users. To know more about all_users and dba_users column properties stay tuned!!

Best Wishes!!