C/C++教程

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

本文主要是介绍【Oracle】Oracle 18c主库日志已被删除的情况下使用service恢复从库,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

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

这篇关于【Oracle】Oracle 18c主库日志已被删除的情况下使用service恢复从库的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!