Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › Oracle 11g 物理备库standby日志组疑问！
发新帖
ylw6006

29

积分

0

好友

0

主题

Oracle 11g 物理备库standby日志组疑问！

1#
发表于 2012-4-18 10:48:08 | 查看: 8764| 回复: 4
最近做了套ADG，主要用来实现数据的读写分离，RAC成本太高，穷人的用法！

通过连接主库duplicate target database for standby nofilenamecheck from active database的方式构建的备库，后面将物理备库启动到ADG状态的时候，online redolog日志组和standby 日志组会自动创建，默认创建在了$ORACLE_BASE/fast_recovery_area目录下，好不爽，于是想改！

在备库上查询standby日志的状态和名称如下：
SQL> col member format a50
SQL> select a.group#,a.status,b.member from v$standby_log a,v$logfile b where a.group#=b.group#;
    GROUP# STATUS     MEMBER
---------- ---------- --------------------------------------------------
         4 ACTIVE     /u01/app/oracle/fast_recovery_area/PRIMARY/onlinel
         5 UNASSIGNED /u01/app/oracle/oradata/eprowb2b/standby05a.dbf
         6 ACTIVE     /u01/app/oracle/oradata/eprowb2b/standby06a.dbf
         7 UNASSIGNED /u01/app/oracle/oradata/eprowb2b/standby07a.dbf

5-7三个standby日志组的名称是在将备库启动到mount状态后，设置standby_file_management参数为manual后，使用alter database rename file命令修改成功的，因为先前改的时候这3个日志的状态均为UNASSIGNED，然而4的状态则一直是active，因而在mount状态下修改会报ora-01511错误，请问刘总，有什么办法可以让备库的standby日志迅速的变成UNASSIGNED状态，我试过在主库上切换日志，不过备库上的standby日志组active状态不按套路出牌，一下换成4，一下换成6，过一会又换成4，搞的我头大!

[ 本帖最后由 ylw6006 于 2012-4-18 10:52 编辑 ]
2#
发表于 2012-4-18 13:56:40
尝试 临时把  primary 上的 log transfer 日志传输模式从  LGWR 改成ARCH模式。

回复 只看该作者 道具 举报

3#
发表于 2012-4-18 16:29:21
原帖由 maclean 于 2012-4-18 13:56 发表
尝试 临时把  primary 上的 log transfer 日志传输模式从  LGWR 改成ARCH模式。


测试过了，standby日志组4依旧是active状态，估计只能等下次做完switchover后再切换了！

回复 只看该作者 道具 举报

4#
发表于 2012-4-18 16:53:48
ODM FINDING:


Bug 7654309: OLD STANDBY REDO LOG REMAINS AS STATUS=ACTIVE ON V$STANDBY_LOG

Hdr: 7654309 10.2.0.3 RDBMS 10.2.0.3 RECOVERY PRODID-5 PORTID-214 5746174
Abstract: OLD STANDBY REDO LOG REMAINS AS STATUS=ACTIVE ON V$STANDBY_LOG

PROBLEM:
--------
[environment]
primary site         --- 4node-RAC
logical standby site --- 4node-RAC

[problem]
The old Standby Redo Log entry remains as STATUS=ACTIVE on V$STANDBY_LOG.
We could see two entries thread#1 seqence#17 and thread#3 seqence#16.
The sequence of those logs are already applied by seeing DBA_LOGSTDBY_LOG.
So those Standby Redo Log remains without being reused until recreate
Standby Redo Log.

SQL> select * from v$standby_log;

GROUP# DBID        THREAD#  SEQUENCE#      USED   ARCHIVED  STATUS     
------ ----------- -------- ---------- ---------- --------- -----------
    25 1457123581        1         17    1573376  YES       ACTIVE     <-----
    26 UNASSIGNED        1          0        512  NO        UNASSIGNED
    27 1457123581        1         74      99328  YES       ACTIVE     
    28 UNASSIGNED        1          0        512  YES       UNASSIGNED
    29 UNASSIGNED        1          0        512  YES       UNASSIGNED
    30 UNASSIGNED        1          0        512  YES       UNASSIGNED
    31 UNASSIGNED        2          0        512  NO        UNASSIGNED
    32 1457123581        2         70      49152  YES       ACTIVE     
    33 UNASSIGNED        2          0        512  YES       UNASSIGNED
    34 UNASSIGNED        2          0        512  YES       UNASSIGNED
    35 UNASSIGNED        2          0        512  YES       UNASSIGNED
    36 UNASSIGNED        2          0        512  YES       UNASSIGNED
    37 1457123581        3         16    1206272  YES       ACTIVE     <-----
    38 1457123581        3         74     103936  YES       ACTIVE     
    39 UNASSIGNED        3          0        512  NO        UNASSIGNED
    40 UNASSIGNED        3          0        512  YES       UNASSIGNED
    41 UNASSIGNED        3          0        512  YES       UNASSIGNED
    42 UNASSIGNED        3          0        512  YES       UNASSIGNED
    43 UNASSIGNED        4          0        512  NO        UNASSIGNED
    44 1457123581        4         75      49664  YES       ACTIVE     
    45 UNASSIGNED        4          0        512  NO        UNASSIGNED
    46 UNASSIGNED        4          0        512  YES       UNASSIGNED
    47 UNASSIGNED        4          0        512  YES       UNASSIGNED
    48 UNASSIGNED        4          0        512  YES       UNASSIGNED

SQL> select * from dba_logstdby_log;

THREAD#  SEQUENCE# FILE_NAME                            APPLIED
------- ---------- -----------------------------------  -------
     1          17 /DBDD/sarchive/1_17_670942747.arc    YES
     3          16 /DBDD/sarchive/3_16_670942747.arc    YES

DIAGNOSTIC ANALYSIS:
--------------------
We comfirmed alertlog on logical standby.
Almost log are archived by ARC to standby_archive_dest, but that two
logs are archived by RFS.
And after complete archive and register, LOGMINER transits to archivelog
on standby_archive_dest from Standby Redo Log.

[alertlog on logical standby(alert_DBDD541.log)]
- focus on thread#1 sequence#17

Sun Nov 16 16:50:50 2008
RFS[43]: Begin archive primary thread 1 sequence 17 (DBDD541)
Sun Nov 16 16:50:50 2008
RFS[43]: Successfully opened standby log 25: '/dev/raw/raw161' <----- open
SRL
Sun Nov 16 16:50:58 2008
LOGMINER: Begin mining logfile: /dev/raw/raw161 <----- begin mining SRL
Sun Nov 16 17:04:30 2008
RFS[43]: Possible network disconnect with primary database
Sun Nov 16 17:05:03 2008
RFS[38]: Begin archive primary thread 1 sequence 17 (DBDD541)
Sun Nov 16 17:05:04 2008
RFS[38]: Completed archive primary log 0 thread 1 sequence 17 (DBDD541)
Sun Nov 16 17:05:04 2008
RFS[38]: Archived Log: '/DBDD/sarchive/1_17_670942747.arc' <--- archive by
RFS
Committing creation of archivelog '/DBDD/sarchive/1_17_670942747.arc'
Sun Nov 16 17:05:04 2008
RFS LogMiner: Registered logfile [/DBDD/sarchive/1_17_670942747.arc]
to LogMiner session id [82]
Sun Nov 16 17:05:04 2008
LOGMINER: Archived logfile found, transition to mining logfile:
/DBDD/sarchive/1_17_670942747.arc              <----- mining transit
Sun Nov 16 17:05:06 2008
LOGMINER: Archived logfile found, transition to mining logfile:
/DBDD/sarchive/1_17_670942747.arc
Sun Nov 16 17:05:16 2008
LOGMINER: End mining logfile: /DBDD/sarchive/1_17_670942747.arc
================================================

The situation of that two logs is as follows.
1. archived to standby_archive_dest by RFS (Other logs archived by ARCH)

2. mining through following steps.
  2-1. Begin mining Standby Redo Log at first.
  2-2. After complete archiving by RFS to standby_archive_dest,
       LOGMINER transits mining logfile to archivelog on
standby_archive_dest.
  2-3. End mining logfile

3. Standby Redo Log entry remains as STATUS=ACTIVE on V$STANDBY_LOG.

We confirmed problem about deleting entry of Standby Redo Log.
And could not find another bugs and notes about this behavior.

WORKAROUND:
-----------
none.

alert.log shows 4 RFS process stopped by network disconnect at
same time.
-------------------------
Sun Nov 16 17:04:30 2008
RFS[54]: Possible network disconnect with primary database
Sun Nov 16 17:04:30 2008
RFS[46]: Possible network disconnect with primary database
Sun Nov 16 17:04:30 2008
RFS[50]: Possible network disconnect with primary database
Sun Nov 16 17:04:30 2008
RFS[43]: Possible network disconnect with primary database
-------------------------
RFS[54] was archiving thread 4 seq 16 to /dev/raw/raw180
RFS[46] was archiving thread 2 seq 12 to /dev/raw/raw168
RFS[50] was archiving thread 3 seq 16 to /dev/raw/raw173
RFS[43] was archiving thread 1 seq 17 to /dev/raw/raw161
These destinations are standby logfiles.
No "RFS[n]: Completed archive ..." log is written for these.

Customer see entries for thread 1 seq 17 and thread 3 seq 16 are
left in v$standby_log

thread 2 sequence 12 is processed later by RFS[47] using
same standby logfile. thread 4 sequence 16 is processed by same way.
------------------------------
Sun Nov 16 17:05:03 2008
RFS[47]: Begin archive primary thread 2 sequence 12 (DBDD541)
Sun Nov 16 17:05:03 2008
RFS[47]: Successfully opened standby log 32: '/dev/raw/raw168'
Sun Nov 16 17:05:04 2008
RFS[47]: Completed archive primary log 0 thread 2 sequence 12 (DBDD541)

回复 只看该作者 道具 举报

5#
发表于 2012-4-28 10:56:47
原帖由 maclean 于 2012-4-18 16:53 发表
ODM FINDING:


Bug 7654309: OLD STANDBY REDO LOG REMAINS AS STATUS=ACTIVE ON V$STANDBY_LOG

Hdr: 7654309 10.2.0.3 RDBMS 10.2.0.3 RECOVERY PRODID-5 PORTID-214 5746174
Abstract: OLD STANDBY REDO LOG REM ...


今天搞定这个问题了，之前是因为关闭备库的时候未先关闭mrp进程，一直存在误区，以为使用shutdown immediate命令关闭备库，备库会先关闭mrp进程！

SQL> select status,group# from v$standby_log;
STATUS         GROUP#
---------- ----------
UNASSIGNED          4
ACTIVE              5
UNASSIGNED          6
UNASSIGNED          7
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/eprowb2b/redo03_a.dbf
/u01/app/oracle/oradata/eprowb2b/redo02_a.dbf
/u01/app/oracle/oradata/eprowb2b/redo01_a.dbf
/u01/app/oracle/oradata/eprowb2b/standby04a.dbf
/u01/app/oracle/oradata/eprowb2b/standby05a.dbf
/u01/app/oracle/oradata/eprowb2b/standby06a.dbf
/u01/app/oracle/oradata/eprowb2b/standby07a.dbf

回复 只看该作者 道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-11-15 12:33 , Processed in 0.050448 second(s), 21 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569