• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to convert NON-OMF to OMF files ( Oracle Managed File conversion – NON-ASM )

by admin

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:

  1. The default creation location.
  2. 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.
  3. 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

Filed Under: oracle

Some more articles you might also be interested in …

  1. ORA-00257:Archiver Error, Connect Internal Only Until Freed
  2. What are Oracle Database Valut Schemas
  3. How to Disable Oracle Net Tracing without stopping server process
  4. How to find daily and hourly archive log generation in Oracle Database
  5. How an SQL query is executed in Oracle Database
  6. Oracle 12c New Feature – Multi-Threaded architecture of processes
  7. How to Configure Device File owner/group with udev rules
  8. Oracle Database Server Architecture: Overview
  9. How to get datafile information when PDB is Mounted/Read-Write/Restricted Mode
  10. How to Modify an Existing ASM Spfile in a RAC Environment

You May Also Like

Primary Sidebar

Recent Posts

  • diffstat: Create a histogram from the output of the diff command
  • diffoscope: Compare files, archives, and directories
  • diff-pdf: Tool for comparing two PDFs
  • dict: Command line dictionary using the DICT protocol

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright