ORA-03114: Not connect to Oracle while startup solutions

Recently, one of my Oracle databases can start mount but unable to open.

sql> startup
...
Total System Global Area                              7629732 bytes Fixed Size                                              60324 bytes Variable Size                                         6627328 bytes Database Buffers                                       409600 bytes Redo Buffers                                           532480 bytes 
...
ORA-03114: Not connect to Oracle

The ORA-03114 is an error message triggered when a user attempts a call to Oracle when no connection was established. Here is my solution:

Step 1: First of all check your listener

$ lsnrctl status
 LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 09-SEP-2019 12:37:10
 Copyright (c) 1991, 2009, Oracle.  All rights reserved.
 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
 STATUS of the LISTENER
...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.10.84)(PORT=1521)))
 Services Summary…
 Service "DB84" has 1 instance(s).
   Instance "DB84", status READY, has 1 handler(s) for this service…
 Service "DB84XDB" has 1 instance(s).
   Instance "DB84", status READY, has 1 handler(s) for this service…
 The command completed successfully

The output above means everything goes well, then next step.

Step 2: Check Disk Space

df -h
 Filesystem                   Size  Used Avail Use% Mounted on
 devtmpfs                     2.0G     0  2.0G   0% /dev
 tmpfs                        2.0G  1.1G  939M  53% /dev/shm
 tmpfs                        2.0G  9.0M  2.0G   1% /run
 tmpfs                        2.0G     0  2.0G   0% /sys/fs/cgroup
 /dev/mapper/ol_db84nix-root   96G   96G   10M  100% /
 /dev/sdb1                     50G   47G   27M 100% /backup
 /dev/sda1                   1014M  276M  739M  28% /boot
 tmpfs                        396M     0  396M   0% /run/user/54321
 tmpfs                        396M   12K  396M   1% /run/user/42
 tmpfs                        396M     0  396M   0% /run/user/0

In this step, we see the disk space is empty which makes Oracle unable to open. But I am pretty sure my data can not fill up 100% of disk space. So it should be any log file. We have to find them out.

Step 3: Check the log size

First we’ll need to check the listener log at default: $ORACLE_BASE/diag/tnslsnr/db84/listener/

In my case it is: /u01/app/oracle/diag/tnslsnr/db84/listener/

$ cd /u01/app/oracle/diag/tnslsnr/db84/listener/
$ du -sh ./*
 46G     ./alert
 0       ./cdump
 0       ./incident
 0       ./incpkg
 4.0K    ./lck
 260K    ./metadata
 0       ./stage
 0       ./sweep
 8.0M    ./trace

Here we go, alert folder has 46G of disk space. Whatever it is, we need to free up disk space first.

cd alert
rm -rf ./log_*.xml

Now check the disk space again

df -h
 Filesystem                   Size  Used Avail Use% Mounted on
 devtmpfs                     2.0G     0  2.0G   0% /dev
 tmpfs                        2.0G  1.1G  939M  53% /dev/shm
 tmpfs                        2.0G  9.0M  2.0G   1% /run
 tmpfs                        2.0G     0  2.0G   0% /sys/fs/cgroup
 /dev/mapper/ol_db84nix-root   96G   50G   46G  53% /
 /dev/sdb1                     50G   47G   27M 100% /backup
 /dev/sda1                   1014M  276M  739M  28% /boot
 tmpfs                        396M     0  396M   0% /run/user/54321
 tmpfs                        396M   12K  396M   1% /run/user/42
 tmpfs                        396M     0  396M   0% /run/user/0

It’s seem ok now. Lets try to start oracle database again. In case your database already mounted:

$ sqlplus / as sysdba
sql> alter database open;

If not:

sql> shutdown immediate;
sql> startup;

I am glad if this case could help you. There are a lot of other reasons for sure. But that’s my case and I want to share my experience with you guys.

Happy DBA.