Oracle-通过(RECOVER STANDBY DATABASE FROM SERVICE)方式修复DataGuard

前言:

Oracle在18c对(RESTORE/RECOVER ...FROM SERVICE)功能进行了加强，推出了(RECOVER STANDBY DATABASE ... FROM SERVICE)通过网络服务直接增量恢复备库数据,这个新特性也大大简化了DataGuard主备的问题修复。

修复主备gap

1 当前备库缺失47-55个归档,并且没有归档日志备

SQL> select open_mode,database_role from v$database;
​
OPEN_MODE       DATABASE_ROLE
-------------------- ----------------
READ ONLY WITH APPLY PHYSICAL STANDBY
​
SQL> select  * from v$archive_gap;
​
   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#     CON_ID
---------- ------------- -------------- ----------
   1        47       55    1
​
SQL> 

2 关闭mrp进程

alter database recover managed standby database cancel;
​
---注意在数据库open的时候，要把mrp进程关闭，否则会出现以下错误
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/01/2022 17:35:51
RMAN-05150: Managed Recovery Process must be disabled before running RECOVER STANDBY DATABASE.

3 通过from service 进行recover的方式恢复

rman target sys/oracle
run
{
allocate channel c1 type disk connect '/@testdg';
allocate channel c2 type disk connect '/@testdg';
allocate channel c3 type disk connect '/@testdg';
allocate channel c4 type disk connect '/@testdg';
recover standby database from service 'testdb' ;
}
​

4 恢复的步骤

1 终止并重新启动实例到mount
License high water mark = 12
2022-11-01T17:38:11.813430+08:00
USER(prelim) (ospid: 24653): terminating the instance
2022-11-01T17:38:12.826936+08:00
Instance terminated by USER(prelim), pid = 24653
2022-11-01T17:38:16.129260+08:00
Starting ORACLE instance (normal) (OS id: 24658)
2022-11-01T17:38:16.138029+08:00
2 set standby_file_management=manual
3 重新从主库恢复新的standby control
4 切换控制文件路径到实际路径
5 进行增量recover恢复
6 set standby_file_management=auto

5 恢复日志

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=34 device type=DISK
​
allocated channel: c2
channel c2: SID=270 device type=DISK
​
allocated channel: c3
channel c3: SID=35 device type=DISK
​
allocated channel: c4
channel c4: SID=266 device type=DISK
​
Starting recover at 2022/11/01 17:38:10
Oracle instance started
​
Total System Global Area    1543500144 bytes
​
Fixed Size                     8896880 bytes
Variable Size                889192448 bytes
Database Buffers             637534208 bytes
Redo Buffers                   7876608 bytes
​
contents of Memory Script:
{
   restore standby controlfile from service  'testdb';
   alter database mount standby database;
}
executing Memory Script
​
Starting restore at 2022/11/01 17:38:23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=253 device type=DISK
​
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service testdb
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/TESTDG/controlfile/control01.ctl
Finished restore at 2022/11/01 17:38:26
​
released channel: ORA_DISK_1
Statement processed
Executing: alter system set standby_file_management=manual
​
contents of Memory Script:
{
set newname for tempfile  1 to 
 "/u01/app/oracle/oradata/TESTDG/datafile/o1_mf_temp_kp1q02wb_.tmp";
   switch tempfile all;
set newname for datafile  1 to 
 "/u01/app/oracle/oradata/TESTDG/datafile/o1_mf_system_kp1phykz_.dbf";
set newname for datafile  3 to 
 "/u01/app/oracle/oradata/TESTDG/datafile/o1_mf_sysaux_kp1phyo1_.dbf";
set newname for datafile  4 to 
 "/u01/app/oracle/oradata/TESTDG/datafile/o1_mf_undotbs1_kp1phyt2_.dbf";
set newname for datafile  7 to 
 "/u01/app/oracle/oradata/TESTDG/datafile/o1_mf_users_kp1phz14_.dbf";
   catalog datafilecopy  "/u01/app/oracle/oradata/TESTDG/datafile/o1_mf_system_kp1phykz_.dbf", 
 "/u01/app/oracle/oradata/TESTDG/datafile/o1_mf_sysaux_kp1phyo1_.dbf", 
 "/u01/app/oracle/oradata/TESTDG/datafile/o1_mf_undotbs1_kp1phyt2_.dbf", 
 "/u01/app/oracle/oradata/TESTDG/datafile/o1_mf_users_kp1phz14_.dbf";
   switch datafile all;
}
executing Memory Script
​
executing command: SET NEWNAME
​
renamed tempfile 1 to /u01/app/oracle/oradata/TESTDG/datafile/o1_mf_temp_kp1q02wb_.tmp in control file
​
executing command: SET NEWNAME
​
executing command: SET NEWNAME
​
executing command: SET NEWNAME
​
executing command: SET NEWNAME
​
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/TESTDG/datafile/o1_mf_system_kp1phykz_.dbf RECID=1 STAMP=1119634711
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/TESTDG/datafile/o1_mf_sysaux_kp1phyo1_.dbf RECID=2 STAMP=1119634711
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/TESTDG/datafile/o1_mf_undotbs1_kp1phyt2_.dbf RECID=3 STAMP=1119634711
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/TESTDG/datafile/o1_mf_users_kp1phz14_.dbf RECID=4 STAMP=1119634711
​
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1119634711 file name=/u01/app/oracle/oradata/TESTDG/datafile/o1_mf_system_kp1phykz_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1119634711 file name=/u01/app/oracle/oradata/TESTDG/datafile/o1_mf_sysaux_kp1phyo1_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1119634711 file name=/u01/app/oracle/oradata/TESTDG/datafile/o1_mf_undotbs1_kp1phyt2_.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=1119634711 file name=/u01/app/oracle/oradata/TESTDG/datafile/o1_mf_users_kp1phz14_.dbf
​
contents of Memory Script:
{
  recover database from service  'testdb';
}
executing Memory Script
​
Starting recover at 2022/11/01 17:38:31
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=260 device type=DISK
RMAN-06900: warning: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: warning: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
Oracle error from target database: 
ORA-19922: there is no parent row with id 0 and level 2
​
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service testdb
destination for restore of datafile 00001: /u01/app/oracle/oradata/TESTDG/datafile/o1_mf_system_kp1phykz_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service testdb
destination for restore of datafile 00003: /u01/app/oracle/oradata/TESTDG/datafile/o1_mf_sysaux_kp1phyo1_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service testdb
destination for restore of datafile 00004: /u01/app/oracle/oradata/TESTDG/datafile/o1_mf_undotbs1_kp1phyt2_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service testdb
destination for restore of datafile 00007: /u01/app/oracle/oradata/TESTDG/datafile/o1_mf_users_kp1phz14_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
​
starting media recovery
​
media recovery complete, elapsed time: 00:00:00
Finished recover at 2022/11/01 17:38:39
Executing: alter system set standby_file_management=auto
Finished recover at 2022/11/01 17:38:39

6 重建standby log

---因为standby log的路径是主库的，可能跟实际的不一致
select 'alter database drop standby logfile group '||group#||';'
from v$standby_log;
​
alter database ADD standby logfile thread 1 group 21 ('/u01/app/oracle/oradata/TESTDG/onlinelog/redo01.log') size 200M ;
alter database ADD standby logfile thread 1 group 22 ('/u01/app/oracle/oradata/TESTDG/onlinelog/redo02.log') size 200M ; 
alter database ADD standby logfile thread 1 group 23 ('/u01/app/oracle/oradata/TESTDG/onlinelog/redo03.log') size 200M ; 
alter database ADD standby logfile thread 1 group 24 ('/u01/app/oracle/oradata/TESTDG/onlinelog/redo04.log') size 200M ;

7 启动数据库恢复同步

alter database open read ONLY;
alter database recover managed standby database using current logfile disconnect from session;

修复备库数据文件坏块问题

1 备库有时会遇到由于主库没有设置force logging导致的坏块问题

Errors in file /u01/app/oracle/diag/rdbms/testdg/testdg/trace/testdg_ora_4978.trc  (incident=10025):
ORA-01578: ORACLE data block corrupted (file # 5, block # 368)
ORA-01110: data file 5: '/u01/app/oracle/oradata/TESTDG/datafile/o1_mf_tbs_test_kp27r5t8_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
Incident details in: /u01/app/oracle/diag/rdbms/testdg/testdg/incident/incdir_10025/testdg_ora_4978_i10025.trc

2 关闭mrp进程

alter database recover managed standby database cancel;

3 将坏块问题文件手动转移到其他位置存放

mv /u01/app/oracle/oradata/TESTDG/datafile/o1_mf_tbs_test_kp27r5t8_.dbf /tmp/

4 通过from service 进行recover

rman target sys/oracle
run
{
allocate channel c1 type disk connect '/@testdg';
allocate channel c2 type disk connect '/@testdg';
allocate channel c3 type disk connect '/@testdg';
allocate channel c4 type disk connect '/@testdg';
recover standby database from service 'testdb' ;
}

5 恢复过程跟之前修改gap步骤完全一样，不需要人为的去干预

6 恢复日志

allocated channel: c1
channel c1: SID=25 device type=DISK
​
allocated channel: c2
channel c2: SID=261 device type=DISK
​
allocated channel: c3
channel c3: SID=26 device type=DISK
​
allocated channel: c4
channel c4: SID=262 device type=DISK
​
Starting recover at 2022/11/01 21:44:38
Oracle instance started
​
Total System Global Area    1543500144 bytes
​
Fixed Size                     8896880 bytes
Variable Size                889192448 bytes
Database Buffers             637534208 bytes
Redo Buffers                   7876608 bytes
​
contents of Memory Script:
{
   restore standby controlfile from service  'testdb';
   alter database mount standby database;
}
executing Memory Script
​
Starting restore at 2022/11/01 21:44:51
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=253 device type=DISK
​
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service testdb
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/TESTDG/controlfile/control01.ctl
Finished restore at 2022/11/01 21:44:54
​
released channel: ORA_DISK_1
Statement processed
Executing: alter system set standby_file_management=manual
​
contents of Memory Script:
{
set newname for tempfile  1 to 
 "/u01/app/oracle/oradata/TESTDG/datafile/o1_mf_temp_kp1q02wb_.tmp";
   switch tempfile all;
set newname for datafile  1 to 
 "/u01/app/oracle/oradata/TESTDG/datafile/o1_mf_system_kp1phykz_.dbf";
set newname for datafile  3 to 
 "/u01/app/oracle/oradata/TESTDG/datafile/o1_mf_sysaux_kp1phyo1_.dbf";
set newname for datafile  4 to 
 "/u01/app/oracle/oradata/TESTDG/datafile/o1_mf_undotbs1_kp1phyt2_.dbf";
set newname for datafile  5 to new;
set newname for datafile  7 to 
 "/u01/app/oracle/oradata/TESTDG/datafile/o1_mf_users_kp1phz14_.dbf";
   restore from service  'testdb' datafile
    5;
   catalog datafilecopy  "/u01/app/oracle/oradata/TESTDG/datafile/o1_mf_system_kp1phykz_.dbf", 
 "/u01/app/oracle/oradata/TESTDG/datafile/o1_mf_sysaux_kp1phyo1_.dbf", 
 "/u01/app/oracle/oradata/TESTDG/datafile/o1_mf_undotbs1_kp1phyt2_.dbf", 
 "/u01/app/oracle/oradata/TESTDG/datafile/o1_mf_users_kp1phz14_.dbf";
   switch datafile all;
}
executing Memory Script
​
executing command: SET NEWNAME
​
renamed tempfile 1 to /u01/app/oracle/oradata/TESTDG/datafile/o1_mf_temp_kp1q02wb_.tmp in control file
​
executing command: SET NEWNAME
​
executing command: SET NEWNAME
​
executing command: SET NEWNAME
​
executing command: SET NEWNAME
​
executing command: SET NEWNAME
​
Starting restore at 2022/11/01 21:44:59
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=260 device type=DISK
​
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service testdb
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/TESTDG/datafile/o1_mf_tbs_test_%u_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2022/11/01 21:45:01
​
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/TESTDG/datafile/o1_mf_system_kp1phykz_.dbf RECID=2 STAMP=1119649501
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/TESTDG/datafile/o1_mf_sysaux_kp1phyo1_.dbf RECID=3 STAMP=1119649501
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/TESTDG/datafile/o1_mf_undotbs1_kp1phyt2_.dbf RECID=4 STAMP=1119649501
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/TESTDG/datafile/o1_mf_users_kp1phz14_.dbf RECID=5 STAMP=1119649501
​
datafile 5 switched to datafile copy
input datafile copy RECID=1 STAMP=1119649500 file name=/u01/app/oracle/oradata/TESTDG/datafile/o1_mf_tbs_test_kp28pw2t_.dbf
datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=1119649501 file name=/u01/app/oracle/oradata/TESTDG/datafile/o1_mf_system_kp1phykz_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=1119649501 file name=/u01/app/oracle/oradata/TESTDG/datafile/o1_mf_sysaux_kp1phyo1_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=1119649501 file name=/u01/app/oracle/oradata/TESTDG/datafile/o1_mf_undotbs1_kp1phyt2_.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=5 STAMP=1119649501 file name=/u01/app/oracle/oradata/TESTDG/datafile/o1_mf_users_kp1phz14_.dbf
​
contents of Memory Script:
{
  recover database from service  'testdb';
}
executing Memory Script
​
Starting recover at 2022/11/01 21:45:01
using channel ORA_DISK_1
skipping datafile 5; already restored to SCN 2174252
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service testdb
destination for restore of datafile 00001: /u01/app/oracle/oradata/TESTDG/datafile/o1_mf_system_kp1phykz_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service testdb
destination for restore of datafile 00003: /u01/app/oracle/oradata/TESTDG/datafile/o1_mf_sysaux_kp1phyo1_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service testdb
destination for restore of datafile 00004: /u01/app/oracle/oradata/TESTDG/datafile/o1_mf_undotbs1_kp1phyt2_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service testdb
destination for restore of datafile 00007: /u01/app/oracle/oradata/TESTDG/datafile/o1_mf_users_kp1phz14_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
​
starting media recovery
​
media recovery complete, elapsed time: 00:00:00
Finished recover at 2022/11/01 21:45:08
Executing: alter system set standby_file_management=auto
Finished recover at 2022/11/01 21:45:08
​
RMAN> exit

7 重建standby log

---因为standby log的路径是主库的，可能跟实际的不一致
select 'alter database drop standby logfile group '||group#||';'
from v$standby_log;
​
alter database ADD standby logfile thread 1 group 21 ('/u01/app/oracle/oradata/TESTDG/onlinelog/redo01.log') size 200M ;
alter database ADD standby logfile thread 1 group 22 ('/u01/app/oracle/oradata/TESTDG/onlinelog/redo02.log') size 200M ; 
alter database ADD standby logfile thread 1 group 23 ('/u01/app/oracle/oradata/TESTDG/onlinelog/redo03.log') size 200M ; 
alter database ADD standby logfile thread 1 group 24 ('/u01/app/oracle/oradata/TESTDG/onlinelog/redo04.log') size 200M ; 

8 启动数据库恢复同步

alter database open read ONLY;
alter database recover managed standby database using current logfile disconnect from session;

总结:

        通过(RECOVER STANDBY DATABASE ... FROM SERVICE)方式对备库问题进行修复，可以大大简化了备库问题的修复步骤以及时间。