Friday, 21 July 2017

ORA-03113: end-of-file on communication channel Fixed and Troubleshooting



Hi Friends,
Recently we faced this issue which was very critical. It is very general ora alert which almost time we can ignore but sometimes it is very critical which I have mentioned below the criticality.
ORA-03113: end-of-file on communication channel

This error can occur at the time such as:
1. While we are starting the oracle database.
2. While making any connection with database.
3. While running any SQL/PLSQL queries.
4. While mounting the database.
5. While recovering the database.
6. While opening database through Alter command.
7. While connecting as SYSDBA.

This error ORA-03113: end-of-file on communication channel occurs while a client process trying to connect a oracle database.

Above alert can be because of Network issue also.
Or may be server process was killed by some other users at OS level.

This can cause other ora alerts also such as: ora-1041, ora-3114, ora-1012 etc.
These all are like disconnection of the client from oracle database.

Here, I will show you as example while startup the oracle database we faced this below issue:

SQL> startup
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: 1919
Session ID: 326 Serial number: 62214

There may be many reasons as below:
1. May be redo log file system has full; and there is 0% space free.
 
Solution:
For this, we can clean up the old redo log file system and again startup the database.
OR
We can move the old redo logs file system to new location file system by which there space can available and we can try to startup the database.
 
If above check is fine then we can check the alert log, from there we can check the exact error. And will be helpful for future investigations.
 
In case, If getting error during the connecting as SYSDBA then need to check below points such as:
 
1. Check for environments file properly.
2. Check for LD_LIBRARY_PATH entries and setting should be properly.
3. Check for correct ORACLE_SID and ORACLE_HOME path.
4. And may be needed to check some more as per alert log error and details.

And others many more may also reason... for more info: Doc ID 1506805.1

Hope these above points clear and helpful. Please let us know for any concerns and suggestions.

Thursday, 6 July 2017

Oracle Troubleshooting Tips


Hey Buddies, hope you all are doing well.


Oracle DBA troubleshooting is very important and advance for DBA’s life. We all should know and understand the troubleshooting ways. Here we have some points and the ways which can be helpful in troubleshooting.

One thing I would like to share here that troubleshooting is not a rocket science but people make it like a rocket science hehehh. Even if rocket science also we can do it friends.

Friends, please just try once and follows these below points. I am sure it will be very useful. There are many more points technical but I tried to explain in very simple ways to kick start.

As per my experiences I have seen some people feel as troubleshooting is either hard or they afraid.

WHY TROUBLESHOOTING IS HARD:

1. You feel as you can’t do it even without any try. Already they have mindset.

2. You think it will take time but it takes time for everyone.

You know one SECRET, no one know everything, even seniors also check in metalink or google. Only one thing is different i.e. Observations as per experiences. Otherwise everything is same. Whatever we/they do, you can also do.

3. You afraid about mistakes but we need to try on test server which everyone does it.

Never give up. Today if you try for 1 hr and got solution, tomorrow you will be king of it. If you do not get solution in 1 hr, take it as challenge and try till get the solution. Every one learns like this only.

Simple Steps to START TROUBLESHOOTING:

1. You must Read error carefully.

2. Must check alert logs accordingly.

3. Check for related error either in Metalink or Google.

4. Understand or observe the error 100%. Do not be hurry in investigations.

After checking Metalink or Google, never do hurry to execute solution. First understand the solution 100% thoroughly. Check what is required in the given solution.

Some Important Suggestions:

For Example: Suppose, there is given any “rm” command in solution. As per my DBA experiences never ever use “rm or rm –rf” command. If required check 7 times before using this command.

Actually I forget this command; I don’t know “rm or rm-rf” command hehehh.
If it is very necessary, then take the backup of file and then remove.

Before executing solution check for any SQL DML command or any other SQL SCRIPTS statements which can impact like DML or any Transactional etc…

And if you are not sure then check on test servers and check with your seniors.

I guarantee that just follow these simple above steps. You will see yourself changes. If any more suggestions, I will keep update here.

And also if you have any more points please let us know which will be helpful for all of us.

Some more useful links:






Thanks,

Saturday, 17 June 2017

How to restart a failed patch in r12



Hi Friends,
Some of my friends asked questions related to adpatch. We have some scenario for adpatch failed. Please find the below details:

Suppose, our adpatch fails due to pre-requisite Patch or some other reasons. But we have already spent lots of time on patching.
So, here we have two options either apply the patch again or apply the patch from where adpatch patch is failed.

Off course, everybody likes to save time. So let’s start the below steps to save time and start the patching from where it was failed.

1. We need to shutdown the workers by below command.
$adctrl

Then select option 3 “Tell worker to shutdown/quit”

2. Now, take the backup of the FND_INSTALL_PROCESSES table
Steps:
Connect as applsys because FND_INSTALL_PROCESS is owned by applsys schema.
 
$Sqlplus applsys/<password>

create table fnd_Install_processes_back
as select * from fnd_Install_processes;

Check the both tables should have the same number of rows.

SQL>select count(*) from fnd_Install_processes_back;
1919 rows selected

SQL>select count(*) from fnd_Install_processes;
1919 rows selected

Same way take the backup of the AD_DEFERRED_JOBS table.

$sqlplus applsys/<password>

SQL>create table AD_DEFERRED_JOBS_back
as select * from AD_DEFERRED_JOBS;

Check the both tables should have same number of rows as we checked in above commands.

Now backup the .rf9 files located at below path directory.
$APPL_TOP/admin/ORA-DATA/restart directory.

Here adpatch session should have ended and the cursor should be back at the UNIX prompt.

$echo $ORACLE_SID
ORA-DATA

$ cd $APPL_TOP/admin/ORA-DATA
$ mv restart restart_back
$ mkdir restart
$ chmod 777 restart

Now we are ready to DROP the FND_INSTALL_PROCESSES table and the AD_DEFERRED_JOBS table.

$sqlplus applsys/<password>
$drop table FND_INSTALL_PROCESSES;
$drop table AD_DEFERRED_JOBS;

Now, we are ready to apply the new patch or say pre-req patch in case if you are missing the pre-req patch.
After Pre-req Patch is applied successfully.

Then restore the .rf9 files located at below location as shown above command
$ $APPL_TOP/admin/ORA-DATA/restart_back
directory.
$cd $APPL_TOP/admin/ORA-DATA
$mv restart restart_<patchnumber>
$mv restart_back restart

Again restore the both FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS table owned by the APPLSYS
schema.
$sqlplus applsys/<password>

$create table fnd_Install_processes
as select * from fnd_Install_processes_back;

$create table AD_DEFERRED_JOBS
as select * from AD_DEFERRED_JOBS_back;

Just cross-check the number of rows for both tables which should be equal from above commands.

And now Re-create the synonyms

$sqlplus apps/apps

$create synonym AD_DEFERRED_JOBS for APPLSYS.AD_DEFERRED_JOBS;

$create synonym FND_INSTALL_PROCESSES FOR APPLSYS.FND_INSTALL_PROCESSES;

Now, we are ready to Start adpatch and it will resume from where it was stopped in previous sessions.

Note: If you ask me frankly, I would like to go through re-apply the patch.

Patch log file location: /u01/oracle/VIS/apps/apps_st/appl/admin/VIS/log


Some more useful links:






Hope this may useful and helpful. Please let us know for any concerns or suggestion in either comment box or contact us @ora-data.blogspot.com

Thanks,