|
前言:
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)方式对备库问题进行修复，可以大大简化了备库问题的修复步骤以及时间。
|