This error typically occurs when there is a communication problem between the Oracle client and the Oracle database server. The error implies a failure in communicating with the Oracle shadow process. Since it’s a broad error, additional information is required to pinpoint the root cause. The error, on its own, doesn’t provide enough details to identify the specific issue. Further investigation and gathering more information are necessary to understand the underlying problem.
- Server machine crashed
- The DB server(the OS) shut down without properly shutting down the database server.
- Your server process was killed at the O/S level
- The client incorrectly handled multiple connections
- Oracle internal errors
- Network-related issues….etc
To solve the issue follow the following steps
1. Connect the DB sqlplus / as sysdba
5. shutdown immediate
Solved
connect / as sysdba
SQL> SELECT *
FROM V$RECOVERY_FILE_DEST;
SQL> SELECT *
FROM V$FLASH_RECOVERY_AREA_USAGE;
SQL> ALTER SYSTEM
SET DB_RECOVERY_FILE_DEST_SIZE = 200G SCOPE=BOTH SID='*';
If still not
resolved then
1. rename
flash_recovery_area as flash_recovery_area_bck
2. create new folder
flash_recovery_area on same location.
3. connect / as
sysdba
4. SQL>
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 200G SCOPE=BOTH SID='*';
5. restart/reboot
the server and check database will be up.
After hours of misdirection from official Oracle
support, I dove into this on my own and fixed it. I am documenting it here
in case someone else has this problem.
To do any of this, you must be the oracle user:
$ su - oracle
Step 1: You need to look at the alert log. It isn't in /var/log as expected.
You have to run an Oracle log reading program:
$ adrci
ADRCI: Release 11.2.0.1.0 - Production on Wed Sep 11 18:27:56 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
ADR base = "/u01/app/oracle"
adrci>
Notice the ADR base. That is not the install. You need to see the homes so you
can connect to the one that you use.
adrci> show homes
ADR Homes:
diag/rdbms/cci/CCI
diag/tnslsnr/cci/listener
diag/tnslsnr/cci/start
diag/tnslsnr/cci/reload
CCI is the home. Set that.
adrci> set home diag/rdbms/cci/CCI
adrci>
Now, you can look at the alert logs. It would be very nice if they were in
/var/log so you could easily parse the logs.
Just stop wanting and deal with this interface. At least you can tail (and I
hope you have a scrollback buffer):
adrci> show alert -tail 100
Scroll back until you see errors. You want the FIRST error. Any errors after
the first error are likely being caused by the
first error. In my case, the first error was:
ORA-19815: WARNING: db_recovery_file_dest_size of 53687091200 bytes is 100.00%
used, and has 0 remaining bytes available.
This is caused by transactions. Oracle is not designed to be used. If you do
push a lot of data into it, it saves
transaction logs. Those go into the recovery file area. Once that is full (50GB
full in this case). Then, Oracle just dies.
By design, if anything is messed up, Oracle will respond by shutting down.
There are two solutions, the proper one and the quick and dirty one. The quick
and dirty one is to increase
db_recovery_file_dest_size. First, exit adrci.
adrci> exit
Now, go into sqlplus without opening the database, just mounting it (you may be
able to do this without mounting the
database, but I mount it anyway).
$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 11 18:40:25 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> connect / as sysdba
Connected.
SQL> startup mount
Now, you can increase your current db_recovery_file_dest_size, increased to 75G
in my case:
SQL> alter system set db_recovery_file_dest_size = 120G scope=both
Now, you can shutdown and startup again and that previous error should be gone.
The proper fix is to get rid of the recovery files. You do that using RMAN, not
SQLPLUS or ADRCI.
$ rman
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Sep 11 18:45:11 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
RMAN> backup archivelog all delete input;
Wait a long time and your archivelog (that was using up all that space) will be
gone. So, you can shutdown/startup your
database and be beck in business.
$ rman target /
RMAN> SHOW ALL;
RMAN> LIST BACKUP SUMMARY;
RMAN> CROSSCHECK BACKUP;
RMAN> DELETE OBSOLETE;
RMAN> DELETE NOPROMPT OBSOLETE;
RMAN> DELETE OBSOLETE RECOVERY WINDOW OF 10 DAYS;
RMAN> LIST BACKUP SUMMARY;
RMAN> DELETE EXPIRED BACKUP;
log in as root and execute
command "ipcs". should you see ipcs owned by the oracle user, delete
them using the "ipcrm" command, and everything will work fine again
OR 01/04/2023 I resolved this problem in this way:
Fix for ORA-03113: end-of-file on communication channelOracle Database Server 12c Here is how to fix ORA-03113: end-of-file on communication channel
…
Connected to an idle instance.
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2926472 bytes
Variable Size 1224738936 bytes
Database Buffers 905969664 bytes
Redo Buffers 13848576 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 4903
Session ID: 237 Serial number: 26032
Disconnected from Oracle Database 12c
Enterprise Edition Release 12.1.0.2.0 – 64bit Production
…
…
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2926472 bytes
Variable Size 1224738936 bytes
Database Buffers 905969664 bytes
Redo Buffers 13848576 bytes
Database altered.
Database altered.
Database altered.
Database altered.
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2926472 bytes
Variable Size 1224738936 bytes
Database Buffers 905969664 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.
SQL>
No comments:
Post a Comment