1.错误描述
检查 ADG 同步状态
DGMGRL> show database verbose sljj2dg;
Database - sljj2dg
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 39 days 17 hours 52 minutes 32 seconds (computed 0 seconds ago)
Apply Rate: 5.94 MByte/s
Real Time Query: OFF
Instance(s):
sljj
Database Error(s):
ORA-16766: Redo Apply is stopped
Properties:
DGConnectIdentifier = 'sljj2dg'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '120'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '+data/sljj/datafile, /u01/app/oracle/oradata/sljj, +IMAGE/sljj/datafile, /u01/app/oracle/oradata/sljj'
LogFileNameConvert = '+REDO/sljj/onlinelog, /u01/app/oracle/oradata/sljj, +FRA/sljj/onlinelog, /u01/app/oracle/oradata/sljj'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
SidName = 'sljj'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jcpt-database)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=sljj2dg_DGMGRL)(INSTANCE_NAME=sljj)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/u01/app/oracle/archivelog'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
ERROR
DGMGRL>
发现备库日志应用停止。
2.错误检查
主库检查命令
step 1.主库dg环境的保护模式检查
SQL> select db_unique_name,database_role, protection_mode,switchover_status from v$database;
DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
---------------- ---------------- -------------------- --------------------
ORCL PRIMARY MAXIMUM PERFORMANCE TO STANDBY
上述结果显示：主库db_unique_name为 SLJJ；角色为主库；保护模式为最大性能；切换状态为可以切备库。
step 2.主库上查看设置的归档日志路径是否可用
SQL> select dest_name,status,error from v$archive_dest;
该视图用户查看本地和远程的归档日志路径是否可用，如果远程的归档日志路径不可用，在ERROR列会有相应报错
step 3.主库上查看DG的状态信息
SQL> set linesize 300
SQL> set pagesize 1000
SQL> col message for a100
SQL> select to_char(timestamp,'DD-mm-YYYY HH24:MI:SS') time,message FROM v$dataguard_status;
......
MESSAGE
--------------------------------------------------------------------------------
MRP0: Background Media Recovery process shutdown
ARC3: Beginning to archive thread 1 sequence 78376 (22999090901-22999329239)
ARC3: Completed archiving thread 1 sequence 78376 (0-0)
256 rows selected.
SQL>
step 4.主库上查看DG环境进程的状态
SQL> select PROCESS,STATUS from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CLOSING
ARCH CLOSING
ARCH CONNECTED
ARCH CLOSING
RFS IDLE
RFS IDLE
RFS IDLE
RFS IDLE
RFS IDLE
RFS IDLE
RFS IDLE
PROCESS STATUS
--------- ------------
RFS IDLE
12 rows selected.
- ARCH进程：用于主库上复制redo log，从而生成归档日志，当前状态为CLOSING表示该进程目前正在复制 redo log，我们在参数文件中设置了该进程的数量上限是4个。
- LNS进程：用于在主库上将主库的归档日志同步到备库上，将归档日志投递给备库上的RFS进程;lns从主库端日志缓冲区中读取相同的重做记录，通过oracle net服务传给备用数据库，备用数据库上的rfs接收重做数据，然后将其写入备用重做日志文件中。
备库检查命令
step 1.备库上查看DG环境特有进程的状态
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
- RFS进程：用于备库接收从主库LNS进程或ARCH进程投递过来的归档日志
- ARCH：用于复制从主库上同步过来的归档日志
- MRP0：用于应用归档日志
step 2.备库上查看DG环境的状态信息
SQL> set linesize 300
SQL> set pagesize 1000
SQL> col message for a100
SQL> select message_num,message from v$dataguard_status;
step 3. 备库端查看日志是否应用
SQL> select name,SEQUENCE#,APPLIED from v$archived_log order by sequence#;
如果发现备库归档日志的编号不连续，则需要到主库去对照主库的归档日志编号，找到主库上已经归档但却没有同步到备库上的那些归档日志手动拷贝过来，并将其注册到备库内，注册归档日志的命令如下所示：
SQL> alter database register physical logfile '/u01/app/oracle/archivelog/’
然后再重新开启备库的应用归档日志进程
step 4.备库上查看归档日志有没有断档
SQL> select * from v$archive_gap;
如果DG环境日志同步正常，则不会查到任何记录。如果查出结果，则说明目前的DG环境归档日志传递有断档。
3.解决办法
停止redo apply
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
oracle data guard 备库启停日志应用
- dg broker 启用日志同步
DGMGRL> edit database sljj2dg set state='apply-on';
Succeeded.
DGMGRL>
- 在Dataguard的备库上应用日志的时候，通常有两个语句，但是这两个语句是有区别的，一个是实时应用，一个是非实时应用。
alter database recover managed standby database disconnect from session;（非实时应用）
alter database recover managed standby database using current logfile disconnect;（实时应用）
启用日志应用，该问题未得到解决。查看告警日志如下：
<msg time='2025-05-26T09:13:51.491+08:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='jcpt-database' host_addr='92.236.125.173' module=''
pid='27957'>
<txt>Errors in file /u01/app/oracle/diag/rdbms/sljj2dg/sljj/trace/sljj_dbw0_27957.trc:
ORA-01186: file 334 failed verification tests
ORA-01157: cannot identify/lock data file 334 - see DBWR trace file
ORA-01111: name for data file 334 is unknown - rename to correct file
ORA-01110: data file 334: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00334'
</txt>
</msg>
<msg time='2025-05-26T09:13:51.491+08:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='jcpt-database' host_addr='92.236.125.173' module=''
pid='27957'>
<txt>File 334 not verified due to error ORA-01157
</txt>
</msg>
<msg time='2025-05-26T09:13:51.491+08:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='jcpt-database' host_addr='92.236.125.173' module=''
pid='27957'>
<txt>Errors in file /u01/app/oracle/diag/rdbms/sljj2dg/sljj/trace/sljj_dbw0_27957.trc:
ORA-01186: file 2001 failed verification tests
ORA-01157: cannot identify/lock data file 2001 - see DBWR trace file
ORA-01110: data file 2001: '+DATA'
</txt>
</msg>
<msg time='2025-05-26T09:13:51.491+08:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='jcpt-database' host_addr='92.236.125.173' module=''
pid='27957'>
<txt>File 2001 not verified due to error ORA-01157
</txt>
</msg>
<msg time='2025-05-26T09:13:55.996+08:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='jcpt-database' host_addr='92.236.125.173' module=''
pid='2823'>
<txt>MRP0: Background Media Recovery terminated with error 1111
</txt>
</msg>
<msg time='2025-05-26T09:13:55.996+08:00' org_id='oracle' comp_id='rdbms'
client_id='' type='UNKNOWN' level='16'
host_id='jcpt-database' host_addr='92.236.125.173' module=''
pid='2823'>
<txt>Errors in file /u01/app/oracle/diag/rdbms/sljj2dg/sljj/trace/sljj_pr00_2823.trc:
ORA-01111: name for data file 334 is unknown - rename to correct file
ORA-01110: data file 334: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00334'
ORA-01157: cannot identify/lock data file 334 - see DBWR trace file
ORA-01111: name for data file 334 is unknown - rename to correct file
ORA-01110: data file 334: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00334'
</txt>
</msg>
出现问题，描述如下：
根据报错信息可以初步判断334号数据文件没有创建成功，也就是主库添加的数据文件在备库创建失败。
4.解决问题
step 1.停止redo apply
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
*
ERROR at line 1:
ORA-16136: Managed Standby Recovery not active
SQL>
由于该问题，导致日志应用无法正常启动。
step 2.将备库启动到mount状态
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
SLJJ MOUNTED PHYSICAL STANDBY
SQL>
step 3.将standby_file_management改为 manual
SQL> alter system set standby_file_management=manual;
System altered.
SQL> show parameter standby
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string ?/dbs/arch
standby_file_management string MANUAL
step 4.主库检查334号数据文件
SQL> select file#,name from v$datafile where file#=334;
FILE# NAME
--------------------------------------------------------------------------------
334 +FRA/sljj/datafile/rm_index.12427.1198592895
SQL>
step 5.备库使用create datafile as方式创建数据文件
SQL> select file#,name from v$datafile where file#=334;
FILE# NAME
--------------------------------------------------------------------------------
334 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00334
SQL>
SQL> alter database create datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00334' as '/u01/app/oracle/oradata/sljj/rm_index.12427.1198592895';
Database altered.
SQL> select file#,name from v$datafile where file#=334;
FILE# NAME
--------------------------------------------------------------------------------
334 /u01/app/oracle/oradata/sljj/rm_index.12427.1198592895
SQL>
step 6.修改standby_file_management参数为auto
SQL> alter system set standby_file_management=auto;
System altered.
SQL> show parameter standby
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string ?/dbs/arch
standby_file_management string AUTO
step 7.开启MRP，检查数据同步情况
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
step 8.查看日志应用进程
SQL> select process,status,thread#,sequence#,block# from v$managed_standby;
PROCESS STATUS THREAD# SEQUENCE# BLOCK#
--------- ------------ ---------- ---------- ----------
ARCH CLOSING 1 78391 522240
ARCH CLOSING 1 78392 624640
ARCH CONNECTED 0 0 0
ARCH CLOSING 2 28925 325632
RFS IDLE 0 0 0
RFS IDLE 0 0 0
RFS WRITING 1 78393 280347
RFS WRITING 2 28926 386394
RFS IDLE 0 0 0
RFS IDLE 0 0 0
MRP0 APPLYING_LOG 1 70176 624390
11 rows selected.
SQL>
至此，日志应用开始。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
