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