利用flashback快速恢复failover 的备库

客户数据库架构为单机+dataguard,一台生产库跑在物理机,备库跑在虚拟化环境中(当时由于成本原因使用了机械盘),今天物理机突然直接罢工,客户要求紧急切换备库

Thu Aug 08 09:52:13 2024
Media Recovery Waiting for thread 1 sequence 189448 (in transit)
Recovery of Online Redo Log: Thread 1 Group 12 Seq 189448 Reading mem 0
  Mem# 0: /oradata/xff/std_redo12.log
Thu Aug 08 09:52:13 2024
Archived Log entry 187514 added for thread 1 sequence 189447 ID 0x2e6bc37f dest 1:
Thu Aug 08 10:54:40 2024
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH force
Terminal Recovery: Stopping real time apply
Thu Aug 08 10:54:40 2024
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/xffdg/xff/trace/xff_pr00_17876.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 34188310512
Thu Aug 08 10:54:43 2024
MRP0: Background Media Recovery process shutdown (xff)
Terminal Recovery: Stopped real time apply
Thu Aug 08 10:55:14 2024
Stopping background process MMNL
Stopping background process MMON
Thu Aug 08 10:55:46 2024
Background process MMON not dead after 30 seconds
Killing background process MMON
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
Active process 17691 user 'oracle' program 'oracle@xffDG (MMON)'
Active process 15077 user 'oracle' program 'oracle@xffDG'
Active process 17691 user 'oracle' program 'oracle@xffDG (MMON)'
Active process 11536 user 'oracle' program 'oracle@xffDG (M000)'
Active process 17691 user 'oracle' program 'oracle@xffDG (MMON)'
Active process 15077 user 'oracle' program 'oracle@xffDG'
Active process 11536 user 'oracle' program 'oracle@xffDG (M000)'
Active process 11536 user 'oracle' program 'oracle@xffDG (M000)'
Active process 11536 user 'oracle' program 'oracle@xffDG (M000)'
CLOSE: all sessions shutdown successfully.
Thu Aug 08 10:56:11 2024
SMON: disabling cache recovery
Attempt to do a Terminal Recovery (xff)
Media Recovery Start: Managed Standby Recovery (xff)
 started logmerger process
Thu Aug 08 10:56:13 2024
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 4 slaves
Media Recovery Waiting for thread 1 sequence 189448 (in transit)
Killing 4 processes with pids 17733,17729,17731,32533 (all RFS, wait for I/O) 
in order to disallow current and future RFS connections. Requested by OS process 15184
Thu Aug 08 10:56:16 2024
idle dispatcher 'D000' terminated, pid = (16, 1)
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
Terminal Recovery timestamp is '08/08/2024 10:56:17'
Terminal Recovery: applying standby redo logs.
Terminal Recovery: thread 1 seq# 189448 redo required
Terminal Recovery:
Recovery of Online Redo Log: Thread 1 Group 12 Seq 189448 Reading mem 0
  Mem# 0: /oradata/xff/std_redo12.log
Identified End-Of-Redo (failover) for thread 1 sequence 189448 at SCN 0xffff.ffffffff
Incomplete Recovery applied until change 34188310513 time 08/08/2024 11:32:41
Thu Aug 08 10:56:18 2024
Media Recovery Complete (xff)
Terminal Recovery: successful completion
Thu Aug 08 10:56:18 2024
ARCH: Archival stopped, error occurred. Will continue retrying
Forcing ARSCN to IRSCN for TR 7:4123539441
ORACLE Instance xff - Archival Error
Attempt to set limbo arscn 7:4123539441 irscn 7:4123539441
Resetting standby activation ID 778814335 (0x2e6bc37f)
ORA-16014: log 12 sequence# 189448 not archived, no available destinations
ORA-00312: online log 12 thread 1: '/oradata/xff/std_redo12.log'
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH force
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
ORA-16136 signalled during: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL...
Thu Aug 08 10:56:28 2024
ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE
ALTER DATABASE ACTIVATE [PHYSICAL] STANDBY DATABASE (xff)
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
Thu Aug 08 10:56:28 2024
Archiver process freed from errors. No longer stopped
Standby terminal recovery start SCN: 34188310512
RESETLOGS after incomplete recovery UNTIL CHANGE 34188310513
Online log /oradata/xff/redo01.log: Thread 1 Group 1 was previously cleared
Online log /oradata/xff/redo02.log: Thread 1 Group 2 was previously cleared
Online log /oradata/xff/redo03.log: Thread 1 Group 3 was previously cleared
Online log /oradata/xff/redo04.log: Thread 1 Group 4 was previously cleared
Standby became primary SCN: 34188310511
Thu Aug 08 10:56:29 2024
Setting recovery target incarnation to 3
ACTIVATE STANDBY: Complete - Database mounted as primary
Completed: ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE
ARC1: Becoming the 'no SRL' ARCH
alter database open
Thu Aug 08 10:56:34 2024
Assigning activation ID 832379854 (0x319d1bce)
Thread 1 advanced to log sequence 2 (thread open)
Thread 1 opened at log sequence 2
  Current log# 2 seq# 2 mem# 0: /oradata/xff/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Aug 08 10:56:34 2024
SMON: enabling cache recovery
Thu Aug 08 10:56:34 2024
ARC0: LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_2 after log switch
Thu Aug 08 10:56:34 2024
NSA2 started with pid=14, OS id=15198
[15133] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:1087824580 end:1087828220 diff:3640 (36 seconds)
Dictionary check beginning
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Thu Aug 08 10:56:38 2024
Database Characterset is ZHS16GBK
Starting background process SMCO
Thu Aug 08 10:56:39 2024
SMCO started with pid=15, OS id=15200
Thread 1 advanced to log sequence 3 (LGWR switch)
  Current log# 3 seq# 3 mem# 0: /oradata/xff/redo03.log
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Thu Aug 08 10:56:40 2024
Archived Log entry 187515 added for thread 1 sequence 2 ID 0x319d1bce dest 1:
Starting background process QMNC
Thu Aug 08 10:56:43 2024
QMNC started with pid=17, OS id=15204
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: alter database open

很不幸由于虚拟机资源io太差,无法接管业务,硬件工程师紧急修复好物理机,启动数据库正常,客户直接把业务又切换到物理机中,现在需要恢复dataguard环境(并且客户把虚拟机迁移到ssd环境中),把虚拟机数据库重启到mount状态

[oracle@xffDG ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 8 20:06:30 2024

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 2.5655E+10 bytes
Fixed Size                  2265224 bytes
Variable Size            3892318072 bytes
Database Buffers         2.1743E+10 bytes
Redo Buffers               16896000 bytes
Database mounted.
SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
MOUNTED              PRIMARY

闪回数据库到备库failover之前scn

SQL> flashback database to scn 34188310500;       

Flashback complete.

Thu Aug 08 20:09:40 2024
flashback database to scn 34188310500
Flashback Restore Start
Thu Aug 08 20:10:34 2024
Flashback Restore Complete
Flashback Media Recovery Start
Thu Aug 08 20:10:34 2024
Setting recovery target incarnation to 2
 started logmerger process
Parallel Media Recovery started with 4 slaves
Flashback Media Recovery Log /oradata/fast_recovery_area/XFF/archivelog/2024_08_08/o1_mf_1_189448_mc8dzjxn_.arc
Thu Aug 08 20:10:35 2024
Identified End-Of-Redo (failover) for thread 1 sequence 189448 at SCN 0x7.f5c837f1
Incomplete Recovery applied until change 34188310501 time 08/08/2024 11:32:40
Flashback Media Recovery Complete
Setting recovery target incarnation to 3
Completed: flashback database to scn 34188310500

切换虚拟机库到standby 状态

SQL> alter database convert to physical standby;

Database altered.

SQL> select database_role from v$database;
select database_role from v$database
                          *
ERROR at line 1:
ORA-01507: database not mounted


SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00750: database has been previously mounted and dismounted

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2.5655E+10 bytes
Fixed Size                  2265224 bytes
Variable Size            3892318072 bytes
Database Buffers         2.1743E+10 bytes
Redo Buffers               16896000 bytes
Database mounted.
SQL>  select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
MOUNTED              PHYSICAL STANDBY

Thu Aug 08 20:10:46 2024
alter database convert to physical standby
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (xff)
Flush standby redo logfile failed:1649
Clearing standby activation ID 832379854 (0x319d1bce)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 12 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl5.f' SIZE 209715200;
Shutting down archive processes
Archiving is disabled
Completed: alter database convert to physical standby

开启mrp进程

SQL> alter database open read only;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT  LOGFILE DISCONNECT FROM SESSION;

Database altered.