I posted my first challenge almost 2 years ago and there was a lot of interest in it. Today I post another one.
Many years ago I saw this strange thing and it took me some time to realize what’s going on. Since then I ran into this scenario a few times, solving it instantly (and make a good impression at the same time).
In Oracle, instance name should be unique, if not in the entire environment, at least on the server. Starting two instances with the same name is not supported and it’s something that we shouldn’t do (and usually can’t).
However, one time I got to an environment with a running instance, but couldn’t connect to it for some reason. When I connected using “sqlplus / as sysdba” I got “Connected to idle instance”. At one point I decided to start it and it started (in nomount state).
Now I’ll show that I can start 2 instances on the same server with the same name from the same ORACLE_HOME. I had to tweak it a little bit as Oracle fixed the original way of reproducing it, but I can still do it. This is 11.2.0.4, but works the same on 12.2.0.1.
First instance:
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 29 13:54:46 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 331350920 bytes
Database Buffers 729808896 bytes
Redo Buffers 5517312 bytes
Database mounted.
Database opened.
Second instance:
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 29 13:59:31 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 331350920 bytes
Database Buffers 729808896 bytes
Redo Buffers 5517312 bytes
ORA-01102: cannot mount database in EXCLUSIVE mode
Of course the controlfile could not be opened, but the instance is up (I wonder what would happen if this was a RAC installation…)
Let’s prove that we have 2 instances with the same name:
[oracle@ORA112 4065]$ ps -ef|grep pmon
oracle 4065 1 0 13:54 ? 00:00:00 ora_pmon_ora112
oracle 4239 1 0 13:59 ? 00:00:00 ora_pmon_ora112
oracle 4291 4161 0 14:00 pts/0 00:00:00 grep pmon
[oracle@ORA112 4065]$ ls -l /proc/4065/exe
lrwxrwxrwx 1 oracle oinstall 0 Jan 29 14:00 /proc/4065/exe -> /oracle/app/oracle/product/11.2.0/dbhome_1/bin/oracle
[oracle@ORA112 4065]$ ls -l /proc/4239/exe
lrwxrwxrwx 1 oracle oinstall 0 Jan 29 14:01 /proc/4239/exe -> /oracle/app/oracle/product/11.2.0/dbhome_1/bin/oracle
And now to the question: how could I do it? And just a comment, it’s really simple and does not require any hacking or anything too sophisticated.
Hi Liron,
Perhaps by starting the second instance with a slightly modified value for ORACLE_HOME (e.g. an additional slash), so that the combination ORACLE_HOME+SID produces a different hash which is used to attach to a SGA?
Spaces.
export ORACLE_SID=cdbrac11
sqlplus “/ as sysdba”
SQL> create pfile=’?/dbs/initcdbrac11 .ora.ora’ from spfile;
exit
export ORACLE_SID=”cdbrac11 ”
sqlplus “/ as sysdba”
SQL> startup
ORACLE instance started.
Total System Global Area 1493172224 bytes
Fixed Size 8792968 bytes
Variable Size 486540408 bytes
Database Buffers 989855744 bytes
Redo Buffers 7983104 bytes
ORA-00600: internal error code, arguments: [kccsbck_first], [1], [1494511901],
[], [], [], [], [], [], [], [], []
SQL> exit
# ps -ef | grep pmon | grep cdb
oracle 4996 1 0 12:10 ? 00:00:00 ora_pmon_cdbrac11
oracle 19955 1 0 11:47 ? 00:00:00 ora_pmon_cdbrac11
# ls -l $ORACLE_HOME/dbs/init*
-rw-r–r– 1 oracle oinstall 763 Jan 30 12:07 initcdbrac11 .ora
BTW, this was on a 12.2.0.1 RAC EE.
A little more information:
SQL> select instance_number, instance_name, regexp_replace(instance_name, ‘ ‘, ‘@’) allofit, status, logins, database_status from v$instance;
INSTANCE_NUMBER INSTANCE_NAME ALLOFIT STATUS LOGINS DATABASE_STATUS
————— —————- ———— ———— ———- —————–
1 cdbrac11 cdbrac11@ STARTED ALLOWED ACTIVE
Different DB_UNIQUE_NAME?
Isn’t this how data guard works?
Regards,
Frank