ALTER DATABASE

Open an existing database, and /or modify associated files.

Syntax:

   ALTER DATABASE database_name options

options can be any combination of the following:

open / mount options:
   MOUNT
   MOUNT STANDBY DATABASE
   MOUNT CLONE DATABASE
   MOUNT PARALLEL
   MOUNT STANDBY DATABASE
   CONVERT
   OPEN [READ ONLY]
   OPEN [READ WRITE] RESETLOGS|NORESETLOGS
   ACTIVATE STANDBY DATABASE
   [NATIONAL] CHARACTER SET char_set

archivelog options:
   ARCHIVELOG
   NOARCHIVELOG

backup and recovery options:
   BACKUP CONTROLFILE TO 'filename' [REUSE]
   BACKUP CONTROLFILE TO TRACE
   BACKUP CONTROLFILE TO TRACE RESETLOGS
   CREATE STANDBY CONTROLFILE AS 'filename' [REUSE]
   RENAME FILE 'data_file_name' TO 'data_file_name'
   RENAME FILE 'redolog_file_name' TO 'redolog_file_name'
   RECOVER recover_clause
   DATAFILE 'filename' END BACKUP

Datafile options:
   CREATE DATAFILE 'filename' AS filespec
   DATAFILE 'filename' ONLINE
   DATAFILE 'filename' OFFLINE [DROP]
   DATAFILE 'filename' RESIZE int K | M
   DATAFILE 'filename' AUTOEXTEND OFF
   DATAFILE 'filename' AUTOEXTEND ON [NEXT int K | M] [MAXSIZE int K | M]
   DATAFILE 'filename' END BACKUP
   TEMPFILE 'filename' ONLINE
   TEMPFILE 'filename' OFFLINE
   TEMPFILE 'filename' DROP
   TEMPFILE 'filename' RESIZE int K | M
   TEMPFILE 'filename' AUTOEXTEND OFF
   TEMPFILE 'filename' AUTOEXTEND ON [NEXT int K | M] [MAXSIZE int K | M]

redo log options:
   ADD LOGFILE [THREAD int] [GROUP int] filespec
   ADD LOGFILE MEMBER 'filename' [REUSE] TO GROUP int
   ADD LOGFILE MEMBER 'filename' [REUSE] TO 'filename'
   DROP LOGFILE GROUP int
   DROP LOGFILE ('filename')
   DROP LOGFILE MEMBER 'filename'
   CLEAR [UNARCHIVED] LOGFILE GROUP int [UNRECOVERABLE DATAFILE]
   CLEAR [UNARCHIVED] LOGFILE ('filename') [UNRECOVERABLE DATAFILE]

Parallel server options:
   CREATE STANDBY CONTROLFILE AS 'filename' [REUSE]
   SET DBLOW = 'text'
   SET DBHIGH = 'text'
   SET DBMAC = ON | OFF
   ENABLE [PUBLIC] THREAD int
   DISABLE THREAD int

Backwards compatibility options:
   RENAME GLOBAL_NAME TO database [domain]
   RESET COMPATIBILITY

Apart from RENAME, any option above that includes a 'filename' can be extended to cover multiple files using the syntax: ('filename1', 'filename2')

database_name is defined when the database is created - it is normally set to the same as the database SID.

Some of the commands above can only be used when the database is in a particular state:

MOUNT, CONVERT - Require that the db is Not Mounted.

ARCHIVELOG, NOARCHIVLOG, RECOVER - Require that the db is Mounted but not open (must be mount exclusive - not mount parallel).

ENABLE, DISABLE, RENAME GLOBAL_NAME, RESET, SET - Require that the db is Open.

All other options will work with the db mounted, open or closed as long as none of the files involved are 'in use'

"You know, the very powerful and the very stupid have one thing in common, they don't alter their views to fit the facts, they alter the facts to fit the views, which can be uncomfortable, if you happen to be one of the facts that needs altering."- Doctor Who


Related Commands:

DATABASE - CREATE DATABASE
RECOVER -
SHUTDOWN -
STARTUP -
ALTER SYSTEM ARCHIVE LOG STOP

Related Views:

  GLOBAL_NAME 
  DBA_DATA_FILES
  V$CONTROLFILE
  V$DATABASE
  V$DATAFILE
  V$LOG
  V$LOGFILE  




Back to the Top

Simon Sheppard
SS64.com