We are currently planning to migrate a 50 TB Oracle data warehouse to Amazon Redshift.
Data from different OLTP data sources were staged first in an Oracle staging database and then loaded into the Data Warehouse currently. Currently data has been transformed using tons of PL/SQL stored procedures within staging database as well as loading into the Data Warehouse.
OLTP Data Source 1 --> JMS (MQ) Real-time --> Oracle STG Database --> Oracle DW
Note: JMS MQ consumer writes data into staging database
OLTP Data Source 2 --> CDC Incremental Data (once in 10 mins) --> Oracle STG Database --> Oracle DW
Note: Change Data Capture on the source side data gets loaded into staging database once in 10 mins.
What would be the better framework to migrate this stack entirely (highlighted) to Amazon Redshift? What are the different components within AWS we can migrate to?
Wow, sounds like a big piece of work. There are quite a few things going on here that all need to be considered.
Your best starting point is probably AWS Database Migration Service (https://aws.amazon.com/dms/). This can do a lot of work for you in regards to converting your schemas and highlighting areas that you will have to migrate manually.
You should consider S3 to be your primary staging area. You need to land all (or almost all) the data in S3 before loading to Redshift. Give very careful consideration to how the data is laid out. In particular, I recommend that you use partitioning prefixes (s3://my_bucket/YYYYMMDDHHMI/files or s3://my_bucket/year=YYYY/month=MM/day=DD/hour=HH/minute=MI/files).
Your PL/SQL logic will not be portable to Redshift. You'll need to convert the non-SQL parts to either bash or Python and use an external tool to run the SQL parts in Redshift. I'd suggest that you start with Apache Airflow (Python) or Azkaban (bash). If you want to stay pure AWS then you can try Data Pipeline (not recommended) or wait for AWS Glue to be released (looks promising - untested).
You may be able to use Amazon Kinesis Firehose for the work that's currently done by JMS but the ideal use of Kinesis is quite different from the typical use of JMS (AFAICT).
Good luck