ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle LOGICAL standby 日志无法应用处理

oracle LOGICAL standby 日志无法应用处理

原创 Linux操作系统 作者:aaqwsh 时间:2011-06-04 10:39:03 0 删除 编辑

今天同事早上建了个逻辑备库,但是发现日志一直没有应用:

 

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

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

请登录后发表评论 登录
全部评论

注册时间:2010-11-24

  • 博文量
    132
  • 访问量
    265303