Using Oracle Managed Files IN Oracle RAC 19c
What Is Oracle Managed Files? UsinOracle Managed Files eliminates the need for you, the DBA, to directly manage the operating system files that comprise an Oracle Database. With Oracle Managed Files, you specify file system directories in which the database automatically creates, names, and manages files at the database object level. Files Managed by OMF settings The database internally uses standard file system interfaces to create and delete files as needed for the following database structures: => Tablespaces => Redo log files => Control files => Archived logs => Block change tracking files => Flashback logs => RMAN backups Through initialization parameters, you specify the file system directory to be used for a particular type of file. The database then ensures that a unique file, an Oracle managed file, is created and deleted when no longer needed. Initialization Parameters That Enable Oracle Managed Files The following table lists the initialization parameters that enable the use of Oracle Managed Files. DB_CREATE_FILE_DEST (For data files or temp files ) Defines the location where the database creates data files or temp files when no file specification is given in the create operation. Also used as the default location for redo log and control files if DB_CREATE_ONLINE_LOG_DEST_n are not specified. DB_CREATE_ONLINE_LOG_DEST_n (For redo log files and control file ) Defines the location of for redo log files and control file creation when no file specification is given in the create operation. By changing n, you can use this initialization parameter multiple times, where n specifies a multiplexed copy of the redo log or control file. DB_RECOVERY_FILE_DEST (For RMAN backups,archived logs,archived logs) Defines the location of the Fast Recovery Area, where the database creates RMAN backups when no format option is used, archived logs when no other local destination is configured, andarchived logs. Also used as the default location for redo log and control files or multiplexed copies of redo log and control files if DB_CREATE_ONLINE_LOG_DEST_n are not specified. When this parameter is specified, the DB_RECOVERY_FILE_DEST_SIZE initialization parameter must also be specified. Scenarios of OMF 1=> Creating Data Files for Tablespaces Using Oracle Managed Files =================================================================== Oracle Database can create data files for tablespaces using Oracle Managed Files when certain conditions are met. About Creating Data Files for Tablespaces Using Oracle Managed Files When certain conditions are met, the following SQL statements can create data files for tablespaces using Oracle Managed Files: CREATE TABLESPACE, CREATE UNDO TABLESPACE, and ALTER TABLESPACE ... ADD DATAFILE. The following statements can create data files: => CREATE TABLESPACE => CREATE UNDO TABLESPACE => ALTER TABLESPACE ... ADD DATAFILE When creating a tablespace, either a permanent tablespace or an undo tablespace, the DATAFILE clause is optional. When you include the DATAFILE clause, the file name is optional. If the DATAFILE clause or file name is not provided, then the following rules apply: => If the DB_CREATE_FILE_DEST initialization parameter is specified, then an Oracle managed data file is created in the location specified by the parameter. => If the DB_CREATE_FILE_DEST initialization parameter is not specified, then the statement creating the data file fails. When you add a data file to a tablespace with the ALTER TABLESPACE...ADD DATAFILE statement the file name is optional. If the file name is not specified, then the same rules apply as discussed in the previous paragraph. By default, an Oracle managed data file for a permanent tablespace is 100 MB and is autoextensible with an unlimited maximum size. However, if in your DATAFILE clause you override these defaults by specifying a SIZE value (and no AUTOEXTEND clause), then the data file is not autoextensible. Example 1 => Create Tablespace when OMF in enabled as below. SQL> show parameter DB_CREATE_FILE_DEST NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string +DATA SQL> SQL> select file_name from dba_data_files where tablespace_name like 'OMF_T1'; no rows selected SQL> create tablespace OMF_T1 datafile size 10m autoextend on maxsize 1g; Tablespace created. SQL> select file_name from dba_data_files where tablespace_name like 'OMF_T1'; FILE_NAME -------------------------------------------------------------------------------- +DATA/NTNT/DATAFILE/omf_t1.344.1128016749 SQL> Example 2 => Create mutliple datafile with 1 command when OMF is enabled. SQL> show parameter DB_CREATE_FILE_DEST NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string +DATA SQL> SQL> SQL> select file_name from dba_data_files where tablespace_name like 'OMF_T2'; no rows selected SQL> create tablespace OMF_T2 datafile size 10m ,size 20m autoextend on maxsize 1g; Tablespace created. SQL> select file_name from dba_data_files where tablespace_name like 'OMF_T2'; FILE_NAME --------------------------------------------------------------------------------------------------------- +DATA/NTNT/DATAFILE/omf_t2.343.1128016873 +DATA/NTNT/DATAFILE/omf_t2.330.1128016873 SQL> 2=>Creating Control Files Using Oracle Managed Files ===================================================== About Creating Control Files Using Oracle Managed Files When you issue the CREATE CONTROLFILE statement, a control file is created (or reused, if REUSE is specified) in the files specified by the CONTROL_FILES initialization parameter. If the CONTROL_FILES parameter is not set, then the control file is created in the default control file destinations. The default destination is determined according to the precedence documented in "Specifying Control Files at Database Creation". If Oracle Database creates an Oracle managed control file, and there is a server parameter file, then the database creates a CONTROL_FILES initialization parameter for the server parameter file. If there is no server parameter file, then you must create a CONTROL_FILES initialization parameter manually and include it in the initialization parameter file. If the data files in the database are Oracle Managed Files, then the database-generated file names for the files must be supplied in the DATAFILE clause of the statement. If the redo log files are Oracle Managed Files, then the NORESETLOGS or RESETLOGS keyword determines what can be supplied in the LOGFILE clause: => If the NORESETLOGS keyword is used, then the database-generated file names for the Oracle managed redo log files must be supplied in the LOGFILE clause. => If the RESETLOGS keyword is used, then the redo log file names can be supplied as with the CREATE DATABASE statement. 3=>Creating Redo Log Files Using Oracle Managed Files ===================================================== Redo log files are created at database creation time. They can also be created when you issue either of the following statements:ALTER DATABASE ADD LOGFILEandALTER DATABASE OPEN RESETLOGS. Using the ALTER DATABASE ADD LOGFILE Statement The ALTER DATABASE ADD LOGFILE statement lets you later add a new group to your current redo log. The file name in the ADD LOGFILE clause is optional if you are using Oracle Managed Files. If a file name is not provided, then a redo log file is created in the default log file destination. If a file name is not provided and you have not provided one of the initialization parameters required for creating Oracle Managed Files, then the statement returns an error. The default size for an Oracle managed log file is 100 MB. You continue to add and drop redo log file members by specifying complete file names. Specifying Redo Log Files at Database Creation The LOGFILE clause is not required in the CREATE DATABASE statement, and omitting it provides a simple means of creating Oracle managed redo log files. If the LOGFILE clause is omitted, then redo log files are created in the default redo log file destinations. In order of precedence, the default destination is defined as follows: => If either the DB_CREATE_ONLINE_LOG_DEST_n is set, then the database creates a log file member in each directory specified, up to the value of the MAXLOGMEMBERS initialization parameter. => If the DB_CREATE_ONLINE_LOG_DEST_n parameter is not set, but both the DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST initialization parameters are set, then the database creates one Oracle managed log file member in each of those locations. The log file in the DB_CREATE_FILE_DEST destination is the first member. => If only the DB_CREATE_FILE_DEST initialization parameter is specified, then the database creates a log file member in that location. => If only the DB_RECOVERY_FILE_DEST initialization parameter is specified, then the database creates a log file member in that location. The default size of an Oracle managed redo log file is 100 MB. Optionally, you can create Oracle managed redo log files, and override default attributes, by including the LOGFILE clause but omitting a file name. Redo log files are created the same way, except for the following: If no file name is provided in the LOGFILE clause of CREATE DATABASE, and none of the initialization parameters required for creating Oracle Managed Files are provided, then the CREATE DATABASE statement fails. Example SQL> show parameter db_Create_file_Dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string +DATA SQL> show parameter db_recovery_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string +FRA db_recovery_file_dest_size big integer 8256M SQL> show parameter db_create_online_log NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_online_log_dest_1 string db_create_online_log_dest_2 string db_create_online_log_dest_3 string db_create_online_log_dest_4 string db_create_online_log_dest_5 string SQL> SQL> SQL> select * from v$logfile where TYPE='ONLINE'; GROUP# STATUS TYPE MEMBER IS_ CON_ID ---------- ------- ------- ------------------------------------------------------------ --- ---------- 2 ONLINE +DATA/NTNT/ONLINELOG/group_2.272.1123698529 NO 0 2 ONLINE +FRA/NTNT/ONLINELOG/group_2.789.1123698531 YES 0 1 ONLINE +DATA/NTNT/ONLINELOG/group_1.280.1123698529 NO 0 1 ONLINE +FRA/NTNT/ONLINELOG/group_1.783.1123698531 YES 0 3 ONLINE +DATA/NTNT/ONLINELOG/group_3.279.1123699801 NO 0 3 ONLINE +FRA/NTNT/ONLINELOG/group_3.821.1123699803 YES 0 4 ONLINE +DATA/NTNT/ONLINELOG/group_4.278.1123699813 NO 0 4 ONLINE +FRA/NTNT/ONLINELOG/group_4.851.1123699813 YES 0 16 ONLINE +DATA/NTNT/ONLINELOG/group_16.346.1128014965 NO 0 16 ONLINE +FRA/NTNT/ONLINELOG/group_16.774.1128014965 YES 0 SQL> ALTER DATABASE ADD LOGFILE; Database altered. SQL> select * from v$logfile where TYPE='ONLINE'; GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE CON_ID ---------- ------- ------- ------------------------------------------------------------ ------------------------- ---------- 2 ONLINE +DATA/NTNT/ONLINELOG/group_2.272.1123698529 NO 0 2 ONLINE +FRA/NTNT/ONLINELOG/group_2.789.1123698531 YES 0 1 ONLINE +DATA/NTNT/ONLINELOG/group_1.280.1123698529 NO 0 1 ONLINE +FRA/NTNT/ONLINELOG/group_1.783.1123698531 YES 0 3 ONLINE +DATA/NTNT/ONLINELOG/group_3.279.1123699801 NO 0 3 ONLINE +FRA/NTNT/ONLINELOG/group_3.821.1123699803 YES 0 4 ONLINE +DATA/NTNT/ONLINELOG/group_4.278.1123699813 NO 0 4 ONLINE +FRA/NTNT/ONLINELOG/group_4.851.1123699813 YES 0 16 ONLINE +DATA/NTNT/ONLINELOG/group_16.346.1128014965 NO 0 16 ONLINE +FRA/NTNT/ONLINELOG/group_16.774.1128014965 YES 0 17 ONLINE +DATA/NTNT/ONLINELOG/group_17.329.1128017361 NO 0 17 ONLINE +FRA/NTNT/ONLINELOG/group_17.856.1128017361 YES 0 SQL> 4=>Creating Temp Files for Temporary Tablespaces Using Oracle Managed Files ============================================================================ Oracle Database can create temp files for temporary tablespaces using Oracle Managed Files when certain conditions are met. About Creating Temp Files for Temporary Tablespaces Using Oracle Managed Files When certain conditions are met, the following SQL statements can create temp files for tablespaces using Oracle Managed Files: CREATE TEMPORARY TABLESPACE and ALTER TABLESPACE ... ADD TEMPFILE. The following statements that create temp files are relevant to the discussion in this section: => CREATE TEMPORARY TABLESPACE => ALTER TABLESPACE ... ADD TEMPFILE When creating a temporary tablespace the TEMPFILE clause is optional. If you include the TEMPFILE clause, then the file name is optional. If the TEMPFILE clause or file name is not provided, then the following rules apply: => If the DB_CREATE_FILE_DEST initialization parameter is specified, then an Oracle managed temp file is created in the location specified by the parameter. => If the DB_CREATE_FILE_DEST initialization parameter is not specified, then the statement creating the temp file fails. When you add a temp file to a tablespace with the ALTER TABLESPACE...ADD TEMPFILE statement the file name is optional. If the file name is not specified, then the same rules apply as discussed in the previous paragraph. When overriding the default attributes of an Oracle managed file, if a SIZE value is specified but no AUTOEXTEND clause is specified, then the data file is not autoextensible. Example => SQL> select file_name ,tablespace_name from dba_temp_files; FILE_NAME TABLESPACE_NAME ------------------------------------------------------------------------- +DATA/NTNT/TEMPFILE/temp.263.1123698611 TEMP SQL> show parameter db_create_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string +DATA SQL> SQL> create temporary tablespace TEMP_OMF ; Tablespace created. SQL> select file_name ,tablespace_name from dba_temp_files; FILE_NAME TABLESPACE_NAME ------------------------------ +DATA/NTNT/TEMPFILE/temp.263.1123698611 TEMP +DATA/NTNT/TEMPFILE/temp_omf.328.1128018063 TEMP_OMF SQL>
© 2021 Ace2Oracle. All Rights Reserved | Developed By IBOX444
