Oracle

How do I access SQL/Plus and other Oracle utilities on a VMS system?

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 have the environment set up automatically on login, run this command file in your login.com file.
To access SQLPLUS use the following command with a valid username and password:

$ SQLPLUS username/password

SQLDBA is also available on VMS and can be invoked similarly:

$ SQLDBA username/password

CTRL/K can be used to show the various key mappings required to drive the menu system of SQLDBA.

Where are all the Oracle files?

When Oracle is installed on VMS 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.

How do I start up and shut down a database?

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

To start this database automatically when the VMS system is rebooted place these commands in a command procedure, e.g. DUA0:[[ORACLE7]]START_SALES.COM. Then edit the system startup file SYS$MANAGER:SYSTARTUP_VMS.COM and add the following command at the end of the file:

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

This will start a batch job running under the Oracle7 user account which will start up the database instance SALES.
A database can be shut down by running the command procedure SHUTDOWN_dbname.COM which is found in the database's home directory.

How do I create a new database instance on VMS?

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.

How do I delete a database instance on VMS?

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

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

How do I set up SQL/NET on VMS?

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

When a server connection is initiated, the commands specified in the program file pointed at by the PROGRAM qualifier are executed. This file must set up the Oracle environment and start the server process. It is important to keep this file as small as possible to reduce initial connection time when connecting from clients.
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

There are three commands to control the listener process:

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

The LSNRCTL START command should be included in the system startup file if you require SQL/NET to be started automatically on VMS startup.

How does one allocate more OS memory to the SGA?

Login as Oracle, and then:

• $ORACLEINS - choose option RECONFIGURE EXISTING PRODUCTS
• Prompt for default directory - accept or specify new directory
• Prompt for the location of the SAVE SET - accept or specify new directory
• Display "Doing some setup" will appear - when finished press ENTER
• Choose Software Installation and Upgrade Menu
• Choose Build Configuration Options
• Choose RDBMS Option
• Modify the value for SGA PAD
• When back at prompt choose REBUILD
• Exit (E option)
• Choose option Build Selected Products
• When relinking is finished exit out of the menu