Skip Headers

Oracle® Database SQL Reference
10g Release 1 (10.1)

Part Number B10759-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

ALTER DATABASE

Purpose

Use the ALTER DATABASE statement to modify, maintain, or recover an existing database.


Note:

In earlier versions of Oracle Database, you could use the ALTER DATABASE for two conversion operations:
  • The RESET COMPATIBILITY clause let you reset the database to an earlier version at the next instance startup.

  • The CONVERT clause let you upgrade an Oracle7 data dictionary to an Oracle8i or Oracle9i data dictionary.

These clauses are no longer supported. Please refer to Oracle Database Upgrade Guide for more information on migration and interoperability issues.



See Also:


Prerequisites

You must have the ALTER DATABASE system privilege.

To specify the RECOVER clause, you must also have the SYSDBA system privilege.

Syntax


alter_database::=
Description of alter_database.gif follows
Description of the illustration alter_database.gif


Groups of ALTER DATABASE syntax:

startup_clauses::=
Description of startup_clauses.gif follows
Description of the illustration startup_clauses.gif


recovery_clauses ::=
Description of recovery_clauses.gif follows
Description of the illustration recovery_clauses.gif

(general_recovery ::=, managed_standby_recovery ::=)


general_recovery ::=
Description of general_recovery.gif follows
Description of the illustration general_recovery.gif

(full_database_recovery ::=, partial_database_recovery::=, parallel_clause::=)


full_database_recovery ::=
Description of full_database_recovery.gif follows
Description of the illustration full_database_recovery.gif


partial_database_recovery::=
Description of partial_database_recovery.gif follows
Description of the illustration partial_database_recovery.gif


parallel_clause::=
Description of parallel_clause.gif follows
Description of the illustration parallel_clause.gif


managed_standby_recovery ::=
Description of managed_standby_recovery.gif follows
Description of the illustration managed_standby_recovery.gif

(recover_clause::=, cancel_clause::=, finish_clause::=)


recover_clause::=
Description of recover_clause.gif follows
Description of the illustration recover_clause.gif

(parallel_clause::=)


cancel_clause::=
Description of cancel_clause.gif follows
Description of the illustration cancel_clause.gif


finish_clause::=
Description of finish_clause.gif follows
Description of the illustration finish_clause.gif

(parallel_clause::=)


database_file_clauses ::=
Description of database_file_clauses.gif follows
Description of the illustration database_file_clauses.gif

(create_datafile_clause ::=, alter_datafile_clause ::=, alter_tempfile_clause ::=)


create_datafile_clause ::=
Description of create_datafile_clause.gif follows
Description of the illustration create_datafile_clause.gif

(file_specification::=)


alter_datafile_clause ::=
Description of alter_datafile_clause.gif follows
Description of the illustration alter_datafile_clause.gif

(autoextend_clause ::=, size_clause::=)


size_clause::=
Description of size_clause.gif follows
Description of the illustration size_clause.gif


alter_tempfile_clause ::=
Description of alter_tempfile_clause.gif follows
Description of the illustration alter_tempfile_clause.gif

(autoextend_clause ::=, size_clause::=)


autoextend_clause ::=
Description of autoextend_clause.gif follows
Description of the illustration autoextend_clause.gif


maxsize_clause::=
Description of maxsize_clause.gif follows
Description of the illustration maxsize_clause.gif

(size_clause::=)


logfile_clauses ::=
Description of logfile_clauses.gif follows
Description of the illustration logfile_clauses.gif

(logfile_descriptor::=, add_logfile_clauses ::=, drop_logfile_clauses ::=, supplemental_db_logging ::=)


add_logfile_clauses ::=
Description of add_logfile_clauses.gif follows
Description of the illustration add_logfile_clauses.gif

(redo_log_file_spec::=, logfile_descriptor::=)


drop_logfile_clauses ::=
Description of drop_logfile_clauses.gif follows
Description of the illustration drop_logfile_clauses.gif

(logfile_descriptor::=)


supplemental_db_logging ::=
Description of supplemental_db_logging.gif follows
Description of the illustration supplemental_db_logging.gif

(supplemental_id_key_clause::=)


supplemental_id_key_clause::=
Description of supplemental_id_key_clause.gif follows
Description of the illustration supplemental_id_key_clause.gif


logfile_descriptor::=
Description of logfile_descriptor.gif follows
Description of the illustration logfile_descriptor.gif


controlfile_clauses ::=
Description of controlfile_clauses.gif follows
Description of the illustration controlfile_clauses.gif

(trace_file_clause::=)


trace_file_clause::=
Description of trace_file_clause.gif follows
Description of the illustration trace_file_clause.gif


standby_database_clauses ::=
Description of standby_database_clauses.gif follows
Description of the illustration standby_database_clauses.gif

(activate_standby_db_clause ::=, maximize_standby_db_clause ::=, register_logfile_clause ::=, commit_switchover_clause ::=, start_standby_clause ::=, stop_standby_clause ::=, parallel_clause::=)


activate_standby_db_clause ::=
Description of activate_standby_db_clause.gif follows
Description of the illustration activate_standby_db_clause.gif


maximize_standby_db_clause ::=
Description of maximize_standby_db_clause.gif follows
Description of the illustration maximize_standby_db_clause.gif


register_logfile_clause ::=
Description of register_logfile_clause.gif follows
Description of the illustration register_logfile_clause.gif

(file_specification::=)


commit_switchover_clause ::=
Description of commit_switchover_clause.gif follows
Description of the illustration commit_switchover_clause.gif


start_standby_clause ::=
Description of start_standby_clause.gif follows
Description of the illustration start_standby_clause.gif


stop_standby_clause ::=
Description of stop_standby_clause.gif follows
Description of the illustration stop_standby_clause.gif


default_settings_clauses::=
Description of default_settings_clauses.gif follows
Description of the illustration default_settings_clauses.gif

(flashback_mode_clause ::=, set_time_zone_clause ::=)


set_time_zone_clause ::=
Description of set_time_zone_clause.gif follows
Description of the illustration set_time_zone_clause.gif


flashback_mode_clause ::=
Description of flashback_mode_clause.gif follows
Description of the illustration flashback_mode_clause.gif


redo_thread_clauses::=
Description of redo_thread_clauses.gif follows
Description of the illustration redo_thread_clauses.gif


security_clause ::=
Description of security_clause.gif follows
Description of the illustration security_clause.gif

Semantics


database

Specify the name of the database to be altered. The database name can contain only ASCII characters. If you omit database, Oracle Database alters the database identified by the value of the initialization parameter DB_NAME. You can alter only the database whose control files are specified by the initialization parameter CONTROL_FILES. The database identifier is not related to the Oracle Net database specification.


startup_clauses

The startup_clauses let you mount and open the database so that it is accessible to users.


MOUNT Clause

Use the MOUNT clause to mount the database. Do not use this clause when the database is already mounted.


MOUNT STANDBY DATABASE

You can specify MOUNT STANDBY DATABASE to mount a physical standby database. The keywords STANDBY DATABASE are optional, because Oracle Database determines automatically whether the database to be mounted is a primary or standby database. As soon as this statement executes, the standby instance can receive archived redo logs from the primary instance and can archive the logs to the STANDBY_ARCHIVE_DEST location.


See Also:

Oracle Data Guard Concepts and Administration for more information on standby databases


MOUNT CLONE DATABASE

Specify MOUNT CLONE DATABASE to mount the clone database.


OPEN Clause

Use the OPEN clause to make the database available for normal use. You must mount the database before you can open it.

If you specify only OPEN, without any other keywords, the default is OPEN READ WRITE NORESETLOGS on a primary database and OPEN READ ONLY on a standby database.


OPEN READ WRITE

Specify OPEN READ WRITE to open the database in read/write mode, allowing users to generate redo logs. This is the default if you are opening a primary database. You cannot specify this clause for a physical standby database.


RESETLOGS | NORESETLOGS

This clause determines whether Oracle Database resets the current log sequence number to 1, archives any unarchived logs (including the current log), and discards any redo information that was not applied during recovery, ensuring that it will never be applied. Oracle Database uses NORESETLOGS automatically except in the following specific situations, which require a setting for this clause:


UPGRADE | DOWNGRADE

Use these OPEN clause parameters only if you are upgrading or downgrading a database. This clause instructs Oracle Database to modify system parameters dynamically as required for upgrade and downgrade, respectively. You can achieve the same result using the SQL*Plus STARTUP UPGRADE or STARTUP DOWNGRADE command.


See Also:



OPEN READ ONLY

Specify OPEN READ ONLY to restrict users to read-only transactions, preventing them from generating redo logs. This setting is the default when you are opening a physical standby database, so that the physical standby database is available for queries even while archive logs are being copied from the primary database site.

Restrictions on Opening a Database
  • You cannot open a database in READ ONLY mode if it is currently opened in READ WRITE mode by another instance.

  • You cannot open a database in READ ONLY mode if it requires recovery.

  • You cannot take tablespaces offline while the database is open in READ ONLY mode. However, you can take datafiles offline and online, and you can recover offline datafiles and tablespaces while the database is open in READ ONLY mode.


recovery_clauses

The recovery_clauses include post-backup operations. For all of these clauses, Oracle Database recovers the database using any incarnations of datafiles and log files that are known to the current control file.


See Also:

Oracle Database Backup and Recovery Basics for information on backing up the database and "Database Recovery: Examples "


general_recovery

The general_recovery clause lets you control media recovery for the database or standby database or for specified tablespaces or files. You can use this clause when your instance has the database mounted, open or closed, and the files involved are not in use.

Restrictions on General Database Recovery
  • You can recover the entire database only when the database is closed.

  • Your instance must have the database mounted in exclusive mode.

  • You can recover tablespaces or datafiles when the database is open or closed, if the tablespaces or datafiles to be recovered are offline.

  • You cannot perform media recovery if you are connected to Oracle Database through the shared server architecture.


Note:

If you do not have special media requirements, Oracle recommends that you use the SQL*Plus RECOVER command rather than the general_recovery_clause.


See Also:



AUTOMATIC

Specify AUTOMATIC if you want Oracle Database to automatically generate the name of the next archived redo log file needed to continue the recovery operation. If the LOG_ARCHIVE_DEST_n parameters are defined, Oracle Database scans those that are valid and enabled for the first local destination. It uses that destination in conjunction with LOG_ARCHIVE_FORMAT to generate the target redo log filename. If the LOG_ARCHIVE_DEST_n parameters are not defined, Oracle Database uses the value of the LOG_ARCHIVE_DEST parameter instead.

If the resulting file is found, Oracle Database applies the redo contained in that file. If the file is not found, Oracle Database prompts you for a filename, displaying the generated filename as a suggestion.

If you specify neither AUTOMATIC nor LOGFILE, Oracle Database prompts you for a filename, displaying the generated filename as a suggestion. You can then accept the generated filename or replace it with a fully qualified filename. If you know that the archived filename differs from what Oracle Database would generate, you can save time by using the LOGFILE clause.


FROM 'location'

Specify FROM 'location' to indicate the location from which the archived redo log file group is read. The value of location must be a fully specified file location following the conventions of your operating system. If you omit this parameter, Oracle Database assumes that the archived redo log file group is in the location specified by the initialization parameter LOG_ARCHIVE_DEST or LOG_ARCHIVE_DEST_1.


full_database_recovery

The full_database_recovery clause lets you recover an entire database.


DATABASE

Specify the DATABASE clause to recover the entire database. This is the default. You can use this clause only when the database is closed.


STANDBY DATABASE

Specify the STANDBY DATABASE clause to manually recover a physical standby database using the control file and archived redo log files copied from the primary database. The standby database must be mounted but not open.

This clause recovers only online datafiles.


partial_database_recovery

The partial_database_recovery clause lets you recover individual tablespaces and datafiles.


TABLESPACE

Specify the TABLESPACE clause to recover only the specified tablespaces. You can use this clause if the database is open or closed, provided the tablespaces to be recovered are offline.


DATAFILE

Specify the DATAFILE clause to recover the specified datafiles. You can use this clause when the database is open or closed, provided the datafiles to be recovered are offline.

You can identify the datafile by name or by number. If you identify it by number, then filenumber is an integer representing the number found in the FILE# column of the V$DATAFILE dynamic performance view or in the FILE_ID column of the DBA_DATA_FILES data dictionary view.


STANDBY TABLESPACE

Specify STANDBY TABLESPACE to reconstruct a lost or damaged tablespace in the standby database using archived redo log files copied from the primary database and a control file.


STANDBY DATAFILE

Specify STANDBY DATAFILE to manually reconstruct a lost or damaged datafile in the physical standby database using archived redo log files copied from the primary database and a control file. You can identify the file by name or by number, as described for the DATAFILE clause.

Specify UNTIL [CONSISTENT WITH] CONTROLFILE if you want the recovery of an old standby datafile or tablespace to use the current standby database control file. However, any redo in advance of the standby controlfile will not be applied. The keywords CONSISTENT WITH are optional and are provided for semantic clarity.


LOGFILE

Specify the LOGFILE 'filename' to continue media recovery by applying the specified redo log file.


TEST

Use the TEST clause to conduct a trial recovery. A trial recovery is useful if a normal recovery procedure has encountered some problem. It lets you look ahead into the redo stream to detect possible additional problems. The trial recovery applies redo in a way similar to normal recovery, but it does not write changes to disk, and it rolls back its changes at the end of the trial recovery.

You can use this clause only if you have restored a backup taken since the last RESETLOGS operation. Otherwise, Oracle Database returns an error.


ALLOW ... CORRUPTION

The ALLOW integer CORRUPTION clause lets you specify, in the event of logfile corruption, the number of corrupt blocks that can be tolerated while allowing recovery to proceed.

When you use this clause during trial recovery (that is, in conjunction with the TEST clause), integer can exceed 1. When using this clause during normal recovery, integer cannot exceed 1.


See Also:



parallel_clause

Use the PARALLEL clause to specify whether the recovery of media will be parallelized.

For complete information on this clause, please refer to parallel_clause in the documentation on CREATE TABLE.


CONTINUE

Specify CONTINUE to continue multi-instance recovery after it has been interrupted to disable a thread.

Specify CONTINUE DEFAULT to continue recovery using the redo log file that Oracle Database would automatically generate if no other logfile were specified. This clause is equivalent to specifying AUTOMATIC, except that Oracle Database does not prompt for a filename.


CANCEL

Specify CANCEL to terminate cancel-based recovery.


managed_standby_recovery

The managed_standby_recovery clause applies to physical standby only. Use it to specify managed standby recovery mode. This mode assumes that the managed standby database is an active component of an overall standby database architecture. A primary database actively archives its redo log files to the standby site. As these archived redo logs arrive at the standby site, they become available for use by a managed standby recovery operation. Managed standby recovery is restricted to media recovery. You can use this clause when your instance has the database mounted, open or closed, and the files involved are not in use.


Restrictions on Managed Standby Recovery

The same restrictions apply as are listed under general_recovery .


See Also:

Oracle Data Guard Concepts and Administration for more information on the parameters of this clause "Recovering a Managed Standby Database: Examples"


DISCONNECT

Specify DISCONNECT to indicate that the managed redo process (MRP), an Oracle Database background process, should apply archived redo files as a detached background process. Doing so leaves the current session available for other tasks. The FROM SESSION keywords are optional and are provided for semantic clarity.

Restrictions on DISCONNECT
  • You can specify DISCONNECT only when you are initiating managed standby recovery. You cannot specify it after the operation has started.

  • You cannot specify both TIMEOUT and DISCONNECT [FROM SESSION]. TIMEOUT applies only to foreground recovery operations, whereas the DISCONNECT clause initiates background recovery operations.


TIMEOUT integer

Specify in minutes the wait period of the managed recovery operation. The recovery process waits for integer minutes for a requested archived log redo to be available for writing to the managed standby database. If the redo log file does not become available within that time, the recovery process terminates with an error message. You can then issue the statement again to return to managed standby recovery mode.

If you omit TIMEOUT or if you specify NOTIMEOUT, the database remains in managed standby recovery mode until you reissue the statement with the RECOVER CANCEL clause or until instance shutdown or failure.

Restrictions on TIMEOUT
  • If you specify TIMEOUT, you cannot also specify FINISH.

  • You cannot specify both TIMEOUT and DISCONNECT [FROM SESSION]. TIMEOUT applies only to foreground recovery operations, whereas the DISCONNECT clause initiates background recovery operations.


NODELAY | DEFAULT DELAY | DELAY integer

Specify DELAY to instruct Oracle Database to wait the specified interval (in minutes) before applying the archived redo logs. The delay interval begins after the archived redo logs have been selected for recovery.

Both of these parameters override any setting of DELAY in the LOG_ARCHIVE_DEST_n parameter on the primary database. If you specify neither of these parameters, application of the archivelog is delayed according to the LOG_ARCHIVE_DEST_n setting. If DELAY was not specified in that parameter, the archivelog is applied immediately.

Restrictions on DELAY
  • You cannot specify both NODELAY and DELAY.

  • If you specify DELAY, you cannot also specify FINISH.


See Also:

Oracle Database Reference for detailed information on the LOG_ARCHIVE_DEST_n parameter


NEXT integer

Use the NEXT parameter to apply the specified number of archived redo logs as soon as possible after they have been archived. This parameter temporarily overrides any delay setting in the LOG_ARCHIVE_DEST_n initialization parameter on the primary database and any DELAY values specified in an earlier ALTER DATABASE ... managed_standby_recovery statement. Once the integer archived redo logs are processed, any such delay again takes effect.


Restriction on NEXT

If you specify NEXT, you cannot also specify FINISH.


See Also:

Oracle Database Reference for detailed information on the LOG_ARCHIVE_DEST_n parameter


EXPIRE integer

Specify the number of minutes from the current time after which the managed recovery operation terminates automatically. The process may actually expire after the interval specified, because Oracle Database will finish processing any archived redo log that is being processed at the expiration time.

Specify NOEXPIRE to disable a previously specified EXPIRE option.

Expiration is always relative to the time the current statement is issued rather than to the start time of the managed recovery process. To terminate an existing managed recovery operation, use the CANCEL parameter.


Restriction on EXPIRE

If you specify EXPIRE, you cannot also specify FINISH.


parallel_clause

Use the parallel_clause to indicate whether Oracle Database should parallelize the managed recovery processes. If you specify NOPARALLEL or omit this clause entirely, Oracle Database performs the managed standby recovery operation serially. Please refer to the parallel_clause for more information on this clause.


See Also:

Oracle Data Guard Concepts and Administration for guidelines on determining whether parallel managed standby recovery will result in performance gains


UNTIL CHANGE Clause

Use this clause to instruct Oracle Database to conduct managed recovery up to but not including the specified system change number.


USING CURRENT LOGFILE Clause

Specify USING CURRENT LOGFILE to invoke real time apply, which lets you recover redo from standby online logs as they are being filled up, without requiring them first to be archived at the standby database.


See Also:

Oracle Data Guard Concepts and Administration for more information on real time apply


THROUGH Clause

Use this clause to instruct Oracle Database when to terminate managed recovery.


CANCEL

Specify CANCEL to terminate the managed standby recovery operation after applying all the redo in the current archived redo file. If you specify only the CANCEL keyword, session control returns when the recovery process actually terminates.


Restriction on CANCEL IMMEDIATE

The CANCEL IMMEDIATE clause cannot be issued from the same session that issued the RECOVER MANAGED STANDBY DATABASE statement.


FINISH

The FINISH clause applies only to physical standby databases. Specify FINISH to recover the current standby online redo logfiles of the standby database. Use this clause only in the event of the failure of the primary database, when the logwriter (LGWR) process has been transmitting redo to the standby current logs. This clause overrides any delay intervals specified for the archivelogs. Oracle Database applies the logs immediately.

After the FINISH operation, you must open the standby database as the primary database.

Specify NOWAIT to have control returned immediately rather than after the recovery process is complete.


Restriction on FINISH

You cannot specify FINISH if you have also specified TIMEOUT, DELAY, EXPIRE, or NEXT.


BACKUP Clauses

Use these clauses to move all the datafiles in the database into or out of online backup mode (also called hot backup mode).


See Also:

ALTER TABLESPACE for information on moving all datafiles in an individual tablespace into and out of online backup mode


BEGIN BACKUP Clause

Specify BEGIN BACKUP to move all datafiles in the database into online backup mode. The database must be mounted and open, and media recovery must be enabled (the database must be in archivelog mode).

While the database is in online backup mode, you cannot shut down the instance normally, begin backup of an individual tablespace, or take any tablespace offline or make it read only.

This clause has no effect on datafiles that are in offline or on read-only tablespaces.


END BACKUP Clause

Specify END BACKUP to take out of online backup mode any datafiles in the database currently in online backup mode. The database must be mounted (either open or closed) when you perform this operation.

After a system failure, instance failure, or SHUTDOWN ABORT operation, Oracle Database does not know whether the files in online backup mode match the files at the time the system crashed. If you know the files are consistent, you can take either individual datafiles or all datafiles out of online backup mode. Doing so avoids media recovery of the files upon startup.


database_file_clauses

The database_file_clauses let you modify datafiles and tempfiles. You can use any of the following clauses when your instance has the database mounted, open or closed, and the files involved are not in use.


RENAME FILE Clause

Use the RENAME FILE clause to rename datafiles or redo log file members. You must create each filename using the conventions for filenames on your operating system before specifying this clause. This clause is not valid for renaming tempfiles.

This clause renames only files in the control file. It does not actually rename them on your operating system. The operating system files continue to exist, but Oracle Database no longer uses them.


create_datafile_clause

Use the CREATE DATAFILE clause to create a new empty datafile in place of an old one. You can use this clause to re-create a datafile that was lost with no backup. The filename or filenumber must identify a file that is or was once part of the database. If you identify the file by number, then filenumber is an integer representing the number found in the FILE# column of the V$DATAFILE dynamic performance view or in the FILE_ID column of the DBA_DATA_FILES data dictionary view.

If the original file (filename or filenumber) is an existing Oracle-managed datafile, then Oracle Database attempts to delete the original file after creating the new file. If the original file is an existing user-managed datafile, Oracle Database does not attempt to delete the original file.

If you omit the AS clause entirely, Oracle Database creates the new file with the same name and size as the file specified by filename or filenumber.

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.

Oracle Database 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.

Restrictions on Creating New Datafiles
  • You cannot create a new file based on the first datafile of the SYSTEM tablespace.

  • You cannot specify the autoextend_clause of datafile_tempfile_spec in this CREATE DATAFILE clause.


See Also:



alter_datafile_clause

The DATAFILE clause lets you manipulate a file that you identify by name or by number. If you identify it by number, then filenumber is an integer representing the number found in the FILE# column of the V$DATAFILE dynamic performance view or in the FILE_ID column of the DBA_DATA_FILES data dictionary view. The DATAFILE clauses affect your database files as follows:


ONLINE

Specify ONLINE to bring the datafile online.


OFFLINE

Specify OFFLINE to take the datafile offline. If the database is open, you must perform media recovery on the datafile before bringing it back online, because a checkpoint is not performed on the datafile before it is taken offline.


FOR DROP

If the database is in noarchivelog mode, you must specify FOR DROP clause to take a datafile offline. However, this clause does not remove the datafile from the database. To do that, you must use an operating system command or drop the tablespace in which the datafile resides. Until you do so, the datafile remains in the data dictionary with the status RECOVER or OFFLINE.

If the database is in archivelog mode, Oracle Database ignores the FOR DROP clause.

In earlier releases, the OFFLINE FOR DROP clause was documented as OFFLINE DROP, with the same semantics. That syntax is still supported for back ward compatibility. However, Oracle recommends that you use the more semantically correct OFFLINE FOR DROP syntax.


RESIZE

Specify RESIZE if you want Oracle Database to attempt to increase or decrease the size of the datafile to the specified absolute size in bytes. There is no default, so you must specify a size.

If sufficient disk space is not available for the increased size, or if the file contains data beyond the specified decreased size, Oracle Database returns an error.


END BACKUP

Specify END BACKUP to take the datafile out of online backup mode. The END BACKUP clause is described more fully at the top level of the syntax of ALTER DATABASE. See "END BACKUP Clause ".


alter_tempfile_clause

Use the TEMPFILE clause to resize your temporary datafile or specify the autoextend_clause, with the same effect as for a permanent datafile. The database must be open. You can identify the tempfile by name or by number. If you identify it by number, then filenumber is an integer representing the number found in the FILE# column of the V$TEMPFILE dynamic performance view.


Note:

On some operating systems, Oracle does not allocate space for a tempfile until the tempfile blocks are actually accessed. This delay in space allocation results in faster creation and resizing of tempfiles, but it requires that sufficient disk space is available when the tempfiles are later used. To avoid potential problems, before you create or resize a tempfile, ensure that the available disk space exceeds the size of the new tempfile or the increased size of a resized tempfile. The excess space should allow for anticipated increases in disk space use by unrelated operations as well. Then proceed with the creation or resizing operation.


DROP

Specify DROP to drop tempfile from the database. The tablespace remains.

If you specify INCLUDING DATAFILES, Oracle Database also deletes the associated operating system files and writes a message to the alert log for each such deleted file.


autoextend_clause

Use the autoextend_clause to enable or disable the automatic extension of a new or existing datafile or tempfile. Please refer to file_specification for information about this clause.


logfile_clauses

The logfile clauses let you add, drop, or modify log files.


ARCHIVELOG

Specify ARCHIVELOG if you want the contents of a redo log file group to be archived before the group can be reused. This mode prepares for the possibility of media recovery. Use this clause only after shutting down your instance normally, or immediately with no errors, and then restarting it and mounting the database, Real Application Clusters must be disabled.


MANUAL

Specify MANUAL to indicate that Oracle Database should create redo log files, but the archiving of the redo log files is controlled entirely by the user. This clause is provided for backward compatibility, for example for users who archive directly to tape. If you specify MANUAL, then:

If you omit this clause, Oracle Database automatically archives the redo log files to the destination specified in the LOG_ARCHIVE_DEST_n initialization parameters.


NOARCHIVELOG

Specify NOARCHIVELOG if you do not want the contents of a redo log file group to be archived so that the group can be reused. This mode does not prepare for recovery after media failure. Use this clause only if your instance has the database mounted but not open, and Real Application Clusters must be disabled.


[NO] FORCE LOGGING

Use this clause to put the database into or take the database out of FORCE LOGGING mode. The database must be mounted or open.

In FORCE LOGGING mode, Oracle Database logs all changes in the database except changes in temporary tablespaces and temporary segments. This setting takes precedence over and is independent of any NOLOGGING or FORCE LOGGING settings you specify for individual tablespaces and any NOLOGGING settings you specify for individual database objects.

If you specify FORCE LOGGING, Oracle Database waits for all ongoing unlogged operations to finish.


See Also:

Oracle Database Administrator's Guide for information on when to use FORCE LOGGING mode


RENAME FILE Clause

This clause has the same function for logfiles that it has for datafiles and tempfiles. See "RENAME FILE Clause ".


CLEAR LOGFILE Clause

Use the CLEAR LOGFILE clause to reinitialize an online redo log, optionally without archiving the redo log. CLEAR LOGFILE is similar to adding and dropping a redo log, except that the statement may be issued even if there are only two logs for the thread and may be issued for the current redo log of a closed thread.

For a standby database, if the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO, and if any of the log files are Oracle-managed files, Oracle Database will create as many Oracle-managed log files as are in the control file. The log file members will reside in the current default log file destination.


add_logfile_clauses

Use these clauses to add redo log file groups to the database and to add new members to existing redo log file groups.


ADD LOGFILE Clause

Use the ADD LOGFILE clause to add one or more redo log file groups to the specified thread or instance, making them available to the instance to which the thread is assigned.

To learn whether a logfile has been designated for online or standby database use, query the TYPE column of the V$LOGFILE dynamic performance view.


See Also:



INSTANCE

The INSTANCE clause is applicable only if you are using Oracle Database with the Real Application Clusters option in parallel mode. Specify the name of the instance for which you want to add a logfile. The instance name is a string of up to 80 characters. Oracle Database automatically uses the thread that is mapped to the specified instance. If no thread is mapped to the specified instance, then Oracle Database automatically acquires an available unmapped thread and assigns it to that instance. If you specify neither this clause nor the THREAD clause, then Oracle Database executes the command as if you had specified the current instance. If the specified instance has no current thread mapping and there are no available unmapped threads, then Oracle Database returns an error.


THREAD

The THREAD clause has been deprecated. The INSTANCE clause achieves the same purpose and is easier to use. The THREAD clause is supported for backward compatibility. integer is the thread number. The number of threads you can create is limited by the value of the MAXINSTANCES parameter specified in the CREATE DATABASE statement.


GROUP

The GROUP clause uniquely identifies the redo log file group among all groups in all threads and can range from 1 to the value specified for MAXLOGFILES in the CREATE DATABASE statement. You cannot add multiple redo log file groups having the same GROUP value. If you omit this parameter, Oracle Database generates its value automatically. You can examine the GROUP value for a redo log file group through the dynamic performance view V$LOG.


redo_log_file_spec

Each redo_log_file_spec specifies a redo log file group containing one or more members (that is, one or more copies). If you do not specify a filename for the new log file, then Oracle Database creates Oracle-managed files according to the rules described in the "LOGFILE Clause " of CREATE DATABASE.


See Also:



ADD [STANDBY] LOGFILE MEMBER Clause

Use the ADD LOGFILE MEMBER clause to add 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 REUSE. If the file does not exist, Oracle Database creates a file of the correct size. You cannot add a member to a group if all of the members of the group have been lost through media failure.

You can specify STANDBY for symmetry, to indicate that the logfile member is for use only by a physical standby database. However, this keyword is not required. If group integer was added for standby database use, all of its members will be used only for standby databases as well.

You can specify an existing redo log file group in one of two ways:


GROUP integer

Specify the value of the GROUP parameter that identifies the redo log file group.


filename(s)

List all members of the redo log file group. You must fully specify each filename according to the conventions of your operating system.


See Also:



drop_logfile_clauses

Use these clauses to drop redo log file groups or redo log file members.


DROP LOGFILE Clause

Use the DROP LOGFILE clause to drop all members of a redo log file group. If you use this clause to drop Oracle-managed files, then Oracle Database also removes all log file members from disk. Specify a redo log file group as indicated for the ADD LOGFILE MEMBER clause.


DROP LOGFILE MEMBER Clause

Use the DROP LOGFILE MEMBER clause to drop one or more redo log file members. Each 'filename' must fully specify a member using the conventions for filenames on your operating system.


supplemental_db_logging

Use these clauses to instruct Oracle Database to add or stop adding supplemental data into the log stream.


ADD SUPPLEMENTAL LOG Clause

Specify ADD SUPPLEMENTAL LOG DATA to enable minimal supplemental logging. Specify ADD SUPPLEMENTAL LOG supplemental_id_key_clause to enable column data logging in addition to minimal supplemental logging. Oracle Database does not enable either minimal supplemental logging or supplemental logging by default.

Minimal supplemental logging ensures that LogMiner (and any products building on LogMiner technology) will have sufficient information to support chained rows and various storage arrangements such as cluster tables.

If the redo generated on one database is to be the source of changes (to be mined and applied) at another database, as is the case with logical standby, then the affected rows need to be identified using column data (as opposed to rowids). In this case, you should specify the supplemental_id_key_clause.

You can query the appropriate columns in the V$DATABASE view to determine whether any supplemental logging has already been enabled.

You can issue this statement when the database is open. However, Oracle Database will invalidate all DML cursors in the cursor cache, which will have an effect on performance until the cache is repopulated.

For a full discussion of the supplemental_id_clause, please refer to supplemental_id_key_clause in the documentation on CREATE TABLE.


See Also:

Oracle Data Guard Concepts and Administration for information on supplemental logging


DROP SUPPLEMENTAL LOG Clause

Use this clause to stop supplemental logging.


controlfile_clauses

The controlfile_clauses let you create or back up a control file.


CREATE STANDBY CONTROLFILE Clause

The CREATE STANDBY CONTROLFILE clause lets you create a control file to be used to maintain a physical or logical standby database. If the file already exists, you must specify REUSE.


BACKUP CONTROLFILE Clause

Use the BACKUP CONTROLFILE clause to back up the current control file. The database must be open or mounted when you specify this clause.


TO 'filename'

Specify 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 REUSE.


TO TRACE

Specify TO TRACE if you want Oracle Database to write SQL statements to a trace file rather than making a physical backup of the control file. You can use SQL statements written to the trace file to start up the database, re-create the control file, and recover and open the database appropriately, based on the created control file. If you issue an ALTER DATABASE BACKUP CONTROLFILE TO TRACE statement while block change tracking is enabled, the resulting script will contain a command to reenable block change tracking.

This statement issues an implicit ALTER DATABASE REGISTER LOGFILE statement, which creates incarnation records if the archived log files reside in the current archivelog destinations.

You can copy the statements from the trace file into a script file, edit the statements as necessary, and use the script if all copies of the control file are lost (or to change the size of the control file).

If you cannot predict the future state of the online logs, specify neither RESETLOGS nor NORESETLOGS. In this case, Oracle Database puts both versions of the script into the trace file, and you can choose which version is appropriate when the script becomes necessary.


standby_database_clauses

Use these clauses to activate the standby database or to specify whether it is in protected or unprotected mode.


See Also:

Oracle Data Guard Concepts and Administration for descriptions of physical and logical the standby database and for information on maintaining and using standby databases


activate_standby_db_clause

The ACTIVATE STANDBY DATABASE clause changes the state of a standby database to an active database and prepares it to become the primary database. The database must be mounted before you can specify this clause.


PHYSICAL

Specify PHYSICAL to activate a physical standby database. This is the default.


LOGICAL

Specify LOGICAL to activate a logical standby database. If you have more than one logical standby database, you should first ensure that the same log data is available on all the standby systems.


SKIP [STANDBY LOGFILE]

This clause applies only to physical standby databases. Use this clause to force the operation to proceed even if standby redo logfiles contain data that could be recovered using the RECOVER MANAGED STANDBY DATABASE FINISH command.


Note:

Oracle recommends that you always use the RECOVER MANAGED STANDBY DATABASE FINISH statement for physical standby even if you do not use standby redo logfiles. Use the SKIP clause only if it is acceptable to discard the contents of the standby redo log.


maximize_standby_db_clause

Use this clause to specify the level of protection for the data in your database environment. You specify this clause from the primary database, which must be mounted but not open.


Note:

The PROTECTED and UNPROTECTED keywords have been replaced for clarity but are still supported. PROTECTED is equivalent to TO MAXIMIZE PROTECTION. UNPROTECTED is equivalent to TO MAXIMIZE PERFORMANCE.


TO MAXIMIZE PROTECTION

This setting establishes maximum protection mode and offers the highest level of data protection. A transaction does not commit until all data needed to recover that transaction has been written to at least one physical standby database that is configured to use the SYNC log transport mode. If the primary database is unable to write the redo records to at least one such standby database, the primary database is shut down. This mode guarantees zero data loss, but it has the greatest potential impact on the performance and availability of the primary database.


TO MAXIMIZE AVAILABILITY

This setting establishes maximum availability mode and offers the next highest level of data protection. A transaction does not commit until all data needed to recover that transaction has been written to at least one physical or logical standby database that is configured to use the SYNC log transport mode. Unlike maximum protection mode, the primary database does not shut down if it is unable to write the redo records to at least one such standby database. Instead, the protection is lowered to maximum performance mode until the fault has been corrected and the standby database has caught up with the primary database. This mode guarantees zero data loss unless the primary database fails while in maximum performance mode. Maximum availability mode provides the highest level of data protection that is possible without affecting the availability of the primary database.


TO MAXIMIZE PERFORMANCE

This setting establishes maximum performance mode and is the default setting. A transaction commits before the data needed to recover that transaction has been written to a standby database. Therefore, some transactions may be lost if the primary database fails and you are unable to recover the redo records from the primary database. This mode provides the highest level of data protection that is possible without affecting the performance of the primary database.

To determine the current mode of the database, query the PROTECTION_MODE column of the V$DATABASE dynamic performance view.


See Also:

Oracle Data Guard Concepts and Administration for full information on using these standby database settings


register_logfile_clause

Specify the REGISTER LOGFILE clause from the standby database to manually register log files from the failed primary. Use the redo_log_file_spec form of file_specification (see file_specification ) to list regular redo log files in an operating system file system or to list Automatic Storage Management disk group redo log files.

When a log file is from an unknown incarnation, the REGISTER LOGFILE clause causes an incarnation record to be added to the V$DATABASE_INCARNATION view. If the newly registered log file belongs to an incarnation having a higher RESETLOGS_TIME than the current RECOVERY_TARGET_INCARNATION#, the REGISTER LOGFILE clause also causes RECOVERY_TARGET_INCARNATION# to be changed to correspond to the newly added incarnation record.


OR REPLACE

Specify OR REPLACE to allow an existing archivelog entry in the standby database to be updated, for example, when its location or file specification changes. The system change numbers of the entries must match exactly, and the original entry must have been created by the managed standby log transmittal mechanism.


FOR logminer_session_name

This clause is useful in a Streams environment. It lets you register the log file with one specified LogMiner session.


commit_switchover_clause

Use this clause to perform a graceful switchover, in which the current primary database takes on standby status, and one standby database becomes the primary database. In a Real Application Clusters environment, all instances other than the instance from which you issue this statement must be shut down normally.


PREPARE TO SWITCHOVER

The PREPARE TO SWITCHOVER clause prepares the primary and standby databases to begin exchanging log files in preparation for the switchover.


COMMIT TO SWITCHOVER

The COMMIT TO SWITCHOVER completes the switchover operation.


CANCEL

Specify CANCEL to cancel the switchover from primary to standby database. This clause is necessary to stop the shipping of log files from the standby database to the primary database.


See Also:

Oracle Data Guard Concepts and Administration for full information on graceful switchover between primary and standby databases


start_standby_clause

Specify the START LOGICAL STANDBY APPLY clause to begin applying redo logs to a logical standby database.


stop_standby_clause

Use this clause to stop the log apply services. This clause applies only to logical standby databases, not to physical standby databases. Use the STOP clause to stop the apply in an orderly fashion.


default_settings_clauses

Use these clauses to modify the default settings of the database.


CHARACTER SET, NATIONAL CHARACTER SET

You can no longer change the database character set or the national character set using the ALTER DATABASE statement. Please refer to Oracle Database Globalization Support Guide for information on database character set migration.


SET DEFAULT TABLESPACE Clause

Use this clause to specify or change the default type of subsequently created tablespaces. Specify BIGFILE or SMALLFILE to indicate whether the tablespaces should be bigfile or smallfile tablespaces.


See Also:



DEFAULT TABLESPACE Clause

Specify this clause to establish or change the default permanent tablespace of the database. The tablespace you specify must already have been created. After this operation completes, Oracle Database automatically reassigns to the new default tablespace all non-SYSTEM users who have not explicitly been assigned a default tablespace. All objects subsequently created by those users will be stored in the new default tablespace. If you are replacing a previously specified default tablespace, you can move the previously created objects from the old to the new default tablespace, and then drop the old default tablespace if you wish.


DEFAULT TEMPORARY TABLESPACE Clause

Specify this clause to change the default temporary tablespace of the database to a new tablespace or tablespace group.

To learn the name of the current default temporary tablespace or default temporary tablespace group, query the TEMPORARY_TABLESPACE column of the ALL_, DBA-, or USER_USERS data dictionary views.

Restrictions on Default Temporary Tablespaces
  • The tablespace you assign or reassign as the default temporary tablespace must have a standard block size.

  • If the SYSTEM tablespace is locally managed, the tablespace you specify as the default temporary tablespace must also be locally managed.


redo_thread_clauses

Use these clauses to enable and disable a thread of redo or log file group.


ENABLE | DISABLE INSTANCE Clause

In an Oracle Real Application Clusters environment, specify ENABLE INSTANCE to enable the thread that is mapped to the specified database instance. The thread must have at least two redo log file groups, and the database must be open.

Specify DISABLE INSTANCE to disable the thread that is mapped to the specified database instance. The name of the instance is a string of up to 80 characters. If no thread is currently mapped to the specified instance, then Oracle Database returns an error. The database must be open, but you cannot disable a thread if an instance using it has the database mounted.


ENABLE | DISABLE THREAD Clause

This clause has been deprecated, because the ENABLE | DISABLE INSTANCE clause achieves the same purpose and is easier to use. The ENABLE | DISABLE THREAD clause is supported for backward compatibility. Specify ENABLE THREAD to enable the specified thread of redo log file groups. The thread must have at least two redo log file groups before you can enable it. The database must be open.


PUBLIC

Specify PUBLIC to make the enabled thread available to any instance that does not explicitly request a specific thread with the initialization parameter THREAD. The PUBLIC keyword is not valid with DISABLE THREAD. If you omit PUBLIC, then the thread is available only to the instance that explicitly requests it with the initialization parameter THREAD.

Specify DISABLE THREAD to disable the specified thread, making it unavailable to all instances. The database must be open, but you cannot disable a thread if an instance using it has the database mounted.


RENAME GLOBAL_NAME Clause

Specify RENAME GLOBAL_NAME to change the global name of the database. The database is the new database name and can be as long as eight bytes. The optional domain specifies where the database is effectively located in the network hierarchy. The database must be open.


Note:

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.


BLOCK CHANGE TRACKING Clauses

The block change tracking feature causes Oracle Database to keep track of the physical locations of all database updates. This information is maintained in a separate file called the block change tracking file. If you are using Oracle-managed files, Oracle Database automatically creates the block change tracking file in the location specified by DB_CREATE_FILE_DEST. If you are not using Oracle-managed files, you must specify the change tracking filename. Oracle Database uses change tracking data for some internal tasks, such as increasing the performance of incremental backups. You can enable or disable block change tracking with the database either open or mounted, in either archivelog or noarchivelog mode.


ENABLE BLOCK CHANGE TRACKING

This clause enables block change tracking and causes Oracle Database to create a block change tracking file.


DISABLE BLOCK CHANGE TRACKING

Specify this clause if you want Oracle Database to stop tracking changes and delete the existing block change tracking file.


See Also:

Oracle Database Backup and Recovery Basics for information on setting up block change tracking and "Enabling and Disabling Block Change Tracking: Examples"


flashback_mode_clause

Use this clause to put the database in or take the database out of FLASHBACK mode. You can specify this clause only if the database is in archivelog mode and you have already prepared a flash recovery area for the database.You can specify this clause when the database is mounted in exclusive mode but not open.


See Also:

Oracle Database Backup and Recovery Basics for information on preparing the flash recovery area for Flashback operations


FLASHBACK ON

Use this clause to put the database in FLASHBACK mode. When the database is in FLASHBACK mode, Oracle Database automatically creates and manages Flashback Database logs in the flash recovery area. Users with SYSDBA system privilege can then issue a FLASHBACK DATABASE statement.


FLASHBACK OFF

Use this clause to take the database out of FLASHBACK mode. Oracle Database stops logging Flashback data and deletes all existing Flashback Database logs. Any attempt to issue a FLASHBACK DATABASE will fail with an error.


set_time_zone_clause

This clause has the same semantics in CREATE DATABASE and ALTER DATABASE statements. When used in with ALTER DATABASE, this clause resets the time zone of the database. To determine the time zone of the database, query the built-in function DBTIMEZONE . After setting or changing the time zone with this clause, you must restart the database for the new time zone to take effect.

Oracle Database normalizes all new TIMESTAMP WITH LOCAL TIME ZONE data to the time zone of the database when the data is stored on disk.Oracle Database does not automatically update existing data in the database to the new time zone. Therefore, you cannot reset the database time zone if there is any TIMESTAMP WITH LOCAL TIME ZONE data in the database. You must first delete or export the TIMESTAMP WITH LOCAL TIME ZONE data and then reset the database time zone. For this reason, Oracle does not encourage you to change the time zone of a database that contains data.

For a full description of this clause, please refer to set_time_zone_clause in the documentation on CREATE DATABASE.


security_clause

Use the security_clause (GUARD) to protect data in the database from being changed. You can override this setting for a current session using the ALTER SESSION DISABLE GUARD statement. Please refer to ALTER SESSION for more information.


ALL

Specify ALL to prevent all users other than SYS from making changes to any data in the database.


STANDBY

Specify STANDBY to prevent all users other than SYS from making changes to any database object being maintained by logical standby. This setting is useful if you want report operations to be able to modify data as long as it is not being replicated by logical standby.


See Also:

Oracle Data Guard Concepts and Administration for information on logical standby


NONE

Specify NONE if you want normal security for all data in the database.

Examples


READ ONLY / READ WRITE: Example

The following statement opens the database in read-only mode:

ALTER DATABASE OPEN READ ONLY;

The following statement opens the database in read/write mode and clears the online redo logs:

ALTER DATABASE OPEN READ WRITE RESETLOGS;

Using Parallel Recovery Processes: Example

The following statement performs tablespace recovery using parallel recovery processes:

ALTER DATABASE
   RECOVER TABLESPACE tbs_03
   PARALLEL;

Adding Redo Log File Groups: Examples

The following statement adds a redo log file group with two members and identifies it with a GROUP parameter value of 3:

ALTER DATABASE
  ADD LOGFILE GROUP 3 
    ('diska:log3.log' ,  
     'diskb:log3.log') SIZE 50K; 

The following statement adds a redo log file group containing two members to thread 5 (in a Real Application Clusters environment) and assigns it a GROUP parameter value of 4:

ALTER DATABASE  
    ADD LOGFILE THREAD 5 GROUP 4  
        ('diska:log4.log', 
         'diskb:log4:log'); 

Adding Redo Log File Group Members: Example

The following statement adds a member to the redo log file group added in the previous example:

ALTER DATABASE   
   ADD LOGFILE MEMBER 'diskc:log3.log'  
   TO GROUP 3; 

Dropping Log File Members: Example

The following statement drops one redo log file member added in the previous example:

ALTER DATABASE
    DROP LOGFILE MEMBER 'diskb:log3.log'; 

The following statement drops all members of the redo log file group 3:

ALTER DATABASE DROP LOGFILE GROUP 3; 

Renaming a Log File Member: Example

The following statement renames a redo log file member:

ALTER DATABASE   
    RENAME FILE 'diskc:log3.log' TO 'diskb:log3.log'; 

The preceding 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 diskc:log3.log to diskb:log3.log. You must perform this operation through your operating system.


Setting the Default Type of Tablespaces: Example

The following statement specifies that subsequently created tablespaces be created as bigfile tablespaces by default:

ALTER DATABASE
    SET DEFAULT BIGFILE TABLESPACE;

Changing the Default Temporary Tablespace: Examples

The following statement makes the tbs_5 tablespace (created in "Creating a Temporary Tablespace: Example") the default temporary tablespace of the database. This statement either establishes a default temporary tablespace if none was specified at create time, or replaces an existing default temporary tablespace with tbs_05:

ALTER DATABASE 
   DEFAULT TEMPORARY TABLESPACE tbs_05;

Alternatively, a group of tablespaces can be defined as the default temporary tablespace by using a tablespace group. The following statement makes the tablespaces in the tablespace group tbs_group_01 (created in "Adding a Temporary Tablespace to a Tablespace Group: Example") the default temporary tablespaces of the database:

ALTER DATABASE
   DEFAULT TEMPORARY TABLESPACE tbs_grp_01;

Disabling and Enabling a Real Application Clusters Thread: Examples

The following statement disables thread 5 in a Real Application Clusters environment:

ALTER DATABASE   
    DISABLE THREAD 5; 

The following statement enables thread 5 in a Real Application Clusters environment, making it available to any Oracle Database instance that does not explicitly request a specific thread:

ALTER DATABASE   
    ENABLE PUBLIC THREAD 5; 

Creating a New Datafile: Example

The following statement creates a new datafile tbs_f04.dbf based on the file tbs_f03.dbf. Before creating the new datafile, you must take the existing datafile (or the tablespace in which it resides) offline.

ALTER DATABASE 
    CREATE DATAFILE 'tbs_f03.dbf' 
                 AS 'tbs_f04.dbf'; 

Changing the Global Database Name: Example

The following statement changes the global name of the database and includes both the database name and domain:

ALTER DATABASE  
    RENAME GLOBAL_NAME TO demo.world.oracle.com; 

Enabling and Disabling Block Change Tracking: Examples

The following statement enables block change tracking and causes Oracle Database to create a block change tracking file named tracking_file and overwrite the file if it already exists:

ALTER DATABASE
  ENABLE BLOCK CHANGE TRACKING
    USING FILE 'tracking_file' REUSE;

The following statement disables block change tracking and deletes the existing block change tracking file:

ALTER DATABASE
  DISABLE BLOCK CHANGE TRACKING;

Resizing a Datafile: Example

The following statement attempts to change the size of datafile disk1:db1.dat:

ALTER DATABASE  
    DATAFILE 'disk1:db1.dat' RESIZE 10 M;

Clearing a Log File: Example

The following statement clears a log file:

ALTER DATABASE  
    CLEAR LOGFILE 'diskc:log3.log';

Database Recovery: Examples

The following statement performs complete recovery of the entire database, letting Oracle Database generate the name of the next archived redo log file needed:

ALTER DATABASE 
  RECOVER AUTOMATIC DATABASE; 

The following statement explicitly names a redo log file for Oracle Database to apply:

ALTER DATABASE 
    RECOVER LOGFILE 'diskc:log3.log'; 

The following statement recovers the standby datafile /finance/stbs_21.f, using the corresponding datafile in the original standby database, plus all relevant archived logs and the current standby database control file:

ALTER DATABASE  
   RECOVER STANDBY DATAFILE '/finance/stbs_21.f' 
   UNTIL CONTROLFILE;

The following statement performs time-based recovery of the database:

ALTER DATABASE 
    RECOVER AUTOMATIC UNTIL TIME '2001-10-27:14:00:00'; 

Oracle Database recovers the database until 2:00 p.m. on October 27, 2001.

For an example of recovering a tablespace, see "Using Parallel Recovery Processes: Example".


Recovering a Managed Standby Database: Examples

The following statement recovers the standby database in managed standby recovery mode:

ALTER DATABASE 
   RECOVER MANAGED STANDBY DATABASE;

The following statement puts the database in managed standby recovery mode. The managed recovery process will wait up to 60 minutes for the next archive log:

ALTER DATABASE 
   RECOVER MANAGED STANDBY DATABASE TIMEOUT 60;

If each subsequent log arrives within 60 minutes of the last log, recovery continues indefinitely or until manually terminated.

The following statement terminates the managed recovery operation:

ALTER DATABASE 
   RECOVER MANAGED STANDBY DATABASE CANCEL IMMEDIATE;

The managed recovery operation terminates before the next group of redo is read from the current redo log file. Media recovery ends during the process of applying redo from the current redo log file.