Remote DBA Plans
Remote DBA Service

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.

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'

See 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.