DataGuard搭建步骤参考
--解决步骤
--Standby查询缺少归档日志号，可以看到seq# 38 ~ seq# 67的归档日志丢失未应用
SQL> select first_time,first_change#,next_change#,sequence# from v$log_history;
FIRST_TIME FIRST_CHANGE# NEXT_CHANGE# SEQUENCE#
------------------- ------------- ------------ ----------
2014-02-10 18:04:16 599380 629351 1
2014-02-10 18:05:28 629351 637595 2
2014-02-10 18:08:38 637595 637597 3
2014-02-10 18:08:39 637597 637601 4
2014-02-10 18:08:45 637601 637603 5
2014-02-10 18:08:46 637603 639724 6
2014-02-10 19:04:45 639724 639862 7
2014-02-10 19:04:49 639862 640638 8
2014-02-10 19:29:05 640638 640664 9
2014-02-10 19:29:54 640664 640666 10
2014-02-10 19:29:55 640666 640668 11
2014-02-10 19:29:56 640668 640670 12
2014-02-10 19:29:58 640670 643748 13
2014-02-10 21:42:28 643748 643879 14
2014-02-10 21:47:02 643879 643983 15
2014-02-10 21:50:36 643983 644027 16
2014-02-10 21:51:33 644027 644177 17
2014-02-10 21:56:47 644177 644196 18
2014-02-10 21:56:56 644196 644256 19
2014-02-10 21:58:42 644256 644274 20
2014-02-10 22:50:18 644275 650789 1
2014-02-10 23:03:43 650789 650791 2
2014-02-10 23:03:46 650791 650794 3
2014-02-10 23:03:51 650794 650798 4
2014-02-10 23:04:01 650798 675198 5
2014-02-11 12:43:59 675198 679827 6
2014-02-11 14:49:31 679827 679929 7
2014-02-11 14:52:05 679929 680026 8
2014-02-11 14:54:09 680026 680132 9
2014-02-11 14:56:43 680132 681334 10
2014-02-11 15:25:54 681334 701447 11
2014-02-13 22:08:41 701447 721714 12
2014-02-13 22:10:53 721714 742653 13
2014-02-13 22:17:29 742653 744515 14
2014-02-13 22:20:27 744515 744524 15
2014-02-13 22:20:51 744524 744527 16
2014-02-13 22:20:55 744527 746370 17
2014-02-13 23:05:53 746370 746373 18
2014-02-13 23:05:55 746373 746376 19
2014-02-13 23:05:59 746376 746378 20
2014-02-13 23:06:01 746378 746787 21
2014-02-13 23:15:51 746787 748433 22
2014-02-13 23:40:04 748433 748484 23
2014-02-13 23:42:25 748484 748572 24
2014-02-13 23:46:44 748572 749008 25
2014-02-13 23:57:59 749008 749010 26
2014-02-13 23:57:59 749010 749013 27
2014-02-13 23:58:05 749013 770685 28
2014-02-16 22:01:05 770685 780391 29
2014-02-16 22:01:56 780391 780398 30
2014-02-16 22:02:12 780398 782583 31
2014-02-16 23:10:40 782583 782585 32
2014-02-16 23:10:40 782585 782587 33
2014-02-16 23:10:43 782587 782917 34
2014-02-16 23:11:55 782917 782919 35
2014-02-16 23:11:55 782919 782922 36
2014-02-16 23:12:01 782922 783058 37
57 rows selected.
SQL> select sequence#,applied from v$archived_log where applied='YES';
SEQUENCE# APP
---------- ---
19 YES
20 YES
18 YES
21 YES
17 YES
22 YES
23 YES
24 YES
25 YES
26 YES
27 YES
28 YES
29 YES
30 YES
31 YES
32 YES
33 YES
34 YES
35 YES
36 YES
37 YES
21 rows selected.
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APP
---------- ---
19 YES
20 YES
18 YES
21 YES
17 YES
22 YES
23 YES
24 YES
25 YES
26 YES
27 YES
28 YES
29 YES
30 YES
31 YES
32 YES
33 YES
34 YES
35 YES
36 YES
37 YES
68 NO
69 NO
70 NO
71 NO
72 NO
73 NO
74 NO
75 NO
76 NO
77 NO
31 rows selected.
--Standby查询GAP信息，发现seq# 38 ~ seq# 67丢失
SQL> select * from v$archive_gap;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 38 67
SQL>
--在Primary上查询seq#丢失的的归档是否存在，这里查询结果是不存在
SQL> select name from v$archived_log where thread#=1 and dest_id=1 and sequence# between 38 and 67;
--在Primary找到丢失归档的SCN
SQL> select first_change# from v$archived_log where sequence#=38;
FIRST_CHANGE#
-------------
783058
--Primary增量备份，将SCN大于783058的block备份出来，并且传到Standby的一个空目录中
[oracle@vzwc arch]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Mon Feb 17 00:08:38 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: DBSERVER (DBID=66428446)
RMAN> backup device type disk incremental from scn 783058 database
2> format '/u01/app/oracle/arch/%U';
Starting backup at 17-FEB-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=513 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/dbserver/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/dbserver/sysaux01.dbf
input datafile fno=00006 name=/u01/app/oracle/oradata/dbserver/zwc.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/dbserver/example01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/dbserver/undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/dbserver/users01.dbf
channel ORA_DISK_1: starting piece 1 at 17-FEB-14
channel ORA_DISK_1: finished piece 1 at 17-FEB-14
piece handle=/u01/app/oracle/arch/0pp0q956_1_1 tag=TAG20140217T000910 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 17-FEB-14
channel ORA_DISK_1: finished piece 1 at 17-FEB-14
piece handle=/u01/app/oracle/arch/0qp0q95l_1_1 tag=TAG20140217T000910 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 17-FEB-14
RMAN>
--在Primary生成standby controlfile，并且传到Standby
RMAN> backup current controlfile for standby format '/tmp/%U';
Starting backup at 17-FEB-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=509 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including standby control file in backupset
channel ORA_DISK_1: starting piece 1 at 17-FEB-14
channel ORA_DISK_1: finished piece 1 at 17-FEB-14
piece handle=/tmp/0rp0qavt_1_1 tag=TAG20140217T004029 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-FEB-14
--Standby RMAN注册备份文件,Standby库要是mount状态
[oracle@dgstb arch]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Mon Feb 17 00:13:39 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: DBSERVER (DBID=66428446, not open)
RMAN> catalog start with '/u01/app/oracle/arch';
using target database control file instead of recovery catalog
searching for all files that match the pattern /u01/app/oracle/arch
List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/arch/0pp0q956_1_1
File Name: /u01/app/oracle/arch/0qp0q95l_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: /u01/app/oracle/arch/0pp0q956_1_1
File Name: /u01/app/oracle/arch/0qp0q95l_1_1
--Standby恢复
数据库，注意这样并不会把Standby库recover到一致状态，因为主库备份时是在线的，但是recover完之后的状态是可以来做standby的managed recovery的，Standby库是mount状态。
RMAN> recover database noredo;
Starting recover at 17-FEB-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=513 devtype=DISK
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/standby/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/standby/undotbs01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/standby/sysaux01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/standby/users01.dbf
destination for restore of datafile 00005: /u01/app/oracle/oradata/standby/example01.dbf
destination for restore of datafile 00006: /u01/app/oracle/oradata/standby/zwc.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/arch/0pp0q956_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/arch/0pp0q956_1_1 tag=TAG20140217T000910
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished recover at 17-FEB-14
RMAN>
--Standby还原控制文件
RMAN> shutdown immediate
using target database control file instead of recovery catalog
database dismounted
Oracle instance shut down
RMAN> startup nomount
connected to target database (not started)
Oracle instance started
Total System Global Area 612368384 bytes
Fixed Size 2085872 bytes
Variable Size 167775248 bytes
Database Buffers 436207616 bytes
Redo Buffers 6299648 bytes
RMAN> restore standby controlfile from '/tmp/0rp0qavt_1_1';
Starting restore at 17-FEB-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=541 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/u01/app/oracle/standby.ctl
Finished restore at 17-FEB-14
RMAN> alter database mount
2> ;
database mounted
released channel: ORA_DISK_1
RMAN>
--Standby应用日志
SQL> alter database recover managed standby database disconnect from session;
Database altered.
