0 
0 Yesterday, I had to install XMLDB on an already very heavy used system. On this UNIX system, an old Oracle IAS is installed, OAS 10g (version 9.0.4) and an Oracle 10.1.0.3 database. So in terms of used port numbers, it is a very crowded system. The Oracle listener was split-up by me in two different listeners. I defined a version 9 environment and a version 10 environment. I setup a TNS directory via setting the TNS_ADMIN variable in every profile. It makes stuff more complex, in the context of configuring, but it also centralizes all the SQL*Net information and configuration files on a default spot. By the way, I don’t make use of Oracle Names.
The following shows how you can setup different Oracle listeners and register XMLDB on the correct Listener.
Setting up two listeners
It’s possible to have also two different version listeners (the 9 and 10 version) running, by settting different ORACLE_HOME’s and PATH’s, and starting the different listeners. I like to have 1 listener – as long as this can be maintained regarding incompatibility and bugs etc – therefore my latest listener version is leading (version 10g). Set your ORACLE_HOME accordingly. My 10g Oracle home is placed in directory/oracle/rdb10g/ and my TNS_ADMIN directory is set to directory /oracle/network/admin.
> export ORACLE_HOME=/oracle/rdb10g > export TNS_ADMIN=/oracle/network/admin > export PATH=$ORACLE_HOME/bin:$PATH
I defined two listeners via a default LISTENER (dedicated for OAS and the older IAS environment) and a LISTENER_10G (dedicated for my 10g database with it’s XMLDB functionality). This is a local machine in a small local infrastructure. If you want to have a more secure listener configuration than please don’t forget to set a password on your listener. You could set this via your listener control and set password {your password}.
> lsnrctl LSNRCTL> help set The following operations are available after set An asterisk (*) denotes a modifier or extended command: password rawmode ...
Set your ORACLE_HOME’s in the listener.ora file correctly and define your listeners on different port numbers (eg. 1521 and 1522). My configuration of the listener.ora file is shown below:
# Oracle version 9 environment
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = amisux22.amis.nl)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/ias10g/infra)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = asdb.amis.nl)
(ORACLE_HOME = /oracle/ias10g/infra)
(SID_NAME = asdb)
)
)
# Oracle version 10 environment
LISTENER_10G =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = amisux22.amis.nl)(PORT = 1522))
)
)
)
SID_LIST_LISTENER_10G =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl.amis.nl)
(ORACLE_HOME = /oracle/rdb10g)
(SID_NAME = orcl)
)
)
After this you can start the listeners to check your used syntax via:
> lsnrctl start (listener) > lsnrctl start listener_10g
You can check this on UNIX via:
> ps -ef | grep tns | grep -v grep oracle 9950 1 0 12:13:12 ? 0:01 /oracle/rdb10g/bin/tnslsnr listener -inherit oracle 8267 1 0 11:56:44 ? 0:00 /oracle/rdb10g/bin/tnslsnr listener_10g -inherit
Registering the database with the correct listener
Starting the Oracle 10g database would cause the database to register itself with the listener running on port 1521 (the default listener). This is not what I wanted. It should register itself to the listener listener_10g defined on port 1522. For this to happen we have to add an extra line in the database parameter file init{$SID}.ora.
The parameter used by oracle is LOCAL_LISTENER. The reference for this parameter in the Oracle® Database Reference Guide says:
LOCAL_LISTENER
LOCAL_LISTENER specifies a network name that resolves to an address or address list of Oracle Net local listeners (that is, listeners that are running on the same machine as this instance). The address or address list is specified in the TNSNAMES.ORA file or other address repository as configured for your system.
With default value: (ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521)) where hostname is the network name of the local host
I added the following line, in accordance with the default syntax to my init{$SID}.ora file to my Oracle 10g (with XMLDB) database .
LOCAL_LISTENER=(ADDRESS=(PROTOCOL=TCP)(HOST=AMISUX22)(PORT=1522))
If you don’t use a database parameter file, but use the spfile construction, then you can alter/set this setting via a SQL statement in eg. SQL*Plus and an account with the correct privileges:
SQL> alter system set LOCAL_LISTENER="(ADDRESS=(PROTOCOL=TCP)(HOST=AMISUX22)(PORT=1522))" scope=BOTH; System altered. SQL> show parameter LISTENER NAME TYPE VALUE local_listener string (ADDRESS=(PROTOCOL=TCP)(HOST=AMISUX22)(PORT=1522))
The database will register itself to the correct listener_10g listener on port 1522.
The XMLDB daemons are registered via the DISPATCHERS and LOCAL_LISTENER settings in the init{$SID}.ora file of your database, or via the ALTER SYSTEM statement with your spfile. I added the following line, in accordance with the default syntax for XMLDB – (PROTOCOL=TCP) (SERVICE={$SID}XDB) – to my init{$SID}.ora file to my Oracle 10g (with XMLDB) database .
DISPATCHERS=(ADDRESS=(PROTOCOL=TCP) (SERVICE=orclXDB)
You can also alter this setting via SQL*Plus:
SQL> alter system set DISPATCHERS="(PROTOCOL=TCP) (SERVICE=orclXDB)" scope=BOTH; System altered. SQL> show parameter DISPATCHERS NAME TYPE VALUE dispatchers string (PROTOCOL=TCP) (SERVICE=orclXDB)
Registering the FTP / WebDAV and HTTP on different ports
As said, the machine I am using is packed with application servers and other daemons, all trying to use their default port settings. So after checking via the lsnrctl status listener_10g statement, I noticed that the WebDAV / HTTP registration, on port 8080, was not showing up.
LSNRCTL> status listener_10g Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=amisux22.amis.nl)(PORT=1522))) STATUS of the LISTENER ------------------------ Alias listener_10g Version TNSLSNR for Unix: Version 10.1.0.3.0 - Production Start Date 03-MAR-2005 11:56:45 Uptime 0 days 2 hr. 16 min. 39 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /oracle/network/admin/listener.ora Listener Log File /oracle/network/log/listener_10g.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=amisux22.amis.nl)(PORT=1522))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=amisux22.amis.nl)(PORT=2100))(Presentation=FTP)(Session=RAW)) Services Summary... Service "orcl" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... Service "orcl.amis.nl" has 1 instance(s). Instance "orcl", status UNKNOWN, 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
After checking on UNIX with statement netstat -na | grep 8080, I saw that (I should have known) the HTTP daemon of XMLDB and HTML of the 10g (9.0.4) Oracle Application Server, where both defined on port 8080. I decided to change the port settings in the XMLDB “repository”. To alter XMLDB FTP / HTTPbehaviour, we must alter the content of the xdbconfig.xsd file in the database. You could do this via a full update of this file via WebDAV or FTP but it is also do-able via a SQL statement.
SQL> set long 1000000 SQL> set pages 5000 SQL> set trimspool on SQL> SQL> SELECT dbms_xdb.cfg_get "xdbconfig.xsd" FROM dual 2 / xdbconfig.xsd --------------------------------------------------------------------------------900 32 , true 300 65536 100 false 3600 0 1048576 ...
The FTP settings are shown here:
2100 local_listener tcp 0 6000 8192
Some of the HTTP settings are shown here:
8080 local_listener tcp 64 16384 2000000000 6000 XDB HTTP Server
We can alter these ports and other settings via a combination of updatexml and dbms_xdb.cfg_get in SQL*Plus. I used the following script / statements to set the ports to 6010 and 6020. Be aware that cfg_update() is auto-commit.
declare
newconfig XMLType;
begin
--ftp port setting
select updatexml(
dbms_xdb.cfg_get(),'/xdbconfig/sysconfig/protocolconfig/ftpconfig/ftp-port/text()',6010) into newconfig from dual;
dbms_xdb.cfg_update(newconfig);
--http port setting
select updatexml(
dbms_xdb.cfg_get(),'/xdbconfig/sysconfig/protocolconfig/httpconfig/http-port/text()',6020) into newconfig from dual;
dbms_xdb.cfg_update(newconfig);
end;
/
After these update statements the xdbconfig.xsd file is updated with the new port settings. After restarting the listener_10g listener, FTP and WebDAV will register themselves via this listener.
LSNRCTL> status listener_10g Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=amisux22.amis.nl)(PORT=1522))) STATUS of the LISTENER ------------------------ Alias listener_10g Version TNSLSNR for Unix: Version 10.1.0.3.0 - Production Start Date 03-MAR-2005 11:56:45 Uptime 0 days 2 hr. 16 min. 39 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /oracle/network/admin/listener.ora Listener Log File /oracle/network/log/listener_10g.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=amisux22.amis.nl)(PORT=1522))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=amisux22.amis.nl)(PORT=6020))(Presentation=HTTP)(Session=RAW)) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=amisux22.amis.nl)(PORT=6010))(Presentation=FTP)(Session=RAW)) Services Summary... Service "orcl" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... Service "orcl.amis.nl" has 1 instance(s). Instance "orcl", status UNKNOWN, 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
Related posts:
Creating your own advanced search engine for any website – using Oracle Text – Searching the AMIS Technology Weblog 
Find the number of descendant nodes in a SQL query – how many employees work (indirectly) for me? And what do they earn on average? Oracle announces XQuilla / XQilla XQuery engine available under the open source Apache 2.0 license Bloom Filters, Hierarchical Profiling, Synopses & One pass distinct sampling, jumping the GAPP … – feast for DBAs (considered harmful..) 
Uitnodiging: Kom kennismaken met AMIS
😉 One of the reasons i am looking forward to Oracle 10g Release 2
Good Review…. Marco could you give us some more info on how you are using the protocols
Nice Article… Interested in find out more about what you are using the protocol servers for…
10gR2 will have methods on DBMS_XDB (SetHTTPPort, setFTPPort) which will make it easier to update the Port settings
good tips and good review from Pete.
Good review for this post by Pete Finnigan:
03/04/2005: “Amis Blog has an interesting entry on multiple listeners”