Oracle Observations

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!


Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at

%d bloggers like this: