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

标题: db_file_name_convert 修改备库自动生成数据文件路径 [打印本页]

作者: denglj 时间: 2022-6-22 13:26
标题: db_file_name_convert 修改备库自动生成数据文件路径
数据库：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
/u01/app/oracle/oradata/orcl151/system01.dbf                         YES SYSTEM                                  740

7 rows selected.

如上所示，/u01/app/orcl151/users04.dbf为备库新增数据文件，路径为db_file_name_convert修改后的目录路径.

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