1. Who Can Use Oracle Managed Files?
Oracle Managed Files are most useful for the following types of databases:
- A logical volume manager that supports striping/RAID and dynamically extensible logical volumes.
- A file system that provides large, extensible files Low end or test databases.
Initialization Parameters That Enable Oracle Managed Files for Datafiles, Redo and Controlfiles:
DB_CREATE_FILE_DEST DB_CREATE_ONLINE_LOG_DEST_n DB_RECOVERY_FILE_DEST
The name that is used for creation of an Oracle managed file is constructed from three sources:
- The default creation location.
- A file name template that is chosen based on the type of the file. The template also depends on the operating system platform and whether or not Oracle Automatic Storage Management is used.
- A unique string created by Oracle Database or the operating system. This ensures that file creation does not damage an existing file and that the file cannot be mistaken for some other file.
As a specific example, filenames for Oracle Managed Files have the following format on a Solaris file system:
destination_prefix/o1_mf_%t_%u_.dbf
where,
destination_prefix is destination_location/db_unique_name/datafile
destination_location is the location specified in DB_CREATE_FILE_DEST
db_unique_name is the globally unique name (DB_UNIQUE_NAME initialization parameter) of the target database. If there is no DB_UNIQUE_NAME parameter, then the DB_NAME initialization parameter value is used.
%t is the tablespace name.
%u is an eight-character string that guarantees uniqueness
For example, assume the following parameter settings:
DB_CREATE_FILE_DEST = /u01/app/oracle/oradata DB_UNIQUE_NAME = PAYROLL
Then an example data file name would be:
/u01/app/oracle/oradata/PAYROLL/datafile/o1_mf_tbs1_2ixh90q_.dbf
2. How to convert NON-OMF Datafiles to OMF FILES
a. Steps to Convert:
SQL> select name from v$datafile; NAME ----------------------------------------------------------------------- /grdbms/64bit/app/oracle/oradata/sc12Sb/system01.dbf /grdbms/64bit/app/oracle/oradata/sc12Sb/sysaux01.dbf /grdbms/64bit/app/oracle/oradata/sc12Sb/undotbs01.dbf /grdbms/64bit/app/oracle/oradata/sc12Sb/users01.dbf
SQL> create tablespace test datafile '/grdbms/64bit/app/oracle/oradata/sc12Sb/test01.dbf' size 100m; Tablespace created. SQL> create table dd as select * from dba_objects; Table created. SQL> commit; Commit complete.
SQL> alter table dd move tablespace test; Table altered. SQL> select count(*) from dba_objects; COUNT(*) ---------- 90898 SQL> select count(*) from dd; COUNT(*) ---------- 90898 SQL> select table_name,tablespace_name from dba_tables where table_name='DD'; TABLE_NAME TABLESPACE_NAME --------------- ---------------------------- DD TEST
SQL> show parameter db_create_file_dest NAME TYPE VALUE ------------------------------ db_create_file_dest string SQL> Alter system set db_create_file_dest='/tmp/OMF' scope=both; System altered. SQL> show parameter db_create_file_dest NAME TYPE VALUE ------------------------------ ----------- ------------------------------ db_create_file_dest string /tmp/OMF
SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /grdbms/64bit/app/oracle/oradata/sc12Sb/system01.dbf /grdbms/64bit/app/oracle/oradata/sc12Sb/sysaux01.dbf /grdbms/64bit/app/oracle/oradata/sc12Sb/undotbs01.dbf /grdbms/64bit/app/oracle/oradata/sc12Sb/test01.dbf /grdbms/64bit/app/oracle/oradata/sc12Sb/users01.dbf SQL> alter database move datafile 1; Database altered. SQL> alter database move datafile 3; Database altered. SQL> alter database move datafile 4; Database altered. SQL> alter database move datafile 6; Database altered. SQL> alter database move datafile 5; Database altered. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /tmp/OMF/SC12SB/datafile/o1_mf_system_cxh44rx5_.dbf /tmp/OMF/SC12SB/datafile/o1_mf_sysaux_cxh475v6_.dbf /tmp/OMF/SC12SB/datafile/o1_mf_undotbs1_cxh490bz_.dbf /tmp/OMF/SC12SB/datafile/o1_mf_test_cxh49m11_.dbf /tmp/OMF/SC12SB/datafile/o1_mf_users_cxh49fqz_.dbf
b) Now Check With RMAN for recently converted Files:
RMAN> backup database plus archivelog; Starting backup at 13-SEP-16 current log archived using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=248 device type=DISK channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=2 RECID=1 STAMP=922427023 channel ORA_DISK_1: starting piece 1 at 13-SEP-16 channel ORA_DISK_1: finished piece 1 at 13-SEP-16 piece handle=/grdbms/64bit/app/oracle/product/c12102/dbs/01rfm7kf_1_1 tag=TAG20160913T054343 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 13-SEP-16 Starting backup at 13-SEP-16 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/tmp/OMF/SC12SB/datafile/o1_mf_system_cxh44rx5_.dbf input datafile file number=00003 name=/tmp/OMF/SC12SB/datafile/o1_mf_sysaux_cxh475v6_.dbf input datafile file number=00005 name=/tmp/OMF/SC12SB/datafile/o1_mf_test_cxh49m11_.dbf input datafile file number=00004 name=/tmp/OMF/SC12SB/datafile/o1_mf_undotbs1_cxh490bz_.dbf input datafile file number=00006 name=/tmp/OMF/SC12SB/datafile/o1_mf_users_cxh49fqz_.dbf channel ORA_DISK_1: starting piece 1 at 13-SEP-16 channel ORA_DISK_1: finished piece 1 at 13-SEP-16 piece handle=/grdbms/64bit/app/oracle/product/c12102/dbs/02rfm7kh_1_1 tag=TAG20160913T054345 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 13-SEP-16 channel ORA_DISK_1: finished piece 1 at 13-SEP-16 piece handle=/grdbms/64bit/app/oracle/product/c12102/dbs/03rfm7l0_1_1 tag=TAG20160913T054345 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 13-SEP-16 Starting backup at 13-SEP-16 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=3 RECID=2 STAMP=922427042 channel ORA_DISK_1: starting piece 1 at 13-SEP-16 channel ORA_DISK_1: finished piece 1 at 13-SEP-16 piece handle=/grdbms/64bit/app/oracle/product/c12102/dbs/04rfm7l2_1_1 tag=TAG20160913T054402 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 13-SEP-16
RMAN> list backup; List of Backup Sets =================== BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 1 20.96M DISK 00:00:01 13-SEP-16 BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20160913T054343 Piece Name: /grdbms/64bit/app/oracle/product/c12102/dbs/01rfm7kf_1_1 List of Archived Logs in backup set 1 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 2 1698537 13-SEP-16 1705596 13-SEP-16 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 2 Full 1.10G DISK 00:00:02 13-SEP-16 BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20160913T054345 Piece Name: /grdbms/64bit/app/oracle/product/c12102/dbs/02rfm7kh_1_1 List of Datafiles in backup set 2 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 1705609 13-SEP-16 /tmp/OMF/SC12SB/datafile/o1_mf_system_cxh44rx5_.dbf 3 Full 1705609 13-SEP-16 /tmp/OMF/SC12SB/datafile/o1_mf_sysaux_cxh475v6_.dbf 4 Full 1705609 13-SEP-16 /tmp/OMF/SC12SB/datafile/o1_mf_undotbs1_cxh490bz_.dbf 5 Full 1705609 13-SEP-16 /tmp/OMF/SC12SB/datafile/o1_mf_test_cxh49m11_.dbf 6 Full 1705609 13-SEP-16 /tmp/OMF/SC12SB/datafile/o1_mf_users_cxh49fqz_.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 3 Full 10.11M DISK 00:00:01 13-SEP-16 BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20160913T054345 Piece Name: /grdbms/64bit/app/oracle/product/c12102/dbs/03rfm7l0_1_1 SPFILE Included: Modification time: 13-SEP-16 SPFILE db_unique_name: SC12SB Control File Included: Ckp SCN: 1705614 Ckp time: 13-SEP-16 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 4 7.00K DISK 00:00:00 13-SEP-16 BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20160913T054402 Piece Name: /grdbms/64bit/app/oracle/product/c12102/dbs/04rfm7l2_1_1 List of Archived Logs in backup set 4 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 3 1705596 13-SEP-16 1705619 13-SEP-16 RMAN> exit
c) Add New Files using OMF method and check:
SQL> alter tablespace test add datafile; Tablespace altered.
All Files converted into OMF Files and recorded in controlfile:
SQL> select file#,name,status from v$datafile; FILE# NAME STATUS ---------- ------------------------------------------------------------ ------- 1 /tmp/OMF/SC12SB/datafile/o1_mf_system_cxh44rx5_.dbf SYSTEM 2 /tmp/OMF/SC12SB/datafile/o1_mf_test_cxh4rofn_.dbf ONLINE 3 /tmp/OMF/SC12SB/datafile/o1_mf_sysaux_cxh475v6_.dbf ONLINE 4 /tmp/OMF/SC12SB/datafile/o1_mf_undotbs1_cxh490bz_.dbf ONLINE 5 /tmp/OMF/SC12SB/datafile/o1_mf_test_cxh49m11_.dbf ONLINE 6 /tmp/OMF/SC12SB/datafile/o1_mf_users_cxh49fqz_.dbf ONLINE 6 rows selected. SQL> select count(*) from dd; COUNT(*) ---------- 90898 SQL> select table_name,tablespace_name from dba_tables where table_name='DD'; TABLE_NAME TABLESPACE_NAME --------------- ------------------------------ DD TEST
3. How to convert Non-OMF REDO into OMF Files
SQL> select GROUP#,STATUS,MEMBER from v$logfile; GROUP# STATUS MEMBER ---------- ------- ------------------------------------------------------------ 3 /grdbms/64bit/app/oracle/oradata/sc12Sb/redo03.log 2 /grdbms/64bit/app/oracle/oradata/sc12Sb/redo02.log 1 /grdbms/64bit/app/oracle/oradata/sc12Sb/redo01.log SQL> alter system set DB_CREATE_ONLINE_LOG_DEST_1='/tmp' scope=both; System altered. SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG; GROUP# ARC STATUS ---------- --- ---------------- 1 YES ACTIVE 2 NO CURRENT 3 YES ACTIVE
a) Adding New Redo ( OMF FILES) Before Dropping OLD (NON-OMF files):
SQL> ALTER DATABASE ADD LOGFILE GROUP 11 SIZE 100M; Database altered. SQL> select GROUP#,STATUS,MEMBER from v$logfile; GROUP# STATUS MEMBER ---------- ------- ------------------------------------------------------------ 3 /grdbms/64bit/app/oracle/oradata/sc12Sb/redo03.log 2 /grdbms/64bit/app/oracle/oradata/sc12Sb/redo02.log 1 /grdbms/64bit/app/oracle/oradata/sc12Sb/redo01.log 11 /tmp/SC12SB/onlinelog/o1_mf_11_cxh687tt_.log =========> New group added, with OMF format SQL> alter system switch logfile; System altered. SQL> ALTER DATABASE ADD LOGFILE GROUP 12 SIZE 100M; Database altered. SQL> ALTER DATA`BASE ADD LOGFILE GROUP 13 size 10m; Database altered. SQL> ALTER DATABASE ADD LOGFILE GROUP 14 size 50m; Database altered. SQL> ALTER DATABASE DROP LOGFILE GROUP 1; Database altered.
b) If you get any Errors due to Drop redo check if its Current Redo log if so make some few Switch and drop them:
SQL> ALTER DATABASE DROP LOGFILE GROUP 2; ALTER DATABASE DROP LOGFILE GROUP 2 * ERROR at line 1: ORA-01624: log 2 needed for crash recovery of instance sc12Sb (thread 1) ORA-00312: online log 2 thread 1: '/grdbms/64bit/app/oracle/oradata/sc12Sb/redo02.log'
c) Switch Logfile to change the status of the file from “CURRENT”:
SQL> alter system switch logfile; System altered. SQL> ALTER DATABASE DROP LOGFILE GROUP 3; Database altered. SQL> ALTER DATABASE DROP LOGFILE GROUP 4; Database altered. SQL> select GROUP#,STATUS,MEMBER from v$logfile; GROUP# STATUS MEMBER ---------- ------- ------------------------------------------------------------ 12 /tmp/SC12SB/onlinelog/o1_mf_12_cxh6kb1r_.log 11 /tmp/SC12SB/onlinelog/o1_mf_11_cxh687tt_.log 13 /tmp/SC12SB/onlinelog/o1_mf_13_cxh6ns1q_.log 14 /tmp/SC12SB/onlinelog/o1_mf_14_cxh6o20c_.log