{[['
']]}
']]}
Unlock HR user/schema in Oracle Database 12c
In oracle database 12c multitenant architecture we have a container database (CBD) which has at least one Seed pluggable database (PDB) and can have up to 252 user created pluggable databases. Furthermore in oracle database 12c the HR sample schema is placed inside the pluggable database which is created either during the installation of the Oracle Database 12c or during the creation of a new container database.
So let’s see the steps for unlocking the HR schema in oracle Database 12c.
Step 1:
Get the Pluggable database name and service name.HR schema in Oracle 12c is placed in the user pluggable database thus to unlock it we need to get the name of the pluggable database. In order to do so first log on to your database as sys user
C:\> sqlplus / as sysdba
SQL> SHOW con_name;
To find out the name of all the pluggable databases available in your system you can query v$pbds view.
SQL> SELECT name, con_id FROM v$pdbs;Here in this query along with the name of all the available user PDBs I am also selecting their container ID (con_id) because by the help of the corresponding container id of the pluggable database we can easily find its associated net service.
In my case I have 2 pluggable databases available on my system.
- PDB$SEED: First is PDB$SEED which has container id 2. This is our seed PDB which is a default pluggable database (PDB) used as a template for user-created PDBs by the system. The seed is always named PDB$SEED.
- Second is PDBORCL which has container id 3. This pluggable database is the one that was created by me during the installation of oracle database 12c which has our sample HR schema.
SQL> SELECT name FROM v$active_services WHERE con_id = 3;
The result returned from this query in my case is “pdborcl” which is the name of the service for my pluggable database “PDBORCL”. Don’t get confused here as I have already mentioned that in most cases the oracle software creates database and its service with same name.
This pluggable database name and service name will be used for updating tnsname.ora file and configuring some network settings in it by making service entry for the pluggable database.
Step 2:
Update tnsname.ora fileIn the second step we will update tnsname.ora file and make a TNS alias for our pluggable database. In simple words we will just make an entry for our pluggable database in tnsname.ora file so that while establishing a connection listener can find the proper service and can establish connection between client and database.
search in "C:\app" for tnsname.ora fileThis step is required just to connect hassle free with HR user after unlocking it.
First open up your TNS file. This file normally resides in the ORACLE HOME\NETWORK\ADMIN directory.
Remember in my case the name of the pluggable database is “PDBORCL” and the service name is also “pdborcl”. I will use this information to add an entry in my tnsname.ora file.
Before making any changes in the tnsname.ora file I would suggest you to make its duplicate copy as a backup.
PDBORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdborcl)
)
)
A sample entry will look like this. You can change the value for hostname and port number accordingly.
As I mentioned at the starting of this blog that in oracle database 12c the HR sample schema is the part of the pluggable database thus in order to unlock it we need to switch the container from Root CBD to Pluggable PDB (pdborcl).
To switch the container first you need to log on to your database using sys user again
You can use con_name parameter to verify whether you have successfully changed the container or not.
Step3
Switch the container from CBD$ROOT to PDBORCLAs I mentioned at the starting of this blog that in oracle database 12c the HR sample schema is the part of the pluggable database thus in order to unlock it we need to switch the container from Root CBD to Pluggable PDB (pdborcl).
To switch the container first you need to log on to your database using sys user again
C:\> sqlplus / as sysdbaAfter log in as sys you can use “Alter Session” DDL command to switch the container
SQL> ALTER SESSION SET CONTAINER = pdborcl;
SQL> SHOW con_name;
Step 4
Open the pluggable database PDBORCL
As we already know that to perform any DDL such as ALTER USER command, the database needs to be opened and by default pluggable database should be in MOUNT state.
You can check the present open mode of pluggable database using v$pdbs view.
If the pluggable database PDBORCL is in the MOUNT state then you can use ALTER PLUGGABLE DATABASE ddl command to open it.
As we already know that to perform any DDL such as ALTER USER command, the database needs to be opened and by default pluggable database should be in MOUNT state.
You can check the present open mode of pluggable database using v$pdbs view.
SQL> SELECT name, open_mode FROM v$pdbs;
SQL> ALTER PLUGGABLE DATABASE open;
Step 5:
Unlock the HR userNow you are all set to unlock your HR user as we are done with all the necessary settings. To unlock your HR user you have to use ALTER USER DDL command
SQL> ALTER USER hr IDENTIFIED BY hr ACCOUNT unlock;
Step 6
Log in to your database using HR userClick on start menu, Explore the programs directory and locate SQL Developer then click it.
Click the "+" icon under Connections.
The New/Select Database Connection dialog appears. Just fill all fields and Click Test. Remember that the password for hr user was hr. You could save it for re-connection or just connect.
Post a Comment