Pro Oracle Database 11g Administration (21 page)
You can specify ALTER TABLESPACE ... OFFLINE IMMEDIATE when taking a tablespace offline. Your database must be in archivelog mode in this situation. When using OFFLINE IMMEDIATE, Oracle doesn’t checkpoint the datafiles. 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.
83
CHAPTER 4 ■ TABLESPACES AND DATAFILES
You can also use the ALTER DATABASE DATAFILE statement to take a datafile offline. If your database is open for use, then it must be in archivelog mode in order for you to take a datafile offline with the ALTER
DATABASE DATAFILE statement. If you attempt to take a datafile offline using the ALTER DATABASE DATAFILE
statement, and your database isn’t in archivelog mode, you receive the following error: SQL> alter database datafile 6 offline;
ORA-01145: offline immediate disallowed unless media recovery enabled If your database isn’t in archivelog mode, you must specify ALTER DATABASE DATAFILE ... OFFLINE
FOR DROP when taking a datafile offline. You can specify the entire filename or provide the file number. In this example, datafile 6 is taken offline:
SQL> alter database datafile 6 offline for drop;
Now, if you attempt to online the offline datafile, you receive the following error: SQL> alter database datafile 6 online;
ORA-0113: file 6 needs media recovery
When you use the OFFLINE FOR DROP clause, no checkpoint is taken on the datafile. This means you need to perform media recovery on the datafile before bringing it online. Performing media recovery applies any changes to the datafile that are recorded in the online redo logs that aren’t in the datafiles themselves. Before you can bring online a datafile that was taken offline with the OFFLINE FOR DROP
clause, you must perform media recovery on it. You can specify either the entire filename or the file number:
SQL> recover datafile 6;
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 datafile, then you can’t recover the datafile 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 ignores the FOR DROP clause. Even when your database is in archivelog mode, you need to perform media recovery on a datafile that has been taken offline with the ALTER DATABASE DATAFILE
statement. Table 4–3 summarizes the options you must consider when taking a tablespace offline.
■
Note
While the database is in mount mode (and not open), you can use the ALTER DATABASE DATAFILE
command to take any datafile offline, including SYSTEM and UNDO.
84
CHAPTER 4 ■ TABLESPACES AND DATAFILES
Table 4–3.
Options for Taking a Datafile Offline
Statement Archivelog
Mode
Media Recovery Required Works in Mount
Required?
When Toggling Online?
Mode?
ALTER TABLESPACE ...
No No No
OFFLINE NORMAL
ALTER TABLESPACE ...
No
Maybe: depends on
No
OFFLINE TEMPORARY
whether any datafiles
already have offline
status
ALTER TABLESPACE ...
No Yes No
OFFLINE IMMEDIATE
ALTER DATABASE DATAFILE
Yes Yes Yes
... OFFLINE
ALTER DATABASE DATAFILE
No Yes Yes
... OFFLINE FOR DROP
Renaming or Relocating a Datafile
You may occasionally need to rename a datafile. For example, you may need to move datafiles due to changes in the storage devices or move files that were somehow created in the wrong location.
Before you rename a datafile, you must take the datafile offline. (See the previous section.) Here are the steps for renaming a datafile:
1. Use the following query to determine the names of existing datafiles: SQL> select name from v$datafile;
2. Take the datafile offline using either the ALTER TABLESPACE or ALTER DATABASE
DATAFILE statement (see the previous section for details on how to do this). You can also shut down your database and then start it in mount mode; the datafiles can be moved while in this mode because they aren’t open for use.
3. Physically move the datafile to the new location using either an OS command (like mv or cp) or the COPY_FILE procedure of the DBMS_FILE_TRANSFER built-in PL/SQL package.
4. Use either the ALTER TABLESPACE ... RENAME DATAFILE ... TO statement or the ALTER DATABASE RENAME FILE ... TO statement to update the control file with the new datafile name.
5. Alter the datafile online.
85
CHAPTER 4 ■ TABLESPACES AND DATAFILES
■
Note
If you need to rename datafiles associated with the SYSTEM or UNDO tablespace, you must shut down your database and start it in mount mode. When your database is in mount mode, you can rename datafiles associated with the SYSTEM or UNDO tablespace via the ALTER DATABASE RENAME FILE statement.
The following example demonstrates how to move the datafiles associated with a single tablespace.
First, take the datafiles offline with the ALTER TABLESPACE statement: SQL> alter tablespace users offline;
Now, from the operating system prompt, move two datafiles to a new location using the Linux/Unix mv command:
$ mv /ora02/dbfile/O11R2/users01.dbf /ora03/dbfile/O11R2/users01.dbf
$ mv /ora02/dbfile/O11R2/users02.dbf /ora03/dbfile/O11R2/users02.dbf
Update the control file with the ALTER TABLESPACE statement:
alter tablespace users
rename datafile
'/ora02/dbfile/O11R2/users01.dbf',
'/ora02/dbfile/O11R2/users02.dbf'
to
'/ora03/dbfile/O11R2/users01.dbf',
'/ora03/dbfile/O11R2/users02.dbf';
Finally, bring the datafiles within the tablespace back online:
SQL> alter tablespace users online;
If you want to rename datafiles from multiple tablespaces in one operation, you can use the ALTER
DATABASE RENAME FILE statement (instead of the ALTER TABLESPACE...RENAME DATAFILE statement). The following example renames all datafiles in the database. Because the SYSTEM and UNDO tablespaces’
datafiles are being moved, you must take the database offline first and then place it in mount mode: SQL> conn / as sysdba
SQL> shutdown immediate;
SQL> startup mount;
Because the database is in mount mode, the datafiles aren’t open for use, and thus there is no need to take the datafiles offline. Next, physically move the files via the Linux/Unix mv command: $ mv /ora01/dbfile/O11R2/system01.dbf /ora02/dbfile/O11R2/system01.dbf $ mv /ora01/dbfile/O11R2/sysaux01.dbf /ora02/dbfile/O11R2/sysaux01.dbf $ mv /ora01/dbfile/O11R2/undotbs01.dbf /ora02/dbfile/O11R2/undotbs01.dbf $ mv /ora01/dbfile/O11R2/users01.dbf /ora02/dbfile/O11R2/users01.dbf
$ mv /ora01/dbfile/O11R2/toos01.dbf /ora02/dbfile/O11R2/toos01.dbf
$ mv /ora01/dbfile/O11R2/users02.dbf /ora02/dbfile/O11R2/users02.dbf
86
CHAPTER 4 ■ TABLESPACES AND DATAFILES
■
Note
You must move the files before you update the control file. The ALTER DATABASE RENAME FILE command expects the file to be in the renamed location. If the file isn’t there, an error is thrown: “ORA-27037: unable to obtain file status.”
Now you can update the control file to be aware of the new filename:
alter database rename file
'/ora01/dbfile/O11R2/system01.dbf',
'/ora01/dbfile/O11R2/sysaux01.dbf',
'/ora01/dbfile/O11R2/undotbs01.dbf',
'/ora01/dbfile/O11R2/users01.dbf',
'/ora01/dbfile/O11R2/toos01.dbf',
'/ora01/dbfile/O11R2/users02.dbf'
to
'/ora02/dbfile/O11R2/system01.dbf',
'/ora02/dbfile/O11R2/sysaux01.dbf',
'/ora02/dbfile/O11R2/undotbs01.dbf',
'/ora02/dbfile/O11R2/users01.dbf',
'/ora02/dbfile/O11R2/toos01.dbf',
'/ora02/dbfile/O11R2/users02.dbf';
You should be able to open your database:
SQL> alter database open;
Another way you can relocate all datafiles in a database is to re-create the control file with the CREATE CONTROLFILE statement. The steps for this operation are as follows: 1. Create trace file that contains a CREATE CONTROLFILE statement.
2. Locate the trace file that contains the CREATE CONTROLFILE statement.
3. Modify the trace file to display the new location of the datafiles.
4. Shut down the database.
5. Physically move the datafiles using an operating system command.
6. Start the database in nomount mode.
7. Run the CREATE CONTROLFILE command.
■
Note
When you re-create a control file, be aware that any Oracle Recovery Manager (RMAN) information that was contained in the control file will be lost. If you’re not using a recovery catalog, you can repopulate the control file with RMAN backup information using the RMAN CATALOG command.
The following example walks through the previous steps. First, you write a CREATE CONTROLFILE
statement to a trace file via an ALTER DATABASE BACKUP CONTROLFILE TO TRACE statement: 87
CHAPTER 4 ■ TABLESPACES AND DATAFILES
SQL> alter database backup controlfile to trace noresetlogs;
This statement uses the NORESETLOGS clause. It instructs Oracle to write only one SQL statement to the trace file. If you don’t specify NORESETLOGS, Oracle writes two SQL statements to the trace file: one to re-create the control file with the NORESETLOGS option, and one to re-create the control file with RESETLOGS. Normally, you know whether you want to reset the online redo logs as part of re-creating the control file. In this case, you know that you don’t need to reset the online redo logs when you re-create the control file (because the online redo logs haven’t been damaged and are still in the normal location for the database).
Now, locate the directory that contains the trace files for your database: SQL> show parameter background_dump_dest
For this example, the directory is
/ora01/app/oracle/diag/rdbms/o11r2/O11R2/trace
Next, navigate to the trace directory on the operating system:
$ cd /ora01/app/oracle/diag/rdbms/o11r2/O11R2/trace
Look for the last trace file that was generated (or one that was generated at the same time you ran the ALTER DATABASE statement). In this example, the trace file is O11R2_ora_17017.trc.
Make a copy of the trace file, and open the copy with an operating system editor: $ cp O11R2_ora_17017.trc mv.sql
Edit the mv.sql file. In this example, the trace file contains only one SQL statement (because I specified NORESTLOGS when creating the trace file). If you don’t specify NORESETLOGS, the trace file contains two CREATE CONTROLFILE statements, and you must modify the trace file to remove the statement that contains the RESETLOGS as part of the statement.
Next, modify the names of the datafiles to the new locations where you want to move the datafiles.
Here is a CREATE CONTROLFILE statement for this example:
CREATE CONTROLFILE REUSE DATABASE "O11R2" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 4
MAXDATAFILES 1024
MAXINSTANCES 1
MAXLOGHISTORY 876
LOGFILE
GROUP 1 (
'/ora02/oraredo/O11R2/redo01a.rdo',
'/ora03/oraredo/O11R2/redo01b.rdo'
) SIZE 100M BLOCKSIZE 512,
GROUP 2 (
'/ora02/oraredo/O11R2/redo02a.rdo',
'/ora03/oraredo/O11R2/redo02b.rdo'
) SIZE 100M BLOCKSIZE 512,
GROUP 3 (
'/ora02/oraredo/O11R2/redo03a.rdo',
'/ora03/oraredo/O11R2/redo03b.rdo'
) SIZE 100M BLOCKSIZE 512
DATAFILE
'/ora02/dbfile/O11R2/system01.dbf',
'/ora02/dbfile/O11R2/sysaux01.dbf',
'/ora02/dbfile/O11R2/undotbs01.dbf',
88
CHAPTER 4 ■ TABLESPACES AND DATAFILES
Summary
This chapter discussed managing tablespace and datafiles. Tablespaces are logical containers for a group of datafiles. Datafiles are the physical files on disk that contain data. You should plan carefully when creating tablespaces and the corresponding datafiles.
Tablespaces allow you to separate the data of one application from another. You can also separate tables from indexes. These allow you to customize storage characteristics of the tablespace for each application. Furthermore, tablespaces provide a way to better manage applications that have different availability and backup and recovery requirements. As a DBA, you must be proficient in managing tablespaces and datafiles. In any type of environment, you have to add, rename, relocate, and drop these storage containers.
Oracle requires three types of files for a database to operate: datafiles, control files, and online redo-log files. The next chapter in the book focuses on control file and online redo-log file management.