How to set up a standard listener on port 1521

Recently I have seen cases where customers have defined multiple listeners in the $TNS_ADMIN/listener.ora file, but nontheless have started only the default listener called "LISTENER".

Unless you actually start the separate listeners, they will not do you any good. Each listener must be configured to listen to different ports and then started explicitly, as follows:

If desirable to run separate listeners, configure your listener.ora as follows:

SID_LIST_LISTENER_PRODDB01 =
  (SID_LIST =
     (SID_DESC =
       (SID_NAME = PRODDB01)
       (ORACLE_HOME = /u01/oracle/product/11202)
   )
)
 
LISTENER_PRODDB01 =
  (DESCRIPTION_LIST =
     (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT =1521))
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      )
  )
 
SID_LIST_LISTENER_PRODDB02 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PRODDB02)
      (ORACLE_HOME = /u01/oracle/product/11202)
     )
  )
 
LISTENER_PRODDB02 =
  (DESCRIPTION_LIST =
     (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT=1526))
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1526))
     )
 )
 

Then start each listener:

LSNRCTL> set current_listener LISTENER_PRODDB01
Current Listener is LISTENER_PRODDB01
LSNRCTL> start
LSNRCTL> set current_listener LISTENER_PRODDB02
Current Listener is LISTENER_PRODDB01
LSNRCTL> start

After having started all LISTENERS, check that they are running (on unix):

ps -ef | grep LISTEN|grep -v grep

 oracle 7864350 1 0 15:41:47 - 0:12 /u01/oracle/product/11203/bin/tnslsnr LISTENER_PRODDB01 -inherit
 oracle 7864350 1 0 15:41:47 - 0:12 /u01/oracle/product/11203/bin/tnslsnr LISTENER_PRODDB02 -inherit

If you only specify one - 1 - listener per server, you can set it up to listen for incoming Connections for multiple databases, as specified in the SID_LIST:

SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC =
      (SID_NAME =PRODDB01)
      (ORACLE_HOME = /u01/oracle/product/11202)
    )
   (SID_DESC =
     (SID_NAME =PRODDB02)
     (ORACLE_HOME = /u01/oracle/product/11202)
   )
)

LISTENER =
 (DESCRIPTION_LIST =
    (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
     )
 )

I prefer using a $TNS_ADMIN/listener.ora file consequently. It makes it easier to configure the listener later.
I recommomed the following lines to be added to 11gR2 listeners:

DIAG_ADR_ENABLED=on             <-- Use the ADR as base for all future logging and tracing of the listener
ADR_BASE_LISTENER = /u01/oracle <-- The value of Your $ORACLE_BASE variable goes here
LOGGING_LISTENER=off            <-- Turn logging on only if needed
TRACE_LEVEL_LISTENER=off        <-- Turn tracing on only if needed

What is the significance of using the clone.pl script to clone an existing ORACLE_HOME to a new one?

In short, when using the clone method, Oracle will relink your binaries and update your Inventory.
Afterwards, your new ORACLE_HOME can be treated individually like any other ORACLE_HOME.

From Oracle Support note 565009.1:

Cloning re-plays all the actions that were performed during the installation of the Oracle Home like relinking, updating the inventory, etc., and hence the cloned home can be patched using OPatch, and patchsets can be installed to it.

Simply copying the Oracle Home is not supported.

By [simply] copying the Oracle Home from one server to another, all the above mentioned actions are not performed and also it is not possible to apply patches or patchsets to a copied Oracle Home.

If the Oracle Home has to be deployed to multiple destinations with the same configuration, cloning is the only supported method.


For a step-by-step instruction on how to perform such a clone, please see an excellent article by Eric Jenkinsson found here.

How to use the glogin.sql file to set global sqlplus attributes

The file $ORACLE_HOME/sqlplus/admin/glogin.sql can be used to define global attributes for users of sqlplus on that particular host.

For example:

SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER SQL>"
SET LINESIZE 200
SET PAGSEIZE 200

On Production servers, you may consider adding a colored prompt, for example red:

SET SQLPROMPT "'^[[41m'_USER'@'_CONNECT_IDENTIFIER'^[[0m' SQL> "

How to create the libobk.a link in $ORACLE_HOME/lib

su - oracle
cd $ORACLE_HOME/lib
ln -s /usr/lib/libobk64.a libobk.a

How to confirm that the latest PSU has been applied to your ORACLE_HOME

Use opatch lsinventory to extract the information. In my case:

hostname:PRODDB01>opatch lsinventory | egrep -i 'PSU|DATABASE PATCH SET UPDATE'

Patch description:  "Database Patch Set Update : 11.2.0.4.2 (18031668)" 
Sub-patch  17478514; "Database Patch Set Update : 11.2.0.4.1 (17478514)"
Patch description:  "Database Patch Set Update : 11.2.0.3.7 (16619892)"
Sub-patch  16056266; "Database Patch Set Update : 11.2.0.3.6 (16056266)"
Sub-patch  14727310; "Database Patch Set Update : 11.2.0.3.5 (14727310)"
Sub-patch  14275605; "Database Patch Set Update : 11.2.0.3.4 (14275605)"
Sub-patch  13923374; "Database Patch Set Update : 11.2.0.3.3 (13923374)"
Sub-patch  13696216; "Database Patch Set Update : 11.2.0.3.2 (13696216)"
Sub-patch  13343438; "Database Patch Set Update : 11.2.0.3.1 (13343438)"

What is the difference between the views nls_database_parameters and nls_instance_parameters?

The NLS_DATABASE_PARAMETERS view will display what the NLS settings were when the database was created. These are fixed at the database level and cannot be changed.

The NLS_INSTANCE_PARAMETERS view reflects parameters set for your instance in the init.ora file or the server parameter file (spfile).


Source: James Koopman in Databasejournal.com

This matches my settings for a random database:

SQL> show parameter nls_
 
NAME                                  TYPE        VALUE
 ------------------------------------ ----------- ------------------------------
 nls_comp                             string      BINARY
 nls_language                         string      AMERICAN
 nls_length_semantics                 string      BYTE
 nls_nchar_conv_excp                  string      FALSE
 nls_territory                        string      AMERICA
 
SQL> SELECT * FROM NLS_INSTANCE_PARAMETERS WHERE value IS NOT NULL;
 
PARAMETER                       VALUE
 ------------------------------ ----------------------------------------
 NLS_LANGUAGE                   AMERICAN
 NLS_TERRITORY                  AMERICA
 NLS_COMP                       BINARY
 NLS_LENGTH_SEMANTICS           BYTE
 NLS_NCHAR_CONV_EXCP            FALSE
 
5 rows selected.

What are literals?

The terms "literal" and "constant" are synonymous and refer to a fixed data value.

There are:

•Text Literals
Examples: 'JACK', 'BLUE ISLAND', '101'.

•Numeric Literals
Examples: 5001, +256

•Datetime Literals
Example: DATE '1998-12-25'.

Note that the ANSI (American National Standards Institute) date literal contains no time portion.

•Interval Literals
Examples:
INTERVAL '123-2' YEAR(3) TO MONTH
INTERVAL '4 5:12:10.222' DAY TO SECOND(3)


Source: Oracle Documentation