logical standby的管理

查看主库的归档情况：

SQL> select dest_id ,destination,status,target,schedule,process,mountid,error  from v$archive_dest order by dest_id;

备库：

查看数据库的隐含参数：

SQL> select name,SETTING,VALUE from dba_logstdby_parameters;

查看数据库的状态：

SQL> SELECT SESSION_ID, STATE FROM V$LOGSTDBY_STATE;

SESSION_ID STATE
---------- ----------------------------------------------------------------
         1 APPLYING

查看当前应用的SCN：

SQL> SELECT APPLIED_TIME, APPLIED_SCN, MINING_TIME, MINING_SCN FROM V$LOGSTDBY_PROGRESS;

APPLIED_TIME            APPLIED_SCN MINING_TIME             MINING_SCN
----------------------- ----------- ----------------------- ----------
21-JUL-2009 11:51:25     9170448648 21-JUL-2009 11:32:04    9169381652

select type,high_scn,status from v$logstdby;

查看最大sequence号：

select max(SEQUENCE#) from dba_logstdby_log;

统计transactions的apply状态：

SQL> select name,value from v$logstdby_stats where name like 'transactions%';

no rows selected

查看process状态：

SQL> select TYPE,STATUS FROM  V$LOGSTDBY_PROCESS;

TYPE                           STATUS
------------------------------ ------------------------------------------------------------
COORDINATOR           ORA-16112: log mining and apply stopping
APPLIER                        ORA-16117: processing
READER                        ORA-16127: stalled waiting for additional transactions to be
                                applied

BUILDER                        ORA-16127: stalled waiting for additional transactions to be
                                applied

PREPARER                       ORA-16127: stalled waiting for additional transactions to be
                                applied

PREPARER                       ORA-16127: stalled waiting for additional transactions to be
                                applied

PREPARER                       ORA-16127: stalled waiting for additional transactions to be
                                applied

PREPARER                       ORA-16127: stalled waiting for additional transactions to be
                                applied

PREPARER                       ORA-16127: stalled waiting for additional transactions to be
                                applied

PREPARER                       ORA-16127: stalled waiting for additional transactions to be
                                applied

PREPARER                       ORA-16127: stalled waiting for additional transactions to be
                                applied

PREPARER                       ORA-16127: stalled waiting for additional transactions to be
                                applied

12 rows selected.

在log mining and apply processing中，有以下几个进程：

During log mining:

READER：负责从归档日志或redo日志中读取redo records。

PREPARER：converts block changes contained in redo records into logical change records (LCRs)

BUILDER：将LCRs group成transactions及其他。

During apply processing:

ANALYZER：identifies不同的transaction之间的依赖关系。

COORDINATOR：分配transaction到不同的appliers，并且协调他们来确认他们之间的依赖关系被兑现。

APPLIER：应用transaction到logical standby.

查看page out：

SQL> select name,value from v$logstdby_stats where name like '%page%';

NAME
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
bytes paged out
9108912354

pageout time (seconds)
50744

当前正在apply的redo log：

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

当前的apply速度：

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

 

 

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

转载于:http://blog.itpub.net/10322123/viewspace-609834/