ITPub博客

首页 > Linux操作系统 > Linux操作系统 > DataGuard Logical standby维护命令手册

DataGuard Logical standby维护命令手册

原创 Linux操作系统 作者:chriszhang_1117 时间:2009-04-13 13:09:30 0 删除 编辑
转帖一篇eygle的文章.

DataGuard Logical standby维护命令手册

链接:http://www.eygle.com/digest/2009/02/logical_standby_manual.html

1.查看logical standby上当前正在applyredo log

COLUMN DICT_BEGIN FORMAT A15;
COLUMN FILE_NAME FORMAT A30;
SET NUMF 9999999;
COL FCHANGE# format 9999999999999;
COL NCHANGE# for 999999999999999999999;
SET line 200
SELECT  file_name, sequence# AS seq#, first_change# AS fchange#,
        next_change# AS nchange#, TIMESTAMP, dict_begin AS beg,
        dict_end AS END, thread# AS thr#, applied
    FROM dba_logstdby_log
ORDER BY thread#;


2. 查看logical standby节点apply进程状态

select sid,type,status_code,status from v$logstdby_process;


查看applyer进程的个数

SELECT COUNT(*) AS APPLIER_COUNT FROM V$LOGSTDBY_PROCESS WHERE TYPE = 'APPLIER';


查看空闲的applyer进程

SELECT COUNT(*) AS IDLE_APPLIER 
FROM V$LOGSTDBY_PROCESS WHERE TYPE = 'APPLIER' and status_code = 16166;


3. 调整logical standbyapply进程数

ALTER DATABASE STOP LOGICAL STANDBY APPLY; --- Stop SQL Apply
EXECUTE DBMS_LOGSTDBY.APPLY_SET('APPLY_SERVERS', 20); ---
调整apply进程数为20,默认为5
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE; --- Start real-time Apply


确认logical standby上的空闲APPLIER进程

SELECT COUNT (*) AS idle_applier
  FROM v$logstdby_process
WHERE TYPE = 'APPLIER' AND status_code = 16166;


注:status_code = 16166 表示进程是空闲状态,可以看到"STATS""ORA-16116: no work available"

如何根据系统表现调整APPLIER进程的个数,统计transactionsapply状态,

SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS WHERE NAME LIKE 'transactions%';

NAME                        VALUE
-------------------------- ----------------------------
transactions ready          159
transactions applied        159


如果ready(等待apply)applied(已经apply)的值基本同步,则设置的APPLIER进程合适或偏多。根据IDLE_APPLIER的进程数,可减少APPLIER进程数目.如果transactions ready - transactions applied的差比APPLIER进程数的2倍还多,则需要增加APPLIER进程数目了。

4. 调整PREPARER(调制机)的进程数

logical standby上有很多transactions等待apply, 但是还有空闲的applyer进程,且已经没有idle状态的PREPARER进程,这时需要增加PREPARER(调制机)的进程数。如下:

ALTER DATABASE STOP LOGICAL STANDBY APPLY; --- Stop SQL Apply
EXECUTE DBMS_LOGSTDBY.APPLY_SET('PREPARE_SERVERS', 4); ---
调整PREPARER进程数为4
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE; --- Start re
al-time Apply


5. 计算logical standbyapply速度

可以通过v$logstdby_stats视图统计logical standbyapply速度,计算公式如下:
apply_rate = bytes of redo processed / (coordinator uptime - seconds system is idle)

比如:

SELECT NAME, VALUE
  FROM v$logstdby_stats
WHERE NAME IN
          ('coordinator uptime',
          'seconds system is idle',
          'bytes of redo processed'
          );

NAME                        VALUE
--------------------------- ---------------------------------
coordinator uptime          78717
bytes of redo processed    7954813012
seconds system is idle      40

logical standby.apply_rate = 7954813012/(78717-40)/1024/1024


整理成1sql语句计算出apply_rate如下:

SELECT c.VALUE / (a.VALUE - b.VALUE) / 1024 / 1024 AS "APPLY_RATE"
  FROM v$logstdby_stats a, v$logstdby_stats b, v$logstdby_stats c
WHERE a.NAME = 'coordinator uptime'
  AND b.NAME = 'seconds system is idle'
  AND c.NAME = 'bytes of redo processed';



6. 统计logical standby上是否有报错信息

SELECT xidusn, xidslt, xidsqn, status, status_code
  FROM dba_logstdby_events
WHERE event_time = (SELECT MAX (event_time)
                      FROM dba_logstdby_events);


7. 调整MAX_SGA - 防止Pageouts

SQL> select value bytes from v$logstdby_stats where name='bytes paged out';


注:如果以上查询结果在增长,则查到当前MAX_SGA的大小:

SQL> select value from v$logstdby_stats where name = 'maximum SGA for LCR cache';
VALUE
------------------------
30


增大MAX_SGA,备注:10gR2,MAX_SGA可以增大到4095 MB.

SQL> alter database stop logical standby apply;
Database altered.
SQL> execute dbms_logstdby.apply_set('MAX_SGA',1000);
PL/SQL procedure successfully completed.
SQL> alter database start logical standby apply immediate;
Database altered.


逻辑备库需要将redo记录解析成LCR,会在shared pool里分配一部分空间来作为LCR Cache,如果cache太小,就会像OS的虚拟内存管理一样,需要做page out,这会严重影响应用日志的性能。默认情况下,LCR CacheShared pool的四分之一,最少不少于30M,否则SQL Apply不能启动。如果机器的内存足够,建议将LCR Cache尽量设大一点,当然,同时share pool也要足够大。如果机器内存有限,那可以考虑将buffer cache减少一点来给LCR Cache腾出空间。

8.跳过特定表或SchemaDMLDDL事务

alter database stop logical standby apply;
execute dbms_logstdby.skip (stmt => 'DML', schema_name => 'EYGLE',
object_name => 'SALES', proc_name => null);
execute dbms_logstdby.skip (stmt => 'SCHEMA_DDL', schema_name => 'EYGLE',
object_name => 'SALES', proc_name => null);
execute dbms_logstdby.skip (stmt => 'DML',
schema_name => 'EYGLE', object_name => '%', proc_name => null);
execute dbms_logstdby.skip (stmt => 'SCHEMA_DDL',
schema_name => 'EYGLE', object_name => '%', proc_name => null);
alter database start logical standby apply;


9.如何重新初始化数据表
通过以下查询确认当前的skip规则:
select * from dba_logstdby_skip;

建议取消Skip之后,再重新初始化:

alter database stop logical standby apply;
execute dbms_logstdby.unskip('DML','EYGLE','SALES');
exec dbms_logstdby.instantiate_table('EYGLE','SALES','dblink_name');
alter database start logical standby apply;

Oracle文档参考:

INSTANTIATE_TABLE Procedure

This procedure creates and populates a table in the standby database from a corresponding table in the primary database. The table requires the name of the database link (dblink) as an input parameter.

Use the INSTANTIATE_TABLE procedure to:

  • Add a table to a standby database
  • Re-create a table in a standby database

Syntax

DBMS_LOGSTDBY.INSTANTIATE_TABLE (
     table_name          IN VARCHAR2,
     schema_name         IN VARCHAR2,
     dblink              IN VARCHAR2);

Parameters

Table 29-4 describes the parameters for the INSTANTIATE_TABLE procedure.

Table 29-4  DBMS_LOGSTDBY.INSTANTIATE_TABLE Procedure Parameters

Parameter

Description

table_name

Name of the table to be created or re-created in the standby database.

schema_name

Name of the schema.

dblink

Name of the database link account that has privileges to read and lock the table in the primary database.

Exceptions

None.

Usage Notes

·         Use this procedure to create and populate a table in a way that keeps the data on the standby database transactionally consistent with the primary database.

·         This procedure assumes that the metadata has been maintained correctly.

·         This table is not safe until the redo log that was current on the primary database at the time of execution is applied to the standby database.

Example

Enter this statement to create and populate a new table on the standby database.

SQL> EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE ('myschema', 'mytable', 'mydblink');

 

备用:)

 

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

下一篇: RAC service offline
请登录后发表评论 登录
全部评论

注册时间:2008-12-26

  • 博文量
    14
  • 访问量
    13491