Oracle selection under Wrong file name

When creating tablespaces and files, whether the directory exists and whether the space is satisfied is a concern. To ensure the consistency of system data storage policies and avoid large rework actions, this process must be considered.

In many cases, operations that are not cautious may cause great impacts and problems. For example, if we trust the system deployment documents and do not perform analysis, it will cause many problems to our system. However, an active role of the problem is to help us learn more and understand the features and functions of the system.

Oracle OMF Functions

Oracle tablespace creation and deletion, omf, partition table Creation

Oracle OMF features from parameter values

This article describes an Oracle feature discovered by the creation of an error file.

1. Problem Description

During the inspection, the following records appear in the database logs:

Tue May 13 18:08:58 2014

Create tablespace mssite

Datafile 'd: \ APP \ XXX \ ORADATA \ ORCL \ msite. dbf'

Size 10240 m

Autoextend on next 10 m maxsize 20480 m

Tue May 13 18:09:56 2014

Completed: create tablespace mssite

Datafile 'd: \ APP \ XXX \ ORADATA \ ORCL \ mssite. dbf'

Size 10240 m

Autoextend on next 10 m maxsize 20480 m

The current system is 10gR2 and the version is 10.2.0.1. The OMF feature of the system is enabled.

SQL> select * from v $ version;

BANNER

---------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bi

PL/SQL Release 10.2.0.1.0-Production

CORE 10.2.0.1.0 Production

TNS for Linux: Version 10.2.0.1.0-Production

NLSRTL Version 10.2.0.1.0-Production

SQL> show parameter db_create

NAME TYPE VALUE

-----------------------------------------------------------------------------

Db_create_file_dest string/u01/app/oracle/oradata

Db_create_online_log_dest_1 string

In Linux, how does one create the "C: \ xx" directory? It is important that the Oracle statements are executed normally and no error is reported. The new file name is as follows:

SQL> select file_name, tablespace_name from dba_data_files;

FILE_NAME TABLESPACE_NAME

--------------------------------------------------------------------------------------------------------------

(Space reasons, omitted ......)

/U01/app/oracle/oradata/DB/datafile/o1_mf_testtbl_9q1h2gq1 _. dbf TESTTBL

/U01/app/oracle/product/10.2.0/db_1/dbs/D: APPXXXORADATAORCLmssite. DBF MSSITE

6 rows selected

From the dba_data_files view, we can see that all other data files comply with the Oracle OMF features and create files under the db_file_create_dest directory. The specified Error Path file is placed in the $ ORACLE_HOME/dbs directory and named by path compression (remove \ characters.

So, is this a feature of Oracle? We will prove it through experiments.

2. Test the experiment

We chose to conduct an experiment on 11gR2, and the two have little difference in OMF.

SQL> show parameter db_create;

NAME TYPE VALUE

-----------------------------------------------------------------------------

Db_create_file_dest string/u01/app/oradata

Db_create_online_log_dest_1 string

Db_create_online_log_dest_2 string

Create the tablespace of the wrong directory file.

SQL> create tablespace test datafile 'd: \ test. dbf' size 10 m;

Tablespace created

SQL> select file_name, tablespace_name from dba_data_files;

FILE_NAME TABLESPACE_NAME

--------------------------------------------------------------------------------------------------------------

(Space reasons, omitted ......)

/U01/app/oracle/dbs/d: test. dbf TEST

/U01/app/oradata/ORA11G/datafile/o1_mf_system_9ppkjccb _. dbf SYSTEM

/U01/app/oradata/ORA11G/datafile/o1_mf_users_9pplqldd _. dbf USERS

7 rows selected

The same phenomenon occurs. The data file is created in $ ORACLE_HOME/dbs, and the file name is set to the compressed version of the wrong directory structure. Can I create the same file?

SQL> create tablespace test1 datafile 'd: \ test. dbf' size 10 m;

Create tablespace test1 datafile 'd: \ test. dbf' size 10 m

ORA-01537: unable to add file 'd: \ test. dbf '-this file is part of the database

SQL> create tablespace test1 datafile 'd: \ test1.dbf 'size 10 m;

Tablespace created

For the OMF feature, do not write the file name information in datafile. Therefore, the current phenomenon is not the result of OMF. In addition, $ ORACLE_HOME/dbs is a very old and important folder where password files and parameter files reside. During the backup process, this directory often acts as the backup control file.

Therefore, this is an emergency response when an invalid file name occurs in Oracle. If the directory structure is invalid, Oracle will try to convert it to a file name that complies with a single file standard and store it in $ ORACLE_HOME/dbs.

When we delete these files, The OMF feature is not enabled. Therefore, you need to manually delete or add the including clause.

SQL> drop tablespace test;

Tablespace dropped

SQL> drop tablespace test1 including contents and datafiles;

Tablespace dropped

[Root @ SimpleLinux dbs] # ls-l

Total 19788

-Rw-r -----. 1 oracle oinstall 10493952 May 14 d: test. dbf

-Rw ----. 1 oracle oinstall 1544 May 14 hc_ora11g.dat

-Rw-r --. 1 oracle oinstall 2851 May 15 2009 init. ora

-Rw-r -----. 1 oracle oinstall 24 Apr 1 :39 lkORA11G

-Rw-r -----. 1 oracle oinstall 1536 Apr 3 orapwora11g

-Rw-r -----. 1 oracle oinstall 9748480 May 9 snapcf_ora11g.f

-Rw-r -----. 1 oracle oinstall 3584 May 14 spfileora11g. ora

3. Conclusion

In this process, we found the laws during the creation of Oracle non-OMF files. If the file name is invalid, Oracle processes the invalid file name (including the Directory) as a valid file name and stores it in $ ORACLE_HOME/dbs. This process will not report errors.

However, from a management perspective, we do not recommend this approach. First, this directory is a software-level public directory. If there are multiple instance databases, this folder is dangerous. In addition, this method does not meet the management policy required by Oracle OFA.

Due to this feature, I think it is better not to use it. A deployment O & M personnel confirms that basic capabilities such as file directories are still necessary.