How To Unlock HR Schema In Oracle-12C Or How To Fix The ORA - 01033 Error

sqlplus / as sysdba

show con_name;

selectname,con_idfromv$pdbs;

select name from v$ active_services where con_id=3;

# tnsnames.ora Network Configuration File: E:\app\sanghdeep\product\12.1.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

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

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

And paste it at the end of the whole code. Then, make the change into it as follows. After making the following changes save it.

PDBORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdborcl)
    )
  )

Finally, ensure that your entire code will look as follows. Close the 'tnsnames.ora' file and also close the 'app' folder.

# tnsnames.ora Network Configuration File: E:\app\sanghdeep\product\12.1.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

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

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

PDBORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdborcl)
    )
  )

Step 6. Open the command prompt as 'administrator' and give the following command.

lsnrctl reload

When you hit the ENTER key, it shows a description of the connection.

Step 7. Now, give the command,

sqlplus/assysdba

When you hit the ENTER key, it shows a description of the connection.

Step 8. To alter the session from a container database to a pluggable database give the command.

altersessionsetcontainer=pdborcl;

When you hit the ENTER key, it shows the session altered.

Step 9. To check if the session is altered from CDB$ROOT to PDBORCL use the following command.

showcon_name;

When you hit the ENTER key, It shows the connection name as 'PDBORCL'.

Step 10. Now check the connection mode, and give the following command.

selectname,open_modefromv$pdbs;

When you hit the ENTER key, It shows the connection mode is 'OPEN_MODE' but the pluggable database is 'MOUNTED'

Step 11. Now, alter the pluggable database to change the open mode from 'MOUNTED' to 'READ WRITE', by using the command.

alterpluggabledatabaseopen;

Step 12. To ensure the 'OPEN_MODE' of the pluggable database is changed from 'MOUNTED' to 'READ WRITE', once again use the command.

selectname,open_modefromv$pdbs;

When you hit the Enter key, 'OPEN_MODE' is 'READ WRITE'.

Step 13. Now the pluggable database is altered and the open mode is also changed to 'READ WRITE'. So we can unlock the HR Schema. To unlock the HR schema use the following command.

alteruserHRidentifiedbyHRaccountunlock;

When you hit the Enter key, the HR schema will unlock and it shows the user altered.

Step 14. To connect with the pluggable database using HR schema use the following command.

connHR/HR@pdborcl;

When you hit the Enter key, it shows as connected.

showuser;

When you hit the Enter key, it shows USER is “HR”.

In this way, we successfully fix the ORA-01033 error, to unlock the HR schema object. Now we can connect with a pluggable database using HR users. Open the SQL developer tool enter username as 'HR' and password is also 'HR' and click on the 'OK' button.

Now you see that, the ORA-01033 error is successfully fixed and we successfully connected with the pluggable database using the HR schema object/HR user.

Summary

In this article, we learned about how to fix the start-up or shutdown error (ORA - 01033 error).