Even the best-designed database eventually
has to be changed. New log group member files may need to be
added, data files may need to be renamed or moved, archive logging
status may need to be changed, and so on. These tasks are all
accomplished through the use of the ALTER DATABASE command. Let's
look at a simplified command format and some of the options.
ALTER
DATABASE name
[NO]MOUNT
[STANDBY|CLONE] DATABASE
CONVERT
OPEN [READ[ ONLY]|WRITE][RESETLOGS|NORESETLOGS]
ACTIVATE STANDBY DATABASE
ARCHIVELOG|NOARCHIVELOG
Recover_clause
ADD LOGFILE [THREAD n] [GROUP n] file_spec
ADD LOGFILE MEMBER file_spec [REUSE] TO GROUP n|group_spec
DROP LOGFILE [GROUP n|group spec] file_spec
DROP LOGFILE MEMBER file_spec
CLEAR
[UNARCHIVED] LOGFILE [GROUP n|group_spec] file_spec UNRECOVERABLE
DATAFILE
RENAME file_spec TO file_spec
CREATE STANDBY CONTROLFILE AS file_name REUSE
BACKUP CONTROLFILE TO file_name REUSE|TO TRACE
RESETLOGS|NORESETLOGS
RENAME GLOBAL
NAME TO database[.domain]
RESET
COMPATIBILITY
ENABLE [PUBLIC] THREAD n
DISABLE THREAD n
CREATE DATAFILE
file_spec AS file_name
DATAFILE
file_name
[ONLINE|OFFLINE[ DROP]]
RESIZE n [K|M]
AUTOEXTEND CLAUSE
END BACKUP
CHARACTER SET
NATIONAL CHARACTER SET
Set time
zone clauses
DEFAULT TEMPORARY
TABLESPACE tablespace
where:
DATABASE name. Assigned to a maximum of
eight characters. If it is not specified, the value in the
INIT.ORA file will be used.
File_spec. A file specification in the
format of ?filename? SIZE integer K or M REUSE, with filename an
OS-specific full path name; K or M specifies integer as kilobytes
or megabytes; and REUSE specifies to reuse the current file if it
exists. If SIZE isn't specified, 500K will be used. REUSE is
optional.
File_name. A full path filename.
MOUNT. Database is available for
some Remote DBA functions, but not normal functions. Either
exclusive, which is default, or PARALLEL.
STANDBY DATABASE. With version 7.3
and greater, the command operates against a hot-standby database
(see Chapter 15, Backup and Recovery Procedures for Oracle).
CLONE. With 8i a clone
database is used in the recovery of a tablespace to a point in
time.
OPEN. Database is mounted and
opened for general use, either with RESET LOGS (default) or
NORESET LOGS (see Chapter15). @@@Au: Subsequent xrefs to chapter,
give chapter number only; not necessary to repeat title.@@@
ACTIVATE STANDBY DATABASE.
See Chapter 15.
ADD LOGFILE THREAD. Adds a thread
or redo to a PARALLEL instance.
ADD LOGFILE MEMBER. Adds a logfile
member to an existing group.
CLEAR. Reinitializes an online
redo log and, optionally, does not archive the redo log. CLEAR
LOGFILE is similar to adding and dropping a redo log except that
the command may be issued even if there are only two logs for the
thread, and it also may be issued for the current redo log of a
closed thread.
CLEAR LOGFILE. Cannot be used
to clear a log needed for media recovery. If it is necessary to
clear a log containing redo after the database checkpoint, then
incomplete media recovery will be necessary. The current redo log
of an open thread can never be cleared. The current log of a
closed thread can be cleared by switching logs in the closed
thread.
If the CLEAR LOGFILE command is
interrupted by a system or instance failure, then the database may
hang. If so, the command must be reissued once the database is
restarted. If the failure occurred because of I/O errors accessing
one member of a log group, then that member can be dropped and
other members added.
UNARCHIVED. Must be specified if
you want to reuse a redo log that was not archived. Note that
specifying UNARCHIVED will make backups unusable if the redo log
is needed for recovery.
UNRECOVERABLE DATAFILE. Must be
specified if the tablespace has a data file offline, and the
unarchived log must be cleared to bring the tablespace online. If
so, then the data file and entire tablespace must be dropped once
the CLEAR LOGFILE command completes.
DROP LOGFILE. Drops an
existing log group.
DROP LOGFILE MEMBER. Drops an
existing log member.
RENAME. Renames the specified
database file.
ARCHIVELOG/NOARCHIVELOG.
Turns archive logging on or off.
RECOVER. Puts database into
recovery mode. The form of recovery is specified in the recovery
clause. (See Chapter 15)
BACKUP CONTROLFILE. This can be
used in two ways: first, to make a recoverable backup copy of the
control file ('tO 'filename'?) and, second, to make a script to
rebuild the control file (? TO TRACE?).
CREATE DATAFILE. Creates a
new data file in place of an old one. You can use this option to
re-create a data file that was lost with no backup. The ?filename?
must identify a file that was once a part of the database. The
filespec specifies the name and size of the new data file. If you
omit the AS clause, ORACLE creates the new file with the same name
and size as the file specified by 'filename'.
CREATE STANDBY CONTROLFILE.
Creates a control file for use with the standby database.
DATAFILE. Allows you to perform
manipulations against the data files in the instance such as
resizing, turning autoextend on or off, and setting backup status.
ENABLE and DISABLE threads.
Allows the enabling and disabling of redo log threads (only used
for parallel databases).
RESET COMPATIBILITY. Marks
the database to be reset to an earlier version of Oracle7 when the
database is next restarted. This will render archived redo logs
unusable for recovery.
Tip: The RESET COMPATIBILITY option will
not work unless you have successfully disabled Oracle9i features
that affect backward compatibility.
RENAME GLOBAL_NAME TO.
Changes the global name of the database. A rename will
automatically flush the shared pool. It doesn't change data
concerning your global name in remote instances, connect strings,
or db links.
Some examples of the use of ALTER DATABASE
are:
* To mount a database PARALLEL:
ALTER DATABASE dbname MOUNT PARALLEL
* To drop a logfile member:
ALTER DATABASE
DROP LOGFILE '/oracle1/ORTEST1/redo/ORTEST1_redo31.log'
and include the URL for the page.
