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 production database into a test server this type of error may be encountered.So there are the following major reasons why ORA-01033 error occurs:

1. The ORA-01033 error also happens when you have leftover RAM regions held by the OS that makes 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 bellow steps,

 

Step 1

 

Open 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–

select name,con_id from v$pdbs;

 

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

 

 

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

 

Step 4

 

Now, select name of 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 by your own, while installing oracle 12c. In my case it is 'sanghdeep'.
• 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 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 looks as follows. And 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 about connection.

 

 

 

Step 7

 

Now, give the command,

sqlplus / as sysdba

 

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

 

 

 

Step 8

 

To alter the session from container database to pluggable database give the command-

alter session set container=pdborcl;

 

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

 

 

Step 9

 

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

show con_name;

 

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

 

 

Step 10

 

Now check the connection mode, give the following command-

select name,open_mode from v$pdbs;

 

When you hit the ENTER key, It shows connection mode is 'OPEN_MODE' but 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-

alter pluggable database open;

 

Step 12

 

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

select name,open_mode from v$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 as 'READ WRITE'. So we can unlock the HR Schema. To unlock the HR schema use the following command,

alter user HR identified by HR account unlock;

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

 

 

Step 14

 

To connect with the pluggable database using HR schema use the following command-

conn HR/HR @pdborcl;

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

 

 

Step 15

 

show user;

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

 

 

 

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

 

 

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

 

 

Summary

 

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