绑定完请刷新页面
取消
刷新

分享好友

×
取消 复制
DG环境备库数据不同步-同样的问题又发生了(处理过程与之前略有差异,值得记录下来)
2021-02-01 15:01:14

处理xxxx17备库数据不同步问题,定位备库空间不足导致mrp进程终止,
后续通过主库迁移文件,备库重新定位文件,重启MRP进程拉平数据。
备库的第二个节点的告警日志
2021-01-29T17:01:37.931343+08:00
Managed Standby Recovery not using Real Time Apply
2021-01-29T17:01:55.081492+08:00
Reconfiguration started (old inc 16, new inc 18)
List of instances (total 3) :
1 2 3
My inst 2
Global Resource Directory frozen
* SM/ADG-SM mode terminates successfully - (publisher 3) set domain valid
Communication channels reestablished
2021-01-29T17:01:55.131047+08:00
* domain 0 valid = 1 (flags x0, pdb flags x800) according to instance 1
2021-01-29T17:01:55.134175+08:00
Master broadcasted resource hash value bitmaps
Non-local Process blocks cleaned out
2021-01-29T17:01:55.229957+08:00
LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived, skipped 0
2021-01-29T17:01:55.230077+08:00
LMS 3: 0 GCS shadows cancelled, 0 closed, 0 Xw survived, skipped 0
2021-01-29T17:01:55.230082+08:00
LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived, skipped 0
2021-01-29T17:01:55.230088+08:00
LMS 2: 0 GCS shadows cancelled, 0 closed, 0 Xw survived, skipped 0
2021-01-29T17:01:55.230346+08:00
LMS 4: 0 GCS shadows cancelled, 0 closed, 0 Xw survived, skipped 0
Set master node info
Submitted all remote-enqueue requests
Dwn-cvts replayed, VALBLKs dubious
All grantable enqueues granted
Submitted all GCS remote-cache requests
2021-01-29T17:01:56.493982+08:00
Fix write in gcs resources
2021-01-29T17:01:56.519026+08:00
Reconfiguration complete (total time 1.4 secs)
2021-02-01T05:44:08.408942+08:00
Managed Standby Recovery starting Real Time Apply
2021-02-01T05:44:08.419365+08:00
Errors in file /oracle/diag/rdbms/xxxx17dg/xxxx17dg2/trace/xxxx17dg2_dbw0_288944.trc:
ORA-01186: file 589 failed verification tests
ORA-01157: cannot identify/lock data file 589 - see DBWR trace file
ORA-01111: name for data file 589 is unknown - rename to correct file
ORA-01110: data file 589: '/oracle/12.2/dbs/UNNAMED00589'
2021-02-01T05:44:08.419409+08:00
File 589 not verified due to error ORA-01157
2021-02-01T05:44:08.487892+08:00
Managed Standby Recovery not using Real Time Apply
2021-02-01T05:44:08.766778+08:00
Reconfiguration started (old inc 18, new inc 20)
List of instances (total 3) :
1 2 3
My inst 2
Global Resource Directory frozen
Communication channels reestablished

看下错误描述
[oracle@pc1] /oracle/diag/rdbms/xxxx17dg/xxxx17dg2/trace]$oerr ora 1157
01157, 00000, "cannot identify/lock data file %s - see DBWR trace file"
// *Cause: The background process was either unable to find one of the data
// files or failed to lock it because the file was already in use.
// The database will prohibit access to this file but other files will
// be unaffected. However the first instance to open the database will
// need to access all online data files. Accompanying error from the
// operating system describes why the file could not be identified.
// *Action: Have operating system make file available to database. Then either
// open the database or do ALTER SYSTEM CHECK DATAFILES.

就是主库创建了数据文件而备库没有创建成功,需要我们检查为啥没有成功。根据经验我们从主备库的磁盘空间检查
发现备库该文件系统已经了,可以确定是主库创建的数据文件在备库没有成功,目前看需要在备库扩容解决,这个问题之前处理
哈局系统是遇到过,所以处理起来思路比较起清楚。由于扩容需要逐级审批,而我们又不能无限制等待,因为主库归档空间有限,备库日志不能
应用主库归档就不会删除,所以必须尽快解决这个问题。我们尝试如下方式解决问题。

SQL> alter system set standby_file_management=manual;

System altered.

SQL> alter database create datafile '/oracle/12.2/dbs/UNNAMED00589' as '/oradata2/xxxx17/GP_3.dbf';

Database altered.

SQL> alter database open;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

报错如下

File #589 added to control file as 'UNNAMED00589' because
the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL
The file should be manually created to continue.
Errors with log /gd/3_11509_997797587.arc
MRP0: Background Media Recovery terminated with error 1274
2021-02-01T09:47:27.490672+08:00
Errors in file /oracle/diag/rdbms/xxxx17dg/xxxx17dg1/trace/xxxx17dg1_pr00_2404.trc:
ORA-01274: cannot add data file that was originally created as '/oradata1/xxxx17/YXGP_3.dbf'
2021-02-01T09:47:27.491472+08:00
Managed Standby Recovery not using Real Time Apply
2021-02-01T09:47:27.610550+08:00
Recovery interrupted!
从报错看,我们上面的rename操作 是失败的,也就是不允许增加originally created创建的数据文件,
这里提示我们move主库的数据文件到/oradata2,然后再在备库创建失败的数据文件到/oradata2

备库关闭后启动数据库报错
SQL> startup
ORACLE instance started.

Total System Global Area 1.0737E+11 bytes
Fixed Size 29869528 bytes
Variable Size 2.3622E+10 bytes
Database Buffers 8.3483E+10 bytes
Redo Buffers 238563328 bytes
Database mounted.
ORA-10458: standby database requires recovery
ORA-01157: cannot identify/lock data file 589 - see DBWR trace file
ORA-01111: name for data file 589 is unknown - rename to correct file
ORA-01110: data file 589: '/oracle/12.2/dbs/UNNAMED00589

下面在主库将该文件定义到其他目录
在主库
SQL> col file_name for a40
SQL>select file_name,status from dba_data_files where file_id=589


FILE_NAME STATUS
---------------------------------------- ---------
/oradata1/xxxx17/YXGP_3.dbf AVAILABLE

SQL> alter database move datafile '/oradata1/xxxx17/GCAREVTA01_YXGP_3.dbf' to '/oradata2/xxxx17/YXGP_3.dbf';

Database altered.

SQL> select file_name,status from dba_data_files where file_id=589;

FILE_NAME STATUS
---------------------------------------- ---------
/oradata2/xxxx17/YXGP_3.dbf AVAILABLE

在备库重新执行如下操作
SQL> startup mount
SQL> alter system set standby_file_management=manual;
SQL> alter database create datafile '/oracle/12.2/dbs/UNNAMED00589' as '/oradata2/xxxx17/YXGP_3.dbf';
SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

开始追日志
2021-02-01T10:32:57.249224+08:00
Attempt to start background Managed Standby Recovery process (xxxx17dg1)
Starting background process MRP0
2021-02-01T10:32:57.264209+08:00
MRP0 started with pid=9, OS id=128200
2021-02-01T10:32:57.265139+08:00
MRP0: Background Managed Standby Recovery process started (xxxx17dg1)
2021-02-01T10:33:02.278444+08:00
Starting single instance redo apply (SIRA)
Started loxxerger process
2021-02-01T10:33:02.314621+08:00
Managed Standby Recovery starting Real Time Apply
2021-02-01T10:33:04.343863+08:00
Parallel Media Recovery started with 96 slaves
2021-02-01T10:33:04.539233+08:00
Media Recovery Log /gd/1_26471_997797587.arc
2021-02-01T10:33:04.617576+08:00
Media Recovery Log /gd/2_12139_997797587.arc
2021-02-01T10:33:04.803323+08:00
Media Recovery Log /gd/3_11509_997797587.arc
2021-02-01T10:33:05.273477+08:00
Completed: alter database recover managed standby database using current logfile disconnect from session
2021-02-01T10:33:16.496030+08:00
Media Recovery Log /gd/1_26472_997797587.arc

后修改备库参数

SQL> alter system set standby_file_management=auto;

System altered.


分享好友

分享这个小栈给你的朋友们,一起进步吧。

Oracle运维新鲜事-技术与管理各占半边天
创建时间:2020-08-04 11:34:57
本技术栈旨在分享技术心得,运维趣事,故障处理经验,调优案例,故障处理涉及集群,DG,OGG,大家生产中遇到的问题基本都会囊括了,我会发布生产库遇到的故障,希望在交流中互助互益,共同提高,也希望大家讨论,如果您有生产中遇到的集群问题,也可以在这里提出来,一起讨论,现实中也帮助不少同学解决了生产库的故障。
展开
订阅须知

• 所有用户可根据关注领域订阅专区或所有专区

• 付费订阅:虚拟交易,一经交易不退款;若特殊情况,可3日内客服咨询

• 专区发布评论属默认订阅所评论专区(除付费小栈外)

栈主、嘉宾

查看更多
  • Abraham林老师
    栈主
  • 小雨滴
    嘉宾
  • hawkliu
    嘉宾
  • u_97a59a25246404
    嘉宾

小栈成员

查看更多
  • 栈栈
  • dapan
  • 小菜鸟___
  • hwayw
戳我,来吐槽~