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

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

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

-- PRIMARYSQL> 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.检查归档序列号

-- PRIMARYSQL> archive log listDatabase log mode Archive ModeAutomatic archival EnabledArchive destination /archive/PRIMDB/ofaroot/archOldest online log sequence 88Next log sequence to archive 90Current log sequence 90?-- STANDBY?SQL> archive log listDatabase log mode Archive ModeAutomatic archival EnabledArchive destination /archive/PRIMDB/ofaroot/archOldest online log sequence 72Next log sequence to archive 0Current 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 bytesFixed Size 6002112 bytesVariable Size 4630514240 bytesDatabase Buffers 9193914368 bytesRedo Buffers 104153088 bytesDatabase 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-16using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=689 device type=DISKchannel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: using compressed network backup set from service PRIMDBdestination for restore of datafile 00001: /archive/PRIMDB/PRIMDB/system01.dbfchannel ORA_DISK_1: restore complete, elapsed time: 00:00:01channel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: using compressed network backup set from service PRIMDBdestination for restore of datafile 00002: /archive/PRIMDB/PRIMDB/sysaux01.dbfchannel ORA_DISK_1: restore complete, elapsed time: 00:00:01channel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: using compressed network backup set from service PRIMDBdestination for restore of datafile 00003: /archive/PRIMDB/PRIMDB/undotbs01.dbfchannel ORA_DISK_1: restore complete, elapsed time: 00:00:01channel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: using compressed network backup set from service PRIMDBdestination for restore of datafile 00004: /archive/PRIMDB/PRIMDB/users01.dbfchannel 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 dismountedOracle instance shut down?RMAN> startup nomount;?connected to target database (not started)Oracle instance started?Total System Global Area 13934583808 bytes?Fixed Size 6002112 bytesVariable Size 4630514240 bytesDatabase Buffers 9193914368 bytesRedo Buffers 104153088 bytes?RMAN> restore standby controlfile from service PRIMDB;?Starting restore at 18-OCT-16allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=593 device type=DISK?channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: using network backup set from service PRIMDBchannel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:01output file name=/archive/PRIMDB/PRIMDB/control01.ctloutput file name=/archive/PRIMDB/PRIMDB/control02.ctlFinished restore at 18-OCT-16

7.检查数据文件

RMAN> report schema;?Starting implicit crosscheck backup at 18-OCT-16allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=593 device type=DISKFinished implicit crosscheck backup at 18-OCT-16?Starting implicit crosscheck copy at 18-OCT-16using channel ORA_DISK_1Crosschecked 2 objectsFinished implicit crosscheck copy at 18-OCT-16?searching for all files in the recovery areacataloging files...no files cataloged?RMAN-06139: WARNING: control file is not current for REPORT SCHEMAReport 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.dbf2 550 SYSAUX *** /archive/PRIMDB/PRIMDB/sysaux01.dbf3 335 UNDOTBS1 *** /archive/PRIMDB/PRIMDB/undotbs01.dbf4 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 cno files found to be unknown to the database

8.运行recover database

RMAN> RECOVER DATABASE;?Starting recover at 18-OCT-16allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=673 device type=DISK?starting media recovery?media recovery complete, elapsed time: 00:00:00Finished 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 1RFS[4]: Opened log for thread 1 sequence 94 dbid 599956155 branch 925475123Tue 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/