Oracle E-Business Suite Technology

Steve,

This is a great article, thanks a lot. I asked you this question sometime back, so it was nice to see an article on this.

However, a lot of typical EBS environments have a lot of reporting needs (in our case, we have Discoverer, Oracle 10g reports, Brio etc). These application are pure read-only and do account for over 50% of our system load. The new active data guard feature of 11g might be of great benefit to an environment like ours. Do you think you should jst clarify this for everybody's sake?

Thanks.
Naveen Garg

Hi, Naveen,

As I noted above:

This also applies to the use of external reporting tools like Oracle Discoverer. Discoverer users must log into the E-Business Suite instance, which checks their assigned responsibilities to prevent users from running reports against data that they're unauthorized to view.

This also applies to Oracle Reports, too. Both Discoverer and Oracle Reports require an E-Business Suite environment that can create an ICX session before generating a report. You cannot run Discoverer or Oracle Reports against a read-only EBS database.

I'm afraid that I can't comment authoritatively on how Brio works. I would speculate that Brio has the same limitations as any other reporting tool. You may wish to check with Brio Support on whether their products are compatible with Active Data Guard databases.

Regards,
Steven

Steven,

Thanks for this post. Although it's been nice to surprise my clients with this solution, I'm glad to see it's got the blessing from Oracle Support and will hopefully gain wide wide acceptance :D

Steven,

So if I understand this note correctly, neither 10g nor 11g data guard features will allow 'native' Oracle E-Business type reports (XML, Discoverer, 10gReports) to run against the "standby" environment. Correct?

I understand the scale up approaches but having a significant amount of hardware in a DR site for standby does not maximize the use of all available hardware. With 11g and Active Data Guard, however, couldn't we develop queries/reports using non-EBusiness tools like OBIEE or BI Publisher and treat the E-Business 11g standby database as the source?

thx,
John

Hi, Jay,

"Blessing from Oracle Support"? I suppose so, since we establish the base certifications here in Oracle Development.

If there are any conflicts between the response you're getting from Oracle Support and the guidance that we publish here in Oracle Development, please feel free to let me know.

Regards,
Steven

John,

I appreciate the concerns about unused capacity in the DR instance.

We need to be careful about the product references. "Oracle Data Guard" supports the use of "native" reporting tools against standby environments, since those environments allow read-write access.

"Oracle Active Data Guard" creates read-only environments, which prevent the use of "native" reporting tools with E-Business Suite databases created by this technology.

If you use "Oracle Data Guard" (and not "Oracle Active Data Guard") for your EBS disaster recovery instance, then the DR site can be used for reporting purposes.

Regards,
Steven

Hi Steven,

Could you please elaborate on your comment:
If you use "Oracle Data Guard" (and not "Oracle Active Data Guard") for your EBS disaster recovery instance, then the DR site can be used for reporting purposes.
As in EBS environment we can only use physical standby. The database is in a mount mode and it is not open, so how could it be used for reporting purposes.
Thanks, Sandra

Hi, Sandra,

You're quite right, and I didn't mean to mislead readers with potentially-confusing statements. The disaster recovery database can only be used for reporting purposes if it's opened, i.e. you've got a fully functioning environment that allows end-users to access it via middle-tier reporting tools like Discoverer that have been configured to read from the DR database rather than the production database.

Regards,
Steven

Since RDBMS 6.0.33 and the pre-Oracle product customer built standby databases that rely only on the fully supported recovery model, it has been possible to cancel recovery (usually at a carefully chosen and consistent point in the business cycle such as the completion of the generation of receivables), shut down the standby, copy the standby database, and startup rename the copy of standby with reset logs. After the rename is complete the un-renamed standby may then be resumed. The frozen in time copy of standby may then be opened as the new database it is, and after appropriate surgery to the outstanding jobs tables (excising processing jobs, restoring saved pending report jobs from the previous cycle exported for the purpose, redefining the printers used, applications may be run just fine. Negotiating licensing for this particular topology has varied widely from circa 1994 when Oracle regarded it as an interesting alternative for a handful of its very large Apps customers who were pushing the boundaries of capability of the largest SMP configurations of the time to the present, when the appearance of cost savings by using Active dataguard without the need for one or more copies to create different vintages of the database to be used for varying durations has become vogue (within the limitations Steven has carefully noted). DISK IS CHEAP! The advantages to the accounting team of having a frozen reporting database are large as long as everyone understands what it is advantageous to run on the frozen copy.The ability to use standard concurrent manager reports of course requires an open writable copy. Of course the adopter of this topology needs to understand the difference between using entirely supported Oracle technology and the ability and (lack of) obligation of Oracle Support to help you make the ins and outs of the logistics of this sort of configuration work. Before Dataguard was developed into a product, I used to present a paper titled "Getting the most from your your standby recovery server." (Circa 1995). Since the commands and even syntax have drifted since then I hate to repost it, but the methodology and process stand the test of time.

In summary, Steven is exactly correct about the impracticality of running the Ebusiness Suite against "active dataguard," but the alternative of making a copy is entirely workable and allows effective use of the horsepower left idle by the efficiency of roll forward on equipment sized to handle switchover or failover with no degradation in service.

Regards,

mwf

Our High Availability team has elaborated on my reply to Sandra about the use of Discoverer with Active Data Guard:

If the DR database is opened to be fully functional for Discoverer, it has to be opened read-write (to allow the log-in writes), and that in a Data Guard context typically means that the DR (i.e. the standby) database has been "activated" (or failed over to), at which point it ceases to be a DR database, and becomes a new primary database. In other words, a physical standby database can't be open read-write - whether in a regular Data Guard configuration, or Active Data Guard configuration, and hence Oracle Data Guard does NOT support the use of native EBS reporting tools (that require read/write access) against the standby environments.

I hope that clarifies things further; feel free to let us know if you have additional questions about this.

Regards,
Steven

Thanks Steven. All comments posted clarify reporting options available for EBS Data Guard enabled (physical standby) database.

Sandra

Hi Steven,

Can Snapshot Standby mode serve the purpose of reporting instance (with some limitation)?

Snapshot standby database is an Oracle 11g new feature, which allows the use of a physical standby database in read write mode for a short period of time (Ref. Metalink Note 443720.1).

Instead of opening 11g Physical Standby database in Read Only mode we can open it in Snapshot Standby mode during peak hours and allow user to run report on it.

Major disadvantage:
1) All local updates (including any report generated during this period) will be discarded when snapshot database is converted back to physical standby database.
2) Once the snapshot standby is activated this database diverges from its primary database over a time because redo data from the primary database is not applied. But Data from the primary database is always protected, as the redo logs are being received and applied automatically once it is converted back into a physical standby database.

Regards
Chirag

Dear Steven,

Please refer the comment made by Chirag above. Kindly let us know your thoughts.

Regards
Daminda

Chirag, Daminda,

We haven't explicitly tested Snapshot Standby mode with the E-Business Suite, but it is assumed to work without any issues. Your observations about the feature's possible disadvantages are correct.

If you encounter any issues with the use of this feature with EBS, please log a formal Service Request and drop me an email with the details. I'll ensure that our database architects in EBS Development work with Support on any critical issues.

Regards,
Steven

Hi Steven,
Is R12 using 11g redo apply as physical standby database is certified configuration? In your certification page only 10.2.0.3 and 10.2.0.4 are certifed dataguard with EBS. However, I read quite a few articles about using EBS with 11g dataguard as standby. Can you please clarify?

Hi, Kam,

I'd presume that this would work, but our team hasn't had a chance to test this configuration yet. As a result, we haven't published any formal best practices recommendations on how to implement this in an EBS environment yet.

My recommendation would be to wait until we've published some documentation for this. You're welcome to monitor or subscribe to this blog for updates.

Regards,
Steven

Hello Steve

I have couple of suggestions for e-biz suite. Am sure this will take long time to implement.

To make DR sites available for reporting for e-biz suite easiliy we can think of the following:

1. Make the logical standby database as an option. I know this is not considered because of usage of ROWIDs etc in e-biz suite. But in the long term we should make sure that this restriction is removed. If we don't plan to remove this restriction then we should promote partner technology like GoldenGate to do that job for us. Ofcourse DataGuard will not be used in that case.

2. Have a separate DB instance for user login's, roles/entitlements and audit logs. This might add some performance overhead but in a DR configuration, we can have this 'control' instance being updated via the "Real Time Apply" method of DataGuard. Oracle Streams could also be used to keep the this 'control' instance in sync with the master node.

The other database which actually contains all the application specific tables etc can then be placed under "Active Data Guard" and thus be used for reporting.

Regards

Prakash

.

Hello, Prakash,

Thanks for your suggestions. You're right: these would entail some rather significant architectural changes and would not likely be considered for either the Release 11i or 12 codelines at this point.

Nonetheless, EBS customers wishing to use Active Data Guard still have other options for doing so, as described above.

Regards,
Steven

Would the following scenario work?

The aim is to move Discoverer users from the EBS database to a standby database environment. We propose to use active standby database in the following way.

1. Create active standby database
2. Discoverer EUL remains in the production EBS database
3. The Discoverer data source is configured as the active standby database.

Hi, Dave,

Yes, this would work. Remember that the active standby database will be read-only while it's being mirrored with your production database. EBS Discoverer users need to open that database in read-write mode to get access to the Disco EUL.

You'll need to temporarily suspend the active standby process and open the standby database as a regular database to allow Discoverer users to perform their queries. Once the reporting window is done, you'll need to flip the database back into active standby mode and roll forward the transactions that took place while the mirror was suspended.

Regards,
Steven

Steve,
Curious if we can "implement" around this limitation. How about implement Read Only responsibilities in eBiz Suite that are tied to the Active Dataguard instance?

This way the authentication and hopefully all the (FND, etc..) writes happened in the regular primary database but the user switched to the Active Dataguard database by switching over to the Read Only responsibility.

Appreciate the insights.

Hi, Dhanraj,

There are no workarounds to avoid this Active Data Guard limitation right now. We're investigating options with the Active Data Guard team; I'll post more details when we get further into this research.

Regards,
Steven

We are on 11.5.10.2/10.2.0.2 and have a requirement for a DR database that can also be used as a Disco 10G reporting database. The DR database will only have logs applied everyday at midnight. People reporting against the DR database will see the data from the day before. If failover is required the company is fine that it is failing over to data that is only current up through midnight.

My plan was to open the database read/write after the logs have been applied at midnight. Just before the next midnight log apply I would flashback the database to the state it was in before opening it read/write and then let Data Guard update the logs files.

Would this scenario work out?

Thanks - Jeff

Hello, Jeff,

Your proposed approach sounds like it would be very workable. This is the general approach that our architects have brainstormed internally (for which we'd like to be able to provide whitepapers or cookbook implementation guides). I've also heard anecdotal reports from a number of other customers have taken the same approach, too.

The only practical consideration I'd add is about your current 10.2.0.2 database level. You should note that that database version is no longer in Premier Support. In fact, the 10.2.0.3 database version left Premier Support in February, 2009. So, your best bet for staying on a supported database version would be to move up to 10.2.0.4 or 11.1.0.7.

I'd be very interested in hearing about your experiences in deploying this configuration.

Regards,
Steven