5
Database Administration

This chapter provides a brief overview of the database administration tools available in SQL*Plus, and discusses the following topics:

This chapter is intended for use by database administrators. In order to access the functionality of the commands mentioned in this chapter, database administrator privileges are necessary.

For more information on database administration, see the Oracle8i Concepts manual.

Overview

Special operations such as starting up or shutting down a database are performed by a database administrator (DBA). The DBA has certain privileges that are not assigned to normal users. The commands outlined in this chapter would normally be used by a DBA.

For more information about security and roles in SQL*Plus, see Appendix E.

Introduction to Database Startup and Shutdown

An Oracle database may not always be available to all users. To open or close a database, or to start up or shut down an instance, you must have dba privileges or be connected as SYSOPER or SYSDBA. Other users cannot change the current status of an Oracle database.

You cannot use STARTUP or SHUTDOWN to start or stop Oracle instances on Oracle7 servers.

Database Startup

Starting a database involves three steps:

Starting an instance

An instance controls the background processes and the allocation of memory area to access an Oracle database.

Mounting the database

Mounting the database associates it with a previously started instance.

Opening the database

Opening the database makes it available for normal database operations.

For more information about database startup, see the Oracle8i Concepts manual. For information about the STARTUP command, see the Command Reference in Chapter 8.

Example 5-1 Starting an Instance

To start an Oracle instance, without mounting the database, enter

SQL> STARTUP NOMOUNT

Example 5-2 Mounting the Database

To start an instance, mount the database, but leave the database closed, enter

SQL> STARTUP MOUNT

Example 5-3 Opening the Database

To start an instance using the Oracle8i Server parameter file INITSALE.ORA, mount and open the database named SALES in exclusive mode, and restrict access to administrative personnel, enter

SQL> STARTUP OPEN sales PFILE=INITSALE.ORA EXCLUSIVE RESTRICT

where SALES is the database name specified in the DB_NAME parameter in the INITSALE.ORA parameter file.

Database Shutdown

Shutting down a database involves three steps:

Closing the database

When a database is closed, all database and recovery data in the SGA are written to the datafiles and redo log files, and closes all online datafiles.

Dismounting the database

Dismounting the database disassociates the database from an instance and closes the control files of the database.

Shutting down the instance

Shutting down an instance reclaims the SGA from memory and terminates the background Oracle processes that constitute an Oracle instance.

For more information about database shutdown, see the Oracle8i Concepts manual. For information about the SHUTDOWN command, see the "Command Reference" in Chapter 8.

Example 5-4 Shutting Down the Database

To shut down the database normally after it has been opened and mounted, enter

SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.

Redo Log Files

Every Oracle database has a set of two or more redo log files. The set of redo log files for a database is collectively referred to as the database's redo log.

The redo log is used to record changes made to data. If, for example, there is a database failure, the redo log is used to recover the database. To protect against a failure involving the redo log itself, Oracle allows a mirrored redo log so that two or more copies of the redo log can be maintained on different disks.

ARCHIVELOG Mode

Operating a database in ARCHIVELOG mode enables the archiving of the online redo log.

The ARCHIVE LOG command permits a complete recovery from disk failure as well as instance failure, because all changes made to the database are permanently saved in an archived redo log.

For more information about redo log files and database archiving modes, see the Oracle8i Concepts manual. For information about using the ARCHIVE LOG command, see the "Command Reference" in Chapter 8.

To automatically begin archiving, enter

SQL> ARCHIVE LOG START

To list the details of the current log file being archived, enter

SQL> ARCHIVE LOG LIST

SQL*Plus displays results similar to the following:

Database log mode              Archive Mode 
Automatic archival             Enabled 
Archive destination            /vobs/oracle/dbs/arch 
Oldest online log sequence     221 
Next log sequence to archive   222 
Current log sequence           222

Database Recovery

If a damaged database is in ARCHIVELOG mode, it is a candidate for either complete media recovery or incomplete media recovery operations. To begin media recovery operations use the RECOVER command. For more information about using the RECOVER command, see the "Command Reference" in Chapter 8.

In order to begin recovery operations, you must have DBA privileges.

To recover the database up to a specified time using a control backup file, enter

SQL> RECOVER DATABASE UNTIL TIME '1998-11-23:12:47:30'-
>    USING BACKUP CONTROLFILE

To recover two offline tablespaces, enter

SQL> RECOVER TABLESPACE ts1, ts2

Make sure that the tablespaces you are interested in recovering have been taken offline, before proceeding with recovery for those tablespaces.

5 Database Administration