<

Zero Data Reporting Database

Published on
11,606 Points
4,306 Views
3 Endorsements
Last Modified:
Approved
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
Author:MikeOM_DBA
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free