database
identifies the database to be altered. If you omit database, Oracle7 alters the database identified by the value of the initialization parameter DB_NAME. You can only alter the database whose control files are specified by the initialization parameter CONTROL_FILES. Note that the database identifier is not related to the SQL*Net database specification.
You can only use the following options when the database is not mounted by your instance:
MOUNT
STANDBY DATABASE
mounts the standby database. For more information, see the Oracle7 Server Administrator's Guide.
The default is EXCLUSIVE.
CONVERT
completes the conversion of the Oracle Version 6 data dictionary. After you use this option, the Version 6 data dictionary no longer exists in the Oracle7 database. Only use this option when you are migrating to Oracle7. For more information on using this option, see Oracle7 Server Migration.
OPEN
opens the database, making it available for normal use. You must mount the database before you can open it. You cannot open a standby database that has not been activated.
RESETLOGS resets the current log sequence number to 1 and discards any redo information that was not applied during recovery; ensuring that it will never be applied. This effectively discards all changes to the database. You must use this option to open the database after performing media recovery with an incomplete recovery using the RECOVER UNTIL clause (see ![[*]](jump.gif)
) or with a backup controlfile. After opening the database with this option, you should perform a complete database backup.
NORESETLOGS
leaves the log sequence number and redo log files in their current state.
You can only specify the above options after performing incomplete media recovery or complete media recovery with a backup controlfile. In any other case, Oracle7 uses the NORESETLOGS automatically.
ACTIVATE STANDBY DATABASE
changes the state of a standby database to an active database. For more information, see
Oracle7 Server Administrator's Guide.
You can only use the following options when your instance has the database mounted in exclusive mode, but not open:
ARCHIVELOG
establishes archivelog mode for redo log file groups. In this mode, the contents of a redo log file group must be archived before the group can be reused. This option prepares for the possibility of media recovery. You can only use this option after shutting down your instance normally or immediately with no errors and then restarting it, mounting the database in exclusive mode.
NOARCHIVELOG
establishes noarchivelog mode for redo log files. In this mode, the contents of a redo log file group need not be archived so that the group can be reused. This mode does not prepare for recovery after media failure.
RECOVER
performs media recovery. See the RECOVER clause . You only recover the entire database when the database is closed. You can recover tablespaces or datafiles when the database is open or closed, provided the tablespaces or datafiles to be recovered are offline. You cannot perform media recovery if you are connected to Oracle7 through the multi-threaded server architecture. You can also perform media recovery with the Server Manager recovery dialog box.
You can use any of the following options when your instance has the database mounted, open or closed, and the files involved are not in use:
ADD LOGFILE
adds one or more redo log file groups to the specified thread, making them available to the instance assigned the thread. If you omit the THREAD parameter, the redo log file group is added to the thread assigned to your instance. You need only use the THREAD parameter if you are using Oracle7 with the Parallel Server option in parallel mode.
Each filespec specifies a redo log file group containing one or more members, or copies. See the syntax description of filespec .
You can choose the value of the GROUP parameter for each redo log file group. Each value uniquely identifies the redo log file group among all groups in all threads and can range from 1 to the MAXLOGFILES value. You cannot add multiple redo log file groups having the same GROUP value. If you omit this parameter, Oracle7 generates its value automatically. You can examine the GROUP value for a redo log file group through the dynamic performance table V$LOG.
ADD LOGFILE MEMBER
adds new members to existing redo log file groups. Each new member is specified by 'filename'. If the file already exists, it must be the same size as the other group members and you must specify the REUSE option. If the file does not exist, Oracle7 creates a file of the correct size. You cannot add a member to a group if all of the group's members have been lost through media failure.
You can specify an existing redo log file group in one of these ways:
GROUP parameter You can specify the value of the GROUP parameter that identifies the redo log file group.
list of filenames You can list all members of the redo log file group. You must fully specify each filename according to the conventions for your operating system.
DROP LOGFILE
drops all members of a redo log file group. You can specify a redo log file group in the same manner as the ADD LOGFILE MEMBER clause. You cannot drop a redo log file group if it needs archiving or is the currently active group. Nor can you drop a redo log file group if doing so would cause the redo thread to contain less than two redo log file groups.
DROP LOGFILE MEMBER
drops one or more redo log file members. Each 'filename' must fully specify a member using the conventions for filenames on your operating system.
You cannot use this clause to drop all members of a redo log file group that contain valid data. To perform this operation, use the DROP LOGFILE clause.
CLEAR LOGFILE
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 you must specify UNARCHIVED if you want to reuse a redo log that was not archived.
Warning: Specifying UNARCHIVED will make backups unusable if the redo log is needed for recovery.
UNRECOVERABLE DATAFILE
you must specify UNRECOVERABLE DATAFILE if the tablespace has a datafile offline and the unarchived log must be cleared to bring the tablespace online. If so, then the datafile and entire tablespace must be dropped once the CLEAR LOGFILE command completes.
RENAME FILE
renames datafiles or redo log file members. This clause only renames files in the control file, it does not actually rename them on your operating system. You must specify each filename using the conventions for filenames on your operating system.
CREATE STANDBY CONTROLFILE
create a controlfile to be used to maintain a standby database. For more information, see Oracle7 Server Administrator's Guide.
BACKUP CONTROLFILE
backs up the current control file.
TO 'filename' specifies the file to which the control file is backed up. You must fully specify the 'filename' using the conventions for your operating system. If the specified file already exists, you must specify the REUSE option.
TO TRACE writes SQL statements to the database's trace file, rather than making a physical backup of the control file.
The SQL commands can be used to start up the database, re-create the control file, and recover and open the database appropriately, based on the created control file.
You can copy the commands from the trace file into a script file, edit the commands as necessary, and use the script to recover the database if all copies of the control file are lost (or to change the size of the control file).
RESETLOGS the SQL statement written to the trace file for starting the database is ALTER DATABASE OPEN RESETLOGS.
NORESETLOGS
the SQL statement written to the trace file for starting the database is ALTER DATABASE OPEN NORESETLOGS.
You can only use the following options when your instance has the database open:
ENABLE
in a parallel server, enables the specified thread of redo log file groups. The thread must have at least two redo log file groups before you can enable it.
If you omit the PUBLIC option, the thread is only available to the instance that explicitly requests it with the initialization parameter THREAD.
DISABLE
disables the specified thread, making it unavailable to all instances. You cannot disable a thread if an instance using it has the database mounted.
RENAME GLOBAL_NAME
changes the global name of the database. The database is the new database name and can be as long as eight bytes. The optional domains specifies where the database is effectively located in the network hierarchy. Renaming your database automatically clears all data from the shared pool in the SGA. However, renaming your database does not change global references to your database from existing database links, synonyms, and stored procedures and functions on remote databases. Changing such references is the responsibility of the administrator of the remote databases.
For more information on global names, see the "Network Administration" chapter of Oracle7 Server Distributed Systems, Volume I.
RESET COMPATIBILITY
mark the database to be reset to an earlier version of Oracle7 when the database is next restarted.
Note: RESET COMPATIBILITY will not work unless you have successfully disabled Oracle7 features that affect backward compatibility.
For more information on downgrading to an earlier version of Oracle7, see the "Upgrading and Downgrading" chapter of Oracle7 Server Migration.
SET
for Trusted Oracle7, changes one of the following:
DBHIGH equates the predefined label DBHIGH to the operating system label specified by 'text'.
DBLOW equates the predefined label DBLOW to the operating system label specified by 'text'.
DBMAC ON configures Trusted Oracle7 in DBMS MAC mode.
DBMAC OFF configures Trusted Oracle7 in OS MAC mode.
You must specify labels in the default label format for your session. Changes made by this option take effect when you next start your instance. You can only use this clause if you are using Trusted Oracle7. For more information on this clause, see the Trusted Oracle7 Server Administrator's Guide.
You can use any of the following options when your instance has the database mounted, open or closed, and the files involved are not in use:
CREATE DATAFILE
creates a new empty datafile in place of an old one. You can use this option to re-create a datafile that was lost with no backup. The '
filename' must identify a file that is or was once part of the database. The filespec specifies the name and size of the new datafile. If you omit the AS clause, Oracle7 creates the new file with the same name and size as the file specified by 'filename'.
During recovery, all archived redo logs written to since the original datafile was created must be applied to the new, empty version of the lost datafile.
Oracle7 creates the new file in the same state as the old file when it was created. You must perform media recovery on the new file to return it to the state of the old file at the time it was lost.
You cannot create a new file based on the first datafile of the SYSTEM tablespace.
DATAFILE
changes one of the following for your database:
ONLINE brings the datafile online.
OFFLINE takes the datafile offline.
If the database is open, then you must perform media recovery on the datafile before bringing it back online. This is because a checkpoint is not performed on the datafile before it is taken offline.
DROP takes a datafile offline when the database is in NOARCHIVELOG mode.
AUTOEXTEND enables or disables the automatic extension of a datafile.
OFF disable autoextend if it is turned on. NEXT and MAXSIZE are set to zero. Values for NEXT and MAXSIZE must be respecified in further ALTER DATABASE AUTOEXTEND commands.
ON enable autoextend.
MAXSIZE maximum disk space allowed for automatic extension of the datafile.
UNLIMITED set no limit on allocating disk space to the datafile.
Warning: Do not use ALTER TABLESPACE ... END BACKUP if you have restored any of the files affected from a backup. Media recovery is fully described in the Oracle7 Server Administrator's Guide.
For more information on using the ALTER DATABASE command for database maintenance, see the Oracle7 Server Administrator's Guide.
Example I
The following statement mounts the database named STOCKS exclusively:
ALTER DATABASE stocks MOUNT EXCLUSIVE
Example II
ALTER DATABASE stocks
ADD LOGFILE GROUP 3
('diska:log3.log' ,
'diskb:log3.log') SIZE 50K Example III
The following statement adds a member to the redo log file group added in the previous example:
ALTER DATABASE stocks ADD LOGFILE MEMBER 'diskc:log3.log' TO GROUP 3
Example IV
The following statement drops the redo log file member added in the previous example:
ALTER DATABASE stocks DROP LOGFILE MEMBER 'diskc:log3.log'
Example V
The following statement renames a redo log file member:
ALTER DATABASE stocks RENAME FILE 'diskb:log3.log' TO 'diskd:log3.log'
The above statement only changes the member of the redo log group from one file to another. The statement does not actually change the name of the file 'DISKB:LOG3.LOG' to 'DISKD:LOG3.LOG'. You must perform this operation through your operating system.
Example VI
The following statement drops all members of the redo log file group 3:
ALTER DATABASE stocks DROP LOGFILE GROUP 3
Example VII
ALTER DATABASE stocks
ADD LOGFILE THREAD 5 GROUP 4
('diska:log4.log',
'diskb:log4:log',
'diskc:log4.log' ) Example VIII
The following statement disables thread 5 in a parallel server:
ALTER DATABASE stocks DISABLE THREAD 5
Example IX
ALTER DATABASE stocks ENABLE PUBLIC THREAD 5
Example X
The following statement creates the datafile 'DISK1:DB1.DAT' based on the file 'DISK2:DB1.DAT':
ALTER DATABASE
CREATE DATAFILE 'disk1:db1.dat' AS 'disk2:db1.dat'
Example XI
ALTER DATABASE RENAME GLOBAL_NAME TO sales.australia.acme.com
Example XII
The following statement attempts to change the size of datafile 'DISK1:DB1.DAT':
ALTER DATABASE DATAFILE 'disk1:db1.dat' RESIZE 10 M
For examples of performing media recovery, see the Oracle7 Server Administrator's Guide.
Example XIII
The following statement clears a log file:
ALTER DATABASE CLEAR LOGFILE 'disk3:log.dbf'