重庆思庄Oracle、Redhat认证学习论坛

标题: 运用OMF更改备库自动生成数据文件的位置 [打印本页]

作者: denglj 时间: 2022-7-8 11:07
标题: 运用OMF更改备库自动生成数据文件的位置
场景：主库增加数据文件，备库相应也会增加数据文件.
若备库原来存放数据文件的磁盘空间不够，如何更改自动增加数据文件的路径到指定目录下呢？
如下为实验过程：
备库执行
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文件.

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)