adg 的tempfile 和 offline datafile

adg 的tempfile 和 offline datafile 最近做adg项目的时候主要到几个问题，主备库的tempfile 是否一致以及备库 datafile 是否online不影响日志的传输和应用（其实想想原理也能理解），现在就相关问题测试如下： 参考的相关文档： Temporary Datafile created in Primary is missing in the Standby Database (Doc ID 834174.1) Data Guard Physical Standby - Managing temporary tablespace tempfiles (Doc ID 1514588.1) 测试有两个方向： 1 主库添加temp file对备库的影响 2 备库的数据文件offline 对同步的影响 环境准备： 主库情况： SQL> select name from v$tempfile; NAME -------------------------------------------------------------------------------- /oradata/test/temp01.dbf SQL> select open_mode,database_role from v$database; OPEN_MODE DATABASE_ROLE -------------------- ---------------- READ WRITE PRIMARY 备库情况： SQL> select open_mode,database_role from v$database; OPEN_MODE DATABASE_ROLE -------------------- ---------------- READ ONLY WITH APPLY PHYSICAL STANDBY SQL> select name from v$tempfile; NAME ------------- /oradata/test/temp01.dbf select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag'); NAME VALUE UNIT TIME_COMPUTED ------------- -------------------- ------------------------------ ------------------------------ transport lag +00 00:00:00 day(2) to second(0) interval 05/22/2024 15:11:51 apply lag +00 00:00:00 day(2) to second(0) interval 05/22/2024 15:11:51 然后我们主库添加temp file 再次查看备库情况和延迟： alter tablespace temp add tempfile '/oradata/test/temp02.dbf' size 5M autoextend on; 主库情况： SQL> select name from v$tempfile; NAME -------------------------------------------------------------------------------- /oradata/test/temp01.dbf /oradata/test/temp02.dbf 备库情况： SQL> col name format a80 SQL> select name from v$tempfile; NAME -------------------------------------------------------------------------------- /oradata/test/temp01.dbf 备库添加临时文件： SQL> alter tablespace temp add tempfile '/oradata/test/temp02.dbf' size 5M autoextend on; Tablespace altered. SQL> select name from v$tempfile; NAME -------------------------------------------------------------------------------- /oradata/test/temp01.dbf /oradata/test/temp02.dbf 结论： 1 主备库的临时文件对主备库同步关系不大 2 正常rman恢复的时候临时文件是不恢复的，只有在数据库open的时候会在相应的目录下创建临时文件，我们需要保证临时文件所在的目录存在并且数据库 有读写权限。不然得话就得重命名select 'alter database rename file '''||name||''''||' to '''||'/home/'''||';' from v$tempfile; 3 正常同步的主备库主库创建临时文件备库是不会创建的，切换的时候要注意是否需要在切换后添加临时文件 4 备库是可以独立添加临时文件的，如果备库跑报表业务用到TEMP表空间可以添加相应文件。 测试2： 备库的数据文件offline 不会影响主备同步，查延迟是没有的。 主库情况： SQL> select open_mode,database_role from v$database; OPEN_MODE DATABASE_ROLE -------------------- ---------------- READ WRITE PRIMARY SQL> select name,status from v$datafile; NAME STATUS -------------------------------------------------------------------------------- ------- /oradata/test/system01.dbf SYSTEM /oradata/test/sysaux01.dbf ONLINE /oradata/test/undotbs01.dbf ONLINE /oradata/test/users01.dbf ONLINE /oradata/test/example01.dbf ONLINE 备库情况： SQL> select open_mode,database_role from v$database; OPEN_MODE DATABASE_ROLE -------------------- ---------------- READ ONLY WITH APPLY PHYSICAL STANDBY SQL> select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag'); NAME VALUE UNIT TIME_COMPUTED ------------- -------------------- ------------------------------ ------------------------------ transport lag +00 00:00:00 day(2) to second(0) interval 05/22/2024 16:07:19 apply lag +00 00:00:00 day(2) to second(0) interval 05/22/2024 16:07:19 SQL> select name,status from v$datafile; NAME STATUS -------------------------------------------------------------------------------- ------- /oradata/test/system01.dbf SYSTEM /oradata/test/sysaux01.dbf ONLINE /oradata/test/undotbs01.dbf ONLINE /oradata/test/users01.dbf ONLINE /oradata/test/example01.dbf ONLINE alter database datafile '/oradata/test/example01.dbf' offline drop; offline 数据文件 SQL> alter database datafile '/oradata/test/example01.dbf' offline drop; alter database datafile '/oradata/test/example01.dbf' offline drop * ERROR at line 1: ORA-01156: recovery or flashback in progress may need access to files SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Database altered. SQL> alter database datafile '/oradata/test/example01.dbf' offline drop; Database altered. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; Database altered. SQL> select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag'); NAME VALUE UNIT TIME_COMPUTED ------------- -------------------- ------------------------------ ------------------------------ transport lag +00 00:00:00 day(2) to second(0) interval 05/22/2024 16:12:31 apply lag +00 00:00:00 day(2) to second(0) interval 05/22/2024 16:12:31 SQL> select to_char(current_scn) from v$database; TO_CHAR(CURRENT_SCN) ---------------------------------------- 2158450 SQL> select to_char(current_scn) from v$database; TO_CHAR(CURRENT_SCN) ---------------------------------------- 2158451 SQL> select to_char(current_scn) from v$database; TO_CHAR(CURRENT_SCN) ---------------------------------------- 2158451 SQL> select to_char(current_scn) from v$database; TO_CHAR(CURRENT_SCN) ---------------------------------------- 2158452 数据文件状态 NAME STATUS -------------------------------------------------------------------------------- ------- /oradata/test/system01.dbf SYSTEM /oradata/test/sysaux01.dbf ONLINE /oradata/test/undotbs01.dbf ONLINE /oradata/test/users01.dbf ONLINE /oradata/test/example01.dbf RECOVER SQL> select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag'); NAME VALUE UNIT TIME_COMPUTED ------------- -------------------- ------------------------------ ------------------------------ transport lag +00 00:00:00 day(2) to second(0) interval 05/22/2024 16:12:46 apply lag +00 00:00:00 day(2) to second(0) interval 05/22/2024 16:12:46 可以发现数据库还是没有延迟 恢复： 备份数据文件5传输到备库 备库启动到mount状态恢复数据文件5 RMAN> restore datafile 5; Starting restore at 22-MAY-24 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00005 to /oradata/test/example01.dbf channel ORA_DISK_1: reading from backup piece /home/oracle/5.dbk channel ORA_DISK_1: piece handle=/home/oracle/5.dbk tag=TAG20240522T161821 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 Finished restore at 22-MAY-24 SQL> alter database datafile 5 online; SQL> alter database open; NAME STATUS -------------------------------------------------------------------------------- ------- /oradata/test/system01.dbf SYSTEM /oradata/test/sysaux01.dbf ONLINE /oradata/test/undotbs01.dbf ONLINE /oradata/test/users01.dbf ONLINE /oradata/test/example01.dbf ONLINE 12之后可以选择下面的方式恢复 restore datafile 73 FROM SERVICE ykxtoaadg; 可以通过restore .. from service指定的对象类型： database datafile tablespace 控制文件 SPFILE