Oracle8i SQL Reference Release 3 (8.1.7) Part Number A85397-01 |
|
SQL Statements:
ALTER CLUSTER to ALTER SYSTEM, 3 of 19
Use the ALTER
DATABASE
statement 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.
filespec
: See filespec.
logfile_descriptor
::=
maxsize_clause
::=
database
Specify the name of 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 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 Net8 database specification.
recover_clauses
You can use the following clauses when your instance has the database mounted, open or closed, and the files involved are not in use.
general_recovery_clause
The general_recovery_clause
lets you design media recovery for the database or standby database, or for specified tablespaces or files.
Restrictions:
See Also:
managed_recovery_clause
The 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.
Restrictions: The same restrictions apply as are listed under general_recovery_clause.
parallel_clause
Use the PARALLEL
clause to specify whether the recovery of media will be parallelized.
datafile_tempfile_clauses
The datafile and 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:
|
Use 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 |
|
|
The |
|
|
|
Specify |
|
|
Specify |
|
|
|
|
|
Specify 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. |
|
|
Use the |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Specify |
|
|
|
|
Lets you resize your temporary datafile or specify the
Restriction: You cannot specify |
|
|
|
Specify DROP to drop |
logfile_clauses
The logfile clauses let you add, drop, or modify log files.
|
Specify |
|
|
Specify |
|
Use the |
||
|
Use the |
|
|
|
The
If you omit |
|
|
The |
|
|
Each |
|
Use the |
|
|
You can specify an existing redo log file group in one of these ways: |
|
|
|
Specify the value of the |
|
|
List all members of the redo log file group. You must fully specify each filename according to the conventions of your operating system. |
|
Use the DROP
|
|
|
Use the
|
|
|
Use the |
|
|
|
You must specify |
|
|
|
|
|
You must specify |
|
Do not use |
|
|
If the |
controlfile_clauses
MOUNT
Use the MOUNT
clause to mount the database. Do not use this clause when the database is mounted.
|
Specify |
|
|
Specify
|
CONVERT
Use the CONVERT
clause to complete the conversion of the Oracle7 data dictionary. After you use this clause, the Oracle7 data dictionary no longer exists in the Oracle database.
ACTIVATE
STANDBY
DATABASE
The ACTIVATE
STANDBY
DATABASE
clause changes the state of a standby database to an active database. Do not use this clause when the database is mounted.
OPEN
Use the OPEN
clause to make the database 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.
RENAME
GLOBAL_NAME
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. Do not use this clause when the database is mounted.
RENAME
FILE
Use the RENAME
FILE
clause to rename 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. Do not use this clause when the database is mounted.
RESET
COMPATIBILITY
Specify RESET
COMPATIBILITY
to mark the database to be reset to an earlier version of Oracle when the database is next restarted. Do not use this clause when the database is mounted.
ENABLE
THREAD
In an Oracle Parallel Server environment, 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.
See Also: Oracle8i Parallel Server Admininstration and Tuning for more information on enabling and disabling threads. |
DISABLE
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.
See Also: Oracle8i Parallel Server Admininstration and Tuning for more information on enabling and disabling threads. |
CHARACTER
SET
, NATIONAL
CHARACTER
SET
CHARACTER
SET
changes the character set the database uses to store data. NATIONAL
CHARACTER
SET
changes the national character set used to store data in columns specifically defined as NCHAR
, NCLOB
, or NVARCHAR2
. Specify character_set
without quotation marks. The database must be open.
Restrictions:
SYSDBA
system privilege, and you must start up the database in restricted mode (for example, with the SQL*Plus STARTUP
RESTRICT
command).
READ
ONLY
/ READ
WRITE
Example
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;
PARALLEL
Example
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 'diskbk: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 an Oracle Parallel Server environment:
ALTER DATABASE stocks DISABLE THREAD 5;
The following statement enables thread 5 in an Oracle 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;
CHARACTER
SET
Example
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 © 1996-2000, Oracle Corporation. All Rights Reserved. |
|