Toad World Blog

Oracle Administering Oracle-Managed Datafiles (OMFs)

Aug 14, 2015 3:37:00 PM by Quest Software

Naming Conventions

A naming convention is used when Oracle creates managed database datafiles. The following table shows the naming conventions fo the database datafiles. (Note that these may differ for avious operating system ports. Check your operating system documentation for the filenaming convention used.) A new datafile cannot be created using the OMF naming convention. Attempting to do so will result in an error.

File Type Naming Convention Example
Datafile ora_{tablespace_name}_{unique character string}.dbf ORA_NEW_TBS_ZV3NZF00.DBF
Tempfile ora_{tablespace_name}_{unique character string}.tmp ORA_TEMP_TBS_ZV3NZF01.DBF
Redo logfile ora_{online redo log group number}_{unique character string}.log ORA_4_ZV307100.LOG
Control file ora_{unique character string}.ctl ORA_4_ZV307100.CTL

Note: Up to eight characters of the tablespace name are used. Therefore, the second part of the name, the unique character string, is important. Two tablespaces may have unique names, but, the first 8 characters of the tablespaces may be the same.

The names of Oracle-managed datafiles can be used in SQL Statements just as normal datafiles would be used. For example, the alter database rename file or alter tablespace rename datafile commands can be used to rename an Oracle database managed datafile. A specific Oracle-managed redo logfile can be dropped with the alter database drop logfile command, and so on.

The steps below demonstrate how to rename an Oracle-managed datafile:

  1. Offline the tablespace that the OMF is associated with (or offline the OMF).
     
  2. Physically rename the datafile at the OS Level.
     
  3. Once the OMF has been renamed, issue the rename command from within the database (using the ALTER DATABASE or ALTER TABLESPACE commands) to rename the OMF within the database.
     
  4. Online the tabespace or datafile.

If you rename the Oracle-managed datafile using a file-naming convention that does not follow the OMF naming convention, that file will no longer be an OMF. An exisiting non-OMF cannot be renamed to a filename starting with "ORA_". Because ORA_ prefixes are reserved for OMFs, this will cause an error.

Following is an example of renaming an existing Oracle-managed datafile:

alter session set db_create_file_dest = '/home1/teach3';
create tablespace sdgtest4 datafile size 2m;
select file_name,tablespace_name
from dba_data_files where tablespace_name = 'SDGTEST4';
FILE_NAME                                     TABLESPACE_NAME
--------------------------------------------  --------------------
/home1/teach3/ora_sdgtest4_xx5vcmqf.dbf       SDGTEST4

ALTER TABLESPACE sdgtest4 OFFLINE;

HOST ls
ora_sdgtest4_xx5vcmqf.dbf
host mv ora_sdgtest4_xx5vcmqf.dbf ora_sdgtest4_xx5vsdg4.dbf
alter tablespace sdgtest4 rename datafile
 '/home1/teach3/ora_sdgtest4_xx5vcmqf.dbf' to
'/home1/teach3/ora_sdgtest4_xx5vsdg4.dbf';

ALTER TABLESPACE sdgtest4 ONLINE;

Backup and Recovery

Backup and recovery procedures for Oracle-managed datafiles are the same as those for DBA managed datafiles. The use of Oracle imp and exp utilities are not affected by having Oracle-managed datafiles. Finally, the procedures for recovering from the loss of a control file when using backup control files or recreating the control file using the results of an alter database backup control file to trace have not changed either.

Tags: Oracle Spotlight on Oracle Wiki

Quest Software

Written by Quest Software