Seeing Adult Link for the first time. He's all grown up, finally allowed to wear pants and ready to kick some ass.
Hitting Like Likes with any sort of magical arrows. These are the things of nightmares but there is something immensely satisfying about seeing them burn to death. (Or freeze, or explode in holy light...)
The dying soldier was mortally wounded trying to stop Ganondorf from capturing Zelda. Since Zelda escapes, he succeeded.
Ocarina of Time emptied truck loads of respect for Princess Zelda upon all who played it. Especially after getting all six medallions and appearing at the Temple of Time to get the Light Arrows, where the badass Sheikah ninja Sheik unmasks to reveal none other than Zelda herself, who had spent the length of the seven year Time Skip masquerading as Sheik to hide from Ganon.
Defeating any of the bosses counts for both Link and the player, especially difficult ones like Dark Link, Morpha, and Phantom Ganon.
The first time using the Golden Gauntlets. To see Link not only lift an obelisk a hundred times his size, but send it FLYING into the air behind him, is, well... you know.
Even Navi gets a crowning moment at the start of the final boss fight. "There's no way he's going to hold me back again! This time, we fight together!"
Navi's gameplay mechanic in general is a moment of awesome: she gets right into the enemy's face all so Link can focus on the enemy in front of him, and even tells you how to beat most foes; the only ones she can't tell you about are Ganondorf and his One-Winged Angel form Ganon. Say what you will about Navi; she certainly played her part as the Hero of Time's companion. Fi must be looking at her from the Master Sword with approval right now...
Saria's moment of awesome was when she became the sage of the forest. Also, this hints that she had gotten into the chamber in the back part of the temple as well, meaning that she fought through the temple all by herself. A little, unarmed Kokiri girl and her pink fairy. Awesome. note It's implied that by becoming a sage, you have to die...still a Crowning Moment for bravery.
The Twinrova boss fight used the Mirror Shield to great effect, making the fight challenging, fun, and hilarious after you defeat Koume and Kotake and they argue as they die.
Sure, pretty much every time Link takes on Ganon/dorf is a Moment of Awesome, but in Ocarina, the final battle is truly epic: after you escape the collapsing tower, Ganondorf goes One-Winged Angel and turns into Ganon for the first time, towering over Link with twin swords that are each nearly as big as The Hero is tall. Then Ganon bellows in rage, wordlessly promising that this is going to be a Curb-Stomp Battle. That's his Crowning Moment. Then, when Link finally knocks him down, Zelda finally gets hers, using all her might as a Sage and sorceress to pin Ganon in place. Last of all, Link rips into Ganon with the Master Sword and finishes it by stabbing him through the forehead, allowing the Sages to banish Ganon. Hero of Time, no kidding.
Let's face it: Ganon was a lame last boss on the NES, but when he appeared in 3D at the end of Ocarina of Time, he finally represented the kind of Monstrosity Miyamoto imagined.
What also makes that fight intro particularly awesome is two simple things - first, Ganon is the only boss in the game with no subtitle, because he doesn't need one. This is Ganon, you don't need a description of him, the simple knowledge that this is Ganon tells you A) this is the final battle, and B) it is going to be awesome. Then as Ganon starts flailing his arms, he knocks the Master Sword out of Link's grasp and beyond the ring of fire around them, and you realize not only are you about to fight Ganon again, but you're expected to do it without the Master Sword. And then you do, proving Link really is a true hero—he doesn't need the Master Sword to kick Ganon's ass, he's picked up enough tricks and items to do it himself.
Adding onto the bit about Ganon not having boss subtitles, there's an alternate interpretation that amplifies the awesomeness a thousand fold: It isn't just that Ganon needs no description, it's that he's such a terrifying, badass monster that he's beyond any description. "Ganon" is literally the best the game can come up with.
What makes Ganon even more awesome is how genuinely monstrous he looks. In the 2d games, Ganon tends to be... pretty unimposing, being essentially somewhere beteen a Pig Man and a humanoid bulldog. Ganon in OoT, though? He's just plain monstrous. Tusks and snout and scales and hooves and lashing draconic tail all combine into something truly demonic-looking. He's hunched over nearly double, but still roughly twice your size, and carries two triple-bladed swords that are about the size of Link himself, one in either hand, as he strides across the battlefield, the earth quaking at every step.
When you regain the Master Sword while fighting Ganon. It is like a reunion between the hero and the sword as Link picks it up, looks at it, and swings it as if it feels just right in his hand. Also the fact that you MUST beat Ganon with the Master Sword and with no other weapon. Ganon will not fall to his knees until you use the Master Sword to beat him.
Also, for the player, capturing Poes on horseback is a big feat, especially if it's a gold Poe. Those back away so fast you have to be a real good shot to hit one and then very fast to get to it before the flames disappear.
Similarly, getting the Biggest Quiver. You need to fire off arrows on horseback at various targets. With each shot, you can get either 0, 30, 60, or 100 points. You have 20 arrows. You need a score of 1500. That's the equivalent of fifteen bulls-eyes, and unless your first ten shots are perfect, there is zero margin for error.
Getting Epona. You've just wasted Ingo in two straight horse races, and he agrees to give you Epona... but locks you into the ranch. What do you do? Jump the ranch walls and watch Epona do an Awesome pose. And then later on in Gerudo Valley you're confronted with a broken bridge. You jump the FREAKING CANYON.
Or even better than jumping over one of the wall fences, jump over the fence that Ingo just closed AND over him to boot and, if lucky enough, get treated to the badass horseman pose as the ultimate "screw you" to Ingo. It's even possible to summon the Cucco Revenge Squad first to go into Crazy Awesome territory.
Playing the Song of Storms at the windmill for the first time (chronologically). The usually happy guy is mad at a later timeline becouse a kid played that song and messed up the windmill badly and drained the well which had sealed an evil spirit. Turns out you go back in time and create a time loop by being that kid, playing the song he ironically taught you, and opening the most terrific (and terrifying) dungeon in the game.
The cutscene after Link gets the Master Sword, showing Ganondorf praising Link for letting him into the Sacred Realm. The scene itself isn't much (just Ganandorf standing in a white area), but what makes it awesome is the ensuing Fridge Horror: You failed. All that adventuring and hardship you faced to defeat the bosses of the three dungeons to acquire the three spiritual stones were for naught. Ganondorf won. He got the Triforce (kinda). And you might make it through the next seven years unscathed, but no one else will. Now it's time for payback.
The horrifying part isn't failing. The horrifying part is that Ganondorf could not have gotten into the Sacred Realm if you and Zelda had just kept out of it. Granted, he would still have assassinated the Hylian King, but he probably wouldn't have had the power he needed to cause quite the Bad Future he did. A true case of Nice Job Breaking It, Hero! if there ever was one.
Simply the act of ascending Gandondorf's Tower at the end of game is one of the best parts of the story. Not only is the music incredibly ominous and foreboding but it speaks volumes of Gandondorf's character. The music itself is an expertly crafted piece, showcasing the quality of Ganondorf's education and upbringing as a king and that he possesses refined tastes. Then there's the fact that the music echoes throughout the fabric of the tower, as if to broadcast to Link how arrogant and confident he is in his own victory that he can put on a little show before the curtain finally falls. The music also subtly illustrates the nature of Ganondorf's evil — it starts out soft, like a distant rumbling that is just barely audible, foreshadowing the evil that is to come. Then as Link ascends further up the tower the music becomes overwhelming in its intensity, the escalation of that chaos vibrates through the very foundation of the tower, consuming all other thoughts — chaos as profound as terrifying as Ganondorf himself. By the end of it the song has symbolically shown the player that they are in the presence of an ultimate, dastardly, perverse and all consuming-evil that one would be wise to run away from or face their inevitable, complete, and utter destruction.
The "starts soft and grows louder and more sinister" is less that Ganon's playing is changing, and more that the music is just getting louder to Link as he's climbing closer to its source. Still awesome, though.
Darunia, the wacky, Hot-Blooded leader of the Gorons running off to fistfight a fire dragon to try to buy you a little time to save his people. He knows that he doesn't have the megaton hammer, and thus, is pretty much incapable of winning, yet he hoists up his gigantic rocky balls and runs in anyway. That, friends, is a man you should be honored to call your sworn brother.
Awesome Moment for the game itself: It got into the Guinness Book of Records as the best-reviewed game of all time.
A sort of retroactive meta-moment thanks to the official Zelda timeline: The games set after Ocarina of Time fall into one of three timelines, "Child", "Adult", and "Demise". AllfourZeldagames made prior to OoT are in the Demise timeline, which follows from Link failing to defeat Ganon and dying in Ocarina of Time. All Zelda games made after OoT, except for prequels and the Oracle games, are set in either the Child or Adult timeline, both of which follow from the ending of Ocarina of Time. This means that when you beat Ocarina of Time, you are changing Hyrulean history so even if you lose, Link still wins. That's what makes him the Hero of Time.
Beating Dark Link without resorting using the Biggoron Sword or any of the other exploits that can make the fight easy is a moment of this for any player brave enough to try it.