How to unlock HR schema? [XE]

w dniu


Unlocking sample HR scheme in versions older than Oracle 12c was very easy. Just execute from the SYS user:
 alter user hr identified by hr account unlock;
And done! - HR scheme ready to go.
In 12c, when the containers came in, things got complicated:
 alter user hr identified by hr account unlock;  

 Error report -  
 ORA-01918: user 'HR' does not exist
 01918. 00000 - "user '%s' does not exist"  
*Cause:  User does not exist in the system.  
*Action:  Verify the user name is correct.
But I know, that HR is out there somewhere...

When we log as SYS user, we log into the CDB database. And there is actually no HR schema. The HR schema has been hidden in the pluggable database. To be able to unblock the HR user, we need to connect to the PDB (pluggable) base.

How to do that?

1. Find PDB name
as a SYS user execute the SQL
 SELECT name, con_id, open_mode FROM v$pdbs;

Out PDB base is XEPDB1.

2. Update tnsnames.ora file

We need to add our PDB database into the tnsnames

We can find TNSNAMES file in the folder:

<ORA_HOME> product\18.0.0\dbhomeXE\network\admin

example:

C:\app\user\product\18.0.0\dbhomeXE\network\admin

We add the connetion to PDB database (enter the name of your database instead of XEPDB1):

  XEPDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = admin)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XEPDB1)
    )
  )

 3. Resart the listener.

4. Open XEPDB1
We check with the query from point 1 whether our database is in the READ WRITE mode. If not, we can open it with the command:
 ALTER PLUGGABLE DATABASE ALL OPEN;
5. Connect to container XEPDB1
As a SYS connect to XEPDB1
 ALTER SESSION SET container=XEPDB1;
We can reconnect to root usind statement:
 ALTER SESSION SET container=CDB$ROOT;
but let's not do that for now, we need to be PDB based.

6. Unlock HR schema
And finally we can unlock our sample HR scheme!
 alter user hr identified by hr account unlock;
 User HR altered.
7. Connect to HR.
We need to create a connetion to the HR schema. Remember that the database is not on the standard SID, for me it is XE, only on the SID that we created in point 2. So for me it is XEPDB1.


And now we can enjoy our beautiful, new HR scheme :)

Komentarze

Prześlij komentarz