Oracle Challenge #2 Solution

Apparently, quite a few people ran into this situation before, so I got a few correct answers  (some on Twitter, some on LinkedIn, and some as comments to the original post).

The answer is that Oracle uses the ORACLE_HOME value to attach to the SGA, so a different ORACLE_HOME can cause this strange behavior. The first time I had this problem (probably around 15 years ago) it was simply because there was a slash (/) at the end of the ORACLE_HOME. This is very common if we use “TAB” to auto complete the path (and we end up with “/opt/app/oracle/11.2/” instead of “/opt/app/oracle/11.2”).

In any case, it seems like Oracle fixed the slash at the end thing, so I couldn’t reproduce it with 11.2 or 12.2. But knowing that the ORACLE_HOME value matters here, I tried a different variations. I then found that an additional slash at the beginning (“//opt/app/oracle/product/11.2”) or anywhere in the middle would do the trick.

When the ORACLE_HOME is different than the one that was used to start the instance, sqlplus won’t be able to connect to the running instance, and therefore will allow us to start another instance. It won’t let us open the control file, as it is already in use, but new processes will be up and new memory will be allocated. I wonder what will happen if this was a cluster database (in cluster database the control file can be opened by another instance).

Another solution was suggested by Sean Scott, who added space to the ORACLE_SID variable and got a nice ORA-600 (you can see it in the comments of the challenge post).


One thought on “Oracle Challenge #2 Solution”

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s