B Managing the Database

B.3.1 Using Commands to Manage Database Files

This section discusses commands that you can use to manage database files.

ALTER DATABASE

You can use the ALTER DATABASE command to mount, open, or close a database, to add or drop redo log files, and to archive redo log files. You can also use this command to rename or move tablespace files and redo log files.

You cannot use the ALTER DATABASE BACKUP CONTROLFILE command to back up control files to tape. To back up control files to tape, back up to disk and then copy to tape.

DROP TABLESPACE

Before using the DROP TABLESPACE INCLUDING CONTENTS command, take the tablespace offline to ensure that no temporary segments are in use.

B.3.2 Adding Files

When specifying files to be added to the database, logical names are fully translated to either physical device names or system-level concealed logical names (if defined) and then written to the control file.

B.3.3 Renaming Files

If the name of the physical device is somehow dissociated from the database file locations, then the database cannot access these files. Use the ALTER DATABASE command to rename the file to its current location. After renaming the files, shut down the database and then back up the control files as in the following example:

SQL> ALTER DATABASE RENAME FILE 
2> 'DISK$1:[ORACLE10g.oradata.V10TEST]SYSTEM01.DBF' TO 
3> 'MY$DISK:[ORACLE10g.oradata.V10TEST]SYSTEM01.DBF'
SQL> EXIT
$ BACKUP/LOG/VERIFY/-
DISK$1:[ORACLE10g.oradata.V10TEST]*.CTL -
MY$DISK:[ORACLE10g.oradata.V10TEST]*.CTL

$ SQLPLUS/NOLOG 
SQL> CONNECT / AS SYSDBA 
SQL> SELECT * FROM V$DBFILE;
SQL> DISCONNECT 

B.3.4 Moving Tablespace Files

To move a tablespace file to a new location:

1. Identify and write down the exact, fully qualified file name from the data dictionary view, and shut down the database. The physical device name and the file location must appear exactly as in the control file and the data dictionary view, DBA_DATA_FILES or V$LOGFILE.

   $ SQLPLUS/NOLOG 
   SQL> CONNECT / AS SYSDBA 
   SQL> SELECT * from V$DBFILE; 
   SQL> SELECT * from V$LOGFILE; 
   SQL> SHUTDOWN 
   SQL> EXIT 
   
   

2. Back up the tablespace and control files that you want to move.

3. Copy or move the file to a new location.

   Use BACKUP/VERIFY/DELETE to move the file.

   $ BACKUP/IGNORE=NOBACK/DELETE/VERIFY -
   device:[dir]filename.ext -
   new_device:[new_dir]new_filename.ext
   
   

4. Without opening it, mount the database in Exclusive mode.

   $ SQLPLUS/NOLOG
   SQL> CONNECT / AS SYSDBA 
   SQL> STARTUP EXCLUSIVE MOUNT dbname
   
   

5. Rename the file in the database using the exact string taken from V$DBFILE.

   SQL> ALTER DATABASE
      2> RENAME FILE 'device:[dir]filename.ext'
      3> to 'new_device:[new_dir]new_filename.ext';
   SQL> ALTER DATABASE dbname OPEN;
   SQL> EXIT
   
   

6. Back up the control files.

B.3.5 Moving Redo Log Files

Perform the following steps to move a redo log file to a new location:

1. Identify the fully qualified file name of the redo log files that you want to move by using one of the following methods:

   1. Start the database.

   2. Run the following query:

      SQL> SELECT * FROM V$LOGFILE;
      
      

2. Shut down the database, create a backup of the redo log files in the new location, and mount the database in Exclusive mode (not opened).

   Note:

   After the database is shut down, make copies of all database, control, and redo log files as a precaution against any problems that can arise during this procedure.

   $ SQLPLUS/NOLOG 
   SQL> CONNECT / AS SYSDBA 
   SQL> SHUTDOWN 
   SQL> EXIT 
   $ BACKUP/IGNORE=NOBACK -
   old_device:[dir]filename.ext -
   new_device:[new_dir]new_filename.ext
   $ SQLPLUS/NOLOG
   SQL> CONNECT / AS SYSDBA 
   SQL> STARTUP EXCLUSIVE MOUNT dbname
   
   

   Note:

   Having the database mounted and closed is essential when working with the redo log files. This prevents any log files from becoming online or marked as current by the LGWR.

3. From SQL*Plus, rename the files in the database using the ALTER DATABASE command. Specify the full file path.

   SQL> CONNECT / AS SYSDBA 
   SQL> ALTER DATABASE RENAME FILE
      2> 'device:[dir]old_redofile1.RDO',
      3> 'device:[dir]old_redofile2.RDO' to
      4> 'device:[dir]new_redofile1.RDO',
      5> 'device:[dir]new_redofile2.RDO';
   
   

   The file names specified must be correct and the new files must already exist. If either of these requirements is not met, then the statement fails.

4. Shut down the database by using the following command:

   SQL> SHUTDOWN
   
   

5. Back up the control files for safety.

6. Restart the database using the following commands.

   SQL> CONNECT / AS SYSDBA
   SQL> STARTUP OPEN dbname
   SQL> EXIT