运用OMF更改备库自动生成数据文件的位置

原创

Liujun_Deng 2022-07-08 22:54:17 博主文章分类：Oracle ©著作权

文章标签 oracle sql 数据文件 文章分类 Oracle 数据库

©著作权归作者所有：来自51CTO博客作者Liujun_Deng的原创作品，请联系作者获取转载授权，否则将追究法律责任

场景：主库增加数据文件，备库相应也会增加数据文件.

若备库原来存放数据文件的磁盘空间不够，如何更改自动增加数据文件的路径到指定目录下呢？

如下为实验过程：

备库执行

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl151/system01.dbf
/u01/app/oracle/oradata/orcl151/sysaux01.dbf
/u01/app/oracle/oradata/orcl151/undotbs01.dbf
/u01/app/oracle/oradata/orcl151/users01.dbf

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

System altered.

主库执行：

SQL> r
1* select file_name,tablespace_name,autoextensible,bytes/1024/1024 from dba_data_files order by 4

FILE_NAME TABLESPACE_NAME AUT BYTES/1024/1024
-------------------------------------------------- ------------------------------ --- ---------------
/u01/app/oracle/oradata/orcl150/users01.dbf USERS YES 5
/u01/app/oracle/oradata/orcl150/undotbs01.dbf UNDOTBS1 YES 30
/u01/app/oracle/oradata/orcl150/sysaux01.dbf SYSAUX YES 500
/u01/app/oracle/oradata/orcl150/system01.dbf SYSTEM YES 740

SQL> alter tablespace USERS add datafile '/u01/app/oracle/oradata/orcl150/users02.dbf' size 10m autoextend on;

Tablespace altered.

SQL> select file_name,tablespace_name,autoextensible,bytes/1024/1024 from dba_data_files order by 4;

FILE_NAME TABLESPACE_NAME AUT BYTES/1024/1024
-------------------------------------------------- ------------------------------ --- ---------------
/u01/app/oracle/oradata/orcl150/users01.dbf USERS YES 5
/u01/app/oracle/oradata/orcl150/users02.dbf USERS YES 10
/u01/app/oracle/oradata/orcl150/undotbs01.dbf UNDOTBS1 YES 30
/u01/app/oracle/oradata/orcl150/sysaux01.dbf SYSAUX YES 500
/u01/app/oracle/oradata/orcl150/system01.dbf SYSTEM YES 740

备库查询：

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl151/system01.dbf
/u01/app/oracle/oradata/orcl151/sysaux01.dbf
/u01/app/oracle/oradata/orcl151/undotbs01.dbf
/u01/app/oracle/oradata/orcl151/users01.dbf
/u01/app/oracle/omf/ORCL151/datafile/o1_mf_users_k8xyy393_.dbf

说明：如上所示，运用OMF管理文件的方式，备库新增的数据文件在新的路径中生成.

现关闭备库omf，主库增加users02.dbf数据文件大小

SQL> alter system set db_create_file_dest='';

System altered.

SQL> show parameter db_create_file

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string

主库执行：

SQL> alter database datafile '/u01/app/oracle/oradata/orcl150/users02.dbf' resize 20m;

Database altered.

备库查询：

SQL> r
1* select file_name,autoextensible,tablespace_name,bytes/1024/1024 from dba_data_files where tablespace_name='USERS' order by 1

FILE_NAME AUT TABLESPACE_NAME BYTES/1024/1024
---------------------------------------------------------------------- --- ------------------------------ ---------------
/u01/app/oracle/omf/ORCL151/datafile/o1_mf_users_k8xyy393_.dbf YES USERS 20
/u01/app/oracle/oradata/orcl151/users01.dbf YES USERS 5

如上所示，即使备库将OMF关闭，备库中的数据文件o1_mf_users_k8xyy393_.dbf相应也会增长到20m.

现在主库在增加users03.dbf，测试会不会按之前的omf路径生成文件

主库执行

SQL> alter tablespace users add datafile '/u01/app/oracle/oradata/orcl150/users03.dbf' size 10m autoextend on;

Tablespace altered.

备库查询：

SQL> select file_name,tablespace_name,autoextensible,bytes/1024/1024 from dba_data_files order by 4;

FILE_NAME TABLESPACE_NAME AUT BYTES/1024/1024
-------------------------------------------------------------------------------- ------------------------------ --- ---------------
/u01/app/oracle/oradata/orcl151/users01.dbf USERS YES 5
/u01/app/oracle/oradata/orcl151/users03.dbf USERS YES 10
/u01/app/oracle/omf/ORCL151/datafile/o1_mf_users_kbv814pr_.dbf USERS YES 20
/u01/app/oracle/oradata/orcl151/undotbs01.dbf UNDOTBS1 YES 30
/u01/app/oracle/oradata/orcl151/sysaux01.dbf SYSAUX YES 500
/u01/app/oracle/oradata/orcl151/system01.dbf SYSTEM YES 740

6 rows selected.

如上所示，备库关闭OMF后，主库新增的数据文件在备库按此前的路径生成users03.dbf文件.