Since it's a different db the data source connections will need changing. Then it's a matter of replacing the old data source with the new one. Hopefully the table and field names haven't changed!
It shouldn't take a complete re-write of the reports but they will require some minor editing as described above.
Thanks for the response and I have also suggested the same to my colleague. Do you have any docs or process workflow/links available for changing the db. It would be great if you can help me on this.
I do not as this is something that rarely happens...or should rarely happen!
All your colleague needs to do is create a new Tableau data source which points to fields with the (exact) same names as the Oracle data source.
Then, use Tableau's built-in "Replace Data Source" (you can google that for more info) to replace all references to Oracle with the Data Source for Redshift.
If some of the filenames don't match exactly, They will have a red ! mark next to them, and you will need to "re-point" them to the correct field in Redshift.
Very straight forward.
Thanks Toby and Russell. Your replies were helpful.