Symptoms:
2019-07-29 10:15:56 INFO OGG-02248 Logmining server DDL filtering enabled.
Source Context :
SourceModule : [er.redo.oraxo]
SourceID : [/scratch/aime/adestore/views/aime_adc00jza/oggcore/OpenSys/src/app/er/redo/oracle/redooraix.c]
SourceMethod : [REDOORAIX_abend_if_missing_logfile]
SourceLine : [8360]
ThreadBacktrace : [16] elements
: [ng::Thread::RunThread(ggs::gglib::MultiThreading::Thread::ThreadArgs*)]
: [/ogg/app/oracle/oggma/12.3.0.1.4/bin/extract(ggs::gglib::MultiThreading::MainThread::Run(int, char**))]
: [/ogg/app/oracle/oggma/12.3.0.1.4/bin/extract(ggs::gglib::MultiThreading::MainThread::Run(int, char**))]
: [/ogg/app/oracle/oggma/12.3.0.1.4/bin/extract()]
: [/ogg/app/oracle/oggma/12.3.0.1.4/bin/extract(ggs::er::OraTranLogDataSource::setup())]
: [/ogg/app/oracle/oggma/12.3.0.1.4/bin/extract(ggs::gglib::MultiThreading::MainThread::Run(int, char**))]
: [/ogg/app/oracle/oggma/12.3.0.1.4/bin/extract(ggs::er::OraTranLogDataSource::setup())]
: [/ogg/app/oracle/oggma/12.3.0.1.4/bin/extract(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreading::Thread::ThreadArgs*)]
: [/ogg/app/oracle/oggma/12.3.0.1.4/bin/extract(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreading::Thread::ThreadArgs*)]
: [/ogg/app/oracle/oggma/12.3.0.1.4/bin/extract()]
: [/ogg/app/oracle/oggma/12.3.0.1.4/bin/extract(ggs::gglib::MultiThreading::MainThread::ExecMain())]
: [/ogg/app/oracle/oggma/12.3.0.1.4/bin/extract(ggs::gglib::MultiThreading::MainThread::Run(int, char**))]
: [/ogg/app/oracle/oggma/12.3.0.1.4/bin/extract(ggs::gglib::MultiThreading::MainThread::Run(int, char**))]
: [/ogg/app/oracle/oggma/12.3.0.1.4/bin/extract(main)]
: [/ogg/app/oracle/oggma/12.3.0.1.4/bin/extract()]
: [/ogg/app/oracle/oggma/12.3.0.1.4/bin/extract()]
2019-07-29 10:18:56 ERROR OGG-00868 Error code 1291, error message: ORA-01291: missing logfile
(Missing Log File WAITING FOR DICTIONARY REDO. Read Position SCN: 0.162208249 (162208249)).
Cause:
The archive logs have been removed in ASM.
Solution1: alter extract begin scn
select distinct first_change#,next_change#, INST_ID, THREAD#, SEQUENCE#, NAME, DELETED, STATUS from gv$archived_log order by first_change#;
174045165 174046741 1 1 5493 YES D
174046741 174108212 1 1 5494 YES D
174108212 174120607 1 1 5495 YES D
174120607 174131234 1 1 5496 YES D
174131234 174338081 1 1 5497 +ARDG/ORCL/ARCHIVELOG/2019_07_24/thread_1_seq_54 NO A 97.275.1014474887
174338081 174350814 1 1 5498 +ARDG/ORCL/ARCHIVELOG/2019_07_24/thread_1_seq_54 NO A 98.263.1014475049
174350814 174418150 1 1 5499 +ARDG/ORCL/ARCHIVELOG/2019_07_24/thread_1_seq_54 NO A 99.268.1014481037
174418150 174424316 1 1 5500 +ARDG/ORCL/ARCHIVELOG/2019_07_24/thread_1_seq_55 NO A 00.266.1014481163
174424316 174426287 1 1 5501 +ARDG/ORCL/ARCHIVELOG/2019_07_24/thread_1_seq_55 NO A 01.319.1014481209
174426287 174427450 1 1 5502 +ARDG/ORCL/ARCHIVELOG/2019_07_24/thread_1_seq_55 NO A 02.262.1014481251
174427450 174429394 1 1 5503 +ARDG/ORCL/ARCHIVELOG/2019_07_24/thread_1_seq_55 NO A 03.846.1014481293
174429394 174439082 1 1 5504 +ARDG/ORCL/ARCHIVELOG/2019_07_24/thread_1_seq_55 NO A 04.847.1014481409
174439082 174447276 1 1 5505 +ARDG/ORCL/ARCHIVELOG/2019_07_24/thread_1_seq_55 NO A 83.533.1014854571
181850188 181974245 1 1 5584 +ARDG/ORCL/ARCHIVELOG/2019_07_29/thread_1_seq_55 NO 84.269.1014859507
181974245 182124125 1 1 5585 +ARDG/ORCL/ARCHIVELOG/2019_07_29/thread_1_seq_55 NO 85.693.1014865501
182124125 182269735 1 1 5586 +ARDG/ORCL/ARCHIVELOG/2019_07_29/thread_1_seq_55 NO 86.692.1014871441
182269735 182413560 1 1 5587 +ARDG/ORCL/ARCHIVELOG/2019_07_29/thread_1_seq_55 NO 87.691.1014877277
182413560 182545126 1 1 5588 +ARDG/ORCL/ARCHIVELOG/2019_07_29/thread_1_seq_55 NO 88.480.1014883027
182545126 182663737 1 1 5589 +ARDG/ORCL/ARCHIVELOG/2019_07_29/thread_1_seq_55 NO 89.843.1014888423
SQL> select scn_to_timestamp (174131234) from dual;
SCN_TO_TIMESTAMP(174131234)
—————————————————————————
24-JUL-19 09.27.02.000000000 AM
OGG (https://linuxvm01:18000 LOCAL) 14> info EXT01 detail
EXTRACT EXT01 Last Started 2019-07-18 13:22 Status STOPPED
Checkpoint Lag 00:00:05 (updated 142:25:03 ago)
Log Read Checkpoint Oracle Integrated Redo Logs
2019-07-23 12:11:39
SCN 0.162208249 (162208249)
Target Extract Trails:
Trail Name Seqno RBA Max MB Trail Type
/ogg/app/oracle/oggma/12.3.0.1.4/DEPLOYMENT_LOCAL/LOCAL/var/lib/data/ET 0 176028862 200 EXTTRAIL
Integrated Extract outbound server first scn: 0.151507336 (151507336)
Integrated Extract outbound server filtering start scn: 0.151508146 (151508146)
Extract Source Begin End
Not Available 2019-07-18 13:17 2019-07-23 12:11
Not Available * Initialized * 2019-07-18 13:17
Not Available * Initialized * 2019-07-18 13:17
Not Available * Initialized * 2019-07-18 13:17
Current directory /ogg/app/oracle/oggma/12.3.0.1.4/DEPLOYMENT_LOCAL/ServiceManager/bin
Report file /ogg/app/oracle/oggma/12.3.0.1.4/DEPLOYMENT_LOCAL/LOCAL/var/lib/report/EXT01.rpt
Parameter file /ogg/app/oracle/oggma/12.3.0.1.4/DEPLOYMENT_LOCAL/LOCAL/etc/conf/ogg/EXT01.prm
Checkpoint file /ogg/app/oracle/oggma/12.3.0.1.4/DEPLOYMENT_LOCAL/LOCAL/var/lib/checkpt/EXT01.cpe
Process file /ogg/app/oracle/oggma/12.3.0.1.4/DEPLOYMENT_LOCAL/LOCAL/var/run/EXT01.pce
Error log /ogg/app/oracle/oggma/12.3.0.1.4/DEPLOYMENT_LOCAL/LOCAL/var/log/ggserr.log
No REPLIETT groups found, but some coordinated threads may have been excluded
SCN scn
Starts Extract at the transaction in the redo log that has the specified
Oracle system change number (SCN). This option is valid for Extract both
in classic capture and integrated modes. For Extract in integrated mode,
the SCN value must be greater than the SCN at which the Extract was
registered with the database. If using the SCN or BEGIN option for
Integrated Extract, it requires a DBLOGIN, and the SCN or timestamp
value specified cannot be below the outbound server’s first SCN or timestamp.
To find the outbound server’s first SCN, issue the following command:
INFO EXTRACT group-name, SHOWCH DETAIL
The first SCN value is listed as shown in the following example:
Integrated Extract outbound server first scn: 0.665884 (665884)
OGG (https://linuxvm01:18000 LOCAL) 17> INFO EXTRACT EXT01, SHOWCH DETAIL
EXTRACT EXT01 Last Started 2019-07-18 13:22 Status STOPPED
Checkpoint Lag 00:00:05 (updated 142:27:24 ago)
Log Read Checkpoint Oracle Integrated Redo Logs
2019-07-23 12:11:39
SCN 0.162208249 (162208249)
Current Checkpoint Detail:
Read Checkpoint #1
Oracle Integrated Redo Log
Startup Checkpoint (starting position in the data source):
Timestamp: 2019-07-18 13:17:49.000000
SCN: Not available
Recovery Checkpoint (position of oldest unprocessed transaction in the data source):
Timestamp: 2019-07-23 12:11:39.000000
SCN: 0.162208249 (162208249)
Current Checkpoint (position of last record read in the data source):
Timestamp: 2019-07-23 12:11:39.000000
SCN: 0.162208249 (162208249)
BR Previous Recovery Checkpoint:
Timestamp: 2019-07-18 13:22:15.447084
SCN: Not available
BR Begin Recovery Checkpoint:
Timestamp: 2019-07-23 09:35:26.000000
SCN: 0.162002440 (162002440)
BR End Recovery Checkpoint:
Timestamp: 2019-07-23 09:35:26.000000
SCN: 0.162002442 (162002442)
Write Checkpoint #1
GGS Log Trail
Current Checkpoint (current write position):
Sequence #: 0
RBA: 176028862
Timestamp: 2019-07-23 12:11:44.357951
Extract Trail: /ogg/app/oracle/oggma/12.3.0.1.4/DEPLOYMENT_LOCAL/LOCAL/var/lib/data/ET
Seqno Length: 9
Flip Seqno Length: No
Trail Type: EXTTRAIL
Header:
Version = 2
Record Source = A
Type = 13
# Input Checkpoints = 1
# Output Checkpoints = 1
File Information:
Block Size = 2048
Max Blocks = 100
Record Length = 2048
Current Offset = 0
Configuration:
Data Source = 3
Transaction Integrity = 1
Task Type = 0
Status:
Start Time = 2019-07-18 13:22:22
Last Update Time = 2019-07-23 12:11:44
Stop Status = G
Last Result = 520
OGG (https://linuxvm01:18000 LOCAL) 20> dblogin useridalias SRCOGGCDB domain SRCOGGCDB
OGG (https://linuxvm01:18000 LOCAL as SRCOGGCDB) 21> alter extract EXT01, SCN 174131234
2019-07-29T10:40:55Z INFO OGG-08100 EXTRACT altered.
OGG (https://linuxvm01:18000 LOCAL as SRCOGGCDB) 22> info EXT01 detail
EXTRACT EXT01 Initialized 2019-07-29 10:40 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:00:07 ago)
Log Read Checkpoint Oracle Integrated Redo Logs
First Record
SCN 0.174131234 (174131234)
Target Extract Trails:
Trail Name Seqno RBA Max MB Trail Type
/ogg/app/oracle/oggma/12.3.0.1.4/DEPLOYMENT_LOCAL/LOCAL/var/lib/data/ET 0 176028862 200 EXTTRAIL
Integrated Extract outbound server first scn: 0.151507336 (151507336)
Integrated Extract outbound server filtering start scn: 0.151508146 (151508146)
Extract Source Begin End
Not Available * Initialized * First Record
Not Available 2019-07-18 13:17 2019-07-23 12:11
Not Available * Initialized * 2019-07-18 13:17
Not Available * Initialized * 2019-07-18 13:17
Not Available * Initialized * 2019-07-18 13:17
Current directory /ogg/app/oracle/oggma/12.3.0.1.4/DEPLOYMENT_LOCAL/ServiceManager/bin
Report file /ogg/app/oracle/oggma/12.3.0.1.4/DEPLOYMENT_LOCAL/LOCAL/var/lib/report/EXT01.rpt
Parameter file /ogg/app/oracle/oggma/12.3.0.1.4/DEPLOYMENT_LOCAL/LOCAL/etc/conf/ogg/EXT01.prm
Checkpoint file /ogg/app/oracle/oggma/12.3.0.1.4/DEPLOYMENT_LOCAL/LOCAL/var/lib/checkpt/EXT01.cpe
Process file /ogg/app/oracle/oggma/12.3.0.1.4/DEPLOYMENT_LOCAL/LOCAL/var/run/EXT01.pce
Error log /ogg/app/oracle/oggma/12.3.0.1.4/DEPLOYMENT_LOCAL/LOCAL/var/log/ggserr.log
No REPLIETT groups found, but some coordinated threads may have been excluded
OGG (https://linuxvm01:18000 LOCAL as SRCOGGCDB) 23> INFO EXTRACT EXT01, SHOWCH DETAIL
EXTRACT EXT01 Initialized 2019-07-29 10:40 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:01:08 ago)
Log Read Checkpoint Oracle Integrated Redo Logs
First Record
SCN 0.174131234 (174131234)
Current Checkpoint Detail:
Read Checkpoint #1
Oracle Integrated Redo Log
Startup Checkpoint (starting position in the data source):
Timestamp:
SCN: 0.174131234 (174131234)
Recovery Checkpoint (position of oldest unprocessed transaction in the data source):
Timestamp:
SCN: 0.174131234 (174131234)
Current Checkpoint (position of last record read in the data source):
Timestamp:
SCN: 0.174131234 (174131234)
Write Checkpoint #1
GGS Log Trail
Current Checkpoint (current write position):
Sequence #: 0
RBA: 176028862
Timestamp: 2019-07-23 12:11:44.357951
Extract Trail: /ogg/app/oracle/oggma/12.3.0.1.4/DEPLOYMENT_LOCAL/LOCAL/var/lib/data/ET
Seqno Length: 9
Flip Seqno Length: No
Trail Type: EXTTRAIL
Header:
Version = 2
Record Source = U
Type = 13
# Input Checkpoints = 1
# Output Checkpoints = 1
File Information:
Block Size = 2048
Max Blocks = 100
Record Length = 2048
Current Offset = 0
Configuration:
Data Source = 3
Transaction Integrity = 1
Task Type = 0
Status:
Start Time = 2019-07-29 10:40:55
Last Update Time = 2019-07-29 10:40:55
Stop Status = G
Last Result = 520
alter extract EXT01, SCN 175207446
alter extract EXT01, SCN 182545126
Solution2: alter extract begin now
OGG (https://linuxvm01:18000 LOCAL as SRCOGGCDB) 39> alter extract EXT01 begin now
2019-07-29T11:33:18Z INFO OGG-08100 EXTRACT altered.
OGG (https://linuxvm01:18000 LOCAL as SRCOGGCDB) 40> info EXT01 detail
EXTRACT EXT01 Initialized 2019-07-29 11:33 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:00:04 ago)
Log Read Checkpoint Oracle Integrated Redo Logs
2019-07-29 11:33:18
SCN 0.0 (0)
Target Extract Trails:
Trail Name Seqno RBA Max MB Trail Type
/ogg/app/oracle/oggma/12.3.0.1.4/DEPLOYMENT_LOCAL/LOCAL/var/lib/data/ET 0 176028862 200 EXTTRAIL
Integrated Extract outbound server first scn: 0.151507336 (151507336)
Integrated Extract outbound server filtering start scn: 0.151508146 (151508146)
Extract Source Begin End
Not Available * Initialized * 2019-07-29 11:33
Not Available * Initialized * First Record
Not Available * Initialized * First Record
Not Available * Initialized * First Record
Not Available 2019-07-18 13:17 2019-07-23 12:11
Not Available * Initialized * 2019-07-18 13:17
Not Available * Initialized * 2019-07-18 13:17
Not Available * Initialized * 2019-07-18 13:17
Current directory /ogg/app/oracle/oggma/12.3.0.1.4/DEPLOYMENT_LOCAL/ServiceManager/bin
Report file /ogg/app/oracle/oggma/12.3.0.1.4/DEPLOYMENT_LOCAL/LOCAL/var/lib/report/EXT01.rpt
Parameter file /ogg/app/oracle/oggma/12.3.0.1.4/DEPLOYMENT_LOCAL/LOCAL/etc/conf/ogg/EXT01.prm
Checkpoint file /ogg/app/oracle/oggma/12.3.0.1.4/DEPLOYMENT_LOCAL/LOCAL/var/lib/checkpt/EXT01.cpe
Process file /ogg/app/oracle/oggma/12.3.0.1.4/DEPLOYMENT_LOCAL/LOCAL/var/run/EXT01.pce
Error log /ogg/app/oracle/oggma/12.3.0.1.4/DEPLOYMENT_LOCAL/LOCAL/var/log/ggserr.log
No REPLIETT groups found, but some coordinated threads may have been excluded
OGG (https://linuxvm01:18000 LOCAL as SRCOGGCDB) 41>
http://www.ateam-oracle.com/oracle-goldengate-extract-recovery-missing-archivelogs
Solution3: Delete and Register extract
OGG (https://linuxvm01:18000 LOCAL) 20> dblogin useridalias SRCOGGCDB domain SRCOGGCDB
OGG (https://linuxvm01:18000 LOCAL as SRCOGGCDB) 46> delete extract EXT01
2019-07-29T11:45:50Z INFO OGG-01750 Successfully unregistered EXTRACT EXT01 from database.
2019-07-29T11:45:50Z INFO OGG-08100 Deleted EXTRACT EXT01.
OGG (https://linuxvm01:18000 LOCAL as SRCOGGCDB) 47> add extract EXT01, INTEGRATED TRANLOG, scn 174131234
2019-07-29T11:47:53Z INFO OGG-08100 EXTRACT (Integrated) added.
2019-07-29T11:47:53Z INFO OGG-08100 EXTRACT altered.
OGG (https://linuxvm01:18000 LOCAL as SRCOGGCDB) 48> add exttrail /ogg/app/oracle/oggma/12.3.0.1.4/DEPLOYMENT_LOCAL/LOCAL/var/lib/data/ET, extract EXT01, megabytes 200
2019-07-29T11:48:49Z WARNING OGG-08272 Trail file ‘/ogg/app/oracle/oggma/12.3.0.1.4/DEPLOYMENT_LOCAL/LOCAL/var/lib/data/ET000000000’ already exists.
2019-07-29T11:48:49Z INFO OGG-08100 EXTTRAIL added.
OGG (https://linuxvm01:18000 LOCAL as SRCOGGCDB) 58> register extract EXT01 DATABASE CONTAINER (ORCLPDB1)
2019-07-29T11:57:24Z INFO OGG-02003 Extract EXT01 successfully registered with database at SCN 182895444.
OGG (https://linuxvm01:18000 LOCAL as SRCOGGCDB) 60> start EXT01
2019-07-29T12:00:02Z INFO OGG-00975 EXTRACT EXT01 starting
2019-07-29T12:00:02Z INFO OGG-15426 EXTRACT EXT01 started
OGG (https://linuxvm01:18000 LOCAL as SRCOGGCDB) 62> info EXT01 detail
EXTRACT EXT01 Last Started 2019-07-29 12:00 Status RUNNING
Checkpoint Lag 00:00:01 (updated 00:00:00 ago)
Process ID 27915
Log Read Checkpoint Oracle Integrated Redo Logs
2019-07-29 12:00:34
SCN 0.182937230 (182937230)
Target Extract Trails:
Trail Name Seqno RBA Max MB Trail Type
/ogg/app/oracle/oggma/12.3.0.1.4/DEPLOYMENT_LOCAL/LOCAL/var/lib/data/ET 0 46757 200 EXTTRAIL
Integrated Extract outbound server first scn: 0.182894551 (182894551)
Integrated Extract outbound server filtering start scn: 0.182895444 (182895444)
Extract Source Begin End
Not Available * Initialized * 2019-07-29 12:00
Not Available * Initialized * First Record
Not Available * Initialized * First Record
Not Available * Initialized * First Record
Not Available * Initialized * 2019-07-29 11:47
Current directory /ogg/app/oracle/oggma/12.3.0.1.4/DEPLOYMENT_LOCAL/ServiceManager/bin
Report file /ogg/app/oracle/oggma/12.3.0.1.4/DEPLOYMENT_LOCAL/LOCAL/var/lib/report/EXT01.rpt
Parameter file /ogg/app/oracle/oggma/12.3.0.1.4/DEPLOYMENT_LOCAL/LOCAL/etc/conf/ogg/EXT01.prm
Checkpoint file /ogg/app/oracle/oggma/12.3.0.1.4/DEPLOYMENT_LOCAL/LOCAL/var/lib/checkpt/EXT01.cpe
Process file /ogg/app/oracle/oggma/12.3.0.1.4/DEPLOYMENT_LOCAL/LOCAL/var/run/EXT01.pce
Error log /ogg/app/oracle/oggma/12.3.0.1.4/DEPLOYMENT_LOCAL/LOCAL/var/log/ggserr.log
No REPLIETT groups found, but some coordinated threads may have been excluded
OGG (https://linuxvm01:18000 LOCAL as SRCOGGCDB) 64> send extract EXT01 status
Sending status request to EXTRACT EXT01 …
EXTRACT EXT01 (PID 27915)
Current status: Recovery complete: Processing data
Current read position:
Redo thread #: 1
Sequence #: 5593
RBA: 335019420
Timestamp: 2019-07-29 12:01:22.000000
SCN: 0.182938856 (182938856)
Current write position:
Sequence #: 0
RBA: 47073
Timestamp: 2019-07-29 12:01:15.995807
Extract Trail: /ogg/app/oracle/oggma/12.3.0.1.4/DEPLOYMENT_LOCAL/LOCAL/var/lib/data/ET
