11 Using Oracle-Managed Files

Scenario 1: Create and Manage a Database with Multip lexed Redo Logs

In this scenario, a DBA creates a database where the datafile s and redo log files are created in separate directories. The redo log files and control files are multiplexed. The database uses an undo tablespace, and has a default temporary tablespace. The following are tasks involved with creating and maintaining this database .

1. Setting the initialization parameters

   The DBA includes three generic file creation defau lts in the initialization parameter file before creating the database. Automatic undo management mode is also specified.

   DB_CREATE_FILE_DEST = '/u01/oradata'
   DB_CREATE_ONLINE_LOG_DEST_1 = '/u02/oradata'
   DB_CREATE_ONLINE_LOG_DEST_2 = '/u
   03/oradata'
   UNDO_MANAGEMENT = AUTO
   
   

   The DB_CREATE_FILE_DEST parameter sets the default file system directory for the datafiles and tempfiles.

   The DB_CREATE_ONLINE_LOG_DEST_1 and DB_CREATE_ONLINE_LOG_DEST_2 par ameters set the default file system directories for redo log file and control file creation. Each redo log file and control file is m ultiplexed across the two directories.

2. Creating a database

   Once the initialization parameters are set, the da tabase can be created by using this statement:

   SQL> CREATE DATABASE sample
   2>   DEFAULT TEMPORARY
    TABLESPACE dflttmp;
   
   

   Because a DATAFILE clause is not present and the DB_CREATE_FILE_DEST initi alization parameter is set, the SYSTEM tablespace datafile is created in the default file system (/u01/oradata in this scenario). The filename is uniquely generated by the database. The file is autoextensible with an initial size of 100 MB and an unlimited maximum size. The file is an Oracle-managed file. A similar datafile is created for the SYSAUX tablespa ce.

   Because a LOGFILE clause is not present, two redo log groups are created. Each log group has two members, wit h one member in the DB_CREATE_ONLINE_LOG_DEST_1 location and the other member in the DB_CREATE_ONLINE_LOG_DEST_2 location. The filenames are uniquely generated by the database. The log files are created with a size of 100 MB. The log file m embers are Oracle-managed files.

   Similarly, because the CONTROL_FILES initialization parameter is not present, an d two DB_CREATE_ONLINE_LOG_DEST_n initialization parameters are specified, two control files are c reated. The control file located in the DB_CREATE_ONLINE_LOG_DEST_1 location is the primary control file; the control fi le located in the DB_CREATE_ONLINE_LOG_DEST_2 location is a multiplexed copy. The filenames are uniquely generated by th e database. They are Oracle-managed files. Assuming there is a server parameter file, a CONTROL_FILES initialization par ameter is generated.

   Automatic undo management mode is specified, but because an undo tablespace is not specified and the DB_CREATE_FILE_DEST initialization parameter is set, a default undo tablespace named SYS_UNDOTBS is created in the directory specified by DB_CREATE_FILE_DEST. The datafile is a 10 MB datafile that is autoextensible. It is an Oracle -managed file.

   Lastly, a default temporary tablespace named dflttmp is specified. Because DB_CREATE_FILE_DE ST is included in the parameter file, the tempfile for dflttmp is created in the directory specified by that para meter. The tempfile is 100 MB and is autoextensible with an unlimited maximum size. It is an Oracle-managed file.

   The resultan t file tree, with generated filenames, is as follows:

   /u01
        /oradata
             /SAMPLE
      /datafile
                       /o1_mf_system_cmr7t30p_.dbf
                       /o1_mf_sysaux_cmr7t88p_.dbf
                       /o1_
   mf_sys_undotbs_2ixfh90q_.dbf
                       /o1_mf_dflttmp_157se6ff_.tmp
   /u02
        /oradata
             /SAMPLE
                  /on
   linelog
                       /o1_mf_1_0orrm31z_.log
                       /o1_mf_2_2xyz16am_.log
                  /controlfile
            /o1_mf_cmr7t30p_.ctl
   /u03
        /oradata
             /SAMPLE
                  /onlinelog
                       /o1_mf_1_ixfvm8w9_.l
   og
                       /o1_mf_2_q89tmp28_.log
                  /controlfile
                       /o1_mf_x1sr8t36_.ctl
   
   

   The in ternally generated filenames can be seen when selecting from the usual views. For example:

   SQL> SELE
   CT NAME FROM V$DATAFILE;
   
   NAME
   ----------------------------------------------------
   /u01/oradata/SAMPLE/datafile/o1_mf_system_cmr7t30
   p_.dbf
   /u01/oradata/SAMPLE/datafile/o1_mf_sysaux_cmr7t88p_.dbf
   /u01/oradata/SAMPLE/datafile/o1_mf_sys_undotbs_2ixfh90q_.dbf
   
   3 rows s
   elected
   
   

   The name is also printed to the alert file when the file is created.

3. Managing control files

   The control file was created when generating the database, and a CONTROL_FILES initialization parameter was added to the parameter file. If needed, then the DBA can re-create the control file or build a new one for the database using the CREATE CONTROLFILE statement.

   The correct O racle-managed filenames must be used in the DATAFILE and LOGFILE clauses. The ALTER DATABASE BACKUP C ONTROLFILE TO TRACE statement generates a script with the correct filenames. Alternatively, the filenames can be found by sele cting from the V$DATAFILE, V$TEMPFILE, and V$LOGFILE views. The following example re-creates t he control file for the sample database:

   CREATE CONTROLFILE REUSE
     DATABASE sample
     LOGFILE 
       GROU
   P 1('/u02/oradata/SAMPLE/onlinelog/o1_mf_1_0orrm31z_.log', 
               '/u03/oradata/SAMPLE/onlinelog/o1_mf_1_ixfvm8w9_.log'), 
   GROUP 2('/u02/oradata/SAMPLE/onlinelog/o1_mf_2_2xyz16am_.log',
               '/u03/oradata/SAMPLE/onlinelog/o1_mf_2_q89tmp28_.log')
     N
   ORESETLOGS
     DATAFILE '/u01/oradata/SAMPLE/datafile/o1_mf_system_cmr7t30p_.dbf',
              '/u01/oradata/SAMPLE/datafile/o1_mf_sysau
   x_cmr7t88p_.dbf',
              '/u01/oradata/SAMPLE/datafile/o1_mf_sys_undotbs_2ixfh90q_.dbf',
              '/u01/oradata/SAMPLE/datafile
   /o1_mf_dflttmp_157se6ff_.tmp'
     MAXLOGFILES 5
     MAXLOGHISTORY 100
     MAXDATAFILES 10
     MAXINSTANCES 2
     ARCHIVELOG;
   
   

   The co ntrol file created by this statement is located as specified by the CONTROL_FILES initialization parameter that was gene rated when the database was created. The REUSE clause causes any existing files to be overwritten.

4. Mana ging the redo log

   To create a new group of redo log files, the DBA can use the ALTER DATABASE ADD LOGFILE statement. The following statement adds a log file with a member in the DB_CREATE_ONLINE_LOG_ DEST_1 location and a member in the DB_CREATE_ONLINE_LOG_DEST_2 location. These files are Oracle-managed files.

   SQL> ALTER DATABASE ADD LOGFILE;
   
   

   Log file members continue to be added and dropped by spe cifying complete filenames.

   The GROUP clause can be used to drop a log group. In the following example the operat ing system file associated with each Oracle-managed log file member is automatically deleted.

   SQL> A
   LTER DATABASE DROP LOGFILE GROUP 3;
   
   

5. Managing tablespaces

   The de fault storage for all datafiles for future tablespace creations in the sample database is the location specified by the DB_CREATE_FILE_DEST initialization parameter (/u01/oradata in this scenario). Any datafiles for which no fi lename is specified, are created in the file system specified by the initialization parameter DB_CREATE_FILE_DEST. For e xample:

   SQL> CREATE TABLESPACE tbs_1;
   
   

   The preceding statement creates a tablespace whose storage is in /u01/oradata. A datafile is created with an initial of 100 MB and it is autoextensible with an unlimited maximum size. The datafile is an Oracle-managed file.

   When the tablespace is dropped, the Oracle-managed files for the tablesp ace are automatically removed. The following statement drops the tablespace and all the Oracle-managed files used for its storage:

   SQL> DROP TABLESPACE tbs_1;
   
   

   Once the first datafile is full, the database does not automat ically create a new datafile. More space can be added to the tablespace by adding another Oracle-managed datafile. The following stat ement adds another datafile in the location specified by DB_CREATE_FILE_DEST:

   SQL> ALTE
   R TABLESPACE tbs_1 ADD DATAFILE;
   
   

   The default file system can be changed by changing the initialization parameter. This do es not change any existing datafiles. It only affects future creations. This can be done dynamically using the following statement:

   SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST='/u04/oradata';
   
   

6. Archiving redo inform ation

   Archiving of redo log files is no different for Oracle-managed files, than it is for unmanaged files. A file system loca tion for the archived log files can be specified using the LOG_ARCHIVE_DEST_n initialization param eters. The filenames are formed based on the LOG_ARCHIVE_FORMAT parameter or its default. The archived logs are not Orac le-managed files

7. Backup, restore, and recover

   Since an Oracle-managed file is compatible with standard operat ing system files, you can use operating system utilities to backup or restore Oracle-managed files. All existing methods for backing up, restoring, and recovering the database work for Oracle-managed files.

Scenario 3: Adding Oracl e-Managed Files to an Existing Database

Assume in this case that an existing database does not have any Oracle-managed files, but the DBA would like to create new tablespaces with Oracle-managed files and locate them in directory /u03/oradata.