oracle dataguard 日志 很多no,Oracle DataGuard主庫丟失歸檔日志后備庫的RMAN增量恢復一例...

第一部分  問題描述和環境狀態確認

----1. 問題場景

Oracle DataGuard主庫丟失archivelog，如何不重建備庫完成同步？

在Oracle DataGuard主從同步過程中可能出現主庫archivelog丟失，備庫出現gap錯誤。

此時，除了重新完整搭建DataGuard之外，可以通過主庫增量備份完成備份重新同步。

----2. 解決方式

【主要步驟】

1.在備庫上找出當前scn；

2.根據此scn，在主庫上使用rman完成增量備份；

3.傳輸增量備份到備庫，同時在備庫上恢復；

4.重新啟動備庫同步。

----3. 增量恢復詳細過程

==查看主庫信息==

SQL> select database_role from v$database;

DATABASE_ROLE

----------------

PRIMARY

SQL> archive log list;

==查看備庫信息==

SQL> select database_role from v$database;

DATABASE_ROLE

----------------

PHYSICAL STANDBY

--3.1.主、備庫上查看日志信息

SQL> select group#,thread#,sequence#,bytes,members,archived,status,first_change#,first_time from v$log;

---- #在主庫上  查看 LOG GAP 狀態

SQL>select dest_name,destination, database_mode, recovery_mode,gap_status from  v$archive_dest_status where dest_id=2;

---- #在備庫上查詢

SELECT * FROM V$ARCHIVE_GAP;

====3.2 驗證主庫到備庫的歸檔日志傳輸情況和備庫上歸檔日志的APPLY情況

====3.2.1 在備庫上查看歸檔日志應用情況:  val.applied='YES' --已應用; val.applied='NO' --待應用;

PhyStdby:SQL>select thread#, sequence#, val.applied

from v$archived_log val, v$database vdb

where val.resetlogs_change# = vdb.resetlogs_change#;

PhyStdby:SQL>select max(lh.SEQUENCE#) "Last applied arc",max(al.SEQUENCE#) "Last recieved arc"

from v$log_history lh,v$archived_log al;

------ 驗證最大日志序列號

Primary: SQL> select thread#, max(sequence#) "Last Primary Seq Generated"

from v$archived_log val, v$database vdb

where val.resetlogs_change# = vdb.resetlogs_change#

group by thread# order by 1;

PhyStdby:SQL> select thread#, max(sequence#) "Last Standby Seq Received"

from v$archived_log val, v$database vdb

where val.resetlogs_change# = vdb.resetlogs_change#

group by thread# order by 1;

------ "Last Standby Seq Applied"

PhyStdby:SQL>select thread#, max(sequence#) "Last Standby Seq Applied"

from v$archived_log val, v$database vdb

where val.resetlogs_change# = vdb.resetlogs_change#

and val.applied='YES'

group by thread# order by 1;

----================================================================

====第二部分  利用rman進行增量scn的恢復

==利用rman進行增量scn的恢復==

1.在備庫上取消日志應用

SQL> alter database recover managed standby database cancel;

2.查看備庫scn

SQL> select current_scn from v$database;

---- select to_char(current_scn) from v$database;

CURRENT_SCN

---------------

13402877625847

3.根據scn，在主庫上進行rman增量備份

Primary: rman>  rman target /

RMAN> backup incremental from scn 13402877625847 database format 'E:\ForStandby_%U.bka' tag 'forstandby';

------backup as compressed backupset incremental from scn 13402877625847 database format 'E:\ForStandby_%U.bka' tag 'forstandby';

RMAN> backup current controlfile for standby format 'E:\ForStandbyCTRL.bkc';

4.查看增量備份信息

E:\temp> dir

5.copy增量備份到備庫主機 一目錄，如:  E:\temp

6.將備庫啟動到mount狀態

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

READ ONLY

SQL> shutdown immediate;

SQL> startup nomount;

SQL> alter database mount standby database;

SQL> exit

7.rman恢復增量備份和控制文件備份

rman target / nocatalog

RMAN> crosscheck archivelog all;

RMAN> delete expired archivelog all;

RMAN> DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-15';

RMAN> catalog start with 'E:\temp';  ####注冊增量備份文件

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

RMAN> RECOVER DATABASE NOREDO;  #### 注意 NOREDO 選項

SQL> shutdown;

SQL> startup nomount;

RMAN> restore standby controlfile from 'E:\temp\ForStandbyCTRL.bkc';

RMAN> shutdown;

RMAN> exit

8.啟動備庫到mount狀態

SQL> startup nomount;

SQL> alter database mount standby database;

9.由於恢復了控制文件，因此需要重新添加新的standby redo log file(若先前有，可忽略此步)

alter database add standby logfile group 11 ('D:\APP\ADMINISTRATOR\WHBIDB_REDO\STANDBY11.LOG') size 1024m;

alter database add standby logfile group 12 ('D:\APP\ADMINISTRATOR\WHBIDB_REDO\STANDBY12.LOG') size 1024m;

alter database add standby logfile group 13 ('D:\APP\ADMINISTRATOR\WHBIDB_REDO\STANDBY13.LOG') size 1024m;

alter database add standby logfile group 14 ('D:\APP\ADMINISTRATOR\WHBIDB_REDO\STANDBY14.LOG') size 1024m;

alter database add standby logfile group 15 ('D:\APP\ADMINISTRATOR\WHBIDB_REDO\STANDBY15.LOG') size 1024m;

alter database add standby logfile group 16 ('D:\APP\ADMINISTRATOR\WHBIDB_REDO\STANDBY16.LOG') size 1024m;

10.啟動備庫同步

SQL> alter database open read only;

Database altered.

SQL> alter database recover managed standby database disconnect from session using current logfile;

Database altered.

11.查看歸檔日志應用以及表數據是否恢復

SQL> select max(lh.SEQUENCE#) "Lastapplied arc", max(al.SEQUENCE#) "Last recieved arc"

from v$log_history lh,v$archived_log al;

SQL> select count(1) from dba_tables;

----在備庫上查看歸檔日志應用情況:  val.applied='YES' --已應用; val.applied='NO' --待應用;

PhyStdby:SQL>select thread#, sequence#, val.applied

from v$archived_log val, v$database vdb

where val.resetlogs_change# = vdb.resetlogs_change#;