How to unlock HR sample user in Oracle Database 18c by Manish Sharma

How to unlock HR sample user in Oracle Database 18c by Manish Sharma


What’s up Internet? I am Manish from RebellionRider.com and I
am back with another Oracle Database 18c tutorial. Here we will learn how to unlock schemas or
say users in Oracle Database 18c. Since the starting of multi-tenant architecture
with Oracle Database 12c, all the high privileged users like sys and system were placed inside
the container database and other schemas like HR & OE were moved to the pluggable database,
which you have created during your installation. So in simple words Oracle is still shipping
those sample schemas as part of the package Before we jump onto this tutorial, I just
want to tell you two things. First, in this video we will be using the
same settings that we did in the previous video while installing the 18c. So I would suggest you to go ahead and watch
the installation video first. Second, for the demonstration I will unlock
the HR schema but you can follow these same steps to unlock any other sample schema that
you want. Now that I have informed you let’s not waste
any time and start the tutorial. Step 1: Find out the container ID. To unlock the user we need find out the database
service corresponding to our pluggable database which is containing our sample schemas, for
that we require the container ID of that pluggable database. Let’s quickly find out that container ID. First connect to your database using sys user. Sqlplus / as sysdba
For those who don’t know how to do that, let me quickly tell you. First write SQLPLUS followed by forward / then
the keyword as and the role sysdba, then hit enter. You will be connected to your database with
the sys user. Great
Earlier I told you that in multi-tenant architecture all the high privileged users like sys are
placed inside the container database, and other users like HR and OE were moved to the
default pluggable database. Now the question is how do we get to know
whether we are connected to a container database or a pluggable database? Although I was intent on finding out the container
ID but with such good question you guys have managed to distract me. Great, now let me answer this question. To know whether you are connected to a container
database or a pluggable database, you can query a property known as con_name. Like this. SHOW con_name;
As you can see right now I am connected to CDB$ROOT. The first three initials show the type of
database you are connected with. CDB implies Container Database and PDB implies
Pluggable Database Having answered that now let’s come back
to our topic. To find out the container id you can query
the v$PDBS dynamic performance view. Let’s see how. But first let’s clear the screen and set
the format for the column so that the output will be more readable. Cl scr
Screen cleared now let’s set the format COLUMN name FORMAT a20;
Format set Great, now let’s find out the container
ID. SELECT name, con_id FROM v$pdbs;
The output of this SQL query will show us the name and the container id of all the pluggable
databases created inside the CBD$ROOT container with which we are currently connected. Let’s check the output. As you can see, here we got two pluggable
databases. PDB$seed with container ID 2 and ORCLPDB with
container ID 3. Among these two, ORCLPDB is the same one which
we created during the installation. PDB$SEED is the seed pluggable database & a
system supplied template which a container database uses for creating user defined pluggable
databases. I suggest you not to tamper with this. Taking that in knowledge, ORCLPDB will become
our pluggable database of interest. So now we have the name and container id of
our pluggable database which has all our sample users. With this step 1 is complete. Now let’s jump to the Step 2. STEP 2: Find out the service name. Now using the container ID of ORCLPDB pluggable
database we will find out its service name. Usually, pluggable database and its service
shares the same name, but its good practice to check it beforehand. Let’s do that
To find out the service name corresponding to our pluggable database we can query v$active_services
dynamic performance view. Let me show you how. SELECT
name as “Service Name” FROM v$active_services
WHERE con_id=3; Using this query we are retrieving the service
name corresponding to container ID 3 which is the container ID for our pluggable database
ORCLPDB. Let’s see the output. So both our pluggable database and its corresponding
service shares the same name. Great. Now we know the name of our pluggable database,
its container ID and its corresponding database service name. Now let’s jump to the next step. Step 3: Create an entry in tnsnames.ora file. Now using the name of our pluggable database
and its corresponding service we need to create an entry for our listener. There are two ways of doing that, we can either
use NETCA utility or we can do it manually. I’ll go with the latter option as it is
less time consuming. That means we need to find the tnsnames.ora
file first. Let’s search it. Tnsnames.ora is a network configuration file
thus you will find it inside the “Network” folder which in turn is located inside our
DB_HOME. So let’s go and get our file. My DB_HOME folder is inside my D: drive, yours could be somewhere else. Here is my DB_HOME let’s get into it. Now we have to find a folder with name “Network”. Here it is. Let’s open it. Fine now you have to go to this “Admin”
folder. Great. Here is our tnsnames.or file. Open it with an editor of your choice. I’m using notepad++
First of all, chances are there, that the content of your tnsnames.ora file may look
different than mine, so don’t panic. You can check the corresponding blog to find
the entry which we are going to create now. I will also upload this tnsnames.ora file
on my GIT repository. If you want you can download it from there. All the links are in the description. That being said, let’s make the changes
here. To save time, we are going to copy this ORCL
block and paste it at the end of this same file and make the necessary modifications. Let’s do that. Copy done, now let’s go to the end of the
file and paste it. Done. Now change the name of this entry from ORCL
to ORCLPDB. Which is the name of our pluggable database. Make sure to keep the name in all caps. Great
Now, come to the section where it says SERICE_NAME=ORCL. Here change the service name ORCL with ORCLPDB
the name of our service which we just found out. This time in small letters. Done. Now save the file and close it. And now open a fresh command prompt with administrative
privileges. Let me fix it… looking great. We just added a new service to our network
configuration file, it is not activated yet. To bring these new changes into action we
need to restart the listener. We can do that just by writing
Lsnrctl reload Great, the command completed successfully. Which means we have successfully added a network
TNS service entry and it is activated now. With this the step number 3 is completed. Now let’s move to the next step. Step 4: Switch from Container database to
Pluggable database. First let’s close all these windows and
open a fresh command prompt. As all other users are locked so we have no
choice but to connect to the database using sys user. Sqlplus / as sysdba
Great. So far, one thing which is very clear to all
of us is that all the high privileged users like sys are placed inside a container database. This means whenever we connect to our database
using any of this high privileged user the oracle server automatically connects us to
their native container database. But, we also know that all our sample schemas
are placed inside the pluggable database, which means to unlock them we need to switch
from the container database with which we are connected right now to the pluggable database. Let’s do that
ALTER SESSION SET container=ORCLPDB; To switch your database you need to write
an ALTER SESSION DDL query like this. Here you assign the name of your database
to the container field. In our case the name of that database is ORCLPDB. Hit enter, Session altered. Before we do anything let’s quickly check
whether we have switched from CBD$ROOT container database to ORCLPDB pluggable database or
not? And we know how to do that, don’t we? SHOW con_name;
This output confirms that we are right now connected to our desirable pluggable database
which is ORCLPDB. That being done let’s move to the final
step. Step 5: Unlock the HR user
Again for this demonstration I am unlocking the HR user, whereas you can use these steps
for unlocking any other sample user you want. Fine
In order to perform any DDL or DML the database needs to be in open mode. Chances are that the pluggable database over
which you just switched may not be opened or in mount state. In any case there is no harm in checking. I promise a sweet simple SELECT statement
and nothing else. We will again write a query on v$pdbs view
but this time we will be selecting the value from the column open mode along with the name
of the currently connected database. Let’s do that, I guess we should set the
format for our column first so that our output will be more readable. Column name FORMAT a20
Good, now let’s write the query. SELECT name, open_mode FROM v$pdbs;
This select statement will show you the open mode along with the name of the database which
you are currently connected to. Let’s see the output. This output shows that our database is in
mount state, now we need to open it and for that we need to write an ALTER pluggable database
DDL, Let me show you how. ALTER PLUGGABLE DATABASE open;
Pluggable database altered. Ok, let’s once again run the previous SELECT
statement and confirm the open mode of our database. Now the open mode for ORCLPDB is “Read Write”
which is perfect. Now let’s write the final ALTER DDL which
will unlock the HR schema. Before that let’s clear the screen. Done. ALTER USER hr
Here write the name of the user you want to unlock. In my case it will be the HR user, if you
want to unlock any other user write its name here. IDENTIFIED BY hr
Here write the password you want for your user account. I will set hr as the password for the HR user. You can set whatever you want. Next write
ACCOUNT UNLOCK; This parameter will set the status of the
HR user to unlock. Hit enter, User alter. Here you have your HR account unlocked. That’s all we have to do. Make sure to read the corresponding blog to
learn the proper way of connecting with your database using the sample schema which you
just unlocked. Link is in the description. That’s it for this tutorial. Hope you enjoyed watching and learnt something
new. Please give this video a thumbs up and share
it on your social media. Subscribe to the channel if you haven’t
already. That’s the tutorial on how to unlock HR
user in Oracle Database 18c. Thanks for watching this is Manish from RebellionRider.com.

Danny Hutson

36 thoughts on “How to unlock HR sample user in Oracle Database 18c by Manish Sharma

  1. thanks for making this video …..
    Sir, I need to connect local database (my laptop) by using MobaXterm, could you please tell me how to do this…

  2. Timestamps
    ( 01:12 ) Step 1: Find out the container ID
    ( 02:10 ) How do we get to know whether we are connected to a CDB or PDB
    ( 04:41 ) Step 2: Find out the service name
    ( 05:49 ) Step 3: Create an entry in TNSNAMES.ORA
    ( 09:13 ) Step 4: Switch from Container DB to Pluggable DB
    ( 11:04 ) Step 5: Unlock the HR user

  3. everything is okay ,, now how can i connect it from jdeveloper ..they tell me"Test failed: ORA-01017: invalid username/password; logon denied"

  4. i have a problem with the installation
    fail to add user/user to %2% group
    and then the installation stuck on 52% creating and starting oracle instance
    wish answering fast

  5. manish, make a video for unlocking scott schema in oracle 18c, please
    I am not able to unlock scott in 18c
    it shows user scott does not exist

  6. hi after doing all the steps still getting "ORA-12154: TNS:could not resolve the connect identifier specified" while connecting to hr db

  7. Hi, Sir i am unable to run 18c set up file in windows 7 64 bit, after running set up file it is disappearing the command prompt, and no any error or log file is generated, even i checked the regedit , firewall and antivirus as well can you please help me

  8. Hello Sir, i am facing a problem in step 3,
    Lisntener is not working. I have already edited the tnsnames.ora file according to you but the following ERROR occurs and we are unable to find out your TNSNAMES.Ora file at GIT. So, what to do now please suggest.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1539)))
    TNS-12541: TNS:no listener
    TNS-12560: TNS:protocol adapter error
    TNS-00511: No listener
    64-bit Windows Error: 61: Unknown error
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
    TNS-12541: TNS:no listener
    TNS-12560: TNS:protocol adapter error
    TNS-00511: No listener
    64-bit Windows Error: 2: No such file or directory

  9. thanks pal, help us on Linux , Oracle 18c XE installed on oracle-linux-7.6 , how to unlock hr sample there. Thanks

  10. The video helped me twice.First was while installing it. The pluggable database was shut down unexpectedly.At last was able to change to read-write mode…pheewww

  11. Awesome!!!! i was trying to follow some tutorials written in the oracle 11g era, that used the HR database that came with oracle xe. Needless to say, I got completely stuck in 18c. But your video saved the day, thanks a lot !!!

  12. Dear. I perform all the steps as is, without having any problem. The problem is when I turn off or reject my pc, I have to re-unlock the database, because it returns as it was before. It does not save the unlocking changes.

    Could you help me?

  13. thanks for the video Manish, i was able to unlock HR, but when i try to unlock OE and PM i get error that user does not exist. looks like i have to install from https://github.com/oracle/db-sample-schemas/releases/tag/v18c . Any videos on installing the github sample schemas,? i don't understand the readme that comes with the samples

  14. Hello Manish! Thank you for your tutorial. Have you any idea what should I do if I face the following problem:

    SQL> ALTER PLUGGABLE DATABASE open;

    ALTER PLUGGABLE DATABASE open

    *

    ERROR at line 1:

    ORA-00604: error occurred at recursive SQL level 1

    ORA-01405: fetched column value is NULL

    Thank you,
    Anna

Leave a Reply

Your email address will not be published. Required fields are marked *