Time to start securing the listeners

I decided to apply basic security issues to all of my databases listener processes. As always i prefer Oracle Documentation for a reliable reference, this time i found a detailed white paper on integrigy web site which has a lot of hits from external links.

Both two resources call attention for two most important issues. Setting a password and Logging for the listener. The other issues can be taken in account are securing lsnrctl and tnslsnr executables in $ORACLE_HOME/bin/ directory, setting ADMIN_RESTRICTIONS parameter of the listener itself, changing default port of the listener utility and so on mentioned in the referenced documents. But here i decided to secure most common tasks of setting a password for listener and logging the listener activity for my initial action.

Setting a listener password:

-bash-3.00$ lsnrctl

LSNRCTL for Linux: Version 10.2.0.3.0 - Production on 03-NOV-2008 14:06:01

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> CHANGE_PASSWORD
Old password: <if not set then just hit enter>
New password: 
Reenter new password: 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=foa-op)(PORT=1521)))
Password changed for LISTENER
The command completed successfully
LSNRCTL> SAVE_CONFIG
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=foa-op)(PORT=1521)))
Saved LISTENER configuration parameters.
Listener Parameter File   /oracle/product/10.2.0/OFADB/network/admin/listener.ora
Old Parameter File   /oracle/product/10.2.0/OFADB/network/admin/listener.bak
The command completed successfully
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=foa-op)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.3.0 - Production
Start Date                03-NOV-2008 11:12:23
Uptime                    0 days 2 hr. 54 min. 43 sec
Trace Level               off
Security                  ON: Password or Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/product/10.2.0/OFADB/network/admin/listener.ora
Listener Log File         /oracle/product/10.2.0/OFADB/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=foa-op)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "OFADB" has 1 instance(s).
  Instance "OFADB", status READY, has 1 handler(s) for this service...
Service "OFADBXDB" has 1 instance(s).
  Instance "OFADB", status READY, has 1 handler(s) for this service...
Service "OFADB_XPT" has 1 instance(s).
  Instance "OFADB", status READY, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> exit

Logging the listener:

-bash-3.00$ lsnrctl

LSNRCTL for Linux: Version 10.2.0.3.0 - Production on 03-NOV-2008 11:18:43

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> SET LOG_DIRECTORY /oracle/product/10.2.0/OFADB/network/admin/lsnr_log
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=foa-op)(PORT=1521)))
LISTENER parameter "log_directory" set to /oracle/product/10.2.0/OFADB/network/admin/lsnr_log
The command completed successfully
LSNRCTL> set log_file lsnr.log
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=foa-op)(PORT=1521)))
LISTENER parameter "log_file" set to lsnr.log
The command completed successfully
LSNRCTL> set log_status on
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=foa-op)(PORT=1521)))
LISTENER parameter "log_status" set to ON
The command completed successfully
LSNRCTL> SAVE_CONFIG
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=foa-op)(PORT=1521)))
Saved LISTENER configuration parameters.
Listener Parameter File   /oracle/product/10.2.0/OFADB/network/admin/listener.ora
Old Parameter File   /oracle/product/10.2.0/OFADB/network/admin/listener.bak
The command completed successfully
LSNRCTL> exit
-bash-3.00$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.3.0 - Production on 03-NOV-2008 11:20:08

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=foa-op)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.3.0 - Production
Start Date                03-NOV-2008 11:12:23
Uptime                    0 days 0 hr. 7 min. 44 sec
Trace Level               off
Security                  ON: Password or Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/product/10.2.0/OFADB/network/admin/listener.ora
Listener Log File         /oracle/product/10.2.0/OFADB/network/admin/lsnr_log/lsnr.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=foa-op)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "OFADB" has 1 instance(s).
  Instance "OFADB", status READY, has 1 handler(s) for this service...
Service "OFADBXDB" has 1 instance(s).
  Instance "OFADB", status READY, has 1 handler(s) for this service...
Service "OFADB_XPT" has 1 instance(s).
  Instance "OFADB", status READY, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

But while i was monitoring the log file of the listener i found some warning messages right before every server process created by listener. The warning is something like;

WARNING: Subscription for node down event still pending

There are some comments on forums for this warning message but the general sentence is setting the

SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener_name>

parameter to OFF in listener.ora file. By disabling this option we prevent listener to automatically registers itself to a notification service as it is unsuccessful to do because there is not one installed. Setting this parameter to OFF is worked for my environment and warning messages are cleared. Following you can find my final listener.ora file.

-bash-3.00$ cat listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /oracle/product/10.2.0/OFADB)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxx-xx)(PORT = xxxx))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

#----ADDED BY TNSLSNR 03-NOV-2008 11:08:38---
PASSWORDS_LISTENER = xxxxxxxxxxxxxxxx
LOGGING_LISTENER = ON
#--------------------------------------------

#----ADDED BY TNSLSNR 03-NOV-2008 11:19:57---
LOG_FILE_LISTENER = lsnr.log
LOG_DIRECTORY_LISTENER = /oracle/product/10.2.0/OFADB/network/admin/lsnr_log
#--------------------------------------------

SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF

Another handy operation of setting the password for the listener is remote administration option. The following demonstration is from my client computer to administer the server listener remotely. In a nutshell if you now the ip and port that the listeners runs on and also know the password, then you can remotely administer you listener.

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\>lsnrctl status 10.4.38.32:1521

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 03-NOV-2008 14:36:02

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

Connecting to (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=10.4.38.32))(ADDRESS=(PRO
TOCOL=TCP)(HOST=10.4.38.32)(PORT=1521)))
TNS-01169: The listener has not recognized the password
 TNS-01189: The listener could not authenticate the user

C:\>lsnrctl

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 03-NOV-2008 14:36:05

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> set password
Password:
The command completed successfully
LSNRCTL> status 10.4.38.32:1521
Connecting to (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=10.4.38.32))(ADDRESS=(PRO
TOCOL=TCP)(HOST=10.4.38.32)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.3.0 - Production
Start Date                03-NOV-2008 11:12:23
Uptime                    0 days 3 hr. 24 min. 0 sec
Trace Level               off
Security                  ON: Password or Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/product/10.2.0/OFADB/network/admin/listener.ora
Listener Log File         /oracle/product/10.2.0/OFADB/network/admin/lsnr_log/lsnr.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=foa-op)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "OFADB" has 1 instance(s).
  Instance "OFADB", status READY, has 1 handler(s) for this service...
Service "OFADBXDB" has 1 instance(s).
  Instance "OFADB", status READY, has 1 handler(s) for this service...
Service "OFADB_XPT" has 1 instance(s).
  Instance "OFADB", status READY, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>

Querying Oracle with C# and odp.net

Oracle recommends odp.net (Oracle Data Provider for .net) for opening and managing connections to an Oracle Database via .NET development environment. Odp.net has its own connection and command objects as well as dataadapter and datareader structures optimized for Oracle database. Here you can find the simple way of connecting and running a command on an Oracle Database v10.2.0.3.

First of all, ODAC should be downloaded from Oracle 's odp.net download page. After installing the software according to the installation instructions you can follow the following snapshots to include .dll file in Microsoft Visual C# IDE. After that we are able to use the classes in that dll.




After importing the .dll file successfully you should easily use the classes.

/*
 * Defining variables
 */

// Connection Object 
private Oracle.DataAccess.Client.OracleConnection ocnn;

// Command object
private Oracle.DataAccess.Client.OracleCommand ocmd;

// Structures to manipulate data
private Oracle.DataAccess.Client.OracleDataAdapter oda;
private Oracle.DataAccess.Client.OracleDataReader odr;
private DataSet ods;


// ------------------------------------------------------------
// After all the definitions are made a sample try-catch block 
// that writes the sysdate to a label should be as follows
// ------------------------------------------------------------

try
    {
 // Creating the connection object
 ocnn = new Oracle.DataAccess.Client.OracleConnection();

 // Defining the connection string to connect to the specified database
 // User id and Password informations are obvious. Data Source value is the TNS alias of the database
 // There can be different values that can be used. Next example will cover another value.
 ocnn.ConnectionString = "User id=epeker;Password=********;Data Source=GRID_10.4.46.70";

 // Opening the connection and information is written as a label text
 ocnn.Open();
 lblerr.Text = "connected";

 // Now we need a command object that we want to execute on the database
 ocmd = new Oracle.DataAccess.Client.OracleCommand();

 // we defşne the previously created connection as the connection property of the command
 ocmd.Connection = ocnn;

 // commandText will be run on the database
 ocmd.CommandText = "select sysdate from dual";

 // with ExecuteReader() method of the command object we run the command and return the result to a reader object.
 odr = ocmd.ExecuteReader();

 // one or more rows could be fetched from the database
 // so we are readering from the reader of all rows
 while (odr.Read())
 {
     lblerr.Text = lblerr.Text + '-' + odr[0].ToString();
 }

    }
    catch (Exception ex)
    {
 // if an exception raises we catch it here and write to our info label
 lblerr.Text = ex.Message;
    }
    finally 
    { 
 // finally, we cleaning and tidying the memory and the database connections
 if (ocnn != null)
 {
   if (ocnn.State != ConnectionState.Closed)
   {
       ocnn.Close();
       ocnn = null;
   }
 }
    }


// ------------------------------------------------------------------------------------------------
// This block uses standart Net8 connection string instead of a TNS alias and also demonstrates
// the bind variable usage
// ------------------------------------------------------------------------------------------------

try
    {
 ocnn = new Oracle.DataAccess.Client.OracleConnection();
 ocnn.ConnectionString = "User id=epeker;Password=********;Data Source=(DESCRIPTION= " +
    " (ADDRESS= " +
    " (PROTOCOL=TCP)" +
    " (HOST=10.4.46.70)" +
    " (PORT=1521))" +    
    " (CONNECT_DATA=(SERVICE_NAME=GRID)))";
 ocnn.Open();
 lblerr.Text = "connected";
 
 ods = new DataSet();
 oda = new Oracle.DataAccess.Client.OracleDataAdapter();

 ocmd = new Oracle.DataAccess.Client.OracleCommand();                
 string str_sql = "select * from v$datafile where file# = :f_number ";

 ocmd.CommandType = CommandType.Text;
 ocmd.CommandText = str_sql;
 ocmd.Connection = ocnn;
 
 oda.SelectCommand = ocmd;
 
 // this parameter can also be set at the command level
 oda.SelectCommand.Parameters.Add("f_number",
   Oracle.DataAccess.Client.OracleDbType.Int32,15).Value=2;                

 // another difference is dataset object
 // in the previous example we used datareader to read from the database which fetches the data row by row basis
 // Fill() method of the dataadapter object fetches all the recordset to the client side at one time
 oda.Fill(ods);

 // i will not cover the details of the dataadapter and dataset here
 lblerr.Text = ods.Tables[0].Rows[0]["name"].ToString(); 
    }
    catch (Exception ex)
    {
 lblerr.Text = ex.Message;
    }
    finally
    {
 if (ocnn != null)
 {
     if (ocnn.State != ConnectionState.Closed)
     {
  ocnn.Close();
  ocnn = null;
     }
 }
    }

// ----------------------------------------------------------------------------
// last example demonstrates how to call a Oracle stored procedure with odp.net
// ----------------------------------------------------------------------------

try
    {
     ocnn = new Oracle.DataAccess.Client.OracleConnection();
 ocnn.ConnectionString = "User id=epeker;Password=********;Data Source=GRID_10.4.46.70;";
 ocnn.Open();
 lblerr.Text = "connected";

 oda = new Oracle.DataAccess.Client.OracleDataAdapter();
 ods = new DataSet();

 ocmd = new Oracle.DataAccess.Client.OracleCommand();
 ocmd.Connection = ocnn;
 ocmd.CommandText = "epeker.p_return_cursor";

 // this time command type is a StoredProcedure
 ocmd.CommandType = CommandType.StoredProcedure;

 // this time parameters have directions
 ocmd.Parameters.Add("c_return", 
   Oracle.DataAccess.Client.OracleDbType.RefCursor).Direction = ParameterDirection.Output;
 ocmd.Parameters.Add("i_var", 
   Oracle.DataAccess.Client.OracleDbType.Int32).Direction = ParameterDirection.Input;
 ocmd.Parameters["i_var"].Value = 2;

 oda.SelectCommand = ocmd;

 oda.Fill(ods);

 // gw_result is a gridview object of .NET framework
 // helps to view the recordset as a table
 gw_result.DataSource = ods.Tables[0];
 gw_result.Refresh();

    }
    catch (Exception ex)
    {
 lblerr.Text = ex.Message;
    }
    finally
    {
 if (ocnn != null)
 {
     if (ocnn.State != ConnectionState.Closed)
     {
  ocnn.Close();
  ocnn = null;
     }
 }
    }


/*
---------------------------------------------
--stored procedure used with the last example
---------------------------------------------

-- package needed for the ref_type declaration
CREATE OR REPLACE package EPEKER.pck_ref_type as 
  type ref_type is ref cursor; 
end pck_ref_type;

-- procedure itself
CREATE OR REPLACE procedure EPEKER.p_return_cursor 
  (c_return out epeker.pck_ref_type.ref_type, i_var in number)
is
begin

  open c_return for
    select * from v$datafile where file#=i_var;

  exception
    when others then
      rollback;

end p_return_cursor;
*/

Some other articles for .NET
odp.net homepage

sql loader performance experiences

Lots of the reporting databases has data loading scripts, using oracle SQL Loader to load mass amount of data from a text file. Loaded data is used for reporting purposes in day time. Commonly reported problem is long running loader scripts and solving this kind of problems has genarally similar approaches.

1- Dropping unnecessary indexes improves the performance of the batch loader operations hence every insert needs to update the indexes after updating the table. Fewer indexes means fewer operation for the DML statements. In my databases i examine v$sql, v$sqlarea, v$sqltext and dba_hist_sqltext views to examine the daily routine of the executed sql statements on the database to find out the index usage percentage of the statements. This gives a brief information about the index usage and to decide if i can drop any.

2- Using nologging option for the tables generates less redo logs. If the database in archive mode then less redo means less work for the archiver. log writer related wait events can also be improved. Using this option causes database not to log the operation and database admin should be cautious about the backup and restore operations. Bacause a recover operation may not recover your nologging tables data. I prefer full and incremental backups with rman in this kind of databases.

3- By setting Direct=true option of the sqlloader direct can be enabled, which loads data directly to datafiles by bypassing most of the database operations.

4- Sometimes table data is deleted periodically. This delete operation fragments the segments of the table and indexes of the table. Moving the tables and the indexes defragments the segments. If the table is fragmented, i prefer dropping the indexes first and move the table and then recreate the indexes again. One of my early postings covers this topic.

There are some other parameters; rows (row size to be commited) and unrecoverable (if used with direct=true option, redo writes are bypassed) parameters for further enhancement for loader performance. But i frequently use the mentioned parameters and they worked more than enough for most cases.