Easy Connect Method to Connecting with Oracle

Back to: Oracle DBA Tutorials

Easy Connect Method to connecting with Oracle Database

In this article, I am going to discuss Easy Connect Method to connecting with Oracle Database. Please read our previous article where we discussed Starting and Stopping Listeners in Oracle.

Easy Connect Method to connecting with Oracle

In the previous article, we have learnt about how to start and stop the listener and learnt about the status of the listener. While connecting to the database we have connected to the database as “sqlplus / as sysdba” using OS authentication.

There is an important note for this method of logging as sysdba.

1. The sys user need to login as the same machine or same server as the oracle database exists.
2. The oracle os user needs to present in the group called dba group.

Now, let us learn about the easy connect method to the database.

Step 1: Connect to the database as “sqlplus / as sysdba”

Step 2: Disconnect to the database.

Step 3: This is the easy connect method.

The easy connect is a simple connection type to the database which helps us to connect to the database a little bit easier and helps us to understand the requirements for an oracle user who is connecting to the oracle database. The basic syntax of easy connect is as follows

Connect sys/goodluck@oracletest.com:1521/jayadb.com

You can see there is an error. This error indicates the role is not mentioned during login. Use sysdba or sysoper role to login to the database with sys user. Try to connect again. Use the same syntax which little changes

Connect sys/goodluck@oracletest.com:1521/jayadb.com as sysdba

There is a basic syntax for easy connect method. The Syntax for the easy connect method is as follows Connect username/password@hostname:portnumber/service_name. Remember these are the parameters that we use to login from the SQL developer. We provide username, password, hostname, port number and SID or service name.

Step 4: Checking Connection with SQL Developer

Open the SQL developer and make a new connection and enter the username as sys, password for the sys user and select the role as SYSDBA.

Enter the hostname as oracletest.com and port number as 1521 and provide the service name as jayadb.com.

Click on Test and it shows success. So, the connection to the database is success.

Note: These types of connections require the listener to be up and running fine, because these connections depend up on hostname, port number and the service name. So, in this method you need to start up the listener. In our case the listener is up and running fine.

Step 5: Easy connect to pluggable database.

The service name of the pluggable database is jayapdb.com. To make sure the service name we will verify the service name with the listener. Previously, if we need to check the services, we use the command lsnrctl status which displays all the services which are running. There is another method to check the services. Run the command lsnrctl and press enter. It opens up the console of the lsnrctl. This is called the listener utility.

Enter the command help. It displays all the other commands that we can use with the listener. We can use the command start to start the listener, stop to stop the listener, status to check the status of the listener, services to check the services running on the listener and version to check the version of the listener.

Let’s check with the command services. Enter the command services. There we go all the services running are listed here.

Now let’s try to connect with the pluggable database. Connect to database as “sqlplus / as sysdba” and then disconnect.

After disconnecting now try using the easy method to connect to the pluggable database.

Connect sys/goodluck@oracletest.com:1521/jayapdb.com as sysdba and press enter.

Note: The easy connect needs listener up and running.

Step 6: Let’s try to stop the listener and try to connect again using the easy method.

Stop the listener using the command lsnrctl stop.

Login to the database using “sqlplus / as sysdba”. The sysdba user can login to the database even if the listener is down. It is using the OS authentication method. This method doesn’t require the listener.

So, the listener is down. Try to connect to the pluggable database using the easy connect.

The error is TNS: no listener. This method requires the listener to be up and running.

Step 7: Connect to the database after starting the listener.

Remember the LREG background process require 60 seconds to register all the services to the listener. There is another method to register all the services to the listener.

Connect to the database using “Sqlplus /as sysdba”. After connecting to the database Run the statement “alter system register”. This method forces to register all the services to the listener.

After this disconnect and check the listener status. We can see all the services are up and running.

Connect to the database with easy connect to check whether the easy connect method is up and running or not.

So, we have learnt about how we can connect to the database and pluggable database with multiple options and with listener on and off.

In the next article, I am going to discuss Connecting to Oracle using tnsnames.ora. Here, in this article, I try to explain Easy Connect Method to connecting with Oracle Database and I hope you enjoy this Easy Connect Method to connecting with Oracle Database article.