I know, unlocking sample schema like HR in
Oracle Database 19c is not as simple as it used to be in Oracle Database 11g or 12c. It may seem daunting to you now. But I promise after this video your opinion
will be something different. What’s up internet! I am Manish from RebellionRider.com and today
in this video I will show you, how to unlock sample HR user in Oracle Database 19c. But, before we start the tutorial, I just
want to ask you to subscribe to the channel if you haven’t already. Also, press the bell icon to get notified
whenever I upload such awesome tutorials. That being said, let’s start –
If you try to unlock the sample schema like the way we used to do in Oracle 10g or 11g
you will get an error. Let me show you –
Sqlplus / as sysdba Right now, we are connected to our database
using the sys user. Let’s try to unlock the HR user. ALTER USER hr IDENTIFIED BY hr ACCOUNT unlock;
See as soon as I executed this ALTER USER statement, I got an error. This error is “user HR does not exist”. Now you must be wondering – does that mean
we don’t have sample schemas in Oracle Database 19c? Hold your thoughts – it’s nothing like
that. HR sample user is still here. It’s just that we are searching for it in
the wrong place. In the multi-tenant architecture of Oracle
Database 19c, all the sample schemas are placed inside the default pluggable database not
in the root container. With which we are connected right now. So, now you ask – what should we do to unlock
the sample schemas? And the answer to your question is – simply
switch from container database to the pluggable database. Simple, right? Wait, there is a process of doing that. There are a few configurations which we need
to do before switching to the default pluggable database that has all our sample schemas. Let’s quickly do that –
In order to unlock the sample schemas in Oracle Database 19c we need to create a TNS entry
and for that we need some information, such as the container ID and the service name of
the pluggable database. Let’s find out this information. Step 1: Find out the container ID
To find out the container id of the pluggable database we need to query the v$pdbs view. Like this –
COLUMN name FORMAT a20; SELECT name, con_id FROM v$pdbs;
This select statement will return the name and the container id of all the available
pluggable databases. Let’s execute it –
As a result, this statement has returned two names and their container ids. Now the question here is which one of these
pluggable container of Oracle Database 19c has our sample schemas? The pluggable database with the name ORCLPDB
and container id 3 has all our sample schemas. So, you asked, how do you know that? ORCLPDB is the only pluggable database which
I created during the installation of Oracle Database 19c. This I have also demonstrated in the previous
video. And, now you asked, Manish, if ORCLPDB is
the only pluggable database you have created then what is this PDB$SEED. As the name suggests, PDB$SEED is the seed
pluggable database created by Oracle Universal installer during the installation. This seed pluggable database is used as a
template for creating the user defined pluggable databases. I suggest you not to mess with it. Anyways—
Now that we know the name and container id of the pluggable database which has our HR
user. Let’s use it to find out the service name
associated with it. In almost all the cases the database and its
service share the same name. But I suggest you check it and get assured. To find out the service name associated with
our pluggable database we need to query the v$active_services view. SELECT name as “Service Name” FROM v$active_services
WHERE con_id =3; This statement will return the name of the
service associated with the pluggable database whose container id is 3. Which we know is the pluggable database orclpdb. Let’s run it and check
The service name of our pluggable database ORCLPDB is also orclpdb. Fine. Along with the service name we will also need
to find out the network protocol, hostname and port number. We can find that information from listener.ora
file. Let’s open this file and get the information. You can find this listener.ora file at the
location which is flashing on your screen. Let’s get to this location and open this
file. Here is our listener.ora file. Let’s open it. Now come to this connect descriptor with the
name listener. Here you can see all the information that
we need. • Protocol – TCP
• Host – localhost • Port – 1521
This entry could be different in your case so don’t panic. Anyways, write this information down somewhere
as we will be using it for creating a TNS alias. Now we have all the information we need to
create a TNS entry. So, let’s move ahead and do it –
See there are two ways of creating a TNS entry in Oracle Database –
One is using Net Configuration Assistant. A free tool given by Oracle for configuring
the network settings of your Database Server. Second is by manually adding the entry into
the tnsname.ora file. The first one is always recommended but to
save time I will go with the second one. Which means I will be adding a TNS service
manually into the tnsnames.ora file for our pluggable database orclpdb. Also, I have already demonstrated how to create
a TNS entry using Net Configuration Assistant in the last video. You can check that out. Link is on your screen as well as in the description
of this video. Anyways, let’s move ahead
In oracle database all the TNS services are stored into a file with the name tnsnames.ora. To create a tns alias for our pluggable database
orclpdb we need to access it. You can find this file at the same location
as that of your listener.ora file. Let’s open this file
Fine, now you must be wondering, Manish what’s the plan. The plan here is to copy an already created
TNS entry and modify it with all the information which we just gathered. So, copy this TNS entry ORCL. Now paste it at the end of this file. Done! Now we have to modify this TNS service with
the information which we have gathered. That is
• Host name – localhost • Port Number – 1521
• Network Protocol – TCP • Service name – orclpdb
We will use this information to modify our TNS service. Let’s do that –
Let’s start with the name. You can name your TNS service whatever you
want. But it’s always advisable to give it some
logical name. In most cases we name our tns entry on the
name of the tns service for which we are creating this entry. In our case that name is ORCLPDB. Keeping the naming conventions in mind I have
named the TNS entry in all capital. And I suggest you do the same. Next is address protocol. Which is the combination of Network Protocol,
hostname and port number. In this entry, Protocol is set on TCP and
hostname on localhost and port number is set on 1521. In my case all these values are the same as
the one which I gathered from our listener.ora file. Thus, I will not change them. If this is not the case with you then replace
the value in this entry with that of listener.ora file. The next thing which we have to update is
service name. Here, change the service name from orcl to
orclpdb. That’s it. That’s all we have to do. For those who want to master the concept of
Oracle TNS, I have created a package comprising of a Video, a blog and a Free PDF. I will leave the link in the description. So go ahead and check that out. You can thank me later. That being said now let’s come back to our
topic. But first let’s close this file and minimize
this window also, minimize this command prompt. Great! Ok,
Next, we have to restart the listener and for that
Open up the command prompt with administrative privileges – Let me fix the size. Great
Now write lsnrctl reload
The command completed successfully. That’s all you have to do. All the necessary settings have been done. Now we can start the process of unlocking
the HR user of Oracle Database 19c. One thing which is very clear to us is that
in multi-tenant architecture the HR user is stored inside the default pluggable database
not in the root container database. In our case the default pluggable database
is orclpdb. And, by default we always get connected to
the root container database. Which means to unlock our sample user we first
need to switch from root container to our pluggable database. Let’s do that –
let me first clear the screen of this command prompt. Ops
Let me also adjust the size of this command prompt once again. Great! Now let me connect to my database. Make sure you connect with your database using
sys user with sysdba privileges. To do that simply write –
Sqplus / as sysdba Sys is an external user thus this statement
will get you connected with your database using sys user. Fine, we are connected now! Let’s check the container with which we
are connected to our database right now. Show con_name
So right now, we are connected with CDB$ROOT which is the root container database. Now we need to switch from container CDB to
Pluggable PDB. Let’s do that –
ALTER SESSION SET container = ORCLPDB; Where ORCLPDB is the name of the pluggable
database. To unlock the sample user in Oracle 19c we
need to execute an ALTER DDL statement. We can do that only if the pluggable database
orclpdb is open. So let’s check if it is open or not. SELECT name, open_mode FROM v$pdbs;
So pluggable database ORCLPDB is not open. Let’s quickly open it. ALTER PLUGGABLE DATABASE open;
Great, now our pluggable database orclpdb is open. Which means we are all set to execute alter
user command to unlock the sample user in Oracle database 19c. Let’s do that. ALTER USER
Now we have to write the name of the sample user which we want to unlock. Which in our case is HR. IDENTIFIED BY
Now we have to specify the password for our user hr. In my case I will set the password for my
HR user as HR. For the demonstration that will work. Plus it’s easier to remember. hr
Next we have to tell the Oracle engine what we want to do with this user account. ACCOUNT UNLOCK;
This phrase will tell the oracle engine that we want to unlock the HR user account. On execution of this statement the sample
user HR will be unlocked. Let’s do that. Account unlocked. Now let’s try to connect with HR user. Conn hr/hr@orclpdb
Connection established. Let’s check
Show user HR perfect. So that’s how we unlock Hr user in Oracle
Database 19c. Hope you found this video helpful. If so, then please hit the thumbs up button
and share this video on your social media. Most importantly subscribe to the channel. See you next week. Thanks for watching. This is Manish from RebellionRider.com.
