Oracle Study之--Logical Standby日志应用错误案例
系统环境:
操作系统:RedHat EL54
数据库: Oracle 11gR2
一、错误现象:(在逻辑备库应用日志时)
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
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, p_w_picpath: 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信息
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
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视图:
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.
查看告警日志:
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
此时主库和备库数据已经同步!
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。