标签云
asm 恢复 asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (100)
- 数据库 (1,603)
- DB2 (22)
- MySQL (70)
- Oracle (1,468)
- Data Guard (50)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (13)
- ORACLE 21C (3)
- Oracle 23ai (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (47)
- Oracle 安全 (6)
- Oracle 开发 (27)
- Oracle 监听 (27)
- Oracle备份恢复 (532)
- Oracle安装升级 (84)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- WINDOWS 下用dg broker搭建ADG(单机to单机)
- 存储故障后oracle报—ORA-01122/ORA-01207故障处理
- Oracle 23ai rm redo*.log恢复
- Oracle 发布计划—包含Oracle 23ai版本
- Oracle 23ai 变化之—-默认数据文件变为bigfile
- PostgreSQL解析wal日志之—walminer
- Oracle 19c/21c最新patch信息-202404
- PostgreSQL恢复系列:pg_filedump批量处理
- PostgreSQL部分主要字典信息
- PostgreSQL恢复系列:pg_filedump恢复字典构造
- PostgreSQL 16 源码安装
- ORA-00742 ORA-00312 恢复
- 数据库open成功后报ORA-00353 ORA-00354错误引起的一系列问题(本质ntfs文件系统异常)
- ORA-600 ktsiseginfo1故障
- ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因
- 最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视
- ORA-600 2662快速恢复之Patch scn工具
- TNS-12518: TNS:listener could not hand off client connection
- ora.storage无法启动报ORA-12514故障处理
- 断电引起文件scn异常数据库恢复
分类目录归档:Data Guard
ORA-10485故障解决
今天对主库进行打了psu和ojvm成功之后,对standby库打上了psu和ojvm,然后给备库启动mrp进程发现余下报错
Fri Apr 16 23:18:54 2021 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION Attempt to start background Managed Standby Recovery process (orcl1) Fri Apr 16 23:18:54 2021 MRP0 started with pid=32, OS id=15961 MRP0: Background Managed Standby Recovery process started (orcl1) started logmerger process Fri Apr 16 23:18:59 2021 Managed Standby Recovery starting Real Time Apply Parallel Media Recovery started with 32 slaves Waiting for all non-current ORLs to be archived... All non-current ORLs have been archived. Media Recovery Log /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_1_63308_j7m6domc_.arc Media Recovery Log /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_2_41599_j7m70kyx_.arc Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION Media Recovery Log /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_1_63309_j7m6dop8_.arc Media Recovery Log /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_1_63310_j7m70fmp_.arc Media Recovery Log /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_1_63311_j7m70hlj_.arc Media Recovery Log /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_1_63312_j7m75cnk_.arc Media Recovery Log /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_2_41600_j7m75b77_.arc Media Recovery Log /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_2_41601_j7m75bcx_.arc Media Recovery Log /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_1_63313_j7m7vs0h_.arc Media Recovery Log /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_2_41602_j7m7vn3g_.arc Media Recovery Log /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_2_41603_j7m7vq2b_.arc Media Recovery Log /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_2_41604_j7m81g5r_.arc Media Recovery Log /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_1_63314_j7m81cz2_.arc Media Recovery Log /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_1_63315_j7m81d31_.arc Fri Apr 16 23:19:10 2021 Media Recovery Log /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_2_41605_j7m8vwz5_.arc Media Recovery Log /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_2_41606_j7m8vzcr_.arc Media Recovery Log /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_1_63316_j7m8vxgm_.arc Media Recovery Log /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_2_41607_j7m8vzfk_.arc Media Recovery Log /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_1_63317_j7m8w213_.arc Errors with log /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_1_63317_j7m8w213_.arc MRP0: Background Media Recovery terminated with error 10485 Errors in file /u01/app/oracle/diag/rdbms/orcldg/orcl1/trace/orcl1_pr00_15963.trc: ORA-10485: Real-Time Query cannot be enabled while applying migration redo. Managed Standby Recovery not using Real Time Apply Recovery interrupted! Fri Apr 16 23:19:12 2021 MRP0: Background Media Recovery process shutdown (orcl1)
由于redo中有migration操作,导致备库mrp应用进程失败,解决此类问题最简单的方法,就是把备库重启到mount状态,然后使用rman进行恢复操作,然后再继续open库,启动mrp解决这类问题
oracle@localhost trace]$ ss SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 16 23:24:55 2021 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount; ORACLE instance started. Total System Global Area 4.2758E+10 bytes Fixed Size 2262656 bytes Variable Size 5905582464 bytes Database Buffers 3.6776E+10 bytes Redo Buffers 74420224 bytes Database mounted. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@localhost trace]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Fri Apr 16 23:25:12 2021 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1442395283, not open) RMAN> recover database; Starting recover at 16-APR-21 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=143 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=286 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=428 device type=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: SID=570 device type=DISK starting media recovery archived log for thread 1 with sequence 63317 is already on disk as file /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_1_63317_j7m8w213_.arc archived log for thread 1 with sequence 63318 is already on disk as file /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_1_63318_j7m932ms_.arc archived log for thread 1 with sequence 63319 is already on disk as file /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_1_63319_j7m93719_.arc archived log for thread 1 with sequence 63320 is already on disk as file /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_1_63320_j7m96b3g_.arc archived log for thread 2 with sequence 41607 is already on disk as file /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_2_41607_j7m8vzfk_.arc archived log for thread 2 with sequence 41608 is already on disk as file /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_2_41608_j7m963jp_.arc archived log for thread 2 with sequence 41609 is already on disk as file /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_2_41609_j7m967wm_.arc archived log file name=/u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_1_63317_j7m8w213_.arc thread=1 sequence=63317 archived log file name=/u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_2_41607_j7m8vzfk_.arc thread=2 sequence=41607 archived log file name=/u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_1_63318_j7m932ms_.arc thread=1 sequence=63318 archived log file name=/u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_1_63319_j7m93719_.arc thread=1 sequence=63319 archived log file name=/u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_1_63320_j7m96b3g_.arc thread=1 sequence=63320 archived log file name=/u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_2_41608_j7m963jp_.arc thread=2 sequence=41608 archived log file name=/u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_2_41609_j7m967wm_.arc thread=2 sequence=41609 unable to find archived log archived log thread=2 sequence=41610 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 04/16/2021 23:25:25 RMAN-06054: media recovery requesting unknown archived log for thread 2 with sequence 41610 and starting SCN of 15880064787
SQL> alter database open; Database altered. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; Database altered.
Fri Apr 16 23:26:45 2021 Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION Fri Apr 16 23:26:47 2021 RFS[2]: Assigned to RFS process 16538 RFS[2]: Opened log for thread 2 sequence 41611 dbid 1442395283 branch 914614547 Archived Log entry 54459 added for thread 2 sequence 41611 rlc 914614547 ID 0x5b2c46ea dest 2: Media Recovery Log /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_2_41611_j7mc5qcc_.arc Media Recovery Waiting for thread 2 sequence 41612 Fetching gap sequence in thread 2, gap sequence 41612-41612 Fri Apr 16 23:26:50 2021 RFS[1]: Opened log for thread 2 sequence 41612 dbid 1442395283 branch 914614547 Archived Log entry 54460 added for thread 2 sequence 41612 rlc 914614547 ID 0x5b2c46ea dest 2: Media Recovery Log /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_2_41612_j7mc5ttr_.arc Media Recovery Waiting for thread 2 sequence 41613 Fetching gap sequence in thread 2, gap sequence 41613-41613 RFS[1]: Opened log for thread 2 sequence 41613 dbid 1442395283 branch 914614547 Archived Log entry 54461 added for thread 2 sequence 41613 rlc 914614547 ID 0x5b2c46ea dest 2: Fri Apr 16 23:26:55 2021 Media Recovery Log /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_2_41613_j7mc5y6k_.arc Media Recovery Waiting for thread 1 sequence 63323 Deleted Oracle managed file /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_03_25/o1_mf_2_40942_j5s61m0d_.arc RFS[1]: Opened log for thread 1 sequence 63323 dbid 1442395283 branch 914614547 Archived Log entry 54462 added for thread 1 sequence 63323 rlc 914614547 ID 0x5b2c46ea dest 2: Media Recovery Log /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_1_63323_j7mc61gg_.arc Media Recovery Waiting for thread 1 sequence 63324 Fri Apr 16 23:28:20 2021 RFS[3]: Assigned to RFS process 16545 RFS[3]: Opened log for thread 2 sequence 41615 dbid 1442395283 branch 914614547 Fri Apr 16 23:28:20 2021 RFS[4]: Assigned to RFS process 16543 RFS[4]: Opened log for thread 1 sequence 63326 dbid 1442395283 branch 914614547 Fri Apr 16 23:28:20 2021 RFS[5]: Assigned to RFS process 16551 RFS[5]: Opened log for thread 1 sequence 63324 dbid 1442395283 branch 914614547 Fri Apr 16 23:28:20 2021 RFS[6]: Assigned to RFS process 16549 RFS[6]: Opened log for thread 2 sequence 41614 dbid 1442395283 branch 914614547 Fri Apr 16 23:28:20 2021 RFS[7]: Assigned to RFS process 16547 RFS[7]: Opened log for thread 1 sequence 63325 dbid 1442395283 branch 914614547 Archived Log entry 54463 added for thread 2 sequence 41615 rlc 914614547 ID 0x5b2c46ea dest 2: Fri Apr 16 23:28:20 2021 RFS[8]: Assigned to RFS process 16553 RFS[8]: Opened log for thread 2 sequence 41616 dbid 1442395283 branch 914614547 Archived Log entry 54464 added for thread 1 sequence 63326 rlc 914614547 ID 0x5b2c46ea dest 2: Archived Log entry 54465 added for thread 2 sequence 41616 rlc 914614547 ID 0x5b2c46ea dest 2: Archived Log entry 54466 added for thread 1 sequence 63325 rlc 914614547 ID 0x5b2c46ea dest 2: RFS[3]: Opened log for thread 2 sequence 41617 dbid 1442395283 branch 914614547 RFS[8]: Opened log for thread 2 sequence 41618 dbid 1442395283 branch 914614547 RFS[4]: Opened log for thread 1 sequence 63327 dbid 1442395283 branch 914614547 Archived Log entry 54467 added for thread 2 sequence 41617 rlc 914614547 ID 0x5b2c46ea dest 2: Archived Log entry 54468 added for thread 1 sequence 63327 rlc 914614547 ID 0x5b2c46ea dest 2: Archived Log entry 54469 added for thread 2 sequence 41618 rlc 914614547 ID 0x5b2c46ea dest 2: Archived Log entry 54470 added for thread 1 sequence 63324 rlc 914614547 ID 0x5b2c46ea dest 2: Archived Log entry 54471 added for thread 2 sequence 41614 rlc 914614547 ID 0x5b2c46ea dest 2: Fri Apr 16 23:28:20 2021 Media Recovery Log /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_1_63324_j7mc8n1n_.arc Media Recovery Log /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_2_41614_j7mc8n1r_.arc Media Recovery Log /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_2_41615_j7mc8n03_.arc Media Recovery Log /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_2_41616_j7mc8n2n_.arc Media Recovery Log /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_2_41617_j7mc8n6m_.arc Media Recovery Log /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_2_41618_j7mc8n6p_.arc Media Recovery Log /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_1_63325_j7mc8n21_.arc Media Recovery Log /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_1_63326_j7mc8n11_.arc Media Recovery Log /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_1_63327_j7mc8n6t_.arc Media Recovery Waiting for thread 1 sequence 63328 RFS[5]: Selected log 20 for thread 1 sequence 63328 dbid 1442395283 branch 914614547 Fri Apr 16 23:28:23 2021 Primary database is in MAXIMUM PERFORMANCE mode Re-archiving standby log 20 thread 1 sequence 63328 Fri Apr 16 23:28:23 2021 Archived Log entry 54472 added for thread 1 sequence 63328 ID 0x5b2c46ea dest 1: RFS[9]: Assigned to RFS process 16555 RFS[9]: Selected log 20 for thread 1 sequence 63329 dbid 1442395283 branch 914614547 Media Recovery Log /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_1_63328_j7mc8q4n_.arc Media Recovery Waiting for thread 2 sequence 41619 Fri Apr 16 23:28:24 2021 Primary database is in MAXIMUM PERFORMANCE mode RFS[10]: Assigned to RFS process 16557 RFS[10]: Selected log 30 for thread 2 sequence 41620 dbid 1442395283 branch 914614547 Fri Apr 16 23:28:24 2021 RFS[11]: Assigned to RFS process 16559 RFS[11]: Selected log 31 for thread 2 sequence 41619 dbid 1442395283 branch 914614547 Fri Apr 16 23:28:24 2021 Archived Log entry 54473 added for thread 2 sequence 41619 ID 0x5b2c46ea dest 1: Media Recovery Log /u01/nfs/fast_recovery_area/ORCLDG/archivelog/2021_04_16/o1_mf_2_41619_j7mc8rdz_.arc Media Recovery Waiting for thread 1 sequence 63329 (in transit) Recovery of Online Redo Log: Thread 1 Group 20 Seq 63329 Reading mem 0 Mem# 0: /u01/app/oradata/orcl/std_redo20.log Media Recovery Waiting for thread 2 sequence 41620 (in transit) Recovery of Online Redo Log: Thread 2 Group 30 Seq 41620 Reading mem 0 Mem# 0: /u01/app/oradata/orcl/std_redo30.log
mos上有类似文章供参考:MRP process getting terminated with error ORA-10485 (Doc ID 1618485.1)
使用rman from service 搭建dataguard
从oracle 12c开始提供了rman通过from service方式搭建dg,使用12c长期支持版19c(并打上最新的patch)
配置dataguard相关参数(主备库有稍微不同)
alter system set db_unique_name='XIFENFEI' scope=spfile; alter system set service_names='XIFENFEI'; alter system set log_archive_config='dg_config=(XIFENFEI,XIFENFEIDG)'; alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=XIFENFEI'; alter system set log_archive_dest_2='service=XIFENFEIDG lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=XIFENFEIDG'; alter system set standby_file_management=auto; alter system set db_file_name_convert='/u01/app/oracle/oradata/XIFENFEI/', '/u01/app/oracle/oradata/XIFENFEI/' scope=spfile; alter system set log_file_name_convert='/u01/app/oracle/oradata/XIFENFEI/', '/u01/app/oracle/oradata/XIFENFEI/' scope=spfile; alter system set fal_server=XIFENFEIDG;
配置tnsnames.ora
XIFENFEI = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.238)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = XIFENFEI) ) ) XIFENFEIDG = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.124)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = XIFENFEI) ) )
拷贝主库密码文件到备库
[oracle@primary ~]$ scp $ORACLE_HOME/dbs/orapwXIFENFEI 192.168.0.124:$ORACLE_HOME/dbs/ The authenticity of host '192.168.0.124 (192.168.0.124)' can't be established. ECDSA key fingerprint is SHA256:NI2952z4Bqc3M/B+AK7EJRiJNauROIyluvu1l4NSTX0. ECDSA key fingerprint is MD5:1d:64:dd:ef:1c:ad:ed:cf:70:22:2d:4d:7c:90:5e:5e. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.0.124' (ECDSA) to the list of known hosts. oracle@192.168.0.124's password: orapwXIFENFEI 100% 2048 6.6MB/s 00:00 [oracle@primary ~]$
备库启动到nomount状态
[oracle@standby ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 13 20:32:34 2021 Version 19.10.0.0.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to an idle instance. SQL> create spfile from pfile='/tmp/pfile'; File created. SQL> startup nomount pfile='/tmp/pfile' ORACLE instance started. Total System Global Area 4294963264 bytes Fixed Size 8904768 bytes Variable Size 805306368 bytes Database Buffers 3472883712 bytes Redo Buffers 7868416 bytes SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.10.0.0.0
rman from service方式创建standby ctl和还原数据文件
[oracle@standby ~]$ rman target / Recovery Manager: Release 19.0.0.0.0 - Production on Sat Mar 13 20:34:37 2021 Version 19.10.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: XIFENFEI (not mounted) RMAN> restore standby controlfile from service XIFENFEI; Starting restore at 13-MAR-21 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=9 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service XIFENFEI channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/u01/app/oracle/oradata/XIFENFEI/control01.ctl output file name=/u01/app/oracle/fast_recovery_area/XIFENFEI/control02.ctl Finished restore at 13-MAR-21 RMAN> alter database mount; released channel: ORA_DISK_1 Statement processed RMAN> restore database from service XIFENFEI; Starting restore at 13-MAR-21 Starting implicit crosscheck backup at 13-MAR-21 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=12 device type=DISK Finished implicit crosscheck backup at 13-MAR-21 Starting implicit crosscheck copy at 13-MAR-21 using channel ORA_DISK_1 Finished implicit crosscheck copy at 13-MAR-21 searching for all files in the recovery area cataloging files... no files cataloged using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service XIFENFEI channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/XIFENFEI/system01.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service XIFENFEI channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/XIFENFEI/sysaux01.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service XIFENFEI channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/XIFENFEI/undotbs01.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service XIFENFEI channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/XIFENFEI/users01.dbf channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 13-MAR-21
备库启动mrp进程
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; Database altered.
2021-03-13T20:54:08.075418+08:00 Attempt to start background Managed Standby Recovery process (XIFENFEI) Starting background process MRP0 2021-03-13T20:54:08.086269+08:00 MRP0 started with pid=56, OS id=8182 2021-03-13T20:54:08.087276+08:00 Background Managed Standby Recovery process started (XIFENFEI) 2021-03-13T20:54:13.104757+08:00 Started logmerger process 2021-03-13T20:54:13.112058+08:00 IM on ADG: Start of Empty Journal IM on ADG: End of Empty Journal PR00 (PID:8188): Managed Standby Recovery starting Real Time Apply 2021-03-13T20:54:13.205668+08:00 Parallel Media Recovery started with 4 slaves 2021-03-13T20:54:13.216576+08:00 Stopping change tracking PR00 (PID:8188): Media Recovery Waiting for T-1.S-25 (in transit) 2021-03-13T20:54:13.269138+08:00 Recovery of Online Redo Log: Thread 1 Group 12 Seq 25 Reading mem 0 Mem# 0: /u01/app/oracle/oradata/XIFENFEI/s_redo12.log
至此dataguard基本上搭建完成
ORA-04020导致adg异常
今日早上有客户反馈adg停止同步了,通过检查alert日志发现
Tue Dec 24 18:17:41 2019 Media Recovery Waiting for thread 1 sequence 56655 (in transit) Recovery of Online Redo Log: Thread 1 Group 11 Seq 56655 Reading mem 0 Mem# 0: Y:\ORACLE\ORADATA\ORACLE11\STD_REDO11.LOG Archived Log entry 56248 added for thread 1 sequence 56654 ID 0x5b6bcf9b dest 1: Tue Dec 24 18:18:11 2019 Errors in file C:\APP\ADMINISTRATOR\diag\rdbms\oracle11dg\oracle11\trace\oracle11_lgwr_3252.trc: ORA-04020: deadlock detected while trying to lock object SYS.orcl LGWR (ospid: 3252): terminating the instance due to error 4020 Tue Dec 24 18:18:11 2019 System state dump requested by (instance=1, osid=3252 (LGWR)), summary=[abnormal instance termination]. System State dumped to trace file C:\APP\ADMINISTRATOR\diag\rdbms\oracle11dg\trace\oracle11_diag_3236_20191224181811.trc Dumping diagnostic data in directory=[cdmp_20191224181811], requested by (instance=1, osid=3252 (LGWR)), summary=[abnormal instance termination]. Instance terminated by LGWR, pid = 3252
由于lgwr进程遭遇ORA-04020,从而使得lgwr进程异常,进而整个数据库crash.
分析trace文件
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Windows NT Version V6.1 Service Pack 1 CPU : 8 - type 8664, 8 Physical Cores Process Affinity : 0x0x0000000000000000 Memory (Avail/Total): Ph:8395M/32733M, Ph+PgF:41002M/65464M Instance name: oracle11 Redo thread mounted by this instance: 1 Oracle process number: 10 Windows thread id: 3252, image: ORACLE.EXE (LGWR) *** 2019-12-24 18:18:11.072 *** SESSION ID:(384.1) 2019-12-24 18:18:11.072 *** CLIENT ID:() 2019-12-24 18:18:11.072 *** SERVICE NAME:(SYS$BACKGROUND) 2019-12-24 18:18:11.072 *** MODULE NAME:() 2019-12-24 18:18:11.072 *** ACTION NAME:() 2019-12-24 18:18:11.072 A deadlock among DDL and parse locks is detected. This deadlock is usually due to user errors in the design of an application or from issuing a set of concurrent statements which can cause a deadlock. This should not be reported to Oracle Support. The following information may aid in finding the errors which cause the deadlock: ORA-04020: deadlock detected while trying to lock object SYS.orcl -------------------------------------------------------- object waiting waiting blocking blocking handle session lock mode session lock mode -------- -------- -------- ---- -------- -------- ---- 0000000676C20F08 000000066D22BE10 00000006738AB970 X 000000066D22BE10 00000006738A04B0 S 0000000677DF2E80 00000006792E2880 0000000673B13AE8 X 000000066D22BE10 00000006738A19B8 S -------------------------------------------------------- ---------- DUMP OF WAITING AND BLOCKING LOCKS ---------- -------------------------------------------------------- ------------- WAITING LOCK ------------- ---------------------------------------- SO: 0x00000006738AB970, type: 78, owner: 0x000000065D440498, flag: INIT/-/-/0x00 if: 0x3 c: 0x3 proc=0x000000066CDE7AD0, name=LIBRARY OBJECT LOCK, file=kgl.h LINE:8751, pg=0 LibraryObjectLock:Address=00000006738AB970 Handle=0000000676C20F08 RequestMode=X CanBeBrokenCount=2 Incarnation=3 ExecutionCount=0 ……………… SO: 0x00000006738A19B8, type: 78, owner: 0x000000065A38D6C0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3 proc=0x000000066CDE7AD0, name=LIBRARY OBJECT LOCK, file=kgl.h LINE:8751, pg=0 LibraryObjectLock:Address=00000006738A19B8 Handle=0000000677DF2E80 Mode=S CanBeBrokenCount=1 Incarnation=1 ExecutionCount=0 User=000000066D22BE10 Session=000000066D22BE10 ReferenceCount=1 Flags=CNB/[0001] SavepointNum=1b749 LibraryHandle: Address=0000000677DF2E80 Hash=675351da LockMode=S PinMode=0 LoadLockMode=0 Status=0 ObjectName: Name=SYS.orcl FullHashValue=285b654fe3f440652c403c98675351da Namespace=DBINSTANCE(74) Type=CURSOR(00) Identifier=1 OwnerIdn=0 Statistics: InvalidationCount=0 ExecutionCount=0 LoadCount=0 ActiveLocks=1 TotalLockCount=74719 TotalPinCount=0 Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0 BucketInUse=2 HandleInUse=2 HandleReferenceCount=0 Concurrency: DependencyMutex=0000000677DF2F30(0, 0, 0, 0) Mutex=0000000677DF2FC0(0, 149471, 1, 0) Flags=RON/PIN/KEP/BSO/[00810003] WaitersLists: Lock=0000000677DF2F10[0000000673B13B58,000000067382E2F0] Pin=0000000677DF2EF0[0000000677DF2EF0,0000000677DF2EF0] LoadLock=0000000677DF2F68[0000000677DF2F68,0000000677DF2F68] Timestamp: HandleReference: Address=0000000677DF3030 Handle=0000000000000000 Flags=[00] --------------------------------- This lock request was aborted. error 4020 detected in background process ORA-04020: deadlock detected while trying to lock object SYS.orcl kjzduptcctx: Notifying DIAG for crash event ----- Abridged Call Stack Trace ----- ksedsts()+585<-kjzdssdmp()+329<-kjzduptcctx()+288<-kjzdicrshnfy()+99<-ksuitm()+1525<-ksbrdp()+4578<-opirip() +853<-opidrv()+906<-sou2o()+98<-opimai_real()+280<-opimai()+191 <-BackgroundThreadStart()+646<-0000000076CF59CD<-0000000076E2A561 ----- End of Abridged Call Stack Trace ----- *** 2019-12-24 18:18:11.165 LGWR (ospid: 3252): terminating the instance due to error 4020 *** 2019-12-24 18:18:17.483 ksuitm: waiting up to [5] seconds before killing DIAG(3236)
日志显示由于lgwr进程等待LIBRARY OBJECT LOCK超时,从而引起异常,根据经验此类问题一般是由于bug导致,查询mos发现匹配bug信息Bug 18515268 ORA-4020 in ADG Standby Database causing instance crash by LGWR
可以根据需要打上相关Patch 18515268: ACTIVE DATAGUARD STANDBY CRASHES DUE TO AN ORA-4020 ENCOUNTERED BY LGWR