Oracle an' T'ing

Tuesday, October 23, 2012

This article is intended to help with troubleshooting SQL Developer connection
problems. It won't tell you how to set up a connection because that is already well covered
online help (Start with the Getting Started topic and follow the link from there.)

The article is limited to the SQL Developer BASIC connection type. That is a simple
JDBC type 4 connection with no additional authentication or
security options, and no additional oracle client software.

What is a connection?

When you connect to an oracle instance using SQL Developer, you are
making a TCP/IP connection to a specific
host, on a specific port, and to a named instance
or service. These are the parameters you supply when creating a
basic connection in SQLDeveloper.

host

This can be a hostname or an IP address. If you use a hostname, including localhost, your workstation must be able to resolve it to an IP address.

port

This is the TCP port on the host which is listening for oracle connections. It is usually 1521, but not always.

SID

This the instance name of the database

service

This is the service name under which the database is available.
It is often the same as the SID, but may not be.

These details should be available from your DBA, or from when you created a database.

In the remainder of this article there is an assumption that you have
the correct details. There is always the possibility that you have the
wrong information or have mistyped something. If a test fails, re-check
your information before investigating other causes.

The database host and your workstation may be the same machine —
you may have SQL Developer installed on the database host,
or you may have an oracle database installed on your PC —
but you still need a TCP/IP connection. In this article I will use the
terms host and workstation to distinguish the two.

Connection Errors

This is not a comprehensive list of oracle errors, or even all errors relevant to SQL Developer connections.

These are errors caused by issues on the workstation,
or somewhere between the workstation and the host. If you get either of these errors, follow the troubleshooting tips at Basic Network Connectivity Below

An error was encountered performing the requested operation:
ORA-28000: the account is locked
28000. 00000 - "the account is locked"
*Cause: The user has entered wrong password consequently for maximum
number of times specified by the user's profile parameter
FAILED_LOGIN_ATTEMPTS, or the DBA has locked the account
*Action: Wait for PASSWORD_LOCK_TIME or contact DBA
Vendor code 28000

The account you are using is locked. You will need to contact your security administrator or DBA.

Basic Network Connectivity

These tests use a windows command window and windows tools. Tools on other platforms may produce different responses.

Can you ping the host?

Q:\tests>ping mercure
Ping request could not find host mercure. Please check the name and try again.

This indicates that the host "mercure" isn't known to your workstation. Typically this means that there is no DNS entry, or no HOSTS file entry.

This indicates that the host "hermes" is known to your workstation, but the host itself is not accessible.
The host could be down, or there could be a DNS error (i.e. wrong IP address,) or your network configuration
means that your workstation cannot reach it.

My current client has only a few databases on windows, and does not generally use external authentication, but each windows server has a couple of service accounts for backups and other operations and these are normally externally authenticated domain accounts.

Wednesday, February 21, 2007

Symantec have release a version of Veritas Storage Foundation as freeware. It seems to be aimed at small businesses running small servers as it is limited to creating 4 volumes / file systems and will only run on 2 processor machines.

This is good news for small companies as the licence cost of Storage Foundation used to be a significant overhead. If you are buying commodity hardware and putting a free operating system on it, then the $1000 or so SF used to cost is significant.

It is also good news for me as it means that my home lab set up can now include even more software I am likely to use at client sites.

Tuesday, February 06, 2007

I've recently given up an old email address which I no longer used, except as my contact point for OTN. This meant that I also had to give up my OTN account because the security software for OTN does not let you change your email address. It doesn't matter that much, but it means I have lost all my brownie points and my little gold medal for being an active user.

It is very annoying however. The forum software they use allows email addresses to be changed out of the box but they have modified it to disable this. I thought at first it was part of the move to a single sign-on approach to bring it in to line with metalink where your email address is your userid. But no, metalink allows you to change your email address even though it your userid.

And don't get me started on the fact that you can't change your password either.

I'm sure there will be all sorts of exciting new features designed to make me redundant, but the main reason is that I'll have to upgrade my lab machines.

Each new version has increased the minumum hardware requirements, but it is usually possible to run Oracle below the minimum requirements if all you are doing is single user experimentation. There are exceptions though. I tried to run Grid Control on a machine with 512MB of RAM (the recommended minimum is 1024MB) and I spent more time dealing with OC4J crashes than tring out grid control.

Most of my lab machines are so old that they are not worth upgrading - either they are at the maximum RAM or the RAM is no longer readily available.

When I finally bite the bullet, I will probably invest in something big and state of the art which can support at least 2 VMWare virtual machines concurrently.

Monday, December 04, 2006

This entry describes a very simple implementation of CVS as an external tool in Oracle's SQLDeveloper database ide. Integrated version control is promised for later releases, but until then an external tools implementation is the best that can be done.

The external tools interface is new in version 1.1 which is currently going through a preview cycle. There are limitations in the external tools interface which make it difficult to work with CVS in the way I would like to.

The external script

This is a windows batch file. All it does is change directory to the source file directory and issue a commit.

@echo offpushd %1cvs commit -m%3 %2popd

The external tool definition

Goto Tools > External Tools

If this is the first time you have used the dialog, you will be prompted to set up some automatic definitions.

The quotes round the final parameter are necessary because if can contain spaces. If your pathnames or file names contain spaces, the other parameters will need to be quoted too.

Run Directory: <directory containing batch file>

Click Next

As of build 2197 there are a couple of error dialogs to click through at this stage. They don't seem to affect the functionality.

Fill in the display options as follows

Caption for Menu Items: C&VS commit

This will set V as the accelerator for menu options.

ToolTip Text: Commit the current file to CVS

Icon Location: (Leave as default)

Click Next

Fill in the Integration options as appropriate. I haven't experimented with these, but you should have at least the tools menu selected. You should probably also check the Reload Open Files box.

Fill in the display options as follows

Add items to Menus:

Tools Menu - checked

Navigator Context menu - unchecked

Code Editor Context Menu - checked1

Add Buttons to Toolbars:

Main toolbar - Unchecked

After Tool Exits:

Reload Open Files - Checked.

This should ensure that if the file is modified during the commit (by keyword expansion for example), the new version is loaded into SQLDeveloper. NB. As of version 1.1.0.21.97, this causes the buffer to be closed

Click Next

File in the Availability options as follows.

Enabled: When specific file types are selected.

Selected types:

SQL Script

Text Document

PlSqlNode1

Click Finish

Note 1. These options will make the menu option available when editing PL/SQL in the database as well as in source files.

Limitations

Only works on one file at a time

Only works on FILES, not other object nodes.

No access to the database. In the case of PL/SQL files I would like to be able to resubmit them to the database after checkin (so that the checkin comments and version numbers are up to date in the database. The external tools interface doesn't reliably provide connection details to allow this.

Modifications

You can replace the commit command with other cvs commands, or you can make the cvs command a prompted parameter.

Thursday, November 23, 2006

Naming oracle databases - Part 1There are many names which can be considered the database name, depending on the point of view.

DBID - system generated database identifier

SID - System IDentifier

DB_NAME - The real name of the database.

GLOBAL_NAME - combination of DNAME and DBDOMAIN

DB_UNIQUE_NAME - used in data guard to distinguish between copies of the same database

SERVICE - the 'published' name under which end users would access a databas

DBID

This is not strictly a name, but is the one absolutely constant identfier for a database. It is generated when you create a database and if you are lucky you will never see it again. It becomes important if you have to restore a database on to a clean machine. You can find the DBID by querying V$DATABASE.

SID

This is the system - or instance - identifier. The instance is the set of processes which manage the database. The SID is used as the environment variable ORACLE_SID and is key to database admin tasks such as starting and stopping the database. You specify the SID when you create the instance.

DB_NAME

This IS the name of the database, but for most purposes is largely irrelevant. You never connect to a database, you connect to an instance or a service. You specify the db_name when you create the database by setting the DB_NAME initialisation parameter. The DB_NAME can be different from the instance name, but (except in the case of a RAC setup) there is no real reason to make it different.

SERVICE

This is where it starts to get complicated. The service is the public face of the database and is what clients connect to. The complexity arises because the service name can be defined in 3 different places - in the database; in the listener; and in the client.

In the database

Since Oracle8i, a running instance publishes its availability by registering with a listener. The SERVICE_NAMES initialisation parameter defines the name(s) to be registered. The default is the same as SID.

In the listener

You can override the self-registered name by added service entries to the listener configuration LISTENER.ORA.

This entry defines a "service" called BLAG, which connects to the service defined on the server as BLOG (which in turn uses the database ODBUAD1).

While it is possible to have several different names in several different places for the same database, and multiple names sometimes have their uses, for ease of management it is generally best to keep all the names the same. In a non-RAC environment the main use for different names is to isolate users from changes in the backend.