分类： Oracle
2013-07-27 23:42:54
SQL> col FILE_NAME format a60
SQL> select file_name,applied from DBA_LOGSTDBY_LOG ;
FILE_NAME APPLIED
------------------------------------------------------------ --------
/opt/odata/arch/1_116724_657561562.dbf NO
/opt/odata/arch/1_116725_657561562.dbf NO
/opt/odata/arch/1_116726_657561562.dbf NO
/opt/odata/arch/1_116727_657561562.dbf NO
/opt/odata/arch/2_3644_657561562.dbf NO
/opt/odata/arch/2_3646_657561562.dbf NO
/opt/odata/arch/2_3647_657561562.dbf NO
/opt/odata/arch/2_3648_657561562.dbf NO
/opt/odata/arch/3_502_657561562.dbf NO
/opt/odata/arch/3_503_657561562.dbf NO
/opt/odata/arch/4_111_657561562.dbf NO
FILE_NAME APPLIED
------------------------------------------------------------ --------
/opt/odata/arch/4_112_657561562.dbf NO
/opt/odata/arch/2_3649_657561562.dbf NO
/opt/odata/arch/2_3650_657561562.dbf NO
/opt/odata/arch/1_116728_657561562.dbf NO
/opt/odata/arch/4_113_657561562.dbf NO
/opt/odata/arch/3_504_657561562.dbf NO
1 检查process里有报错，说缺少日志
2 v$logstdby_state里显示在等待字典信息，说明这个库还不是真正的逻辑备库：
SQL> select session_id,state from v$logstdby_state;
SESSION_ID STATE
---------- ----------------------------------------------------------------
1 WAITING FOR DICTIONARY LOGS
3 alert log发现有如下错误，日志路径有问题（为什么会出现这么怪的路径，应该是配置错误，后来同事调整了）：
LOGSTDBY: Attempting to pre-register dictionary build logfiles
Fri Jun 03 06:35:03 CST 2011
LOGMINER: Error 308 encountered, failed to read logfile /opt/odata/arch3_495_657561562.dbf
Fri Jun 03 06:35:03 CST 2011
LOGMINER: Error 308 encountered, failed to read logfile /opt/odata/arch1_116714_657561562.dbf
Fri Jun 03 06:35:03 CST 2011
LOGMINER: Error 308 encountered, failed to read logfile /opt/odata/arch3_496_657561562.dbf
Fri Jun 03 06:35:03 CST 2011
LOGMINER: Error 308 encountered, failed to read logfile /opt/odata/arch4_103_657561562.dbf
Fri Jun 03 06:35:03 CST 2011
LOGMINER: Error 308 encountered, failed to read logfile /opt/odata/arch2_3636_657561562.dbf
Fri Jun 03 06:35:03 CST 2011
LOGMINER: Error 308 encountered, failed to read logfile /opt/odata/arch2_3635_657561562.dbf
Fri Jun 03 06:35:03 CST 2011
LOGMINER: Error 308 encountered, failed to read logfile /opt/odata/arch1_116715_657561562.dbf
4 手工注册日志
ALTER DATABASE REGISTER LOGICAL LOGFILE '/opt/odata/arch/3_495_657561562.dbf';
ALTER DATABASE REGISTER LOGICAL LOGFILE '/opt/odata/arch/1_116714_657561562.dbf';
ALTER DATABASE REGISTER LOGICAL LOGFILE '/opt/odata/arch/3_496_657561562.dbf';
ALTER DATABASE REGISTER LOGICAL LOGFILE '/opt/odata/arch/4_103_657561562.dbf';
ALTER DATABASE REGISTER LOGICAL LOGFILE '/opt/odata/arch/2_3636_657561562.dbf';
ALTER DATABASE REGISTER LOGICAL LOGFILE '/opt/odata/arch/2_3635_657561562.dbf';
ALTER DATABASE REGISTER LOGICAL LOGFILE '/opt/odata/arch/1_116715_657561562.dbf';
5 应用日志
开始应用字典表，应用日志
6 发现缺失日志，手工拷贝注册
ALTER DATABASE REGISTER LOGICAL LOGFILE '/opt/odata/arch/1_116716_657561562.dbf';
ALTER DATABASE REGISTER LOGICAL LOGFILE '/opt/odata/arch/2_3637_657561562.dbf';
ALTER DATABASE REGISTER LOGICAL LOGFILE '/opt/odata/arch/3_497_657561562.dbf';
ALTER DATABASE REGISTER LOGICAL LOGFILE '/opt/odata/arch/1_1149_116717.dbf';
ALTER DATABASE REGISTER LOGICAL LOGFILE '/opt/odata/arch/2_3638_116717.dbf';
ALTER DATABASE REGISTER LOGICAL LOGFILE '/opt/odata/arch/3_498_116717.dbf';
7在日志应用过程中出现paged out，应用缓慢，调整参数maximum SGA for LCR cache，其实还有很多参数可以调整：
SQL> set linesize 300
SQL> select * from v$logstdby_stats ;
NAME VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
number of preparers 1
number of appliers 5
maximum SGA for LCR cache 1000
parallel servers in use 9
maximum events recorded 100
preserve commit order TRUE
transaction consistency FULL
record skip errors Y
record skip DDL Y
record applied DDL N
record unsupported operations N
NAME VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
coordinator state APPLYING
transactions ready 1215093
transactions applied 1214689
coordinator uptime 7508
realtime logmining Y
apply delay 0
Log Miner session ID 1
txns delivered to client 1294223
DML txns delivered 1139887
DDL txns delivered 58
CTAS txns delivered 1
NAME VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Recursive txns delivered 154278
Rolled back txns seen 1802
LCRs delivered to client 15107849
bytes of redo processed 10692904088
bytes paged out 0
seconds spent in pageout 0
bytes checkpointed 3076592
seconds spent in checkpoint 0
bytes rolled back 0
seconds spent in rollback 0
seconds system is idle 0
33 rows selected.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
-bash-3.2$ progress
Session altered.
APPLIED_SCN APPLIED_TIME RESTART_SCN RESTART_TIME LATEST_SCN LATEST_TIME MINING_SCN MINING_TIME
----------- ------------------- ----------- ------------------- ---------- ------------------- ---------- -------------------
1.4271E+10 2011-06-03 15:00:13 1.4271E+10 2011-06-03 14:47:03 1.4272E+10 2011-06-03 15:24:59 1.4272E+10 2011-06-03 15:24:37
