DG物理备库的实时查询设置（失败）

最新推荐文章于 2024-04-25 09:07:33 发布

cuili9760

最新推荐文章于 2024-04-25 09:07:33 发布

阅读量382

点赞数

文章标签：数据库运维

在主库进行以下操作：
SQL> select count(1) from dbpri_t3;

COUNT(1)
----------
10924

SQL> delete from dbpri_t3 where rownum<10000;

9999 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(1) from dbpri_t3;

COUNT(1)
----------
925

此时查询备库dbstd：

SQL> select count(1) from dbpri_t3;

COUNT(1)
----------
10924

结果未发生变化。
再到主库dbpri执行：
SQL> alter system switch logfile;

System altered.

再次查询备库dbstd：
SQL> select count(1) from dbpri_t3;

COUNT(1)
----------
925

=================================================================================

在备库查询
SQL> col name for a30
SQL> col datum_time for a30
SQL> col value for a20
SQL> col time_computed for a30
SQL> set linesize 200
SQL> SELECT name, value, datum_time, time_computed FROM V$DATAGUARD_STATS WHERE name like 'apply lag';

NAME VALUE DATUM_TIME TIME_COMPUTED
------------------------------ -------------------- ------------------------------ ------------------------------
apply lag +00 00:05:12 04/17/2014 09:35:36 04/17/2014 09:36:07

以上查询用于监控实时查询环境的apply lag
apply lag是根据周期性从主库获取的数据得到的。
datum_time是上次收到此数据的时间戳
time_computed是计算apply lag的时刻
两者之差应小于30s，否则apply lag可能不准确。

下面查询每次获取apply lag的详细信息：
SQL> set pages 1000
SQL> SELECT * FROM V$STANDBY_EVENT_HISTOGRAM WHERE NAME = 'apply lag' AND COUNT > 0;

NAME TIME UNIT COUNT LAST_TIME_UPDATED
------------------------------ ---------- ---------------- ---------- --------------------
apply lag 0 seconds 2 04/17/2014 09:30:37
apply lag 14 seconds 1 04/17/2014 09:30:38
apply lag 15 seconds 1 04/17/2014 09:30:39
……
apply lag 11 minutes 59 04/17/2014 09:41:23
apply lag 12 minutes 54 04/17/2014 09:42:18

58 rows selected.

查看了两个节点的时间，发现相差7小时！
修改后，重启服务器（虚拟机）。
再次查询：
SQL> col name for a30
SQL> col datum_time for a30
SQL> col value for a20
SQL> col time_computed for a30
SQL> set linesize 200
SQL> SELECT name, value, datum_time, time_computed FROM V$DATAGUARD_STATS WHERE name like 'apply lag';

NAME VALUE DATUM_TIME TIME_COMPUTED
------------------------------ -------------------- ------------------------------ ------------------------------
apply lag +00 00:00:00 04/17/2014 15:52:43 04/17/2014 15:53:40

查询allpy lag，发现值一直在变：

SQL> /

NAME VALUE
------------------------------ --------------------
transport lag +00 00:00:00
apply lag +00 00:12:40
apply finish time
estimated startup time 43

SQL> /

NAME VALUE
------------------------------ --------------------
transport lag +00 00:00:00
apply lag +00 00:13:41
apply finish time
estimated startup time 43

由此，应该是日志一直没有应用成功。

核对主库的spfile，发现其实造成apply lag增长的原因并不是主库和该物理备库的日志没有接收成功，而是额外设置的逻辑备库没有接收日志成功。
将对应的两行注释掉：
*.log_archive_config='dg_config=(dbpri,dbstd,dblstd)'
*.log_archive_dest_1='location=/opt/ora10g/archive valid_for=(all_logfiles,all_roles) db_unique_name=dbpri'
*.log_archive_dest_2='service=dbstd async valid_for=(online_logfiles,primary_role) db_unique_name=dbstd'
#*.log_archive_dest_3='service=dblstd async valid_for=(online_logfiles,primary_role) db_unique_name=dblstd'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
#*.log_archive_dest_state_3='enable'

重启主库后，再次查询仍然没有解决。（apply lag的value仍然不是0）

重新查看以前执行的命令。
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect from session;命令并不是实时应用redo日志，
应该执行：
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE （disconnect from session）;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.

SQL> col name for a30
SQL> col datum_time for a30
SQL> col value for a20
SQL> col time_computed for a30
SQL> set linesize 200
SQL> SELECT name, value, datum_time, time_computed FROM V$DATAGUARD_STATS WHERE name like 'apply lag';

NAME VALUE DATUM_TIME TIME_COMPUTED
------------------------------ -------------------- ------------------------------ ------------------------------
apply lag 04/17/2014 16:39:10

这时没有发生过日志的同步，因此value为空。
重启主库后，再次在备库查询：

SQL> SELECT name, value, datum_time, time_computed FROM V$DATAGUARD_STATS WHERE name like 'apply lag';

NAME VALUE DATUM_TIME TIME_COMPUTED
------------------------------ -------------------- ------------------------------ ------------------------------
apply lag +00 00:00:00 04/17/2014 16:39:26 04/17/2014 16:39:28

但是执行下列命令仍然失败：

SQL> ALTER SESSION SYNC WITH PRIMARY;
ERROR:
ORA-03173: Standby may not be synced with primary

在OTN查询相关bug，发现所使用的11.2.0.1确实有类似bug：

Description

ALTER SESSION SYNC WITH PRIMARY returns ORA-3173 at primary log switch

Rediscovery Notes:
ORA-3173 is raised on standby during a primary log switch


bug已经在11.2.0.2修复了。
但尚不能确认遇到的是否是bug。
【 Bug 9467704 - ALTER SESSION SYNC with primary returns ORA-3173 at primary log switch (文档 ID 9467704.8)】

================================================================================

下面为备库设置最大可接受的延迟时间，并查看效果：
从库执行：
SQL> ALTER SESSION SET STANDBY_MAX_DATA_DELAY=2;
ERROR:
ORA-03174: STANDBY_MAX_DATA_DELAY does not apply to SYS users

注：该参数只能用于非sys用户的会话级生效。

SQL> conn scott/scott
Connected.
SQL> ALTER SESSION SET STANDBY_MAX_DATA_DELAY=2;

Session altered.

在主库执行：

SQL> create table emp1 as select * from emp;

Table created.

SQL> desc emp1;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

回到从库执行：

SQL> desc emp1;
ERROR:
ORA-00604: error occurred at recursive SQL level 2
ORA-03172: STANDBY_MAX_DATA_DELAY of 2 seconds exceeded
ORA-00604: error occurred at recursive SQL level 1
ORA-03172: STANDBY_MAX_DATA_DELAY of 2 seconds exceeded

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26451536/viewspace-1144471/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26451536/viewspace-1144471/