Disabling an old RAC instance thread

Today I’ve notice in our production database (9.2.0.8) 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.

Advertisement