Oracle8i SQL Reference
Release 3 (8.1.7)

Part Number A85397-01

Library

Product

Contents

Index

Go to previous page Go to beginning of chapter Go to next page

SQL Statements:
ALTER CLUSTER to ALTER SYSTEM, 3 of 19


ALTER DATABASE

Purpose

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

See Also:

 

Prerequisites

You must have ALTER DATABASE system privilege.

To specify the RECOVER clause, you must also have the OSDBA role enabled.

Syntax


recover_clauses::=


general_recovery_clause::=


managed_recovery_clause::=


datafile_tempfile_clauses::=


autoextend_clause::=


filespec: See filespec.

logfile_clauses::=


logfile_descriptor::=


controlfile_clauses::=


maxsize_clause::=


parallel_clause::=


Keywords and Parameters

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:

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.

See Also: Oracle8i Backup and Recovery Guide for more information on the parameters of this clause. 

TIMEOUT integer 

Use the TIMEOUT clause to 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 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 RECOVER CANCEL clause or until instance shutdown or failure. 

CANCEL 

Use the CANCEL clause to terminate the managed recovery operation after applying all the redo in the current archived redo file. 

CANCEL IMMEDIATE 

Specify CANCEL IMMEDIATE to terminate 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 RECOVER MANAGED STANDBY DATABASE statement. 

parallel_clause

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


Note:

The syntax of the parallel_clause supersedes syntax appearing in earlier releases of Oracle. Superseded syntax is still supported for backward compatibility, but may result in slightly different behavior. 


NOPARALLEL 

Specify NOPARALLEL for serial execution. This is the default. 

PARALLEL 

Specify PARALLEL if you want Oracle to select a degree of parallelism equal to the number of CPUs available on all participating instances times the value of the PARALLEL_THREADS_PER_CPU initialization parameter. 

PARALLEL integer 

Specification of integer indicates 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 servers. Normally Oracle calculates the optimum degree of parallelism, so it is not necessary for you to specify integer

See Also: "Notes on the parallel_clause" for CREATE TABLE  

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:

CREATE DATAFILE 

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' must identify a file that is or was once part of the database. The filespec specifies the name and size of the new datafile. If you omit the AS clause, Oracle creates the new file with the name and size as the file specified by 'filename'. 

 

During recovery, all archived redo logs written to since the original datafile was created must be applied to the new, empty version of the lost datafile. 

 

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. 

DATAFILE 'filename' 

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. 

 

 

DROP takes a datafile offline when the database is in NOARCHIVELOG mode. 

 

RESIZE 

Specify RESIZE if you want Oracle to attempt 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 

Use the autoextend_clause to enable or disable 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. NEXT and MAXSIZE are set to zero. Values for NEXT and MAXSIZE must be respecified in further ALTER DATABASE AUTOEXTEND statements. 

 

 

  • ON enables autoextend.

 

 

 

  • NEXT specifies in bytes the size of the next increment of disk space to be automatically allocated to the datafile when more extents are required. Use K or M to specify this size in kilobytes or megabytes. The default is one data block.

 

 

 

  • MAXSIZE specifies the maximum disk space allowed for automatic extension of the datafile.

 

 

 

  • UNLIMITED sets no limit on allocating disk space to the datafile.

 

 

END BACKUP  

Specify END BACKUP to avoid media recovery on database startup after an online tablespace backup was interrupted by a system failure or instance failure or SHUTDOWN ABORT

 

Caution: Do not use ALTER TABLESPACE ... END BACKUP if you have restored any of the files affected from a backup. Media recovery is fully described in Oracle8i Backup and Recovery Guide.

 

TEMPFILE 'filename' 

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. 

 

DROP 

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

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, mounting the database in parallel server disabled mode.  

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 the ARCHIVELOG clause and NOARCHIVELOG clause only if your instance has the database mounted in Oracle Parallel Server disabled mode, but not open. 

ADD LOGFILE 

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

 

THREAD integer 

The THREAD clause 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 MAXINSTANCES parameter specified in the CREATE DATABASE statement.

If you omit THREAD, the redo log file group is added to the thread assigned to your instance.  

 

GROUP integer 

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

 

filespec 

Each filespec specifies a redo log file group containing one or more members, or copies.

See Also: the syntax description of filespec in filespec

 

ADD LOGFILE MEMBER 

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 creates a file of the correct size. You cannot add a member to a group if all of the group's members have been lost through media failure.  

 

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

 

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

DROP LOGFILE  

Use the DROP LOGFILE clause to drop all members of a redo log file group. Specify a redo log file group as indicated for the ADD LOGFILE MEMBER clause.

  • To drop the current log file group, you must first issue an ALTER SYSTEM SWITCH LOGFILE statement.

    See Also: ALTER SYSTEM

  • You cannot drop a redo log file group if it needs archiving.

  • You cannot drop a redo log file group if doing so would cause the redo thread to contain less than two redo log file groups.

 

DROP LOGFILE MEMBER  

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.

  • To drop a log file in the current log, you must first issue an ALTER SYSTEM SWITCH LOGFILE statement.

    See Also: ALTER SYSTEM

  • You cannot use this clause to drop all members of a redo log file group that contains valid data. To perform this operation, use the DROP LOGFILE clause.

 

CLEAR LOGFILE  

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 also may be issued for the current redo log of a closed thread. 

 

UNARCHIVED  

You must specify UNARCHIVED if you want to reuse a redo log that was not archived. 

 

 

Caution: Specifying UNARCHIVED makes backups unusable if the redo log is needed for recovery.

 

 

UNRECOVERABLE DATAFILE 

You must specify UNRECOVERABLE DATAFILE if you have taken the datafile offline with the database in ARCHIVELOG mode (that is, you specified ALTER DATABSE ... DATAFILE OFFLINE without the DROP keyword), and if the unarchived log to be cleared is needed to recover the datafile before bringing it back online. In this case, you must drop the datafile and the entire tablespace once the CLEAR LOGFILE statement completes. 

 

Do not use CLEAR LOGFILE to clear a log needed for media recovery. If it is necessary to clear a log containing redo after the database checkpoint, you must first perform incomplete media recovery. The current redo log of an open thread can be cleared. The current log of a closed thread can be cleared by switching logs in the closed thread. 

 

If the CLEAR LOGFILE statement is interrupted by a system or instance failure, then the database may hang. If this occurs, reissue the statement after the database is restarted. If the failure occurred because of I/O errors accessing one member of a log group, then that member can be dropped and other members added. 

controlfile_clauses

CREATE STANDBY CONTROLFILE 

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

See Also: Oracle8i Standby Database Concepts and Administration.

 

BACKUP CONTROLFILE 

Use the BACKUP CONTROLFILE clause to back up the current control file. 

 

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 to write 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). 

 

 

  • RESETLOGS indicates that the SQL statement written to the trace file for starting the database is ALTER DATABASE OPEN RESETLOGS.

  • NORESETLOGS indicates that the SQL statement written to the trace file for starting the database is ALTER DATABASE OPEN NORESETLOGS.

 

MOUNT

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

STANDBY DATABASE 

Specify STANDBY to mount the standby database.

See Also: Oracle8i Standby Database Concepts and Administration

 

CLONE DATABASE 

Specify CLONE to mount the clone database.

See Also: Oracle8i Backup and Recovery Guide

 

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.


Note: Use this clause only when you are migrating to Oracle8i, and do not use this clause when the database is mounted. 


See Also: Oracle8i Migration 

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.

See Also: Oracle8i Standby Database Concepts and Administration 

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.

READ ONLY

 

Specify READ ONLY to restrict 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:

  • You cannot open a database READ ONLY if it is currently opened READ WRITE by another instance.

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

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

 

READ WRITE

 

Specify READ WRITE to open the database in read-write mode, allowing users to generate redo logs. This is the default. 

 

RESETLOGS 

Specify RESETLOGS to reset 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 RESETLOGS to open the database after performing media recovery with an incomplete recovery using the RECOVER clause or with a backup control file. After opening the database with this clause, you should perform a complete database backup.  

 

NORESETLOGS 

Specify NORESETLOGS to retain the current stat of the log sequence number and redo log files. 

 

Restriction: You can specify RESETLOGS and NORESETLOGS only after performing incomplete media recovery or complete media recovery with a backup control file. In any other case, Oracle uses the NORESETLOGS automatically.  

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.


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. 


See Also: Oracle8i Distributed Database Systems for more information on global names 

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.


Note: RESET COMPATIBILITY works only if you have successfully disabled Oracle features that affect backward compatibility.  


See Also: Oracle8i Migration for more information on downgrading to an earlier version of Oracle 

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.

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. If you omit PUBLIC, the thread is available only to the instance that explicitly requests it with the initialization parameter THREAD.  

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.


Caution: You cannot roll back an ALTER DATABASE CHARACTER SET or ALTER DATABASE NATIONAL CHARACTER SET statement. Therefore, you should perform a full backup before issuing either of these statements. 


Restrictions:

Examples

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;

Redo Log File Group Example

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; 

Redo Log File Group Member Example

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; 

Dropping a Log File Member

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

ALTER DATABASE stocks  
    DROP LOGFILE MEMBER 'diskc:log3.log'; 

Renaming a Log File Member Example

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.

Dropping All Log File Group Members Example

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

ALTER DATABASE stocks DROP LOGFILE GROUP 3; 

Adding a Redo Log File Group Example

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' ); 

Disabling a Parallel Server Thread Example

The following statement disables thread 5 in an Oracle Parallel Server environment:

ALTER DATABASE stocks  
    DISABLE THREAD 5; 

Enabling a Parallel Server Thread Example

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; 

Creating a New Datafile Example

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'; 

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

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

The following statement clears a log file:

ALTER DATABASE  
    CLEAR LOGFILE 'disk3:log.dbf';

Database Recovery Examples

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;

Managed Standby Database Examples

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.


Go to previous page Go to beginning of chapter Go to next page
Oracle
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index