4
Managing Instances

Because you access a database with an instance, managing instances is an integral part of database administration. This chapter describes how to control and manage both single and parallel instances.

The following topics are presented:

Managing Single Instances

This section discusses the following topics:

Components of Instances

Information for each instance is recorded in a file named ORA_RDBMS:ORA_RDBMS_SIDS.DAT. This file contains the instance SID, the name of the database associated with the instance, and the name of the node where the instance was set up. This file is used to determine whether a SID is currently in use and if it is defined for a given database on a given node.

The following files are associated with an instance:

Instance-specific initialization file
Instance-specific trace file

Controlling Instances

You can use ORACLEINS to set up an instance on a particular node to access a particular database.

The following command files, located in the ORA_DB directory of each database, start up, shut down, and define logical names for all instances of database <dbname>:

STARTUP_<dbname>.COM

SHUTDOWN_<dbname>.COM

ORAUSER_<dbname>.COM

Deleting Instances

To delete an instance, perform the following procedure:

Shut down the instance on the node on which the instance is running.
Edit ORA_RDBMS:ORA_RDBMS_SIDS.DAT and remove the instance entry.
Edit the ORA_UTIL:DATABASE.TXT file and remove the instance entry.
Delete the ORA_DB:<nodename>_<sid>_INIT.ORA file.

Delete the following instance-specific files in the TNS_ADMIN directory:

orasrv_beq_'sid'.com

orasrv_netv2_'sid'.com
orasrv_hsalloci_'sid'.com

Clean out the instance's trace files in ORA_DUMP.

Adding and Enabling Instance Threads

With Oracle8i, each instance writes to its own log files. A given log file is not associated with any particular instance like rollback segments are, but is instead associated with a thread number.

Each instance that starts has the next available thread number assigned to it. Before a thread number can be assigned, it must have log files associated with it and these log files must be enabled. Refer to the SQL Language Reference Manual for more information.

Adding threads for new instances is accomplished in two steps:

Add log files to the database, associating each log file with a thread using the ALTER DATABASE command:
```
ALTER DATABASE ADD LOGFILE THREAD <number> <filespec> <storage>;  
```

For example:

ALTER DATABASE ADD LOGFILE THREAD 2

	'DISK$DEV1:[ORALOG]ORA_LOG3.RDO' SIZE 2000K REUSE,
	'DISK$DEV1:[ORALOG]ORA_LOG4.RDO' SIZE 2000K REUSE;

When adding log files to your database, if you do not specify the thread number, the log file is associated with the thread number of the instance to which you are connected. Once a log file is associated with a thread, it is very difficult to change its thread number. Each thread you want to use must have at least two log files associated with it.

After log files are associated with a thread, you must enable the thread for it to be usable when you try to start your instances.
```
ALTER DATABASE ENABLE PUBLIC THREAD <number>  
```

For example:

ALTER DATABASE ENABLE PUBLIC THREAD 4;

The thread for the initial instance is enabled by default, and all log files created with the initial database are associated with this thread. Additional instances, even if they are set up and have rollback segments identified in their instance-specific INIT.ORA files, cannot be started until additional log files (associated with unique thread numbers) have been created and their respective threads have been enabled.

Preparing an Instance for Parallel Query

Parallel Query is an option that allows parallel query processing, index creation, and data loading.

To prevent a parallel query server from timing out, you can modify your INIT.ORA file by setting one of the following parameters:

Set PARALLEL_SERVER_IDLE_TIME to 0
Set PARALLEL_MIN_SERVERS to the same value as PARALLEL_MAX_SERVERS

These modifications will prevent trace files from being generated when the parallel query servers time out.

For more information on the parallel query option, refer to the Oracle8i Server Administrator's Guide.

4 Managing Instances