Re: Logical standby database for reporting

Aloha spnm wrote:
> Hello,
>
> I am not a DBA, and I apologize in advance for my lack of knowledge in
> database administration issues.
>
> We host a database server shared by multiple clients (each have its own
> schema), and we need to be able to provide a logical standby for some
> of our clients for reporting. So each client that subscribed for
> reporting service, should get a real-time backup (10-15 minutes delay
> is okay) of just its schema.
>
> Our DBAs suggested that the logical standby through "Archive Logs +
> DataGuard + SQL Apply" cannot be controlled at schema level, and is
> controlled at the whole database instance, and suggested that we write
> our own application to identify the transactions that were modified in
> the last "n" minutes, and export those transactions. I am worried that
> the software we write can neither beat the performance nor the
> reliability of Oracle or other products (being used in the community)
> that are designed to do the similar stuff.
>
> Please advice me if there any other way my needs are addressed in
> Oracle, if not are there any other products that you could recommend?
> Obviously, we are looking at a cost effective solution.
>
> Thanks in advance,
> SP
>

Your DBA needs to read the documentation for DBMS_LOGSTDBY.SKIP procedure, and also the white paper on Metalink, "SQL Apply Best Practices".

It is definitely possible to apply SQL in a logical standby to a subset of schemas in the primary. Just as it is possible to create new schemas not in the primary database, and even materialized view logs on tables that are maintained via SQL apply.

However, given your scenario, what would be the harm of keeping all the schemas up to date, but only letting in the customers who subscribe to reporting? This way adding a new subscriber would be simple.

Finally, if logical standby somehow still doesn't fit the bill, check the use of Oracle streams for implementing a home-grown solution. As the doc says, "Streams can capture DML and DDL changes made to database objects and replicate those changes to one or more other databases." It is the underlying techology of the logical standby and the stated future direction for replication in Oracle databases (eventually replacing Advanced Replication functionality).

-Mark Bole Received on Tue Mar 22 2005 - 09:04:45 CST