【Oracle】Oracle 18c主库日志已被删除的情况下使用service恢复从库

2022-06-18 14:26 abce 阅读(184) 评论(0) 编辑收藏举报

【Oracle】Oracle 11g主库日志已被删除的情况下如何恢复从库

【Oracle】Oracle 12c主库日志已被删除的情况下使用service恢复从库

如果备库与主库不同步，或者某些归档日志在发送或应用到备库之前被删除，那么可以按照以下方法将备库与主库同步。我们可以将此过程称为备库的前滚。

在oracle 11g中，这个过程是纯手工的，涉及很多步骤。这个过程在oracle 12c中得到了重大改进。同样在oracle 18c中，从主库刷新备库只是一个命令。

示例

主库：CLSPROD
从库：CLSTDBY
类型：2节点的RAC

1.取消从库的recovery

1 2	`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连接到从库

语法：

1	`RECOVER STANDBY` `DATABASE` `FROM` `SERVICE`

这里CLSPROD是主库的数据库service_name

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

[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/

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· Oracle18c主库日志被删除后如何恢复Standby数据库

· 【Oracle】Oracle 12c主库日志已被删除的情况下使用service恢复从库

· 利用 Oracle 19c 新特性 from service 修复备库 GAP

· Oracle11g备份与恢复

· scn增量备份恢复dataguard从库

阅读排行：
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· .NET10 - 预览版1新功能体验（一）

abce don't dream it , be it