Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
SQL Statements (continued), 3 of 17
recover_clauses::=
filespec: See "filespec".
logfile_descriptor::=
maxsize_clause::=
To modify, maintain, or recover an existing database.
See Also:
|
You must have ALTER
DATABASE
system privilege.
To specify the RECOVER
clause, you must also have the OSDBA role enabled.
database |
identifies the database to be altered. The database name can contain only ASCII characters. If you omit database, Oracle alters the database identified by the value of the initialization parameter |
|
You can use the following clauses only when the database is not mounted by your instance: |
||
|
mounts the database. |
|
|
|
See Also: Oracle8i Standby Database Concepts and Administration. |
|
|
See Also: Oracle8i Backup and Recovery Guide. |
|
completes the conversion of the Oracle7 data dictionary. After you use this clause, the Oracle7 data dictionary no longer exists in the Oracle database. Use this clause only when you are migrating to Oracle8i. See Also: Oracle8i Migration. |
|
|
changes the state of a standby database to an active database. See Also: Oracle8i Standby Database Concepts and Administration. |
|
|
opens the database, making it available for normal use. You must mount the database before you can open it. You must activate a standby database before you can open it. |
|
|
|
restricts users to read-only transactions, preventing them from generating redo logs. You can use this clause to make a standby database available for queries even while archive logs are being copied from the primary database site. |
|
|
Restrictions:
|
|
|
opens the database in read-write mode, allowing users to generate redo logs. This is the default. |
|
|
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 that are in the redo log, but not in the database.
You must specify |
|
|
leaves the log sequence number and redo log files in their current state. |
|
Restriction: You can specify |
|
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: |
||
general_recovery_clause |
lets you design media recovery for the database or standby database, or for specified tablespaces or files. See Also: Oracle8i Backup and Recovery Guide for more information on media recovery. |
|
|
Note: If you do not have special media requirements, Oracle Corporation recommends that you use the SQL*Plus See Also: SQL*Plus User's Guide and Reference. |
|
|
Restrictions:
|
|
|
automatically generates the name of the next archived redo log file needed to continue the recovery operation. If the If the resulting file is found, Oracle applies the redo contained in that file. If the file is not found, Oracle prompts you for a filename, displaying the generated filename as a suggestion. |
|
|
If you specify neither |
|
|
specifies 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 assumes the archived redo log file group is in the location specified by the initialization parameter |
|
|
recovers the 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. |
|
|
recovers the entire database. This is the default. You can use this clause only when the database is closed. |
|
|
Note: This clause recovers only online datafiles. |
|
|
|
specifies the duration of the recovery operation. |
|
|
|
|
|
|
|
|
|
|
|
specifies that a backup control file is being used instead of the current control file. |
|
recovers only the specified tablespaces. You can use this clause if the database is open or closed, provided the tablespaces to be recovered are offline. |
|
|
recovers the specified datafiles. You can use this clause when the database is open or closed, provided the datafiles to be recovered are offline. |
|
|
reconstructs a lost or damaged datafile or tablespace in the standby database using archived redo log files copied from the primary database and a control file. |
|
|
|
specifies that the recovery of an old standby datafile or tablespace uses the current standby database control file. However, any redo in advance of the standby controlfile will not be applied. The keywords |
|
continues media recovery by applying the specified redo log file. |
|
|
continues multi-instance recovery after it has been interrupted to disable a thread. |
|
|
continues recovery using the redo log file that Oracle would automatically generate if no other logfile were specified. This clause is equivalent to specifying |
|
|
terminates cancel-based recovery. |
|
managed_recovery_clause |
specifies automated standby recovery mode. This mode assumes that the automated 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. Automated standby recovery is restricted to media recovery. See Also: Oracle8i Backup and Recovery Guide for more information on the parameters of this clause. Restrictions: The same restrictions apply as are listed under general_recovery_clause. |
|
|
specifies 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 automated 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 automated standby recovery mode. |
|
|
If you do not specify this clause, the database remains in automated standby recovery mode until you reissue the statement with the |
|
|
terminates the managed recovery operation after applying all the redo in the current archived redo file. |
|
|
terminates the managed recovery operation after applying all the redo in the current archived redo file or after the next redo log file read, whichever comes first.
Restriction: This clause cannot be issued from the same session that issued the |
|
parallel_clause |
specifies whether the recovery of media will be parallelized. For additional information, see the Notes to the parallel_clause of "CREATE TABLE". |
|
|
|
specifies serial execution. This is the default. |
|
|
causes Oracle to select a degree of parallelism equal to the number of CPUs available on all participating instances times the value of the |
|
|
specifies the degree of parallelism, which is the number of parallel threads used in the parallel operation. Each parallel thread may use one or two parallel execution processes. Normally Oracle calculates the optimum degree of parallelism, so it is not necessary for you to specify integer. |
|
changes 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. See Also: Oracle8i Distributed Database Systems for more information on global names. |
|
|
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. |
|
|
renames datafiles, tempfiles, or redo log file members. This clause renames only 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 before specifying this clause. |
|
|
marks the database to be reset to an earlier version of Oracle when the database is next restarted. |
|
|
Note: See Also: Oracle8i Migration for more information on downgrading to an earlier version of Oracle. |
|
You can use the following clauses only when your instance has the database open: |
||
|
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. |
|
|
|
makes the enabled thread available to any instance that does not explicitly request a specific thread with the initialization parameter |
|
disables the specified thread, making it unavailable to all instances. You cannot disable a thread if an instance using it has the database mounted. |
|
See Also: Oracle8i Designing and Tuning for Performance for more information on enabling and disabling threads. |
||
|
|
|
|
WARNING: You cannot roll back an |
|
|
Restrictions:
|
|
datafile/tempfile_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: |
||
|
creates 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' 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 |
|
|
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 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. |
|
|
Restriction: You cannot create a new file based on the first datafile of the SYSTEM tablespace. |
|
|
affects your database files as follows: |
|
|
|
brings the datafile online. |
|
|
takes 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. |
|
|
|
|
|
attempts to increase or decrease the size of the datafile to the specified absolute size in bytes. Use K or M to specify this size in kilobytes or megabytes. 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 returns an error. |
|
autoextend_clause |
enables or disables the automatic extension of a datafile. If you do not specify this clause, datafiles are not automatically extended. |
|
|
OFF disables autoextend if it is turned on. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
avoids media recovery on database startup after an online tablespace backup was interrupted by a system failure or instance failure or |
|
WARNING: Do not use |
|
|
Lets you resize your temporary datafile or specify the autoextend_clause, with the same effect as with a permanent datafile. Restriction: You cannot specify TEMPFILE unless the database is open. |
|
|
|
drops tempfile from the database. The tablespace remains. |
logfile_clauses |
lets you add, drop, or modify log files. |
|
|
specifies that the contents of a redo log file group must 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, mounting the database in parallel server disabled mode. |
|
|
specifies that 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. |
|
Use the |
||
|
adds one or more redo log file groups to the specified thread, making them available to the instance assigned the thread. |
|
|
|
is applicable only if you are using Oracle with the Parallel Server option in parallel mode. integer is the thread number. The number of threads you can create is limited by the value of the
If you omit |
|
|
uniquely identifies the redo log file group among all groups in all threads and can range from 1 to the |
|
filespec |
Each filespec specifies a redo log file group containing one or more members, or copies. See the syntax description of filespec in "filespec". |
|
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 |
|
|
You can specify an existing redo log file group in one of these ways: |
|
|
|
Specify the value of the |
|
list of filenames |
List all members of the redo log file group. You must fully specify each filename according to the conventions of your operating system. |
|
drops all members of a redo log file group. Specify a redo log file group as indicated for the
|
|
|
drops one or more redo log file members. Each 'filename' must fully specify a member using the conventions for filenames on your operating system.
|
|
|
reinitializes an online redo log, optionally without archiving the redo log. |
|
|
|
You must specify |
|
|
WARNING: Specifying |
|
Do not use |
|
|
If the |
|
|
|
You must specify |
controlfile_clauses |
|
|
|
creates a control file to be used to maintain a standby database. If the file already exists, you must specify See Also: Oracle8i Standby Database Concepts and Administration. |
|
|
backs up the current control file. |
|
|
|
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 |
|
|
writes SQL statements to the database's trace file rather than making a physical backup of the control file. The SQL statements can 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 statements from the trace file into a script file, edit the statements as necessary, and use the database if all copies of the control file are lost (or to change the size of the control file). |
|
|
|
|
|
The first statement below opens the database in read-only mode. The second statement returns the database to read-write mode and clears the online redo logs:
ALTER DATABASE OPEN READ ONLY; ALTER DATABASE OPEN READ WRITE RESETLOGS;
The following statement performs tablespace recovery using parallel recovery processes:
ALTER DATABASE RECOVER TABLESPACE binky PARALLEL;
The following statement adds a redo log file group with two members and identifies it with a GROUP
parameter value of 3:
ALTER DATABASE stocks ADD LOGFILE GROUP 3 ('diska:log3.log' , 'diskb:log3.log') SIZE 50K;
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;
The following statement drops the redo log file member added in the previous example:
ALTER DATABASE stocks DROP LOGFILE MEMBER 'diskc:log3.log';
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.
The following statement drops all members of the redo log file group 3:
ALTER DATABASE stocks DROP LOGFILE GROUP 3;
The following statement adds a redo log file group containing three members to thread 5 (in an Oracle Parallel Server environment) and assigns it a GROUP
parameter value of 4:
ALTER DATABASE stocks ADD LOGFILE THREAD 5 GROUP 4 ('diska:log4.log', 'diskb:log4:log', 'diskc:log4.log' );
The following statement disables thread 5 in a parallel server:
ALTER DATABASE stocks DISABLE THREAD 5;
The following statement enables thread 5 in a parallel server, making it available to any Oracle instance that does not explicitly request a specific thread:
ALTER DATABASE stocks ENABLE PUBLIC THREAD 5;
The following statement creates a new datafile 'DISK2:DB1.DAT
' based on the file 'DISK1:DB1.DAT
':
ALTER DATABASE CREATE DATAFILE 'disk1:db1.dat' AS 'disk2:db1.dat';
The following statement changes the global name of the database and includes both the database name and domain:
ALTER DATABASE RENAME GLOBAL_NAME TO sales.australia.acme.com;
The following statements change the database character set and national character set to the WE8ISO8859P1 character set:
ALTER DATABASE db1 CHARACTER SET WE8ISO8859P1; ALTER DATABASE db1 NATIONAL CHARACTER SET WE8ISO8859P1;
The database name is optional, and the character set name is specified without quotation marks.
The following statement attempts to change the size of datafile 'DISK1:DB1.DAT
':
ALTER DATABASE DATAFILE 'disk1:db1.dat' RESIZE 10 M;
The following statement clears a log file:
ALTER DATABASE CLEAR LOGFILE 'disk3:log.dbf';
The following statement performs complete recovery of the entire database, letting Oracle 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 to apply:
ALTER DATABASE RECOVER LOGFILE 'diska:arch0006.arc';
The following statement performs time-based recovery of the database:
ALTER DATABASE RECOVER AUTOMATIC UNTIL TIME '1998-10-27:14:00:00';
Oracle recovers the database until 2:00 pm on October 27, 1998.
The following statement recovers the tablespace USER5
:
ALTER DATABASE RECOVER TABLESPACE user5;
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 recovers the standby database in automated standby recovery mode:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
The following statement puts the database in automated 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 in the "middle" of applying redo from the current redo log file.
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|