Creating MV Logs in a logical standby database

[San_Ora_DBA]

I am trying to create materialized views based on a few tables in a
logical standby database.

The target database (11g R2) where the MVs will be created is a stand-
alone database.

The DB where the base tables reside is a logical standby database (11g
R2).

The requirement is to do a "FAST REFRESH" of the Materialized Views.

My questions are :

1. Can I create MV logs in the logical standby DB?
2. If the answer to question no. 1 is "Yes", do I need to do anything
different or configure the logical standby DB in a specific manner in
order to create MV logs. From what I understand, the objects in the
logical standby database are in a locked state. Is that going to be a
problem ?

Any other information that might be relevant is greatly appreciated.

Thanks in advance.

[ddf]

Comments embedded.

On Dec 9, 8:18 pm, San_Ora_DBA <sanjaydharmadhik...@gmail.com> wrote:
> I am trying to create materialized views based on a few tables in a
> logical standby database.
>
> The target database (11g R2) where the MVs will be created is a stand-
> alone database.
>
> The DB where the base tables reside is a logical standby database (11g
> R2).
>
> The requirement is to do a "FAST REFRESH" of the Materialized Views.
>

You cannot fulfill that requirement as only on-demand materialzed
views are allowed in a logical standby database:

"A key benefit of logical standby is that significant auxiliary
structures can be created to optimize the reporting workload;
structures that could have a prohibitive impact on the primary's
transactional response time. A logical standby can have its data
physically reorganized into a different storage type with different
partitioning, have many different indexes, have on-demand refresh
materialized views created and maintained, and it can be used to drive
the creation of data cubes and other OLAP data views."

> My questions are :
>
> 1. Can I create MV logs in the logical standby DB?

No.

> 2. If the answer to question no. 1 is "Yes", do I need to do anything
> different or configure the logical standby DB in a specific manner in
> order to create MV logs. From what I understand, the objects in the
> logical standby database are in a locked state. Is that going to be a
> problem ?
>

The problem is the requirement as it cannot be executed in a logical
standby database.

> Any other information that might be relevant is greatly appreciated.
>
> Thanks in advance.

David Fitzjarrell

[San_Ora_DBA]

David:

Thanks for your response.

After reading your response, I checked Oracle Dataguard Concept And
Administration Guide. In the Guide, Section 10.6.2 has this example:

SQL> CREATE MATERIALIZED VIEW IN_DEPT -
> REFRESH FAST ON COMMIT -
> ENABLE QUERY REWRITE -
> AS SELECT E.ROWID AS ERID, D.ROWID AS DRID, E.ENAME, D.DNAME -
> FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO;

I read both the sections, the section you quoted in your response and
this one (10.6.2). It was a little confusing to me. Just want to know
your thoughts on this.

Under section 10.6.2. in the guide, the MVs and the MV logs both are
created in the Logical Standby DB itself.

The way I am trying to implement it, is by creating MVs in a stand-
alone DB. The MV LOGS will be created on the table in the Logical
Standby DB.

I wasn't sure if that was possible. I don't yet have a logical standby
DB to try this out.

Any thoughts?

Sorry if this sounds confusing.

Again, thanks for your response.

[ddf]

On Dec 13, 10:12 am, San_Ora_DBA <sanjaydharmadhik...@gmail.com>
wrote:

> > David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -

Realize that some sections of the documentation pass unaltered between
releases and in some cases new functionality supercedes the
information from a prior release; this is such a case, however you
want to create a materialized view log in the standby database and
have an external materialized view benefit from that configuration.
Reading the section you cited also provides this piece of knowledge:

"Logical Standby supports the creation and maintenance of new
materialized views locally on the logical standby database in addition
to other kinds of auxiliary data structure. For example, online
transaction processing (OLTP) systems frequently use highly normalized
tables for update performance but these can lead to slower response
times for complex decision support queries. Materialized views that
denormalize the replicated data for more efficient query support on
the logical standby database can be created, as follows (connect as
user SYS before issuing these statements):

SQL> ALTER SESSION DISABLE GUARD;
...
"

The above alter session statement is critical to enabling the creation
of materialized view logs in a logical standby database.



David Fitzjarrell