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

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

dataguard可能存在这样的情况，即在将归档日志发送到备数据库之前，主数据库中已经删除归档日志。直到 11g，我们可以通过从主数据库获取增量备份并在备数据库中应用相同的备份来使备数据库与主数据库同步。但是在12c中，引入了一个新功能（RECOVER DATBASE 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

从库丢失了从sequence 74开始的归档日志。

按照以下步骤，修复主从同步

注意：以下所有的步骤都是在从库上做的

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.将从库启动到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对从库执行recover

rman连接到从库，并使用service连接主库

语法是：

1	`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控制文件

语法：

1	`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.打开数据库，开启介质恢复

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.

现在从库就和主库同步了。

在主库做一些日志切换，看看日志是否能传送到从库。如果我们在从库查看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文件。

10.删除和重建standby redo日志(在standby端执行)

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.