分享程序员开发的那些事...
更新时间：2022-08-22 16:27:09
【故障处理】DG归档丢失的恢复
各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(∩_∩)O~：
① 主库丢失归档，物理DG的恢复
② 其他常用SQL语句
Tips：
① 若文章代码格式有错乱，推荐使用QQ、搜狗或360浏览器，也可以下载pdf格式的文档来查看，pdf文档下载地址：http://yunpan.cn/cdEQedhCs2kFz （提取码：ed9b）
② 本篇BLOG中命令的输出部分需要特别关注的地方我都用灰色背景和粉红色字体来表示，比如下边的例子中，thread 1的最大归档日志号为33，thread 2的最大归档日志号为43是需要特别关注的地方；而命令一般使用黄色背景和红色字体标注；对代码或代码输出部分的注释一般采用蓝色字体表示。
List of Archived Logs in backup set 11
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48
1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58
2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49
2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53
[ZHLHRDB1:root]:/>lsvg -o
T_XDESK_APP1_vg
rootvg
[ZHLHRDB1:root]:/>
00:27:22 SQL> alter tablespace idxtbs read write;
====》2097152*512/1024/1024/1024=1G
本文如有错误或不完善的地方请大家多多指正，ITPUB留言或QQ皆可，您的批评指正是我写作的最大动力。
【DATAGUARD】物理dg在主库丢失归档文件的情况下的恢复(七)： http://blog.itpub.net/26736162/viewspace-1780863/
我的oracle健康检查报告(三) http://blog.itpub.net/26736162/viewspace-1870296/
我的oracle健康检查报告(二) http://blog.itpub.net/26736162/viewspace-1805156/
我的oracle健康检查报告 http://blog.itpub.net/26736162/viewspace-1783297/
| 项目 | source db | PHYSICAL STANDBY |
| db 类型 | RAC | RAC |
| db version | 11.2.0.3 | 11.2.0.3 |
| db 存储 | ASM | ASM |
| OS版本及kernel版本 | AIX 64位 6.1.0.0 | AIX 64位 6.1.0.0 |
由于客户这边有N套库，我过来时间短，没有完全整理，昨天对一套rac执行健康检查的时候发现带有一套物理DG,但是DG库不同步，而且同步的日志号已经断档很久了，于是决定把这套DG恢复一下，下边简单记录一下。
数据库信息：
DG库信息：
| THREAD# | DEST_ID | DEST_NAME | TARGET | DATABASE_MODE | DB_UNIQUE_NAME | DESTINATION | CURRENT_SEQ# | LAST_ARCHIVED | APPLIED_SEQ# |
| 1 | 1 | LOG_ARCHIVE_DEST_1 | LOCAL PRIMARY | OPEN | NONE | /archive/arch | 14311 | 14310 |
|
| 1 | 2 | LOG_ARCHIVE_DEST_2 | PHYSICAL STANDBY | OPEN_READ-ONLY | oraNETR | oraNETR | 14311 | 14310 | 968 |
| 2 | 1 | LOG_ARCHIVE_DEST_1 | LOCAL PRIMARY | OPEN | NONE | /archive/arch | 13403 | 13402 |
|
| 2 | 2 | LOG_ARCHIVE_DEST_2 | PHYSICAL STANDBY | OPEN_READ-ONLY | oraNETR | oraNETR | 13403 | 13402 | 644 |
可以看到实例一当前是14311，但是DG库才应用到968，而实例二当前是13403，DG应用到644，下边着手恢复备库，恢复的原理可以参考之前的文档 【DATAGUARD】物理dg在主库丢失归档文件的情况下的恢复(七)： http://blog.itpub.net/26736162/viewspace-1780863/ 。
在主库看了下，968、644的日志早都不见了，没办法只能对主库进行基于SCN号的增量备份：
先查找最小的scn号：
SELECT (SELECT MIN(d.CHECKPOINT_CHANGE#) FROM v$datafile d) datafile_scn,
(SELECT MIN(d.CHECKPOINT_CHANGE#)
FROM v$datafile_header d
WHERE rownum = 1) datafile_header_scn,
(SELECT current_scn FROM v$database) current_scn,
(SELECT min(b.NEXT_CHANGE#)
FROM v$archived_log b
WHERE b.SEQUENCE# in (968,644)
AND resetlogs_change# =
(SELECT d.RESETLOGS_CHANGE# FROM v$database d)) NEXT_CHANGE#
FROM dual;
我们取12232942713886为备份的SCN号：
备份：
run
{
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
backup as compressed backupset incremental from SCN 12232942713886 database format '/archive/standby_new_%d_%T_%U.bak' include current controlfile for standby filesperset=5 tag 'FOR STANDBY new';
release channel d1;
release channel d2;
release channel d3;
release channel d4;
}
将日志传递到备库：
root@ZHLHRDB7:/archive# l
total 478725456
drwxr-xr-x 2 oracle dba 524288 Apr 22 08:56 arch
-rw-r--r-- 1 oracle dba 2253 Apr 08 2015 initnetr.ora
-rwxr-xr-x 1 oracle dba 21708800 Apr 08 2015 standby.ctl
-rwxr-xr-x 1 oracle dba 22414245888 Apr 20 09:06 standby_ORANET_20160130_0bqsm476_1_1.bak
-rwxr-xr-x 1 oracle dba 22140502016 Apr 20 09:36 standby_ORANET_20160130_0cqsm477_1_1.bak
-rwxr-xr-x 1 oracle dba 13977583616 Apr 20 09:57 standby_ORANET_20160130_0dqsmdf6_1_1.bak
-rwxr-xr-x 1 oracle dba 14525480960 Apr 20 10:16 standby_ORANET_20160130_0eqsmdkv_1_1.bak
-rwxr-xr-x 1 oracle dba 14335983616 Apr 20 10:34 standby_ORANET_20160130_0fqsmkgt_1_1.bak
-rwxr-xr-x 1 oracle dba 16120840192 Apr 20 10:55 standby_ORANET_20160130_0gqsmkvf_1_1.bak
-rwxr-xr-x 1 oracle dba 16035766272 Apr 20 11:16 standby_ORANET_20160130_0hqsmrlg_1_1.bak
-rwxr-xr-x 1 oracle dba 16075489280 Apr 20 11:37 standby_ORANET_20160130_0iqsmspa_1_1.bak
-rwxr-xr-x 1 oracle dba 16070926336 Apr 20 11:58 standby_ORANET_20160130_0jqsn37g_1_1.bak
-rwxr-xr-x 1 oracle dba 16039673856 Apr 20 12:19 standby_ORANET_20160130_0kqsn4b0_1_1.bak
-rwxr-xr-x 1 oracle dba 15593078784 Apr 20 13:47 standby_ORANET_20160131_0lqsnadm_1_1.bak
-rwxr-xr-x 1 oracle dba 15463137280 Apr 20 14:07 standby_ORANET_20160131_0mqsnbfu_1_1.bak
-rwxr-xr-x 1 oracle dba 15369084928 Apr 20 14:27 standby_ORANET_20160131_0nqsnhb1_1_1.bak
-rwxr-xr-x 1 oracle dba 15504777216 Apr 20 14:47 standby_ORANET_20160131_0oqsnibd_1_1.bak
-rwxr-xr-x 1 oracle dba 15410495488 Apr 20 15:09 standby_ORANET_20160131_0pqsno4l_1_1.bak
-rwxr-xr-x 1 oracle dba 4063232 Apr 20 15:09 standby_ORANET_20160131_0qqsnp6t_1_1.bak
-rwxr-xr-x 1 oracle dba 4063232 Apr 20 15:09 standby_ORANET_20160131_0rqsnulk_1_1.bak
root@ZHLHRDB7:/archive#
从健康检查中可以找到最新的控制文件备份集名称是/archive/standby_ORANET_20160131_0rqsnulk_1_1.bak，我们在备库首先启动到nomount状态，然后恢复控制文件：
SQL> show parameter cont
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 31
control_files string +DATA/oranetr/controlfile/cont
rol01.ctl, +DATA/oranetr/contr
olfile/control02.ctl, +DATA/or
anetr/controlfile/control03.ct
l
control_management_pack_access string DIAGNOSTIC+TUNING
global_context_pool_size string
SQL>
col name format a60
set line 9999 pagesize 9999
SELECT a.FILE#, a.NAME,a.CHECKPOINT_CHANGE#,a.LAST_CHANGE#,status FROM v$datafile a;
FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
---------- ------------------------------------------------------------ ------------------ ------------ -------
1 +DATA/oranetr/datafile/system.293.876478571 12233018392104 SYSTEM
2 +DATA/oranetr/datafile/sysaux.291.876478569 12233018392104 ONLINE
3 +DATA/oranetr/datafile/undotbs1.292.876478569 12233018392104 ONLINE
4 +DATA/oranetr/datafile/undotbs2.288.876478511 12233018392104 ONLINE
5 +DATA/oranetr/datafile/users.290.876478513 12233018392104 ONLINE
6 +DATA/oranetr/datafile/bocnet_tbs.260.876478197 12233018392104 ONLINE
7 +DATA/oranetr/datafile/bocnet_tbs.280.876478353 12233018392104 ONLINE
8 +DATA/oranetr/datafile/bocnet_tbs.271.876478273 12233018392104 ONLINE
9 +DATA/oranetr/datafile/bocnet_tbs.283.876478429 12233018392104 ONLINE
10 +DATA/oranetr/datafile/bocnet_tbs.287.876478509 12233018392104 ONLINE
11 +DATA/oranetr/datafile/bocnet_tbs.284.876478431 12233018392104 ONLINE
12 +DATA/oranetr/datafile/bocnet_tbs.289.876478511 12233018392104 ONLINE
13 +DATA/oranetr/datafile/bocnet_tbs.276.876478349 12233018392104 ONLINE
14 +DATA/oranetr/datafile/bocnet_tbs.277.876478349 12233018392104 ONLINE
15 +DATA/oranetr/datafile/bocnet_tbs.272.876478273 12233018392104 ONLINE
16 +DATA/oranetr/datafile/bocnet_tbs.279.876478351 12233018392104 ONLINE
17 +DATA/oranetr/datafile/bocnet_tbs.278.876478351 12233018392104 ONLINE
18 +DATA/oranetr/datafile/bocnet_tbs.258.876478117 12233018392104 ONLINE
19 +DATA/oranetr/datafile/bocnet_tbs.267.876478039 12233018392104 ONLINE
20 +DATA/oranetr/datafile/bocnet_tbs.256.876478039 12233018392104 ONLINE
21 +DATA/oranetr/datafile/bocnet_tbs.266.876478115 12233018392104 ONLINE
22 +DATA/oranetr/datafile/bocnet_tbs.257.876478117 12233018392104 ONLINE
23 +DATA/oranetr/datafile/bocnet_tbs.268.876478039 12233018392104 ONLINE
24 +DATA/oranetr/datafile/bocnet_tbs.263.876478039 12233018392104 ONLINE
25 +DATA/oranetr/datafile/bocnet_tbs.269.876478039 12233018392104 ONLINE
26 +DATA/oranetr/datafile/bocnet_tbs.265.876478119 12233018392104 ONLINE
27 +DATA/oranetr/datafile/bocnet_tbs.259.876478117 12233018392104 ONLINE
28 +DATA/oranetr/datafile/bocnet_tbs.264.876478195 12233018392104 ONLINE
29 +DATA/oranetr/datafile/bocnet_tbs.281.876478427 12233018392104 ONLINE
30 +DATA/oranetr/datafile/bocnet_tbs.274.876478275 12233018392104 ONLINE
31 +DATA/oranetr/datafile/bocnet_tbs.261.876478195 12233018392104 ONLINE
32 +DATA/oranetr/datafile/bocnet_tbs.286.876478509 12233018392104 ONLINE
33 +DATA/oranetr/datafile/bocnet_tbs.282.876478427 12233018392104 ONLINE
34 +DATA/oranetr/datafile/bocnet_tbs.275.876478275 12233018392104 ONLINE
35 +DATA/oranetr/datafile/bocnet_tbs.270.876478197 12233018392104 ONLINE
36 +DATA/oranetr/datafile/bocnet_tbs.262.876478195 12233018392104 ONLINE
37 +DATA/oranetr/datafile/bocnet_tbs.273.876478273 12233018392104 ONLINE
38 +DATA/oranetr/datafile/bocnet_tbs.285.876478431 12233018392104 ONLINE
39 +DATA/oranetr/datafile/bocnet_tbs.313.876559505 12233018392104 ONLINE
40 +DATA/oranetr/datafile/bocnet_tbs.314.876559507 12233018392104 ONLINE
41 +DATA/oranetr/datafile/bocnet_tbs.315.876559509 12233018392104 ONLINE
42 +DATA/oranetr/datafile/bocnet_tbs.316.876559509 12233018392104 ONLINE
43 +DATA/oranetr/datafile/bocnet_tbs.317.876559511 12233018392104 ONLINE
44 +DATA/oranetr/datafile/bocnet_tbs.318.876559511 12233018392104 ONLINE
45 +DATA/oranetr/datafile/bocnet_tbs.319.876559513 12233018392104 ONLINE
46 +DATA/oranetr/datafile/bocnet_tbs.320.876559513 12233018392104 ONLINE
47 +DATA/oranetr/datafile/bocnet_tbs.321.876559515 12233018392104 ONLINE
48 +DATA/oranetr/datafile/bocnet_tbs.322.876559517 12233018392104 ONLINE
49 +DATA/oranetr/datafile/bocnet_tbs.323.876559517 12233018392104 ONLINE
50 +DATA/oranetr/datafile/bocnet_tbs.324.876559519 12233018392104 ONLINE
51 +DATA/oranetr/datafile/bocnet_tbs.325.876559521 12233018392104 ONLINE
52 +DATA/oranetr/datafile/bocnet_tbs.326.876559521 12233018392104 ONLINE
53 +DATA/oranetr/datafile/bocnet_tbs.327.876559523 12233018392104 ONLINE
54 +DATA/oranetr/datafile/bocnet_tbs.328.876559523 12233018392104 ONLINE
55 +DATA/oranetr/datafile/bocnet_tbs.329.876559525 12233018392104 ONLINE
56 +DATA/oranetr/datafile/bocnet_tbs.330.876559525 12233018392104 ONLINE
57 +DATA/oranetr/datafile/bocnet_tbs.331.876559527 12233018392104 ONLINE
58 +DATA/oranetr/datafile/bocnet_tbs.332.876559527 12233018392104 ONLINE
59 +DATA/oranetr/datafile/bocnet_tbs.333.876559529 12233018392104 ONLINE
60 +DATA/oranetr/datafile/bocnet_tbs.334.876559529 12233018392104 ONLINE
61 +DATA/oranetr/datafile/bocnet_tbs.335.876559531 12233018392104 ONLINE
62 +DATA/oranetr/datafile/bocnet_tbs.336.876559531 12233018392104 ONLINE
63 +DATA/oranetr/datafile/bocnet_tbs.337.876559533 12233018392104 ONLINE
64 +DATA/oranetr/datafile/bocnet_tbs.338.876559533 12233018392104 ONLINE
65 +DATA/oranetr/datafile/bocnet_tbs.339.876559535 12233018392104 ONLINE
66 +DATA/oranetr/datafile/bocnet_tbs.340.876559535 12233018392104 ONLINE
67 +DATA/oranetr/datafile/bocnet_tbs.341.876559537 12233018392104 ONLINE
68 +DATA/oranetr/datafile/bocnet_tbs.342.876559539 12233018392104 ONLINE
69 +DATA/oranetr/datafile/bocnet_tbs.343.876559539 12233018392104 ONLINE
70 +DATA/oranetr/datafile/bocnet_tbs.344.876559541 12233018392104 ONLINE
71 +DATA/oranetr/datafile/bocnet_tbs.345.876559541 12233018392104 ONLINE
72 +DATA/oranetr/datafile/bocnet_tbs.346.876559543 12233018392104 ONLINE
73 +DATA/oranetr/datafile/bocnet_tbs.347.876559543 12233018392104 ONLINE
74 +DATA/oranetr/datafile/bocnet_tbs.348.876559545 12233018392104 ONLINE
75 +DATA/oranetr/datafile/bocnet_tbs.349.876559545 12233018392104 ONLINE
rman恢复，恢复之前将原来的控制文件进行手工的冷备：
cp +DATA/oranetr/controlfile/control01.ctl +DATA/oranetr/controlfile/control01.ctl_bk
restore standby controlfile to '+DATA/oranetr/controlfile/control01.ctl' from '/archive/standby_ORANET_20160131_0rqsnulk_1_1.bak';
restore standby controlfile to '+DATA/oranetr/controlfile/control02.ctl' from '/archive/standby_ORANET_20160131_0rqsnulk_1_1.bak';
restore standby controlfile to '+DATA/oranetr/controlfile/control03.ctl' from '/archive/standby_ORANET_20160131_0rqsnulk_1_1.bak';
alter database mount;
catalog start with '/archive/';
run
{
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
recover DATABASE noredo;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
}
报错：
Thu Apr 21 17:08:13 2016
Errors in file /oracle/app/oracle/diag/rdbms/oranetr/oraNETR2/trace/oraNETR2_dbw0_15794356.trc:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '+DATA/oranetr/datafile/system.260.873307133'
ORA-17503: ksfdopn:2 Failed to open file +DATA/oranetr/datafile/system.260.873307133
ORA-15012: ASM file '+DATA/oranetr/datafile/system.260.873307133' does not exist
Errors in file /oracle/app/oracle/diag/rdbms/oranetr/oraNETR2/trace/oraNETR2_dbw0_15794356.trc:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '+DATA/oranetr/datafile/sysaux.261.873307151'
ORA-17503: ksfdopn:2 Failed to open file +DATA/oranetr/datafile/sysaux.261.873307151
ORA-15012: ASM file '+DATA/oranetr/datafile/sysaux.261.873307151' does not exist
很明显，控制文件是从主库恢复过来的，但是ASM的文件名是不一样的，所以报错，那么要做的就是重命名备库的数据文件名，用editplus或UE的列模式来编辑代码：
col name format a60
set line 9999 pagesize 9999
SELECT a.FILE#, a.NAME,a.CHECKPOINT_CHANGE#,a.LAST_CHANGE#,status FROM v$datafile a;
alter system set standby_file_management=manual;
alter database rename file '+DATA/oranetr/datafile/system.260.873307133' to '+DATA/oranetr/datafile/system.293.876478571' ;
alter database rename file '+DATA/oranetr/datafile/sysaux.261.873307151' to '+DATA/oranetr/datafile/sysaux.291.876478569' ;
。。。。。。。。。。。。。。。。。。。
alter database rename file '+DATA/oranetr/datafile/bocnet_tbs.338.1163840135' to '+DATA/oranetr/datafile/bocnet_tbs.347.876559543';
alter database rename file '+DATA/oranetr/datafile/bocnet_tbs.339.1163840135' to '+DATA/oranetr/datafile/bocnet_tbs.348.876559545';
alter database rename file '+DATA/oranetr/datafile/bocnet_tbs.340.116384013' to '+DATA/oranetr/datafile/bocnet_tbs.349.876559545';
重命名数据文件完成后再次执行恢复操作即可，恢复的过程中我们可以通过如下的SQL语句来查看恢复的进度：
SELECT a.USERNAME,
(SELECT upper(nb.OSUSER) FROM v$session nb WHERE nb.SID = a.sid) OSUSER,
(SELECT nb.sid || ',' || nb.SERIAL# || ',' || pr.SPID
FROM v$process pr, v$session nb
WHERE nb.PADDR = pr.ADDR
and nb.sid = a.SID
and nb.SERIAL# = a.SERIAL#) session_info,
a.target,
a.opname,
to_char(a.START_TIME, 'YYYY-MM-DD HH24:MI:SS') start_time,
round(a.SOFAR * 100 / a.TOTALWORK, 2) || '%' AS progress,
(a.TIME_REMAINING) TIME_REMAINING,
(a.sofar || ':' || a.TOTALWORK) sofar_TOTALWORK,
(a.elapsed_seconds) elapsed_seconds,
message message,
(SELECT nb.EVENT FROM V$session_Wait nb WHERE nb.SID = a.SID) wait_event,
(SELECT nb.STATUS FROM v$session nb WHERE nb.SID = a.SID) STATUS
FROM v$session_longops a
WHERE a.time_remaining <> 0
ORDER BY status, a.TIME_REMAINING DESC, a.SQL_ID, a.sid;
SQL> alter database recover managed standby database using current logfile disconnect from session;
alter database recover managed standby database using current logfile disconnect from session
*
ERROR at line 1:
ORA-38500: USING CURRENT LOGFILE option not available without standby redo logs
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> set line 9999 pagesize 9999
SQL> col db_id format a15
SQL> select GROUP#, DBID db_id, THREAD#, SEQUENCE#, BYTES, USED, ARCHIVED, STATUS, FIRST_CHANGE#, NEXT_CHANGE#,LAST_CHANGE# from v$standby_log ;
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database add standby logfile thread 1 group 7 size 1073741824 ,group 8 size 1073741824 ,group 9 size 1073741824,group 10 size 1073741824 ;
Database altered.
SQL> alter database add standby logfile thread 2 group 11 size 1073741824 ,group 12 size 1073741824 ,group 13 size 1073741824,group 14 size 1073741824 ;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select GROUP#, DBID db_id, THREAD#, SEQUENCE#, BYTES, USED, ARCHIVED, STATUS, FIRST_CHANGE#, NEXT_CHANGE#,LAST_CHANGE# from v$standby_log ;
GROUP# DB_ID THREAD# SEQUENCE# BYTES USED ARC STATUS FIRST_CHANGE# NEXT_CHANGE# LAST_CHANGE#
---------- --------------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------ ------------
7 2420371020 1 14345 1073741824 159744 YES ACTIVE 1.2234E+13 1.2234E+13 1.2234E+13
8 UNASSIGNED 1 0 1073741824 0 YES UNASSIGNED
9 UNASSIGNED 1 0 1073741824 0 YES UNASSIGNED
10 UNASSIGNED 1 0 1073741824 0 YES UNASSIGNED
11 UNASSIGNED 2 0 1073741824 0 YES UNASSIGNED
12 UNASSIGNED 2 0 1073741824 0 YES UNASSIGNED
13 UNASSIGNED 2 0 1073741824 0 YES UNASSIGNED
14 UNASSIGNED 2 0 1073741824 0 YES UNASSIGNED
8 rows selected.
恢复完成后发现主备库都没有standby日志，然后添加一下日志:
alter database add standby logfile thread 1 group 7 size 1073741824 ,group 8 size 1073741824 ,group 9 size 1073741824,group 10 size 1073741824 ;
alter database add standby logfile thread 2 group 11 size 1073741824 ,group 12 size 1073741824 ,group 13 size 1073741824,group 14 size 1073741824 ;
再次执行健康检查的时候发现DG库已经正常了。
DG库
| 参数名称 | 实例名称 | 参数值 |
| db_file_name_convert | oraNET1 | +DATA/oraNETR/datafile, +DATA/oraNET/datafile |
|
| oraNET2 | +DATA/oraNETR/datafile, +DATA/oraNET/datafile |
| fal_client | oraNET1 | oraNET |
|
| oraNET2 | oraNET |
| fal_server | oraNET1 | oraNETR |
|
| oraNET2 | oraNETR |
| log_archive_config | oraNET1 | DG_CONFIG=(oraNET,oraNETR) |
|
| oraNET2 | DG_CONFIG=(oraNET,oraNETR) |
| log_archive_dest_1 | oraNET1 | LOCATION=/archive/arch |
|
| oraNET2 | LOCATION=/archive/arch |
| log_archive_dest_2 | oraNET1 | SERVICE=oraNETR LGWR ASYNC VALID_FOR=(ONLINE_LOGFI LES,PRIMARY_ROLE) DB_UNIQUE_NAME=oraNETR |
|
| oraNET2 | SERVICE=oraNETR LGWR ASYNC VALID_FOR=(ONLINE_LOGFI LES,PRIMARY_ROLE) DB_UNIQUE_NAME=oraNETR |
| log_archive_dest_state_2 | oraNET1 | ENABLE |
|
| oraNET2 | ENABLE |
| log_file_name_convert | oraNET1 | +DATA/oraNETR/onlinelog, +DATA/oraNET/onlinelog |
|
| oraNET2 | +DATA/oraNETR/onlinelog, +DATA/oraNET/onlinelog |
| standby_file_management | oraNET1 | AUTO |
|
| oraNET2 | AUTO |
[回到目录]
| INST_ID | NAME | LOG_MODE | OPEN_MODE | DATABASE_ROLE | SWITCHOVER_STATUS | DB_UNIQUE_NAME | FLASHBACK_ON | PROTECTION_MODE | PROTECTION_LEVEL | REMOTE_ARCHIVE | SWITCHOVER# | SWITCHOVER_STATUS | DATAGUARD_BROKER | GUARD_STATUS | SUPPLEMENTAL_LOG | SUPPLE | SUPPLE | FORCE_ | SUPPLE | SUPPLE | STANDBY_BECAME_PRIMARY_SCN | FS_FAILOVER_STATUS | FS_FAILOVER_CURRENT_TARGET | FS_FAILOVER_THRESHOLD | FS_FAILOVER_OB | FS_FAILOVER_OBSERVER_HOST |
| 2 | ORANET | ARCHIVELOG | READ WRITE | PRIMARY | TO STANDBY | oraNET | NO | MAXIMUM PERFORMANCE | MAXIMUM PERFORMANCE | ENABLED | 2420341836 | TO STANDBY | DISABLED | NONE | NO | NO | NO | NO | NO | NO | 0 | DISABLED |
| 0 |
|
|
| 1 | ORANET | ARCHIVELOG | READ WRITE | PRIMARY | TO STANDBY | oraNET | NO | MAXIMUM PERFORMANCE | MAXIMUM PERFORMANCE | ENABLED | 2420341836 | TO STANDBY | DISABLED | NONE | NO | NO | NO | NO | NO | NO | 0 | DISABLED |
| 0 |
|
|
| THREAD# | DEST_ID | DEST_NAME | TARGET | DATABASE_MODE | STATUS | ERROR | RECOVERY_MODE | DB_UNIQUE_NAME | DESTINATION | GAP_STATUS | CURRENT_SEQ# | LAST_ARCHIVED | APPLIED_SEQ# | APPLIED_SCN |
| 1 | 1 | LOG_ARCHIVE_DEST_1 | LOCAL PRIMARY | OPEN | VALID |
| IDLE | NONE | /archive/arch |
| 14347 | 14346 |
| 0 |
| 1 | 2 | LOG_ARCHIVE_DEST_2 | PHYSICAL STANDBY | OPEN_READ-ONLY | VALID |
| MANAGED REAL TIME APPLY | oraNETR | oraNETR | NO GAP | 14347 | 14346 | 14345 | 12234221408001 |
| 2 | 1 | LOG_ARCHIVE_DEST_1 | LOCAL PRIMARY | OPEN | VALID |
| IDLE | NONE | /archive/arch |
| 13441 | 13440 |
| 0 |
| 2 | 2 | LOG_ARCHIVE_DEST_2 | PHYSICAL STANDBY | OPEN_READ-ONLY | VALID |
| MANAGED REAL TIME APPLY | oraNETR | oraNETR | NO GAP | 13441 | 13440 | 13440 | 12234221408001 |
[回到目录]
| INSTANCE_NAME | PROCESS | CLIENT_PROCESS | CLIENT_PID | STATUS | GROUP_# | THREAD# | SEQUENCE# | DELAY_MINS | RESETLOG_ID | SID | SERIAL# | SPID |
| oraNET1 | ARCH | ARCH | 12320920 | CLOSING | 2 | 1 | 14345 | 0 | 873307084 | 577 | 3 | 12320920 |
| oraNET1 | ARCH | ARCH | 11862152 | CLOSING | 1 | 1 | 14346 | 0 | 873307084 | 768 | 1 | 11862152 |
| oraNET1 | ARCH | ARCH | 12255366 | CLOSING | N/A | 1 | 14310 | 0 | 873307084 | 962 | 1 | 12255366 |
| oraNET1 | ARCH | ARCH | 12386452 | CLOSING | 3 | 1 | 14344 | 0 | 873307084 | 1152 | 1 | 12386452 |
| oraNET1 | LNS | LNS | 12648578 | WRITING | 3 | 1 | 14347 | 0 | 873307084 | 5 | 1 | 12648578 |
| oraNET2 | ARCH | ARCH | 13697186 | CLOSING | 6 | 2 | 13440 | 0 | 873307084 | 771 | 3 | 13697186 |
| oraNET2 | ARCH | ARCH | 13762724 | CLOSING | 6 | 2 | 13434 | 0 | 873307084 | 961 | 1 | 13762724 |
| oraNET2 | ARCH | ARCH | 13828262 | CLOSING | N/A | 2 | 13428 | 0 | 873307084 | 1153 | 1 | 13828262 |
| oraNET2 | ARCH | ARCH | 13959338 | CLOSING | N/A | 2 | 13439 | 0 | 873307084 | 6 | 1 | 13959338 |
| oraNET2 | LNS | LNS | 14155952 | WRITING | 5 | 2 | 13441 | 0 | 873307084 | 198 | 3 | 14155952 |
[回到目录]
备库DG进程
| INSTANCE_NAME | PROCESS | CLIENT_PROCESS | CLIENT_PID | STATUS | GROUP_# | THREAD# | SEQUENCE# | DELAY_MINS | RESETLOG_ID | SID | SERIAL# | SPID |
| oraNETR1 | ARCH | ARCH | 12910842 | CLOSING | 7 | 1 | 14346 | 0 | 873307084 | 1144 | 9 | 12910842 |
| oraNETR1 | ARCH | ARCH | 20906040 | CONNECTED | N/A | 0 | 0 | 0 | 0 | 10 | 1 | 20906040 |
| oraNETR1 | ARCH | ARCH | 13959174 | CONNECTED | N/A | 0 | 0 | 0 | 0 | 392 | 1 | 13959174 |
| oraNETR1 | ARCH | ARCH | 19595368 | CLOSING | 7 | 1 | 14345 | 0 | 873307084 | 773 | 7 | 19595368 |
| oraNETR1 | RFS | UNKNOWN | 12386452 | IDLE | N/A | 0 | 0 | 0 | 0 | 389 | 9 | 20054214 |
| oraNETR1 | RFS | UNKNOWN | 13959338 | IDLE | N/A | 0 | 0 | 0 | 0 | 771 | 7 | 19464320 |
| oraNETR1 | RFS | LGWR | 12648578 | IDLE | 3 | 1 | 14347 | 0 | 873307084 | 391 | 29 | 20840536 |
| oraNETR1 | RFS | UNKNOWN | 12320920 | IDLE | N/A | 0 | 0 | 0 | 0 | 775 | 3 | 12583132 |
| oraNETR1 | RFS | LGWR | 14155952 | IDLE | 5 | 2 | 13441 | 0 | 873307084 | 1154 | 1 | 21102682 |
| oraNETR2 | ARCH | ARCH | 19333240 | CONNECTED | N/A | 0 | 0 | 0 | 0 | 10 | 3 | 19333240 |
| oraNETR2 | ARCH | ARCH | 12845164 | CONNECTED | N/A | 0 | 0 | 0 | 0 | 392 | 1 | 12845164 |
| oraNETR2 | ARCH | ARCH | 14614690 | CONNECTED | N/A | 0 | 0 | 0 | 0 | 773 | 1 | 14614690 |
| oraNETR2 | ARCH | ARCH | 21495916 | CONNECTED | N/A | 0 | 0 | 0 | 0 | 1152 | 3 | 21495916 |
| oraNETR2 | MRP0 | N/A | N/A | APPLYING_LOG | N/A | 1 | 14347 | 0 | 873307084 | 12 | 5 | 15859922 |
| oraNETR2 | RFS | ARCH | 12255366 | IDLE | N/A | 0 | 0 | 0 | 0 | 779 | 1 | 19988678 |
| oraNETR2 | RFS | ARCH | 13828262 | IDLE | N/A | 0 | 0 | 0 | 0 | 771 | 5 | 3997896 |
[回到目录]
| INSTANCE | THREAD# | NAME | SEQUENCE# | ARCHIV | APPLIED | NEXT_CHANGE# |
| oraNET1 | 1 | /archive/arch/1_14344_873307084.dbf | 14344 | YES | YES | 12234221306722 |
| oraNET1 | 1 | /archive/arch/1_14345_873307084.dbf | 14345 | YES | YES | 12234221316152 |
| oraNET1 | 1 | /archive/arch/1_14346_873307084.dbf | 14346 | YES | IN-MEMORY | 12234221316559 |
| oraNET2 | 2 | /archive/arch/2_13438_873307084.dbf | 13438 | YES | YES | 12234221214634 |
| oraNET2 | 2 | /archive/arch/2_13439_873307084.dbf | 13439 | YES | YES | 12234221246543 |
| oraNET2 | 2 | /archive/arch/2_13440_873307084.dbf | 13440 | YES | YES | 12234221316477 |
[回到目录]
| GROUP# | DB_ID | THREAD# | SEQUENCE# | BYTES | USED | ARCHIV | STATUS | FIRST_CHANGE# | NEXT_CHANGE# | LAST_CHANGE# |
| 7 | UNASSIGNED | 1 | 0 | 1073741824 | 0 | YES | UNASSIGNED |
|
|
|
| 8 | UNASSIGNED | 1 | 0 | 1073741824 | 0 | YES | UNASSIGNED |
|
|
|
| 9 | UNASSIGNED | 1 | 0 | 1073741824 | 0 | YES | UNASSIGNED |
|
|
|
| 10 | UNASSIGNED | 1 | 0 | 1073741824 | 0 | YES | UNASSIGNED |
|
|
|
| 11 | UNASSIGNED | 2 | 0 | 1073741824 | 0 | YES | UNASSIGNED |
|
|
|
| 12 | UNASSIGNED | 2 | 0 | 1073741824 | 0 | YES | UNASSIGNED |
|
|
|
| 13 | UNASSIGNED | 2 | 0 | 1073741824 | 0 | YES | UNASSIGNED |
|
|
|
| 14 | UNASSIGNED | 2 | 0 | 1073741824 | 0 | YES | UNASSIGNED |
|
|
|
[回到目录]
备库standby日志
| INSTANCE | GROUP# | DB_ID | THREAD# | SEQUENCE# | BYTES | USED | ARCHIV | STATUS | FIRST_CHANGE# | LAST_CHANGE# |
| oraNET1 | 7 | UNASSIGNED | 1 | 0 | 1073741824 | 0 | NO | UNASSIGNED |
|
|
| oraNET1 | 8 | 2420371020 | 1 | 14347 | 1073741824 | 25055744 | YES | ACTIVE | 12234221316559 | 12234221411988 |
| oraNET1 | 9 | UNASSIGNED | 1 | 0 | 1073741824 | 0 | YES | UNASSIGNED |
|
|
| oraNET1 | 10 | UNASSIGNED | 1 | 0 | 1073741824 | 0 | YES | UNASSIGNED |
|
|
| oraNET2 | 11 | 2420371020 | 2 | 13441 | 1073741824 | 36782592 | YES | ACTIVE | 12234221316477 | 12234221411986 |
| oraNET2 | 12 | UNASSIGNED | 2 | 0 | 1073741824 | 0 | YES | UNASSIGNED |
|
|
| oraNET2 | 13 | UNASSIGNED | 2 | 0 | 1073741824 | 0 | YES | UNASSIGNED |
|
|
| oraNET2 | 14 | UNASSIGNED | 2 | 0 | 1073741824 | 0 | YES | UNASSIGNED |
|
|
[回到目录]
一切正常，测试一下实时同步的功能也是可以的。
文章写的比较简单，主要是因为之前有过类似的文章，但这篇是基于RAC环境的，对控制文件的处理稍有不同而已，详细可以参考：http://blog.itpub.net/26736162/viewspace-1780863/。
About Me
....................................................................................................................................................
本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
ITPUB BLOG：http://blog.itpub.net/26736162
本文地址：http://blog.itpub.net/26736162/viewspace-2087473/
本文pdf版：http://yunpan.cn/cdEQedhCs2kFz （提取码：ed9b）
QQ：642808185 若加QQ请注明您所正在读的文章标题
于 2016-04-20 10:00~ 2016-04-22 19:00 在中行完成
【版权所有，文章允许转载，但须以链接方式注明源地址，否则追究法律责任】
....................................................................................................................................................