模拟增加数据文件
查看数据库状态
--主库信息 switchover显示to standby或者session active为正常
set linesize 999
select name,open_mode,database_role,protection_mode,switchover_status from v$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
FUWA READ WRITE PRIMARY MAXIMUM PERFORMANCE TO STANDBY
--备库信息
set linesize 999
select name,open_mode,database_role,protection_mode,switchover_status from v$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
FUWA READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE NOT ALLOWED
查看主备库参数
--查看主库参数
sys@FUWA 2023-11-30 10:57:09> show parameter standby
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string ?/dbs/arch
standby_file_management string AUTO
--查看备库参数
sys@FUWA 2023-11-30 10:57:09> show parameter standby
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string ?/dbs/arch
standby_file_management string AUTO
主库模拟增加数据文件
idle 30-NOV-23> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
USERS /data/oradata/wafu/users01.dbf
UNDOTBS1 /data/oradata/wafu/undotbs01.dbf
SYSAUX /data/oradata/wafu/sysaux01.dbf
SYSTEM /data/oradata/wafu/system01.dbf
--增加数据文件
alter tablespace users add datafile '/data/oradata/wafu/users02.dbf' size 10M;
--切日志
alter system switch logfile;
问题描述
DG库主库创建数据文件传送到备库,同步的数据文件路径和其他的数据文件路径不一致
查看日志:
Thu Nov 30 11:03:09 2023
Media Recovery Log /data/oradata/archivelog/1_81_1147204804.dbf
Successfully added datafile 5 to media recovery
Datafile #5: '/data/oradata/FUWA/datafile/o1_mf_users_lphyvf37_.dbf'
Media Recovery Waiting for thread 1 sequence 82 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 82 Reading mem 0
Mem# 0: /data/oradata/fuwa/redo04.log
分析过程
--查看备库参数
sys@FUWA 2023-11-30 10:57:09> show parameter standby
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string ?/dbs/arch
standby_file_management string AUTO
--查看备库数据文件
sys@FUWA 2023-11-30 11:03:30> select FILE#,name,CREATION_TIME from v$datafile;
FILE# NAME CREATION_TIME
---------- -------------------------------------------------------------------------------- -------------------
1 /data/oradata/fuwa/system01.dbf 2013-08-24 11:37:33
2 /data/oradata/fuwa/sysaux01.dbf 2013-08-24 11:37:37
3 /data/oradata/fuwa/undotbs01.dbf 2013-08-24 12:07:19
4 /data/oradata/fuwa/users01.dbf 2013-08-24 11:37:49
5 /data/oradata/FUWA/datafile/o1_mf_users_lphyvf37_.dbf 2023-11-30 11:01:53
解决办法
--关闭备库
sys@FUWA 2023-11-30 13:15:13> shutdown immediate
--操作系统上重命名
[oracle@db01:/home/oracle]$ mv /data/oradata/FUWA/datafile/o1_mf_users_lphyvf37_.dbf /data/oradata/fuwa/users02.dbf
[root@db01:/data/oradata]$ ls -l /data/oradata/fuwa
total 2469540
-rw-r----- 1 oracle oinstall 10043392 Nov 30 13:27 control01.ctl
-rw-r----- 1 oracle oinstall 104858112 Nov 30 10:23 redo01.log
-rw-r----- 1 oracle oinstall 104858112 Nov 30 10:29 redo02.log
-rw-r----- 1 oracle oinstall 104858112 Nov 30 10:24 redo03.log
-rw-r----- 1 oracle oinstall 104858112 Nov 30 13:27 redo04.log
-rw-r----- 1 oracle oinstall 104858112 Nov 30 13:16 redo05.log
-rw-r----- 1 oracle oinstall 104858112 Nov 30 10:39 redo06.log
-rw-r----- 1 oracle oinstall 104858112 Nov 30 10:39 redo07.log
-rw-r----- 1 oracle oinstall 104858112 Nov 30 10:29 redo08.log
-rw-r----- 1 oracle oinstall 104858112 Nov 30 10:45 redo09.log
-rw-r----- 1 oracle oinstall 555753472 Nov 30 13:22 sysaux01.dbf
-rw-r----- 1 oracle oinstall 775954432 Nov 30 13:22 system01.dbf
-rw-r----- 1 oracle oinstall 62922752 Sep 12 08:51 temp01.dbf
-rw-r----- 1 oracle oinstall 78651392 Nov 30 13:22 undotbs01.dbf
-rw-r----- 1 oracle oinstall 152051712 Nov 30 13:22 users01.dbf
-rw-r----- 1 oracle oinstall 10493952 Nov 30 13:22 users02.dbf
--启动备库至mount
sys@FUWA 2023-11-30 13:16:39> startup mount
ORACLE instance started.
Total System Global Area 2638954496 bytes
Fixed Size 2256152 bytes
Variable Size 620757736 bytes
Database Buffers 2013265920 bytes
Redo Buffers 2674688 bytes
Database mounted.
--更改控制文件中该文件位置
sys@FUWA 30-NOV-23> alter database rename file '/data/oradata/FUWA/datafile/o1_mf_users_lphyvf37_.dbf' to '/data/oradata/fuwa/users02.dbf';
Database altered.
col name for a60
sys@FUWA 30-NOV-23> select * from v$dbfile;
FILE# NAME
---------- ------------------------------------------------------------
4 /data/oradata/fuwa/users01.dbf
3 /data/oradata/fuwa/undotbs01.dbf
2 /data/oradata/fuwa/sysaux01.dbf
1 /data/oradata/fuwa/system01.dbf
5 /data/oradata/fuwa/users02.dbf
--启库
sys@FUWA 30-NOV-23> alter database open;
sys@FUWA 30-NOV-23> select name from v$datafile;
NAME
------------------------------------------------------------
/data/oradata/fuwa/system01.dbf
/data/oradata/fuwa/sysaux01.dbf
/data/oradata/fuwa/undotbs01.dbf
/data/oradata/fuwa/users01.dbf
/data/oradata/fuwa/users02.dbf