在我的环境中有两台服务器:
Master:
10.158.1.112
Standby:
10.158.1.113
出问题的时候:
备库的情况:
SYS@enmy --> set linesize 400 SYS@enmy --> col name for a65 SYS@enmy --> select name,thread#,sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') "First",to_char(next_time,'yyyy-mm-dd hh24:mi:ss') "Next",applied from v$archived_log,(select max(sequence#) "SEQ#" from v$archived_log where applied='YES') b where sequence# between b.seq#-5 and b.seq#+9 order by sequence#; NAME THREAD# SEQUENCE# First Next APPLIED ----------------------------------------------------------------- ---------- ---------- ------------------- ------------------- --------- /u01/app/oracle/dg_archlog/1_512_928630070.archive 1 512 2017-01-17 13:52:04 2017-01-17 13:52:05 YES /u01/app/oracle/dg_archlog/1_513_928630070.archive 1 513 2017-01-17 13:52:05 2017-01-17 13:52:06 YES /u01/app/oracle/dg_archlog/1_514_928630070.archive 1 514 2017-01-17 13:52:06 2017-01-17 13:52:07 YES /u01/app/oracle/dg_archlog/1_515_928630070.archive 1 515 2017-01-17 13:52:07 2017-01-17 14:02:54 YES /u01/app/oracle/dg_archlog/1_516_928630070.archive 1 516 2017-01-17 14:02:54 2017-01-17 14:06:42 YES /u01/app/oracle/dg_archlog/1_517_928630070.archive 1 517 2017-01-17 14:06:42 2017-01-17 14:06:51 YES /u01/app/oracle/dg_archlog/1_518_928630070.archive 1 518 2017-01-17 14:06:51 2017-01-17 14:45:57 NO /u01/app/oracle/dg_archlog/1_519_928630070.archive 1 519 2017-01-17 14:45:57 2017-01-17 14:52:19 NO /u01/app/oracle/dg_archlog/1_520_928630070.archive 1 520 2017-01-17 14:52:19 2017-01-17 14:52:26 NO /u01/app/oracle/dg_archlog/1_521_928630070.archive 1 521 2017-01-17 14:52:26 2017-01-17 20:36:21 NO /u01/app/oracle/dg_archlog/1_522_928630070.archive 1 522 2017-01-17 20:36:21 2017-01-17 22:00:52 NO NAME THREAD# SEQUENCE# First Next APPLIED ----------------------------------------------------------------- ---------- ---------- ------------------- ------------------- --------- /u01/app/oracle/dg_archlog/1_523_928630070.archive 1 523 2017-01-17 22:00:52 2017-01-18 00:00:03 NO /u01/app/oracle/dg_archlog/1_524_928630070.archive 1 524 2017-01-18 00:00:03 2017-01-18 06:00:30 NO /u01/app/oracle/dg_archlog/1_525_928630070.archive 1 525 2017-01-18 06:00:30 2017-01-18 08:02:03 NO /u01/app/oracle/dg_archlog/1_526_928630070.archive 1 526 2017-01-18 08:02:03 2017-01-18 08:02:11 NO 15 rows selected. SYS@enmy --> SYS@enmy --> select process,pid,client_pid,status,delay_mins,known_agents,active_agents from v$managed_standby; PROCESS PID CLIENT_PID STATUS DELAY_MINS KNOWN_AGENTS ACTIVE_AGENTS --------- ---------- -------------------- ------------ ---------- ------------ ------------- ARCH 9180 9180 CONNECTED 0 0 0 ARCH 9182 9182 CONNECTED 0 0 0 ARCH 9184 9184 CONNECTED 0 0 0 ARCH 9186 9186 CONNECTED 0 0 0 MRP0 9196 N/A WAIT_FOR_GAP 0 3 3 SYS@enmy -->
查看日志:
[oracle@ora11gdg2 trace]$ cat alert_enmy.log | grep --color "Media Recovery Waiting for" | tail -n 4 Media Recovery Waiting for thread 1 sequence 494 Media Recovery Waiting for thread 1 sequence 494 Media Recovery Waiting for thread 1 sequence 494 Media Recovery Waiting for thread 1 sequence 494 [oracle@ora11gdg2 trace]$ [oracle@ora11gdg2 trace]$ cat alert_enmy.log | grep --color "Fetching gap sequence in thread" | tail -n 4 Fetching gap sequence in thread 1, gap sequence 494-499 Fetching gap sequence in thread 1, gap sequence 494-499 Fetching gap sequence in thread 1, gap sequence 494-499 Fetching gap sequence in thread 1, gap sequence 494-499 [oracle@ora11gdg2 trace]$
主库端:
SYS@enmy --> show parameter log_archive_dest_1 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_1 string LOCATION=/u01/app/oracle/dg_ar chlog valid_for=(all_logfiles, primary_role) db_unique_name=e nmy log_archive_dest_10 string log_archive_dest_11 string log_archive_dest_12 string log_archive_dest_13 string log_archive_dest_14 string log_archive_dest_15 string log_archive_dest_16 string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_17 string log_archive_dest_18 string log_archive_dest_19 string SYS@enmy --> SYS@enmy --> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/dg_stdbylog Oldest online log sequence 603 Next log sequence to archive 605 Current log sequence 605 SYS@enmy --> SYS@enmy --> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@ora11gdg1 dg_archlog]$ pwd /u01/app/oracle/dg_archlog [oracle@ora11gdg1 dg_archlog]$ ls 1_600_928630070.dbf 1_601_928630070.dbf 1_602_928630070.dbf 1_603_928630070.dbf 1_604_928630070.dbf [oracle@ora11gdg1 dg_archlog]$
没有看到编号494的日志。
这时候要恢复备库的DG同步,就需要RMAN基于SCN的增量恢复了。
一、收集备库信息:
停掉DG Standby:
SYS@enmy --> set linesize 400 SYS@enmy --> col client_pid for a20 SYS@enmy --> SYS@enmy --> select process,pid,client_pid,status,delay_mins,known_agents,active_agents from v$managed_standby; PROCESS PID CLIENT_PID STATUS DELAY_MINS KNOWN_AGENTS ACTIVE_AGENTS --------- ---------- -------------------- ------------ ---------- ------------ ------------- ARCH 9180 9180 CONNECTED 0 0 0 ARCH 9182 9182 CONNECTED 0 0 0 ARCH 9184 9184 CONNECTED 0 0 0 ARCH 9186 9186 CONNECTED 0 0 0 RFS 9508 22249 IDLE 0 0 0 RFS 9482 22247 IDLE 0 0 0 RFS 9463 22219 IDLE 0 0 0 RFS 9469 22243 IDLE 0 0 0 MRP0 9511 N/A WAIT_FOR_GAP 0 3 3 9 rows selected. SYS@enmy --> SYS@enmy --> alter database recover managed standby database cancel; Database altered. SYS@enmy --> select process,pid,client_pid,status,delay_mins,known_agents,active_agents from v$managed_standby; PROCESS PID CLIENT_PID STATUS DELAY_MINS KNOWN_AGENTS ACTIVE_AGENTS --------- ---------- -------------------- ------------ ---------- ------------ ------------- ARCH 9180 9180 CONNECTED 0 0 0 ARCH 9182 9182 CONNECTED 0 0 0 ARCH 9184 9184 CONNECTED 0 0 0 ARCH 9186 9186 CONNECTED 0 0 0 RFS 9508 22249 IDLE 0 0 0 RFS 9482 22247 IDLE 0 0 0 RFS 9463 22219 IDLE 0 0 0 RFS 9469 22243 IDLE 0 0 0 8 rows selected. SYS@enmy -->
查询最小SCN编号:
SYS@enmy --> col current_scn for 999999999 SYS@enmy --> col name for a13 SYS@enmy --> set linesize 400 SYS@enmy --> SYS@enmy --> select name,database_role,open_mode,current_scn from v$database; NAME DATABASE_ROLE OPEN_MODE CURRENT_SCN ------------- ---------------- -------------------- ----------- ENMY PHYSICAL STANDBY MOUNTED 8224991 SYS@enmy --> SYS@enmy --> select min(fhscn) from x$kcvfh; MIN(FHSCN) ---------------- 8217936 SYS@enmy --> select min(f.fhscn) from x$kcvfh f,v$datafile d where f.hxfil = d.file# and d.enabled != 'READ ONLY'; MIN(F.FHSCN) ---------------- 8217936 SYS@enmy --> col checkpoint_change# for 999999999 SYS@enmy --> select min(checkpoint_change#) from v$datafile_header; MIN(CHECKPOINT_CHANGE#) ----------------------- 8217936 SYS@enmy -->
可以看到,最小编号是【8217936】。
主库端操作:
创建需要的目录:
[oracle@ora11gdg1 ~]$ pwd /home/oracle [oracle@ora11gdg1 ~]$ [oracle@ora11gdg1 ~]$ mkdir rman_forstandby [oracle@ora11gdg1 ~]$ cd rman_forstandby/ [oracle@ora11gdg1 rman_forstandby]$ pwd /home/oracle/rman_forstandby [oracle@ora11gdg1 rman_forstandby]$ [oracle@ora11gdg1 ~]$ du -sh rman_forstandby/ 4.0K rman_forstandby/ [oracle@ora11gdg1 ~]$ [oracle@ora11gdg1 ~]$ ls -ltr rman_forstandby/ total 0 [oracle@ora11gdg1 ~]$
RMAN执行备份:
[oracle@ora11gdg1 ~]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Thu Mar 2 00:23:32 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ENMY (DBID=3081960307) RMAN> backup incremental from scn 8217936 database format '/home/oracle/rman_forstandby/forstandby_%U' tag 'ForStandby'; Starting backup at 02-MAR-2017 00:24:41 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=142 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=00002 name=/u01/app/oracle/oradata/enmy/sysaux01.dbf input datafile file number=00001 name=/u01/app/oracle/oradata/enmy/system01.dbf input datafile file number=00005 name=/u01/app/oracle/oradata/enmy/example01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/enmy/undotbs01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/enmy/users01.dbf channel ORA_DISK_1: starting piece 1 at 02-MAR-2017 00:24:42 channel ORA_DISK_1: finished piece 1 at 02-MAR-2017 00:25:47 piece handle=/home/oracle/rman_forstandby/forstandby_0jru32ia_1_1 tag=FORSTANDBY comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05 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 02-MAR-2017 00:25:50 channel ORA_DISK_1: finished piece 1 at 02-MAR-2017 00:25:51 piece handle=/home/oracle/rman_forstandby/forstandby_0kru32kb_1_1 tag=FORSTANDBY comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 02-MAR-2017 00:25:51 RMAN> RMAN> backup current controlfile for standby format '/home/oracle/rman_forstandby/forstandby_controlfile.bck'; Starting backup at 02-MAR-2017 00:26:44 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 02-MAR-2017 00:26:47 channel ORA_DISK_1: finished piece 1 at 02-MAR-2017 00:26:48 piece handle=/home/oracle/rman_forstandby/forstandby_controlfile.bck tag=TAG20170302T002644 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 02-MAR-2017 00:26:48 RMAN> RMAN> exit Recovery Manager complete. [oracle@ora11gdg1 ~]$
备份完成后,查看目录状态:
[oracle@ora11gdg1 ~]$ ls -ltr rman_forstandby/ total 577116 -rw-r----- 1 oracle oinstall 570908672 Mar 2 00:25 forstandby_0jru32ia_1_1 -rw-r----- 1 oracle oinstall 10027008 Mar 2 00:25 forstandby_0kru32kb_1_1 -rw-r----- 1 oracle oinstall 10027008 Mar 2 00:26 forstandby_controlfile.bck [oracle@ora11gdg1 ~]$ [oracle@ora11gdg1 ~]$ du -sh rman_forstandby/ 564M rman_forstandby/ [oracle@ora11gdg1 ~]$ [oracle@ora11gdg1 ~]$ du -sh rman_forstandby/* 545M rman_forstandby/forstandby_0jru32ia_1_1 9.6M rman_forstandby/forstandby_0kru32kb_1_1 9.6M rman_forstandby/forstandby_controlfile.bck [oracle@ora11gdg1 ~]$
传送到备库:
[oracle@ora11gdg1 ~]$ scp -r rman_forstandby/ ora11gdg2:/home/oracle oracle@ora11gdg2's password: forstandby_controlfile.bck 100% 9792KB 9.6MB/s 00:01 forstandby_0jru32ia_1_1 100% 544MB 68.1MB/s 00:08 forstandby_0kru32kb_1_1 100% 9792KB 9.6MB/s 00:00 [oracle@ora11gdg1 ~]$
传输完成后,备库查看:
[oracle@ora11gdg2 ~]$ ls -ltr total 8 -rw-r--r-- 1 oracle oinstall 1487 Nov 23 11:38 dgStandby.ora -rw-r--r-- 1 oracle oinstall 1349 Jan 3 14:43 oracle.sh [oracle@ora11gdg2 ~]$ [oracle@ora11gdg2 ~]$ ls -ltr total 12 -rw-r--r-- 1 oracle oinstall 1487 Nov 23 11:38 dgStandby.ora -rw-r--r-- 1 oracle oinstall 1349 Jan 3 14:43 oracle.sh drwxr-xr-x 2 oracle oinstall 4096 Mar 2 00:33 rman_forstandby [oracle@ora11gdg2 ~]$ [oracle@ora11gdg2 ~]$ ls -ltr rman_forstandby/ total 577116 -rw-r----- 1 oracle oinstall 10027008 Mar 2 00:33 forstandby_controlfile.bck -rw-r----- 1 oracle oinstall 570908672 Mar 2 00:33 forstandby_0jru32ia_1_1 -rw-r----- 1 oracle oinstall 10027008 Mar 2 00:33 forstandby_0kru32kb_1_1 [oracle@ora11gdg2 ~]$
备库:
恢复控制文件:
[oracle@ora11gdg2 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 2 00:34:54 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SYS@enmy --> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ enmy MOUNTED SYS@enmy --> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@ora11gdg2 ~]$ [oracle@ora11gdg2 ~]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Thu Mar 2 00:35:15 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ENMY (DBID=3081960307, not open) RMAN> shutdown; using target database control file instead of recovery catalog database dismounted Oracle instance shut down RMAN> RMAN> startup nomount; connected to target database (not started) Oracle instance started Total System Global Area 2505338880 bytes Fixed Size 2255832 bytes Variable Size 603980840 bytes Database Buffers 1879048192 bytes Redo Buffers 20054016 bytes RMAN> RMAN> restore standby controlfile from '/home/oracle/rman_forstandby/forstandby_controlfile.bck'; Starting restore at 02-MAR-2017 00:37:15 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=134 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/enmy/control01.ctl output file name=/u01/app/oracle/fast_recovery_area/enmy/control02.ctl Finished restore at 02-MAR-2017 00:37:17 RMAN>
开始恢复增量数据:
RMAN> startup mount; database is already started database mounted released channel: ORA_DISK_1 RMAN> RMAN> catalog start with '/home/oracle/rman_forstandby'; Starting implicit crosscheck backup at 02-MAR-2017 00:39:21 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=139 device type=DISK Crosschecked 1 objects Finished implicit crosscheck backup at 02-MAR-2017 00:39:22 Starting implicit crosscheck copy at 02-MAR-2017 00:39:22 using channel ORA_DISK_1 Crosschecked 2 objects Finished implicit crosscheck copy at 02-MAR-2017 00:39:22 searching for all files in the recovery area cataloging files... no files cataloged searching for all files that match the pattern /home/oracle/rman_forstandby List of Files Unknown to the Database ===================================== File Name: /home/oracle/rman_forstandby/forstandby_controlfile.bck File Name: /home/oracle/rman_forstandby/forstandby_0jru32ia_1_1 File Name: /home/oracle/rman_forstandby/forstandby_0kru32kb_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: /home/oracle/rman_forstandby/forstandby_controlfile.bck File Name: /home/oracle/rman_forstandby/forstandby_0jru32ia_1_1 File Name: /home/oracle/rman_forstandby/forstandby_0kru32kb_1_1 RMAN>
继续恢复之前,先看看数据库当前的状态:
[oracle@ora11gdg2 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 2 00:41:41 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SYS@enmy --> col current_scn for 999999999 SYS@enmy --> select name,current_scn,database_role,open_mode from v$database; NAME CURRENT_SCN DATABASE_ROLE OPEN_MODE --------- ----------- ---------------- -------------------- ENMY 10530772 PHYSICAL STANDBY MOUNTED SYS@enmy --> SYS@enmy --> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@ora11gdg2 ~]$
恢复数据库(备库)
[oracle@ora11gdg2 ~]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Thu Mar 2 00:42:57 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ENMY (DBID=3081960307, not open) RMAN> recover database noredo; Starting recover at 02-MAR-2017 00:43:09 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=125 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/enmy/system01.dbf destination for restore of datafile 00002: /u01/app/oracle/oradata/enmy/sysaux01.dbf destination for restore of datafile 00003: /u01/app/oracle/oradata/enmy/undotbs01.dbf destination for restore of datafile 00004: /u01/app/oracle/oradata/enmy/users01.dbf destination for restore of datafile 00005: /u01/app/oracle/oradata/enmy/example01.dbf channel ORA_DISK_1: reading from backup piece /home/oracle/rman_forstandby/forstandby_0jru32ia_1_1 channel ORA_DISK_1: piece handle=/home/oracle/rman_forstandby/forstandby_0jru32ia_1_1 tag=FORSTANDBY channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:02:05 Finished recover at 02-MAR-2017 00:45:16 RMAN> exit Recovery Manager complete. [oracle@ora11gdg2 ~]$ [oracle@ora11gdg2 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 2 00:45:54 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SYS@enmy --> col current_scn for 999999999 SYS@enmy --> select name,current_scn,database_role,open_mode from v$database; NAME CURRENT_SCN DATABASE_ROLE OPEN_MODE --------- ----------- ---------------- -------------------- ENMY 10530772 PHYSICAL STANDBY MOUNTED SYS@enmy -->
启动应用进程:
SYS@enmy --> set linesize 400 SYS@enmy --> col client_pid for a20 SYS@enmy --> select process,pid,client_pid,status,delay_mins,known_agents,active_agents from v$managed_standby; PROCESS PID CLIENT_PID STATUS DELAY_MINS KNOWN_AGENTS ACTIVE_AGENTS --------- ---------- -------------------- ------------ ---------- ------------ ------------- ARCH 9909 9909 CONNECTED 0 0 0 ARCH 9911 9911 CONNECTED 0 0 0 ARCH 9913 9913 CONNECTED 0 0 0 ARCH 9915 9915 CONNECTED 0 0 0 RFS 9940 22247 IDLE 0 0 0 RFS 9926 22219 IDLE 0 0 0 RFS 9928 22243 IDLE 0 0 0 7 rows selected. SYS@enmy --> SYS@enmy --> alter database recover managed standby database disconnect; Database altered. SYS@enmy --> select process,pid,client_pid,status,delay_mins,known_agents,active_agents from v$managed_standby; PROCESS PID CLIENT_PID STATUS DELAY_MINS KNOWN_AGENTS ACTIVE_AGENTS --------- ---------- -------------------- ------------ ---------- ------------ ------------- ARCH 9909 9909 CONNECTED 0 0 0 ARCH 9911 9911 CONNECTED 0 0 0 ARCH 9913 9913 CONNECTED 0 0 0 ARCH 9915 9915 CONNECTED 0 0 0 RFS 9940 22247 IDLE 0 0 0 RFS 9926 22219 IDLE 0 0 0 RFS 9928 22243 IDLE 0 0 0 MRP0 10046 N/A WAIT_FOR_LOG 0 3 3 8 rows selected. SYS@enmy -->
再次查看备库的日志应用情况:
SYS@enmy --> set linesize 400 SYS@enmy --> col name for a65 SYS@enmy --> select name,thread#,sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') "First",to_char(next_time,'yyyy-mm-dd hh24:mi:ss') "Next",applied from v$archived_log,(select max(sequence#) "SEQ#" from v$archived_log where applied='YES') b where sequence# between b.seq#-5 and b.seq#+9 order by sequence#; NAME THREAD# SEQUENCE# First Next APPLIED ----------------------------------------------------------------- ---------- ---------- ------------------- ------------------- --------- /u01/app/oracle/dg_archlog/1_609_928630070.archive 1 609 2017-03-02 00:22:56 2017-03-02 00:33:50 YES SYS@enmy -->
这样,问题就解决了。
————————————————————————
Done。