When using SQLplus to start the database I receive the error "ORA-03113 end-of-file on communication channel" right after it says that the database was mounted. If I look in the alert log it states that there are errors in the .trc files with error codes "ORA-19809", "ORA-19804", "ORA-16038", and "ORA-00312". This is proceeded by the error "ORA-19815" which states that the db_recovery_file_dest_size is 100% used and has 0 bytes available. After that are the 4 options listed to free up space. Easy enough, right?....Wrong!

No matter what I do, I cannot connect with SQLplus or RMAN to that instance. When I try to connect in both of those programs I receive the same two errors: "ORA-01034: ORACLE not available" followed by "ORA-27101: shared memory realm does not exist".

I have researched the shared memory error which usually deals with the ORACLE_SID or ORACLE_HOME being incorrect. However, none of the help I have found tells you where to look for those to verify their contents on a Windows 2008 R2 Server running multiple instances. Obviously, the Environment Variable can only point to one instance at a time, so these values must reside someplace else on a Windows server.

Any help with this would be greatly appreciated!!!!

Hi Don,

The database stopped and started OK last time?

If so, SOMETHING has changed.

>> ORA-01034

http://dba-oracle.com/m_ora_03113_end_of_f...ons_channel.htm

Cause: The connection between Client and Server process was broken.

Action: There was a communication error that requires further investigation.

*****************************
the ORA-19805 is not a concern, READ:

http://dba-oracle.com/t_ora_19815_warning.htm


********************************

>> ORA-27101

Ise the ipcs-pmb command to clena up the left-over RAM.

http://www.dba-oracle.com/t_unix_mrmory_ipcs.htm

This instace will start now . . . .

Check yout I/O syslogs for detauils on the root cause of this problem.

Hi Don,

****************************************
>> Archive logs are full, but Oracle won't let me clear them.

The archive logs are outside Oracle! and owned by the OS "Oracle ID" with 770 permissions.

You use the "rm" command to remove them!

I have automated it, removing all trce or dump files over a week old.

You can also auomate a script to muke all qrchived reo over x days old:

I have the fuill scripts if you donlt want to write and text it yourself

http://www.dba-oracle.com/oracle_scripts.htm

Donald,

Thanks for the replies, but none of those ideas have helped. Most refer to linux/unix systems too. As I stated in my original post, this is a Windows 2008 R2 server with multiple instances of Oracle. The other instances are working just fine. It is just this one instance that is not working.

You are correct with the statement that "The database stopped and started OK last time? If so, SOMETHING has changed.". As I pointed out in the alert log, it says that the db_recovery_file_dest_size is 100% used. I have deleted the old logs at the OS level, but from what I have read, I now need to use RMAN to tell the database that those files have been deleted. This is where the Catch 22 comes in. I cannot connect to that database with RMAN to perform that operation. When I try it gives me the shared memory error. I just don't know if I am getting the shared memory error because the db_recovery_file_dest_size is full or because of an error with the ORACLE_SID or ORACLE_HOME variables. I believe my next step should be to check those variables to verify they are correct. However, since this Windows server is running multiple instances, those variables don't exist in the Environment Variables screen. Do you know where those variables are located on a Windows server?

Or, better yet, do you know how I can manually increase the set size of the db_recovery_file_dest_size setting without using SQLplus? Maybe it is located in a text file I could just change?

Again, thanks for any help!

Hi Don,

Sorry I mis-understood your questions . . . .

I think I've got it now . . .

You get a ORA-19809 error on startup because the instance thinks it is already running:

>> ORA-19809

PLEASE READ.

1 - Use ADRCI to reduce the limit for db_recoveryfile_dest_size:

http://www.dba-oracle.com/t_ora_19809_limi...or_recovery.htm

2 - The next thing to try is bounce the server and re-start the services . . .

**********************************************
>> I just don't know if I am getting the shared memory error because the db_recovery_file_dest_size is full

NO. The docs says that db_recovery_file_dest_size only determines how much space can be used by Oracle in the flash recovery area.

You are confusing shared memory (stored on RAM) with the space in your db_recov ery_file_dest (stored on on disk).

Leftover RAM happens when an instance crashes and Oracle does not get a chance to nuke the RAM.

In Windows the db_recov ery_file_dest is located here:

c:\oraclexe\app\oracle\flash_recovery_area\

This is DISK storage, NOT shared memory.


********************************
>> I now need to use RMAN to tell the database that those files have been deleted.

I'll ask an RMAN expert to pop-in and tell you how!



***************************************
>> Most refer to linux/unix systems too

Many Windows adatabase is UNIX/LINUX interfaces like MKS tooolkit.

http://www.dba-oracle.com/t_scripts_windows.htm

YHou REALLY need to use a UNIX command interface so that you canm run shell scripts . .
Plus, UNIX DBA's earn double what Windows people earn . . .



**********************************************
>> I cannot connect to that database with RMAN to perform that operation. When I try it gives me the shared memory error

Shared memory is volatile, bounce the box and all RAM in the heap is released.

I bounce my Windows servers daily to remove memory leaks . . .

You you are on a PC, just bounce the box to free-up the RAM held by the RMAN database. . . .

Or you can remoive the Shared Memory segments by deleting all the SID files in $TMP*.

Donald,

Sorry, but I should have given you more information. The database became unavailable due to running out of space on the disk in the flash recovery area. At that point I was getting an error that the database was refusing connections. I used the web based DB Manager to do a shutdown. After that, the database would not start back up. I did some research online and others suggested that I bounce the server. So that is what I did. After bouncing the server, the other two instances worked fine, but the one I am struggling with wouldn't start. This is when I did a bunch more research and couldn't find a solution. Which brings us to now.

I do know the difference between the disk and the ram errors. However, a lot of help online references that the ORA-03113 error is mostly a catch-all and just means that it lost communication with the database. Some people have had this error due to their windows logs being full. That plus the fact that I can see that the flash recovery area is full, leads me to believe that the reason I cannot connect to the database is due to the flash being full. I am not positive that this is what is wrong, but it is pretty much the main error in the alert log.

Which brings me to the two things that I need to try to fix. First of all is the flash recovery error being full. To fix this I can either do one of two things. First, I could try to increase the db_recoveryfile_dest_size value of the database. This requires that I connect to the database via SQL plus to alter the size. SQL plus will not connect to the database due to the shared memory realm error. The second thing I can do is use RMAN to delete the archive logs or delete them in the OS and then use RMAN to notify the database that they have been removed. Again, the problem here is that I cannot connect with RMAN to the database due to the same shared memory realm error that I receive when using SQL plus.

I did find the article on your site that refers to ORA-27101: shared memory realm does not exist tips.

This suggests that I check that ORACLE_HOME and ORACLE_SID are correct. The issue here is that I am running multiple instances in Windows. Because of this there isn't just one ORACLE_HOME/ORACLE_SID located in the Environment Variable area. Actually, neither of those exist if I look where that article suggests they would be (right click My Computer->Properties->Advanced Tab->Environment Variable). Since the other two instances are working correctly, I assume that these variables are not located in that suggested area, but somewhere else. Registry maybe? I cannot find any help on where those are located when running multiple instances.

So, I guess that this is my main question: Where can I find those variables so that I can verify that they are correct for the one instance that is not working?

OR....How can I adjust the db_recoveryfile_dest_size value for that database instance WITHOUT using SQL plus or anything else that has to connect to the database? For example, edit a text file that contains the database startup parameters maybe?

Again, Thanks for any and all help!

Hi Don,

This is a fun puzzle, no neeed for an apology!

But again, I'm a big-server DBA, I have very little expertise on Windows.

DBA's are supposed to be problem-solvers!

>> Where can I find those variables so that I can verify that they are correct for the one instance that is not working?

OK, lets start from the beginning:

***************************************
>> STEP 1 The database became unavailable due to running out of space on the disk in the flash recovery area.

Do you remember the exact ORA- error?



The solution is to go to the location at db_recovery_file_dest and add disk space to that directory

**********************************
>> I used the web based DB Manager to do a shutdown.

Go back to thre alrt log andf see if it shut down correctly. It's still there . . .

>> others suggested that I bounce the server.

Yup! 99% of the time a bounce will fix the error . . . .

Don, please learn not to rely on the OEM GUI!

I do everywhring at a DOS or UNIX command prompt, and it's FAR easier because you can see exactly what's happening.

I can mail you a free data dictionary poster, to help you understand the dictionary:

http://www.rampant-books.com/poster_oracle_database.htm

DBA's who know command-line can earn dluble the ssalary of OEM "button mashers" . . .

I don;t know where you live, nit here in North Carolina, a good Oracle DBA earns over $100,000 per year . . . .

*****************************************************
>> of this there isn't just one ORACLE_HOME/ORACLE_SID located in the Environment Variable area.

Yes, Windows parms are in the registry, the biggest pile of poo ever unleashed on us folks . . . .

FYI, each instance has it;s own init.ora file . . .

In Windows, try $ORACLE_HOME/$ORACLE_SID/pfile

In Windows, Oracle parms are in flat files unless you are using the spfile option . . .

Please read:

http://www.dba-oracle.com/concepts/pfile_spfile.htm

I think there is a registry entry that says the exact location if your init.ora file . . .


****************************************************
>> the other two instances worked fine, but the one I am struggling with wouldn't start.

You are running three instances on a personal computer?

when you bounced, two of the three autostarted OK?

Why three instances? Many instances on a larger server is great because Oracle has easily share computing resources, but on a PC it makes me think that you have a very tiny SGA for each instance.

Could you just move the schemas into a single instance?

****************************************************
>> Some people have had this error due to their windows logs being full.

Interesting, I did not know that . . . . I'm adding that to my notes . . .

*****************************************************
>> the flash recovery area is full, leads me to believe that the reason I cannot connect to the database is due to the flash being full.

Disk is cheap! Add a few gig to the directory . . .

>> The second thing I can do is use RMAN to delete the archive logs

Are these on the same disk as your flash recovery area? If so, YES! nuke the oldest ones . . .

****************************************************
>> How can I adjust the db_recoveryfile_dest_size value for that database instance WITHOUT using SQL plus

I would try adding more disk space first!

If the database is down, go to your startup deck (init.ora) at $ORACLE_HOME/$ORACLE_SID/pfile and change it!

Since you are on a Windows PC, just go by a disk and gen it in as an e: drive and re-name the directoroes in your init.ora files . . .

Also, since I don;t do Windows, open a service request with Oracle tech support. That's what your license fees pay for. I'll bet they have seen this dozens of times before and will know the exact solution:

http://support.oracle.com

If you don't have Oracle tech support, you can rent a Windows DBA for a couple of hours to fix it for you. I have plenty of them, just call 800-766-1884 . . .

In either case, get my free data dictionary poster so you are not so helpless uing only OEM . . . . . .

Let me know if you get it fixed!

Donald,

Thanks for all of your attempts, but as I have said before, I have been through what you are suggesting. Just to recap:

1.) This is a Windows 2008 R2 Server (not a personal computer)
2.) There are 3 instances that have to run separately, but this server has 8 cores and 32GB of Ram, so no worries.
3.) I am NOT out of disk space. I just hit the stupid db_recoveryfile_dest_size limit.
4.) Since I have hit the db_recoveryfile_dest_size limit, the database will not open. I can start and mount the database, but cannot connect with SQL plus to alter the db_recoveryfile_dest_size value. When I try to connect with SQL Plus I recieve the error "ORA-01033: ORACLE initialization or shutdown in progress".
5.) Nevermind -

I think I have it figured out. The trick was to first start the database, then mount the database. Although I still could not connect with SQL Plus, this allowed me to finally use RMAN to run a crosscheck on the archive logs and then delete the expired logs that I had manually deleted via the OS.

Before I was trying to OPEN the database, which was giving me all of the errors I had referenced before due to hitting that size limit. I haven't looked through the database yet to see if all of the data is still intact, but I was finally able to OPEN the database without errors.

Thanks for your help!

Hi Don,

>> The trick was to first start the database, then mount the database. Although I still could not connect with SQL Plus, this allowed me to finally use RMAN to run a crosscheck on the archive logs and then delete the expired logs that I had manually deleted via the OS.

Great!

Sorry I could not help more . . .

Remember to use MOSC when you have issues like this:

http://support.oracle.com

I made this page to describe your problem and solution in my wiki, hopefully yo help people wuith this problem in the future:

http://www.dba-oracle.com/t_rman_crosscheck.htm

http://www.dba-oracle.com/p_db_recovery_file_dest_size.htm

If I can improve this, please let me know!