Alteration of Databases
Oracle Tips by Burleson Consulting
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.
[NO]MOUNT [STANDBY|CLONE] DATABASE
OPEN [READ[ ONLY]|WRITE][RESETLOGS|NORESETLOGS]
ACTIVATE STANDBY DATABASE
ADD LOGFILE [THREAD n] [GROUP n] file_spec
ADD LOGFILE MEMBER file_spec [REUSE] TO GROUP
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
RENAME GLOBAL NAME TO database[.domain]
ENABLE [PUBLIC] THREAD n
DISABLE THREAD n
CREATE DATAFILE file_spec AS file_name
RESIZE n [K|M]
NATIONAL CHARACTER SET
Set time zone clauses
DEFAULT TEMPORARY TABLESPACE tablespace
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
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
DROP LOGFILE MEMBER. Drops an
existing log member.
RENAME. Renames the specified
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
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
* To mount a database PARALLEL:
ALTER DATABASE dbname MOUNT PARALLEL
* To drop a logfile member:
DROP LOGFILE '/oracle1/ORTEST1/redo/ORTEST1_redo31.log'
Code Depot for Full Scripts
This is an excerpt from Mike Ault, bestselling author of "Oracle
10g Grid and Real Application Clusters".
You can buy it direct from the publisher for 30%-off and get instant access to
the code depot of Oracle tuning scripts.
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.