本文主要是介绍DG11g备库ORA-19909 ORA-01110,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
问题场景
巡检发现备库状态不正常,open_mode由read only with apply 变成了read only。原因:主库做过rman备份恢复,因为是同一个库恢复出来的,所以备库认为这些归档也是主库产生的,所以会注册进去。
问题分析
查主备状态
#部署时主备状态
--主库信息 switchover显示to standby或者session active为正常
set linesize 999
select name,open_mode,database_role,protection_mode,switchover_status from v$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
topicis READ WRITE PRIMARY MAXIMUM PERFORMANCE session active--备库信息
set linesize 999
select name,open_mode,database_role,protection_mode,switchover_status from v$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
topicis READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE NOT ALLOWED#巡检时主备状态
--主库信息 switchover显示to standby或者session active为正常
set linesize 999
select name,open_mode,database_role,protection_mode,switchover_status from v$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
topicis READ WRITE PRIMARY MAXIMUM PERFORMANCE session active--备库信息
set linesize 999
select name,open_mode,database_role,protection_mode,switchover_status from v$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
topicis READ ONLY PHYSICAL STANDBY MAXIMUM PERFORMANCE NOT ALLOWED
查告警日志
tail -200f $ORACLE_BASE/diag/rdbms/btopicis/btopicis/trace/alert_btopicis.log
Fri Oct 06 22:05:03 2023
Managed Standby Recovery starting Real Time Apply
Warning: Recovery target destination is in a sibling branch在同级分支中
of the controlfile checkpoint. Recovery will only recover
changes to datafiles.
Datafile 1 (ckpscn 212602019) is orphaned on incarnation#=2 孤儿化身
MRP0: Detected orphaned datafiles!
Recovery will possibly be retried after flashback...
Errors in file /gs/u01/app/oracle/diag/rdbms/btopicis/btopicis/trace/btopicis_pr00_742.trc:
ORA-19909: datafile 1 belongs to an orphan incarnation 孤儿化身
ORA-01110: data file 1: '/gs/oradata/btopicis/system01.dbf'
Managed Standby Recovery not using Real Time Apply
Recovery Slave PR00 previously exited with exception 19909
查主备化身
主库
备库
解决办法1
重置备库化身和主库保持一致
将备库置为mount,然后reset database to incarnation 3;
--备库
shutdown immediate
startup mount
rman target /
reset database to incarnation 3;
list incarnation;
打开备库和备库开启日志应用
#开启备库
alter database open;
#备库开启日志实时应用
alter database recover managed standby database using current logfile disconnect from session;
#查看进程
select pid,process, status, thread#,sequence#, block#, blocks from v$managed_standby;
查主备库状态
--主库信息 switchover显示to standby或者session active为正常
set linesize 999
select name,open_mode,database_role,protection_mode,switchover_status from v$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
topicis READ WRITE PRIMARY MAXIMUM PERFORMANCE session active--备库信息
set linesize 999
select name,open_mode,database_role,protection_mode,switchover_status from v$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
btopicis READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE NOT ALLOWED
注意事项
备库必须mount下,不然会报错
解决办法2
将备库重新和主库同步
#备库关库
shutdown immediate
#启动到nomount
startup nomount
#主库上操作 使用RMAN auxiliary恢复数据库,会覆盖原来的数据文件日志文件控制文件密码文件,原有文件不用删除
rman target 'sys/"top@123"'@fuwa auxiliary 'sys/"top@123"'@wafu
duplicate target database for standby from active database nofilenamecheck;
#备库上操作 打开备库
alter database open;
select open_mode from v$database; #READ ONLY
备库启用日志实时应用
备库open read only用
alter database recover managed standby database parallel 10 using current logfile disconnect from session;
#查看主备状态
--主库信息 switchover显示to standby或者session active为正常
set linesize 999
select name,open_mode,database_role,protection_mode,switchover_status from v$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
topicis READ WRITE PRIMARY MAXIMUM PERFORMANCE TO STANDBY--备库信息
set linesize 999
select name,open_mode,database_role,protection_mode,switchover_status from v$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS
topicis READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE NOT ALLOWED
补充1:系统时间和scn时间不一致
#查看当前延迟情况(比较时间和服务器时间)
SELECT name, value, datum_time, time_computed FROM V$DATAGUARD_STATS WHERE name like 'apply_lag';
#监控数据库recovey操作,以及所需时间的估算
select * from v$recovery_progress;
v$recovery_progress视图可以用来监控数据库recovey操作,以及所需时间的估算,此外,可以用来监控physical standbr环的redo应用速度.
#系统时间
date
#scn时间
select scn_to_timestamp(current_scn) from v$database;
解决办法
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
!date -s 时间
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
补充2:Oracle 中的 Incarnation 到底是个什么?实验操作篇 - askscuti - 博客园
参考链接:https://blog.csdn.net/hezuijiudexiaobai/article/details/123899317
这篇关于DG11g备库ORA-19909 ORA-01110的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!