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

2022-01-13 22:04 abce 阅读(475) 评论(0) 编辑收藏举报

仍然可以按照11g提供的根据主库的增量备份来恢复备库，不过是纯手工的；也可以使用12c中的特性：recover database using service，过程简单了一些。

在18C中，只需要一个命令来重新刷新一下standby库。

示例

primary：CLSPROD

standby：CLSTDBY

数据库类型：双节点的RAC

1.取消standby的recovery过程

1 2	`SQL> recover managed standby` `database` `cancel;` `Media recovery complete.`

2.将standby库至于mount状态，从其中一个节点执行即可：

因为我们的standby是rac环境，所以需要关闭所有的节点。然后从其中一个节点将数据库mount起来

[oracle@stdby-host]$srvctl stop database -d CLSTDBY
 
sqlplus / as sysdba 
SQL> startup mount;

3.在standby库连接rman，执行recovery命令

语法：

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

这里CLSPROD是主库的service_name(TNS)

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 standby库到一致性状态

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

到这个时候，primary上的实时的更新并不会反应到standby库上。

因此，需要重新创建standby库的redo日志。

5.重建standby的redologs

因为我们是rac环境，所以有两个thread。其实standby环境，不用在意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　

至此，primary上的实时更新会反应到standby库上。

如果，遇到以下问题：

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.

解决方法：

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/

刷新页面返回顶部

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

相关博文：

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

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

· RAC ASM到单机ASM的dataguard创建步骤

· Oracle11g备份与恢复

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

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

abce don't dream it , be it

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

About

最新随笔

最新评论

随笔档案

积分与排名

日历

我的标签

随笔分类