Build a Reporting Database using standby and flashback
It's been a long time since I posted something. So, here's something worthwhile. Thanks to my colleague Kevin who devised this method! Requirement A reporting database to be refreshed daily from the UAT instance. The reporting database should be available during the daytime and the refresh can happen during the night. Solution 1. Initially, build the reporting database as a standby database. 2. Once the Managed Recovery process (MRP) is initiated and the standby is in sync with the primary database, cancel the MRP. SQL> alter database recover managed standby database cancel; 3. Enable flashback in the standby database. Note that the DB should be in mount state for this. SQL> startup mount force; SQL> alter database flashback on; 4. create guaranteed restore point. SQL> drop restore point before_open; SQL> create restore point before_open guarantee flashback database; 5. Activate the standby database. SQL> alter database activate standby database; SQL> start