oracle LOGICAL standby 日誌無法應用處理

今天同事早上建了個邏輯備庫，但是發現日誌一直沒有應用：

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/，如需轉載，請註明出處，否則將追究法律責任。

oracle LOGICAL standby 日誌無法應用處理

相關文章