RE: Reporting database

With no APPS experience as a DBA I wonder what archive log mode can do for you with all these nologging objects. This does effectively mean that a recovery operation will fail!

I do not know the amount of transactions these objects are involved in, but you might try (and test thouroughly before putting in production) the ALTER DATABASE FORCE LOGGING command, which exactly does what is says. Even truncates will generate logging. Performance penalties are there, of course, it's all a trade-off.

Carel-Jan

At 14:49 3-12-03 -0800, you wrote:
>Kader,
>
>Just a word of caution when considering options. Keep an eye on all the
>Apps objects that have NOLOGGING set. Last time I checked there were over
>a thousand of those which were mostly indexes but there are some tables
>among them. These are a real pain when using redo log refreshing/updating
>techniques like Quest's SharePlex and Dataguard.
>
>Definitely check out Metalink notes 216212.1 and 216211.1 when considering
>and implementing this refreshing technique.
>
>Good Luck and may the Force be with you!
>
>-f
>
>-----Original Message-----
>Sent: Wednesday, December 03, 2003 1:45 PM
>To: Multiple recipients of list ORACLE-L
>
>
>At 06:24 3-12-03 -0800, you wrote:
> >Hi Listers,
> >
> >I'm about producing document to my boss about
> >different strategies to build Informational database
> >(reporting database) and ETL. Our production database
> >is 9i supporting Oracle Financials 11i.
> >
> >I'm concerned about the strategies that have a minimum
> >impact on the overload of production database.
> >
> >Could you please give me your advise and experience.
> >Any input well be very appreciated.
>
>Hi Kader,
>
>What do you mean with ETL? Is your reporting database a DWH, and are you
>considering unload from production and ETL into it? Or do you just need an
>exact copy of your production database? What frequency should it be
>updated? Daily, Weekly, real-time?
>
>For some of these options Data Guard might be a solution, for others not.
>For a daily update you can create a (physical) standby database and put it
>in read-only mode. You can query along, transactions get forwarded but not
>processed in the meantime. Every midnight, for instance, you switch the
>standby from R/O to Managed Recovery mode, and it will 'synchronise' using
>the redologs received since the last synchronisation. After synchronisation
>put it back into R/O mode, and you can query all day long. During
>synchronisation the database isn't available for reporting. Data Guard
>comes for free with your Oracle Licence. However, as discussed in an
>earlier thread on this list, you have to pay for the standby server, unless
>, AFAIK, you're paying according to the named-user-plus model.
>
>It will give you the lowest possible overhead on your production database,
>except from using non-oracle storage level options like mirroring disks and
>detach them every n hours.
>
>
>Regards, Carel-Jan
>
>-- There will allwasy be another 10 last bugs --
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Carel-Jan Engel
> INET: cjpengel.dbalert_at_xs4all.nl
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Faan DeSwardt
> INET: Faan_D1_at_VERIFONE.com
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).

Regards, Carel-Jan

• There will allwasy be another 10 last bugs --

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Carel-Jan Engel
  INET: cjpengel.dbalert_at_xs4all.nl

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).