1

As our team is planning to implement BI reporting in our application, they requested a separate but a parallel db for reporting purpose in which data should be as in our production db. I read about standby database and data guard. Which is the best way for this purpose? What should I prefer? Do it cause any performance degradation to my current db?

I am using oracle 11g DB in windows environment which is in archivelog mode.

2 Answers 2

1

Data Guard Configurations

Physical standby database

Provides a physically identical copy of the primary database, with on disk database structures that are identical to the primary database on a block-for-block basis. The database schema, including indexes, are the same. A physical standby database is kept synchronized with the primary database, through Redo Apply, which recovers the redo data received from the primary database and applies the redo to the physical standby database.

As of Oracle Database 11g release 1 (11.1), a physical standby database can receive and apply redo while it is open for read-only access. A physical standby database can therefore be used concurrently for data protection and reporting.

Having a physical standby in read-only open mode while applying redo requires the Active Data Guard option.

Snapshot Standby Database

A snapshot standby database is a fully updatable standby database.

Like a physical or logical standby database, a snapshot standby database receives and archives redo data from a primary database. Unlike a physical or logical standby database, a snapshot standby database does not apply the redo data that it receives. The redo data received by a snapshot standby database is not applied until the snapshot standby is converted back into a physical standby database, after first discarding any local updates made to the snapshot standby database.

A snapshot standby database is best used in scenarios that require a temporary, updatable snapshot of a physical standby database. Note that because redo data received by a snapshot standby database is not applied until it is converted back into a physical standby, the time needed to recover from a primary database failure is directly proportional to the amount of redo data that needs to be applied.

You can convert a physical standby to a snapshot standby during the period required for running reports. Then after you can convert it to physical standby to catch up to the primary database. This does not require additional licensed options on top of EE.

1

Please research data warehousing concepts like Operational Data Store (ODS), Star Schema, OLAP and ETL, which will definetely give performance and maintenance benefits compared to a physical copy (which I assume to be in some form of 3NF)

1
  • I have to concur with this answer. If you are serious about reporting and analytics, you should design your database as such .
    – BobC
    Mar 1, 2017 at 5:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.