ALTER DATABASE [ database ]
{ startup_clauses
| recovery_clauses
| database_file_clauses
| logfile_clauses
| controlfile_clauses
| standby_database_clauses
| default_settings_clauses
| instance_clauses
| security_clause
} ;
startup_clauses:
{ MOUNT [ { STANDBY | CLONE } DATABASE ]
| OPEN
{ [ READ WRITE ]
[ RESETLOGS | NORESETLOGS ]
[ UPGRADE | DOWNGRADE ]
| READ ONLY
}
}
recovery_clauses:
{ general_recovery
| managed_standby_recovery
| BEGIN BACKUP
| END BACKUP
}
general_recovery:
RECOVER
[ AUTOMATIC ]
[ FROM 'location' ]
{ { full_database_recovery
| partial_database_recovery
| LOGFILE 'filename'
}
[ { TEST
| ALLOW integer CORRUPTION
| parallel_clause
}...
]
| CONTINUE [ DEFAULT ]
| CANCEL
}
full_database_recovery:
[ STANDBY ] DATABASE
[ { UNTIL { CANCEL
| TIME date
| CHANGE integer
| CONSISTENT
}
| USING BACKUP CONTROLFILE
}...
]
parallel_clause:
{ NOPARALLEL | PARALLEL [ integer ] }
managed_standby_recovery:
RECOVER
{ MANAGED STANDBY DATABASE
[ { USING CURRENT LOGFILE
| DISCONNECT [FROM SESSION]
| NODELAY
| UNTIL CHANGE integer
| UNTIL CONSISTENT
| parallel_clause
}...
| FINISH
| CANCEL
]
| TO LOGICAL STANDBY { db_name | KEEP IDENTITY }
}
database_file_clauses :
{ RENAME FILE 'filename' [, 'filename' ]...
TO 'filename'
| create_datafile_clause
| alter_datafile_clause
| alter_tempfile_clause
}
create_datafile_clause:
CREATE DATAFILE
{ 'filename' | filenumber }
[, 'filename' | filenumber ]...
}
[ AS { file_specification
[, file_specification ]...
| NEW
}
]
alter_datafile_clause:
DATAFILE
{ 'filename' | filenumber }
[, 'filename' | filenumber ]...
}
{ ONLINE
| OFFLINE [ FOR DROP ]
| RESIZE size_clause
| autoextend_clause
| END BACKUP
}
alter_tempfile_clause:
TEMPFILE
{ 'filename' [, 'filename' ]...
| filenumber [, filenumber ]...
}
{ RESIZE size_clause
| autoextend_clause
| DROP [ INCLUDING DATAFILES ]
| ONLINE
| OFFLINE
}
autoextend_clause :
AUTOEXTEND
{ OFF
| ON [ NEXT size_clause ]
[ maxsize_clause ]
}
maxsize_clause:
MAXSIZE { UNLIMITED | size_clause }
logfile_clauses :
{ { ARCHIVELOG [ MANUAL ]
| NOARCHIVELOG
}
| [ NO ] FORCE LOGGING
| RENAME FILE 'filename' [, 'filename' ]...
TO 'filename'
| CLEAR [ UNARCHIVED ]
LOGFILE logfile_descriptor [, logfile_descriptor ]...
[ UNRECOVERABLE DATAFILE ]
| add_logfile_clauses
| drop_logfile_clauses
| switch_logfile_clause
| supplemental_db_logging
}
add_logfile_clauses:
ADD [ STANDBY ] LOGFILE
{
{ [ INSTANCE 'instance_name' ] | [ THREAD 'integer' ] }
[ GROUP integer ] redo_log_file_spec
[, [ GROUP integer ] redo_log_file_spec ]...
| MEMBER 'filename' [ REUSE ] [, 'filename' [ REUSE ] ]...
TO logfile_descriptor [, logfile_descriptor ]...
}
drop_logfile_clauses:
DROP [ STANDBY ] LOGFILE
{ logfile_descriptor
[, logfile_descriptor ]...
| MEMBER 'filename'
[, 'filename' ]...
}
switch_logfile_clause:
SWITCH ALL LOGFILES TO BLOCKSIZE integer
supplemental_db_logging:
{ ADD | DROP } SUPPLEMENTAL LOG
{ DATA
| supplemental_id_key_clause
| supplemental_plsql_clause
}
Example
ALTER DATABASE OPEN READ ONLY;
ALTER DATABASE OPEN READ WRITE RESETLOGS;
ALTER DATABASE
RECOVER TABLESPACE tbs_03
PARALLEL;
ALTER DATABASE
ADD LOGFILE GROUP 3
('diska:log3.log' ,
'diskb:log3.log') SIZE 50K;
ALTER DATABASE
ADD LOGFILE THREAD 5 GROUP 4
('diska:log4.log',
'diskb:log4:log');
ALTER DATABASE
ADD LOGFILE MEMBER 'diskc:log3.log'
TO GROUP 3;
ALTER DATABASE
DROP LOGFILE MEMBER 'diskb:log3.log';
ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE
RENAME FILE 'diskc:log3.log' TO 'diskb:log3.log';
ALTER DATABASE
SET DEFAULT BIGFILE TABLESPACE;
ALTER DATABASE
DEFAULT TEMPORARY TABLESPACE tbs_05;
ALTER DATABASE
DEFAULT TEMPORARY TABLESPACE tbs_grp_01;
ALTER DATABASE
CREATE DATAFILE 'tbs_f03.dbf'
AS 'tbs_f04.dbf';
ALTER DATABASE TEMPFILE 'temp02.dbf' OFFLINE;
ALTER DATABASE RENAME FILE 'temp02.dbf' TO 'temp03.dbf';
ALTER DATABASE
RENAME GLOBAL_NAME TO demo.world.example.com;
ALTER DATABASE
ENABLE BLOCK CHANGE TRACKING
USING FILE 'tracking_file' REUSE;
ALTER DATABASE
DISABLE BLOCK CHANGE TRACKING;
ALTER DATABASE
DATAFILE 'diskb:tbs_f5.dbf' RESIZE 10 M;
ALTER DATABASE
CLEAR LOGFILE 'diskc:log3.log';
ALTER DATABASE
RECOVER AUTOMATIC DATABASE;
ALTER DATABASE
RECOVER LOGFILE 'diskc:log3.log';
ALTER DATABASE
RECOVER AUTOMATIC UNTIL TIME '2001-10-27:14:00:00';