Recently I came across the following error :
ERROR: failed to establish dependency between database ps1 and diskgroup resource ora.FRA.dg
Error 12154 received logging on to the standby
FAL[client, ARC0]: Error 12154 connecting to s1 for fetching gap sequence
Error 12154 received logging on to the standby
FAL[client, ARC0]: Error 12154 connecting to s2 for fetching gap sequence
Lets have a look to see what has happened and resolve.
1) If we log onto the standby and run the following we can see the gap.
SQL> select *
2 from v$archive_gap;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 74298 74419
2 67767 67844
1b) Also as a different example you could log onto the ASM standby machine as Oracle and have a look at the archive logs and see if there is any missing.
$asmcmd cd FRA cd LS78LIVE ls
If we look at the list we can identify missing log, it is in sequential order ls78live_1 is thread 1, ls78live_2 is thread 2., We can see below ls78live_1_144812_704059285.arc is missing.
ls78live_1_144811_704059285.arc ls78live_1_144813_704059285.arc ls78live_1_144814_704059285.arc ls78live_1_144815_704059285.arc .. . ls78live_2_107944_704059285.arc ls78live_2_107945_704059285.arc ls78live_2_107946_704059285.arc
2) On Primary database retrieve the missing archive log files based on the information we got from v$archive_gap.
rman target sys/** catalog rcuser/**@rmcatalog
RMAN> run
{allocate channel c1 type disk;
backup archivelog from sequence 74298 until sequence 74299 thread 1 format '/database/backup/s78live/gap/arch_1_%U';
backup archivelog from sequence 67767 until sequence 67768 thread 2 format '/database/backup/s78live/gap/arch_2_%U';
}
If the archive logs have been already removed you will get the following error.
Starting backup at 22-JUL-16 specification does not match any archived log in the repository backup cancelled because there are no files to backup Finished backup at 22-JUL-16.
In this case you will need to look through your backed up archive logs.
3) On Standby – lets restore the archive files
RMAN> run
{catalog start with '/database/backup/s82live/arch/1dayold';
allocate channel c1 type disk ;
allocate channel c2 type disk ;
restore archivelog from sequence 74298 until sequence 74299 thread 1;
restore archivelog from sequence 67767 until sequence 67768 thread 2;
}
12) We now enable the recovery of the standby.
SQL> sqlplus / as sysdba SQL> alter database recover managed standby database using current logfile disconnect from session;
Summary
We have identified the gap, found the missing archive log’s, copied them to the standby and re-enabled the dataguard.
(Day 1). There was a really good turnout easily I would say 150+ and some had travelled from the borders of Poland. There were 2 tracks running, I went to Jim’s talk DBA, Heal Thyself: Five diseases of IT Organizations. It was my first time at Jim’s talk, it was superbly delivered after I had left I decided to polish up on my slides for later. I missed Heli Helskyaho’s @helifromfinland talk about SQL developer that I really wanted to go to, I heard it was really good. I will catch you next time.
My talk was about extraction of cloud data to a premise BI and monitoring. The room was pretty full, that was a great sign, never really been nervous too much before but I was this time – as the talk progressed we moved to Cloud security and monitoring in the cloud as there was a great interest in this area. After my talk had finished we still had around 5m so we discussed the cloud aspects in more detail. I know there were some more questions on the monitoring side, if anybody did want any info, do get in contact me. This is another great side of user groups, you can go to one talk and ask other topic questions that may come to your mind.
(Day 2) – It was a long night, and it was so amazing to see so many people the next day for Neil’s @chandlerDBA talk on ‘Why has my plan changed’. Being a man with many hats at my work and being a DBA for over 17 years, I enjoyed Neil’s talk as I am always fighting SQL performance looking forward to see his scripts. I would definitely recommend it, if you are at a different conference and he is talking – don’t miss it
Once we had consumed our lunch we had our final session of the day ‘#DBADEV,Bridging the gap between development and operation table’ This was a panel session with Sabine @oraesque, Martin @MDWidlake, Philippe @pfierens, Neil @ChandlerDBA, Piet @pdevisser and Erik @evrocs_nl. I think most people would be able to say something in this area, I am always fighting with developers