We have lots of self-built high performance Oracle 12c database servers with two quite large instances (OLTP and data warehouse) for development purposes. Our PL/SQL developers sometimes have to redefine table structures, update millions to billions of rows, create/test new indexes and so on. At the moment everytime the update scripts run and the data is changed, we have to re-import all tablespaces, recreate replication, etc. because we need to test the updates again. This procedure takes about 3-5 hours (much of it running in the background) until the developers can access the server again. In the meantime they switch to another, unchanged server to test again, which, of course, is then unusable after the update (repeat as often as needed...).
My teams tasks include recreating the databases for these exact purposes, which is annoying, to say the least.
I'm trying to come up with some alternatives and have looked into creating RMAN backups prior to the tests, ARCHIVELOG-mode, huge UNDO tablespaces or retention time, FLASHBACK RECOVERY, etc. Nothing I've come up with is exactly what I need, I think.
Mind: I've got very little DBA-experience and all of that little experience is self-taught or learning by doing.
So what I need or try to comprehend is, what techniques could I use to reset the database (or the changed tables / data) to some point in the past (maybe even five minutes ago), without having to re-import the database? I think ARCHIVELOG mode and restoring the changed tables / data might be the best way, but I'm not remotely sure.
Could someone point me in the right direction?