Step by Step - How to resolve redo log file corruption using ALTER DATABASE CLEAR UNARCHIVED LOGFILE command



Connect to sys using sqlplus in admin command prompt
Check whether your database is in archive log mode or not. Run command ARCHIVE LOG LIST. It will show you the following details:
It is always preferable to have your database in archive log mode. The reason being if your database is in archive log mode you can recover from all committed changes in the event of an OS or disk failure.
This query will display the path of redo log files, their group and their status. It is always recommended to have a minimum of two members in one group.

As you can see I have only 1 member in group 6 whose current status is INACTIVE. I intentionally have 1 member to generate a scenario for the sake of this practical.

Now go to the specified path where the redo log member of group 6 is located and open it in a notepad. It will show some symbolic data. Delete couple of lines and save it the original location which is the one we got as a result of query.

Why did we do the above step i.e. why did we delete some lines of the inactive redo log file?
The reason is we cannot corrupt the redo log file of the member whose status is CURRENT. Also since I want to explain what is the danger when you have only 1 member in the group and also about the risks of the command which we will be using to restart our database.

Another question is since the status of the member whose redo log file we just corrupted is inactive, then how are we going to check or confirm whether the redo log file is actually corrupt or not and what impact it is going to have on our database?

We certainly cannot wait till that member becomes current to get the error, if any. So this is what we are going to do: We will force the database to switch the log file using
Keep repeating above two commands till the member of group 6 becomes CURRENT while status of other members become INACTIVE.

When it becomes CURRENT, Run SHUT IMMEDIATE and then STARTUP. You should get the following error/s:
But this is one of the most common errors you will get when learning database. So how are we going to know that our redo log file is corrupted and that is the actual reason why we cannot startup our database?

I know I know you are all freaked out since the database isn’t starting... In such case you should recite the vintage and famous magical words:
ABRA KA DABRA!!!

Keep reciting those 3 words till you realize that there is a beautiful text document which is located in the following location
It is the alert_admin (or alert_SID where SID stands for System Identifier. For example if your database name is orcl then the name will be alert_orcl) text document which records or logs everything that’s happening within your database.

Open that document in Notepad (for Windows) or VI (for Linux), then scroll down till the end (or press CTRL+END) and look for the following errors:
And thus we have successfully corrupted our redo log file and the impact of that is we are not able to startup our database! YIPEEE!! Wait a min I don’t think we should be happy about it.................

Let’s take a look at the following steps to resolve these errors:

Since we cannot startup database in current session, open another session and login as sys. We will be connected to an idle instance. Then start the database in mount mode using:
Then run one of the most dangerous DBA's commands you will ever have to run which is:
BUT WAIT!!!! Before running the above command let's first understand WHY this(above) particular command?
(One of the reasons why the above command is incomplete....MUAHAHAHAHA.!!!)

Isn’t it easier if we just drop that corrupted redo log file? Or why not run this command:
The reason is: If the status of your redo log file is CURRENT then there is no archive log file created of that particular redo log file. Hence we cannot run ALTER DATABASE CLEAR LOGFILE.
To check whether your redo log file is archived or not you can run the following queries:
You can see that status of redo log files in group 2 is CURRENT (in 1st query) and a big NO in front of group 2(in 2nd query. Also displays the status, which is CURRENT).
And also database won’t allow you to drop the redo log file whose status is CURRENT. If you try to drop the redo log file whose status is CURRENT it will give you an error. Take a look at the following example:
Here status of members of group 2 is CURRENT. Now run the following command:
But then you will say "AHAHAHA I can change the status of group from CURRENT to INACTIVE by forcing a log switch and then drop the file".

Well BOOO!!! It's not going to help since you will have to drop the entire group. The reason being you cannot just drop the redo log file if it’s the single-lonely member in the group. Take a look at the following example:
As you can see I have only one member in group 6 whose status is CURRENT. I have already explained and shown why we cannot drop redo log file when its status is CURRENT.
Now I will force a log switch using:

As you can see I have forced the log switch and changed the status from CURRENT to INACTIVE. Now look and understand carefully what happens when I try to drop redo log file or member of group 6.
It throws an error saying "cannot remove last log member" meaning you cannot drop redo log member if it is the only member remaining in the group.

Then comes the next question: Can I drop the entire group? Or What happens if I drop the entire group?

The answer to that is it will work but it’s not recommended. The reason being your redo log file of that group is corrupted so you are losing your data anyways. Also if you want the group back you will have recreate the group and add a redo log member thus increasing your own efforts.

So I guess it's time to go back to the most dangerous DBA's command and see what happens after we execute it. Before you execute check your archive log list (You will know the reason later on) and I am going to repeat the steps I mentioned above about corrupting the redo log file and then execute the command. (Geez!! Be patient.)

Step 1: Check the status of your redo log files.
Step 2: Corrupt the redo log member of the inactive group and for this practical only choose the group which has only member in it.

Step 3: As mentioned earlier you won’t see the error of corruption directly on the your screen. Your might get the following error on your screen:
You will be able to see the actual error in the alert log document(Please scroll above for the information about alert log document.)
Depending upon how much you have corrupted your redo log file or which lines you have deleted you should get following errors in the alert log document:
Looking at the error you can make a wild guess that you have deleted something in the redo log file thus causing the reduction in its size.

Step 4: Then perform the following steps:
As you can see database starts in MOUNT mode and then shows the error, which means you cannot start the database in OPEN mode. But you can start the database in MOUNT mode which is exactly what we need to run the required command.

Step 5: Connect to sys since you have shut down the instance. It will connect to an idle instance.
Your database has started in MOUNT mode.

Step 6: Now run the following DBA command:
This is the complete command. The reason I have given GROUP 6 is because I have corrupted the redo log member of this group and no archive log of this redo log member is created since its status is ACTIVE.

Step 7: Check the status of your redo log file by running the following commands:
As you can see the status of the group which we have cleared is now UNUSED which is group 6 in this case. Now let’s run the same query but this time we will check sequence of the group member too.
And as you can see the sequence of the UNUSED redo log group is 0.

Step 8: Now we will run switch log file command to change the current status which is UNUSED.
OOPSSS !!! Looks like our database is not in OPEN mode. So to change the mode to OPEN mode run the following command:
Now we will try to switch log file again.
Thus we need the database to be in OPEN mode to execute the above command.
Let's check the status and sequence of the redo log file again by executing the following command:
As you can see the status of the redo log file of group 6 has now changed to CURRENT from UNUSED and we also have new sequence generated which is 2657.

Step 9: Then run the following command:
You will get a long list. Just scroll to the bottom till you see a certain sequence whose name is nothing but a blank space. In this case look for sequence# 2652. You will find a blank space before it. The reason for the blank space is that there was no archive log created of that particular log file and hence we had to run clear unarchived log file command.

Now think carefully about what will happen if a situation arises where you are required to recover your database and restore it and you don’t have the backup after executing clear unarchived log file command?

Your backup, restore and recovery will work fine till the sequence# 2651 but when it reaches sequence# 2652 there will be a big mess. Since the redo log file wasn’t archived you will lose the data from that redo log file and we have seen earlier that we need redo log file for recovery in case of media failure. And in this case since we have no archive log of this redo log file we won’t be able to recover the database after sequence# 2651.

Thus TAKE BACKUP of the whole database as soon as you execute the following command and have followed the above steps:

If you have followed the above steps perfectly then you should be able to perform this practical without any problems.

Comments

  1. Excellent information,. Very helpful. Thank you

    ReplyDelete
  2. Great article with valuable information found very resourceful and enjoyed reading it waiting for next blog updated thanks for sharing.
    typeerror nonetype object is not subscriptable

    ReplyDelete
  3. Nice Information Your first-class knowledge of this great job can become a suitable foundation for these people. I did some research on the subject and found that almost everyone will agree with your blog.
    Cyber Security Course in Bangalore

    ReplyDelete
  4. Writing in style and getting good compliments on the article is hard enough, to be honest, but you did it so calmly and with such a great feeling and got the job done. This item is owned with style and I give it a nice compliment. Better!
    Cyber Security Training in Bangalore

    ReplyDelete
  5. Hi, I looked at most of your posts. This article is probably where I got the most useful information for my research. Thanks for posting, we can find out more about this. Do you know of any other websites on this topic?

    Data Analytics Course in Bangalore

    ReplyDelete
  6. I have voiced some of the posts on your website now, and I really like your blogging style. I added it to my list of favorite blogging sites and will be back soon ...

    Digital Marketing Training in Bangalore

    ReplyDelete
  7. You have completed certain reliable points there. I did some research on the subject and found that almost everyone will agree with your blog.

    Data Science Training in Bangalore

    ReplyDelete
  8. Truly incredible blog found to be very impressive due to which the learners who go through it will try to explore themselves with the content to develop the skills to an extreme level. Eventually, thanking the blogger to come up with such phenomenal content. Hope you arrive with similar content in the future as well.

    Machine Learning Course in Bangalore

    ReplyDelete
  9. Truly overall quite fascinating post. I was searching for this sort of data and delighted in perusing this one. Continue posting. Much obliged for sharing.artificial intelligence course in chennai

    ReplyDelete
  10. It is late to find this act. At least one should be familiar with the fact that such events exist. I agree with your blog and will come back to inspect it further in the future, so keep your performance going.

    Digital Marketing Training in Bangalore

    ReplyDelete
  11. I wanted to leave a little comment to support you and wish you the best of luck. We wish you the best of luck in all of your blogging endeavors.

    Data Science Training in Bangalore

    ReplyDelete
  12. Happy to chat on your blog, I feel like I can't wait to read more reliable posts and think we all want to thank many blog posts to share with us.

    Machine Learning Course in Bangalore

    ReplyDelete
  13. You have done excellent job Thanks a lot and I enjoyed your blog. Great Post.
    Ethical Hacking Course in Hyderabad

    ReplyDelete
  14. Great post happy to see this. I thought this was a pretty interesting read when it comes to this topic Information. Thanks..
    Artificial Intelligence Course

    ReplyDelete
  15. Nice Post thank you very much for sharing such a useful information and will definitely saved and revisit your site and i have bookmarked to check out new things frm your post.
    Data Science Course

    ReplyDelete
  16. Very interesting post and this is my first time visit here and found so many interesting stuff in your blog especially its discussion. Thanks for the post!
    Digital Marketing Training Institute in Hyderabad

    ReplyDelete
  17. We are really grateful for your blog post. You will find a lot of approaches after visiting your post. Great work thank you.
    Cloud Computing Training in Bangalore

    ReplyDelete
  18. I am very happy to have seen your blog. Thanks again for all the details.
    Data Science Training in Lucknow

    ReplyDelete
  19. This is an informative and knowledgeable article. therefore, I would like to thank you for your effort in writing this article.
    Data Scientist Course in Lucknow

    ReplyDelete
  20. Informative Post. The information you have posted is very useful and sites you have referred was good. Thanks for sharing.
    Data Science Course with Placement

    ReplyDelete
  21. You have done a great job and will definitely dig it and personally recommend to my friends. Thank You.
    Data Science Online Training

    ReplyDelete
  22. A good blog always contains new and exciting information that helps readers to get useful information I am sure this is it.
    Data Science Course in Lucknow

    ReplyDelete
  23. I have read your article, it is very informative and useful to me, I admire the valuable information you offer in your articles. Thanks for posting it ...

    Business Analytics Course in Patna

    ReplyDelete
  24. I like to view your web site which is very useful and excellent resource and truly adored reading your posting. Thank you!
    Data Science Course in Gurgaon

    ReplyDelete
  25. I like viewing this web page which comprehend the price of delivering the excellent useful resource free of charge and truly adored reading your posting. Thank you!
    Data Science Certification Course

    ReplyDelete
  26. Nice post. This is a great article and am pretty much pleased with your good work. Very helpful information. Thank you.
    Best Data Science Courses

    ReplyDelete
  27. Nice Post i have read this article and if I can I would like to suggest some cool tips or advice and perhaps you could write future articles that reference this article. I want to know more!
    Data Analytics Course in Gurgaon

    ReplyDelete
  28. IEEE Final Year projects Project Centers in India are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes, while specialists like the enjoyment in interfering with innovation. For experts, it's an alternate ball game through and through. Smaller than expected IEEE Final Year project centers ground for all fragments of CSE & IT engineers hoping to assemble. Final Year Projects for CSE It gives you tips and rules that is progressively critical to consider while choosing any final year project point.

    JavaScript Online Training in India

    JavaScript Training in India

    The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training

    ReplyDelete

Post a Comment

Popular posts from this blog

Oracle Architecture - Explained In Detailed - Administration I

Oracle Database SQL - Practise - Question - Scott Schema Examples