环境说明
OS：Red Hat Enterprise Linux Server release 6.5 (Santiago)
数据库版本：SQL*Plus: Release 11.2.0.4.0 Production
背景介绍
测试使用的一套单机ADG，早上到单位之后同事询问表空间使用率查询的两种方式，基于数据文件的自动扩展和默认数据文件非自动扩展，以及现有脚本查询是否准确，随即在自己的测试环境上创建了一个表空间，并指定扩展上线大小，进行了如下操作。
09:08:46 SQL> create tablespace lhh datafile '/oradata/orcl/lhh01.dbf' size 1G autoextend on maxsize 5G; Tablespace created.
上述操作执行成功后，发现备库异常宕机了感觉非常奇怪，随即查看了备库的alert日志信息，发现提示STANDBY_FILE_MANAGEMENT参数设置为MANUAL，MRP进程在应用日志的时候，因ORA-01274错误被异常终止。alert信息截取如下：
Wed Mar 02 09:10:49 2022 File #5 added to control file as 'UNNAMED00005' because the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL The file should be manually created to continue. MRP0: Background Media Recovery terminated with error 1274 Errors in file /u01/app/oracle/diag/rdbms/orcldg/orcl/trace/orcl_mrp0_10718.trc: ORA-01274: cannot add datafile '/oradata/orcl/lhh01.dbf' - file could not be created Managed Standby Recovery not using Real Time Apply Recovery interrupted! Recovered data files to a consistent state at change 1251042 MRP0: Background Media Recovery process shutdown (orcl)
问题找到之后处理就比较简单了
问题解决处理 查看备库的数据文件信息，这里就能发现异常的数据文件了，此时备库恢复正常
09:13:36 SQL> select name from v$datafile; NAME ------------------------------ /oradata/orcldg/system01.dbf /oradata/orcldg/sysaux01.dbf /oradata/orcldg/undotbs01.dbf /oradata/orcldg/users01.dbf /u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00005
查看standby_file_management参数状态值 --这里因为是没有设置导致的问题，不同于设置auto因备库空间不足导致的故障，因此直接下一步操作即可
09:13:42 SQL> show parameter standby_file_management NAME TYPE VALUE ------------------------------------ --------------- ------------------------------ standby_file_management string MANUAL
重建一个新的数据文件
09:15:52 SQL> alter database create datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00005' as '/oradata/orcl/lhh01.dbf'; Database altered.
重新设置 standby_file_management参数为auto，避免问题再次发生
10:30:22 SQL> alter system set standby_file_management=auto; System altered. 10:35:29 SQL> show parameter standby_file_management NAME TYPE VALUE ------------------------------------ --------------- ------------------------------ standby_file_management string AUTO
再次查看数据文件，此时发现数据文件名称正常
09:16:30 SQL> select name from v$datafile; NAME ------------------------------ /oradata/orcldg/system01.dbf /oradata/orcldg/sysaux01.dbf /oradata/orcldg/undotbs01.dbf /oradata/orcldg/users01.dbf /oradata/orcl/lhh01.dbf
再次开启备库实时应用
10:28:40 SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered.
查看应用延迟
10:29:43 SQL> set linesize 150;
10:30:22 SQL> set pagesize 20;
10:30:22 SQL> column name format a13;
10:30:22 SQL> column value format a20;
10:30:22 SQL> column unit format a30;
10:30:22 SQL> column TIME_COMPUTED format a30;
10:30:22 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 03/02/2022 10:30:22
apply lag +00 00:00:00 day(2) to second(0) interval 03/02/2022 10:30:22
