standby维护命令手册

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#;

DataGuard Logical

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

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

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

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

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

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

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

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

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

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

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

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

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

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.

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;

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;

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;

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

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

----------------

     10gR2大大简化了logical standby的创建步骤，从physical到logical的转换步骤异常简单，不再对搭建过程过多描述。

     因为Logical standby是SQL语句的逻辑运用，而standby是block级别的recover，从故障角度上将，Logical standby比standby standby有更多的故障机会出现。
     常用的用于维护和排查logical standby的视图几乎都是已”dba_logstdby_*”和“v$logstdby_*”开头，常用的包以”dbms_logstdby.*”开头，具体含义根据字面意思也不难理解,不赘述(下文有部分提及)。当然，用于physical standby的部分视图，对排查逻辑备库也同样适用。
1. APPLY状态的检查和诊断
     Apply进程是逻辑SQL运用的核心。Apply进程遇到错误会自动终止，需要处理后手工重新启动。因此，监控apply进度和状态是监控逻辑备库同步与否的关键，如同物理DG监控日志同步。
(1) 监控备库上各个进程的状态
     可以查询v$logstdby_process视图，用于得到与SQL APPLY相关的各进程的状态情况.
     如果没有返回值，表示APPLY进程没有启动或者已停止。如果是后者，可以从alert.log文件或者dba_logstdby_events中得到更多的错误信息用于后续处理。
(2) Apply 进度
     监控Apply进度可以得到备库的恢复进展以及同步状态。关联查询v$logstdby_progress 和dba_logstdby_log，得出目前SQL APPLY的apply scn/time以及log sequence。如果apply进程正常而主备之间同步有很大差异，需要做进一步排查。
2. SQL Apply的异常事务处理
     SQL 　Apply的异常事务会记录到dba_logstdby_events视图中，对于能够进行手工fix的异常，可以处理后重新运行apply进程。对于某些无法处理或者修复后需要忽略的异常，通过dbms_logstdby.skip*一系列包做标记以便让apply进程跳过。比如，我们可以对特定的错误事务进行标记，通过dbms_logstdby.skip_transaction标记指定的事务而忽略该事务的运用。
     在10G中，oracle提供了带skip failed transaction子句的 SQL apply启动语句，可以方便的对当前记录的错误事务全部跳过。
SQL>alter database start logical standby apply skip failed transaction;
3.SKIP策略
Logical standby提供skip的策略，可根据实际情况跳过不想被apply的对象或者跳过特定对象上的所有错误事务。
dbms_logstdby.skip: 用于指定对象的DML DDL skip
dbms_logstdby.skip_error: 用于指定对象上的错误事务 skip
dbms_logstdby.skip_transaction: 用于指定事务 skip 
4. UNSKIP与初始化
     Unskip是与skip相反的操作，用于已skip操作的撤销。对于unskip的对象，需要进行手工构造或者初始化。
     Oracle提供dbms_logstdby.instantiate_table包来对指定的table初始化。我们也可以手工来构造一个当前apply scn时刻的闪回查询版本来初始化数据。
5. DDL handle
     Logical standby中提供DDL handle的功能，用于自定义的一些DDL语句处理，比如一些表对象的重命名。
     需要注意的是,在逻辑备库中，db_file_name_convert/ log_file_name_convert参数并不生效。对于路径不同的主备体系，添加数据文件和表空间会引起逻辑备库的apply错误,可以通过DDL handle可以实现文件路径的重命名。
     ORACLE联机文档上的一个例子：
CREATE OR REPLACE PROCEDURE SYS.HANDLE_TBS_DDL (
OLD_STMT IN VARCHAR2,
STMT_TYP IN VARCHAR2,
SCHEMA IN VARCHAR2,
NAME IN VARCHAR2,
XIDUSN IN NUMBER,
XIDSLT IN NUMBER,
XIDSQN IN NUMBER,
ACTION OUT NUMBER,
NEW_STMT OUT VARCHAR2
) AS
BEGIN
– All primary file specification that contains a directory
– /usr/orcl/primary/dbs
– should go to /usr/orcl/stdby directory specification
NEW_STMT := REPLACE(OLD_STMT,
‘/usr/orcl/primary/dbs’,
‘/usr/orcl/stdby’);
ACTION := DBMS_LOGSTDBY.SKIP_ACTION_REPLACE;
EXCEPTION
WHEN OTHERS THEN
ACTION := DBMS_LOGSTDBY.SKIP_ACTION_ERROR;
NEW_STMT := NULL;
END HANDLE_TBS_DDL; 
然后指向这个ddl handle
SQL>exec dbms_logstdby.skip(stmt => ‘TABLESPACE’, proc_name => ‘SYS.HANDLE_TBS_DDL’); 
6.参数设置
     通过查看v$logstdby_stats观察现有的一些参数设置和状态。设置/恢复参数使用dbms_logstdby.apply_set/dbms_logstdby. apply_unset包进行。
7. Role Transitions
     逻辑switchover的过程要比physical standby复杂一些，需要有一步prepare的过程，注意留意期间主备库角色的当值状态。
8.Flashback database
     如果开启了flashback database，对于一个做过failover的原主库，可以通过flashback database的功能将数据库闪回到某个scn上，然后转换成备库，从而无需重新重新搭建环境。
     (1). 在新主库上，执行如下查询获得这个转换时刻的SCN
SQL>SELECT VALUE FROM DBA_LOGSTDBY_PARAMETERS WHERE NAME = ‘STANDBY_BECAME_PRIMARY_SCN’;
     (2). 旧库上，执行flashback，并resetlogs
SQL>FLASHBACK DATABASE TO SCN became_primary_scn;
     (3).角色切换
SQL>ALTER DATABASE START LOGICAL STANDBY APPLY NEW PRIMARY mylink;
9.Guard 模式
    默认状态下的逻辑备库提供查询功能和sys用户下的DDL/DML操作,即standby模式.
    Oracle提供database级别的3种模式,即none/standby/all模式,通过查询v$database.guard_status列获得当前状态，可以通过以下SQL在备库上重新定义:
SQL>alter databsae guard none(standby/all); 
NONE模式:
逻辑备库可以以任意数据库用户进行任意数据库动作(查询/DML/DDL/DCL等).置于该模式下的逻辑备库,除了还需要运用SQL APPLY外,可读写,同一般的实例无区别.
STANDBY模式:
阻止除SYS用户外的所有用户对受SQL apply保护的所有对象的DDL和DDL操作.比如:通过SQL APPLY生成的表,普通用户不允许对其进行DML/DDL操作,但普通用户可以创建/操作主库上不存在的表(对象).
ALL模式:
阻止除SYS用户外的所有数据库用户进行任意更改操作,数据库只读.
    同时逻辑备库还支持session级别的guard保护开关,有时候需要进行维护操作,可以临时将guard关闭掉以支持该session下的DDL/DML操作.
SQL>alter session disable/enable guard; 
http://www.easyora.net/blog/logical_standby_daily_maintenance.html

转自：http://www.eygle.com/digest/2009/02/logical_standby_manual.html

参考网址：
http://www.wonderslife.net/数据库技术/oracle数据库及相关/201005179/oracle-logical-standby-日常管理常用的一些语句.html
http://hi.baidu.com/edeed/blog/item/3b465aafdc6d81f7fbed50d7.html
http://fangrn.javaeye.com/blog/539004
http://fangrn.javaeye.com/blog/53901
http://blog.tianya.cn/blogger/post_show.asp?BlogID=1995494&PostID=19162830&idWriter=0&Key=0
http://www.easyora.net/blog/logical_standby_performance.html