更改备库自动生成数据文件路径

数据库：oracle 11.2.0.4

系统：CentOS 7.9

目标：修改db_file_name_convert配置参数，将主库增加的数据文件引导到备库其他目录位置.

应用场景：备库存放数据文件的目录空间不足，若主库再增加数据文件便会将备库原目录路径撑满，

此时就需要将备库自动生成的数据文件引导到别的目录.

以下为实际演练操作：

查看主库数据文件信息

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/users03.dbf        USERS                          YES              10

/u01/app/oracle/oradata/orcl150/users02.dbf        USERS                          YES              25

/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

6 rows selected.

备库修改db_file_name_convert参数

修改前如下：

*.db_file_name_convert='/u01/app/oracle/oradata/orcl150/','/u01/app/oracle/oradata/orcl151/'

修改参数

SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/orcl150/','/u01/app/orcl151/' scope=spfile;      

System altered.

因db_file_name_convert为静态参数，修改后需重启数据库生效.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 2455228416 bytes

Fixed Size                  2255712 bytes

Variable Size             620758176 bytes

Database Buffers         1811939328 bytes

Redo Buffers               20275200 bytes

Database mounted.

Database opened.

修改后参数

*.db_file_name_convert='/u01/app/oracle/oradata/orcl150/','/u01/app/orcl151/'

SQL> show parameter db_file_name_convert

NAME                                 TYPE        VALUE

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

db_file_name_convert                 string      /u01/app/oracle/oradata/orcl15

                                                                0/, /u01/app/orcl151/

验证：

主库添加数据文件

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

Tablespace altered.

备库查询

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

FILE_NAME                                                              AUT TABLESPACE_NAME                BYTES/1024/1024

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

/u01/app/oracle/oradata/orcl151/users01.dbf                            YES USERS                                        5

/u01/app/orcl151/users04.dbf                                           YES USERS                                        5

/u01/app/oracle/oradata/orcl151/users03.dbf                            YES USERS                                       10

/u01/app/oracle/omf/ORCL151/datafile/o1_mf_users_kbv814pr_.dbf         YES USERS                                       25

/u01/app/oracle/oradata/orcl151/undotbs01.dbf                          YES UNDOTBS1                                    30

/u01/app/oracle/oradata/orcl151/sysaux01.dbf                           YES SYSAUX                                     500