发现DG的主备库不同步,解决方法:
主库:
sys@G_CHRIS> select sequence#,first_change#,next_change#,applied,activation# from v$archived_log;
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# APPLIED ACTIVATION#
---------- ------------- ------------ --------- -----------
.... ...
68 1706805 1743500 NO 1376726418
69 1743500 1743529 NO 1376726418
69 1743500 1743529 YES 1376726418
70 1743529 1743540 NO 1376726418
71 1743540 1779628 NO 1376726418
71 1743540 1779628 NO 1376726418
72 1779628 1815021 NO 1376726418
73 1815021 1852327 NO 1376726418
74 1852327 1885418 NO 1376726418
75 1885418 1915931 NO 1376726418
76 1915931 1915938 NO 1376726418
99 rows selected.
备库:
sys@G_CHRIS> select sequence#,first_change#,next_change#,applied,activation# from v$archived_log;
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# APPLIED ACTIVATION#
---------- ------------- ------------ --------- -----------
.........
60 1490505 1530325 YES 1376726418
61 1530325 1563382 YES 1376726418
62 1563382 1577449 YES 1376726418
63 1577449 1577462 YES 1376726418
64 1577462 1597050 YES 1376726418
65 1597050 1635207 YES 1376726418
66 1635207 1671193 YES 1376726418
67 1671193 1706805 YES 1376726418
68 1706805 1743500 YES 1376726418
69 1743500 1743529 YES 1376726418
71 1743540 1779628 NO 1376726418
51 rows selected.
发现缺少好几个日志文件
查看主库的告警日志有大量的:
[oracle@master ~]$ tail -f /u01/app/diag/rdbms/g_chris_g1/chris/trace/alert_chris.log
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
------------------------------------------------------------
Errors in file /u01/app/diag/rdbms/g_chris_g1/chris/trace/chris_arc2_4303.trc:
ORA-16191: Primary log shipping client not logged on standby
PING[ARC2]: Heartbeat failed to connect to standby 'g_chris_g2'. Error is 16191.
看到这样的告警,感觉是主备库的sys密码不一致导致的,所以:
sys@G_CHRIS> alter user sys identified by sysoracle;
User altered.
sys@G_CHRIS> !
[oracle@master ~]$ cd /u01/app/oracle/dbs/
[oracle@master dbs]$ ls
contr03.ctl initchris.ora lkG_CHRIS orapwchris peshm_g_chris_g1_0 spfilechris.ora
hc_chris.dat init.ora lkG_CHRIS_G1 peshm_g_chris_0 snapcf_chris.f
[oracle@master dbs]$ scp orapwchris 192.168.8.112:/u01/app/oracle/dbs/
The authenticity of host '192.168.8.112 (192.168.8.112)' can't be established.
RSA key fingerprint is 66:cb:4d:0a:92:81:ac:50:d6:3e:d9:4c:42:5e:05:95.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.8.112' (RSA) to the list of known hosts.
oracle@192.168.8.112's password:
orapwchris 100% 1536 1.5KB/s 00:00
再看主库告警日志:
[oracle@master dbs]$ tail -f /u01/app/diag/rdbms/g_chris_g1/chris/trace/alert_chris.log
returning error ORA-16191
------------------------------------------------------------
Errors in file /u01/app/diag/rdbms/g_chris_g1/chris/trace/chris_arc2_4303.trc:
ORA-16191: Primary log shipping client not logged on standby
PING[ARC2]: Heartbeat failed to connect to standby 'g_chris_g2'. Error is 16191.
Wed Jun 13 11:21:17 2012
Thread 1 advanced to log sequence 78 (LGWR switch)
Current log# 2 seq# 78 mem# 0: +DATA/g_chris/oradata/redo02.log
Wed Jun 13 11:21:18 2012
Archived Log entry 100 added for thread 1 sequence 77 ID 0x520f2d92 dest 1:
发现sequence 77已经传输过去
验证备库:
sys@G_CHRIS> select sequence#,first_change#,next_change#,applied,activation# from v$archived_log;
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# APPLIED ACTIVATION#
---------- ------------- ------------ --------- -----------
.........................
58 1421218 1455522 YES 1376726418
59 1455522 1490505 YES 1376726418
60 1490505 1530325 YES 1376726418
61 1530325 1563382 YES 1376726418
62 1563382 1577449 YES 1376726418
63 1577449 1577462 YES 1376726418
64 1577462 1597050 YES 1376726418
65 1597050 1635207 YES 1376726418
66 1635207 1671193 YES 1376726418
67 1671193 1706805 YES 1376726418
68 1706805 1743500 YES 1376726418
69 1743500 1743529 YES 1376726418
71 1743540 1779628 NO 1376726418
77 1915938 1918287 NO 1376726418
52 rows selected.
说明主备库已经可以正常传输日志了,但备库还缺少sequence为70,72,73,74,75,76的归档日志
查看主库的归档日志:
[oracle@master ~]$ ls /u01/app/archlog/g_chris/ -lht
总计 121M
-rw-r----- 1 oracle oinstall 1.5K 06-13 10:56 1_76_783430861.dbf
-rw-r----- 1 oracle oinstall 8.8M 06-13 10:56 1_75_783430861.dbf
-rw-r----- 1 oracle oinstall 26M 06-11 08:00 1_74_783430861.dbf
-rw-r----- 1 oracle oinstall 47M 06-11 00:00 1_73_783430861.dbf
-rw-r----- 1 oracle oinstall 40M 06-10 16:00 1_72_783430861.dbf
没发现sequence为70的归档日志,决定从RMAN备份中提取出来:
[oracle@master g_chris]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jun 13 14:00:22 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: G_CHRIS (DBID=1374986122)
RMAN> restore archivelog sequence 70;
Starting restore at 13-JUN-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=70
channel ORA_DISK_1: reading from backup piece /u01/app/rmanback/arch/arch_G_CHRIS_20120610_3gnd51gr_1_1
channel ORA_DISK_1: piece handle=/u01/app/rmanback/arch/arch_G_CHRIS_20120610_3gnd51gr_1_1 tag=TAG20120610T000017
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 13-JUN-12
RMAN> quit
Recovery Manager complete.
[oracle@master g_chris]$ ls /u01/app/archlog/g_chris/
1_70_783430861.dbf 1_73_783430861.dbf 1_75_783430861.dbf 1_77_783430861.dbf
1_72_783430861.dbf 1_74_783430861.dbf 1_76_783430861.dbf 1_78_783430861.dbf
拷贝归档日志到备库:
[oracle@master g_chris]$ scp 1_{72,73,74,75,76}_783430861.dbf 192.168.8.112:/u01/app/archlog/g_chris/
oracle@192.168.8.112's password:
1_70_783430861.dbf 100% 4KB 4KB/s 00:00
1_72_783430861.dbf 100% 40MB 19.8MB/s 00:02
1_73_783430861.dbf 100% 47MB 9.3MB/s 00:05
1_74_783430861.dbf 100% 26MB 25.7MB/s 00:01
1_75_783430861.dbf 100% 8987KB 8.8MB/s 00:01
1_76_783430861.dbf 100% 1536 1.5KB/s 00:00
在备库手动注册这些文件:
sys@G_CHRIS> Alter database register logfile '/u01/app/archlog/g_chris/';
Alter database register logfile '/u01/app/archlog/g_chris/'
*
ERROR at line 1:
ORA-00308: cannot open archived log '/u01/app/archlog/g_chris/'
ORA-27037: unable to obtain file status
Additional information: 5
这里说添加了5条信息,其实是添加了6条,而且报错了,应该是sequence 70导致的,从下面的 ACTIVATION#可以猜测出来
sys@G_CHRIS> select sequence#,first_change#,next_change#,applied,activation# from v$archived_log;
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# APPLIED ACTIVATION#
---------- ------------- ------------ --------- -----------
......................
63 1577449 1577462 YES 1376726418
64 1577462 1597050 YES 1376726418
65 1597050 1635207 YES 1376726418
66 1635207 1671193 YES 1376726418
67 1671193 1706805 YES 1376726418
68 1706805 1743500 YES 1376726418
69 1743500 1743529 YES 1376726418
71 1743540 1779628 YES 1376726418
77 1915938 1918287 YES 1376726418
78 1918287 1918741 YES 1376726418
70 1743529 1743540 YES 0 ---出现这个问题应该是库异常关闭导致的
72 1779628 1815021 YES 1376726418
75 1885418 1915931 YES 1376726418
76 1915931 1915938 YES 1376726418
74 1852327 1885418 YES 1376726418
73 1815021 1852327 YES 1376726418
56 rows selected.
验证主备库是否同步:
主库:
sys@G_CHRIS> select * from test2;
select * from test2
*
ERROR at line 1:
ORA-00942: table or view does not exist
备库:
sys@G_CHRIS> select * from test2;
select * from test2
*
ERROR at line 1:
ORA-00942: table or view does not exist
主库:
sys@G_CHRIS> create table test2 as select * from test1 where 1=2;
Table created.
sys@G_CHRIS> alter system switch logfile;
System altered.
备库:
sys@G_CHRIS> select * from test2;
no rows selected
验证OK!