DataGuard Logical standby维护命令手册

« 使用RMAN恢复Oracle 10g RAC库至异机 | 文摘首页 | Logical STANDBY日志应用延迟案例一则 »

转载 by：eygle | 【恩墨学院 OCM培训传DBA成功之道】
链接：https://www.eygle.com/digest/2009/02/logical_standby_manual.html

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;

set linesize 120
col type for a10
col status for a40
col sid for 9999
col high_scn for 9999999999999
select * from v$logstdby_process where status_code<>16116;

查看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 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进程的个数，统计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;

10. Starting Real-time Apply

To start real-time apply on the logical standby database to immediately recover redo data from the standby redo log files on the logical standby database, include the IMMEDIATE keyword as shown in the following statement:

SQL>  ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

11.S topping Log Apply Services on a Logical Standby Database

To stop SQL Apply, issue the following statement on the logical standby database:

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;

When you issue this statement, SQL Apply waits until it has committed all complete transactions that were in the process of being applied. Thus, this command may not stop the SQL Apply processes immediately.

If you want to stop SQL Apply immediately, issue the following statement:

SQL> ALTER DATABASE ABORT 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');

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.

历史上的今天...

By eygle on 2009-02-07 16:56 | Comments (0) | Oracle摘 | 2177 |