Two or more Threads on an Oracle single instance 9 March 2010Posted by David Alejo Marcos in RAC, Tuning.
Tags: Dataguard, RAC, Tuning
Have you ever move from a RAC configuration to a single instance?, this is one of the scenarios were you may finish with more than one thread on a single instance database.
The problem is that having more that one thread is for RAC, not single instances and will give you problems like restoring the database or alerts if you have scripts to monitor standby databases being kept up to date.
having two threads on a single instance.
Thread 2 and 3 where closed on our primary database:
SQL> select thread#, status, enabled, sequence#’, checkpoint_time from v$thread;
THREAD# STATUS ENABLED SEQUENCE# CHECKPOIN
———- —— ——– ———- ———
1 OPEN PUBLIC 26956 09-MAR-10
2 CLOSED PRIVATE 31 06-MAR-10
3 CLOSED PRIVATE 104 09-MAR-10
but as you can see on the following query, Oracle was still using one of them (thread 3 in our case):
SQL> select thread#, sequence#, status from v$log;
THREAD# SEQUENCE# STATUS
———- ———- —————-
1 26947 INACTIVE
1 26948 INACTIVE
1 26949 CURRENT
1 26945 INACTIVE
1 26946 INACTIVE
3 100 INACTIVE
3 101 CURRENT
2 0 UNUSED
2 0 UNUSED
9 rows selected.
this was problematic once we tried to restore backups from that database, among other things.
it is quite simple, we need to remove thread 2 and thread 3 from our database. The steps to follow are:
1.- disable unwanted threads:
ALTER DATABASE DISABLE THREAD 2;
ALTER DATABASE DISABLE THREAD 3;
2.- archive the thread that is in “CURRENT” or “INACTIVE” status from threads 2 or 3:
select group#, thread#, sequence#, status from v$log where status = ‘CURRENT’ and thread# in (2,3);
ALTER SYSTEM ARCHIVE LOG GROUP ????;
replace ???? with any group# returned.
3.- Drop logfile groups belonging to threads 2 and 3:
ALTER DATABASE DROP LOGFILE GROUP 6;
ALTER DATABASE DROP LOGFILE GROUP 7;
ALTER DATABASE DROP LOGFILE GROUP 8;
ALTER DATABASE DROP LOGFILE GROUP 9;
The reason behind this problem may differ from system to system.
Some people have this problem because they copied the spfile (or init.ora) file from a different RAC system, and use it on a single instance database without removing RAC parameters.
Other people (like us) is because our primary database is RAC and the standby is single instance.
The init.ora parameter thread has a default value of 0 (zero) meaning that this instance will use any available, enabled public thread.
When we create a database, Oracle creates and enables thread 1 as public by default. This parameter is classified as RAC parameter, but we can use it on a single instance if we decide to use a different public thread that 1 or to fix this problem.
We did our switchover test from node 3. This node has instance 3 from our 3-node RAC running. As soon as we did the switchover to a single instance, Oracle started using thread 1 (default) and thread 3 (the same thread defined from the source database at the time of the switch).
At that time we should have followed the steps above OR modify our init.ora (or spfile) file and specify thread=1 for our single instance database.
From now on, we will add thread=1 on our single instance that have RAC as standby or any single instance with RAC as primary to avoid this problem in the future.
As always, comments are welcome.