sql应用架构
sql apply使用一批并行执行server，和后台进程来应用改变到备库。
在日志挖掘时：
reader进程在归档redo日志或是standby redo日志中读取日志记录
prepare进程转换redo记录中的改变到逻辑改变记录(lcrs)，多个prepare进程对一个给定的归档redo日志或standby redo日志。lcr被放在共享池中，是lcr cache中。
builder进程处理一组lcr到事务中，并做别的工作，像是lcr cache的内存管理，检查sql apply的重启及过滤出不感兴趣的改变。
在应用过程：
analyzer进程检查包含一组lcr的事务，可能会过滤掉不感兴趣的事务，并标明 不同事务的依赖。
coordinator进程
1分配事务 2监控事务的依赖和协调调度 3认证对逻辑 备库的提交改变
applier进程
1应用lcr到数据库2提交事务3找cordinator处理没有解决依赖的事务
查看v$logstdby_process视图来检查sql apply的活动，另外的一个视图v$ogstdby_stats视图提供当前活动信息。
sql apply的不同考虑
事务大小的考虑
sql apply把事务分成2类：小和大
小事务----sql apply应用小事务的lcr一旦他遇到redo log中的事务提交记录。
大事务---sql apply讲大事务分解成小片，称为事务 chunks，在提交记录前应用小事务。这么做减少了lcr chace的内存压力，也减少了故障处理的时间。
所有的事务开始被认为是小事务,依赖于lcr cache的可用内存数量和lcr中事务消耗的内存量。sql apply决定什么时候标记事务为大事务。
pageout的考虑
pageout发生在当lcr cache的内存耗尽，需要空闲出空间的时候。例如lcr cache的内存空间是100MB,sql apply遇到一个插入事务，表中有个long字段大小是300MB,这种情况下，log-mining部分会将long数据交换出去读取后面部分修改的列。在好的逻辑备库中，pageout是偶然发生，不会影响整体性能的。
重启的考虑
逻辑备库的修改知道被挖掘的事务记录在提交后才变的持久,每次sql apply停止，sql apply都要回去挖掘最早的未提交事务。有中情况,一个事务做了很少的工作,但是需呀打开很长的时间,重启sql apply是消耗很大的，因为sql apply也许要重新挖掘大量的归档日志，只是为了少数未提交事务读取redo数据，为了解决这个问题，sql apply定期的检查最老的未提交的数据,检查点的scn在v$logstdby_progress视图的restart_snc这列体现。一旦重启，sql apply开始挖掘比restart_scn大的redo记录，不被需要的归档日志就被sql apply自动删掉了。
dml应用的考虑
sql apply在应用对影响吞吐量和延时的dml事务的时候有下面的特征
1在主库上的批量更新或删除做完后，在备库上被当做单行修改来应用因此对于每个被维护的表上有个主键或唯一键是很重要的。
2在主库上的直接路径插入在备库上使用常规插入被应用
3并行dml事务在逻辑备库上不并行
ddl应用的考虑
在应用ddl的时候有下面的特征
1并行的ddl在逻辑备库上不并行
2ddl事务在逻辑备库上串行执行。因此主库上并行的ddl事务，在逻辑备库上被应用是串行执行的。
3CREATE TABLE AS SELECT (CTAS) statements are executed such that the DML activities (that are part of the CTAS statement) are suppressed on the logical standby database. The rows inserted in the newly created table as part of the CTAS statement are mined from the redo log files and applied to the logical standby database using INSERT statements.
管理和监控逻辑备库的相关视图
dba_logstandby_events视图记录了在sql apply的时候的事件。该视图记录最近的100个事件，可以通过dbms_logstdby.apply_set()存储过程来改变这个值。如果sql apply被异常的停止了，那么原因也会记录在这个视图中。也会记录被应用的ddl事务及被跳跃的事务。例如
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI:SS';
Session altered.
SQL> COLUMN STATUS FORMAT A60
SQL> SELECT EVENT_TIME, STATUS, EVENT FROM DBA_LOGSTDBY_EVENTS
2 ORDER BY EVENT_TIMESTAMP, COMMIT_SCN;
EVENT_TIME STATUS EVENT------------------------------------------------------------------------------
23-JUL-02 18:20:12 ORA-16111: log mining and apply setting up
23-JUL-02 18:25:12 ORA-16128: User initiated shut down successfully completed
23-JUL-02 18:27:12 ORA-16112: log mining and apply stopping
23-JUL-02 18:55:12 ORA-16128: User initiated shut down successfully completed
23-JUL-02 18:57:09 ORA-16111: log mining and apply setting up
23-JUL-02 20:21:47 ORA-16204: DDL successfully applied
create table hr.test_emp (empno number, ename varchar2(64))
23-JUL-02 20:22:55 ORA-16205: DDL skipped due to skip setting
create database link link_to_boston connect to system identified by change_on_inst
7 rows selected.
dba_logstdby_log视图
该视图提供了正杂被sql apply处理的归档日志的信息
SQL> COLUMN DICT_BEGIN FORMAT A10;SQL> SET NUMF 9999999SQL> 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# FROM DBA_LOGSTDBY_LOG - ORDER BY SEQUENCE#;FILE_NAME SEQ# F_SCN N_SCN TIMESTAM BEG END THR# APPLIED------------------------- ---- ------- ------- -------- --- --- --- ---------/oracle/dbs/hq_nyc_2.log 2 101579 101588 11:02:58 NO NO 1 YES/oracle/dbs/hq_nyc_3.log 3 101588 142065 11:02:02 NO NO 1 YES/oracle/dbs/hq_nyc_4.log 4 142065 142307 11:02:10 NO NO 1 YES/oracle/dbs/hq_nyc_5.log 5 142307 142739 11:02:48 YES YES 1 YES/oracle/dbs/hq_nyc_6.log 6 142739 143973 12:02:10 NO NO 1 YES/oracle/dbs/hq_nyc_7.log 7 143973 144042 01:02:11 NO NO 1 YES/oracle/dbs/hq_nyc_8.log 8 144042 144051 01:02:01 NO NO 1 YES/oracle/dbs/hq_nyc_9.log 9 144051 144054 01:02:16 NO NO 1 YES/oracle/dbs/hq_nyc_10.log 10 144054 144057 01:02:21 NO NO 1 YES/oracle/dbs/hq_nyc_11.log 11 144057 144060 01:02:26 NO NO 1 CURRENT/oracle/dbs/hq_nyc_12.log 12 144060 144089 01:02:30 NO NO 1 CURRENT/oracle/dbs/hq_nyc_13.log 13 144089 144147 01:02:41 NO NO 1 NO
v$logstdby_stat视图
这个视图提供了故障转移的相关信息
1故障转移的时间2灾难时可能会丢失的数据3lag time
SQL> SELECT NAME, VALUE, TIME_COMPUTED FROM V$LOGSTDBY_STATS;
NAME VALUE TIME_COMPUTED
------------------ -------------- ---------------------a
pply finish time +00 00:00:00.1 07-APR-2005 08:29:23
lag time +00 00:00:00.1 07-APR-2005 08:29:23
potential data loss +00 00:00:00 07-APR-2005 08:29:23
v$logstdby_process视图
这个视图提供了sql apply的不同进程的当前状态
SQL> COLUMN LID FORMAT 9999
SQL> COLUMN SERIAL# FORMAT 9999
SQL> COLUMN SID FORMAT 9999
SQL> SELECT SID, SERIAL#, LOGSTDBY_ID AS LID, SPID, TYPE, HIGH_SCN FROM V$LOGSTDBY_PROCESS;
SID SERIAL# LID SPID TYPE HIGH_SCN
----- ------- ----- ------------ ---------------- ----------
48 6 -1 11074 COORDINATOR 7178242899
56 56 0 10858 READER 7178243497
46 1 1 10860 BUILDER 7178242901
45 1 2 10862 PREPARER 7178243295
37 1 3 10864 ANALYZER 7178241034
36 1 4 10866 APPLIER 7178239467
35 3 5 10868 APPLIER 7178239463
34 7 6 10870 APPLIER 7178239461
33 1 7 10872 APPLIER 7178239472
9 rows selected.
The HIGH_SCN column shows that the reader process is ahead of all other processes, and the PREPARER and BUILDER process ahead of the rest.
SQL> COLUMN STATUS FORMAT A40
SQL> SELECT TYPE, STATUS_CODE, STATUS FROM V$LOGSTDBY_PROCESS;
TYPE STATUS_CODE STATUS
---------------- ----------- -----------------------------------------
COORDINATOR 16117 ORA-16117: processing
READER 16127 ORA-16127: stalled waiting for additional
transactions to be applied
BUILDER 16116 ORA-16116: no work available
PREPARER 16116 ORA-16117: processing
ANALYZER 16120 ORA-16120: dependencies being computed for
transaction at SCN 0x0001.abdb440a
APPLIER 16124 ORA-16124: transaction 1 13 1427 is waiting
on another transaction
APPLIER 16121 ORA-16121: applying transaction with commit
SCN 0x0001.abdb4390
APPLIER 16123 ORA-16123: transaction 1 23 1231 is waiting
for commit approval
APPLIER 16116 ORA-16116: no work available
The output shows a snapshot of SQL Apply running. On the mining side, the READER process is waiting for additional memory to become available before it can read more, the PREPARER process is processing redo records, and the BUILDER process has no work available. On the apply side, the COORDINATOR is assigning more transactions to APPLIER processes, the ANALYZER is computing dependencies at SCN 7178241034, one APPLIER has no work available, while two have outstanding dependencies that are not yet satisfied.
9.2.5 V$LOGSTDBY_PROGRESS View
This view provides detailed information regarding progress made by SQL Apply, including:
•SCN or time at which all transactions that have been committed on the primary database have been applied to the logical standby database (applied_scn | applied_time)
•SCN or time at which SQL Apply would begin reading redo records (restart_scn | restart_time) on restart
•SCN or time of the latest redo record received on the logical standby database (latest_scn | latest_time)
•SCN or time of the latest record processed by the BUILDER process (mining_scn | mining_time)
For example:
SQL> SELECT APPLIED_SCN, LATEST_SCN, MINING_SCN, RESTART_SCN FROM V$LOGSTDBY_PROGRESS;
APPLIED_SCN LATEST_SCN MINING_SCN RESTART_SCN
----------- ----------- ---------- -----------
7178240496 7178240507 7178240507 7178219805
According to the output:
•SQL Apply has applied all transactions committed on or before SCN of 7178240496
•The latest redo record received at the logical standby database was generated at SCN 7178240507
•The mining component has processed all redo records generate on or before SCN 7178240507
•If SQL Apply stops and restarts for any reason, it will start mining redo records generated on or after SCN 7178219805
SQL> ALTER SESSION SET NLS_DATE_FORMAT='yy-mm-dd hh24:mi:ss';
Session altered
SQL> SELECT APPLIED_TIME, LATEST_TIME, MINING_TIME, RESTART_TIME FROM V$LOGSTDBY_PROGRESS;
APPLIED_TIME LATEST_TIME MINING_TIME RESTART_TIME
----------------- ----------------- ----------------- -----------------
05-05-12 10:38:21 05-05-12 10:41:21 05-05-12 10:41:53 05-05-12 10:09:30
According to the output:
•SQL Apply has applied all transactions committed on or before the time 05-05-12 10:38:21 (APPLIED_TIME)
•The last redo was generated at time 05-05-12 10:41:53 at the primary database (LATEST_TIME)
•The mining engine has processed all redo records generated on or before 05-05-12 10:41:21 (MINING_TIME)
•In the event of a restart, SQL Apply will start mining redo records generated after the time 05-05-12 10:09:30
See Also:
V$DATAGUARD_PROGRESS view in Oracle Database Reference for reference information and Section 9.3.1, "Monitoring SQL Apply Progress" for example output
9.2.6 V$LOGSTDBY_STATE View
This view provides a synopsis of the current state of SQL Apply, including:
•The DBID of the primary database (primary_dbid).
•The LogMiner session ID allocated to SQL Apply (session_id).
•Whether or not SQL Apply is applying in real time (realtime_apply).
•Where SQL Apply is currently with regard to loading the LogMiner Multiversioned Data Dictionary (described in Section 4.2.3.2, "Build a Dictionary in the Redo Data"), receiving redo from the primary database, and applying redo data (STATE)
For example:
SQL> COLUMN REALTIME_APPLY FORMAT a15
SQL> COLUMN STATE FORMAT a16
SQL> SELECT * FROM V$LOGSTDBY_STATE;
PRIMARY_DBID SESSION_ID REALTIME_APPLY STATE
------------ ---------- --------------- ----------------
1562626987 1 Y APPLYING
The output shows that SQL Apply is running in the real-time apply mode and is currently applying redo data received from the primary database, the primary database's DBID is 1562626987 and the LogMiner session identifier associated the SQL Apply session is 1.
See Also:
V$LOGSTDBY_STATE view in Oracle Database Reference for reference information and Section 9.3.1, "Monitoring SQL Apply Progress" for example output
9.2.7 V$LOGSTDBY_STATS View
This view provides SQL Apply statistics.
For example:
SQL> COLUMN NAME FORMAT a32
SQL> COLUMN VALUE FORMAT a32
SQL> SELECT * FROM V$LOGSTDBY_STATS;
NAME VALUE
-------------------------------- --------------------------------
number of preparers 1
number of appliers 4
maximum SGA for LCR cache 30
parallel servers in use 8
maximum events recorded 1000
preserve commit order TRUE
record skip errors Y
record skip DDL Y
record applied DDL N
record unsupported operations N
coordinator state APPLYING
transactions ready 132412
transactions applied 132118
coordinator uptime 132102
realtime logmining Y
apply delay 0
Log Miner session ID 1
bytes of redo processed 130142100140
txns delivered to client 131515
DML txns delivered 128
DDL txns delivered 23
CTAS txns delivered 0
Recursive txns delivered 874
Rolled back txns seen 40
LCRs delivered to client 2246414
bytes paged out 0
secs spent in pageout 0
bytes checkpointed 0
secs spent in checkpoint 0
bytes rolled back 0
secs spent in rollback 0
secs system is idle 2119
32 rows selected.
监控sql apply过程
sql apply可以是下面6个过程中的任意一个：1初始化化sql apply 2等待数据字典日志3加载logminer多版本数据字典4应用redo数据5等待gap被解决6空闲。
初始化状态
当使用语句alter database start logical standby apply；语句后，进入初始化状态,查看sql apply的当前状态
SQL> SELECT SESSION_ID, STATE FROM V$LOGSTDBY_STATE;
SESSION_ID STATE
---------- -------------
1 INITIALIZING
加载数据字典状态
只有在数据字典被完全加载后,coordinator进程和挖掘进程才有。为了查看加载的详细信息,查看v$logmnr_dictionary_load视图。数据字典加载有3个阶段
1.The relevant archived redo log files or standby redo logs files are mined to gather the redo changes relevant to load the LogMiner multiversioned data dictionary.
2.The changes are processed and loaded in staging tables inside the database.
3.The LogMiner multiversioned data dictionary tables are loaded by issuing a series of DDL statements.
例如
SQL> SELECT PERCENT_DONE, COMMAND
FROM V$LOGMNR_DICTIONARY_LOAD
WHERE SESSION_ID = (SELECT SESSION_ID FROM V$LOGSTDBY_STATE);
PERCENT_DONE COMMAND
------------- -------------------------------
40 alter table SYSTEM.LOGMNR_CCOL$ exchange partition
P101 with table SYS.LOGMNRLT_101_CCOL$ excluding
indexes without validation
如果command的列很长时间没有改变，那么查询v$session_ongops视图看下事务的过程。
应用状态
为了查看应用的过程,执行下面的语句
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SQL> SELECT APPLIED_TIME, APPLIED_SCN, MINING_TIME, MINING_SCN,
FROM V$LOGSTDBY_PROGRESS;
APPLIED_TIME APPLIED_SCN MINING_TIME MINING_SCN
-------------------- ----------- -------------------- -----------
10-JAN-2005 12:00:05 346791023 10-JAN-2005 12:10:05 3468810134
等待gap状态
这个状态是sql apply已经挖掘和应用了所有的可用redo记录，正在等待被rfs进程归档的新的日志文件
SQL> SELECT STATUS FROM V$LOGSTBDY_PROCESS WHERE TYPE = 'READER';
STATUS
-----------------------------------------------------------------------
-ORA:01291 Waiting for logfile
空闲状态,要是应用完了所有的主库产生的redo后，就处于该状态。
自动删除日志文件
sql apply会自动删除不在被需要的归档日志文件，这个行为可以使用下面的语句来被修改
SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('LOG_AUTO_DELETE', FALSE);
一些情况下,你可能需要手工啦删除不需要的归档日志,按照如下的步骤操作
1清除逻辑备库上不在被需要的会话的元数据
exec dbms_logstdby.purge_session;
2查看dba_ogmnr_purged_log视图来列出可以被移走的归档日志
select * from dba_logmnr_purged_log;
扫一扫
243
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
