Shulou(Shulou.com)05/31 Report--

Editor to share with you how to use OMF in oracle, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Benefits of using OMF

Using OMF has the following benefits:

Make the database easy to manage.

There is no need to compile a file name and determine the specified storage, a consistent set of rules is used to name all related files. The file system determines the storage properties and pools to which it is allocated.

Reduce the damage caused by the error file specified by the administrator.

Each OMF and file name is unique. Using the same file in two different databases is a common error that can lead to long stops and loss of committed transactions. Executing the same file with two different names is another error and can cause significant damage.

Reduce the waste of disk space caused by expired files.

Oracle automatically deletes old OMF when they are no longer needed. A lot of disk space is wasted in a simple large system because no one knows if a particular file is still needed. For content that is no longer needed on disk, you can simplify administrative tasks and prevent files from being mistakenly deleted.

You can simplify creation tasks and database development.

You can use very little time to determine the file structure and file name, reducing file management tasks. Focus on testing and database development to meet its actual needs.

OMF makes it easier to develop with third-party tools.

OMF no longer needs to put the name specified by the operating system in the SQL script.

Implement OMF management of data files

Previously, to create a tablespace, you needed to specify the data file path and size.

SQL > create tablespace wer datafile'/ u01 size

To achieve data file OMF management, you only need to set one parameter

SQL > show parameter db_create_file_dest

NAME TYPE VALUE

-

Db_create_file_dest string

SQL >

SQL > alter system set db_create_file_dest='/u01/app/oracle/oradata/'

System altered.

SQL > create tablespace wer

Tablespace created.

SQL >

SQL > select name from v$datafile

NAME

+ DATA/fengzi/datafile/system.277.842187103

+ DATA/fengzi/datafile/undotbs1.dbf

+ DATA/fengzi/datafile/sysaux.279.842187235

+ DATA/fengzi/datafile/users2.dbf

+ DATA/fengzi/datafile/undotbs.dbf

+ DATA/fengzi/datafile/users1.dbf

/ u01/app/oracle/oradata/FENGZI/datafile/o1_mf_wer_9ns1wd2n_.dbf

7 rows selected.

SQL >

SQL > ho ls-lh / u01/app/oracle/oradata/FENGZI/datafile/o1_mf_wer_9ns1wd2n_.dbf

-rw- 1 oracle oinstall 101m Apr 15 09:12 / u01/app/oracle/oradata/FENGZI/datafile/o1_mf_wer_9ns1wd2n_.dbf

(default allocation size is 100m)

SQL >

You can also specify the size directly

SQL > create tablespace qwe datafile size 15m

SQL > ho ls-lh / u01/app/oracle/oradata/FENGZI/datafile/o1_mf_qwe_9ns25pms_.dbf

-rw- 1 oracle oinstall 16m Apr 15 09:17 / u01/app/oracle/oradata/FENGZI/datafile/o1_mf_qwe_9ns25pms_.dbf

SQL >

If you use the drop tablespace {tablespace_name}; command to delete the tablespace, the OMF management will delete the physical files as well.

SQL > drop tablespace qwe

Tablespace dropped.

SQL > ho ls-lh / u01/app/oracle/oradata/FENGZI/datafile/o1_mf_qwe_9ns25pms_.dbf

Ls: / u01/app/oracle/oradata/FENGZI/datafile/o1_mf_qwe_9ns25pms_.dbf: No such file or directory

SQL >

Other commands that use drop tablespace {tablespace_name}; do not delete physical files

SQL > create tablespace qaz datafile'/ u01 size size 10m

Tablespace created.

SQL > ho ls / u01/app/oracle/oradata/fengzi/qaz.dbf

/ u01/app/oracle/oradata/fengzi/qaz.dbf

SQL > drop tablespace qaz

Tablespace dropped.

SQL > ho ls / u01/app/oracle/oradata/fengzi/qaz.dbf

/ u01/app/oracle/oradata/fengzi/qaz.dbf

SQL >

If you want to delete, you should

Drop tablespace tablespace_name including contents and datafiles

Including contents: used to delete tablespaces while deleting all segments on tablespaces

Including contents and datafiles: delete tablespaces, data files, and upper segments of tablespaces, while deleting data files in OS

Implement OMF management of log files

Confirm the original default location of the storage group

SQL > alter database add logfile

Database altered.

SQL > select member from v$logfile

MEMBER

+ DATA/fengzi/onlinelog/group_1_1.log

+ DATA/fengzi/onlinelog/group_1_2.log

+ DATA/fengzi/onlinelog/group_2_1.log

+ DATA/fengzi/onlinelog/group_2_2.log

+ DATA/fengzi/onlinelog/group_3_1.log

+ DATA/fengzi/onlinelog/group_3_2.log

/ u01/app/oracle/oradata/FENGZI/onlinelog/o1_mf_4_9ns3nvl6_.log

+ DATA/fengzi/onlinelog/group_4.345.844940573

8 rows selected.

The member path of the above default creation group is determined by the following parameters

SQL > show parameter db_create_file_dest

NAME TYPE VALUE

-

Db_create_file_dest string / u01/app/oracle/oradata/

SQL > show parameter db_recovery_file_dest

NAME TYPE VALUE

-

Db_recovery_file_dest string + DATA

SQL >

Similarly, you only need to modify the corresponding parameters.

SQL > show parameter db_create_online

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 >

You can see that five members are given in a group, and it is generally sufficient to have two members in a group. So choose the number of parameters according to the situation.

First select the path where the log is stored

SQL > ho mkdir / u01/app/oracle/d1

SQL > ho mkdir / u01/app/oracle/d2

SQL >

Define parameters db_create_online_log_dest_1 and db_create_online_log_dest_2

SQL > alter system set db_create_online_log_dest_1='/u01/app/oracle/d1'

System altered.

SQL > alter system set db_create_online_log_dest_2='/u01/app/oracle/d2'

System altered.

SQL >

Create the log group again

SQL > alter database add logfile

Database altered.

SQL > select group#,member from v$logfile

GROUP#

-

MEMBER

one

+ DATA/fengzi/onlinelog/group_1_1.log

one

+ DATA/fengzi/onlinelog/group_1_2.log

two

+ DATA/fengzi/onlinelog/group_2_1.log

GROUP#

-

MEMBER

two

+ DATA/fengzi/onlinelog/group_2_2.log

three

+ DATA/fengzi/onlinelog/group_3_1.log

three

+ DATA/fengzi/onlinelog/group_3_2.log

GROUP#

-

MEMBER

four

/ u01/app/oracle/oradata/FENGZI/onlinelog/o1_mf_4_9ns3nvl6_.log

four

+ DATA/fengzi/onlinelog/group_4.345.844940573

five

/ u01/app/oracle/d1/FENGZI/onlinelog/o1_mf_5_9ns4dov5_.log

GROUP#

-

MEMBER

five

/ u01/app/oracle/d2/FENGZI/onlinelog/o1_mf_5_9ns4dptw_.log

10 rows selected.

SQL >

For log file OMF management, you cannot add members to the group, just like data files, OS physical files will be deleted during drop.

If you want to use the control file OMF to manage

The following parameters need to be killed

SQL > alter system reset control_files scope=spfile sid='*'

The control file is also determined according to the parameters of the log file (the storage path is the same as the log file)

SQL > show parameter db_create_online_log

NAME TYPE VALUE

-

Db_create_online_log_dest_1 string / u01/app/oracle/d1

Db_create_online_log_dest_2 string / u01/app/oracle/d2 (there are several control files if several members are defined)

Db_create_online_log_dest_3 string

Db_create_online_log_dest_4 string

Db_create_online_log_dest_5 string

SQL >

The above is all the contents of the article "how to use OMF in oracle". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.