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