If, while standing in a certain place, he thought "I have missed my chance" and then later you want to have him revisit that place and reminisce about his thoughts at the time:
He stood there where he had thought he had missed his chance.
That's grammatical but clunky in its use of the past perfect twice. Moreover, one might infer that he was wrong to have thought that he had missed his chance. He had still had a chance.
But would be better to use the simple past twice and the past perfect once?
He stood there where he thought that he had missed his chance.
No, it's not better, because "where he thought" doesn't clearly indicate that now it is a reminiscence about a prior occasion when he thought "I have missed my chance".
So what do we do? Has the English language painted us into a corner?
We include another little word:
He stood there where he once thought that he had missed his chance.
once puts the verb it modifies at one remove temporally from the context-time, so that if the context is past, the verb that once modifies happens in the far past, and if the context is present, the verb that once modifies happens in the simple past.
I once thought the earth was flat.
could be paraphrased as "I used to think the earth was flat".
Thus, your sentence:
He stood where he once thought that he had had a chance
Your context begins "He stood..." so its context is past, and once puts the thinking in the far past.
.
"had had a chance" uses HAVE in two different ways, of course, as auxiliary and as lexical verb.