Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Reporting database suggestions

Status
Not open for further replies.

dklloyd

MIS
Mar 9, 2001
78
GB
We wish to have a reporting database to take the load off of the production database. We don't really want the overhead of replication as the figures from the previous day's close will do.
What could you suggest to be a suitable method of refreshing the reporting database with the Production data?

Thanks as always.

dklloyd
 
Sorry further to the above... I currently run Hot backups & Full/incremental exports daily. If either of those can be utilised?

Thanks again.
 

If you are running both exports on the same database, I suggest for you to stick on the hot backup only. Of course, implement also a Cold backup as the baseline backup.
 
Have you considered running a standby database? As of Oracle 8i, you can also make this a read-only database. Not only would it enhance your recovery strategy, but it would also take care of your desire for a reporting database.
The only possible downside to this solution is that you have to make the database unavailable to your users while you apply the redo logs from your production database, but if there is a window of time where your users aren't running reports, this should not pose too much of a problem for you.
The stipulation that up to 24 hours of latency is acceptable would seem to make this an almost ideal solution for you.
 
Thanks carp
sounds an interesting idea.. only a couple of questions relating to a standby a) Where can the report writers and runners store their own data from selects carried out on this database, could it be on the original database or another read-write? b) tables that have nologging set would have to be updated manually?
Scouring through the Oracle website also today I see mention of snapshot replication, would this also be a possible method, though with a few hundred tables maybe a headache to manage?
Thanks for your thoughts on this.

dklloyd
 
Yes, any data that is retrieved that you want to save would have to reside on another database; the standby database is read only. Tables with NOLOGGING will indeed not have redo records for operations to which NOLOGGING applies, so that could also be a problem.

Snapshot replication for that many tables would be, as you surmise, an extreme headache.

Would it be possible to do a nightly export of tables that have changed and import them into your reporting database?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top