Today I’ve notice in our production database (220.127.116.11) that v$log view consists some groups with thread# = 2. It was wierd because we are currently using a non-RAC configuration. When I asked to the senior DBA, he said that they were using a RAC configuration 1 year ago but due to various performance problems in 9i they’ve switched to a single instance configuration.
They’ve switched but init.ora didn’t 🙂 When I’ve looked at the spfile I noticed that THREAD parameter is set to 0. Goosh!!! In Oracle the first instance opened with THREAD=0 takes all redolog threads not belonging to itself as public threads and ARCHn processes archive them also with modest terms. As you look at the v$archive_log view it explains everything better. You see some archived logs with thread#=2 and archival_thread#=1. One may think that there is no RAC at all what data in log files of thread#=2 is archived. Yes funny point. Oracle has nothing to archive at all. I don’t know the exact reason but most probably it only archives header or so. Because blocks column of v$archive_log view indicates 1 block for each archive file 🙂 And in a backup session we are continously backing those tiny silly files to tape and when we issue a “restore all archivelog” command they are also carried back. Even more silly that RMAN try to apply this no change vectory containing files when you issue a recover command. Lossing time for nothing to recover…
How to solve the problem ?
The solution of the problem is much more easier than the problem itself.
- Find the log group numbers of thread#n with status current or active from v$log. Those are the logs will be used in a recovery session ans those are the ones that ARCHn has not archived yet. So you can’t drop them.
- You need to disable (stopping ARCHn processes to archive logs with thread# n) related thread (n=2 for me)
ALTER DATABASE DISABLE THREAD 2;
- We will drop log groups but before you need to archive the log groups you define in the first step.
ALTER SYSTEM ARCHIVE LOG GROUP 6;
- Drop all groups of thread# 2
ALTER DATABASE DROP LOGFILE GROUP 6;
ALTER DATABASE DROP LOGFILE GROUP 7;
ALTER DATABASE DROP LOGFILE GROUP 8;
ALTER DATABASE DROP LOGFILE GROUP 9;
ALTER DATABASE DROP LOGFILE GROUP 10;
- Remove THREAD parameter from your pfile/spfile as soon as possible before the next server restart.
Moral of the story
Using the same init.ora parameter for a RAC and non-RAC system is totally inacceptable. More than this from the first day remove any parameter in your current pfile/spfile of which reason you don’t know. Especially hidden ones. They may be set for a workaround solution in older versions or releases or before recently applied patchsets. But they may not require any more and may have serious side effects on your system.