How to unlock HR user in Oracle 19c

How to unlock HR user in Oracle 19c

Once you have successfully installed Oracle database 19c, its time to unlock the sample schemas so that you can start learning Oracle SQL. In this article, we will see how to unlock HR user in Oracle 19c.

High-level steps to be followed

• Use the command prompt (cmd)
• Get the Container ID of the pluggable database 
• Get the Service name of the pluggable database
• Find the network protocol, host name and port number
• Create a new TNS entry  
• Restart listener
• Process to unlock HR user

Steps:

1. Get the container ID of the pluggable database. In Oracle 19c, all sample databases are available inside the pluggable database. So, we need to get the Container Id for our pluggable database ORCLPDB. We know that our pluggable database name is ORCLPDB because we created it when we installed Oracle 19c. To find the container id, we will query v$pdbs view.
   • Go to your windows Start option and type cmd to invoke the command program.
   • Type sqlplus on the prompt and press enter. An option to enter user-name and password will be displayed. Type user-name as system and password as oracle. You will get an SQL prompt.
   • To set the format of the display, set the column width by using the command.

Now get the container id by executing the statement to query the v$pdbs view

This select statement will return the name and the container id of the available pluggable databases.

The pluggable database ORCLPDB and container id 3 contains all of our sample database.  We know this is correct because we created this pluggable database during installation.

2. Get the Service name, network protocol, hostname and the port number.

• Service name. To find the service name associated with the pluggable database, we need to query the view v$active_services. Execute the following statement.

• We will get the service name as orclpdb.
• Network protocol, hostname and the port number. We can get all this information from the listener.ora file. This file is located in the path \app\db_home\network\admin. You can also search for this file in your windows explorer. Open this file in notepad and go to the following entry :

  LISTENER =

    (DESCRIPTION_LIST =

      (DESCRIPTION =

        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

      )

    )

• Here you can see all the information we need. Protocol – TCP, Host – localhost, port 1521. Note down this information as we will be using this for creating a TNS.

3. Creating a TNS entry

In this step, we will manually add the TNS entry for our pluggable database orclpdb.

• Locate the tnsnames.ora file. It will be located in the same path as the listener.ora file in \app\db_home\network\admin. 
• Open this file in notepad, copy/paste the last entry in this file and modify it with the information we gathered in the previous step from listener.ora i.e add the service name, protocol, host and port values. Your last entry may look like the one shown:

• Copy and paste it at the end of the file.
• Modify the new entry. After the changes, your new entry should look like:

  ORCLPDB =

    (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

      (CONNECT_DATA =

        (SERVER = DEDICATED)

        (SERVICE_NAME = orclpdb)

      )

    )

  Save and close tnsnames.ora file.

4. Restart the Listener

• Minimize or close the previous command (cmd) prompt. In your windows start program, type cmd and open it by selecting run as administrator.  
• Execute the following statement – lsnrctl reload 

Unlocking HR User in Oracle 19c

Now we can start the process to unlock HR user which will give us the access to HR database.

All the sample databases are stored under the pluggable database (in our installation, it is orclpdb) and not in the root container database. By default, oracle connects us to the root container database. In order for us to unlock the sample database, we need to first switch from the root container to the pluggable database.

• On the cmd, connect using sys user with sysdba privileges. Use the command

• Check the container with which we are connected now. Use the command Show con_name. It will show us that we are connected to the root container database CDB$ROOT.

• Use the Alter command to switch from the root container database to the pluggable database.

• Check if the pluggable database is open. Execute the command – Select name, open_mode from v$pdbs; It will display the open mode.

• We are fine, if it shows Open_mode as READ WRITE.
• If it shows Open_mode as mounted, that means that database is not opened and we have to open it with the command – Alter Pluggable database open;

• Unlock the HR user. To unlock the user, execute the Alter command with the user name, password which you want to use and the action to be performed on this user. Use the following command .

• If it displays User altered that means our command is successful and HR user is unlocked.
• Test the user. To check if this user is unlocked, use the following command to connect.

• We can also execute select * from tab; to list all the tables.