[zt] Logical standby維護命令手冊

DataGuard Logical standby維護命令手冊

連結：

1.檢視logical standby上當前正在apply的redo 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 standby的apply程式數

ALTER DATABASE STOP LOGICAL STANDBY APPLY; --- Stop 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程式的個數，統計transactions的apply狀態，

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 real-time Apply

5.計算logical standby的apply速度

可以透過v$logstdby_stats檢視統計logical standby的apply速度，計算公式如下：
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

整理成1條sql語句計算出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 Cache為Shared pool的四分之一，最少不少於30M，否則SQL Apply不能啟動。如果機器的記憶體足夠，建議將LCR Cache儘量設大一點，當然，同時share pool也要足夠大。如果機器記憶體有限，那可以考慮將buffer cache減少一點來給LCR Cache騰出空間。

8.跳過特定表或Schema的DML或DDL事務

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;

文件參考：

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 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');