Home > DBA, Linux, Oracle, Unix > Two instances with the same ORACLE_SID on one Unix server

Two instances with the same ORACLE_SID on one Unix server


You may have encountered the problem where you can connect to your Oracle database through the listener but cannot connect locally even though the value of ORACLE_SID in your environment is correct (http://www.freelists.org/post/oracle-l/local-connections-fail-but-listener-connections-succeed). In a Unix environment, this happens when your local connection is not using the value of ORACLE_HOME that was used to start the instance. Surprisingly, both the ORACLE_SID and ORACLE_HOME must be set correctly in order to connect to your instance.

Here’s the reason: When an Oracle instance is started in a Unix environment, a handle (“shmid”) for the shared memory segment (SGA) is constructed using the values of both ORACLE_SID and ORACLE_HOME as inputs to the “shmget” system call. To subsequently attach to the shared memory segment, each database connection needs to reconstruct the shmid of the shared memory segment and, to do this, they must use the same values of ORACLE_SID and ORACLE_HOME that were used to start the Oracle instance. It is not enough that they use the same value of ORACLE_SID. They must also use the same value of ORACLE_HOME. The listener uses the “fork” and “exec” system calls to start Oracle processes and therefore the value of ORACLE_HOME will always be set correctly for processes started by the listener.

When an Oracle instance is started in a Unix environment, a handle (shmid) for the shared memory segment (SGA) is constructed using the values of both ORACLE_SID and ORACLE_HOME as inputs to the shmget system call.

As an academic exercise just to prove the point, let’s do something that would be impossible if an instance was uniquely identified by the value of ORACLE_SID alone: let’s create two Oracle instances with the same value of ORACLE_SID (orcl) on one Unix server. Each instance will use a different value of ORACLE_HOME but, for good measure, both values will resolve to the same underlying physical ORACLE_HOME; that is, we will use soft links. For even more effect, the databases to which these instances connect will have the same db_name (orcl) though each will need a different db_unique_name (orcl1 and orcl2).

The following demonstration was performed on the pre-built development VM available from http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html. The database version is 11.2.0.2.

On checking the environment, we see that there is already an instance running with ORACLE_SID=orcl.

[oracle@localhost ~]$ echo $ORACLE_SID
orcl

[oracle@localhost ~]$ echo $ORACLE_HOME
/home/oracle/app/oracle/product/11.2.0/dbhome_2

[oracle@localhost ~]$ ps -ef | grep pmon
oracle    2431     1  0 21:04 ?        00:00:00 ora_pmon_orcl

[oracle@localhost ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 08-JAN-2013 21:11:37

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                10-SEP-2011 15:37:26
Uptime                    248 days 13 hr. 13 min. 56 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora
Listener Log File         /home/oracle/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=80))(PROTOCOL_STACK=(PRESENTATION=HTTP)(SESSION=RAW)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=21))(PROTOCOL_STACK=(PRESENTATION=FTP)(SESSION=RAW)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 2 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

Since we’re going to be creating two new databases, let’s add a TNS entry for each new database. The service name is the db_unique_name in each case (orcl1 and orcl2).

cat <<eof >>/home/oracle/app/oracle/product/11.2.0/dbhome_2/network/admin/tnsnames.ora
ORCL1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl1)
    )
  )

ORCL2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl2)
    )
  )

eof

Let’s create a data directory for each new database.

mkdir /home/oracle/app/oracle/oradata/orcl1

mkdir /home/oracle/app/oracle/oradata/orcl2

Let’s also create an init.ora (pfile) initialization file for each new database. The db_name (orcl) is the same in each case but the db_unique_name is different (orcl1 and orcl2).

cat <<eof >/home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/initorcl1.ora

db_name=orcl
db_unique_name=orcl1
control_files=('/home/oracle/app/oracle/oradata/orcl1/control01.ctl')
db_create_file_dest='/home/oracle/app/oracle/oradata/orcl1/'

eof
cat <<eof >/home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/initorcl2.ora

db_name=orcl
db_unique_name=orcl2
control_files=('/home/oracle/app/oracle/oradata/orcl2/control01.ctl')
db_create_file_dest='/home/oracle/app/oracle/oradata/orcl2/'

eof

Let’s now change the value of ORACLE_HOME using a soft link and then create the first of two new instances with ORACLE_SID=orcl. We’ll then create a new database with db_name=orcl. We’ll create a schema called hr within the newly created database so that we can verify our work later.

When we start a sqlplus session, we receive the message “Connected to an idle instance” even though an instance with ORACLE_SID=orcl is already running. This proves the point that both ORACLE_SID and ORACLE_HOME matter when connecting locally to an Oracle instance in a Unix environment. In other words, we cannot connect to the existing instance because we have changed the value of ORACLE_HOME. We can therefore proceed with creating a new instance with the same value of ORACLE_SID.

Both ORACLE_SID and ORACLE_HOME matter when connecting locally to an Oracle instance in a Unix environment.

[oracle@localhost ~]$ ln -s /home/oracle/app/oracle/product/11.2.0/dbhome_2 /home/oracle/app/oracle/product/11.2.0/orcl1

[oracle@localhost ~]$ ORACLE_SID=orcl

[oracle@localhost ~]$ ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/orcl1

[oracle@localhost ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 8 21:13:04 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile=?/dbs/initorcl1.ora
ORACLE instance started.

Total System Global Area  146472960 bytes
Fixed Size                  1342736 bytes
Variable Size              92275440 bytes
Database Buffers           50331648 bytes
Redo Buffers                2523136 bytes
SQL> create database;

Database created.

SQL> create user hr identified by hr;

User created.

SQL> grant create session, resource to hr;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Next create a second database with the same values of ORACLE_SID (orcl) and db_name (orcl). The ORACLE_HOME is technically different but it points to the same underlying physical ORACLE_HOME; that is, both values of ORACLE_HOME are soft links pointing to the same location.

[oracle@localhost ~]$ ln -s /home/oracle/app/oracle/product/11.2.0/dbhome_2 /home/oracle/app/oracle/product/11.2.0/orcl2

[oracle@localhost ~]$ ORACLE_SID=orcl

[oracle@localhost ~]$ ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/orcl2

[oracle@localhost ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 8 21:15:04 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile=?/dbs/initorcl2.ora
ORACLE instance started.

Total System Global Area  146472960 bytes
Fixed Size                  1342736 bytes
Variable Size              92275440 bytes
Database Buffers           50331648 bytes
Redo Buffers                2523136 bytes
SQL> create database;

Database created.

SQL> create user hr identified by hr;

User created.

SQL> grant create session, resource to hr;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

We can now verify our work by connecting locally and inserting different data into the new databases. We have to set ORACLE_HOME correctly in each case.

[oracle@localhost ~]$ ORACLE_SID=orcl

[oracle@localhost ~]$ ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/orcl1

[oracle@localhost ~]$ sqlplus hr/hr

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 8 21:16:27 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-06553: PLS-213: package STANDARD not accessible


Error accessing PRODUCT_USER_PROFILE
Warning:  Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
ERROR:
ORA-06553: PLS-213: package STANDARD not accessible


Error accessing package DBMS_APPLICATION_INFO

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table employees(employee_id integer not null primary key);

Table created.

SQL> insert into employees values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ ORACLE_SID=orcl

[oracle@localhost ~]$ ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/orcl2

[oracle@localhost ~]$ sqlplus hr/hr

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 8 21:17:27 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-06553: PLS-213: package STANDARD not accessible


Error accessing PRODUCT_USER_PROFILE
Warning:  Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
ERROR:
ORA-06553: PLS-213: package STANDARD not accessible


Error accessing package DBMS_APPLICATION_INFO

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table employees(employee_id integer not null primary key);

Table created.

SQL> insert into employees values (2);

1 row created.

SQL> commit;

Commit complete.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Finally, let’s connect through the listener and verify the data in each database.

[oracle@localhost ~]$ sqlplus hr/hr@orcl1

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 8 21:18:06 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-06553: PLS-213: package STANDARD not accessible


Error accessing PRODUCT_USER_PROFILE
Warning:  Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
ERROR:
ORA-06553: PLS-213: package STANDARD not accessible


Error accessing package DBMS_APPLICATION_INFO

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from employees;

EMPLOYEE_ID
-----------
          1

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ sqlplus hr/hr@orcl2

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 8 21:18:30 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-06553: PLS-213: package STANDARD not accessible


Error accessing PRODUCT_USER_PROFILE
Warning:  Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
ERROR:
ORA-06553: PLS-213: package STANDARD not accessible


Error accessing package DBMS_APPLICATION_INFO

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from employees;

EMPLOYEE_ID
-----------
          2

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

On checking the environment, we now see three databases with the same value of ORACLE_SID (i.e. orcl). /proc/<pid>/exe shows that all of them are using the same executable; that is, /home/oracle/app/oracle/product/11.2.0/dbhome_2/bin/oracle. However, /proc/<pid>/environ shows the value of the ORACLE_HOME string is different for each database. All three databases are reachable through the listener, the service name being the db_unique_name in each case; that is, orcl, orcl1, and orcl2 respectively.

[oracle@localhost ~]$ ps -ef | grep pmon
oracle    2431     1  0 21:04 ?        00:00:00 ora_pmon_orcl
oracle    3253     1  0 21:13 ?        00:00:00 ora_pmon_orcl
oracle    3306     1  0 21:15 ?        00:00:00 ora_pmon_orcl
oracle    3369  3175  0 21:18 pts/1    00:00:00 grep pmon

[oracle@localhost ~]$ ls -l /proc/2431/exe
lrwxrwxrwx 1 oracle oracle 0 Jan  9 11:53 /proc/2431/exe -> /home/oracle/app/oracle/product/11.2.0/dbhome_2/bin/oracle

[oracle@localhost ~]$ ls -l /proc/3253/exe
lrwxrwxrwx 1 oracle oracle 0 Jan  9 11:53 /proc/3253/exe -> /home/oracle/app/oracle/product/11.2.0/dbhome_2/bin/oracle

[oracle@localhost ~]$ ls -l /proc/3306/exe
lrwxrwxrwx 1 oracle oracle 0 Jan  9 11:54 /proc/3306/exe -> /home/oracle/app/oracle/product/11.2.0/dbhome_2/bin/oracle

[oracle@localhost ~]$ strings /proc/2431/environ | grep ORACLE_HOME
ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_2

[oracle@localhost ~]$ strings /proc/3253/environ | grep ORACLE_HOME
ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/orcl1

[oracle@localhost ~]$ strings /proc/3306/environ | grep ORACLE_HOME
ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/orcl2

[oracle@localhost ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 08-JAN-2013 21:19:02

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                10-SEP-2011 15:37:26
Uptime                    248 days 13 hr. 13 min. 56 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora
Listener Log File         /home/oracle/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=80))(PROTOCOL_STACK=(PRESENTATION=HTTP)(SESSION=RAW)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=21))(PROTOCOL_STACK=(PRESENTATION=FTP)(SESSION=RAW)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 2 handler(s) for this service...
Service "orcl1" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl2" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

And here are the files created for each database.

[oracle@localhost ~]$ ls -lR /home/oracle/app/oracle/oradata/orcl1
/home/oracle/app/oracle/oradata/orcl1:
total 7680
-rw-rw---- 1 oracle oracle 7847936 Jan  8 21:19 control01.ctl
drwxrwx--- 4 oracle oracle    4096 Jan  8 21:13 ORCL1

/home/oracle/app/oracle/oradata/orcl1/ORCL1:
total 8
drwxrwx--- 2 oracle oracle 4096 Jan  8 21:13 datafile
drwxrwx--- 2 oracle oracle 4096 Jan  8 21:13 onlinelog

/home/oracle/app/oracle/oradata/orcl1/ORCL1/datafile:
total 215288
-rw-rw---- 1 oracle oracle 104865792 Jan  8 21:13 o1_mf_sysaux_8gsz3wpn_.dbf
-rw-rw---- 1 oracle oracle 104865792 Jan  8 21:19 o1_mf_system_8gsz3spn_.dbf
-rw-rw---- 1 oracle oracle  10493952 Jan  8 21:18 o1_mf_sys_undo_8gsz3ygv_.dbf

/home/oracle/app/oracle/oradata/orcl1/ORCL1/onlinelog:
total 205016
-rw-rw---- 1 oracle oracle 104858112 Jan  8 21:19 o1_mf_1_8gsz3r7t_.log
-rw-rw---- 1 oracle oracle 104858112 Jan  8 21:13 o1_mf_2_8gsz3rod_.log
[oracle@localhost ~]$ ls -lR /home/oracle/app/oracle/oradata/orcl2
/home/oracle/app/oracle/oradata/orcl2:
total 7680
-rw-rw---- 1 oracle oracle 7847936 Jan  8 21:19 control01.ctl
drwxrwx--- 4 oracle oracle    4096 Jan  8 21:15 ORCL2

/home/oracle/app/oracle/oradata/orcl2/ORCL2:
total 8
drwxrwx--- 2 oracle oracle 4096 Jan  8 21:15 datafile
drwxrwx--- 2 oracle oracle 4096 Jan  8 21:15 onlinelog

/home/oracle/app/oracle/oradata/orcl2/ORCL2/datafile:
total 215288
-rw-rw---- 1 oracle oracle 104865792 Jan  8 21:15 o1_mf_sysaux_8gsz7gty_.dbf
-rw-rw---- 1 oracle oracle 104865792 Jan  8 21:15 o1_mf_system_8gsz7d4g_.dbf
-rw-rw---- 1 oracle oracle  10493952 Jan  8 21:15 o1_mf_sys_undo_8gsz7jlz_.dbf

/home/oracle/app/oracle/oradata/orcl2/ORCL2/onlinelog:
total 205016
-rw-rw---- 1 oracle oracle 104858112 Jan  8 21:18 o1_mf_1_8gsz7bs8_.log
-rw-rw---- 1 oracle oracle 104858112 Jan  8 21:15 o1_mf_2_8gsz7c4l_.log

What about the initialization parameters that depend on the database name? We’re covered since the db_unique_name is unique in each case.

[oracle@localhost ~]$ ORACLE_SID=orcl

[oracle@localhost ~]$ ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/orcl1

[oracle@localhost ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.2.0 Production on Thu Jan 10 01:45:03 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set linesize 200
SQL> set pagesize 100
SQL> column name format a30
SQL> column value format a100
SQL> select name, value from v$parameter where value like '%orcl%';

NAME                           VALUE
------------------------------ ----------------------------------------------------------------------------------------------------
control_files                  /home/oracle/app/oracle/oradata/orcl1/control01.ctl
db_create_file_dest            /home/oracle/app/oracle/oradata/orcl1/
instance_name                  orcl
service_names                  orcl1
background_dump_dest           /home/oracle/app/oracle/product/11.2.0/orcl1/log/diag/rdbms/orcl1/orcl/trace
user_dump_dest                 /home/oracle/app/oracle/product/11.2.0/orcl1/log/diag/rdbms/orcl1/orcl/trace
core_dump_dest                 /home/oracle/app/oracle/product/11.2.0/orcl1/log/diag/rdbms/orcl1/orcl/cdump
audit_file_dest                /home/oracle/app/oracle/product/11.2.0/orcl1/rdbms/audit
db_name                        orcl
db_unique_name                 orcl1
dg_broker_config_file1         /home/oracle/app/oracle/product/11.2.0/orcl1/dbs/dr1orcl1.dat
dg_broker_config_file2         /home/oracle/app/oracle/product/11.2.0/orcl1/dbs/dr2orcl1.dat
diagnostic_dest                /home/oracle/app/oracle/product/11.2.0/orcl1/log

13 rows selected.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ ORACLE_SID=orcl

[oracle@localhost ~]$ ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/orcl2

[oracle@localhost ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.2.0 Production on Thu Jan 10 01:46:52 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set linesize 200
SQL> set pagesize 100
SQL> column name format a30
SQL> column value format a100
SQL> select name, value from v$parameter where value like '%orcl%';

NAME                           VALUE
------------------------------ ----------------------------------------------------------------------------------------------------
control_files                  /home/oracle/app/oracle/oradata/orcl2/control01.ctl
db_create_file_dest            /home/oracle/app/oracle/oradata/orcl2/
instance_name                  orcl
service_names                  orcl2
background_dump_dest           /home/oracle/app/oracle/product/11.2.0/orcl2/log/diag/rdbms/orcl2/orcl/trace
user_dump_dest                 /home/oracle/app/oracle/product/11.2.0/orcl2/log/diag/rdbms/orcl2/orcl/trace
core_dump_dest                 /home/oracle/app/oracle/product/11.2.0/orcl2/log/diag/rdbms/orcl2/orcl/cdump
audit_file_dest                /home/oracle/app/oracle/product/11.2.0/orcl2/rdbms/audit
db_name                        orcl
db_unique_name                 orcl2
dg_broker_config_file1         /home/oracle/app/oracle/product/11.2.0/orcl2/dbs/dr1orcl2.dat
dg_broker_config_file2         /home/oracle/app/oracle/product/11.2.0/orcl2/dbs/dr2orcl2.dat
diagnostic_dest                /home/oracle/app/oracle/product/11.2.0/orcl2/log

13 rows selected.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Categories: DBA, Linux, Oracle, Unix
  1. January 8, 2013 at 4:44 pm

    Interesting! Good catch, Iggy.

  2. Iggy Fernandez
    January 9, 2013 at 11:17 pm

    Reblogged this on So Many Oracle Manuals, So Little Time and commented:
    It has been understood for a while that it is possible to create two instances with the same ORACLE_SID on one Unix server (https://groups.google.com/d/topic/comp.databases.oracle.server/Vs1n-LyptxA/discussion). However, it was supposed that two physically separate Oracle home directories were necessary for reasons that could not be explained. The above explanation and demonstration make it clear that the value of the ORACLE_HOME string variable is what matters and that physically separate Oracle home directories are not necessary.

  3. Sybrand Bakker
    January 11, 2013 at 4:01 pm

    The observation you *can* abuse Oracle to do things Oracle doesn’t recommend, does not imply you *should* abuse Oracle to do things Oracke doesn’t recommend. The above demonstration makes clear you have scruples in providing very bad and unsound advice to the Oracle commiunty. I recommend you *remove* this blog entry *asap*.

    • Mike Boligan
      May 13, 2013 at 5:17 am

      Sybrand,

      Relax. He is not telling anyone to run this as a production setup. In fact maybe you missed this part ” As an academic exercise just to prove the point…”. This is a good way to learn. This is not a reflection on Iggy’s scruples (I do not know or have never met him). What advice does he provide that is unsound or bad?

  1. January 10, 2013 at 11:01 pm

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

%d bloggers like this: