Databases Reference
In-Depth Information
You can specify
ALTER TABLESPACE ... OFFLINE IMMEDIATE
when taking a tablespace offline. Your database
must be in archivelog mode in this situation, or the following error is thrown:
ORA-01145: offline immediate disallowed unless media recovery enabled
When using
OFFLINE IMMEDIATE
, Oracle doesn't issue a checkpoint on the data files. You must perform media
recovery on the tablespace before bringing it back online.
■
Note
You can't take the
SYSTEM
or
UNDO
tablespace offline while the database is open.
You can also use the
ALTER DATABASE DATAFILE
statement to take a data file offline. If your database is open for
use, then it must be in archivelog mode in order for you to take a data file offline with the
ALTER DATABASE DATAFILE
statement. If you attempt to take a data file offline using the
ALTER DATABASE DATAFILE
statement, and your database
isn't in archivelog mode, the
ORA-01145
error is thrown.
If your database isn't in archivelog mode, you must specify
ALTER DATABASE DATAFILE ... OFFLINE FOR DROP
when taking a data file offline. You can specify the entire file name or provide the file number. In this example, data
file 4 is taken offline:
SQL> alter database datafile 4 offline for drop;
Now, if you attempt to bring online the offline data file, you receive the following error:
SQL> alter database datafile 4 online;
ORA-01113: file 4 needs media recovery
When you use the
OFFLINE FOR DROP
clause, no checkpoint is taken on the data file. This means you need to
perform media recovery on the data file before bringing it online. Performing media recovery applies any changes
to the data file that are recorded in the online redo logs that aren't in the data files themselves. Before you can bring
online a data file that was taken offline with the
OFFLINE FOR DROP
clause, you must perform media recovery on it.
You can specify either the entire file name or the file number:
SQL> recover datafile 4;
If the redo information that Oracle needs is contained in the online redo logs, you should see this message:
Media recovery complete.
If your database isn't in archivelog mode, and if Oracle needs redo information not contained in the online redo
logs to recover the data file, then you can't recover the data file and place it back online.
If your database is in archivelog mode, you can take it offline without the
FOR DROP
clause. In this scenario, Oracle
overlooks the
FOR DROP
clause. Even when your database is in archivelog mode, you need to perform media recovery
on a data file that has been taken offline with the
ALTER DATABASE DATAFILE
statement. Table
4-3
summarizes the
options you must consider when taking a tablespace/data files offline.
■
While the database is in mount mode (and not open), you can use the
ALTER DATABASE DATAFILE
command to
take any data file offline, including
SYSTEM
and
UNDO
.
Note
