Oracle Study之--Logical Standby日志应用错误案例

hteesa

系统环境：
操作系统：RedHat EL54
数据库：  Oracle 11gR2


一、错误现象：（在逻辑备库应用日志时）

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

LOGMINER: session#=1 (Logical_Standby$), preparer MS02 pid=34 OS id=4241 sid=37 started LOGSTDBY status: ORA-00604: error occurred at recursive SQL level 1 ORA-01658: unable to create INITIAL extent for segment in tablespace LOGMNRTBS ORA-06512: at "SYS.LOGMNR_KRVRDLUID3", line 1300 ORA-06512: at line 1 Errors in file /u01/app/oracle/diag/rdbms/bjdb/TestDB12/trace/TestDB12_lsp0_4228.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-01658: unable to create INITIAL extent for segment in tablespace LOGMNRTBS ORA-06512: at "SYS.LOGMNR_KRVRDLUID3", line 1300 ORA-06512: at line 1 LOGMINER: session#=1 (Logical_Standby$), reader MS00 pid=32 OS id=4237 sid=43 stopped LOGMINER: session#=1 (Logical_Standby$), builder MS01 pid=33 OS id=4239 sid=41 stopped LOGMINER: session#=1 (Logical_Standby$), preparer MS02 pid=34 OS id=4241 sid=37 stopped ^[[AThu Mar 12 05:01:49 2015 Starting background process SMCO Thu Mar 12 05:01:49 2015 SMCO started with pid=31, OS id=4260 查看logmnr应用： SYS@ TestDB12>select * from v$logstdby_stats; no rows selected logmnr没有任何动作。

二、分析问题原因
1、查看trace文件

oracle@bjsrv:/home/oracle> more /u01/app/oracle/diag/rdbms/bjdb/TestDB12/trace/TestDB12_lsp0_4228.trc

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201

Trace file /u01/app/oracle/diag/rdbms/bjdb/TestDB12/trace/TestDB12_lsp0_4228.trc Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1 System name:    Linux Node name:      bjsrv Release:        2.6.18-194.el5 Version:        #1 SMP Tue Mar 16 21:52:39 EDT 2010 Machine:        x86_64 Instance name: TestDB12 Redo thread mounted by this instance: 1 Oracle process number: 31 Unix process pid: 4228, image: oracle@bjsrv (LSP0) *** 2015-03-12 04:58:11.426 *** SESSION ID:(38.1) 2015-03-12 04:58:11.426 *** CLIENT ID:() 2015-03-12 04:58:11.426 *** SERVICE NAME:(SYS$BACKGROUND) 2015-03-12 04:58:11.426 *** MODULE NAME:() 2015-03-12 04:58:11.426 *** ACTION NAME:() 2015-03-12 04:58:11.426    knahcapplymain: encountered error=604 *** 2015-03-12 04:58:11.426 dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=0, mask=0x0) ----- Error Stack Dump ----- ORA-00604: error occurred at recursive SQL level 1 ORA-01658: unable to create INITIAL extent for segment in tablespace LOGMNRTBS ORA-06512: at "SYS.LOGMNR_KRVRDLUID3", line 1300 ORA-06512: at line 1 KNACDMP: ******************************************************* KNACDMP: Dumping apply coordinator's context at 830931c0 KNACDMP: Apply Engine # 0 KNACDMP: Apply Engine name KNACDMP: Coordinator's Watermarks ------------------------------ KNACDMP: Apply High Watermark            = 00000014572d (SCN=0x0000.0014572d) KNACDMP: Apply Low Watermark             = 00000014572d (SCN=0x0000.0014572d) KNACDMP: Recovery Low Watermark          =  (SCN=0x0000.00000000) KNACDMP: Fetch Low Watermark             =  (SCN=0x0000.00000000) KNACDMP: Fetch Low Watermark Time        = 0 KNACDMP: Oldest SCN                      =  (SCN=0x0000.00000000) KNACDMP: Oldest XID                      = KNACDMP: Oldest Create Time              = 0 KNACDMP: Last replicant syncpoint SCN    = 0x0000.00000000 KNACDMP: Last syncpoint at primary  SCN  = 0x0000.00000000 KNACDMP: First partition max pos         =  (SCN=0x0000.00000000) KNACDMP: Last  partition max pos         =  (SCN=0x0000.00000000) KNACDMP: Last  processed                 = 00000014572e (SCN=0x0000.0014572e) KNACDMP: Conservative pos                =  (SCN=0x0000.00000000) KNACDMP: Recovery start pos              =  (SCN=0x0000.00000000) KNACDMP: Recovery high watermark         =  (SCN=0x0000.00000000) KNACDMP: Spill LWM                       =  (SCN=0x0000.00000000) KNACDMP: Spill LWM Create Time           = 0 KNACDMP: Coordinator's constants ------------------------------- KNACDMP: number of apply slaves          = 5 KNACDMP: min number of apply slaves          = 5 KNACDMP: max number of apply slaves          = 5 KNACDMP: safety level (K)                = 1 KNACDMP: max txns in memory              = 400 KNACDMP: max constraints per table       = 620 KNACDMP: hash table size (in entries)    = 10000000 KNACDMP: Coordinator's intervals ------------------------------- KNACDMP: syncpoint interval (ms)         = 0 KNACDMP: write low watermark interval(ms)= 1 KNACDMP: Coordinator's timers/counters ------------------------- KNACDMP: current time                    = 1426150690 KNACDMP: low watermark timer             = 0 KNACDMP: syncpoint timer                 = 0 KNACDMP: txnbufsize timer                = 0 KNACDMP: Coordinator's txn counts ------------------------- KNACDMP: total txns applied              = 0 KNACDMP: number of unassigned comp txns  = 0 KNACDMP: number of unassigned incomp txns= 0 KNACDMP: avg number of unassigned txns   = 0.00 KNACDMP: total applied at last plwm write= 0 KNACDMP: apply prog. entries below plwm  = 0 KNACDMP: total unassigned lcrs           = 0 KNACDMP: Coordinator's State/Flags ----------------------------- KNACDMP: Coordinator's State             = KNACST_APPLY_UNTIL_END KNACDMP: Coordinator's Flags             = 0x8000 KNACDMP: Slave counts ------------------------------------------ KNACDMP: number of reserved slaves       = 1 KNACDMP: number of admin slaves          = 0 KNACDMP: number of slaves in wait cmt    = 0 KNACDMP: number of slaves suspended      = 0 KNACDMP: number of safe slaves           = 0 KNACDMP: avg number of idle slaves       = 0.00 KNACDMP: number of slaves initializing   = 0 KNACDMP: number of slaves terminating    = 0 KNACDMP: Slave Lists ------------------------------------------- KNACDMP: Dumping All Slaves :- Slave id = 0, State = 8, Flags = 0, Not Assigned 0 txns  0 lcrs Slave id = 1, State = 0, Flags = 0, Not Assigned 0 txns  0 lcrs Slave id = 2, State = 0, Flags = 0, Not Assigned 0 txns  0 lcrs Slave id = 3, State = 0, Flags = 0, Not Assigned 0 txns  0 lcrs Slave id = 4, State = 0, Flags = 0, Not Assigned 0 txns  0 lcrs Slave id = 5, State = 0, Flags = 0, Not Assigned 0 txns  0 lcrs KNACDMP: End dumping all slaves KNACDMP: syncdep slaves = { } KNACDMP: cont chunk slaves = { } KNACDMP: cont slaves = { } KNACDMP: exec txn slaves = { } KNACDMP: Active slaves (0) = { } KNACDMP: Idle slaves (5) = { 1 2 3 4 5 } KNACDMP: Txn Lists --------------------------------------------- KNACDMP: Dumping all txns :- KNACDMP: End dumping all txns. KNACDMP: Complete txns = { ** NO UNASS ** } KNACDMP: Unassigned txns = { } KNACDMP: ******************************************************* Warning:  Apply error received:  ORA-26714: User Error  encountered during apply process.  Clearing. *** 2015-03-12 04:58:11.994 knahcapplymain: encountered error=604 *** 2015-03-12 04:58:11.994 dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=0, mask=0x0) ----- Error Stack Dump ----- ORA-00604: error occurred at recursive SQL level 1 ORA-01658: unable to create INITIAL extent for segment in tablespace LOGMNRTBS ORA-06512: at "SYS.LOGMNR_KRVRDLUID3", line 1300 ORA-06512: at line 1 KNACDMP: ******************************************************* KNACDMP: Dumping apply coordinator's context at 830931c0 KNACDMP: Apply Engine # 0 KNACDMP: Apply Engine name KNACDMP: Coordinator's Watermarks ------------------------------ KNACDMP: Apply High Watermark            = 00000014572d (SCN=0x0000.0014572d) KNACDMP: Apply Low Watermark             = 00000014572d (SCN=0x0000.0014572d) KNACDMP: Recovery Low Watermark          =  (SCN=0x0000.00000000) KNACDMP: Fetch Low Watermark             =  (SCN=0x0000.00000000) KNACDMP: Fetch Low Watermark Time        = 0 KNACDMP: Oldest SCN                      =  (SCN=0x0000.00000000) KNACDMP: Oldest XID                      = KNACDMP: Oldest Create Time              = 0 KNACDMP: Last replicant syncpoint SCN    = 0x0000.00000000 KNACDMP: Last syncpoint at primary  SCN  = 0x0000.00000000 KNACDMP: First partition max pos         =  (SCN=0x0000.00000000) KNACDMP: Last  partition max pos         =  (SCN=0x0000.00000000) KNACDMP: Last  processed                 = 00000014572e (SCN=0x0000.0014572e) KNACDMP: Conservative pos                =  (SCN=0x0000.00000000) KNACDMP: Recovery start pos              =  (SCN=0x0000.00000000) KNACDMP: Recovery high watermark         =  (SCN=0x0000.00000000) KNACDMP: Spill LWM                       =  (SCN=0x0000.00000000) KNACDMP: Spill LWM Create Time           = 0 KNACDMP: Coordinator's constants ------------------------------- KNACDMP: number of apply slaves          = 5 KNACDMP: min number of apply slaves          = 5 KNACDMP: max number of apply slaves          = 5 KNACDMP: safety level (K)                = 1 KNACDMP: max txns in memory              = 400 KNACDMP: max constraints per table       = 620 KNACDMP: hash table size (in entries)    = 10000000 KNACDMP: Coordinator's intervals ------------------------------- KNACDMP: syncpoint interval (ms)         = 0 KNACDMP: write low watermark interval(ms)= 1 KNACDMP: Coordinator's timers/counters ------------------------- KNACDMP: current time                    = 1426150691 KNACDMP: low watermark timer             = 0 KNACDMP: syncpoint timer                 = 0 KNACDMP: txnbufsize timer                = 0 KNACDMP: Coordinator's txn counts ------------------------- KNACDMP: total txns applied              = 0 KNACDMP: number of unassigned comp txns  = 0 KNACDMP: number of unassigned incomp txns= 0 KNACDMP: avg number of unassigned txns   = 0.00 KNACDMP: total applied at last plwm write= 0 KNACDMP: apply prog. entries below plwm  = 0 KNACDMP: total unassigned lcrs           = 0 KNACDMP: Coordinator's State/Flags ----------------------------- KNACDMP: Coordinator's State             = KNACST_APPLY_UNTIL_END KNACDMP: Coordinator's Flags             = 0x8200 KNACDMP: Slave counts ------------------------------------------ KNACDMP: number of reserved slaves       = 1 KNACDMP: number of admin slaves          = 0 KNACDMP: number of slaves in wait cmt    = 0 KNACDMP: number of slaves suspended      = 0 KNACDMP: number of safe slaves           = 0 KNACDMP: avg number of idle slaves       = 0.00 KNACDMP: number of slaves initializing   = 0 KNACDMP: number of slaves terminating    = 0 KNACDMP: Slave Lists ------------------------------------------- KNACDMP: Dumping All Slaves :- Slave id = 0, State = 8, Flags = 0, Not Assigned 0 txns  0 lcrs Slave id = 1, State = 0, Flags = 0, Not Assigned 0 txns  0 lcrs Slave id = 2, State = 0, Flags = 0, Not Assigned 0 txns  0 lcrs Slave id = 3, State = 0, Flags = 0, Not Assigned 0 txns  0 lcrs Slave id = 4, State = 0, Flags = 0, Not Assigned 0 txns  0 lcrs Slave id = 5, State = 0, Flags = 0, Not Assigned 0 txns  0 lcrs KNACDMP: End dumping all slaves KNACDMP: syncdep slaves = { } KNACDMP: cont chunk slaves = { } KNACDMP: cont slaves = { } KNACDMP: exec txn slaves = { } KNACDMP: Active slaves (0) = { } KNACDMP: Idle slaves (5) = { 1 2 3 4 5 } KNACDMP: Txn Lists --------------------------------------------- KNACDMP: Dumping all txns :- KNACDMP: End dumping all txns. KNACDMP: Complete txns = { ** NO UNASS ** } KNACDMP: Unassigned txns = { } KNACDMP: ******************************************************* Warning:  Apply error received:  ORA-26714: User Error  encountered during apply process.  Clearing. oracle@bjsrv:/home/oracle>

2、查看tablespace信息

1 2 3 4 5 6 7 8 9 10

SYS@ TestDB12>select file_id,file_name,tablespace_name,bytes/1024/1024 from dba_data_files;    FILE_ID FILE_NAME                                          TABLESPACE_NAME                BYTES/1024/1024 ---------- -------------------------------------------------- ------------------------------ ---------------          4 /u01/app/oracle/oradata/TestDB12/users01.dbf       USERS                                        5          3 /u01/app/oracle/oradata/TestDB12/undotbs01.dbf     UNDOTBS1                                   115          2 /u01/app/oracle/oradata/TestDB12/sysaux01.dbf      SYSAUX                                     570          1 /u01/app/oracle/oradata/TestDB12/system01.dbf      SYSTEM                                     730          5 /u01/app/oracle/oradata/TestDB12/example01.dbf     EXAMPLE                                345.625          6 /u01/app/oracle/oradata/TestDB12/logmnrtbs01.dbf   LOGMNRTBS                                  100 6 rows selected.

  --作为logminer的应用表空间，“LOGMNRTBS"初始只分配了100M的size，估计在做sql apply时，空间不足，而不能分配extent。

三、解决方法
1、扩展“LOGMNRTBS" size

1 2 3 4 5 6 7 8 9 10 11 12 13

SYS@ TestDB12>alter database datafile 6 resize 500m; Database altered. SYS@ TestDB12>select file_id,file_name,tablespace_name,bytes/1024/1024 from dba_data_files;    FILE_ID FILE_NAME                                          TABLESPACE_NAME                BYTES/1024/1024 ---------- -------------------------------------------------- ------------------------------ ---------------          4 /u01/app/oracle/oradata/TestDB12/users01.dbf       USERS                                        5          3 /u01/app/oracle/oradata/TestDB12/undotbs01.dbf     UNDOTBS1                                   115          2 /u01/app/oracle/oradata/TestDB12/sysaux01.dbf      SYSAUX                                     570          1 /u01/app/oracle/oradata/TestDB12/system01.dbf      SYSTEM                                     730          5 /u01/app/oracle/oradata/TestDB12/example01.dbf     EXAMPLE                                345.625          6 /u01/app/oracle/oradata/TestDB12/logmnrtbs01.dbf   LOGMNRTBS                                  500 6 rows selected.

四、在备库上做SQL APPLY
SYS@ TestDB12>alter database start logical standby apply immediate;
Database altered.
1、查看logmnr视图：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47

SYS@ TestDB12>select *from v$logstdby_stats; NAME                                                             VALUE ---------------------------------------------------------------- ---------------------------------------------------------------- logminer session id                                              1 number of preparers                                              1 number of appliers                                               5 server processes in use                                          9 maximum SGA for LCR cache (MB)                                   30 maximum events recorded                                          10000 preserve commit order                                            TRUE transaction consistency                                          FULL record skipped errors                                            Y record skipped DDLs                                              Y record applied DDLs                                              N record unsupported operations                                    N realtime apply                                                   Y apply delay (minutes)                                            0 peak apply rate (bytes/sec)                                      15148888 coordinator state                                                IDLE coordinator startup time                                         12-MAR-15 coordinator uptime (seconds)                                     96 txns received from logminer                                      1523 txns assigned to apply                                           1523 txns applied                                                     1523 txns discarded during restart                                    0 large txns waiting to be assigned                                0 session restart SCN                                              1332344 rolled back txns mined                                           501 DDL txns mined                                                   11 CTAS txns mined                                                  0 bytes of redo mined                                              72757172 bytes paged out                                                  0 pageout time (seconds)                                           0 bytes checkpointed                                               133310 checkpoint time (seconds)                                        0 system idle time (seconds)                                       0 standby redo logs mined                                          0 archived logs mined                                              15 gap fetched logs mined                                           7 standby redo log reuse detected                                  0 logfile open failures                                            0 current logfile wait (seconds)                                   0 total logfile wait (seconds)                                     0 thread enable mined                                              0 thread disable mined                                             0 distinct txns in queue                                           0 43 rows selected.

查看告警日志：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169

RFS LogMiner: Registered logfile [/dsk4/arch_bj/arch_1_65_873495347.log] to LogMiner session id [1] Thu Mar 12 05:05:38 2015 RFS[2]: Selected log 6 for thread 1 sequence 66 dbid -1483137996 branch 873495347 Thu Mar 12 05:06:52 2015 alter database start logical standby apply immediate ALTER DATABASE START LOGICAL STANDBY APPLY (TestDB12) with optional part IMMEDIATE Attempt to start background Logical Standby process Thu Mar 12 05:06:52 2015 LSP0 started with pid=33, OS id=4277 Completed: alter database start logical standby apply immediate LOGMINER: Parameters summary for session# = 1 LOGMINER: Number of processes = 3, Transaction Chunk Size = 201 LOGMINER: Memory Size = 30M, Checkpoint interval = 150M LOGMINER: SpillScn 0, ResetLogScn 1234253 LOGMINER: summary for session# = 1 LOGMINER: StartScn: 1332344 (0x0000.00145478) LOGMINER: EndScn: 0 (0x0000.00000000) LOGMINER: HighConsumedScn: 1333037 (0x0000.0014572d) LOGMINER: session_flag: 0x1 LOGMINER: Read buffers: 16 Thu Mar 12 05:06:52 2015 LOGMINER: session#=1 (Logical_Standby$), reader MS00 pid=34 OS id=4279 sid=40 started Thu Mar 12 05:06:52 2015 LOGMINER: session#=1 (Logical_Standby$), builder MS01 pid=35 OS id=4281 sid=44 started Thu Mar 12 05:06:53 2015 LOGMINER: session#=1 (Logical_Standby$), preparer MS02 pid=36 OS id=4283 sid=46 started LOGMINER: Turning ON Log Auto Delete LOGMINER: Begin mining logfile during dictionary load for session 1 thread 1 sequence 51, /dsk4/arch_bj/arch_1_51_873495347.log Thu Mar 12 05:07:02 2015 Thread 1 advanced to log sequence 7 (LGWR switch)   Current log# 1 seq# 7 mem# 0: /dsk2/oradata/bjdb/redo01b.log   Current log# 1 seq# 7 mem# 1: /dsk1/oradata/bjdb/redo01a.log Thu Mar 12 05:07:04 2015 Time drift detected. Please check VKTM trace file for more details. Thu Mar 12 05:07:05 2015 Archived Log entry 6 added for thread 1 sequence 6 ID 0x38edae0f dest 1: Thread 1 advanced to log sequence 8 (LGWR switch)   Current log# 2 seq# 8 mem# 0: /dsk2/oradata/bjdb/redo02b.log   Current log# 2 seq# 8 mem# 1: /dsk1/oradata/bjdb/redo02a.log Thu Mar 12 05:07:14 2015 Archived Log entry 7 added for thread 1 sequence 7 ID 0x38edae0f dest 1: Thu Mar 12 05:07:18 2015 Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TAB$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TS$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_OBJ$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_IND$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_ICOL$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_COL$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_USER$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_CDEF$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_CCOL$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_LOB$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_PROPS$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_ENC$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_COLTYPE$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_SUBCOLTYPE$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_ATTRCOL$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TYPE$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_ATTRIBUTE$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_NTAB$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_REFCON$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_OPQTYPE$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_KOPM$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_PARTOBJ$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TABPART$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_INDPART$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TABSUBPART$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_INDSUBPART$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TABCOMPART$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_INDCOMPART$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_LOBFRAG$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_LOGMNR_BUILDLOG have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_SEED$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_DICTIONARY$ have been marked unusable Indexes of table  SYSTEM.LOGMNR_ATTRCOL$ have been rebuilt and are now usable Indexes of table  SYSTEM.LOGMNR_ATTRIBUTE$ have been rebuilt and are now usable Indexes of table  SYSTEM.LOGMNR_CCOL$ have been rebuilt and are now usable Indexes of table  SYSTEM.LOGMNR_CDEF$ have been rebuilt and are now usable Indexes of table  SYSTEM.LOGMNR_COL$ have been rebuilt and are now usable Indexes of table  SYSTEM.LOGMNR_COLTYPE$ have been rebuilt and are now usable Indexes of table  SYSTEM.LOGMNR_DICTIONARY$ have been rebuilt and are now usable Indexes of table  SYSTEM.LOGMNR_ENC$ have been rebuilt and are now usable Indexes of table  SYSTEM.LOGMNR_ICOL$ have been rebuilt and are now usable Indexes of table  SYSTEM.LOGMNR_IND$ have been rebuilt and are now usable Indexes of table  SYSTEM.LOGMNR_INDCOMPART$ have been rebuilt and are now usable Indexes of table  SYSTEM.LOGMNR_INDPART$ have been rebuilt and are now usable Indexes of table  SYSTEM.LOGMNR_INDSUBPART$ have been rebuilt and are now usable Indexes of table  SYSTEM.LOGMNR_KOPM$ have been rebuilt and are now usable Indexes of table  SYSTEM.LOGMNR_LOB$ have been rebuilt and are now usable Indexes of table  SYSTEM.LOGMNR_LOBFRAG$ have been rebuilt and are now usable Indexes of table  SYSTEM.LOGMNR_LOGMNR_BUILDLOG have been rebuilt and are now usable Indexes of table  SYSTEM.LOGMNR_NTAB$ have been rebuilt and are now usable Indexes of table  SYSTEM.LOGMNR_OBJ$ have been rebuilt and are now usable Indexes of table  SYSTEM.LOGMNR_OPQTYPE$ have been rebuilt and are now usable Indexes of table  SYSTEM.LOGMNR_PARTOBJ$ have been rebuilt and are now usable Indexes of table  SYSTEM.LOGMNR_PROPS$ have been rebuilt and are now usable Indexes of table  SYSTEM.LOGMNR_REFCON$ have been rebuilt and are now usable Indexes of table  SYSTEM.LOGMNR_SEED$ have been rebuilt and are now usable Indexes of table  SYSTEM.LOGMNR_SUBCOLTYPE$ have been rebuilt and are now usable Indexes of table  SYSTEM.LOGMNR_TAB$ have been rebuilt and are now usable Indexes of table  SYSTEM.LOGMNR_TABCOMPART$ have been rebuilt and are now usable Indexes of table  SYSTEM.LOGMNR_TABPART$ have been rebuilt and are now usable Indexes of table  SYSTEM.LOGMNR_TABSUBPART$ have been rebuilt and are now usable Indexes of table  SYSTEM.LOGMNR_TS$ have been rebuilt and are now usable Indexes of table  SYSTEM.LOGMNR_TYPE$ have been rebuilt and are now usable Indexes of table  SYSTEM.LOGMNR_USER$ have been rebuilt and are now usable Thu Mar 12 05:07:30 2015 Thread 1 advanced to log sequence 9 (LGWR switch)   Current log# 3 seq# 9 mem# 0: /dsk2/oradata/bjdb/redo03b.log   Current log# 3 seq# 9 mem# 1: /dsk1/oradata/bjdb/redo03a.log Thu Mar 12 05:07:31 2015 Archived Log entry 8 added for thread 1 sequence 8 ID 0x38edae0f dest 1: Thu Mar 12 05:07:38 2015 LOGMINER: End mining logfiles during dictionary load for session 1 Thu Mar 12 05:07:38 2015 LSP2 started with pid=37, OS id=4286 Thu Mar 12 05:07:40 2015 LOGMINER: Turning ON Log Auto Delete LOGMINER: Begin mining logfile during commit scan for session 1 thread 1 sequence 51, /dsk4/arch_bj/arch_1_51_873495347.log LOGMINER: End   mining logfile during commit scan for session 1 thread 1 sequence 51, /dsk4/arch_bj/arch_1_51_873495347.log LOGMINER: Begin mining logfile during commit scan for session 1 thread 1 sequence 52, /dsk4/arch_bj/arch_1_52_873495347.log LOGMINER: End mining logfiles during commit scan for session 1 LOGMINER: Turning ON Log Auto Delete LOGMINER: Begin mining logfile for session 1 thread 1 sequence 51, /dsk4/arch_bj/arch_1_51_873495347.log LOGMINER: End   mining logfile for session 1 thread 1 sequence 51, /dsk4/arch_bj/arch_1_51_873495347.log LOGMINER: Begin mining logfile for session 1 thread 1 sequence 52, /dsk4/arch_bj/arch_1_52_873495347.log LOGMINER: End   mining logfile for session 1 thread 1 sequence 52, /dsk4/arch_bj/arch_1_52_873495347.log LOGMINER: Begin mining logfile for session 1 thread 1 sequence 53, /dsk4/arch_bj/arch_1_53_873495347.log LOGMINER: End   mining logfile for session 1 thread 1 sequence 53, /dsk4/arch_bj/arch_1_53_873495347.log LOGMINER: Begin mining logfile for session 1 thread 1 sequence 54, /dsk4/arch_bj/arch_1_54_873495347.log Thu Mar 12 05:07:42 2015 LOGSTDBY Analyzer process AS00 started with server id=0 pid=37 OS id=4289 Thu Mar 12 05:07:42 2015 LOGSTDBY Apply process AS01 started with server id=1 pid=38 OS id=4291 Thu Mar 12 05:07:43 2015 LOGSTDBY Apply process AS03 started with server id=3 pid=40 OS id=4295 Thu Mar 12 05:07:43 2015 LOGSTDBY Apply process AS05 started with server id=5 pid=42 OS id=4299 Thu Mar 12 05:07:43 2015 LOGSTDBY Apply process AS04 started with server id=4 pid=41 OS id=4297 Thu Mar 12 05:07:43 2015 LOGSTDBY Apply process AS02 started with server id=2 pid=39 OS id=4293 LOGMINER: End   mining logfile for session 1 thread 1 sequence 54, /dsk4/arch_bj/arch_1_54_873495347.log LOGMINER: Begin mining logfile for session 1 thread 1 sequence 55, /dsk4/arch_bj/arch_1_55_873495347.log LOGMINER: End   mining logfile for session 1 thread 1 sequence 55, /dsk4/arch_bj/arch_1_55_873495347.log LOGMINER: Begin mining logfile for session 1 thread 1 sequence 56, /dsk4/arch_bj/arch_1_56_873495347.log LOGMINER: End   mining logfile for session 1 thread 1 sequence 56, /dsk4/arch_bj/arch_1_56_873495347.log LOGMINER: Begin mining logfile for session 1 thread 1 sequence 57, /dsk4/arch_bj/arch_1_57_873495347.log LOGMINER: End   mining logfile for session 1 thread 1 sequence 57, /dsk4/arch_bj/arch_1_57_873495347.log LOGMINER: Begin mining logfile for session 1 thread 1 sequence 58, /dsk4/arch_bj/arch_1_58_873495347.log LOGMINER: End   mining logfile for session 1 thread 1 sequence 58, /dsk4/arch_bj/arch_1_58_873495347.log LOGMINER: Begin mining logfile for session 1 thread 1 sequence 59, /dsk4/arch_bj/arch_1_59_873495347.log Thu Mar 12 05:07:55 2015 LOGMINER: End   mining logfile for session 1 thread 1 sequence 59, /dsk4/arch_bj/arch_1_59_873495347.log LOGMINER: Begin mining logfile for session 1 thread 1 sequence 60, /dsk4/arch_bj/arch_1_60_873495347.log LOGMINER: End   mining logfile for session 1 thread 1 sequence 60, /dsk4/arch_bj/arch_1_60_873495347.log LOGMINER: Begin mining logfile for session 1 thread 1 sequence 61, /dsk4/arch_bj/arch_1_61_873495347.log LOGMINER: End   mining logfile for session 1 thread 1 sequence 61, /dsk4/arch_bj/arch_1_61_873495347.log LOGMINER: Begin mining logfile for session 1 thread 1 sequence 62, /dsk4/arch_bj/arch_1_62_873495347.log LOGMINER: End   mining logfile for session 1 thread 1 sequence 62, /dsk4/arch_bj/arch_1_62_873495347.log LOGMINER: Begin mining logfile for session 1 thread 1 sequence 63, /dsk4/arch_bj/arch_1_63_873495347.log LOGMINER: End   mining logfile for session 1 thread 1 sequence 63, /dsk4/arch_bj/arch_1_63_873495347.log LOGMINER: Begin mining logfile for session 1 thread 1 sequence 64, /dsk4/arch_bj/arch_1_64_873495347.log LOGMINER: End   mining logfile for session 1 thread 1 sequence 64, /dsk4/arch_bj/arch_1_64_873495347.log LOGMINER: Begin mining logfile for session 1 thread 1 sequence 65, /dsk4/arch_bj/arch_1_65_873495347.log LOGMINER: End   mining logfile for session 1 thread 1 sequence 65, /dsk4/arch_bj/arch_1_65_873495347.log LOGMINER: Begin mining logfile for session 1 thread 1 sequence 66, /dsk1/oradata/bjdb/std_redo02.log

--从告警日志可以看出，logmnr已经开始对日志进行挖掘，并开始做SQL APPLY.

五、查看数据库同步信息

主库：

sys@TESTDB12>select count(*) from scott.emp1;

  COUNT(*)
----------
       120


备库：
SYS@ TestDB12>select count(*) from scott.emp1;

  COUNT(*)
----------
       120
此时主库和备库数据已经同步！