|
|
本帖最后由 lujiaguai 于 2017-6-30 17:06 编辑
唐老师，站点迁移后，丢数据了啊~
做data guard的时候遇到一个问题，是这样的
1，我在 cloud conrol上把物理备库转换为快照备用
2，然后用sqlplus 命令行，关闭快照备用数据库，再mount起来，执行alter database covert to pyhsical standby
3, 命令成功执行，然后在备库mount的情况下，执行 alter database recover managed standby using current logfile disconnect ，命令也成功执行
4，此时我在主库及备库上查询：select sequence#,applied,registrar,archived,FIRST_TIME from v$archived_log order by 1;
主备库上查询的结果是一致的，备库上最大的sequence# 的 applied=yes，只有中间几个在快照备用期间的日志 applied=no
如下:
SQL> select sequence#,applied,registrar,archived,FIRST_TIME from v$archived_log order by 1;
SEQUENCE# APPLIED REGISTR ARC FIRST_TIME
---------- --------- ------- --- -------------------
1 NO ARCH YES 2017-06-27:11:11:59
1 NO ARCH YES 2017-06-23:16:53:02
2 NO ARCH YES 2017-06-27:22:01:04
2 NO ARCH YES 2017-06-24:01:00:08
3 NO ARCH YES 2017-06-24:12:44:34
4 NO ARCH YES 2017-06-24:22:09:55
18 YES RFS YES 2017-06-18:18:02:28
19 YES RFS YES 2017-06-19:00:25:17
20 YES RFS YES 2017-06-19:12:58:03
21 YES RFS YES 2017-06-19:22:00:18
22 YES RFS YES 2017-06-20:08:05:59
SEQUENCE# APPLIED REGISTR ARC FIRST_TIME
---------- --------- ------- --- -------------------
23 YES RFS YES 2017-06-20:20:52:27
24 YES RFS YES 2017-06-21:02:48:40
25 YES RFS YES 2017-06-21:15:00:10
26 YES RFS YES 2017-06-21:22:00:32
27 YES RFS YES 2017-06-22:10:03:17
28 YES RFS YES 2017-06-22:22:00:06
29 YES RFS YES 2017-06-23:06:17:11
30 YES RFS YES 2017-06-23:16:53:27
31 YES RFS YES 2017-06-23:22:00:23
32 YES RFS YES 2017-06-24:07:14:21
33 YES RFS YES 2017-06-24:14:03:09
SEQUENCE# APPLIED REGISTR ARC FIRST_TIME
---------- --------- ------- --- -------------------
34 YES RFS YES 2017-06-24:22:03:36
35 YES RFS YES 2017-06-25:08:17:27
36 YES RFS YES 2017-06-25:13:43:19
37 YES RFS YES 2017-06-25:13:44:03
38 YES RFS YES 2017-06-25:13:45:28
39 YES RFS YES 2017-06-25:13:45:55
40 YES RFS YES 2017-06-25:21:43:01
41 YES RFS YES 2017-06-26:08:00:05
42 YES RFS YES 2017-06-26:15:00:43
43 YES RFS YES 2017-06-26:22:00:12
44 YES RFS YES 2017-06-27:04:42:59
SEQUENCE# APPLIED REGISTR ARC FIRST_TIME
---------- --------- ------- --- -------------------
45 YES RFS YES 2017-06-27:09:01:00
46 YES RFS YES 2017-06-27:10:58:54
47 YES RFS YES 2017-06-27:10:59:11
48 YES RFS YES 2017-06-27:11:00:03
49 YES RFS YES 2017-06-27:11:02:48
50 YES RFS YES 2017-06-27:11:05:17
51 YES RFS YES 2017-06-27:11:08:17
52 YES RFS YES 2017-06-27:11:09:07
53 YES RFS YES 2017-06-27:11:11:10
54 YES RFS YES 2017-06-27:11:12:09
55 YES RFS YES 2017-06-27:22:00:13
SEQUENCE# APPLIED REGISTR ARC FIRST_TIME
---------- --------- ------- --- -------------------
56 YES RFS YES 2017-06-28:06:00:45
57 YES RFS YES 2017-06-28:09:39:42
58 YES RFS YES 2017-06-28:09:41:00
59 YES RFS YES 2017-06-28:09:46:58
60 YES RFS YES 2017-06-28:09:48:36
61 YES RFS YES 2017-06-28:10:02:42
62 YES RFS YES 2017-06-28:10:11:56
63 YES RFS YES 2017-06-28:22:00:06
64 YES RFS YES 2017-06-29:06:00:32
65 YES RFS YES 2017-06-29:10:04:48
66 YES RFS YES 2017-06-29:22:00:11
SEQUENCE# APPLIED REGISTR ARC FIRST_TIME
---------- --------- ------- --- -------------------
67 YES RFS YES 2017-06-30:06:00:35
另外，查询了 select client_process,process,thread#,sequence#,status from v$managed_standby;
结果看似也是没有问题的：
SQL> select client_process,process,thread#,sequence#,status from v$managed_standby;
CLIENT_P PROCESS THREAD# SEQUENCE# STATUS
-------- --------- ---------- ---------- ------------
ARCH ARCH 1 67 CLOSING
ARCH ARCH 1 66 CLOSING
ARCH ARCH 0 0 CONNECTED
ARCH ARCH 1 68 CLOSING
ARCH RFS 0 0 IDLE
LGWR RFS 1 69 IDLE
UNKNOWN RFS 0 0 IDLE
N/A MRP0 1 69 APPLYING_LOG
同时无论是主库还是备库，alret日志都没有看到明显的告警
但是现在我发现在cloud control上,选择data guard管理页面，却显示dg的状态为：ORA-16810: multiple errors or warnings detected for the database并且依然是快照备用：
截图如下：
同时我在 dgmgrl 命令行下，show configuration，看到的也是这样的信息：
DGMGRL> show configuration
Configuration - orcl188
Protection Mode: MaxPerformance
Databases:
orcl188 - Primary database
dorcl188 - Snapshot standby database
Error: ORA-16810: multiple errors or warnings detected for the database
Fast-Start Failover: DISABLED
Configuration Status:
ERROR
另外在备库上 show database verbose ‘备库名’，也是有报错：Intended State: APPLY-OFF
DGMGRL> show database verbose dorcl188
Database - dorcl188
Role: SNAPSHOT STANDBY
Intended State: APPLY-OFF
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Instance(s):
dorcl188
Warning: ORA-16782: instance not open for read and write access
Database Error(s):
ORA-16816: incorrect database role
Properties:
DGConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=centos189)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dorcl188)(SERVER=DEDICATED)))'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'auto'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = 'null, null'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
SidName = 'dorcl188'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=centos189)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dorcl188_DGMGRL)(INSTANCE_NAME=dorcl188)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
ERROR
唐sir,我目前的这个DG状态，到底是正常还是不正常？
我在看 oracle data guard 11g HANDBOOK这本厚厚的书的时候，见过其中有类似的描述
大概的意思说，如果用sqlplus，BROKER，GRID CONTROL三种方式管理DG，如果用了后两种，则不能使用第一种
不知道是不是这样，亦或者是我理解有误？这不是我用图形界面和sqlplus混合在一起操作了DG状态的转换造成的？
目前这个状态，有办法修复吗？
|
|
发表于 2017-6-30 17:03:14
发表于 2017-7-3 15:49:26
楼主