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.

About kocakahin

Just a computer engineer

Posted on November 29, 2007, in Oracle. Bookmark the permalink. 3 Comments.

  1. especially to benefit from migrations most to remove the cbo parameters and let them to take their version specific default values and then monitor and tune is a better strategy. hints and outlines here is another problem, they are like hard-coding, make the execution plan static for years. after 10g with this hidden parameter you can tell Oracle to ignore hints and benefit from what is developed with new versions of cbo, but this is a test subject of course.

    alter session set “_optimizer_ignore_hints”=TRUE;

    another interesting behavior is Oracle may change some hidden cbo parameters default values from release to release and this may harm you bad if you do not do performance tests during your upgrade, here are some of them and how to revert the behavior back;

    – during 9i -> 10g
    alter session set “_optimizer_cost_based_transformation” =off;
    alter session set “_gby_hash_aggregation_enabled” = FALSE;

    – during 8i -> 9i
    alter session set “_UNNEST_SUBQUERY” = false;
    alter session set “_ALWAYS_SEMI_JOIN” = off;
    alter session set “_ALWAYS_ANTI_JOIN” = off;
    alter session set “_COMPLEX_VIEW_MERGING” = false;
    alter session set “_B_TREE_BITMAP_PLANS” = false;

  2. hi im new to husnusensoy.wordpress.com , looking to learn new things😀

  3. Hello,

    Nice to be registered on husnusensoy.wordpress.com. My little name is maxizhu😉

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: