Loss of Oracle archive file causes OGG not to start
Environment: AIX7.1
DB:ORACLE11.2.0.4
OGG:12.3.0.1.4
Problem: unable to repeatedly synchronize data to the target library, close archiving, unable to start OGG after data recovery
# = #
Connecting to 153.12.72.110:22...
Connection established.
To escape to local shell, press' Ctrl+Alt+]'.
1 unsuccessful login attempt since last login.
Last unsuccessful login: Fri Dec 6 18:38:24 BEIST 2019 on ssh from 89.12.74.46
Last login: Fri Dec 6 18:38:05 BEIST 2019 on ssh from 89.12.88.71
*
* *
* *
* Welcome to AIX Version 7.1! *
* *
* *
* Please see the README file in / usr/lpp/bos for information pertinent to *
* this release of the AIX Operating System. *
* *
* *
*
[YOU HAVE NEW MAIL]
# = = stop OGG==
# = = stop order: EX- > PP- > mgr=
Nskxt1:/home/oracle$cd / ogg/ogg12c
Nskxt1:/ogg/ogg12c$ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.3.0.1.4 OGGCORE_12.3.0.1.0_PLATFORMS_180415.0359_FBO
AIX 6, ppc, 64bit (optimized), Oracle 11g on Apr 19 2018 05:30:29
Operating system character set identified as US-ASCII.
Copyright (C) 1995, 2018, Oracle and/or its affiliates. All rights reserved.
GGSCI (nskxt1) 1 > info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EX_DZFP 00:00:00 00:00:00
EXTRACT RUNNING EX_PTFP 00:00:01 00:00:03
EXTRACT RUNNING EX_QTFP 00:00:01 00:00:09
EXTRACT RUNNING EX_ZYFP 00:00:00 00:00:00
EXTRACT RUNNING PP_DZFP 00:00:00 00:00:00
EXTRACT RUNNING PP_PTFP 00:00:00 00:00:03
EXTRACT RUNNING PP_QTFP 00:00:00 00:00:07
EXTRACT RUNNING PP_ZYFP 00:00:00 00:00:04
GGSCI (nskxt1) 3 > stop ex_dzfp
Sending STOP request to EXTRACT EX_DZFP...
Request processed.
GGSCI (nskxt1) 5 > stop ex_ptfp
Sending STOP request to EXTRACT EX_PTFP...
Request processed.
GGSCI (nskxt1) 6 > stop ex_qtfp
Sending STOP request to EXTRACT EX_QTFP...
Request processed.
GGSCI (nskxt1) 7 > stop ex_zyfp
Sending STOP request to EXTRACT EX_ZYFP...
Request processed.
GGSCI (nskxt1) 8 > info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EX_DZFP 00:00:01 00:00:49
EXTRACT STOPPED EX_PTFP 00:00:01 00:00:27
EXTRACT STOPPED EX_QTFP 00:00:01 00:00:16
EXTRACT STOPPED EX_ZYFP 00:00:02 00:00:03
EXTRACT RUNNING PP_DZFP 00:00:00 00:00:02
EXTRACT RUNNING PP_PTFP 00:00:00 00:00:05
EXTRACT RUNNING PP_QTFP 00:00:00 00:00:10
EXTRACT RUNNING PP_ZYFP 00:00:00 00:00:07
GGSCI (nskxt1) 12 > stop PP_DZFP
Sending STOP request to EXTRACT PP_DZFP...
Request processed.
GGSCI (nskxt1) 14 > stop PP_PTFP
Sending STOP request to EXTRACT PP_PTFP...
Request processed.
GGSCI (nskxt1) 15 > stop PP_QTFP
Sending STOP request to EXTRACT PP_QTFP...
Request processed.
GGSCI (nskxt1) 16 > stop PP_ZYFP
Sending STOP request to EXTRACT PP_ZYFP...
Request processed.
GGSCI (nskxt1) 17 > info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EX_DZFP 00:00:01 00:04:05
EXTRACT STOPPED EX_PTFP 00:00:01 00:03:43
EXTRACT STOPPED EX_QTFP 00:00:01 00:03:32
EXTRACT STOPPED EX_ZYFP 00:00:02 00:03:18
EXTRACT STOPPED PP_DZFP 00:00:00 00:00:52
EXTRACT STOPPED PP_PTFP 00:00:00 00:00:27
EXTRACT STOPPED PP_QTFP 00:00:00 00:00:16
EXTRACT STOPPED PP_ZYFP 00:00:00 00:00:05
GGSCI (nskxt1) 18 > stop mgr
Manager process is required by other GGS processes.
Are you sure you want to stop it (yzone)? y
Sending STOP request to MANAGER...
Request processed.
Manager stopped.
GGSCI (nskxt1) 19 > info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
EXTRACT STOPPED EX_DZFP 00:00:01 00:04:39
EXTRACT STOPPED EX_PTFP 00:00:01 00:04:17
EXTRACT STOPPED EX_QTFP 00:00:01 00:04:06
EXTRACT STOPPED EX_ZYFP 00:00:02 00:03:52
EXTRACT STOPPED PP_DZFP 00:00:00 00:01:26
EXTRACT STOPPED PP_PTFP 00:00:00 00:01:01
EXTRACT STOPPED PP_QTFP 00:00:00 00:00:50
EXTRACT STOPPED PP_ZYFP 00:00:00 00:00:39
GGSCI (nskxt1) 20 > exit
# = stop database archiving =
# all RAC nodes should be shut down
# execute both in 110 and 111
Sqlplus / as sysdba
SQL > shutdown immediate
# execute in 110
SQL > startup mount
SQL > alter database noarchivelog
# = record stop archiving time =
Nskxt2:/home/oracle$date
Fri Dec 6 19:10:01 BEIST 2019
SQL > alter database open
SQL > alter system set cluster_database=true scope=spfile
# View Archive status
SQL > archive log list
# = processing, recovering data = =
Slightly
# = start database archiving and database =
# = = start OGG==
# = = order: MGR- > PP- > EX==
GGSCI (nskxt1) 1 > start mgr
GGSCI (nskxt1) 2 > start PP_DZFP
Sending START request to MANAGER...
EXTRACT PP_DZFP starting
GGSCI (nskxt1) 3 > start PP_PTFP
Sending START request to MANAGER...
EXTRACT PP_PTFP starting
GGSCI (nskxt1) 4 > start PP_QTFP
Sending START request to MANAGER...
EXTRACT PP_QTFP starting
GGSCI (nskxt1) 5 > start PP_ZYFP
Sending START request to MANAGER...
EXTRACT PP_ZYFP starting
GGSCI (nskxt1) 6 > start EX_DZFP
Sending START request to MANAGER...
EXTRACT EX_DZFP starting
GGSCI (nskxt1) 7 > start EX_PTFP
Sending START request to MANAGER...
EXTRACT EX_PTFP starting
GGSCI (nskxt1) 8 > start EX_QTFP
Sending START request to MANAGER...
EXTRACT EX_QTFP starting
GGSCI (nskxt1) 9 > start EX_ZYFP
Sending START request to MANAGER...
EXTRACT EX_ZYFP starting
GGSCI (nskxt1) 10 > info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EX_DZFP 00:00:01 00:04:05
EXTRACT STOPPED EX_PTFP 00:00:01 00:03:43
EXTRACT STOPPED EX_QTFP 00:00:01 00:03:32
EXTRACT STOPPED EX_ZYFP 00:00:02 00:03:18
EXTRACT RUNNING PP_DZFP 00:00:00 00:00:52
EXTRACT RUNNING PP_PTFP 00:00:00 00:00:27
EXTRACT RUNNING PP_QTFP 00:00:00 00:00:16
EXTRACT RUNNING PP_ZYFP 00:00:00 00:00:05
# if it doesn't work to start OGG directly, you can start it by specifying the time to close the archive.
GGSCI (nskxt1) 11 > alter extract EX_DZFP,tranlog,begin 2019-12-06 19:10:01
GGSCI (nskxt1) 12 > alter extract EX_PTFP,tranlog,begin 2019-12-06 19:10:01
GGSCI (nskxt1) 13 > alter extract EX_QTFP,tranlog,begin 2019-12-06 19:10:01
GGSCI (nskxt1) 14 > alter extract EX_ZYFP,tranlog,begin 2019-12-06 19:10:01
# found that some processes cannot be started
GGSCI (nskxt1) 15 > info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EX_DZFP 00:00:01 00:04:05
EXTRACT STOPPED EX_PTFP 00:00:01 00:03:43
EXTRACT STOPPED EX_QTFP 00:00:01 00:03:32
EXTRACT STOPPED EX_ZYFP 00:00:02 00:03:18
EXTRACT RUNNING PP_DZFP 00:00:00 00:00:52
EXTRACT RUNNING PP_PTFP 00:00:00 00:00:27
EXTRACT RUNNING PP_QTFP 00:00:00 00:00:16
EXTRACT RUNNING PP_ZYFP 00:00:00 00:00:05
# = perform startup log view =
GGSCI (nskxt1) 16 > view report EX_PTFP
....
2019-12-06 20:41:55 ERROR OGG-00446 Opening file + Fra/2_2519_1014763310.dbf inDBLOGREADER..
ORA-17503:ksfdopn:2 Failed to open file + FRA/2_2519_1014763310.dbf
ORA-15173:entry'2 "2519" 1014763310.dbf 'does not exist in directory' /'
....
# there is the above error message in the log. Go to ASM to check whether the 2_2519_1014763310.dbf archive log exists.
GGSCI (nskxt1) 17 > exit
Nskxt1:/ogg/ogg12c$su-grid
Grid's Password:
Nskxt1:/home/grid$asmcmd
ASMCMD > ls
CRS/
DATA/
FRA/
OGG/
ASMCMD > cd fra
ASMCMD > ls
NSKXT/
ASMCMD > cd nskxt
ASMCMD > ls
ARCHIVELOG/
ONLINELOG/
ASMCMD > cd ARCHIVELOG
ASMCMD > ls
2019_11_30/
2019_12_01/
2019_12_02/
2019_12_03/
2019_12_04/
2019_12_05/
2019_12_06/
ASMCMD > cd 2019 / 12 / 06
ASMCMD > ls-l
...
ARCHIVELOG UNPROT COARSE DEC 06 21:00:00 Y thread_2_seq_2516.562.1026325513
ARCHIVELOG UNPROT COARSE DEC 06 21:00:00 Y thread_2_seq_2517.859.1026325771
ARCHIVELOG UNPROT COARSE DEC 06 21:00:00 Y thread_2_seq_2518.299.1026326019
ARCHIVELOG UNPROT COARSE DEC 06 21:00:00 Y thread_2_seq_2521.1565.1026329405
ARCHIVELOG UNPROT COARSE DEC 06 21:00:00 Y thread_2_seq_2522.1627.1026329407
ARCHIVELOG UNPROT COARSE DEC 06 21:00:00 Y thread_2_seq_2523.966.1026334027
ARCHIVELOG UNPROT COARSE DEC 06 22:00:00 Y thread_2_seq_2524.329.1026334807
...
# = 2518 goes directly to 2521. It is suspected that 2519 or 2520 archive logs have entered the black hole. Try using the time of thread_2_seq_2521.1565.1026329405 DEC 06 at 21:00:00 to start OGG.
ASMCMD > exit
Nskxt1:/ogg/ogg12c$su-grid
GGSCI (nskxt1) 1 > alter extract EX_DZFP,tranlog,begin 2019-12-06 21:00:00
GGSCI (nskxt1) 2 > alter extract EX_PTFP,tranlog,begin 2019-12-06 21:00:00
GGSCI (nskxt1) 3 > alter extract EX_QTFP,tranlog,begin 2019-12-06 21:00:00
GGSCI (nskxt1) 4 > alter extract EX_ZYFP,tranlog,begin 2019-12-06 21:00:00
# = only start the capture process, and the delivery process directly start PP_DZFP...
# = continuously check info all, all OGG processes are normal
GGSCI (nskxt1) 10 > info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EX_DZFP 00:00:01 00:04:05
EXTRACT RUNNING EX_PTFP 00:00:01 00:03:43
EXTRACT RUNNING EX_QTFP 00:00:01 00:03:32
EXTRACT RUNNING EX_ZYFP 00:00:02 00:03:18
EXTRACT RUNNING PP_DZFP 00:00:00 00:00:52
EXTRACT RUNNING PP_PTFP 00:00:00 00:00:27
EXTRACT RUNNING PP_QTFP 00:00:00 00:00:16
EXTRACT RUNNING PP_ZYFP 00:00:00 00:00:05
GGSCI (nskxt1) 18 > exit
# = Open the application
