在Oracle DataGuard主从同步过程中可能出现主库archivelog丢失，备库出现gap错误。此时，除了重新完整搭建DataGuard之外呢，可以通过主库增量备份完成备份重新同步。
【主要步骤】
1.在备库上找出当前scn；
2.根据此scn，在主库上使用rman完成增量备份；
3.传输增量备份到备库，同时在备库上恢复；
4.重新启动备库同步。
【演示示例】
==查看主库信息==
SQL> select database_role fromv$database;
DATABASE_ROLE
----------------
PRIMARY
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u/arch
Oldest online log sequence 199
Next log sequence to archive 221
Current log sequence 221
SQL> select count(1) from reno;
COUNT(1)
----------
30
==查看备库信息==
SQL> select database_role fromv$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u/arch
Oldest online log sequence 199
Next log sequence to archive 0
Current log sequence 221
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY
SQL> select count(1) from reno;
COUNT(1)
----------
30
==模拟主库丢失归档的情况==
1.查看日志信息
SQL> selectgroup#,thread#,sequence#,bytes,members,archived,status,first_change#,first_timefrom v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- -------------------- --- ---------------- ------------- -------------------
1 1 220 52428800 1 YES INACTIVE 1213684 12/05/2013 19:28:17
2 1 221 52428800 1 YES INACTIVE 1213690 12/05/2013 19:28:18
3 1 222 52428800 1 NO CURRENT 1213724 12/05/2013 19:28:47
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u/arch
Oldest online log sequence 220
Next log sequence to archive 222
Current log sequence 222
2.暂停归档传输
SQL> alter system set log_archive_dest_state_2='defer';
System altered.
SQL> show parameterlog_archive_dest_state_2;
NAME TYPE VALUE
----------------------------------------------- ------------------------------
log_archive_dest_state_2 string defer
3.切换归档日志
SQL> alter system archive log current;
System altered.
SQL> selectgroup#,thread#,sequence#,bytes,members,archived,status,first_change#,first_timefrom v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- -------------------- --- ---------------- ------------- -------------------
1 1 223 52428800 1 NO CURRENT 1213934 12/05/2013 19:31:48
2 1 221 52428800 1 YES INACTIVE 1213690 12/05/2013 19:28:18
3 1 222 52428800 1 YES ACTIVE 1213724 12/05/2013 19:28:47
4.删除表reno前10条记录
SQL> delete from reno where a<11;
10 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(1) from reno;
COUNT(1)
----------
20
5.切换归档日志数次
SQL> alter system archive log current;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> selectgroup#,thread#,sequence#,bytes,members,archived,status,first_change#,first_timefrom v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- -------------------- --- ---------------- ------------- -------------------
1 1 226 52428800 1 NO CURRENT 1213981 12/05/2013 19:32:23
2 1 224 52428800 1 YES INACTIVE 1213968 12/05/2013 19:32:16
3 1 225 52428800 1 YES INACTIVE 1213975 12/05/2013 19:32:20
6.mv 223和224号归档日志
oracle@test_mem_issue:/u/arch> mv1_223_730313063.dbf 1_223_730313063.dbf.bak
oracle@test_mem_issue:/u/arch> mv1_224_730313063.dbf 1_224_730313063.dbf.bak
7.启用归档日志传输
SQL> alter system setlog_archive_dest_state_2='enable';
System altered.
8.在备库上查看表数据
SQL> select count(1) from reno;
COUNT(1)
----------
30
9.在备库上查看归档日志应用
SQL> select max(lh.SEQUENCE#) "Lastapplied arc",max(al.SEQUENCE#) "Last recieved arc" fromv$log_history lh,v$archived_log al;
Last applied arc Last recieved arc
---------------- -----------------
222 226
10.查看alert信息
[oracle@TENCENT64 /u/rman]$ adrci
ADRCI: Release 11.2.0.2.0 - Production onThu Dec 5 19:34:21 2013
Copyright (c) 1982, 2009, Oracle and/or itsaffiliates. All rights reserved.
ADR base = "/u/ora11g"
adrci> show alert;
Choose the alert log from the followinghomes to view:
1: diag/rdbms/cftest/CFTEST
2: diag/rdbms/cftestdr/CFTEST
3: diag/tnslsnr/TENCENT64/listener
Q: to quit
Please select option: 2
Output the results to file:/tmp/alert_14280_1403_CFTEST_1.ado
2013-12-05 11:11:49.844000 +08:00
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on.
IMODE=BR
ILAT =249
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise EditionRelease 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Miningand Real Application Testing options.
Using parameter settings in server-sidespfile
......
2013-12-05 19:28:48.369000 +08:00
Media Recovery Log/u/arch/CFTEST_1_220_730313063.arc
Media Recovery Log/u/arch/CFTEST_1_221_730313063.arc
Media Recovery Waiting for thread 1sequence 222 (in transit)
Recovery of Online Redo Log: Thread 1 Group30 Seq 222 Reading mem 0
Mem# 0: /u/arch/stby/db_stb_redo10.log
2013-12-05 19:31:48.792000 +08:00
Archived Log entry 13 added for thread 1sequence 222 ID 0xc8c4a8e7 dest 1:
Media Recovery Waiting for thread 1sequence 223
2013-12-05 19:34:03.408000 +08:00
RFS[7]: Assigned to RFS process 14247
RFS[7]: Opened log for thread 1 sequence225 dbid -926624281 branch 730313063
Archived Log entry 14 added for thread 1sequence 225 rlc 730313063 ID 0xc8c4a8e7 dest 2:
Fetching gap sequence in thread 1, gapsequence 223-224
2013-12-05 19:34:05.722000 +08:00
RFS[7]: Selected log 30 for thread 1sequence 226 dbid -926624281 branch 730313063
Archived Log entry 15 added for thread 1sequence 226 ID 0xc8c4a8e7 dest 1:
Primary database is in MAXIMUM PERFORMANCEmode
RFS[8]: Assigned to RFS process 14251
RFS[8]: Selected log 30 for thread 1sequence 227 dbid -926624281 branch 730313063
Fetching gap sequence inthread 1, gap sequence 223-224
2013-12-05 19:34:15.933000 +08:00
Fetching gap sequence in thread 1, gapsequence 223-224
2013-12-05 19:34:25.953000 +08:00
Fetching gap sequence in thread 1, gapsequence 223-224
--已经出现gap了
==利用rman进行增量scn的恢复==
1.在备库上取消日志应用
SQL> alter database recover managedstandby database cancel;
Database altered.
2.查看备库scn
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1213933
3.根据scn，在主库上进行rman增量备份
oracle@test_mem_issue:/u/rman> rmantarget /
Recovery Manager: Release 11.2.0.2.0 -Production on Thu Dec 5 19:35:14 2013
Copyright (c) 1982, 2009, Oracle and/or itsaffiliates. All rights reserved.
connected to target database: CFTEST(DBID=3368343015)
RMAN> backup incremental from scn1213933 database format '/u/rman/ForStandby_%U.bka' tag 'forstandby';
Starting backup at 12/05/2013 19:35:29
using target database control file insteadof recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=9 device type=DISK
backup will be obsolete on date 12/12/201319:35:29
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafilebackup set
channel ORA_DISK_1: specifying datafile(s)in backup set
input datafile file number=00001 name=/u/ora11g/oradata/CFTEST/system01.dbf
input datafile file number=00002name=/u/ora11g/oradata/CFTEST/sysaux01.dbf
input datafile file number=00003name=/u/ora11g/oradata/CFTEST/undotbs01.dbf
input datafile file number=00005name=/u/ora11g/oradata/CFTEST/renotest01.dbf
input datafile file number=00004name=/u/ora11g/oradata/CFTEST/users01.dbf
channel ORA_DISK_1: starting piece 1 at12/05/2013 19:35:29
channel ORA_DISK_1: finished piece 1 at12/05/2013 19:35:30
piecehandle=/u/rman/ForStandby_36oqp9o1_1_1.bka tag=FORSTANDBY comment=NONE
channel ORA_DISK_1: backup set complete,elapsed time: 00:00:01
using channel ORA_DISK_1
backup will be obsolete on date 12/12/201319:35:30
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafilebackup set
channel ORA_DISK_1: specifying datafile(s)in backup set
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at12/05/2013 19:35:32
channel ORA_DISK_1: finished piece 1 at12/05/2013 19:35:33
piece handle=/u/rman/ForStandby_37oqp9o3_1_1.bkatag=FORSTANDBY comment=NONE
channel ORA_DISK_1: backup set complete,elapsed time: 00:00:01
Finished backup at 12/05/2013 19:35:33
RMAN> backup current controlfile forstandby format '/u/rman/ForStandbyCTRL.bkc';
Starting backup at 12/05/2013 19:35:42
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafilebackup set
channel ORA_DISK_1: specifying datafile(s)in backup set
including standby control file in backupset
channel ORA_DISK_1: starting piece 1 at12/05/2013 19:35:43
channel ORA_DISK_1: finished piece 1 at12/05/2013 19:35:44
piece handle=/u/rman/ForStandbyCTRL.bkctag=TAG20131205T193542 comment=NONE
channel ORA_DISK_1: backup set complete,elapsed time: 00:00:01
Finished backup at 12/05/2013 19:35:44
Starting Control File and SPFILE Autobackupat 12/05/2013 19:35:44
piecehandle=/u/ora11g/fast_recovery_area/CFTESTDB/autobackup/2013_12_05/o1_mf_s_833398544_9b0s8jts_.bkpcomment=NONE
Finished Control File and SPFILE Autobackupat 12/05/2013 19:35:45
RMAN> exit
Recovery Manager complete.
4.查看增量备份信息
oracle@test_mem_issue:/u/rman> ls -lrth
......
-rw-r----- 1 oracle oinstall 96K 2013-12-05 19:35ForStandby_36oqp9o1_1_1.bka
-rw-r----- 1 oracle oinstall 10M 2013-12-05 19:35ForStandby_37oqp9o3_1_1.bka
-rw-r----- 1 oracle oinstall 10M 2013-12-05 19:35 ForStandbyCTRL.bkc
5.copy增量备份到备库
oracle@test_mem_issue:/u/rman> scp -P36000 ForStandby*oracle@10.132.74.37:/u/rman/
6.将备库启动到mount状态
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 2087780352 bytes
Fixed Size 2228072 bytes
Variable Size 486539416 bytes
Database Buffers 1593835520 bytes
Redo Buffers 5177344 bytes
SQL> alter database mount standbydatabase;
Database altered.
SQL> exit
7.rman恢复增量备份和控制文件备份
rman target / nocatalog
Recovery Manager: Release 11.2.0.2.0 - Productionon Thu Dec 5 19:38:27 2013
Copyright (c) 1982, 2009, Oracle and/or itsaffiliates. All rights reserved.
connected to target database: CFTEST(DBID=3368343015, not open)
using target database control file insteadof recovery catalog
RMAN> catalog start with '/u/rman/';
searching for all files that match thepattern /u/rman/
List of Files Unknown to the Database
=====================================
File Name: /u/rman/testdb_1loqo7ed_53_1.bka
File Name: /u/rman/1_66_730313063.dbf
File Name: /u/rman/1_67_730313063.dbf
File Name: /u/rman/testdb_1moqo7es_54_1.bka
File Name:/u/rman/ForStandby_36oqp9o1_1_1.bka
File Name:/u/rman/ForStandby_37oqp9o3_1_1.bka
File Name: /u/rman/ForStandbyCTRL.bkc
Do you really want to catalog the abovefiles (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u/rman/testdb_1loqo7ed_53_1.bka
File Name: /u/rman/1_66_730313063.dbf
File Name: /u/rman/1_67_730313063.dbf
File Name: /u/rman/testdb_1moqo7es_54_1.bka
File Name:/u/rman/ForStandby_36oqp9o1_1_1.bka
File Name:/u/rman/ForStandby_37oqp9o3_1_1.bka
File Name: /u/rman/ForStandbyCTRL.bkc
RMAN> recover database noredo;
Starting recover at 12/05/2013 19:38:51
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1714 devicetype=DISK
channel ORA_DISK_1: starting incrementaldatafile backup set restore
channel ORA_DISK_1: specifying datafile(s)to restore from backup set
destination for restore of datafile 00001:/u/ora11g/oradata/CFTEST/system01.dbf
destination for restore of datafile 00002:/u/ora11g/oradata/CFTEST/sysaux01.dbf
destination for restore of datafile 00003:/u/ora11g/oradata/CFTEST/undotbs01.dbf
destination for restore of datafile 00004:/u/ora11g/oradata/CFTEST/users01.dbf
destination for restore of datafile 00005:/u/ora11g/oradata/CFTEST/renotest01.dbf
channel ORA_DISK_1: reading from backuppiece /u/rman/ForStandby_36oqp9o1_1_1.bka
channel ORA_DISK_1: piecehandle=/u/rman/ForStandby_36oqp9o1_1_1.bka tag=FORSTANDBY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete,elapsed time: 00:00:01
Finished recover at 12/05/2013 19:38:53
RMAN> shutdown;
database dismounted
Oracle instance shut down
RMAN> startup nomount;
connected to target database (not started)
Oracle instance started
Total System Global Area 2087780352 bytes
Fixed Size 2228072 bytes
Variable Size 486539416 bytes
Database Buffers 1593835520 bytes
Redo Buffers 5177344 bytes
RMAN> restore standby controlfile from'/u/rman/ForStandbyCTRL.bkc';
Starting restore at 12/05/2013 19:39:47
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=572 devicetype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete,elapsed time: 00:00:01
output filename=/u/ora11g/oradata/CFTEST/control01.ctl
output filename=/u/ora11g/oradata/CFTEST/control02.ctl
output filename=/u/ora11g/oradata/CFTEST/control03.ctl
Finished restore at 12/05/2013 19:39:49
RMAN> shutdown;
Oracle instance shut down
RMAN> exit
8.启动备库到mount状态
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 2087780352 bytes
Fixed Size 2228072 bytes
Variable Size 486539416 bytes
Database Buffers 1593835520 bytes
Redo Buffers 5177344 bytes
SQL> alter database mount standbydatabase;
Database altered.
9.由于恢复了控制文件，因此需要重新添加新的standby redo log file
SQL> alter database add standby logfilegroup 34 ('/u/arch/stby/db_stb_redo04.log') size 500m;
alter database add standby logfile group 33('/u/arch/stby/db_stb_redo03.log') size 500m;
alter database add standby logfile group 32('/u/arch/stby/db_stb_redo02.log') size 500m;
alter database add standby logfile group 31('/u/arch/stby/db_stb_redo01.log') size 500m;
alter database add standby logfile group 30('/u/arch/stby/db_stb_redo10.log') size 500m;
alter database add standby logfile group 39('/u/arch/stby/db_stb_redo09.log') size 500m;
alter database add standby logfile group 38('/u/arch/stby/db_stb_redo08.log') size 500m;
alter database add standby logfile group 37('/u/arch/stby/db_stb_redo07.log') size 500m;
alter database add standby logfile group 36('/u/arch/stby/db_stb_redo06.log') size 500m;
alter database add standby logfile group 35('/u/arch/stby/db_stb_redo05.log') size 500m;
Database altered.
10.启动备库同步
SQL> alter database open read only;
Database altered.
SQL> alter database recover managedstandby database disconnect from session using current logfile;
Database altered.
11.查看归档日志应用以及reno表数据是否恢复
SQL> select max(lh.SEQUENCE#) "Lastapplied arc",max(al.SEQUENCE#) "Last recieved arc" fromv$log_history lh,v$archived_log al;
Last applied arc Last recieved arc
---------------- -----------------
227 227
SQL> select count(1) from reno;
COUNT(1)
----------
20
==The end==
扫一扫
500
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
