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

Introduction

Before starting the topic, first I want to explain why this started-up or shutdown error occurs. Sometimes while making the clone of the production database into a test server this type of error may be encountered. So there are the following major reasons why the ORA-01033 error occurs.

1. The ORA-01033 error also happens when you have leftover RAM regions held by the OS that make Oracle think that an instance is already running.
2. The ORA-01033 error also happens when the database is not open.
3. The ORA-01033: ORACLE initialization or shutdown in progress error can also happen when Oracle is attempting startup or shutdown and is "hanging" on a resource such as a failed disk, writing to redo, etc.

To fix this error follow the below steps,

Step 1. Open the command prompt and give the command,

sqlplus / as sysdba

When you hit the ENTER key, it shows it's connected to Oracle 12c.

Step 2. To check the connection name, use the following command,

show con_name;

When you hit the ENTER key, It shows connection CDB$ROOT (container Database Selected from root).

Step 3. Check the schema name and connection ID using the below command.

selectname,con_idfromv$pdbs;

When you hit the ENTER key, it shows the name of the schema object with respect to their IDs.

Note that, in PDB$SEED the ‘SEED’ is a template, used to create a new pluggable database within the container database (CDB).

Step 4. Now, select the name of the pluggable database from active services whose connection id=3, by using the following command.

select name from v$ active_services where con_id=3;

When you hit the ENTER key, It shows the name ‘pdborcl’.

Step 5. Now, close the command prompt and follow the below procedure.

• Open the 'app' folder which is created, while installing the Oracle 12c.
• After opening the 'app' folder open the 'DB user folder'. The 'DB user folder' is that folder, whose name you assigned on your own, while installing Oracle 12c. In my case it is 'Sandeep'.
• After this, you see the same folder. From those folders, open the 'product' folder.
  
• After opening the 'product' folder, open the '12.1.0' folder. It varies from your Oracle DB version.
• After opening the '12.1.0' folder, open the 'dbhome_1' folder.
• The 'dbhome_1' folder contains many different types of folders, from those folders you should open the ' NETWORK ' folder.
  
• After opening the 'NETWORK' folder, open the 'ADMIN' folder.
  
• After opening the 'ADMIN' folder, open the ' tnsnames.ora' file in any text editor.
  

After opening the ' tnsnames.ora' file in the text editor, you see the following code.

# 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)
    )
  )

From the above code copy the last portion of the code i.e.:

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).