<

Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

Zero Data Reporting Database

Published on
11,001 Points
3,701 Views
3 Endorsements
Last Modified:
Background

In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical standby, Recovery Manager (RMAN) duplication, streams, or other such methods.

Any of these cloning scenarios require duplication of the data plus the need to perform other type of maintenance to keep the copy synchronized.

Once Oracle version 11g came along, it was the first time we could use a standby database open read-only 24/7 without having to shutdown and restart managed recovery to catch up with the primary database.

Unfortunately, the caveat of using a standby database for reporting is that many of the reporting tools require an updatable catalog or repository and/or need to create tables to record query results.

Therefore, the challenge becomes how to deploy a database for reporting purposes that does not require a copy of the data and allows the reporting tool to maintain its catalog or repository and be able to create/drop tables or other objects.

Solution: Build a zero data reporting database.

How?

The main requirement is to have a standby database open in read-only mode, preferably with real-time apply enabled.

And here are the steps:

1) Create new database, preferable on the same server as the standby.

2) Create a clone of the application user account (user only, no objects).

3) Create the following objects in this user account:

    - A database link to the standby database and connect using the schema owner to be reported on.

    - Views for each production table:
     
CREATE VIEW prod_tab1 AS
        SELECT /*+ DRIVING_SITE(prod_tab1) */ *
          FROM prod_tab1@stbydb;

Open in new window


    - Synonyms for each package/procedure/function:
     
CREATE SYNONYM prod_pkg1 FOR prod_pkg1@stbydb;

Open in new window

     
    - Other synonyms as required

4) Create whatever the reporting tool requires (e.g., a tablespace, catalog, etc.).

5) Grant all permissions necessary to the reporting tool user (schema).

Pros:

    - Easy to setup
    - No data duplication
    - Queries execute on standby database
    - No need for synchronizing schema
    - Data always up to date
    - The possibilities are endless...

Cons:

    - Reporting database is down when standby is down.
    - Perhaps limited resources on the standby (e.g., memory or processor) may hinder the execution of large queries.
    - Can't think of others now, but maybe there are...
   
Good luck!

Links:
              Creating a Physical Standby Database
              Oracle Active Data Guard option
3
Comment
Author:MikeOM_DBA
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 74

Expert Comment

by:sdstuber
The main requirement is to have a standby database open in read-only mode, preferably with real-time apply enabled.

Isn't Datag Guard/standby irrelevant to the whole discussion?

Database links are a generic solution that can be applied to any scenario where you don't have write access.
0
 
LVL 29

Author Comment

by:MikeOM_DBA
Sure, but I tried to base the article on these premises:

1) Off load reporting from production, and the best alternative would be to use a standby (if you have it), where the queries would be executed.

2) The need for the reporting tool to write to the database, either to it's repository and/or create objects.

3) Not have to duplicate the data into a special database that would require some type of synchronzation.

If you do not use a standby, and do not want the queries to execute on production, then the alternative would be opposite of #3.

And...I did successfully deploy such a database in 2008 (which is still active today). The only issues I encountered were having to copy over a few packages and procedures which for some reason did not execute using the synonym.

But thanks, I appreciate very much your comment.
0
 
LVL 29

Author Comment

by:MikeOM_DBA
PS: In my case, the reporting being off-loaded were the daily, weekly and monthly application reports from our production (aka. "mission critical") database, not any analytical stuff.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 74

Expert Comment

by:sdstuber
db links are a good idea, I did this exact scenario except for the standby piece myself.

and, while I did not have #1 on my list (my production db was still doing some of the work)
I had the additional requirement that I sometimes needed to include data that I could write to, in addition to the reporting tool itself.  In those cases, some of the reporting work would be done by my local machine.

I too copied packages locally, but that was mostly for #1,  pull data from remote, process it locally within my copies of the packages.

I tried to keep the code copies to a minimum though, so as to reduce the amount of duplicate maintenance required.

For the same reason, I didn't always use views locally, instead I simply created the synonym directly to the remote object.  That way I had fewer local structures to make sure I kept in synch.
0
 
LVL 29

Author Comment

by:MikeOM_DBA
I agree, synonyms instead of views would be the best, but sometimes when joining two remote tables it was generating too much sqlnet traffic. This went away by creating the views with the "driving_site" hint.
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
well.. it is a good article overall... i can use this scenario in our systems as well... thanks mike..
0
 

Expert Comment

by:xoxomos
I believe that would be a plus here too.  Thanks mike.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Join & Write a Comment

This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month