Oracle for VMS FAQ

$Date: 07-Apr-1999 $
$Revision: 1.1 $
$Author: Ade Bullock $

Topics

A quick overview of the VMS environment for a new user.

DCL Commands

DIRECTORY	- to list contents of a directory
SHOW DEFAULT	- to show current working directory
SET DEFAULT	- to change current working directory
EDIT filename	- to edit a file!
TYPE filename	- to list a file's contents on screen
HELP	- to get on-line help
SEARCH	- to search a set of files for a string
DELETE filename	- deletes the file
PURGE filename	- delete old versions of the file
RENAME	- rename the file
RUN filename	- runs a program - all programs have an extension .EXE
@filename	- runs a command procedure (has a .COM extension)

Only the first significant characters of a command need to be entered, e.g. DIRECTORY can be shortened to DIR. Command shortcuts can also be created, e.g. entering the command PWD :== SHOW DEFAULT will allow you to use the command PWD instead of show default and entering CD :== SET DEFAULT will allow the use of CD to change directory.

File Specifications

File specifications are a little different in VMS from other operating systems. For instance the windows file specification c:\windows\system\readme.txt would look a little like the following in VMS:

DUA0:[WINDOWS.SYSTEM]README.TXT;1

The ;1 indicates version 1 of the file. VMS creates a new version of a file when it is changed (don't you wish DOS did this). You can however start eating large amounts of disk space with multiple versions of the file so regular use of the PURGE command is recommended.

Logical Names

One very useful feature of logical names is it permits location independence for files. In the above example the directory can be renamed or moved to a different disk and provided the logical name is changed any commands or programs using the directory will still work. I would recommend that wherever a directory specification is required (either in a program or command file) a logical name is used instead. ORACLE on VMS makes extensive use of logical names.

There is no real equivalent of a path in VMS. Logical names provide a much more flexible and manageable architecture than Unix or Dos paths. (It is actually possible to set up a logical name to point to several directories which performs a similar function to setting up a path).

Automatically setting up a Customised VMS environment.

The Oracle environment for a VMS user is set up by running the appropriate ORAUSER_dbname.COM file. This sets up the necessary command symbols and logical names to access the various ORACLE utilities. Each database created on a VMS system will have an ORAUSER file in it's home directory and will be named ORAUSER_dbname.COM, e.g. for a database SALES the file specification could be:

ORA_ROOT:[DB_SALES]ORAUSER_SALES.COM

To access SQLPLUS use the following command with a valid username and password:

$ SQLPLUS username/password

$ SQLDBA username/password

When Oracle is installed a root directory is chosen which is pointed to by the logical name ORA_ROOT. This directory can be placed anywhere on the VMS system. The majority of code, configuration files and command procedures are found below this root directory.

When a new database is created a new directory is created in the root directory to store database specific configuration files. This directory is called [.DB_dbname]. This directory will normally hold the system tablespace data file as well as the database specific startup, shutdown and orauser files.

There are several methods available for database startup and shutdown. ORACLEINS (the Oracle install program) and SQLDBA both have menu driven methods to start or stop a database.

Alternatively use command files. The following commands will start a database called SALES (the command INSORACLE will install various shared images which improve Oracle performance):

$ @ORA_ROOT:[DB_SALES]ORAUSER_SALES
$ INSORACLE
$ @ORA_ROOT:[DB_SALES]STARTUP_EXCLUSIVE_SALES

$ SUBMIT/USER=ORACLE7 DUA0:[ORACLE7]START_SALES

A database can be shut down by running the command procedure SHUTDOWN_dbname.COM which is found in the database's home directory.

The easiest method is to use ORACLEINS and follow the menu driven commands to create a new database instance. The utility will ask for the database root directory, an instance name and a database name. ORACLEINS will present a set of default parameters for the database. These can be individually modified, in particular the database control files should be placed on different disks.

There is no utility to delete a database however Oracle recommends the following procedure:

1. Shut down the database.
2. Edit the file ORA_RDBMS:ORA_RDBMS_SIDS.DAT and remove the reference to the database instance.
3. Do the same again to the file ORA_UTIL:DATABASE.TXT
4. All the database files can now be deleted without confusing Oracle.

First, set up the LISTENER.ORA configuration file which defines the databases for which the listener will serve. The format of this file is well documented elsewhere and will look something like :

#LISTENER.ORA
LISTENER=
        (ADDRESS_LIST=
                (ADDRESS=
                        (PROTOCOL=TCP)
                        (HOST=LIVE)
                        (PORT=1526)
                )
        )
SID_LIST_LISTENER=
        (SID_LIST=
                (SID_DESC=
                        (SID_NAME=SALES)
                        PROGRAM='DUA0:[ORACLE7.NETWORK.
ADMIN]SRV_SALES.COM')
                )
        )

An example program file is shown below:

$ PID=F$GETJPI("","PID")
$ TAB="TNS_"+PID
$ ON ERROR GOTO FINISH
$!
$ DEFINE ORA_SID SALES     ! The database name
$!
$ DEFINE ORA_SYSTEM     DUA0:[ORACLE7.RDBMS]
$ DEFINE ORA_RDBMS      DUA0:[ORACLE7.RDBMS]
$ DEFINE ORA_COMMON DUA0:[ORACLE7.RDBMS]
$ DEFINE ORA_DUMP       DUA0:[ORACLE7.TRACE]
$ DEFINE TNS_ADMIN      DUA0:[ORACLE7.NETWORK.ADMIN]
$!
$ ORASRV := $ORA_SYSTEM:SRV.EXE
$ ORASRV "(LOCAL=NO)"
$!
$FINISH:
$ STAT = $STATUS
$ ON ERROR THEN CONTINUE
$ DEASSIGN/USER/TABLE=LNM$SYSTEM_DIRECTORY 'TAB'
$ EXIT STAT

$ LSNRCTL START         - start the listener
$ LSNRCTL STATUS        - return listener status
$ LSNRCTL STOP          - close the listener

• Oracle on OpenVMS - excellent resource of Oracle/VMS information

• Oracle/ VMS Special Interest Group

• DEC's VMS homepage

• DEC VMS FAQ

• DEC OpenVMS FAQ