The OTN toolbar and Metalink classic

For those who believe that new coke was just a great marketing wheeze to get people to appreciate old coke more, there is probably a case to be made for arguing that MOS (My Oracle Support) is a great way to make people appreciate how good old Metalink was (now renamed classic like old coke.)

So for those of you who use the OTN toolbar to launch Metalink and are disappointed that the button has effectively been hijacked, and now takes you to MOS, you will be pleased to hear that with only a couple of minor registry changes, the old functionality can be restored, and it can take you directly to the old style Metalink login screen.

You will require 2 new keys, UserButtons and METALINK, with the UserButtons key being under the standard otn toolbar hive and the METALINK key being under the New UserButtons key thus:

HKEY_CURRENT_USER\Software\oracle_dba_toolbar\UserButtons\METALINK

Then under the MTALINK key, you will need 2 new string values: “caption” and “link”, where Caption will be the text label to the right of the Metalink button in the toolbar, and the link will be the address of the classic Metalink page (https://metalink2.oracle.com/).

Alternatively, you can place the following in a text file with a .reg extension and double click on it to incorporate the contents into your registry.

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\oracle_dba_toolbar\UserButtons]

[HKEY_CURRENT_USER\Software\oracle_dba_toolbar\UserButtons\METALINK]
“Caption”=”Classic Metalink”
“Link”=”https://metalink2.oracle.com/”

The RAC roundtable

Please note as a non-expert, I don’t guarantee my ability to accurately summarise everything!

The experts were Julian Dyke, Joel Goodman, + 2 others.

The first question concerned connection pooling, and the unbalanced load experienced wile attempting to load balance.

Several issues were discussed while closing in on a conclusion.

Joel explained the new FAN events covering service goodness that have been introduced in 11gR2. However these new asynchronous notifications are only used if the JDBC ICC client is used or if the middle tier was aware of these notifications.

As the person asking the question appeared to be setting sensible min and max handlers, it was concluded that the issue was that load balancing was occurring at connect time. I.e. Differing numbers of connections are established at connect time to the RAC servers based on current load, whereas the goal should have been uniform connection allocation initially, and then load balancing needs to be established on the basis of allocation from the existing pool on the basis of current load. Essentially the goal wasn’t to increase the number of connections to notes that were being underutilised, but to rather use more frequently the connections that already existed to the nodes that were under utilised.

Key to achieving this runtime load balancing was utilisation of services to handle listener connections.

There was a brief associated discussion as to which connect time algorithm should be used.

The conclusion was that clbgul (?)=long should be set, although doubt was expressed that this actually related to the original question.

A curious question was then asked about RAC in a virtual IBM server environment, Doubt was expressed that RAC was actually supported in that environment, but that doubt seemed to ebb away.

The question was, should the virtual RAC environment be given another node, more CPUs in existing nodes or an additional CPUs in the existing nodes.

The simple answer was
1) Better CPUs
2) More CPUs
3) More nodes.

However then came the caveats.
1) The amount of batch work may influence the decision.
2) While faster CPUs is traditionally considered the solution that involves less risk, there is the possibility that the increased throughput might increase load on the interconnect, which is probably the biggest performance inhibitor. Ergo there is no simple answer!
3) As the environment was virtual, then it was probably worthwhile just trying it and seeing what the result was!
4) It was pointed out that if the reason for going RAC was HA rather than performance, than all the above answers were wrong! As the main issue would be by going to a 3 server RAC cluster, the user benefited from the fact that a node failure would result in the loss of 1/3rd of the available capacity, rather than ½ that would currently be experienced.

There was a brief discussion of interdependence of clusterware, asm and rdbms versions, and from what point rolling upgrades were available.

Clusterware has always supported rolling upgrades.
ASM supports rolling upgrades from 11g.

Finally the version of Clusterware should be >= the version of ASM >= the version of RDBMS.

Next the question of RAC on GPFS vs. ASM on AIX was asked.

Many questions and observations about technology and politics of file system choice were tackled before attendees attested that RAC worked well on both!

Before that answer was established, the fact that ASM didn’t suit SAP was observed, because SAP requires access to a native file system.

ASMfs would e introduced with ASM 11.2 and would expose the ASM as a normal file system.

An advantage of ASM was that it opened, held and cached data file file handles, which was an optimisation that wouldn’t be possible with other file systems.

Utilisation of the white papers produced by the MAA group was recommended.

What are the effects of internal redundancy?
Memory, IO and CPU! In that order!

The issue of block versioning in RAC was covered.

Essentially multiple versions of blocks can exist on different nodes. When writing, this isn’t a consideration. The latest version of the block needs to be either read from disk, or passed from another node in the cluster causing traffic. However for reading, out of date duplicate blocks on the current node can be utilised for logical reads!

Again, as with the security round table, only the surface seemed to be scratched!

A summary of the Oracle Security round table.

The experts present were:

Pete Finnigan (of petefinnigan.com) – Pete I hope requires no introduction, before Pete I was an Oracle security virgin!
Paul Wright (of Markit) – From the previous days Oracle security session seems to be a heavy proponent of hedgehog form sentrigo.
Slavik Markovich (of Sentrigo (originator of Hedgehog)) – on the basis of his session, a strong proponent of proactive pl/sql security hole discovery.

And possibly Kev Else (of no fools limited) – listed on the agenda; however I failed to confirm his identity or presence.

Very roughly, Pete Finnigan expressed the position that open routing is the greatest general security risk. The ability for anyone to plug a laptop into an open Ethernet socket and then be able to connect directly to the database!

Secondly the implementation of security at the application layer, where the functionality of a user is restricted within an application, but when connecting directly either through SQL*Plus or Excel had little or no restriction on the SQL they could execute.

There was then the consideration of the nature and the source of the threats confronting an organisation.

Threats were predominantly not malicious, but rather based on the failings of various carbon based life forms. The propensity of people to place critical data on CDs or USB sticks, and then not be able to verify what happened to that data or who had access to it.

However there was also the suggestion as more companies have a direct exposure to the internet; the proportion of the risk that was internal (in the past estimated to be 80%) was dropping with organised gangs attempting to attack financial institutions.

Next was an observation made at sites that implement a data map – a system where access to sensitive data is recorded.

The behavior observed, was that people soon to leave an organisation often accessed much more data in the period before they left than they would in normal use of the systems.

Pete then proposed a methodology for reducing user’s privileges.

1) Check what privileges a user holds, both directly and through roles.
2) Check what type of objects that a user owns.
3) Identify roles that a user has been granted, but doesn’t require to create the objects that exist.
4) Audit that user on the roles that the user in theory doesn’t need.
5) If after a couple of months revoke the privileges that the user doesn’t use and doesn’t need.

It was then stated that this was only an approach to system privileges, father actions would then need to be taken to curtail object privileges.

Issues relating to the vulnerabilities that were introduced by not following Oracles recommendations for having a separate oinstall installation user and oper and oasys groups.

If performed correctly, a privileged UNIX user (not oracle) will connect to sqlplus /nolog, connect internal and only acquire public privileges, rather than the sys privileges that are acquired when the oracle user performs these instructions.

There was then an encouragement to prioritorise and escalate security implementation on the basis of an investigation of the importance of the data to be protected. Essentially some of your databases may only hold administrative data, and hardening these databases is substantially less important than hardening those databases that may contain personal or financial information.

There was a discussion regarding whistle blowing, and the stated fact that many firms were now obliged to have a risk officer or security officer, and it is to that person in the first case that security issues should probably be raised.

There was a little more, but I suspect that even tripling the time allocated, we would have only scratched the surface!

Linux and the Centro cross rail line

Somewhat off topic:

Having spent another good day at UKOUG 2008 and being a local boy I took the 21:14 to Longbridge to take me home.

Interestingly before departure, the LCD screens pumping news weather and adverts into the carriages went blank; shortly to be followed by a Linux boot screen.

I have seen games machines in pubs and railway information terminals displaying blue screens of death and even yesterday the screens at the ICC were displaying the helpful information that an error had occurred in a Microsoft C++ library, but this is probably the first evidence I have seen of Linux encroaching on this market.

The fact that the service on the Centro trains is almost certainly not directly paid for the commuters using the service may be a factor in the choice, or there is the possibility that the spread of Linux is actually much more widespread that is immediately apparent.

Ultimately we have a service provider who could have used Windows to provide the service but have chosen Linux instead.

I’d love to see the business case behind that, but I suspect that it is just another very small confirmation that in backing Linux, Oracle has made a very shrewd move.

ukoug 2008

It seems to be 8 months since I last blogged. Unfortunately my work seems to be becoming less and less oracle related. However with the onset of the 25th anniversary UKOUG conference, I do have a little to blog!

Much of the excitement this year has related to the launch of the exadata machine, which is perhaps best described as a Data Warehouse accelerator.

In technical terms, some of the processing that was previously performed by the database server has been offloaded to ‘intelligent’ Disk arrays.

Ultimately, a little very clever software has been used to leverage high end commodity hardware to produce a relatively cheap very fast Data warehouse machine. Moving Oracle even father ahead of SQL*Server in the crucial Decision management arena.

However as this technology has little relevance to my current customer, I skipped most of the sessions on exadata apart from the demonstration of Oracle’s exadata simulator.

Unlike some attendees, it is not my intention to blog about all the sessions that I attend, ultimately I attend the sessions to learn myself, and without independent investigation, I lack confidence that I could fully relate their content accurately.

What I do intend to do, is relate some of the information from the round tables that I attend. It is my opinion that the frank exchange of information between experts that these sessions consist of is highly valuable, and without formal slides available for obvious reasons should be documented and disseminated.

Technorati favouries button

Problems encountered migrating to Oracle 8i

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);

Oracle Names 2 to 3 upgrade checklist.

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:
NMO_BLOB
NMO_INFORMATION
ONRS_CONFIG
ONRS_DOMAIN
ONRS_SERIAL
ONRS_TOPO

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=
(NAME= LOCAL_REGION.world)
(TYPE= ROSDB)
(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’,
‘ONRS_CONFIG’, ‘ONRS_DOMAIN’, ‘ONRS_TOPO’, ‘ONRS_REGION’,
‘ONRS_DOMTOPO’ )

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
( ‘NMO_INFORMATION’, ‘NMO_MASTER’, ‘NMO_BLOB’, ‘ONRS_SERIAL’,
‘ONRS_CONFIG’, ‘ONRS_DOMAIN’, ‘ONRS_TOPO’, ‘ONRS_REGION’,
‘ONRS_DOMTOPO’ )
/

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:
consistent=y
compress=y
indexes=y
buffer=1048576
statistics=estimate
file=names.dmp
log=names.log
grants=y
rows=y
tables=(NMO_INFORMATION, NMO_MASTER, NMO_BLOB, ONRS_SERIAL, ONRS_CONFIG, ONRS_DOMAIN, ONRS_TOPO, ONRS_REGION)

4 Shut the names service down

$ namesctl
Oracle Names Control for IBM/AIX RISC System/6000: Version 8.1.7.0.0 – Production on 30-JUL-2001 18:34:23
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Currently managing name server “NameServer.world”
Version banner is “Oracle Names for IBM/AIX RISC System/6000: Version 2.0.4.0.0 – 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
NAMESCTL> exit

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:

NAMES.ADDRESSES = (ADDRESS = (PROTOCOL = TCP)(HOST = testsvr)(PORT = 1527))

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=
(name= local_region.world)
(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;

SQL> INSERT into NAMES_DID
select DID from NMO_INFORMATION
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 3.3.4.0.0 – 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 7.3.4.4.0 – Production
With the distributed, parallel query and Spatial Data options
PL/SQL Release 2.3.4.4.0 – 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
ckpreg.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!

Why am I here?

It used to be that all you needed for a career in computing was a lack of social skills, poor dress sense and an ability to grow facial hair.

Now, not only are we expected to get qualifications, we need a positive attitude, certifications and so it seems now, a blog!

So to the subject of mine. This will be my collection of hints and tips that are too old or obscure for publication anywhere else and while predominantly oracle based, will not be exclusively so!

However, what this will not be, is just another collection of tips that are widespread, or my attempt at an Oracle FAQ, I simply don’t have the time or the ego!

And finally, come the UKOUG conference 2007, it will be my platform for for mocking those otherwise respected luminaries who believe that All Bar One in Brindley place (http://www.rittman.net/2006/10/18/blogger-meetup-ukoug-conference-tuesday-november-14th/)is a good place to get a pint; those who have an apparently exhaustive knowledge of Oracle, but seem incapable of entering the words CAMRA and BIRMINGHAM into a search engine!