Archive for the 'ORA-30012' Category

DBCA is Fond of ORA-30012 In RAC Environments

I’ve been doing a lot of 4 node 10gR2 RAC stuff lately on 4 different clusters. I ran into a problem that I thought I’d share since I can’t find very much on the web or in Metalink. It is a real headache, but “easy” to fix. I put easy in quotes because it is only easy to fix if you know what to do. First, the problem.

ORA-30012
I was running a Real Application Clusters stress test that I developed called thrash. It is not sophisticated, but it does put a lot of pain on the instances, servers and storage. It consists of staggered instance rebooting followed by the creation of a small tablespace—one per instance. Once the tablespaces are created, a set of sqlplus sessions alter the tablespaces adding a significant number of random sized data files. The tablespaces are dropped and the instances are rebooted in a staggered fashion. As I said, each instance is sustaining this workload. I know it is nothing like a production workload. It’s just one test I run in hopes of exposing file manipulation bugs associated with tablespace creation, datafile addition and tablespace dropping. That is, I’m looking for filesystem bugs exposed by Oracle tablespace manipulation.

Bumps in the Road
On occasion I was getting instances that would stop participating in the thrash. I read the alert logs and found that the error was ORA-30012—which made no sense to me since it was happening out of the blue. In fact, it was happening after running thrash for as long as 12 hours. To show you the oddity of these alert log entries, I’ll provide grep(1) output from all the alert logs (ORACLE_BASE is on a CFS):

$ grep ‘does not exist or of wrong type’ *log
alert_PROD2.log:ORA-30012: undo tablespace ‘UNDOTBS2’ does not exist or of wrong type
alert_PROD2.log:ORA-30012: undo tablespace ” does not exist or of wrong type
alert_PROD3.log:ORA-30012: undo tablespace ‘UNDOTBS3’ does not exist or of wrong type
alert_PROD3.log:ORA-30012: undo tablespace ” does not exist or of wrong type
alert_PROD4.log:ORA-30012: undo tablespace ‘UNDOTBS4’ does not exist or of wrong type

How strange! What is an undo tablespace with a NULL name? Part of the SPFILE is shown later in this post establishing the fact that I assign undo to instances explicity so what gives?

The following is a snippet to show that the error was occuring during ALTER DATABASE OPEN:

Errors in file /u01/app/oracle/admin/PROD/udump/prod2_ora_1569.trc:
ORA-30012: undo tablespace ‘UNDOTBS2’ does not exist or of wrong type
Fri Mar 16 04:41:38 2007
Error 30012 happened during db open, shutting down database
USER: terminating instance due to error 30012
Instance terminated by USER, pid = 1569
ORA-1092 signalled during: ALTER DATABASE OPEN…

The trace file wasn’t much help:

$ more /u01/app/oracle/admin/PROD/udump/prod2_ora_1569.trc
/u01/app/oracle/admin/PROD/udump/prod2_ora_1569.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/rac_1
System name:    Linux
Node name:      qar14s22
Release:        2.6.9-42mxs351RHELupdate4
Version:        #1 SMP Tue Mar 6 16:37:53 PST 2007
Machine:        x86_64
Instance name: PROD2
Redo thread mounted by this instance: 2
Oracle process number: 16
Unix process pid: 1569, image: oracle@qar14s22 (TNS V1-V3)
*** SERVICE NAME:() 2007-03-16 04:41:35.038
kspgetpeeq:  kspasci not KSPASCNOP (0x110001 != 0x0)
*** SESSION ID:(137.25) 2007-03-16 04:41:38.788
ORA-30012: undo tablespace ‘UNDOTBS2’ does not exist or of wrong type

After exhausting my patience spelunking for information on the web and Metalink, I asked my fellow OakTable Network members. Jože Senegačnik of dbprof.com replied with:

If this is a RAC database then you need to specify the instance name in the init.ora or spfile together with the undo_tablespace parameter.

Where did he come up with that? I asked him if he’d actually hit this before. His answer was:

Yes, a couple of weeks ago I have experienced it on RAC on Windows. One node had problems with the undo_tablespace parameter after an unplanned database restart. The undo_tablespace parameter was changed in runtime months before but obviously it lacked the SID information and this caused problems after database restart.

A DBCA NO-NO
Folks, this database was created with DBCA. I don’t know how I haven’t seen this issue before, but the problem is that DBCA does not configure the SPFILE with explicit assignments for the INSTANCE_NAME parameter. For example, the following are a couple of strings(1)|grep(1) command pipelines that would return the 4 assigments of the INSTANCE_NAME parameter had DBCA set it up that way:

$ strings – spfilePROD.ora | grep –I instance_name
$ strings – spfilePROD.ora | grep ‘^PROD’
PROD2.__db_cache_size=71303168
PROD4.__db_cache_size=67108864
PROD3.__db_cache_size=75497472
PROD1.__db_cache_size=71303168
PROD3.__java_pool_size=4194304
PROD2.__java_pool_size=4194304
PROD4.__java_pool_size=4194304
PROD1.__java_pool_size=4194304
PROD3.__large_pool_size=4194304
PROD2.__large_pool_size=4194304
PROD4.__large_pool_size=4194304
PROD1.__large_pool_size=4194304
PROD2.__shared_pool_size=79691776
PROD4.__shared_pool_size=83886080
PROD3.__shared_pool_size=75497472
PROD1.__shared_pool_size=79691776
PROD3.__streams_pool_size=0
PROD2.__streams_pool_size=0
PROD4.__streams_pool_size=0

PROD1.__streams_pool_size=0
PROD3.instance_number=3
PROD4.instance_number=4
PROD2.instance_number=2
PROD1.instance_number=1
PROD3.thread=3
PROD2.thread=2
PROD4.thread=4
PROD1.thread=1
PROD1.undo_tablespace=’UNDOTBS1′
PROD3.undo_tablespace=’UNDOTBS3′
PROD4.undo_tablespace=’UNDOTBS4′
PROD2.undo_tablespace=’UNDOTBS2′

Remedy
It was exactly what Joze said. I set explicit assignments for the ISNTANCE_NAME parameters as follows and the problem has gone away.

PROD1.INSTANCE_NAME=PROD1
PROD2.INSTANCE_NAME=PROD2
PROD3.INSTANCE_NAME=PROD3
PROD4.INSTANCE_NAME=PROD4

Hope this helps some googler someday.


DISCLAIMER

I work for Amazon Web Services. The opinions I share in this blog are my own. I'm *not* communicating as a spokesperson for Amazon. In other words, I work at Amazon, but this is my own opinion.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 742 other followers

Oracle ACE Program Status

Click It

website metrics

Fond Memories

Copyright

All content is © Kevin Closson and "Kevin Closson's Blog: Platforms, Databases, and Storage", 2006-2015. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Kevin Closson and Kevin Closson's Blog: Platforms, Databases, and Storage with appropriate and specific direction to the original content.

%d bloggers like this: