Need a parallel database for reporting purpose

oracleoracle-11greporting

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.

Best Answer

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.

Related Solutions

Shrinking the SYSTEM tablespace in Oracle

Query the DBA_EXTENTS view to see what objects are present towards the end of the data files. It is possible that they are not system objects, or are system objects which can be safely modified so that they are towards the beginning of the files.

However I suspect that you have a big problem here -- you may be looking at creating a new database and moving all your user objects to that.

Oracle Database – How to Move to a New Server

As a side note, rman is very fast when backing up to solid state disks. If you can get your hands on some put them in a raid you'll probably back up and restore in probably less than 30 minutes.

In either event, here's how to move the db to another host with hardly any downtime. Here's the overview:

1. do a full rman backup.

2. copy everything in the flash recovery area to the new host

3. go through the process of restoring the db to the new host.
   a. There are a handful of steps to do this.  It's not too hard.
   b. First the full backup is restored.
   c. Then each archive log is applied to the new instance.
   d. When all of the archive logs are have been applied, the db is opened.

Keep the production db up and running while you are doing steps 1,2,3a,3b. This may take a few hours if you have slow disks/network.

Once the level 0 backup is restored, apply the archive logs sitting in the flash recovery area which you copied in step 2.

At this point, all you need to do put the production db in read only mode and then copy and apply the last set of archive logs which were created after you copied the FRA. This should be very fast.

Then open the db and change clients so they now use the new db.

Best Answer

Related Solutions

Shrinking the SYSTEM tablespace in Oracle

Oracle Database – How to Move to a New Server

Related Question