Archive
Two instances with the same ORACLE_SID on one Unix server
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.
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…
View original post 619 more words
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
I sed awk!
Oracle DBAs most often find themselves working on Unix platforms and therefore some familiarity with Unix scripting is helpful. Utilities like “sed,” “awk,” and “grep” are often used in Unix scripts.
- Sed is a “stream editor” which filters and transforms the input stream.
- Awk is a pattern scanning and processing language named after Unix legends Alfred Aho, Peter Weinberger, and Brian Kernighan. From Alfred Aho’s interview in Computerworld: “A file is treated as a sequence of records, and by default each line is a record. Each line is broken up into a sequence of fields, so we can think of the first word in a line as the first field, the second word as the second field, and so on. An AWK program is of a sequence of pattern-action statements. AWK reads the input a line at a time. A line is scanned for each pattern in the program, and for each pattern that matches, the associated action is executed.”
- Grep prints lines containing the pattern of interest and its name comes from the editing command “g/re/p” where g indicates global scope, re is a “regular expression,” and p is the print command.
When our research colleagues saw the three of us in one or another’s office, they’d walk by the open door and say ‘AWK! AWK!’. So, we called the language AWK because of the good natured ribbing we received from our colleagues.—Alfred Aho
Here is an example which uses all these three utilities in a single multi-line Unix command. You are given the following log file (imp.log) of the Oracle import utility. The log file shows some errors in enabling constraints. You are only interested in foreign key constraints. Also, you want to print them nicely with each error printed on a single line (instead of being broken up into multiple lines as in the log file).
Import: Release 11.2.0.2.0 - Production on XXX XXX XX XX:XX:XX XXXX
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
Warning: the objects were exported by XXXXX, not by you
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing XXXXX's objects into XXXXXX
IMP-00017: following statement failed with ORACLE error 2264:
"ALTER TABLE "XXXXXXXXXXXXXXXXXXXXXXXXXXXXX" ADD CONSTRAINT "XXXXXXXXXXXXXXX"
"XXXXXXX" CHECK (XXXXXXXXXXXXXXXXXX IN ('XXXXX','XXXX')) ENABLE NOVALIDATE"
IMP-00003: ORACLE error 2264 encountered
ORA-02264: name already used by an existing constraint
IMP-00017: following statement failed with ORACLE error 942:
"ALTER TABLE "XXXXXXXXXXXXXXXXXXXXXXXXXXXXX" ADD CONSTRAINT "XXXXXXXXXXXXXXX"
"XXXXXXX" FOREIGN KEY ("XXXXXXXXXXX") REFERENCES "XXXXXXXXXXXXXXXXX" ("XXXXX"
"X") DISABLE NOVALIDATE"
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
About to enable constraints...
Import terminated successfully with warnings.
First, let’s extract blocks of lines which begin with a line containing the characters “ADD CONSTRAINT” (anywhere in the line) and ending before the line containing the characters “IMP-00003” (at the beginning of the line). Let’s also remove the space which occurs at the beginning of lines and the double quotation marks. All this can be done by the sed utility.
sed -n -e ' # invoke the stream editor "sed"; the -n flag suppresses the default printing action of sed
/ADD CONSTRAINT/,/^IMP-00003/ { # block of commands to execute for each line in a range of lines
# the first line should contain the characters "ADD CONSTRAINT" (anywhere within the line)
# the last line should contain the characters "IMP-00003" at the beginning of the line (as indicated by the ^ character)
s/"//g # remove quote marks from each line
s/^ //g # remove single blank spaces if they occur at the beginning of the line
p # print the line
}
' imp.log # the end of the sed processing
The results are as follows:
ALTER TABLE XXXXXXXXXXXXXXXXXXXXXXXXXXXXX ADD CONSTRAINT XXXXXXXXXXXXXXX
XXXXXXX CHECK (XXXXXXXXXXXXXXXXXX IN ('XXXXX','XXXX')) ENABLE NOVALIDATE
ALTER TABLE XXXXXXXXXXXXXXXXXXXXXXXXXXXXX ADD CONSTRAINT XXXXXXXXXXXXXXX
XXXXXXX FOREIGN KEY (XXXXXXXXXXX) REFERENCES XXXXXXXXXXXXXXXXX (XXXXX
Next let’s concatenate lines until each ALTER TABLE command occupies a single line. This can be done by piping the previous results to the awk utility.
sed -n -e '
/ADD CONSTRAINT/,/^IMP-00003/ {
s/"//g
s/^ //g
p
}
' imp.log | awk ' # pipe the previous results to the pattern matching and processing utility "awk"
/^IMP-00003/ { # block of commands to perform when the characters "IMP-00003" are found at the start of a line
print line # print the contents of the line variable if it is non-empty
line="" # reinitialize the line variable
} # the end of the command block
!/^IMP-00003/ { # block of commands to perform when the characters "IMP-00003" are not found at the start of a line
# the ! character indicates negation
line=line $0 # concatenate the contents of the line variable and the current line from the input stream
} # the end of the command block
' # the end of the awk processing
The results are as follows:
ALTER TABLE XXXXXXXXXXXXXXXXXXXXXXXXXXXXX ADD CONSTRAINT XXXXXXXXXXXXXXXXXXXXXX CHECK (XXXXXXXXXXXXXXXXXX IN ('XXXXX','XXXX')) ENABLE NOVALIDATE
ALTER TABLE XXXXXXXXXXXXXXXXXXXXXXXXXXXXX ADD CONSTRAINT XXXXXXXXXXXXXXXXXXXXXX FOREIGN KEY (XXXXXXXXXXX) REFERENCES XXXXXXXXXXXXXXXXX (XXXXXX) DISABLE NOVALIDATE
The last thing to do is to eliminate lines which do not contain the phrase “FOREIGN KEY”. We could have done this during the awk processing but, just as an exercise, let’s pipe the previous results to the grep utility to perform the filtering. We’re now ready to put everything together.
sed -n -e ' # invoke the stream editor "sed"
/ADD CONSTRAINT/,/^IMP-00003/ {
s/"//g
s/^ //g
p
}
' imp.log | awk ' # pipe the previous results to the pattern matching and processing utility "awk"
/^IMP-00003/ {
print line
line=""
}
!/^IMP-00003/ {
line=line $0
}
' | grep 'FOREIGN KEY' # pipe the previous results to the grep utility; print lines that contain the phrase "FOREIGN KEY"
The results are as follows. Errors not relating to foreign key constraints have been eliminated and each error is printed on a single line (instead of being broken up into multiple lines as in the original log file).
ALTER TABLE XXXXXXXXXXXXXXXXXXXXXXXXXXXXX ADD CONSTRAINT XXXXXXXXXXXXXXXXXXXXXX FOREIGN KEY (XXXXXXXXXXX) REFERENCES XXXXXXXXXXXXXXXXX (XXXXXX) DISABLE NOVALIDATE
Addendum: As Jamerfort demonstrated in his comments, it is possible to perform this task using sed only because sed offers some rudimentary programming capabilities such as branching (GOTO). My own attempt is shown below. Note that I only deleted the quotation marks at the beginning and end of each line; I realized that the quotation marks within the lines should be retained.
sed -n -e '
/ADD CONSTRAINT/ { # This is the block of commands to execute for lines containing the characters "ADD CONSTRAINT".
s/^ "//g # Delete the space and quotation mark at the beginning of the line.
s/"$//g # Delete the quotation mark at the end of the line.
h # Overwrite the contents of the "hold space" with the contents of the "pattern space" (the current line).
n # Read the next line; stop the program if no more lines are found.
:loop # Perform the following commands in a loop.
/^IMP-00003/ { # This the the block of commands to execute for lines containing the characters "IMP-00003" at the beginning of the line.
g # Overwrite the contents of the pattern space with the contents of the hold space.
s/\n//g # Remove all newline characters from the pattern space.
/FOREIGN KEY/p # Print the pattern space if it contains the characters "FOREIGN KEY".
b endloop # Branch to the end of the loop.
} # This is the end of the command block.
s/^ "//g # Delete the space and quotation mark at the beginning of the line.
s/"$//g # Delete the quotation mark at the end of the line.
H # Append the pattern space (the current line) to the hold space.
n # Read the next line; stop the program if no more lines are found.
b loop # Branch to the beginning of the loop.
:endloop # This is the end of the loop.
} # This is the end of the command block.
' imp.log
Here are the explanations of the very cryptic one-character sed commands used in the above example. A complete list can be obtained using the command “man sed” on a Unix machine.
n next line h hold this line (discarding whatever else there may be in the hold space) H hold this line (without discarding whatever else there may be in the hold space) g get back the contents of the hold space b branch s substitute p print
The results are as follows:
ALTER TABLE "XXXXXXXXXXXXXXXXXXXXXXXXXXXXX" ADD CONSTRAINT "XXXXXXXXXXXXXXXXXXXXXX" FOREIGN KEY ("XXXXXXXXXXX") REFERENCES "XXXXXXXXXXXXXXXXX" ("XXXXXX") DISABLE NOVALIDATE
Conversely, the task can be performed using awk only because awk offers variables, conditional processing, and a rich collection of functions such as length, substr, and index (instr).
awk '
/ADD CONSTRAINT/ {
line=""
flag="Y"
}
/^IMP-00003/ {
if(flag=="Y") {
if (index(line, "FOREIGN KEY")!=0) print line
line=""
flag="N"
}
}
{
if (flag=="Y") line=line substr(substr($0, 1, length($0)-1), 3)
}
' imp.log
The results don’t change.
ALTER TABLE "XXXXXXXXXXXXXXXXXXXXXXXXXXXXX" ADD CONSTRAINT "XXXXXXXXXXXXXXXXXXXXXX" FOREIGN KEY ("XXXXXXXXXXX") REFERENCES "XXXXXXXXXXXXXXXXX" ("XXXXXX") DISABLE NOVALIDATE
So Many Oracle Manuals, So Little Time 
Recent Comments