The opening of the game contains this. You save the USS Khitomer, and you get back to your old ship... to find out that the entire senior staff you'd been talking to earlier are all dead now and that you and the ensign you met earlier on the Khitomer are now the senior staff on board. And it's time to go fight the Borg. And you do.
Specifically, the game opens with your character's first Crowning Moment Of Awesome. In the middle of a massive starfleet battle, your character is sent over to assist the U.S.S. Khitomer. Going on a killing spree against the malfunctioning Borg on-board, you proceed to rescue the ship. Their victory is triumphantly prevented, despite your ship's command staff being executed by the Borg, you assume command and then take your vessel around to rescue the other damaged vessel's personnel. From there, you head down planetside to rescue a bunch of colonists from assimilation and then bring your ship around to join the fleet in blowing up a damaged Borg cube.
In a nice reversal of the Star Trek movie, while you're in command of your own starship at the end of the battle, you're still only promoted to a lieutenant's rank. Your vessel is also one of the smallest and weakest in Starfleet. Still, you can understand why Starfleet stands up and takes notice of your actions.
You escort a Vulcan ambassador to the monastery at P'jem. Once you arrive, the Klingons claim that the "ambassador" is in fact an Undine (aka Species 8472), and try to blow you up to get at him. Turns out... they're right. You fight the "ambassador", first on the ground, then you fight the Tethys-class dreadnought he has hidden in orbit, your officers noting with some panic that your little light cruiser doesn't have a prayer of beating it alone. You shoot down the plasma torpedos the dreadnought fires at you, trying to hold on... and then an entire fleet warps in and blows away the dreadnought. This is the second storyline mission after the tutorial.
You're sent to meet a contact who knows something about what a rogue Klingon "Ambassador" is plotting. She isn't in the Ferengi-owned bar you were supposed to meet her in, and the owner is playing dumb. How do you get him to talk? You play Odo to his Quark, and inspect his establishment for safety code violations, starting a bar brawl in the process.
You get to save the Enterprise from B'vat. Yes, thatEnterprise, and you're in the past to start with because B'vat has kidnapped thatMiral Paris. Later on in the mission, the Enterprise shows up to pay back the favor.
The Enterprise has joined the battle!
B'vat's last project before being defeated was resurrecting the Doomsday Machine to use against the Federation. His men succeed in his stead. You proceed to use nuclear torpedos powered by material from the core of a destroyed planet to destroy - yes, destroy - the Doomsday Machine by firing straight down its gullet, practically like Kirk did, all while a massive Federation fleet engages an equally massive Klingon fleet to ensure the Klingons don't stop you.
Also in the same mission, K'Valk steals a shuttle to do a Taking You with MeHeroic Sacrifice on the Doomsday Machine. While loudly singing the Klingon War Song. He fails but he does enough damage for you to be able to take it out.
"The Sisko has asked that we entrust this task to you." No spoilers, but that entire sequence, full stop.
Hell, most of the things that happen in Cardie space are awesome to varying degrees. The climax of it all is worth the price of admission to STO by itself, however.
So you're sent on what seems like a routine mission to meet another ship. You find it under attack by a Borg sphere and ride to the rescue... and then, out of nowhere, Q shows up, rewinds time, and makes you fight the Sphere again. You again defeat the Sphere... and Q rewinds time again, only now replacing the Sphere with a wing of Probes and demanding you do it one more time... with feeling! And then the three Borg cubes show up. "Intense, isn't it?" And that's just the intro to the real mission.
New Objective: Tell Q to Get On With It.
The Reman Feature Episode "Cutting The Cord" in its entirety. Your ship blasts through the Tal Shiar's planetary defenses, and you beam to the surface along with tons of Starfleet ground troops to raise some hell. Unfortunately, thanks to some local Applied Phlebotinum, your BOFFs have landed too far away to assist you. Is this a problem for you? Hell, no. You then proceed to wage an almostOne-Man Armyfight against the Tal Shiar, while calling in additional Starfleet reinforcements and orbital bombardment from your ship. This sequence culminates in you calling in an orbital strike to destroy a massive Iconian gateway...which your ship does with a full spread of Photon Torpedoes. And the mission isn't even over yet!
"Colosseum", the mission just before "Cutting the Cord", puts you in an arena and forces you to fight with melee weapons, including a Vulcan lirpa - while the famous music from "Amok Time" plays.
"Boldly they Rode", pretty much all of it. From EVA walking across the Dominion Captured Deep Space Nine station, to the final battle and the ENTERPRISE-F'S Big Damn Heroes entrance!
Denise Crosby Special Guest starring for the 3rd Anniversary as Tasha Yar. And in the "Legacy of Romulus" expansion, as Sela.
The 3rd Anniversary Mission is a direct sequel to the Next Generation Episode 'Yesterdays Enterprise'. When the Enterprise-C dived back into the portal at the end of that episode, it was actually thrown even further into the future, where it was captured by the Tholians, who rule a large portion of Federation space in the altered timeline. The player characters alternate self, here just a freighter captain, and slave to the Tholians, teams up with Tasha Yar (Voiced by Denise Crosby), steal back the Enterprise-C, and fight their way past Tholian ships to the portal to return the ship to its final battle. And when it seems the Enterprise is outgunned, you receive backup from a Federation timeship from the 29th century.
The mission has additional awesome through out. Such as seeing B'Vat or Shon decide to duel a Tholian to the death just for the off-chance of the timeline being restored (and hence never living their horrible lives) and the sequence where you board the Enterprise (seeing the starship LOOM OVER YOU as you walk down the gantry) and TAKE COMMAND OF IT, issuing Tasha Yar orders. REALLY.
Also a meta example: seeing your three most senior Bridge Officers as NPCs in the mission and watching their dejected lives can inspire some players to want to restore the timeline.
In a case of Throw It In, the fate of T'nae in the original version of the mission was left ambigious. The latest version on Tribble, preparing for Legacy of Romulus has her as the Enterprise-C Science officer to reinforce the fact she is on the Enterprise-C and that this T'nae's fate is in the air.
On the Klingon side of the game, you're sent to assist a fleet of Klingon ships in battle. When you get there, the Klingons have already been wiped out...by the Fek'Ihri, who are the Klingons' version of the legions of hell. The arc progresses with you running the Fek'Ihri off of Qo'NoS, storming your way through Klingon hell, and finally taking down the Klingons' answer to the Devil. At the end, you receive a bat'leth just like the fabled Sword of Kahless, forged by Kahless himself to honor your achievement.
Operation Gamma: going up alone against a Jem'Hadar attack ship while in a shuttlecraft, after having gone up against several wings of Jem'Hadar fighters. All this to the background of a Starfleet/KDF fleet attacking the Jem'Hadar as a distraction to let you sneak into the Gamma Wormhole.
The entirety of the Romulan PC's storyline. Literally going From Nobody to Nightmare on the Tal Shiar. It really pays off in the the Romulan version of the Cloaked Intention Series which ends with Hakeev going Sanity Slippage then getting called out by Sela, and then getting blasted in the head by the PC with the best Pre-Mortem One-Liner you can imagine for the character. It's satisfying enough as a Federation or Klingon character, watching Obisek finally defeat the man who's been hounding his people. But Klingons and Starfleet officers don't know Hakeev, not really. To a Romulan player, there is nothing more personal than this mission. Hakeev has been the Big Bad for the player's entire run; hunted them personally, destroyed their home, threatened them and their friends, and finally tortured them brutally for what could easily have been weeks, very nearly succeeding in mindraping them into a broken Tal Shiar lackey out of sheer cruelty and spite. Even Obisek acknowledges that the player has suffered more from Hakeev than he has, and stands aside to let you deal with the bastard. It's incredibly satisfying.
Romulan Player: "This is LESS than you deserve!" *POINT-BLANK HEADSHOT*
From above, the mission where you Meet with the Tal Shiar member trying to get you to join instead of just killing you. Things naturally go terribly awry, and the enemy captain orders you to stand down. When you reply with "You can't take me down with one ship.", An entire Tal Shiar FLEET warps in. The implication that you're powerful and dangerous enough to warrant such a response is a Moment Of Awesome in and of itself, but as icing on the cake, your response is to signal a fleet of your own people and the faction you chose to ally yourself to decloak around you. The ensuing clash has enough weapon fire to be called a full scale battle
How about the announcement that the Voth, space dinosaurs, are going to have their own T-Rex Mounts that shoot lasers while their riders rock Power Armor.
The announcement that Michael Dorn is returning as Worf starting in Season 8. 
Sphere of Influence: Not only do you get to team up with Ambassador Worf, near the end you get to command him in battle as a Bridge Officer, alongside Captain's Shon and Koren, and A'dranna. Yes, you're commanding one of the most well-known characters in ST, along with the captain's of the Federation + KDF's current flagships. How many would have a chance to do that?
Your enemy? At least thirty Elachi ships, mostly of high-ranking types. Opposed by yourself + the aforementioned team in a powerful Carrier ship, the Enterprise-F and Bortasqu', and every other available ship from all three factions. Then you pretty much kick their asses.
Then afterward, Worf says it was an honor to fight alongside you.
Another subtle one from the episode. The new Romulan Flagship Commander? Tiaru Jarok, the daughter of Admiral Jarok from TNG'sThe Defector who gave up his career and later his life so that she could live in a peaceful Romulus. Romulus may be gone, but she's now defending the peace her people have on New Romulus in his name.
The Free to Play trailer qualifies. Not just for being really pretty and showing off the game. But also because of the monologue reminding that while this is an era of war, they are still Starfleet officers and their first duty is still the truth.
An off-screen one for Ambassador S'tass. Managing to get the Klingon High Council to at least entertain the idea of a Gorn representative. Considering how xenophobic and oppressive the Klinks are to their vassal races, you know he's damn good at his job.
A Step Between Stars is one... but not for the player. FOR THE UNDINE AND THE ICONIANS. While trying to save Admiral Tuvok and the scientists trapped on the solar station for the Solanae Dyson Sphere, the player and Tuvok are manipulated into opening an Iconian Gateway by Dr. Cooper, who is a really an Undine in disguise. The gateway leads to the recently disappeared Jenolan Dyson Sphere when the Undine outside the sphere burst in and attack the Voth Battleship and the player's ship. The Romulan and KDF Flagships come to your rescue... but all 3 factions are fighting over who gets the Jenolan Sphere. The Federation claims since they had held it for 40 years before it changed locations, it's rightfully theirs. The Klingons argue they've possessed no territory gained from the Joint Command Operations and it's their right to claim the Jenolan Sphere. The Romulans argue since the Solanae Sphere and the gateway network that connects them is now owned by the Romulans this sphere is properly their space now. Congratulations, the Undine have successfully undermined the alliance that we've been working for the last year or so. But remember whose pulling their stings. The Iconians are the true winners of this battle and no doubt ensured that it was Jenolan that the gateway linked to. Once again, they with one single swipe have started strife across the Alpha Quadrant. The only Hope Spot is Tuvok leading a conference soon to try to resolve this issue.. but if it fails...
And much like Operation Gamma (see above), full-scale fleets are a distraction while we sneak in virtually undetected. But instead of doing so in a shuttlecraft, the player's ship gets the honor, flying dangerously close to astarand using it's damaging energies to work in your favor.
Then in the middle of the final fight:
Undine!Cooper: "You are a failure, Tuvok. The Alpha Quadrant will burn because of you.
The 2014 revamp of the First Contact Day, Day of Honor, & Republic Day missions has the player taking direct command of each factions' flagships to test some experimental anti-Undine torpedoes. At first it looks like you're just going to be doing simple target practice against deactivated frigates, but then an Undine fleet shows up and the player really gets to let loose.
The new Featured Episode for Season 9, Surface Tension, is possibly the most awesome on the list. The conference mentioned during A Sphere Between Stars convenes, and everything seems to be going all right. Of course, the Undine choose this moment to launch a colossal attack on the Alpha Quadrant, sending their huge fleet through the vulnerable gateway to attack Earth! You and the USS Voyager head for Earth Spacedock to combat a massive Undine fleet, fighting along Koren, Shon, Jarok, and Tuvok. You save Earth, and then beam aboard to clear it of Undine invaders and rescue Admiral Quinn (who, despite popular speculation, is not actually an Undine himself—no, it's just his head researcher).Then,Undine-Cooper appears to Tuvok to gloat about his inevitable victory, and Tuvok interrupts by saying that he already used the Undine's invasion of his mind to find out their plan. From there, you head to Qo'nos, where the Undine brought in the bulk of their fleet! You, in only a few ships, hold out against swarms of bioships until Shon and some reinforcements arrive. And then, the Undine bring in a fricking PLANET KILLER, forcing you to fly straight down the maw of the thing, providing cover for Shon in his Aquarius escort, all so he can ram it down its throat! With the killer destroyed, the best part comes next: Jm'pok and Quinn declare an offical end to the conflict between the Federation and the Empire!
Perhaps the most awesome bit? During the battle for Qo'nos:
You: This is Admiral < Player Character >. I am taking command of the task force. Follow my lead.
But the truly epic moment? The last 30 seconds. An ICONIAN interrupts J'mpok declaring peace with the Federation, makes some snide remarks, casually murders the Klingon High Council and then gives a simple threat to entire cast of Star Trek Online (who are all present).
Iconian: "We give you a single warning: Do not attract our attention again."
Related: Councilor Woldan's last act? Telling the Iconian to basically go f herself.
Woldan: "We are not meant for your entertainment, DEMON! We are united and together we will prevail over any-" *mind-vaped*
The implication of the Iconian showing up and making said speech. Their plan at subversion failed and the races are united. The Alpha Quadrant's response is to start that era of cooperation with an equally strong statement. She's basically there to point out that the easy way this will end is over. Now comes the hard way.