dataguard修改备库文件路径的方法

环境:

OS:Centos 7

DB:19.3.0.0

 

因为db_file_name_convert 参数设置错误导致主库创建表空间的文件传输到备库存放到其他目录，需要进行手工进行调整

原参数:

db_file_name_convert='slavea','slnngk'

正确的参数(我这里主库的数据文件实例目录是大写)

db_file_name_convert='slavea','SLNNGK'

 

1.修改standby_file_management参数
alter system set standby_file_management='manual' scope=both;;
同时修改如下参数
alter system set db_file_name_convert='slavea','SLNNGK' scope=spfile;
alter system set log_file_name_convert='slavea','SLNNGK' scope=spfile;

2.备库启动到mount状态
shutdown immediate
startup mount

 

3.拷贝数据文件到目标路径下
su - oracle
mv /u01/app/oracle/oradata/slnngk/tps_hxl0101.dbf /u01/app/oracle/oradata/SLNNGK/

 

4.进行rename
alter database rename file '/u01/app/oracle/oradata/slnngk/tps_hxl0101.dbf' to '/u01/app/oracle/oradata/SLNNGK/tps_hxl0101.dbf';

 

5.迁移部分数据文件后可打开数据库
alter database open;

 

6.查看数据文件
Set linesize 1000;
Column file_name format a64;
Column file_id format 99;
Column tablespace_name format a10;
Select file_name,file_id,tablespace_name From dba_data_files;

SQL> Select file_name,file_id,tablespace_name From dba_data_files;

FILE_NAME                                                        FILE_ID TABLESPACE
---------------------------------------------------------------- ------- ----------
/u01/app/oracle/oradata/SLNNGK/system01.dbf                            1 SYSTEM
/u01/app/oracle/oradata/SLNNGK/sysaux01.dbf                            3 SYSAUX
/u01/app/oracle/oradata/SLNNGK/tps_hxl01.dbf                           5 TPS_HXL
/u01/app/oracle/oradata/SLNNGK/users01.dbf                             7 USERS
/u01/app/oracle/oradata/SLNNGK/tps_hxl0101.dbf                         2 TPS_HXL01
/u01/app/oracle/oradata/SLNNGK/undotbs01.dbf                           4 UNDOTBS1
/u01/app/oracle/oradata/SLNNGK/tps_hxl0201.dbf                         8 TPS_HXL02

 

7.修改standby_file_management参数
alter system set standby_file_management='AUTO' scope=both;

 

8.重新应用日志
SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.