Archive

Archive for the ‘Unix’ Category

Two instances with the same ORACLE_SID on one Unix server

Iggy Fernandez:

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.

Originally posted on So Many Oracle Manuals, So Little Time:

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 2,294 more words

Categories: DBA, Oracle, Unix

Two instances with the same ORACLE_SID on one Unix server

January 8, 2013 5 comments

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

I sed awk!

April 20, 2012 2 comments

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

Sed – An Introduction and Tutorial

Awk – An Introduction and Tutorial

Categories: Oracle, Unix
Follow

Get every new post delivered to your Inbox.

Join 760 other followers

%d bloggers like this: