Clean Up A Obsolete Thread

Today I have been working with a new database, it is an upgrade from a RAC database but now it is single instance. The problem (maybe not, you can work in this way without issues) is thread 2 is not active. I like to keep clean all I can. Here I show you how to drop this thread.

SQL> SELECT * FROM V$LOG;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- ---------- --- ----------------
         1          1        247  104857600        512          1 NO  INACTIVE
         2          1        248  104857600        512          1 NO  INACTIVE
         3          1        249  104857600        512          1 NO  CURRENT
         4          2          1  104857600        512          1 NO  CURRENT
         5          2          0  104857600        512          1 YES UNUSED

In this case you only need execute the next commands;

First of all

SQL> ALTER DATABASE DISABLE THREAD 2;

After you need drop the group associaties to the thread 2;

SQL> ALTER DATABASE DROP LOGFILE GROUP 4;
SQL> ALTER DATABASE DROP LOGFILE GROUP 5;

Now, if we repeat the query on v$log;

SQL> SELECT * FROM V$LOG;                                                                                                                      

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- ---------- --- ----------------
         1          1        250  104857600        512          1 NO  INACTIVE
         2          1        251  104857600        512          1 NO  INACTIVE
         3          1        252  104857600        512          1 NO  CURRENT

HTH – Antonio NAVARRO

Advertisements

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