Modifying the Listener Port for Database Control

A coworker was installing 10Gr2 on a windows server which already had an existing 9i environment. They wanted the database console to connect via the 10G listener (non-standard port 1522) and not the 9i (standard port 1521) to keep the environments separate.

I was able to find the following document which lists all of the default ports used by Enterprise Manager (10g).

Subject: Overview of Default Ports Used by EM 10g Grid Control, DB Control and AS Control
Doc ID: Note:235298.1

Search for "SQL*Net Listener for the 10g DB" and it will list the modifications required in order to change the listener port. The steps in this section assume you need to change the listener port as well. Since the listener was already created for me here are the steps I had to follow:

1. Stop DB Control by executing the command emctl stop dbconsole

2. Modify the agent to reflect the new port number. Edit $ORACLE_HOME/<hostname_sid>/sysman/emd/targets.xml

The instructions say you only need to modify the listener port value. However, I noticed that there are two entries in this file. One is for the database and another for the listener, each pointing to the default port of 1521. I'm not sure if I needed to change the database port but since the DBA wanted to keep both environments separate I changed it to be safe. I didn't test this procedure without making that change.

Here is a copy of the file (of course username and password values were modified. ;).

<Targets AGENT_SEED="39661000">
    <Target TYPE="oracle_emd" NAME="myserver.mydomain:1830"/>
    <Target TYPE="host" NAME="myserver.mydomain" DISPLAY_NAME="myserver.mydomain" VERSION="1.0"/>
    <Target TYPE="oracle_database" NAME="mydb10g" VERSION="1.0">
        <Property NAME="MachineName" VALUE="myserver"/>
        <Property NAME="Port" VALUE="1522"/>
        <Property NAME="SID" VALUE="mydb10g"/>
        <Property NAME="OracleHome" VALUE="D:\oracle\ora10g"/>
        <Property NAME="UserName" VALUE="1993cs3901f8d5af7" ENCRYPTED="TRUE"/>
        <Property NAME="password" VALUE="8c35Da9iks4e07bab7b6c5906a837f4" ENCRYPTED="TRUE"/>
    </Target>
    <Target TYPE="oracle_listener" NAME="LISTENER_myserver" VERSION="1.0">
        <Property NAME="Machine" VALUE="myserver"/>
        <Property NAME="LsnrName" VALUE="LISTENER10g"/>
        <Property NAME="Port" VALUE="1522"/>
        <Property NAME="OracleHome" VALUE="D:\oracle\ora10g"/>
        <Property NAME="ListenerOraDir" VALUE="D:\oracle\ora10g\network\admin"/>
    </Target>
</Targets

3. Modify $ORACLE_HOME/<hostname_sid>/sysman/config/emoms.properties to reflect the new port. Two entries have to be modified here as well. emdRepPort and emdRepConnectDescriptor should reflect the new port number.

Here are the modified entries with 1522 replacing the standard port 1521.

oracle.sysman.eml.mntr.emdRepPort=1522
oracle.sysman.eml.mntr.emdRepConnectDescriptor=(DESCRIPTION\=(ADDRESS_LIST\=(ADDRESS\=(PROTOCOL\=TCP)(HOST\=myserver)(PORT\=1522)))(CONNECT_DATA\=(SERVICE_NAME\=mydb10g)))


4. Last but not least start up database console with emctl start dbconsole and verify that you can login successfully.

Error cloning a clone

A few posts ago I hit an issue with executing the runInstaller. It still surprised me that after hundreds of installs you can still hit new errors. The other day I hit a new error while cloning an EBS environment. While preparing the environment for cloning (executing adpreclone.pl dbTier), the error below appeared:


RC-50409: Topology information required for cloning not found in the database. Make sure that
AutoConfig was run successfully prior to starting Rapid Clone, and try again
Raised by oracle.apps.ad.tools.AdCreateCtlFile
RC-50208: Exception in method gen_crctrfclone_sql
RC-50208: Exception in Constructor AdCreateCtlFile
Raised by oracle.apps.ad.tools.AdCreateCtlFile


This particular environment is a clone of our production environment. After it was built users had performed some work and wanted it cloned to another environment. There are two solutions for this particular error, which can be found in detail in Metalink Note: 376964.1.


The easiest solution is to run autoconfig at the dbTier level. The curious part is that since this environment is a clone of production, autoconfig would would have been executed as part of the post cloning steps (adcfgclone.pl dbTier). I checked my post cloning logs and indeed it completed successfully. I executed autoconfig ($ORACLE_HOME/appsutil/bin/adconfig.sh) on the database tier again and the preclone script finished without error.

HotSpot Virtual Machine Error : 11

I've been working with Oracle for quite a while now and have more than my share of installs under my belt. Today a co-worker emailed me with the following error produced by the 10.2.0.1 runInstaller.

All installer requirements met.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2007-10-23_11-51-47AM. Please wait ...$ Starting Installer in advanced mode ...
Oracle Universal Installer, Version 10.2.0.1.0 Production
Copyright (C) 1999, 2005, Oracle. All rights reserved.


Unexpected Signal : 11 occurred at PC=0xFECD9110
Function=[Unknown. Nearest: JVM_MonitorWait+0x1F24]
Library=/tmp/OraInstall2007-10-23_11-51-47AM/jre/1.4.2/lib/sparc/client/libjvm.so

Current Java thread:
at oracle.sysman.oii.oiip.osd.unix.OiipuUnixOps.chgrp(Native Method)

.
.
.

#
# HotSpot Virtual Machine Error : 11
# Error ID : 4F530E43505002EF 01
# Please report this error at
# http://java.sun.com/cgi-bin/bugreport.cgi
#
# Java VM: Java HotSpot(TM) Client VM (1.4.2_08-b03 mixed mode)
#
# An error report file has been saved as hs_err_pid27925.log.
# Please refer to the file for further information.

I'm still amazed that after hundreds of installs you can still hit a new error. In this case the oraInst.loc file, which points to the central inventory location, was not setup correctly. There was an old location which they commented out, but they forgot to specify a new one.

Ex. Sample oraInst.loc (On unix servers this file is normally stored in /var/opt/oracle or /etc)

#Oracle Installer Location File Location
#Wed Apr 30 14:51:42 EDT 2007
inventory_loc=/home/oracle/oraInventory
inst_group=dba


What is the Central Inventory? The Central Inventory stores information for all Oracle products installed on the server. Inside the inventory location there is an xml file (inventory.xml) which lists all of the ORACLE_HOMEs.

Ex. <Central Inventory Path>/ContentsXML/inventory.xml (In this example, using the path from the oraInst.loc file above you would look in /home/oracle/oraInventory/ContentsXML/inventory.xml)

<?xml version="1.0" standalone="yes" ?>
<!-- Copyright (c) 2001 Oracle Corporation. All rights Reserved -->
<!-- Do not modify the contents of this file by hand. -->
<INVENTORY>
<VERSION_INFO>
<SAVED_WITH>2.2.0.19.0</SAVED_WITH>
<MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME="APPSDB_ORA" LOC="/app/oradb/920_64bit" TYPE="O" IDX="1"/>
<HOME NAME="APPSDB_ORA1" LOC="/app/oradb/1020_64bit" TYPE="O" IDX="2"/>
<HOME NAME="APPSIAS_ORA" LOC="/apps/appsora/oraora/iAS" TYPE="O" IDX="3"/>
</HOME_LIST>

As you can see, this server has both 9i and 10g database software and an application server. Inside each of these ORACLE_HOMEs there is a Local Inventory. The local inventory stores the list of components, patchsets and interim patches installed in that particular ORACLE_HOME. The file in this case is named comps.xml

ex. <Local Inventory Location>/ContentsXML/comps.xml (This file is rather large so just a small clip. This is from the 920_64bit ORACLE_HOME.)

<?xml version="1.0" standalone="yes" ?>
<!-- Copyright (c) 2001 Oracle Corporation. All rights Reserved -->
<!-- Do not modify the contents of this file by hand. -->
<PRD_LIST>
<TL_LIST>
<COMP NAME="oracle.server" VER="9.2.0.1.0" BUILD_NUMBER="0" REP_VER="0.0.0.0.0" RELEASE="Production" INV_LOC="Components/oracle.serv
er/9.2.0.1.0/1/" LANGS="en" XML_INV_LOC="Components21/oracle.server/9.2.0.1.0/" ACT_INST_VER="2.2.0.12.0" DEINST_VER="2.2.0.4.0" INS
TALL_TIME="2004.Dec.16 14:43:30 EST" INST_LOC="/mnt/app/visdb/920_64bit/oracle.server">
<EXT_NAME>Oracle9i Database</EXT_NAME>
<DESC></DESC>
<DESCID>COMPONENT_DESC</DESCID>
<DEP_GRP_LIST>
<DEP_GRP NAME="Optional" TYPE="O"/>
<DEP_GRP NAME="Required" TYPE="R"/>
</DEP_GRP_LIST>
<DEP_LIST>
<DEP NAME="oracle.rdbms" VER="9.2.0.1.0" DEP_GRP_NAME="Optional" HOME_IDX="1"/>

By default Oracle looks for the oraInst.loc file in /var/opt/oracle or /etc depending on your platform. But you can change the location if you wish, you'll just need to remember to specify the -invPtrLoc parameter with opatch and the runInstaller.

ex. opatch lsinventory -invPtrLoc <path to your>/oraInst.loc
runInstaller -invPtrLoc <path to your>/oraInst.loc

This relates back to an earlier post I made which shows you how to recreate your central inventory in an EBS environment.

RAC Crash Recovery

In a normal oracle environment you can set the FAST_START_MTTR_TARGET parameter which controls the amount of time (seconds) required to recover from an instance or server failure. 10g has a new feature Automatic Checkpoint tuning which utilizes periods of low I/O to advance checkpoints. This feature requires that FAST_START_MTTR_TARGET is set to a non-zero value.

FAST_START_MTTR_TARGET includes both instance startup and crash recovery. If you have a RAC environment and there is a failure the surviving instances will perform the recovery. Therefore including the instance startup time in the mean time to recover calculation isn't accurate. So in the case of RAC environments you may choose to set the _FAST_START_INSTANCE_RECOVERY_TARGET parameter which determines the amount of the time for instance recovery. Note: If multiple instances fail, recovery may take longer than the value you specified for this parameter.

So in summary, FAST_START_MTTR_TARGET includes the time for startup, mount, crash recovery, and open. _FAST_START_INSTANCE_RECOVERY_TARGET includes the time for instance recovery only.

Changing any parameter which affects checkpointing should be throughly tested to make sure you don't thrash your IO subsystem. Each of these parameters will increase I/O.

11i and Listener Passwords

A standard practice for any Oracle database should be to enable passwords on your listeners. EBS is no exception and recently Oracle published a metalink note: 386374.1 on how to do this for 11.5.x environments.

There are some pre-reqs, specifically:

3453499 (11i.ADX.F)
5225940 (Post ADX.F Fixes)
5107107 - AUTOCONFIG ENGINE & CONFIG TOOLS ROLLUP PATCH N or higher

The note has detailed steps on how to set the password at the application and database tiers.

In my opinion, just as important as setting a password, is to monitor for invalid attempts after. Otherwise whats the point? At best you will only slow down a determined intruder. Once you have set a password, any attempt to execute a command via lsnrctl (without setting your password first) will result in the following error showing up in your listener log:

TNS-01169: The listener has not recognized the password

Each DBA has his preferred language for scripts. Some prefer shell, others perl. Myself, I prefer perl. Below is a perl script I have used to monitor the listener log for the above error.

#!/usr/bin/perl -w

if ($#ARGV !=1) {
  die "Usage:  check_alert.pl   ex.  hostname /var/opt/oracle/listener.log.\n";
}
sleep 2;

$interval=60;        # How many seconds before we check to see if data has been written to the logfile;
$email_threshold=5;  # How many errors within the interval before an email gets sent;
$hostname=$ARGV[0];
$file=$ARGV[1];
open(filePtr, $file) or die "Can't find $file\n";


for (;;) {
  @errors=("Subject: Listener Password  Errors for $hostname\n");
  $currTime = localtime(time);
  push(@errors,"Here are some errors found at $currTime for $hostname.\n");

  while () {
   chop $_;
   if (/TNS-01169/) {
       push(@errors, "$_\n"); 
   }
  }

  if ($#errors > $email_threshold)  {
   $rndExt = time;
   $rndFile = "alert_errors_$rndExt";
   open (TMPFILE, ">/tmp/$rndFile");

   foreach $error (@errors) {
        print TMPFILE $error;
   }
   close(TMPFILE);
   system ("mail username\@mycompany.com < /tmp/$rndFile");
   system ("rm /tmp/$rndFile");
  }

  sleep $interval;
  seek filePtr, 0, 1;
}

The script takes two parameters: <hostname> <path_to_listener_log>

A couple of key things you may want to customize in the above script are the $interval and $email_threshold variables. As well, there is an email address towards the bottom where alerts will be sent.

Now, I am by no means a perl expert but the above works for me. I'm sure there are some more efficient ways to monitor the log file. Feel free to post a comment with your preferred method. If you cycle your logfiles keep in mind you'll need to restart this script. I've monitored resource consumption and its pretty minimal but I know some DBA's will prefer not having a process run constantly.

Note: I've only tested this script on a 9i listener log. Its on my todo list to test it on 8.0.6.

UPDATE: Thanks to Howard Rogers for informing me of the 10g changes. Security has been enhanced such that the TNS listener administrator has to be a member of the local dba group. Unless you plan on administering your listener remotely then you don't need a password. Please see the comments and Metalink note: 340009.1

Most of our databases are still 9i but i'll be sure to remove the listener passwords as part of the upgrades.