8.6 Database administration

---

Space management, instance management, schema management, reorga nization, etc., are all common database administration functions that are also applicable in a RAC configuration. This section will look at some of the administration activities around the various tasks listed above and will also look at some day-to-day basic tasks like starting and stopping of instances and configuration of SQL_Net.

8.6.1 Space management

Space management depends on the type of storage used for implementing a RAC configuration. For example, when using locally managed tablespaces and automatic segment management on raw devices, it is important that the tablespaces should not be created with autoextend or unlimited clauses. Raw partitions are fixed in size, which means that once the maximum size at which the partition has been created is reached, it cannot grow further. However, in the case of a CFS configuration, unlimited tablespace quota and autoextend do not have such restrictions.

8.6.2 Automatic undo management (AUM)

Under this feature, Oracle manages the segment free and used space with bitmaps, as opposed to free lists and free list groups. Automatic segment-space management is simpler to administer than free lists and it provides improved space utilization. To use AUM the following parameters have to be defined in the parameter file:

• UNDO_MANAGEMENT =<AUTO>: This parameter enables the undo management feature and tells Oracle at instance startup that AUM has been requested.

• UNDO_TABLESPACE =<filename>: This parameter is used to assign the name of the UNDO tablespace for the instance. It is recommended that each instance will have its own UNDO tablespace. The drawback of not setting this parameter in the parameter file and making it available to Oracle during instance startup is that Oracle will use the first available UNDO tablespace.

Using the AUM option for tablespace management ignores most of the storage definition values. For example, the FREE LISTS, FREE LIST GROUPS, and PCTUSED are ignored for bitmap segments. These columns contain NULL values in the database dictionary views. However, PCTFREE can be specified for bitmap segments.

UNDO tablespace files can be switched by using the following command:

ALTER SYSTEM SET UNDO_TABLESPACE = <tablesapce name>

This command will switch the undo tablespace to this new tablespace.

8.6.3 SYSTEM rollback segment

The only external rollback segment used by Oracle with AUM is the SYSTEM rollback segment. Each database has only one SYSTEM rollback segment and RAC uses the same SYSTEM rollback segment for all instances.

8.6.4 Schema management

Management of user definitions or objects such as tables, views, indexes, etc., created by a user is called schema management. This includes reorganization of tables and indexes.

8.6.5 Setting environment variables

On Unix platforms, certain commands or group of commands can be redefined by using environment variables. For example, ORACLE_HOME is an environment variable that points to the Oracle home directory. Similarly, if the node has multiple instances, before accessing any specific instance via SQL_Plus, it is required that the SID environment variable point to the SID that is of interest or the SID be specified as part of the connect command.

The following command will set the environment variable for the SID:

oracle$ ORACLE_SID =RAC1 oracle$ export ORACLE_SID

8.6.6 Instance management

In this section the various instance management operations and verification process will be discussed. Many of the instance management operations like starting, stopping of instances, starting, stopping of listener, etc., are similar to the operations on a single stand-alone configuration of Oracle.

Shutting down an instance

This operation is similar to shutdown processes on a regular stand alone configuration:

oracle$ sqlplus '/as sysdba' SQL> shutdown immediate;

This command shuts down the instance immediately after all the transactions that are active on the instance have committed their work:

SQL>shutdown abort;

This command shuts down the instance abnormally. Oracle forces all user processes running in that instance to log off the database. The user that was terminated by this abnormal operation receives the following error message ''ORA-1092: Oracle instance terminated. Disconnection forced.'' However, if the user process is not currently accessing the database, the user receives the following error message: ''ORA-1012 Not logged on.''

When the instance is shut down abnormally, Oracle has to perform recovery on instance restart or, in the case of RAC, as the surviving instances will perform the recovery task:

SQL>shutdown transactional

This command with the LOCAL option will shut down the instance after all the active transactions on the instance have either committed or rolled back. If the LOCAL option is not specified, then the shutdown transaction has a global effect, in the sense that it waits until the active transactions on all other instances have issued either a commit or rollback operation.

Starting an instance

The following command will start the local instance or the instance where the environment variable is currently pointing:

oracle$ sqlplus '/as sysdba' SQL*Plus: Release 9.2.0.1.0 - Production on Wed Oct 9 18:07:16 2002 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 450937896 bytes Fixed Size 730152 bytes Variable Size 285212672 bytes Database Buffers 163840000 bytes Redo Buffers 1155072 bytes Database mounted. Database opened. SQL>

Quiescing a RAC database

The quiescing of a RAC database is similar to quiescing a single stand- alone database, except for certain limitations. When quiescing a database from an instance, the other instances cannot open the database. For example, when an ALTER SYSTEM QUIESCE RESTRICTED statement is issued and Oracle has not completed processing this statement, the database cannot be opened from the current or any other instance participating in the clustered configuration.

Verifying the instances

From any of the instances participating in the clustered configuration, the following command will display a list of instances.

SQL> SELECT * FROM V$ACTIVE_INSTANCES;

• INST_NUMBER identifies the instance number.

• INST_NAME identifies the host name and instance name.

---