Monthly Archives: May 2010

11g, Oracle, Oracle 10g

Oracle – UTL_MATCH.EDIT_DISTANCE_SIMILARITY string comparison

Leave a comment

Oracle provides the procedure UTL_MATCH to compare the difference between to two sets of strings. In this article we will examine the function EDIT_DISTANCE_SIMILARITY which returns the percentage of matching strings: 0 meaning no similarity and 100 meaning complete similarity.

1. Logon to your Oracle database server as the Oracle software owner.

2. Logon to SQLPLUS with SYSDBA privileges.

mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Tues May 25 19:32:21 ge2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

3. Comparing the same strings: ‘The First Dog’ and ‘The First Dog’

select utl_match.edit_distance_SIMILARITY(‘The First Dog’,’The First Dog’) from dual;

SQL*PLUS Output:

SQL> select utl_match.edit_distance_SIMILARITY(‘The First Dog’,’The First Dog’) from dual;

UTL_MATCH.EDIT_DISTANCE_SIMILARITY(‘THEFIRSTDOG’,’THEFIRSTDOG’)
—————————————————————
100

SQL>

The stings are a 100% match.

4. Comparing strings with no simularity: ‘The First Dog’,’1234567890123′

select utl_match.edit_distance_SIMILARITY(‘The First Dog’,’1234567890123′) from dual;

SQL*PLUS Output:

SQL> select utl_match.edit_distance_SIMILARITY(‘The First Dog’,’1234567890123′) from dual;

UTL_MATCH.EDIT_DISTANCE_SIMILARITY(‘THEFIRSTDOG’,’1234567890123′)
—————————————————————–
0

SQL>

The strings are a 0% match.

5. Comparing strings of varying case: ‘The First Dog’,’tHE fIRST dOG’

select utl_match.edit_distance_SIMILARITY(‘The First Dog’,’tHE fIRST dOG’) from dual;

SQL*PLUS Output:

SQL> select utl_match.edit_distance_SIMILARITY(‘The First Dog’,’tHE fIRST dOG’) from dual;

UTL_MATCH.EDIT_DISTANCE_SIMILARITY(‘The First Dog ‘, ‘tHE fIRST dOG’)
—————————————————————-
16

The strings have a 16% match, due to function being case sensitive.

6. Comparing strings with an off-set: ‘The First Dog’ and ‘-The First Dog’

select utl_match.edit_distance_SIMILARITY(‘The First Dog’,’-The First Dog’) from dual;

SQL*PLUS Output:

SQL> select utl_match.edit_distance_SIMILARITY(‘The First Dog’,’-The First Dog’)
from dual;

UTL_MATCH.EDIT_DISTANCE_SIMILARITY(‘THEFIRSTDOG’,’-THEFIRSTDOG’)
—————————————————————-
93

SQL>

The strings have a 93% match due to offsetting having no affect on comparison operation.

This completes the use of EDIT_DISTANCE function for the Oracle RDBMS procedure UTL_MATCH.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g

Oracle – UTL_MATCH.EDIT_DISTANCE string comparison

Leave a comment

Oracle – UTL_MATCH.EDIT_DISTANCE

Oracle provides the procedure UTL_MATCH to compare the difference between to two sets of strings. In this article we will examine the function EDIT_DISTANCE which returns the number of changes required in a string comparison to make the strings identical.

1. Logon to your Oracle database server as the Oracle software owner.

2. Logon to SQLPLUS with SYSDBA privileges.

mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Mon May 24 21:41:18 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

3. Comparing the same strings: ‘The First Dog’ and ‘The First Dog’

select utl_match.edit_distance(‘The First Dog’,’The First Dog’) from dual;

SQL*PLUS Output:

SQL> select utl_match.edit_distance(‘The First Dog’,’The First Dog’) from dual;

UTL_MATCH.EDIT_DISTANCE(‘THEFIRSTDOG’,’THEFIRSTDOG’)
—————————————————-
0

SQL>

The comparison returns a zero meaning no changes required to make the two strings match.

4. Comparing strings with no simularity: ‘The First Dog’ and ‘1234567890123’

select utl_match.edit_distance(‘The First Dog’,’1234567890123′) from dual;

SQL*PLUS Output:

SQL> select utl_match.edit_distance(‘The First Dog’,’1234567890123′) from dual;

UTL_MATCH.EDIT_DISTANCE(‘THEFIRSTDOG’,’1234567890123′)
——————————————————
13

SQL>

It would take 13 changes to make the strings match.

5. Comparing strings of variaring case: ‘The First Dog’ and ‘tHE fIRST dOG’

select utl_match.edit_distance(‘The First Dog’,’tHE fIRST dOG’) from dual;

SQL*PLUS Output:

SQL> select utl_match.edit_distance(‘The First Dog’,’tHE fIRST dOG’) from dual;

UTL_MATCH.EDIT_DISTANCE(‘THEFIRSTDOG’,’THEFIRSTDOG’)
—————————————————-
11

SQL>

The case of the charactors is critical, thus 11 changes are required to make the strings match.

6. Comparing strings with an off-set: ‘The First Dog’ and ‘-The First Dog’

select utl_match.edit_distance(‘The First Dog’,’Off Set Text The First Dog’) from dual;

SQL*PLUS Output:

SQL> select utl_match.edit_distance(‘The First Dog’,’Off Set Text The First Dog’) from dual;

UTL_MATCH.EDIT_DISTANCE(‘THEFIRSTDOG’,’OFFSETTEXTTHEFIRSTDOG’)
————————————————————–
13

SQL>

Off setting the text will still result in matches, thus in the example above, it would only take 13 changes to make the strings match as oppose to 26 changes.

This completes the use of EDIT_DISTANCE function for the Oracle RDBMS procedure UTL_MATCH.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, Oracle, Oracle 10g, sqlplus

Oracle – Determining Local Host name and IP address with UTL_INADDR procedure

Leave a comment

Oracle – Determining Local Host name and IP address with UTL_INADDR procedure

Oracle provides the procedure UTL_INADDR to retrieve your server’s host name and IP address from within the Oracle RDBMS. In this article we will demonstrate the retrieval of host name and IP address from within the RDBMS.

1. Logon to your Oracle database server as the Oracle software owner.

2. Logon to SQLPLUS with SYSDBA privileges.

mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Sat May 22 07:18:39 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

3. Execute the following PL/SQL block to retrieve the local hosts name and IP address.

SET serveroutput on
BEGIN
DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_NAME); — get local host name
DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_ADDRESS); — get local IP addr
END;
/

Output from SQL*PLUS

SQL> SET serveroutput on
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_NAME); — get local host name
3 DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_ADDRESS); — get local IP addr
4 END;
5 /
mylinux
192.168.0.110

PL/SQL procedure successfully completed.

SQL>

This completes retrieval of local host name and IP address of your Oracle RDBMS server.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle

Determination of Components installed on your Oracle RDBMS.

Leave a comment

Oracle provides the view DBA_REGISTRY which records all components installed on our RDBMS. This view provides more then just the components installed, but also: version, if it is valid, date of modification, etc. This information is very helpful in the administration of any RDBMS. This article covers the determination of what components are installed within our Oracle RDBMS.

1. Logon to your database server with SYSDBA privileges from your remote client.

C:\>sqlplus ljcatt/password5@orcl as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Fri Feb 19 19:24:41 2010

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

2. Execute the following SQL statement: select comp_name, version, status, modified from DBA_REGISTRY;

SQL> column comp_name format a35
SQL> column version format a12
SQL> column status format a8
SQL> select comp_name, version, status, modified from DBA_REGISTRY;

COMP_NAME VERSION STATUS MODIFIED
———————————– ———— ——– ————
Oracle Database Catalog Views 10.2.0.4.0 VALID 08-SEP-2009 13:50:15
Oracle Database Packages and Types 10.2.0.4.0 INVALID 08-SEP-2009 13:50:15
Oracle Workspace Manager 10.2.0.4.3 VALID 08-SEP-2009 13:50:15
JServer JAVA Virtual Machine 10.2.0.4.0 VALID 08-SEP-2009 13:50:15
Oracle XDK 10.2.0.4.0 VALID 08-SEP-2009 13:50:15
Oracle interMedia 10.2.0.4.0 VALID 08-SEP-2009 13:50:15
Spatial 10.2.0.4.0 VALID 08-SEP-2009 13:50:15
Oracle Text 10.2.0.4.0 VALID 08-SEP-2009 13:50:15
Oracle Ultra Search 10.2.0.4.0 INVALID 08-SEP-2009 13:50:20
Oracle XML Database 10.2.0.4.0 VALID 08-SEP-2009 13:50:20
Oracle Application Server Metadata 10.1.2.0.2 VALID 07-JUL-2006 02:52:19
Repository Version-R

Oracle Application Server Distribut 10.1.2.0.2 VALID 06-JUL-2006 23:34:41
ed Configuration Management

Oracle Business Intelligence Discov 10.1.2.0.2 VALID 06-JUL-2006 23:34:43
erer

Oracle Workflow 10.1.2.0.2 VALID 06-JUL-2006 23:37:16
Oracle Application Server Integrati 10.1.2.0.2 VALID 06-JUL-2006 23:40:19
on B2B

Oracle Application Server Integrati 10.1.2.0.2 VALID 06-JUL-2006 23:40:25
on BAM

Oracle Application Server Certifica 10.1.2.0.2 VALID 06-JUL-2006 23:40:42
te Authority

Oracle Internet Directory 10.1.2.0.2 VALID 06-JUL-2006 23:41:16
Oracle Application Server Single Si 10.1.2.0.2 VALID 06-JUL-2006 23:43:27
gn-On

Oracle Application Server Portal 10.1.2.0.2 VALID 07-JUL-2006 00:01:41
Oracle Application Server Syndicati 10.1.2.0.2 VALID 07-JUL-2006 00:01:55
on Services

Oracle Application Server UDDI Regi 10.1.2.0.2 VALID 07-JUL-2006 00:02:06
stry

Oracle Application Server Web Clipp 10.1.2.0.2 VALID 07-JUL-2006 00:02:17
ing

Oracle Application Server Wireless 10.1.2.0.2 VALID 07-JUL-2006 00:08:24
Oracle Database Java Packages 10.2.0.4.0 VALID 08-SEP-2009 13:50:20

25 rows selected.

SQL>

This completes determination of components installed on your Oracle RDBMS.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, hp-ux, itanium, linux, Oracle, Oracle 10g, oracle 9i, SQL, unix

Execution of Oracle SQL commands from within UNIX or LINUX shell scripts:

Leave a comment

In the management of Oracle RDBMS, we frequently have the need to develop shell scripts to manage our databases. This article demonstrates the use of BASH shell script to connect to an Oracle database and execute PL/SQL and SQL statements from within the Oracle database. The following procedure will work on UNIX and LINUX machines.

1. Logon to your Oracle database server as the Oracle software owner.

2. Use vi to create a new file with the name sql_shell_test.sh

mylinux:> vi sql_shell_test.sh
“sql_shell_test.sh” [New file]

~
~

3. Press the ‘a’ key once (for append operations) and past the following text into your file.

sqlplus ‘/ as sysdba’ <

4. Press the keys : –> w –> q and hit the return key to save and exit vi.

5. Execute the chmod command to change you file status to read_write_execute for owner and group

mylinux:> chmod 770 sql_shell_test.sh
mylinux:>

6. As the Oracle software owner, execute the shell script sql_shell_test.sh with the following command.

mylinux:>./sql_shell_test.sh

7. Perform an ls command and you can see that the script has generated the output file test_shell_script_output.lst.

mylinux:>ls -lrt
-rwxrwxrwx 1 oracle dba 148 May 13 03:30 sql_shell_test.sql
-rw-r–r– 1 oracle dba 1391 May 13 03:32 test_shell_script_output.lst
mylinux:>

8. Perform a cat command to see the output of file test_shell_script_output.lst.

mylinux:>cat test_shell_script_output.lst
SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
————— —————-
HOST_NAME
—————————————————————-
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
—————– ——— ———— — ———- ——- —————
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
———- — —————– —————— ——— —
1 orcl
frankie
10.2.0.4.0 13-MAY-10 OPEN NO 1 STOPPED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO

SQL> select name, open_mode from v$database;

NAME OPEN_MODE
——— ———-
ORCL READ WRITE

SQL> spool off
mylinux:>

This concludes the execution of Oracle SQL commands from within UNIX or LINUX shell scripts.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, audit, Oracle, Oracle 10g, oracle 9i

Removal of oracle auditing at the All levels:

Leave a comment

Oracle provides the ability to audit your database activities on a multitude of level which provides the administrator the ability to find suspicious activity. In a heavily used system auditing process can produce a large amount of data, thus it should be used sparingly. This article covers the removal of audit definitions from an Oracle RDBMS, without disabling your ability to perform auditing. This procedure will work on any OS.

NOTE: The initialization parameter AUDIT_TRAIL controls auditing at the entire database level and can be set to three definitions: 1. DB – audit trail in the database; 2. OS – audit trail on the OS; and 3. none – no auditing. In this procedure AUDIT_TRAIL must be set to DB or OS and the procedure does not shutdown auditing at the database level.

1. Auditing definitions can be seen in three views: DBA_OBJ_AUDIT_OPTS, DBA_PRIV_AUDIT_OPTS, and DBA_STMT_AUDIT_OPTS. We can use these views to generate the appropriate commands to remove the current audit definitions from our database.

2. Logon to your Oracle database server as the Oracle software owner.

3. Logon to SQLPLUS with sysdba privileges.

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Tue May 11 20:31:43 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

4. Execute the following PL/SQL block to list all auditing being performed.

———————————————————————–
Beginning of PL/SQL Block
———————————————————————–

set serveroutput on

declare

v_ct number;

begin

–this block removes all auditing from an oracle RDBMS system.

for v_stmt in(select ‘audit all by ‘ || user_name as stmt from
sys.dba_priv_audit_opts)
loop
dbms_output.put_line(v_stmt.stmt);
end loop;

for v_stmt in(select ‘audit ‘ ||audit_option||’ by ‘ ||user_name as stmt from
sys.dba_stmt_audit_opts)
loop
dbms_output.put_line(v_stmt.stmt);
end loop;

for v_stmt in(select ‘audit all on ‘ ||owner||’.’||object_name as stmt from
sys.dba_obj_audit_opts)
loop
dbms_output.put_line(v_stmt.stmt);
end loop;

end;
/
———————————————————————–
end of PL/SQL Block
———————————————————————–

———————————————————————–
output
———————————————————————–

audit all by LJCATT
audit all by LJCATT
audit CREATE SESSION by LJCATT
audit CREATE TABLE by LJCATT
audit all on LJCATT.TEST

PL/SQL procedure successfully completed.

SQL>
———————————————————————–
Ending of PL/SQL Block output
———————————————————————–

5. Once you have validated that you do wish to remove all of these auditing options, execute the following PL/SQL block:

—————————————————————————–
Beginning removal of Oracle auditing definitions PL/SQL Block
————————————————————————–

set serveroutput on

declare

v_ct number;

begin

–this block removes all auditing from an oracle RDBMS system.

for v_stmt in(select ‘noaudit all by ‘ || user_name as stmt from
sys.dba_priv_audit_opts)
loop
execute immediate(v_stmt.stmt);
end loop;

for v_stmt in(select ‘noaudit ‘ ||audit_option||’ by ‘ ||user_name as stmt from
sys.dba_stmt_audit_opts)
loop
execute immediate(v_stmt.stmt);
end loop;

for v_stmt in(select ‘noaudit all on ‘ ||owner||’.’||object_name as stmt from
sys.dba_obj_audit_opts)
loop
execute immediate(v_stmt.stmt);
end loop;

end;
/

——————————————————————————–
End removal of Oracle auditing definitions PL/SQL Block
——————————————————————————-
——————————————————————————-
OUPUT
——————————————————————————-

SQL> set serveroutput on
SQL>
SQL> declare
2
3 v_ct number;
4
5 begin
6
7 –this block removes all auditing from an oracle RDBMS system.
8
9 for v_stmt in(select ‘noaudit all by ‘ || user_name as stmt from
sys.dba_priv_audit_opts)
10 loop
11 execute immediate(v_stmt.stmt);
12 end loop;
13
14 for v_stmt in(select ‘noaudit ‘ ||audit_option||’ by ‘ ||user_name as stmt
from sys.dba_stmt_au
dit_opts)
15 loop
16 execute immediate(v_stmt.stmt);
17 end loop;
18
19 for v_stmt in(select ‘noaudit all on ‘ ||owner||’.’||object_name as stmt
from sys.dba_obj_audit
_opts)
20 loop
21 execute immediate(v_stmt.stmt);
22 end loop;
23
24 end;
25 /

PL/SQL procedure successfully completed.

SQL>
——————————————————————————-
End of OUPUT
——————————————————————————-

6. Execute the PL/SQL block from step 4 to validate that all Oracle auditing definitions have been removed from the RDBMS. As you can see no values are returned because they no longer exist.

SQL> declare
2
3 v_ct number;
4
5 begin
6
7 –this block removes all auditing from an oracle RDBMS system.
8
9 for v_stmt in(select ‘audit all by ‘ || user_name as stmt from
sys.dba_priv_audit_opts)
10 loop
11 dbms_output.put_line(v_stmt.stmt);
12 end loop;
13
14 for v_stmt in(select ‘audit ‘ ||audit_option||’ by ‘ ||user_name as stmt
from sys.dba_stmt_audi
t_opts)
15 loop
16 dbms_output.put_line(v_stmt.stmt);
17 end loop;
18
19 for v_stmt in(select ‘audit all on ‘ ||owner||’.’||object_name as stmt from
sys.dba_obj_audit_o
pts)
20 loop
21 dbms_output.put_line(v_stmt.stmt);
22 end loop;
23
24 end;
25 /

PL/SQL procedure successfully completed.

SQL>

That completes removal of all Oracle auditing from the RDBMS.

Larry J. Catt
oracle@allcompute.com
www.allcompute.com

audit, Oracle

Removal of all Oracle RDBMS audit records:

Leave a comment

Oracle provides the ability to audit your database activities on a multitude of level, providing the administrator the ability to find suspicious activity on the database. However, this audit trail can consume huge amounts of disk space and it is necessary to remove this information once it has become obsolete. This article covers the removal of all audit records within an Oracle RDBMS and will work regardless of OS type.

NOTE: This article covers the removal of audit records from the Oracle database, the initialization parameter of AUDIT_TRAIL must be set to DB in order for this procedure to work correctly.

1. Logon to your Oracle database server as the Oracle software owner.

2. Logon to your Oracle database as sysdba.

mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Sun May 09 13:11:07 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

3. Select from one of the audit tables to see how much audit data has been collected.

SQL> select count(*) from dba_audit_session;

COUNT(*)
———-
1364166

SQL>

4. As SYSDBA delete all audit trail records with the command: delete from sys.aud$;

SQL> DELETE FROM SYS.AUD$;

1364166 rows deleted.

SQL> commit;

Commit complete.

SQL>

5. Now perform a select from dba_audit_session view.

SQL> select count(*) from dba_audit_session;

COUNT(*)
———-
0

SQL>

This completes the removal of old audit records from within the Oracle database.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, hp-ux, itanium, linux, Oracle

Oracle variable ORACLE_HOME undefined:

Leave a comment

This article covers the errors you will receive from a Linux or UNIX OS if your ORACLE_HOME variable is not defined correctly and how to resolve it.

1. Initialize sqlplus without defining an ORACLE_HOME variable at the OS layer.

mylinux:> sqlplus ‘/ as sysdba’
Error 6 initializing SQL*Plus
Message file sp1.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

2. Set your ORACLE_HOME variable at the OS layer.

mylinux:> export ORACLE_HOME=/opt/app/oracle/10.2.0
mylinux:>

3. Oracle SQLPLUS starts up normally once it know where to find the Oracle Home software directory defined by the OS parameter ORACLE_HOME.

mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Wed May 5 14:52:47 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle

Oracle Universal Installer fails with error DISPLAY Variable:

Leave a comment

Oracle Universal Installer on UNIX and Linux normally uses a Java GUI (Graphical User Interface) to display installation options to the operator. I say normally, due to the option of a silent installation which does not require any type of GUI to install, however does require the configuration of certain files, before installation occurs. In this procedure we will cover the error message you will find if the DISPLAY variable is not properly setup and how to resolve.

1. Move to the location of you Oracle binary installer files in this case we have stored our Oracle binaries under /opt/oracle/software/linux/10.2.0.1/database

cd /opt/oracle/software/hp/10.2.0.1/database

2. List the files located under this directory.

myhpux:> ls
doc response stage
install runInstaller welcome.html
myhpux:>

3. Execute the runInstaller shell script to begin your installation.

myhpux:> ./runInstaller -ignoresysprereqs
Starting Oracle Universal Installer…

Checking installer requirements…

Checking operating system version: must be B.11.23. Actual B.11.31
Failed < <<< >>> Ignoring required pre-requisite failures. Continuing…

Preparing to launch Oracle Universal Installer from
/tmp/OraInstall2010-05-02_08-41-03AM. Please wait …
DISPLAY not set. Please set the DISPLAY and try again.
Depending on the Unix Shell, you can use one of the following commands as
examples to set the DISPLAY environment variable:
– For csh: % setenv DISPLAY 192.168.1.128:0.0
– For sh, ksh and bash: $ DISPLAY=192.168.1.128:0.0; export DISPLAY
Use the following command to see what shell is being used:
echo $SHELL
Use the following command to view the current DISPLAY environment variable
setting:
echo $DISPLAY
– Make sure that client users are authorized to connect to the X Server.
To enable client users to access the X Server, open an xterm, dtterm or xconsole
as the user that started the session and type the following command:
% xhost +
To test that the DISPLAY environment variable is set correctly, run a X11 based
program that comes with the native operating system such as ‘xclock’:
%
If you are not able to run xclock successfully, please refer to your PC-X Server
or OS vendor for further assistance.
Typical path for xclock: /usr/bin/X11/xclock

4. NOTE: the error message received references the lack of a defined DISPLAY variable, the OUI (Oracle Universal Installer) does not know where to send output to and thus fails.

5. To resolve this issue simple set your DISPLAY variable to the local host or client machine you are currently using. If using a remote machine you will need a tool to except the output from your server: VNC, Hummingbird, KEA!, etc will all work. NOTE: VNC is free for client use. Example: If my client machines IP is 192.168.0.110

myhpux:> export DISPLAY=192.168.0.110:0.0

6. Now re-execute the runInstaller variable again and you will be presented with the OUI GUI.

myhpux:> ./runInstaller -ignoresysprereqs
Starting Oracle Universal Installer…

Checking installer requirements…

Checking operating system version: must be B.11.23. Actual B.11.31
Failed < <<< >>> Ignoring required pre-requisite failures. Continuing…

Preparing to launch Oracle Universal Installer from
/tmp/OraInstall2010-05-02_08-54-42AM. Please wait …myhpux:> Oracle Universal
Installer, Version 10.2.0.1.0 Production
Copyright (C) 1999, 2005, Oracle. All rights reserved.

This completes correcting failure of OUI (Oracle Universal Installer) due to DISPLAY error.

Larry J Catt OCP 9i. 10g
oracle@allcompute.com
www.allcompute.com

11g, Oracle, Oracle 10g, oracle 9i

Listener Name incorrectly defined in lsnrctl

Leave a comment

Listener Name incorrectly defined in lsnrctl

Normally, connection to an Oracle database is performed through the use of an Oracle listener, whose job it is to listen on a specific machine and port for request to connect to a one or more database instances. The listener is controlled by a utility named lsnrctl located under $ORACLE_HOME/bin no matter the OS. By default Oracle will name you listener process LISTENER on a standard Oracle installation. However, this is not a good practice due to security concerns. In this article we will discuss the error you will receive if you have a non-default Listener name and specify the incorrect name while attempting to start the process. This article was written using a window’s based install but will work just as well on UNIX and Linux.

1. Switch directories to your $ORACLE_HOME/bin directory.

cd C:\oracle\product\10.2.0\db_1\bin
C:\oracle\product\10.2.0\db_1\BIN>

2. Start your listener utility with the command lsnrctl.

C:\oracle\product\10.2.0\db_1\BIN>lsnrctl

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 – Production on 01-MAY-2010 10:30
:17

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

Welcome to LSNRCTL, type “help” for information.

LSNRCTL>

3. Believing that your listener’s name is listener attempt to startup the listener with the command start

LSNRCTL> start listener
Starting tnslsnr: please wait…

Failed to open service , error 106
0.
TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 – Production
System parameter file is C:\oracle\product\10.2.0\db_1\network\admin\listener.or
a
Log messages written to C:\oracle\product\10.2.0\db_1\network\log\listener_orcl.
log
TNS-01151: Missing listener name, listener, in LISTENER.ORA

Listener failed to start. See the error message(s) above…

LSNRCTL>

4. NOTE: the error message returned by lsnrctl states: that we are missing the listener name in the file listener.ora.

5. Navigate to the directory given for listener.ora file and view its contents. You will see that the actual name of the listener is listener_oracle not listener.

LISTENER_ORACLE =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.110)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)

SID_LIST_LISTENER_ORACLE =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
)

(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
)

6. There is no reason to change the listener.ora file but only to give the correct listener name in the lsnrctl utilities start command. As shown below the listener startups correctly.

LSNRCTL> start listener_oracle
Starting tnslsnr: please wait…

TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 – Production
System parameter file is C:\oracle\product\10.2.0\db_1\network\admin\listener.or
a
Log messages written to C:\oracle\product\10.2.0\db_1\network\log\listener_oracl
e.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.110)(PORT=1521)
))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROCipc)
))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)
))
STATUS of the LISTENER
————————
Alias listener_oracle
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 – Produ
ction
Start Date 01-MAY-2010 11:04:00
Uptime 0 days 0 hr. 0 min. 1 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\oracle\product\10.2.0\db_1\network\admin\listener.o
ra
Listener Log File C:\oracle\product\10.2.0\db_1\network\log\listener_ora
cle.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.110)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROCipc)))
Services Summary…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
Service “orcl” has 1 instance(s).
Instance “orcl”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
LSNRCTL>

7. As shown above once we present the correct listener name, the listener starts without error.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com