Re: Top 10 DBA Do's and Don'ts anyone - Here is my list, comments

Tom,

Having a data warehouse database in NOARCHIVELOG is, like any other database, only the very last resort when ARCHIVELOG is simply not possible. Your advice results from several assumptions which may or may not be valid:

• data warehouse are "read-only"; don't worry about transaction processing
• data warehouses have lower availability expectations; the business wll not halt if it is down
• data warehouses are not "mission-critical"; the business will not halt if it is down
• we believe in "rebuild-and-reload" for DW, not "restore-and-recover", so we use NOARCHIVELOG mode

Ironically, data warehouses frequently spend enormous amounts of time performing "extraction, transformation, and load" (ETL), so they are anything but "read-only". Depending on the complexity and duration of ETL processing, uptime and availability may be real concerns. I converted a Teradata DW to Oracle in the early 90s. This DW was loaded monthly, but Teradata could not process 4 weeks of data faster than 6 weeks, so months were being skipped. This was due to frequent outages as well as poorly designed ETL processes. The ETL process averaged 3-4 weeks, but outages would extend this to 5-6 weeks...

The idea that "the business will not halt if the DW is down" is a view commonly held by organizations lacking a successful DW. Successful DW deployments frequently have ties from customer service into DW-supported systems, not to mention the irritation from on-high that accompanies a DW outage. Also, many DW systems have queries that legimately take days to complete, so daily or weekly outages are not an option.

I have worked several DW systems where NOARCHIVELOG mode and the resultant "rebuild-then-reload" recovery mechanism has been in place, instead of ARCHIVELOG mode and the resultant "restore-and-recover" recovery strategy. Each time, our eyes were wide open to the advantages and benefits as well as the disadvantages and risks. The disadvantages and risks outweigh the advantages and benefits in almost every situation. One time, the DW simply didn't have access to suffiicient tape capacity for archivelog backups (we had planned to "steal" bandwidth on the mainframe tape drives, but they shut that down right quick!), which is a problem that was corrected when project funding permitted 3 years later. Another time, the activity on the DW database would have generated an *average* of 2 Tbytes of archived redo log files every day, sometimes peaking at 3-4 Tbytes. This time, considerable tape capacity was available, but it was still far from sufficient. Despite the mission-critical nature of this DW, it remains in NOARCHIVELOG mode to this day, despite several week-long outages during "rebuild-then-reload" recovery operations...

NOARCHIVELOG mode should always be either an indication that the database is utterly unimportant or it should be the absolute last resort, the ultimate expediency, a stopgap until correction. It is never a viable option from a design or planning standpoint. Robert's "do #4" is correct.

Hope this helps...

-Tim

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Monday, February 24, 2003 8:03 AM

>
> #4 on the Do list assumes that you are an On-Line Transaction Process
> database. If you are a Decision Support database, then ARCHIVELOG is not
> needed. But, as a general rule, the world would be a better place if more
> production DBAs had their databases in ARCHIVELOG mode. #4 on the DO list
> is the same as #4 on the DON'T list (or have they got a way now to do hot
> backups without ARCHIVELOG mode?)
>
> My #1 don't is never, ever delete an OS file. Rename it, wait a week, and
> if everything is still running OK then delete the renamed file.
>
>
>
>

> Freeman Robert -
> IL <FREEMANR To: Multiple
recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> @tusc.com> cc:
> Sent by: root Subject: RE: Top 10 DBA
Do's and Don'ts anyone - Here is my list, comments
>
>
> 02/23/2003 05:23
> PM
> Please respond
> to ORACLE-L
>
>
>
>
>
>
> Here is the list of top 10 do's and don't that I came up with.
>
> #1 - Do Maintain your Expertise
> #2 - Do Use the DBMS_STATS Package to Collect Statistics
> #3 - Do Use Bind Variables
> #4 - Do Put your Production Database in ARCHIVELOG Mode
> #5 - Do Use Locally Managed Tablespaces
> #6 - Do Monitor Your Database
> #7 - Do Practice Recoveries
> #8 - Do Get Involved with User Groups and Other Resources
> #9 - Do Establish Standards and Change Control Processes
> #10 - Do Think Ahead
>
> Bonus! - Do tune to Reduce Logical IO's Not Physical IO's.
> (With regards to Cary!)
>
> Oracle Database Top 10 Don'ts
> #1 - Don't Waste Time Re-Organizing Your Databases
> #2 - Don't Use .Log or Other Common Extensions For Your Database File
Names
> #3 - Don't Leave Your Database Open To Attack
> #4 - Don't Decide Against Hot Backups
> #5 - Don't Use ASSM
> #6 - Don't Forget the 80/20 Rule
> #7 - Don't Stack Views
> #8 - Don't Be a Normalization Bigot
> #9 - Don't Forget to Document Everything
> #10 - Do Not Use Products You are Not Licensed For.
>
> Bonus!! - Do Not Assume A Good or Bad Hit Ratio Means Anything
>
> Ok, anyone wanna comment?
>
>
> Robert G. Freeman
> Technical Management Consultant
> TUSC - The Oracle Experts www.tusc.com
> 904.708.5076 Cell (It's everywhere that I am!)
> Author of several books you can find on Amazon.com!
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Freeman Robert - IL
> INET: FREEMANR_at_tusc.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).
>
>
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Thomas Day
> INET: tday6_at_csc.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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: Tim_at_SageLogix.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).