The Boss Oracle DBA Reference Page
The Boss Oracle DBA Reference page.
This page is a holding area for some of the information
I've gathered over the years about Oracle Database Administration.
It is definitely a work in progress and mostly contains things i've
posted on an ad-hoc basis for someone on the Oracle-L groups.
Personal FAQs, High-level feature documents, etc:
- Oracle FAQ (my personal FAQ/howto list): last uploaded 5/17/16: actually updated continually as I get time to upload my working documents (same goes for other files here). I'm considering turning this into a Twiki, just waiting to do some research and get my linux server back online.
- Oracle DBA Administration SQL: last uploaded 5/17/16: SQL I run frequently to do all manner of Oracle DBA tasks (P&T queries, space queries, logged in users, etc).
- Oracle Version Differences: last uploaded 5/17/16: Oracle major Version notes; major functionality changes, new product additions, marketing promises in future versions; updated for 11g announcement 11/7/07 and again with EOL announcements for 10g/11gR2 release dates 7/23/10.
- 3rd Party Software packages: last uploaded 5/17/16: this originally was specific to Sybase-related products, but as I work more in Oracle I've added in more Oracle 3-rd party products.
- Good Oracle DBA Interview Questions: last uploaded 5/17/16. notes from my own interviewing, discussions w/ colleagues, and online discussions about what to ask. There are different sections more suited to PL/SQL developers, design DBAs, operational DBAs, and data warehousing specialists.
- How To Implement RMAN in your environment: My notes from doing an RMAN implementation, with scripting examples, how tos and FAQs (mostly integrated into the main FAQ now)
- RDBMS Vendor Marketshare data: An Excel spreadsheet that has DAtabase Marketshare numbers from 1997-present. Updated 8/7/07 to include all data to 2006. Oracle now at 47.1% market share, and market increased 14.2% from 05 to 06. Its a great time to be a dba :-)
Code Examples
- Oracle Null/not Null/Not in/exists example SQL: created to demonstrate how oracle handles null and not null records in certain types of where clauses.
- Desc to Extract Perl program: A perl routine I wrote that takes the cut-n-pasted output of a "desc tablename" and generates a control file, the extract sql that would select data in sql*loader format, and the ddl for the table. See below for sample input and output files:
- object_access.sql: Stand Alone proc that generates Object Access statistics for your database
- object_access_report.sql: The report used to query the table created by the above script
- Oracle Unix Startup script: Automatically start Oracle with Unix startup and attempt to bring down the Oracle server gracefully during shutdown.
Product integration notes
White Papers: these are various white papers I've collected over the years, none of which are mine. I've done my best to take these only from public sources; If you are the author of one of these and have a copyright concern, email me and I'll remove it.
Backup Recovery, Rman, Disaster Recovery
- HJRBUR (Backup and Recovery).pdf: "Backup & Recovery," Author Howard J Rogers (www.dizwell.com), date May2000. A complete reference book on Backup And Recovery, applicable to 9i and up, includes RMAN overview
- prman (RMAN overview new features).pdf: "(no title)" Author Paul Johny (dbaatcall.com), date unknown. High level RMAN overview of new 10g features. Less of a white paper than captured thoughts about the product.
- : Succeeding with RMAN.pps: "Succeeding with RMAN, "Author Tim Gorman (SageLogix), date circa-2003. RMAN architecture overview with 8i and 9i history, applicable for 9i mostly
DBA tips and tricks:
- 0.Millsap1996.08.21-VLDB.pdf: "Configuring Oracle Server for VLDB," Author Cary Millsap (working for Oracle then, now Hotsos), date Aug1996, a highly technical overview of implementing VLDBs on RAID-based disksets. Applicable from 8i onward, though some information is dated based on its age.
- 7_deadly_sins.doc: "The Seven Deadly Sins just got Worse," Author Steve Adams (ixora), date unk but probably 2000, Seven common performance mistakes developers and DBAs make, revisited upon the release of 9i. Written for 9i but applicable to 10g and beyond.
- 9i_hidden_features_mcdonald_sf_oow_03.pdf: "Oracle 9i Features (The ones they DIDN'T tell us about)," Author Connor MacDonald (DMR Consulting and www.oracledba.co.uk), Date Sept 2003, the ppt behind a presentation about 9i hidden features. Still useful for 10g and beyond.
- 9i newfeatures.doc: "Oracle 9i New Features..."
Author Howard Rogers (www.dizwell.com), dated 2002, a huge paper (119+ pages) on the features introduced in 9i. If you're on 10g its a review of now experienced features of the database.
- 9irac_config.pdf: "How to Build a $1000 RAC," Author Tom Callaghan (Bradmark Technologies, Inc), dated oct 2002; how to build a $1000 RAC using Oracle 9i real application clusters. Technical details for how to build a real-world RAC system, could be useful for anyone who needs/wants RAC experience.
- ASM-and-snapshot-solutions-for-backup.pdf: "ASM and for 3rd Party Snapshot Solutions - for Offhost backup," Authors Duane Smith and Nitin Vengurlekar (Oracle Corp), date appro mid 2007. How-to implement off-site backups with ASM disk group feature of 10g. Applicable for 10g using ASM only.
- DBA Best Practices.ppt: "Oracle DBA Best Practices," Author Dennis Williams (Lifetouch Inc), date unk but old (prob 2000), a presentation about DBAs, what they do and some high level best practices. Some 8i topics discussed but high level info applicable to any version.
- dba_checklist14.doc: Author Thomas Cox (unk employer), dated aug2000, v1.4 a quick checklist of daily, weekly and monthly DBA activities to do. Older so has 8i commands but info still applicable.
- dba checklist.pdfv1.0 of the same as above
- db_patching.pdf "Database Patching through Enterprise Manager 10g-An Illustrated Discourse," Author Suddip Datta (Oracle Corp), unk date but probably 2006. A how-to use EM in 10g environments to keep patches uptodate on your servers.
- H2369_oracle_db_10g_oracle_rac_celerra_ns_series_bp_wp_ldv.pdf: "Oracle Database 10g/Oracle RAC 10g Celerra NS Series NFS Best Practices Planning," Author EMC Inc, dated Sept06, Describes implementing Oracle 10g RAC over Linux using SANS by EMC.
- hjrdba.pdf"Database Administration," Author Howard Rogers (www.dizwell.com) dated Nov2000, an overview of basic database creation to help prepare for the 8i DBA exam. Information dated.
- load_lots_data.posting: "(no title)," Author Mark Townsend (Oracle Corp), Billy Verreynne (unk employer), dated Nov1999 . Comp.databases.oracle.server discussoin on techniques for loading lots of data.
- MAA_WP_10gASMMigration.pdf: "Oracle Database 10g Migration to Automatic Storage Management," Author Oracle Corp, dated Aug2005: A white paper from oracle describing indepth steps to migrate non-ASM databases (aka filesystem based .dbf files) to ASM.
- materialized_v.ppt: "Materialized Views," Author Willie Albino dated May2003. A ppt presentation all about Materialized Views, what they are and how they work. 9i based but relevant to 10g.
- mat_views_search.doc: "Effective search in a normalized application," Author Gints Plivna (Rix Technologies). Author's missive on the use of Materialized Views in OLTP applications to aid performance.
- metalink_40689.1_ora-01555.txt: "ORA-01555 "Snapshot too old" - Detailed Explanation," Author Oracle/Metalink, dated Sep1999. A detailed explanation of the Ora-01555 "Snapshot too old" errors.
- MythsExtPerf.doc: "Myths about Extents and Performance," Author Tim Gorman (SageLogix, Inc.) dated unk but at least Oct2003. Debunks myths regarding space management in oracle 8i and above.
- o9ir2_compression_twp.pdf: "Table Compression in oracle 9i Release 2," Author Oracle Corp, dated May2002. Overview of the table compression feature introduced in Oracle 9i. Aimed more at executives interested in Cost/Benefit analysis than DBAs.
- OracleMyths.zip: "5 Great Oracle Myths, Oracle Myths - Avoiding Bad Ideas in Oracle Management," Author Niall Litchfield (UK Audit Commission), dated mar2007. A ppt covering some of the common "myths" in the Oracle world (separating data and indexes, bchr, using one extent, etc).
- OW_General_OracleDatabase10G_Revolution_10R1_081903.pdf: "Oracle Database 10g: A Revolution in Database Technology," Andy Mendelsohn (Oracle Corp) dated oct2003 or before. A management level white paper discussing all of 10g's new features.
- SunOracle_BestPractices.pdf: "Sun/Oracle Best Practices," Bob Sneed (Sun Microsystems), Jan2001. A discussion of very technical Solaris details to be set for optimal Oracle performance.
Implementing Data Warehousing in Oracle
plsql: pl/SQL specific
Performance and Tuning: P&T
8i and older
If you have any questions or comments about this page, send email to me,
Todd Boss (tboss@bossconsulting.com). I'd love to hear updates to questions in the FAQ that I don't necessarily know the answers to or which are incomplete (search for two questions marks together "??" for such issues).