【Oracle】Oracle 11g主库日志已被删除的情况下如何恢复从库
【Oracle】Oracle 12c主库日志已被删除的情况下使用service恢复从库
如果备库与主库不同步，或者某些归档日志在发送或应用到备库之前被删除，那么可以按照以下方法将备库与主库同步。我们可以将此过程称为备库的前滚。
在oracle 11g中，这个过程是纯手工的，涉及很多步骤。这个过程在oracle 12c中得到了重大改进。同样在oracle 18c中，从主库刷新备库只是一个命令。
主库：CLSPROD 从库：CLSTDBY 类型：2节点的RAC
1.取消从库的recovery
SQL> recover managed standby database cancel; Media recovery complete.
2.将从库启动到mount状态(只在一个节点执行)
因为是RAC环境，需要先关闭所有的节点，然后在其中一个节点执行操作
[oracle@stdby-host]$srvctl stop database -d CLSTDBY sqlplus / as sysdba SQL> startup mount;
3.rman连接到从库
语法：
RECOVER STANDBY DATABASE FROM SERVICE
这里CLSPROD是主库的数据库service_name
[oracle@stdby-host admin]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Sep 9 15:39:06 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: CLSPROD (DBID=2290300697, not open)
RMAN> RECOVER STANDBY DATABASE FROM SERVICE CLSPROD;
Starting recover at 09-SEP-21
Oracle instance started
Total System Global Area 53687090008 bytes
Fixed Size 30145368 bytes
Variable Size 7247757312 bytes
Database Buffers 46305116160 bytes
Redo Buffers 104071168 bytes
contents of Memory Script:
{
restore standby controlfile from service 'CLSPROD';
alter database mount standby database;
}
executing Memory Script
Starting restore at 09-SEP-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1334 instance=CLSDR1 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service CLSPROD
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=+DATA/CLSDR/control01.ctl
output file name=+FRA/CLSDR/control02.ctl
Finished restore at 09-SEP-21
released channel: ORA_DISK_1
Statement processed
Executing: alter system set standby_file_management=manual
contents of Memory Script:
{
set newname for tempfile 2 to
"+DATA/CLSDR/TEMPFILE/temp.295.1068761213";
switch tempfile all;
set newname for datafile 1 to
"+DATA/CLSDR/DATAFILE/system01.dbf";
set newname for datafile 2 to
"+DATA/CLSDR/DATAFILE/sysaux01.dbf";
set newname for datafile 3 to
"+DATA/CLSDR/DATAFILE/undotbs01.dbf";
set newname for datafile 4 to
"+DATA/CLSDR/DATAFILE/users01.dbf";
set newname for datafile 5 to
"+DATA/CLSDR/DATAFILE/undotbs1.290.1068759067";
set newname for datafile 6 to
"+DATA/CLSDR/DATAFILE/dwe.259.1068759067";
set newname for datafile 7 to
"+DATA/CLSDR/DATAFILE/dwe_ndx.258.1068759067";
.
"+DATA/CLSDR/DATAFILE/dwe.304.1081717387";
catalog datafilecopy "+DATA/CLSDR/DATAFILE/system01.dbf",
"+DATA/CLSDR/DATAFILE/sysaux01.dbf",
"+DATA/CLSDR/DATAFILE/undotbs01.dbf",
"+DATA/CLSDR/DATAFILE/users01.dbf",
"+DATA/CLSDR/DATAFILE/undotbs1.290.1068759067",
"+DATA/CLSDR/DATAFILE/dwe.259.1068759067",
"+DATA/CLSDR/DATAFILE/dwe_ndx.258.1068759067",
"+DATA/CLSDR/DATAFILE/dwh.264.1068759067",
"+DATA/CLSDR/DATAFILE/dwh_ndx.263.1068759067",
"+DATA/CLSDR/DATAFILE/dww.266.1068759067",
"+DATA/CLSDR/DATAFILE/dww_ndx.262.1068759067",
..
..
executing Memory Script
executing command: SET NEWNAME
Starting implicit crosscheck backup at 09-SEP-21
allocated channel: ORA_DISK_1
Crosschecked 6 objects
Finished implicit crosscheck backup at 09-SEP-21
Starting implicit crosscheck copy at 09-SEP-21
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 09-SEP-21
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +FRA/CLSDR/AUTOBACKUP/2021_08_27/s_1081649857.272.1081649889
File Name: +FRA/CLSDR/AUTOBACKUP/2021_08_27/s_1081671425.333.1081671459
File Name: +FRA/CLSDR/ARCHIVELOG/2021_09_07/thread_1_seq_1764.323.1082613623
File Name: +FRA/CLSDR/ARCHIVELOG/2021_09_07/thread_1_seq_1765.288.1082634551
File Name: +FRA/CLSDR/ARCHIVELOG/2021_09_07/thread_1_seq_1766.283.1082646769
File Name: +FRA/CLSDR/ARCHIVELOG/2021_09_07/thread_1_seq_1767.315.1082656825
File Name: +FRA/CLSDR/ARCHIVELOG/2021_09_07/thread_1_seq_1768.376.1082663419
File Name: +FRA/CLSDR/ARCHIVELOG/2021_09_07/thread_1_seq_1769.348.1082671631
..
renamed tempfile 2 to +DATA/CLSDR/TEMPFILE/temp.295.1068761213 in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
..
cataloged datafile copy
datafile copy file name=+DATA/CLSDR/DATAFILE/system01.dbf RECID=82 STAMP=1082821215
cataloged datafile copy
datafile copy file name=+DATA/CLSDR/DATAFILE/sysaux01.dbf RECID=83 STAMP=1082821215
cataloged datafile copy
datafile copy file name=+DATA/CLSDR/DATAFILE/undotbs01.dbf RECID=84 STAMP=1082821215
cataloged datafile copy
datafile copy file name=+DATA/CLSDR/DATAFILE/users01.dbf RECID=85 STAMP=1082821215
cataloged datafile copy
datafile copy file name=+DATA/CLSDR/DATAFILE/undotbs1.290.1068759067 RECID=86 STAMP=1082821215
cataloged datafile copy
datafile copy file name=+DATA/CLSDR/DATAFILE/dwe.259.1068759067 RECID=87 STAMP=1082821215
cataloged datafile copy
datafile copy file name=+DATA/CLSDR/DATAFILE/dwe_ndx.258.1068759067 RECID=88 STAMP=1082821215
cataloged datafile copy
datafile copy file name=+DATA/CLSDR/DATAFILE/dwh.264.1068759067 RECID=89 STAMP=1082821215
cataloged datafile copy
datafile copy file name=+DATA/CLSDR/DATAFILE/dwh_ndx.263.1068759067 RECID=90 STAMP=1082821215
cataloged datafile copy
datafile copy file name=+DATA/CLSDR/DATAFILE/dww.266.1068759067 RECID=91 STAMP=1082821215
cataloged datafile copy
datafile copy file name=+DATA/CLSDR/DATAFILE/dww_ndx.262.1068759067 RECID=92 STAMP=1082821215
cataloged datafile copy
datafile copy file name=+DATA/CLSDR/DATAFILE/dwe.267.1068759067 RECID=93 STAMP=1082821215
...
..
datafile 1 switched to datafile copy
input datafile copy RECID=82 STAMP=1082821215 file name=+DATA/CLSDR/DATAFILE/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=83 STAMP=1082821215 file name=+DATA/CLSDR/DATAFILE/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=84 STAMP=1082821215 file name=+DATA/CLSDR/DATAFILE/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=85 STAMP=1082821215 file name=+DATA/CLSDR/DATAFILE/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=86 STAMP=1082821215 file name=+DATA/CLSDR/DATAFILE/undotbs1.290.1068759067
datafile 6 switched to datafile copy
input datafile copy RECID=87 STAMP=1082821215 file name=+DATA/CLSDR/DATAFILE/dwe.259.1068759067
datafile 7 switched to datafile copy
input datafile copy RECID=88 STAMP=1082821215 file name=+DATA/CLSDR/DATAFILE/dwe_ndx.258.1068759067
datafile 8 switched to datafile copy
input datafile copy RECID=89 STAMP=1082821215 file name=+DATA/CLSDR/DATAFILE/dwh.264.1068759067
datafile 9 switched to datafile copy
input datafile copy RECID=90 STAMP=1082821215 file name=+DATA/CLSDR/DATAFILE/dwh_ndx.263.1068759067
datafile 10 switched to datafile copy
input datafile copy RECID=91 STAMP=1082821215 file name=+DATA/CLSDR/DATAFILE/dww.266.1068759067
datafile 11 switched to datafile copy
input datafile copy RECID=92 STAMP=1082821215 file name=+DATA/CLSDR/DATAFILE/dww_ndx.262.1068759067
datafile 12 switched to datafile copy
input datafile copy RECID=93 STAMP=1082821215 file name=+DATA/CLSDR/DATAFILE/dwe.267.1068759067
datafile 13 switched to datafile copy
input datafile copy RECID=94 STAMP=1082821216 file name=+DATA/CLSDR/DATAFILE/dwe.269.1068759067
datafile 14 switched to datafile copy
..
contents of Memory Script:
{
recover database from service 'CLSPROD';
}
executing Memory Script
Starting recover at 09-SEP-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service CLSPROD
destination for restore of datafile 00001: +DATA/CLSDR/DATAFILE/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service CLSPROD
destination for restore of datafile 00002: +DATA/CLSDR/DATAFILE/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service CLSPROD
destination for restore of datafile 00003: +DATA/CLSDR/DATAFILE/undotbs01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service CLSPROD
destination for restore of datafile 00004: +DATA/CLSDR/DATAFILE/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service CLSPROD
destination for restore of datafile 00005: +DATA/CLSDR/DATAFILE/undotbs1.290.1068759067
channel ORA_DISK_1: restore complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service CLSPROD
destination for restore of datafile 00006: +DATA/CLSDR/DATAFILE/dwe.259.1068759067
channel ORA_DISK_1: restore complete, elapsed time: 00:02:46
channel ORA_
...
..
destination for restore of datafile 00038: +DATA/CLSDR/DATAFILE/dww.305.1081717339
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service CLSPROD
destination for restore of datafile 00039: +DATA/CLSDR/DATAFILE/dwe.304.1081717387
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
starting media recovery
archived log for thread 1 with sequence 1782 is already on disk as file +FRA/CLSDR/ARCHIVELOG/2021_09_09/thread_1_seq_1782.438.1082821213
archived log file name=+FRA/CLSDR/ARCHIVELOG/2021_09_09/thread_1_seq_1782.438.1082821213 thread=1 sequence=1782
media recovery complete, elapsed time: 00:00:02
Finished recover at 09-SEP-21
Executing: alter system set standby_file_management=auto
Finished recover at 09-SEP-21
4.recover从库到一致性状态
SQL> select name,open_Mode from v$database; NAME OPEN_MODE --------- -------------------- CLSPROD MOUNTED SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE UNTIL CONSISTENT; Database altered. Note - > If the above command is hung and taking long time to complete, then do alter system switch logfile ; from primary database. SQL>select name,open_Mode from v$database; NAME OPEN_MODE --------- -------------------- CLSPROD MOUNTED SQL> alter database open read only; Database altered. SQL> select name,open_mode from v$database; NAME OPEN_MODE --------- -------------------- CLSPROD READ ONLY SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered. SQL> select name,open_mode from v$database; NAME OPEN_MODE --------- -------------------- CLSPROD READ ONLY WITH APPLY
在这个阶段，主库上的实时更新(real time changes)不会反应到从库。
所以，我们需要在从库上重新创建standby redo日志文件。
5.重建standby redolog
在从库上执行。虽然主库是两节点的RAC，有两个threads，但是在从库上可以不考虑thread的事情。
--- First cancel the recovery:
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> select inst_id,GROUP#,TYPE,MEMBER from gv$logfile where TYPE='STANDBY' WHERE INST_ID=1;
INST_ID GROUP# TYPE MEMBER
---------- ---------- ------- --------------------------------------------------
1 15 STANDBY +DATA/CLSPROD/ONLINELOG/group_15.326.1081670395
1 15 STANDBY +FRA/CLSPROD/ONLINELOG/group_15.355.1081670397
1 16 STANDBY +DATA/CLSPROD/ONLINELOG/group_16.325.1081670425
1 16 STANDBY +FRA/CLSPROD/ONLINELOG/group_16.346.1081670429
1 17 STANDBY +DATA/CLSPROD/ONLINELOG/group_17.324.1081670447
1 17 STANDBY +FRA/CLSPROD/ONLINELOG/group_17.379.1081670451
1 18 STANDBY +DATA/CLSPROD/ONLINELOG/group_18.334.1081670457
1 18 STANDBY +FRA/CLSPROD/ONLINELOG/group_18.385.1081670459
SQL> select inst_id,thread#,group# from gv$standby_log;
INST_ID THREAD# GROUP#
---------- ---------- ----------
1 1 15
1 1 16
1 1 17
1 1 18
2 1 15
2 1 16
2 1 17
2 1 18
-- Drop all standby redologs:
alter database drop standby logfile group 15;
alter database drop standby logfile group 16;
alter database drop standby logfile group 17;
alter database drop standby logfile group 18;
-- Create standby redolog for both threads:
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 15 ('+DATA','+FRA') SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 16 ('+DATA','+FRA') SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 17 ('+DATA','+FRA') SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 18 ('+DATA','+FRA') SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 15 ('+DATA','+FRA') SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 16 ('+DATA','+FRA') SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 17 ('+DATA','+FRA') SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 18 ('+DATA','+FRA') SIZE 1G;
--- Once standby redologs are created start recovery:
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
这样，主库上新的变更就会反映到从库上。
问题处理
1.报错
RMAN> RECOVER STANDBY DATABASE FROM SERVICE CLSPROD; Starting recover at 09-SEP-21 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 09/09/2021 15:38:47 RMAN-05150: Managed Recovery Process must be disabled before running RECOVER STANDBY DATABASE.
处理方法：
先取消recovery过程，然后再recover从库
SQL> recover managed standby database cancel; Media recovery complete. RMAN> RECOVER STANDBY DATABASE FROM SERVICE CLSPROD;
https://dbaclass.com/article/recover-standby-database-from-primary-using-service-in-oracle-18c/
