RE: Reporting database
Hi Faan, Thanks Faan for this effectively critical point. Like you I have faced this problem after applying some patch (FND patches on 11.5.7) and since I have standby database as emergency environment. It was big problem. I wrote a scripts to detect such events and I read some metalink docs that said Oracle will leave these unrecoverable options on their future patches. Have nice day, Kader --- Faan DeSwardt [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kader Ben INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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
RE: reporting database
Sai - All data in the database? How large, how active is the source database? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, November 17, 2003 4:10 PM To: Multiple recipients of list ORACLE-L hi data movement for a reporting database from a OLTP intensive db is what i am interested in. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Reporting database with Time Finder
Sonja, that is a very interesting question. I have tested the source/target scenario under Digital Unix. We did come across a command that allowed you to mount the devices with the same name on the same server with a mount /ignore(?). There may well be a ruse to rename the whole battle ship. It becomes very tricky when you wish to mount the target database and the reporting db on the same server. Already the database will have the same id as PROD unless you are going to rename it and all your physical files. So to mount it once is fine on the same server. You would IMHO save a lot of complexity by mounting it on another server. Yes I know its extra cost For a penny. Peter -Message d'origine- De : Sonja ehovic [mailto:[EMAIL PROTECTED]] Envoy : mercredi 16 janvier 2002 16:57 A : Multiple recipients of list ORACLE-L Objet : Reporting database with Time Finder Hi! Oracle 8.1.7 on AIX, on EMC discs boxes which are synchronized with SRDF. At the target site, we have BCV volumes. We want to open reporting database on a daily basis. Now we can open target database with the same AIX volume group as the primary database. My question is do you have experience in renaming AIX volume group, so we can have two databases on the same machine (SRDF target and BCV)? We have some experience in doing that manually, but we're interested in having some scripts. Please help. TIA, Sonja -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?ISO-8859-2?Q?Sonja_=A9ehovi=E6?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
Re: Reporting database
Tom, One of the big advantages of a "standby" database is that it can be opened in the "read only" mode just for the purpose of a report type database. Be sure to check all of your options before you make the final decision. The report database and the OLTP database could/should have different INIT.ORA parameters to have the database operate at peak effeciency. ROR mm [EMAIL PROTECTED] 02/06/01 07:25AM How are you all creating reporting databases? We currently have an OLTP database and we wish to create a reporting database from it. As I see it, we have the following options: 1. Create the reporting database as a Standby database. I don't think that this will work since the database must be up and not in standby mode. 2. Use Oracle replication. I have heard it is cumbersome and has trouble keeping up with lots of transactions. 3. Snapshots/materialized views. 4. Beef up our current machine so that it can handle OLTP transactions and reports. 5. Since we are using a BMC disk array, we could break the mirror periodically and mount the disks on a new machine. 6. Other 3rd party replication products. 7. We could probably use some type of import/exports. How are the rest of you doing it? Are there any other options that I forgot? Thanks, Tom -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Terrian, Thomas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
Re: Reporting database
You can use the standby database in read only mode if you are going to do reporting only. --- "Terrian, Thomas" [EMAIL PROTECTED] wrote: How are you all creating reporting databases? We currently have an OLTP database and we wish to create a reporting database from it. As I see it, we have the following options: 1. Create the reporting database as a Standby database. I don't think that this will work since the database must be up and not in standby mode. 2. Use Oracle replication. I have heard it is cumbersome and has trouble keeping up with lots of transactions. 3. Snapshots/materialized views. 4. Beef up our current machine so that it can handle OLTP transactions and reports. 5. Since we are using a BMC disk array, we could break the mirror periodically and mount the disks on a new machine. 6. Other 3rd party replication products. 7. We could probably use some type of import/exports. How are the rest of you doing it? Are there any other options that I forgot? Thanks, Tom -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Terrian, Thomas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). __ Do You Yahoo!? Yahoo! Auctions - Buy the things you want at great prices. http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Oracle Apps INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: Reporting database
If your production database can afford a short downtime. You can copy all the datafiles and rename the database. We did this way. Refreshed the database everyday. 15GB database needs 15 min. downtime on the production database. Depends on your system I guess. Or using hot backup recover to another machine rename the database. Of course, all those methods are not real time approach. We don't care a one day delayed data on reporting purpose. Thanks, Joan -Original Message- Sent: Tuesday, February 06, 2001 9:00 AM To: Multiple recipients of list ORACLE-L Tom, One of the big advantages of a "standby" database is that it can be opened in the "read only" mode just for the purpose of a report type database. Be sure to check all of your options before you make the final decision. The report database and the OLTP database could/should have different INIT.ORA parameters to have the database operate at peak effeciency. ROR mm [EMAIL PROTECTED] 02/06/01 07:25AM How are you all creating reporting databases? We currently have an OLTP database and we wish to create a reporting database from it. As I see it, we have the following options: 1. Create the reporting database as a Standby database. I don't think that this will work since the database must be up and not in standby mode. 2. Use Oracle replication. I have heard it is cumbersome and has trouble keeping up with lots of transactions. 3. Snapshots/materialized views. 4. Beef up our current machine so that it can handle OLTP transactions and reports. 5. Since we are using a BMC disk array, we could break the mirror periodically and mount the disks on a new machine. 6. Other 3rd party replication products. 7. We could probably use some type of import/exports. How are the rest of you doing it? Are there any other options that I forgot? Thanks, Tom -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Terrian, Thomas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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.com -- Author: Joan Hsieh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: Reporting database
You don't need replication. You can simply create tables on one machine across a db link fom the first. Depends on the volume and type of data. We (only a few Gb) rebuild our reporting environment from transaction. Reporting doesn't have ALL the tables from OLTP, just the ones we need, somewhat denormalized, summarized, etc. G'luck. Yosi -Original Message- From: Terrian, Thomas [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 06, 2001 7:26 AM To: Multiple recipients of list ORACLE-L Subject: Reporting database How are you all creating reporting databases? We currently have an OLTP database and we wish to create a reporting database from it. As I see it, we have the following options: 1. Create the reporting database as a Standby database. I don't think that this will work since the database must be up and not in standby mode. 2. Use Oracle replication. I have heard it is cumbersome and has trouble keeping up with lots of transactions. 3. Snapshots/materialized views. 4. Beef up our current machine so that it can handle OLTP transactions and reports. 5. Since we are using a BMC disk array, we could break the mirror periodically and mount the disks on a new machine. 6. Other 3rd party replication products. 7. We could probably use some type of import/exports. How are the rest of you doing it? Are there any other options that I forgot? Thanks, Tom -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Terrian, Thomas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
Re: Reporting database
A large client that I worked with last year had a very high volume OLTP system and they implemented Quest's SharePlex for a reporting database. It worked well for them. Oracle's current method of replication could never have approached the speed they needed and it would have added overhead to an already overburdened production system. SharePlex reads from log files. You can control what tables are replicated and it's advisable to only replicate the ones you need to ensure that your reporting instance keeps up with the changes to the production database. I believe that Oracle9i will have a similar option to replicate based on log files. Also, I would assume with 9i's clusters, you could designate one or more clusters as reporting instances and others as OLTP instances. Marc Perkowitz MTP Systems Consulting In a message dated 2/6/2001 6:55:17 AM Central Standard Time, [EMAIL PROTECTED] writes: How are you all creating reporting databases? We currently have an OLTP database and we wish to create a reporting database from it. As I see it, we have the following options: 1. Create the reporting database as a Standby database. I don't think that this will work since the database must be up and not in standby mode. 2. Use Oracle replication. I have heard it is cumbersome and has trouble keeping up with lots of transactions. 3. Snapshots/materialized views. 4. Beef up our current machine so that it can handle OLTP transactions and reports. 5. Since we are using a BMC disk array, we could break the mirror periodically and mount the disks on a new machine. 6. Other 3rd party replication products. 7. We could probably use some type of import/exports. How are the rest of you doing it? Are there any other options that I forgot? Thanks, Tom -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).