Oracle Observations

March 20, 2007

Oracle Names troubleshooting

Filed under: Oracle Names — bigdaveroberts @ 5:22 pm

As described previously, you can check the database entries and the cache entries for your databases.
If the data in the database and the cache file (ckpreg.ora) is correct, then restart the names server.


Restarting the names demon:

/usr/home/oracle O>namesctl

Oracle Names Control for IBM/AIX RISC System/6000: Version – Production on 03-FEB-2003 15:20:29

(c) Copyright 1997 Oracle Corporation.  All rights reserved.

Currently managing name server “”
Version banner is “Oracle Names for IBM/AIX RISC System/6000: Version
– Production”

Welcome to NAMESCTL, type “help” for information.

Confirm [yes or no]: yes
Server shutting down
Starting “/opt/app/oracle/8.1.7/bin/names”…server successfully started

If the data in the database is incorrect, unregister the database and reregister it.


Take the entry in the database that includes the characters: ‘(TYPE=a.smd.)’ in the ZD_VALUE1_P column:

(DATA_LIST=(FLAGS=0x1)(DATA=(TYPE=a.smd.)(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=tplive)(PORT=1521))(CONNECT_DATA=(SID=TPLIVE2))))) Remove the text in red and unregister the database using the remaining text as the address:Eg:NAMESCTL> unregister testdb -t database -d (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=testsvr)(PORT=1521))(CONNECT_DATA=(SID=TESTDB)))
Total response time:   0.09 seconds
Response status:       normal, successful completion

If there are multiple entries, unregister them all.

Then register the correct address.

NAMESCTL> register testdb -t database -d (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=testsvr)(PORT=1521))(CONNECT_DATA=(SID=TESTDB)))
Total response time:   0.07 seconds
Response status:       normal, successful completion

If the data in the cache is incorrect, stop the names server, move or remove all the .ora files in the $ORACLE_HOME/network/names directory. Finally restart the names server.

The $ORACLE_HOME/network/names is the default location for the cache files.

Theoretically the location could be changed by the following parameters in the names.ora file that is normally located in the $ORACLE_HOME/network/admin directory. The names.ora file should never be deleted!

Names.ora file parameters that specify the location of the names cache files:


Frequently the data will be corrupt in more than one place, in that case, re register the data in the database, and when that is correct, stop the names server and remove the cached files.

If you suspect that the incorrect data is being passed to the names server from another names server, then you should configure your own sqlnet.ora file to use oracle names name resolution, and you should list only one names server at a time and verify what is being returned by using tnsping to connect to the database.


    (ADDRESS = (COMMUNITY = = TCP)(Host = namesvr1)(Port = 1527))

If it is not possible to change the configuration to verify the data returned from the names server, then the same information can be gained by switching on tracing.

If the data returned from multiple names servers is incorrect, and attempts to correct the data one server at a time is unsuccessful, then you will need to stop all the names servers, then individually start each one up, correct the data and shut it down. When all the servers are fixed then they can all be restarted. It should be noted that during this period access to all the databases will be restricted.

The primary repository for names configuration data is the database, which can be checked while the names demon is stopped. After starting the names demon, the data is read from the database, and is then saved to the cache files. I am not aware of any documentation that indicates what source the names demon will use as a primary source of information when multiple sources are available.


March 6, 2007

Problems encountered migrating to Oracle 8i

Filed under: 8i — bigdaveroberts @ 11:49 am

Another document that I produced long ago was the issues encounterd upgrading a small document management system to Oracle8i.

With hindsight, the fact that we also changed from the RBO to the CBO explains at least the first issue, but may also be implicated in the other two issues as well.

1 SQL Order of execution.

It has always been my experience that if there are no other factors to consider, Oracle will apply conditions in reverse order, executing the last where clause first. This no longer appears to be the case.

For example:

The table testdate has a column logdate that consists of either a single space or a date as a character string in YYYY MM DD HH24:MI format.

The following select statement works fine under Oracle 7 as all of the rows where the value is a single space are ignored by the last line.

Select count (*) from testdate
Where to_date(logdate, 'yyyy-mm-dd HH24:MI’) <> sysdate
And length(logdate) = 16 ;

However under oracle 8 the following error occurs:

ORA-01840: input value not long enough for date format

Indicating that the first where clause is executed on the rows first!

Note also, that an attempt to get round the problem was made by creating a view that excluded the rows containing a space, however this still failed as Oracle appeared to optimise the query and apply the to_date function on the fields that were not selected by the view!

2 Change in behaviour for invalid private synonyms.

In oracle 7, if an invalid private synonym (where the target object does not exist) and a valid public synonym with the same name exists, then the system will use the public synonym without raising an error. In oracle 8 an error is raised.

3  Ambiguous column names

You are no longer allows to have duplicate column names in a subselect. In oracle 7 a column name could be identical to a second column name as long as they were given separate aliases in the enclosing select statement.

I.e. the following worked under Oracle 7 but not under oracle 8:

Select description d1, description d2
From (select t1.description, t2.description
From t1, t2);
Select description d1, description d2                *
ERROR at line 1:
ORA-00918: column ambiguously defined

Under oracle 8 it would need to be rewritten thus:

Select d1 d2
From (select t1.description d1, t2.description d2
From t1, t2);

March 5, 2007

Oracle Names 2 to 3 upgrade checklist.

Filed under: Oracle Names — bigdaveroberts @ 5:49 pm

As is, I hope, good practice, I tend to increase the quantity and detail of my documentation in areas that have ‘challenged’ me most.

Upgrading Oracle names was one of those challenges, to which I responded with a detailed upgrade checklist. The upgrade was now so long ago that I don’t actually remember much of the process, however my checklist still survives that I present below.

The one main issue with the installation that I remember was the fact that for security reasons, all of the Names objects had been moved to the system schema, with private synonyms created for the names oracle user, along with minimal grants.

This deviation alone caused issues for the standard upgrade process. However the biggest issue that I remember was that a non-default port was being used for the names server, and the port needed to be declared in 2 configuration files, rather than just one, as it had been with Oracle 7.

This meant that if you didn’t add the new configuration entry, the names process would silently revert to the standard names port.

I suspect that the checklist is a blend of the standard upgrade process and issues that were specific to the installation on which I was working, but I present it as is in the hope that it may be of some value to somebody somewhere:

Steps to perform when planning an oracle names upgrade

This document should be read in conjunction with Appendix F (SQL*Net and Net8 Considerations for Migrations and Upgrades) of the Oracle 8I migration guide.

1. Identify names tables and owner.

The names version 2 database objects consists of 7 tables:

By default, these objects are owned by the names user, however as the names username and password are stored in the names.ora configuration file, this implementation is somewhat insecure, so alternative configurations are highly likely.

Example of a names.ora segment specifying the oracle username and password that the names deamon uses to access the database:

names.admin_region= (REGION=
(USERID = names)
(PASSWORD = names)

To find the owner of the Names tables execute the following SQL:

select owner, object_name, object_type, status
from all_objects
where object_name in ( ‘NMO_INFORMATION’, ‘NMO_MASTER’, ‘NMO_BLOB’, ‘ONRS_SERIAL’,

If the objects are either public or private synonyms, then you will also need to check the owner of the underlying objects by running the following SQL:

select * from all_synonyms
where synonym_name in

2 Export the names tables

Export the names tables, to provide a backup and to allow testing of the upgrade process in a test environment.

Parameters required to export the tables:

4 Shut the names service down

$ namesctl
Oracle Names Control for IBM/AIX RISC System/6000: Version – Production on 30-JUL-2001 18:34:23
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Currently managing name server “”
Version banner is “Oracle Names for IBM/AIX RISC System/6000: Version – Production”

Welcome to NAMESCTL, type “help” for information.

NAMESCTL> set password names
NAMESCTL> shutdown
Confirm [yes or no]: yes
NNL-00013: not performed, permission denied for supplied password

If you the password is not being accepted you will need to kill the service:

$ ps -ef | grep names
oracle 73570 80056 2 18:35:04 pts/0 0:00 grep names
oracle 84634 1 0 Jul 27 – 0:00 /opt/app/oracle/8.1.7/bin/names names.ctlstart=yes
$ kill 84634

5 copy the sqlnet.ora, listener.ora and names.ora files to the appropriate positions under the Oracle 8I directory hierarchy.

6 If the default port number 1575 is not being used, check names.ora contains an entry defining the port number, in addition to the definition of the port number in the sqlnet.ora file that was previously required.

Eg where the non standard port number 1527 is being used on the machine testsvr:


7 Create the NAMES_DOM table as follows:

SQL> CONNECT user/password
SQL> CREATE TABLE NAMES_DOM (domain varchar(256));

8 Populate the table with the domain names specified by the NAMES.DOMAINS
parameter in the names.ora file. If regions are not being used, just create the root domain:

SQL> INSERT into NAMES_DOM values (’(root)’);
9 Create the NAMES_DID table as follows:

SQL> CREATE TABLE NAMES_DID (did number(10))

10. Find the DOCNAME value under the NAMES.ADMIN_REGION parameter in
the names.ora file. The DOCNAME represents the name associated with the
region. In the following example, the DOCNAME is sbox.

names.admin_region= (region=
(type= rosdb)
(userid = names)
(password = names)
(description =
(address_list =
(address =
(protocol = tcp)
(host = nineva)
(port = 1387)))
(connect_data = (sid = em)))
(docname = sbox)
(version = 34619392) # 2.1.4
(retry = 60))

11. Query the NMO_INFORMATION table for the ID associated with the
DOCNAME and insert it into the NAMES.DOM table:

SQL> SELECT ID from NMO_INFORMATION where name_P= docname;

where NAME_p=’ docname’;

12 Run the names upgrade program

Connect to SQLPLUS as the table owner

$ cd $ORACLE_HOME/network/admin

$ sqlplus system@

SQL*Plus: Release – Production on Mon Jul 30 17:17:35 2001

Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.

Enter password:

Connected to:
Oracle7 Server Release – Production
With the distributed, parallel query and Spatial Data options
PL/SQL Release – Production

SQL> @namesupg.sql

13 Run the following grants as the names table owner:

grant all on system.NMO_MASTER to names;
grant all on system.ONRS_SERIAL to names;
grant all on system.ONRS_CONFIG to names;
grant all on system.ONRS_REGION to names;

create public synonym onrs_domtopo for system.onrs_region;
create public synonym NMO_MASTER for system.NMO_MASTER;
create public synonym ONRS_SERIAL for system.ONRS_SERIAL;
create public synonym ONRS_CONFIG for system.ONRS_CONFIG;
create public synonym ONRS_REGION for system.ONRS_REGION;

14 Check the contents of the ONRS_SERIAL table.

select * from system.onrs_serial;

If no rows are returned, run the following

insert into system.onrs_serial values (1);

15 rename the .ora cache files.

Go to the directory where the names cache files are located.

$ cd $ORACLE_HOME/network/names
$ ls
cfg003ee.ora install ckpcch.ora
$ mv cfg003ee.ora cfg003ee.ora.old
$ mv ckpcch.ora ckpcch.ora.old
$ mv ckpreg.ora ckpreg.ora.old

16 start the namesserver

The End!

March 1, 2007

First Oracle tip!

Filed under: Oracle Names — bigdaveroberts @ 1:34 pm

Oracle Names was one of those products which never met its promise, I suspect largely because of a lack of a ‘point one’ release.

I have no experience of the first release of Oracle Names, but I was surprised by the substantive change in the product between releases 2 and 3. (The versions that came with Oracle 7.3 and the version that came with Oracle8.1.7).

This first entry on Oracle names will cover general names advice, with troubleshooting and upgrading being covered later.

 1) You should not be considering rolling out new installations of Oracle Names! The product has been superseded by Oracles implementation of LDAP.

2) The commands for registering and unregistering nodes are very syntactically intolerant. It is very easy by mistyping a single character in a command to produce significant dilatory effects.

Specifically there must be a space between -t and the following argument, and the -d and the following argument, and the arguments can contain no spaces. Also no new lines are allowed!


register testdb -t database -d (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=testsvr)(PORT=1521))(CONNECT_DATA=(SID=TESTDB)))

Additionally, the unregister command is case-sensitive. If the description is not identical (including case) to the description used to register the database, the unregister command will fail:


unregister testdb -t database -d (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=testsvr)(PORT=1521))(CONNECT_DATA=(SID=TESTDB)))

The address used to register the database with the -d parameter is the same as the data that would have been used to set up a database in the tnsnames.ora file, except for the fact that the whole entry must be on one line and contain no spaces.

3) When an oracle client contacts a names server, that data returned can be from 1 of 4 places.

1 In the names executable memory
2 From another names server on another machine.
3 From a cached file in the $ORACLE_HOME/network/names directory
4 From the database (the main repository for names data)

When incorrect data is returned, the data is wrong in 1 or more of the above locations!

4) While I know of no method of checking what the names server has in its process space, it is possible to check what is in the cache and the database.

To find out what is stored in the cache file go to the names directory and grep for the database name.


$ cd $ORACLE_HOME/network/names
$ grep -i testdb *.ora

To find out what is stored in the database, connect to the database and perform the following query:

1 select * from onrs_region
  2 where upper( name_p ) like ‘%TESTDB%’
  3* and ZD_VALUE1_P is not null
SQL > /



It can be seen in the above example that the information in both locations (database and cache) are similar.

5) I believe that if the database and cache were wrong, it would still be possible that the names server could be returning data from another names server. I.e. even though 2 servers are defined at the client end, and the names client will transparently contact the second names server if the first server doesn’t respond, there are also mechanisms in names where the names server on machine B with transparently contact the names server on machine A to resolve a TNS name, in this case the database and cache could both be wrong, while the correct data could be returned to the client!

If you suspect that the incorrect data is being passed to the names server from another names server, then you should configure your own sqlnet.ora file to use oracle names name resolution, and you should list only one names server at a time and verify what is being returned by using tnsping to connect to the database.


  (ADDRESS = (COMMUNITY = = TCP)(Host = onamessvr)(Port = 1527))

Create a free website or blog at