Are you still getting the “ORA-01017 Invalid Username/password; logon denied” error even after successfully unlocking the schema? What can be the probable cause of this error? Are we doing something wrong or is there some sort of bug in Oracle Database 18c? Let’s find out.
Before we start here I would suggest you to go through the previous tutorials and configure all the settings as shown there because those will be required in this tutorial.
In this tutorial we will learn how to connect to your Oracle Database 18c with a Sample User/Schema using a free tool – SQL*Plus.
What’s the cause of “ORA-01017 Invalid Username/password; logon denied” error?
Whenever we write a connection string in Oracle 11g style, (like SQLPLUS hr/hr) the server starts searching the user, with which we are trying to connect to the database, inside the container database.
But as we learnt in the previous tutorials that in multi-tenant architecture only the high privileged users like sys and system are placed inside the container database and other sample schemas such as HR and OE are moved to the default pluggable database, which we created during the installation. Hence we got this “Invalid Username and Password” Error.
So, how can we solve this error?
We can solve this error just by telling the server where to find this HR user before it starts searching for it in the container database. We can do that by specifying the corresponding service name of the pluggable database into which our user is placed along with the username and password in the connection string.
How to specify the service name of the pluggable database in connection string?
You can specify the corresponding service name of the pluggable database where your user, through which you want to connect to the database, is placed in two ways. They are:
- The basic way and
- Using TNS alias
Let’s learn each of these one at a time.
Info: For the demonstration I will use the “HR” user to connect to my Oracle Database, but you can use whatever user you want. The process will remain the same.
HR is a sample schema which in multi-tenant architecture is placed inside the default pluggable database whose name is ORCLPDB. The corresponding service name for our Pluggable Database “ORCLPDB” is also “orclpdb”.
The Basic Way
It is nothing but a tedious way of specifying the name of service in the connection string. Here we specify the URL path to the corresponding service of our pluggable database. The URL path consists of a hostname where our operating system is hosting the Oracle Server along with the port number. The default hostname and port number for Oracle Database are “localhost” and “1521”. Usually the default URL will look something like this
//localhost:1521/orclpdb
Where tailing “orclpdb” is the service name.
Chances are that in your case the URL may look different than the one shown above. In such case please check your hostname and port number in “listener.ora”. You can find “listener.ora” file at %DB_Home%\network\admin\listener.ora location
listener.ora file location
%DB_Home%\network\admin\listener.ora
To find out the corresponding service name please refer to the Step 2 of the last tutorial.
Syntax
Once you’ve figured out the URL location path of Pluggable Database’s service then you append it at the end of your connection string. The syntax for doing that will look like this
SQLPLUS username/password@URL-path
Example
Let’s say you want to connect to your Oracle Database 18c using HR user then your connection string will look something like this
C:\>SQLPLUS hr/hr@\\localhost:1521\orclpdb
Make sure there is no blank space before the @ sign.
This is the basic method for connecting to your database in Oracle 18c. It is tedious because you have to remember this entire URL as you will have to write this every time you want to logon to your database. Now let’s see the second method which is by using TNS alias.
Using TNS alias.
Remembering the entire URL is a bit too much especially when we have to logon to our database multiple times in a day. But there is good news! We can completely minimize this hustle of writing this cumbersome weird looking URL with the name of your pluggable database.
But it’s possible only when you have created a valid TNS entry in your TNSNAME.ORA file. I have shown the entire process of “How to create a TNS entry in TNSNAMS.ORA file” in the last tutorial. You can Read that blog here or if you want you can also watch the tutorial on YouTube here.
In the last tutorial we saw that the Pluggable database and its corresponding service shares the same name. Using that name along with the valid hostname and port number we created an entry in TNSNAMES.ORA file. We named this entry after the name of our pluggable database which is ORCLPDB.
Syntax
SQLPLUS username/password@TNS_alias
The syntax is pretty simple, except the ending there is no change in the connection string. The URL path of the service will get replaced by a simple name of the TNS entry also known as the TNS alias.
Example
Once again let’s say you want to connect to your database using HR user in Oracle 18c then the connection string will look something like this.
C:\>SQLPLUS hr/hr@ORCLPDB
On pressing the enter key the server will connect you to your Oracle Database 18c using the HR schema.
How to confirm the USERNAME with which we are connected to our database?
To see the username through which you are connected to your Oracle Database 18c, or for that matter any version of Oracle Database you can query the “USER” property. This property will show you the username using which you are currently connected to your database.
SHOW user;
You can use either of the above demonstrated methods to connect to your database using any user you want. The user could either be a sample schema like HR or a user defined user. Just remember to add the service name of the pluggable database into which your user is created or placed at the end of the connection string.
That is how we connect to our Oracle Database 18c through a Sample User/Schema using a free tool SQL*Plus. Stay tuned as in the next tutorial we will learn how to connect to the Oracle 18c using SQL Developer GUI.
Thanks and have a great day!
