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

 

仍然可以按照11g提供的根据主库的增量备份来恢复备库。不过在12c中，引入了一个新的特性：recover database using service，整个过程变得更简单了。

 

1.检查主库和备库的状态

-- PRIMARY
SQL> select db_unique_name,OPEN_MODE,DATABASE_ROLE from v$database;
​
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
------------------------------ -------------------- ----------------
PRIMDB READ WRITE PRIMARY
​
​
-- STANDBY
​
SQL> SQL> select db_unique_name,OPEN_MODE,DATABASE_ROLE from v$database;
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
------------------------------ -------------------- ----------------
STYDB READ ONLY WITH APPLY PHYSICAL STANDBY

2.检查归档序列号

-- PRIMARY
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /archive/PRIMDB/ofaroot/arch
Oldest online log sequence 88
Next log sequence to archive 90
Current log sequence 90
​
-- STANDBY
​
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /archive/PRIMDB/ofaroot/arch
Oldest online log sequence 72
Next log sequence to archive 0
Current log sequence 74

可以看到，74之后的日志没有传输到standby库。

 

 

 

在oracle 12c中，使用以下方法来同步备库。

 

提醒：以下所有的操作都是在备库执行

 

3.取消备库的recovery过程

SQL> select db_unique_name,OPEN_MODE,DATABASE_ROLE from v$database;
​
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
------------------------------ -------------------- ----------------
STYDB READ ONLY PHYSICAL STANDBY
​
SQL> recover managed standby database cancel;
Media recovery complete.

4.开启standby库到mount状态(如果是adg的话)

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
​
Total System Global Area 1.3935E+10 bytes
Fixed Size 6002112 bytes
Variable Size 4630514240 bytes
Database Buffers 9193914368 bytes
Redo Buffers 104153088 bytes
Database mounted.

5.使用service恢复standby库

语法是：

RECOVER DATABASE FROM SERVICE < PRIMARY DB SERVICE NAME > NOREDO USING COMPRESSED BACKUPSET;

$ rman target /
​
Recovery Manager: Release 12.1.0.1.0 - Production on Tue Oct 18 18:32:15 2016
​
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
​
connected to target database: STYDB (DBID=599956155, not open)
​
RMAN> recover database from service PRIMDB noredo using compressed backupset;
​
Starting recover at 18-OCT-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=689 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service PRIMDB
destination for restore of datafile 00001: /archive/PRIMDB/PRIMDB/system01.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 compressed network backup set from service PRIMDB
destination for restore of datafile 00002: /archive/PRIMDB/PRIMDB/sysaux01.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 compressed network backup set from service PRIMDB
destination for restore of datafile 00003: /archive/PRIMDB/PRIMDB/undotbs01.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 compressed network backup set from service PRIMDB
destination for restore of datafile 00004: /archive/PRIMDB/PRIMDB/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
​
Finished recover at 18-OCT-16

6.在nomount阶段restore standby控制文件

 

语法：

RESTORE STANDBY CONTROLFILE  FROM SERVICE < PRIMARY DB SERVICE NAME > ;

RMAN> shutdown immediate;
​
database dismounted
Oracle instance shut down
​
RMAN> startup nomount;
​
connected to target database (not started)
Oracle instance started
​
Total System Global Area 13934583808 bytes
​
Fixed Size 6002112 bytes
Variable Size 4630514240 bytes
Database Buffers 9193914368 bytes
Redo Buffers 104153088 bytes
​
RMAN> restore standby controlfile from service PRIMDB;
​
Starting restore at 18-OCT-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=593 device type=DISK
​
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service PRIMDB
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/archive/PRIMDB/PRIMDB/control01.ctl
output file name=/archive/PRIMDB/PRIMDB/control02.ctl
Finished restore at 18-OCT-16

7.检查数据文件

RMAN> report schema;
​
Starting implicit crosscheck backup at 18-OCT-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=593 device type=DISK
Finished implicit crosscheck backup at 18-OCT-16
​
Starting implicit crosscheck copy at 18-OCT-16
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 18-OCT-16
​
searching for all files in the recovery area
cataloging files...
no files cataloged
​
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name PRIMDB
​
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 700 SYSTEM *** /archive/PRIMDB/PRIMDB/system01.dbf
2 550 SYSAUX *** /archive/PRIMDB/PRIMDB/sysaux01.dbf
3 335 UNDOTBS1 *** /archive/PRIMDB/PRIMDB/undotbs01.dbf
4 5 USERS *** /archive/PRIMDB/PRIMDB/users01.dbf
​
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /archive/PRIMDB/PRIMDB/temp01.dbf
​
​
​
RMAN> catalog start with 'c';
​
searching for all files that match the pattern c
no files found to be unknown to the database

8.运行recover database

RMAN> RECOVER DATABASE;
​
Starting recover at 18-OCT-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=673 device type=DISK
​
starting media recovery
​
media recovery complete, elapsed time: 00:00:00
Finished recover at 18-OCT-16

9.打开standby库，并启动介质恢复

SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open;
​
Database altered.
​
SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.

此时，standby已经和primary同步了。可以在primary执行一些日志切换操作，并观察是否传输到了standby。

如果检查Standby的alert日志，可能会看到没有standby redo

Archived Log entry 4 added for thread 1 sequence 93 rlc 925475123 ID 0x23c3441f dest 2:
RFS[4]: No standby redo logfiles available for thread 1
RFS[4]: Opened log for thread 1 sequence 94 dbid 599956155 branch 925475123
Tue Oct 18 19:10:59 2016

为了解决这个问题，我们需要删除并重建standby的redo logs。

10.删除并重建standby的redo logs

SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database drop logfile group 4;
​
Database altered.
​
SQL> alter database drop logfile group 5;
​
Database altered.
​
SQL> alter database drop logfile group 6;
​
Database altered.
​
SQL> select thread#, group#, sequence#, status from v$standby_log;
no rows selected
​
SQL> SQL> alter database add standby logfile '/archive/PRIMDB/PRIMDB/stdby_redo04.log' size 52428800;
Database altered.
​
SQL> SQL> alter database add standby logfile '/archive/PRIMDB/PRIMDB/stdby_redo05.log' size 52428800;
Database altered.
​
SQL> SQL> alter database add standby logfile '/archive/PRIMDB/PRIMDB/stdby_redo06.log' size 52428800;
Database altered.
​
SQL> SQL> alter database add standby logfile '/archive/PRIMDB/PRIMDB/stdby_redo07.log' size 52428800;
Database altered.

11.再次启动recovery过程

SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.

 

参考：

https://dbaclass.com/article/rolling-forward-standby-database-when-archives-missing-in-primary-in-12c/