Search This Blog

Total Pageviews

Saturday 1 March 2014

How to disable Oracle Managed Files (OMF)

How to disable Oracle Managed Files (OMF) 

Disable Oracle OMF



col Parameter format a40
col "Instance Value" format a20
select KSPPINM "Parameter",
c.ksppstvl "Instance Value"
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and a.ksppinm like '%_omf%';

Parameter                                Instance Value
---------------------------------------- --------------------
_omf                                     enabled


col name format a25
col value format a10
select x.ksppinm name, y.kspftctxvl value, y.kspftctxdf isdefault, decode(bitand(y.kspftctxvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod,
decode(bitand(y.kspftctxvf,2),2,'TRUE','FALSE') isadj from sys.x$ksppi x, sys.x$ksppcv2 y where x.inst_id = userenv('Instance')
and y.inst_id = userenv('Instance') and x.indx+1 = y.kspftctxpn
and x.ksppinm like '%omf%' ;

NAME                      VALUE      ISDEFA ISMOD      ISADJ
------------------------- ---------- ------ ---------- -----
_omf                      enabled    TRUE   FALSE      FALSE


 To disable the Oracle Managed Files (OMF) feature you have to :
- add the CONTROL_FILES parameter in the pfile/ spfile;
- remove  DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n parameter from pfile/ spfile;
- restart the database.

or
command to disable this.
alter system set "_omf" = disabled scope=spfile;
- restart the database.

Oracle DBA

anuj blog Archive