实时警报通知:微信告警通知的重要性解析
1085
2023-03-28
生产环境Oracle dataguard不同步处理过程分享
一、问题描述
dg环境扩容表空间新增的数据文件部分没有传到备库上去,报ORA-16136: Managed Standby Recovery not active
二、分析及处理过程:
2.1 备库上查看v$managed_standby
SQL> select process,status,sequence# from v$managed_standby; PROCESS STATUS SEQUENCE# ARCH CLOSING 114516 ARCH CLOSING 114517 ARCH CONNECTED 0 ARCH CLOSING 114518 RFS IDLE 0 RFS WRITING 114519 RFS IDLE 0 7 rows selected. 从上面的输出,可以看出,缺少MRP0进程。
2.2 确认报错信息
SQL> alter database recover managed standby database; alter database recover managed standby database * ERROR at line 1: ORA-00283: recovery session canceled due to errors ORA-01111: name for data file 14 is unknown - rename to correct file ORA-01110: data file 14: '/data/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00014' ORA-01157: cannot identify/lock data file 14 - see DBWR trace file ORA-01111: name for data file 14 is unknown - rename to correct file ORA-01110: data file 14: '/data/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00014' 从上面的输出可以看出是14号文件的问题
2.3 备库重新创建14号文件
14号文件本次表空间扩容新增的数据文件。
alter system set standby_file_management=manual; alter database create datafile '/data/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00014' as '/data/app/oracle/newdata/dataqiye_data9.dbf' alter system set standby_file_management=auto;
2.4 再次查看警告日志文件
started logmerger process Tue Jan 30 13:31:35 2018 Managed Standby Recovery not using Real Time Apply Parallel Media Recovery started with 8 slaves Waiting for all non-current ORLs to be archived... All non-current ORLs have been archived. Media Recovery Waiting for thread 1 sequence 114456 Fetching gap sequence in thread 1, gap sequence 114456-114459 Completed: alter database recover managed standby database disconnect from session Tue Jan 30 13:33:26 2018 FAL[client]: Failed to request gap sequence GAP - thread 1 sequence 114456-114459 DBID 1477707379 branch 949967087 FAL[client]: All defined FAL servers have been attempted. Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization parameter is defined to a value that's sufficiently large enough to maintain adequate log switch information to resolve archivelog gaps. 日志出现断档,从主库的归档备份中恢复序列号是 sequence 114456-114459的归档日志。
2.5 主库上操作
从主库归档备份中恢复所需的归档日志序列(114456-114459)
rman target / restore archivelog from sequence 114456 until sequence 114459;
2.6 备库上观察警告日志输出
RFS[3]: Opened log for thread 1 sequence 114456 dbid 1477707379 branch 949967087 Tue Jan 30 13:42:44 2018 Archived Log entry 114633 added for thread 1 sequence 114457 rlc 949967087 ID 0x0 dest 2: Tue Jan 30 13:42:44 2018 Archived Log entry 114634 added for thread 1 sequence 114456 rlc 949967087 ID 0x0 dest 2: Tue Jan 30 13:42:44 2018 RFS[4]: Assigned to RFS process 5977 RFS[4]: Opened log for thread 1 sequence 114504 dbid 1477707379 branch 949967087 RFS[2]: Opened log for thread 1 sequence 114503 dbid 1477707379 branch 949967087 RFS[3]: Opened log for thread 1 sequence 114502 dbid 1477707379 branch 949967087 Tue Jan 30 13:42:47 2018 Media Recovery Log /data/app/oracle/arch/arch_949967087_1_114456.arc Media Recovery Log /data/app/oracle/arch/arch_949967087_1_114457.arc Media Recovery Log /data/app/oracle/arch/arch_949967087_1_114458.arc Tue Jan 30 13:43:08 2018 Archived Log entry 114635 added for thread 1 sequence 114502 rlc 949967087 ID 0x58146bd8 dest 2: Tue Jan 30 13:43:08 2018 Archived Log entry 114636 added for thread 1 sequence 114503 rlc 949967087 ID 0x58146bd8 dest 2: Tue Jan 30 13:43:08 2018 Archived Log entry 114637 added for thread 1 sequence 114504 rlc 949967087 ID 0x58146bd8 dest 2: Tue Jan 30 13:43:17 2018 Media Recovery Log /data/app/oracle/arch/arch_949967087_1_114459.arc Media Recovery Log /data/app/oracle/arch/arch_949967087_1_114460.arc Media Recovery Log /data/app/oracle/arch/arch_949967087_1_114461.arc [oracle@iZwz9h66josrg1nhghd32dZ trace]$ tail -f alert_orcl.log Tue Jan 30 13:43:08 2018 Archived Log entry 114636 added for thread 1 sequence 114503 rlc 949967087 ID 0x58146bd8 dest 2: Tue Jan 30 13:43:08 2018 Archived Log entry 114637 added for thread 1 sequence 114504 rlc 949967087 ID 0x58146bd8 dest 2: Tue Jan 30 13:43:17 2018 Media Recovery Log /data/app/oracle/arch/arch_949967087_1_114459.arc Media Recovery Log /data/app/oracle/arch/arch_949967087_1_114460.arc Media Recovery Log /data/app/oracle/arch/arch_949967087_1_114461.arc Tue Jan 30 13:43:43 2018 Media Recovery Log /data/app/oracle/arch/arch_949967087_1_114462.arc Tue Jan 30 13:43:55 2018 Media Recovery Log /data/app/oracle/arch/arch_949967087_1_114463.arc Tue Jan 30 13:44:11 2018 Media Recovery Log /data/app/oracle/arch/arch_949967087_1_114464.arc Tue Jan 30 13:44:23 2018 Media Recovery Log /data/app/oracle/arch/arch_949967087_1_114465.arc Tue Jan 30 13:44:37 2018 Media Recovery Log /data/app/oracle/arch/arch_949967087_1_114466.arc Tue Jan 30 13:44:51 2018 Media Recovery Log /data/app/oracle/arch/arch_949967087_1_114467.arc Tue Jan 30 13:45:05 2018 Media Recovery Log /data/app/oracle/arch/arch_949967087_1_114468.arc Tue Jan 30 13:45:17 2018 Media Recovery Log /data/app/oracle/arch/arch_949967087_1_114469.arc Tue Jan 30 13:45:31 2018 Media Recovery Log /data/app/oracle/arch/arch_949967087_1_114470.arc Tue Jan 30 13:45:45 2018 Media Recovery Log /data/app/oracle/arch/arch_949967087_1_114471.arc Tue Jan 30 13:46:01 2018 Media Recovery Log /data/app/oracle/arch/arch_949967087_1_114472.arc Tue Jan 30 13:46:14 2018 Media Recovery Log /data/app/oracle/arch/arch_949967087_1_114473.arc .................... 从日志的输出中我们可以看出,备库正在应用归档。
三、验证
3.1 备库验证
SQL> select process,status,sequence# from v$managed_standby; PROCESS STATUS SEQUENCE# ARCH CLOSING 114534 ARCH CLOSING 114535 ARCH CONNECTED 0 ARCH CLOSING 114533 RFS IDLE 0 RFS IDLE 114536 RFS IDLE 0 MRP0 WAIT_FOR_LOG 114536 RFS IDLE 0 9 rows selected. SQL> select database_role,switchover_status,open_mode from v$database; DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE PHYSICAL STANDBY NOT ALLOWED MOUNTED SQL> select distinct thread#,max(sequence#) over(partition by thread#) a from v$archived_log; THREAD# A 1 114537 SQL>
3.2 主库验证
SQL> select distinct thread#,max(sequence#) over(partition by thread#) a from v$archived_log; THREAD# A 1 114537 SQL>
四、dg重启关闭命令总结
4.1 关闭备用数据库
SQL >alter database recover managed standby database cancel; SQL >shutdown immediate;
4.2 启动备库
SQL > startup nomount; SQL >alter database mount standby database; SQL >alter database recover managed standby database disconnect from session;
4.3 启用备库的实时应用
SQL > startup nomount; SQL >alter database mount standby database; SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
4.4 启用备库的实时应用+只读模式
SQL >startup nomount; SQL >alter database mount standby database; SQL >alter database open read only; SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
【编辑推荐】
为什么不建议你用分布式事务?面试官问 HTTPS 是怎么从 HTTP 转过来的,我有点懵2022年及以后全球IT行业的十大预测苹系统版本再更新,由iOS 14.6 迎来了iOS 15.2你要的Windows:精简+ CPU占用率暴降!瞬间干掉LTSC
发表评论
暂时没有评论,来抢沙发吧~