記錄一次Dataguard的修復過程

RDBMS 11.2.0.4的Dataguard， 因為主庫上的歸檔日誌被刪除，而歸檔日誌並沒有被傳送到備庫。導致了歸檔日誌的GAP。又不想重新搭建dg。使用了增量備份恢復的方法，來恢復備庫。

主要步驟如下：

1 在備庫上停止mrp程式
2 檢視備庫的scn
3 在主庫上進行增量備份
4 將增量備份傳輸到備庫所在的伺服器
5 在備庫上註冊傳輸過來的備份
6 用傳輸過來的備份來recover 備庫
7 在主庫上，建立備庫的控制檔案備份
8 複製控制檔案備份到備庫所在的伺服器
9 關閉備庫 ，在nomunt狀態下，恢復備庫的控制檔案

10 啟動備庫，啟動mrp程式 

-- 以下為詳細的過程

備庫的alert log的提示 。提示有gap 。

Using STANDBY_ARCHIVE_DEST parameter default value as /u01/archive_logstdby
Sat Jun 16 18:38:53 2018
Primary database is in MAXIMUM PERFORMANCE mode
RFS[1]: Assigned to RFS process 7854
RFS[1]: Selected log 5 for thread 1 sequence 456 dbid -2033225695 branch 963738576
Sat Jun 16 18:38:54 2018
RFS[2]: Assigned to RFS process 7860
RFS[2]: Selected log 6 for thread 1 sequence 455 dbid -2033225695 branch 963738576
Sat Jun 16 18:38:54 2018
RFS[3]: Assigned to RFS process 7862
RFS[3]: Opened log for thread 1 sequence 445 dbid -2033225695 branch 963738576
Sat Jun 16 18:38:54 2018
RFS[4]: Assigned to RFS process 7864
RFS[4]: Opened log for thread 1 sequence 446 dbid -2033225695 branch 963738576
Archived Log entry 474 added for thread 1 sequence 446 rlc 963738576 ID 0x86d19280 dest 2:
Archived Log entry 475 added for thread 1 sequence 445 rlc 963738576 ID 0x86d19280 dest 2:
Sat Jun 16 18:38:55 2018
Archived Log entry 476 added for thread 1 sequence 455 ID 0x86d19280 dest 1:
RFS[4]: Opened log for thread 1 sequence 448 dbid -2033225695 branch 963738576
RFS[2]: Opened log for thread 1 sequence 447 dbid -2033225695 branch 963738576
Archived Log entry 477 added for thread 1 sequence 448 rlc 963738576 ID 0x86d19280 dest 2:
RFS[3]: Opened log for thread 1 sequence 449 dbid -2033225695 branch 963738576
RFS[4]: Opened log for thread 1 sequence 450 dbid -2033225695 branch 963738576
Archived Log entry 478 added for thread 1 sequence 450 rlc 963738576 ID 0x86d19280 dest 2:
Archived Log entry 479 added for thread 1 sequence 449 rlc 963738576 ID 0x86d19280 dest 2:
Archived Log entry 480 added for thread 1 sequence 447 rlc 963738576 ID 0x86d19280 dest 2:
RFS[4]: Opened log for thread 1 sequence 451 dbid -2033225695 branch 963738576
Archived Log entry 481 added for thread 1 sequence 451 rlc 963738576 ID 0x86d19280 dest 2:
RFS[2]: Opened log for thread 1 sequence 453 dbid -2033225695 branch 963738576
RFS[3]: Opened log for thread 1 sequence 452 dbid -2033225695 branch 963738576
Archived Log entry 482 added for thread 1 sequence 453 rlc 963738576 ID 0x86d19280 dest 2:
Archived Log entry 483 added for thread 1 sequence 452 rlc 963738576 ID 0x86d19280 dest 2:
RFS[4]: Opened log for thread 1 sequence 454 dbid -2033225695 branch 963738576
Archived Log entry 484 added for thread 1 sequence 454 rlc 963738576 ID 0x86d19280 dest 2:
Sat Jun 16 18:39:07 2018
alter database recover managed standby database disconnect from session
Attempt to start background Managed Standby Recovery process (test_stdby)
Sat Jun 16 18:39:07 2018
MRP0 started with pid=28, OS id=7866 
MRP0: Background Managed Standby Recovery process started (test_stdby)
Serial Media Recovery started
Managed Standby Recovery not using Real Time Apply
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Waiting for thread 1 sequence 422
Fetching gap sequence in thread 1, gap sequence 422-444
Completed: alter database recover managed standby database disconnect from session
Sat Jun 16 18:41:03 2018
FAL[client]: Failed to request gap sequence
 GAP - thread 1 sequence 422-444
 DBID 2261741601 branch 963738576
FAL[client]: All defined FAL servers have been attempted.
------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that's sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
------------------------------------------------------------

檢視備庫的scn

SYS@test_stdby>select current_scn from v$database;

CURRENT_SCN
-----------
    3436081

SYS@test_stdby>/

檢視缺失的歸檔，這些歸檔在主庫也沒有了 

SYS@test_stdby>select * from v$archive_gap;

   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
         1           422            444

SYS@test_stdby>

檢視備庫的MRP程式

SYS@test_stdby>alter database recover managed standby database cancel;

Database altered.

在主庫上，進行增量備份

[oracle@test test]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Jun 16 19:00:50 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST (DBID=2261741601)

RMAN> BACKUP INCREMENTAL FROM SCN 3436081 DATABASE FORMAT '/tmp/ForStandby_%U' tag 'FORSTANDBY';

Starting backup at 16-JUN-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/test/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/test/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/test/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/test/example01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/test/lob_tbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/test/users01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/test/ggstbs01.dbf
channel ORA_DISK_1: starting piece 1 at 16-JUN-18
channel ORA_DISK_1: finished piece 1 at 16-JUN-18
piece handle=/tmp/ForStandby_0tt5k3je_1_1 tag=FORSTANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 16-JUN-18
channel ORA_DISK_1: finished piece 1 at 16-JUN-18
piece handle=/tmp/ForStandby_0ut5k3o0_1_1 tag=FORSTANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-JUN-18

RMAN> 

將備份的結果傳送到備庫，因為主庫和備庫是在同一臺機器上。所以不用傳了 

[oracle@test tmp]$ ls -l
total 360092
-rw-r-----. 1 oracle oinstall 358457344 Jun 16 19:03 ForStandby_0tt5k3je_1_1
-rw-r-----. 1 oracle oinstall  10256384 Jun 16 19:03 ForStandby_0ut5k3o0_1_1
drwx------. 2 root   root          4096 Dec 11  2017 keyring-Dgm44i
drwx------. 2 oracle oinstall      4096 Dec 11  2017 keyring-NfANjT
drwx------. 2 oracle oinstall      4096 May  6 15:39 pulse-SeL705CifrS4
drwx------. 2 root   root          4096 Dec 11  2017 pulse-tM9lerES2wmW

將備份結果，在備庫上註冊

[oracle@test logs]$ export ORACLE_SID=test_stdby
[oracle@test logs]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Jun 16 19:06:08 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST (DBID=2261741601)

RMAN> catalog start with '/tmp/ForStandby_0tt5k3je_1_1';

using target database control file instead of recovery catalog
searching for all files that match the pattern /tmp/ForStandby_0tt5k3je_1_1

List of Files Unknown to the Database
=====================================
File Name: /tmp/ForStandby_0tt5k3je_1_1

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /tmp/ForStandby_0tt5k3je_1_1

RMAN> catalog start with '/tmp/ForStandby_0ut5k3o0_1_1';

searching for all files that match the pattern /tmp/ForStandby_0ut5k3o0_1_1

List of Files Unknown to the Database
=====================================
File Name: /tmp/ForStandby_0ut5k3o0_1_1

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /tmp/ForStandby_0ut5k3o0_1_1

RMAN> 

使用增量備份，進行recover 備庫，要在mount狀態下，否則會出錯

[oracle@test backup]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Jun 16 19:22:16 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST (DBID=2261741601, not open)

RMAN> RECOVER DATABASE NOREDO;

Starting recover at 16-JUN-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=29 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/test_stdby/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/test_stdby/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/test_stdby/undotbs01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/test_stdby/users01.dbf
destination for restore of datafile 00005: /u01/app/oracle/oradata/test_stdby/example01.dbf
destination for restore of datafile 00006: /u01/app/oracle/oradata/test_stdby/ggstbs01.dbf
destination for restore of datafile 00007: /u01/app/oracle/oradata/test_stdby/lob_tbs01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/ForStandby_0vt5k4gn_1_1
channel ORA_DISK_1: piece handle=/tmp/ForStandby_0vt5k4gn_1_1 tag=FORSTANDBY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25

Finished recover at 16-JUN-18

RMAN> 

這個時候，如果open 備庫，是無法開啟的

在主庫上，建立備庫的控制檔案

RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/tmp/ForStandbyCTRL.bck';

Starting backup at 16-JUN-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 16-JUN-18
channel ORA_DISK_1: finished piece 1 at 16-JUN-18
piece handle=/tmp/ForStandbyCTRL.bck tag=TAG20180616T192727 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-JUN-18

RMAN> 

關閉備庫，啟動到nomunt下，進行控制檔案的恢復

SYS@test_stdby>shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SYS@test_stdby>startup nomount;
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2257840 bytes
Variable Size             507513936 bytes
Database Buffers          322961408 bytes
Redo Buffers                2371584 bytes
SYS@test_stdby>

[oracle@test backup]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Jun 16 19:30:25 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST (not mounted)

RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/ForStandbyCTRL.bck';

Starting restore at 16-JUN-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/test_stdby/control01.ctl
output file name=/u01/app/oracle/oradata/test_stdby/control02.ctl
Finished restore at 16-JUN-18

RMAN> 

關閉備庫，啟動到mount狀態，啟動到open狀態，其實可以一次性啟動到open狀態

SYS@test_stdby>shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SYS@test_stdby>startup mount;
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2257840 bytes
Variable Size             507513936 bytes
Database Buffers          322961408 bytes
Redo Buffers                2371584 bytes
Database mounted.
SYS@test_stdby>

SYS@test_stdby>alter database open;

Database altered.

SYS@test_stdby>

啟動MRP

SYS@test_stdby>alter database recover managed standby database disconnect from session;

Database altered.

SYS@test_stdby>

檢視主庫和備庫的情況 

@>conn / as sysdba
Connected.
SYS@test>archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/archive_log
Oldest online log sequence     455
Next log sequence to archive   458
Current log sequence           458
SYS@test>

SYS@test_stdby>archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/archive_logstdby
Oldest online log sequence     455
Next log sequence to archive   0
Current log sequence           458
SYS@test_stdby>select count(*) from test20180616;

  COUNT(*)
----------
     87209

SYS@test_stdby>

檢視是否還有gap

SYS@test_stdby>select * from v$archive_gap;

no rows selected

SYS@test_stdby>

END

-- 補充，恢復完畢後，開啟備庫的時候，alert log內容

alter database recover managed standby database disconnect from session
Attempt to start background Managed Standby Recovery process (test_stdby)
Sat Jun 16 19:34:07 2018
MRP0 started with pid=28, OS id=8978 
MRP0: Background Managed Standby Recovery process started (test_stdby)
Serial Media Recovery started
Managed Standby Recovery not using Real Time Apply
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Errors in file /u01/app/oracle/diag/rdbms/test_stdby/test_stdby/trace/test_stdby_mrp0_8978.trc:
ORA-00313: open failed for members of log group 8 of thread 1
ORA-00312: online log 8 thread 1: '/u01/app/oracle/oradata/test_stdby/redo08.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 8 /u01/app/oracle/oradata/test_stdby/redo08.log
Clearing online log 8 of thread 1 sequence number 456
Errors in file /u01/app/oracle/diag/rdbms/test_stdby/test_stdby/trace/test_stdby_mrp0_8978.trc:
ORA-00313: open failed for members of log group 8 of thread 1
ORA-00312: online log 8 thread 1: '/u01/app/oracle/oradata/test_stdby/redo08.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/test_stdby/test_stdby/trace/test_stdby_mrp0_8978.trc:
ORA-00313: open failed for members of log group 8 of thread 1
ORA-00312: online log 8 thread 1: '/u01/app/oracle/oradata/test_stdby/redo08.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Clearing online redo logfile 8 complete
Media Recovery Log /u01/archive_logstdby/1_457_963738576.arc
Completed: alter database recover managed standby database disconnect from session
Media Recovery Waiting for thread 1 sequence 458 (in transit)


--
SYS@test_stdby>select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/test_stdby/redo03.log
/u01/app/oracle/oradata/test_stdby/redo02.log
/u01/app/oracle/oradata/test_stdby/redo01.log
/u01/app/oracle/oradata/test_stdby/stdby_log01.log
/u01/app/oracle/oradata/test_stdby/stdby_log02.log
/u01/app/oracle/oradata/test_stdby/stdby_log03.log
/u01/app/oracle/oradata/test_stdby/stdby_log04.log
/u01/app/oracle/oradata/test_stdby/redo08.log

8 rows selected.

SYS@test_stdby>