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
Recent Comments