Got something that you want to share with your fellow Pipeliners? Send it in and you might see it next on the Oracle DBA Pipeline Archives page!
Email your file as an attachment to dbaarchive@quest-pipelines.com with a brief explanation of what it is.
Please keep the file size as small as possible for easier
downloading, (no files over 1MB in size). Use the archives below to better understand the type of material that we are
looking for. Thank you. |
By Tom Szekely, Gil Candia, and Danny Pham, Quest Software
This paper discusses the use of Oracle’s Redefinition Package to perform online
reorganizations in Oracle 9i and 10G databases. The paper describes the redefinition
process, the commands used to perform the redefinition online, and certain
limitations and deficiencies of this approach to perform online reorganizations. The
paper also discusses the use of Oracle’s Enterprise Manager (OEM) to automate the
redefinition/reorganization process. Finally, the paper compares the redefinition
approach with that of Quest Software’s Space Management with LiveReorg®, the
Quest Central® solution for achieving optimal space use in Oracle databases.
Format: Adobe Acrobat, 464KB
Oracle_Sp_Mgmt.pdf
By Azeem Mohamed, Quest Software
Oracle has introduced many advanced features in managing memory. One of the interesting
features is the advisory, which enables a DBA to dynamically resize SGA components and
control the PGA memory utilization at the instance level. Oracle introduced advisories for
buffer cache and shared pool sizes in SGA and on PGA target size in PGA. This paper
describes how these advisories can be used to resize SGA or PGA memory components
dynamically using the 9i Advisories screen.
Format: Adobe Acrobat, 232KB
Understanding_9i_Advisories.pdf
By Tom Morgan, Software Engineer, BlueFinger Limited
Standardization is critical to the successful achievement of quality in the system engineering
process, allowing an organization to work together in a cohesive manner. Having established
standards:
- Provides consistency across all projects
- Eliminates guesswork on the part of new team members as to how things are supposed to be
done
- Improves the maintainability of models and systems over time (i.e. less guesswork)
- Reduces long-term costs (because systems are easier to maintain)
- Promotes reusability (because objects within the Oracle Designer repository can be easily
maintained)
This document established naming conventions for BlueFinger Ltd for the logical and physical objects
as well as the constructs used in the design, development, implementation and maintenance of
Oracle systems. These standards are largely based on the functionality available within Oracle
Designer but are equally applicable to objects created using other methods and tools. The comprehensive document is
provided here as a sample to be modified for use within your own organization.
Format: Zip, 124KB (Contains MS Word Document, 536KB)
Oracle_Standards.zip
By Itzchak Rehberg
The purpose of this script is to reverse engineer the createdb.sql from a running database. It is
useful when a database has to be recreated.
(During database migrations using export/import or database reorganizations, for example.) The
output of this script ( with a few minor touch ups ) would be the only
script required to recreate the entire database minus the data, of course. This
script was originally developed by Emil Augustine, (see the
original below). Itzchak has converted it to a shell script and made some
useful enhancements to the script for Oracle 8i. He tested the script by using
it to rebuild a production database whose data dictionary had become corrupted.
The test prompted several additional modifications and fixes. Please see the
remarks in the script for details on all modifications to the original script. Format: Shell Script, 18KB
Generate_createdb.sh
By Shankar Govindan
On most sites using Oracle Applications, the common practice of an Oracle Apps
DBA is to check the Unix Process of the Internal Concurrent Manager. If you have a single instance,
then you would get just one output and that would tell you if the Internal
Concurrent Manager Process is up (though not necessarily running or active). However, in cases where
you have different releases of Oracle Apps on the same server it may be difficult to find out which
background process belongs to which Application. The purpose of this paper is to help you find out
not only if the background process is alive, but also to find out if the Internal Manager is really
active and
to find the same across multiple releases.
Format: MicroSoft Word, 47KB
CheckingICMStatus.doc
By Eyal Aronoff, Quest Software
One of the major benefits of the open systems environment is the ability to integrate third-party
packaged solutions into the enterprise automation scheme. Packaged solutions have many
attractions: they are cheap when compared with in-house development; the cost of continuous
support and enhancement is divided between all the users; and the vendor shoulders the
responsibility for keeping up with government regulations. On the other side are the in-house
solutions and the customized code. Ironically, after just one year of production support for
either an off-the-shelf package or an aging customized application, we face very similar
challenges.
Format: Adobe PDF, 47KB
tuning_packaged_apps.pdf
By Mark Gurry, Mark Gurry and Associates
This paper discusses the major issues that must be controlled to successfully manage schemas
in a complex multi-database environment.
Format: Adobe PDF, 61KB
Gurry_WP.pdf
By Gaja Krishna Vaidyanatha, Quest Software
RAID, the final frontier…these are the voyages of an Oracle DBA, who boldly dealt with mass
storage vendors and their claims, and achieved optimal I-O performance by application of
common sense. Many misconceptions surround RAID (Redundant Array of Inexpensive Disks) technology. This paper will define what
RAID is and what it is not. It will also explain how RAID works including the differences
between each RAID level.
Format: Adobe PDF, 499KB
Raid1.pdf
By Gaja Krishna Vaidyanatha, Quest Software
Oracle Performance management can be classified into 2 types - proactive and
reactive. Proactive
performance management involves designing and developing the performance architecture for
a system, during the early stages of an implementation. This involves hardware selection,
performance & capacity planning, mass storage system selection, I-O sub-system configuration
& tuning (i.e. RAID), and tailoring the various components to suite the complex needs of the
application and the Oracle database. The reactive component involves performance evaluation, troubleshooting, tuning and fine-tuning
an Oracle environment, within the boundaries of the existing hardware and performance
architecture. This paper is intended to provide a solid knowledge base for any “reactive” effort.
Format: Adobe PDF, 317KB
oracle_perf.pdf
By Chris Weiss, Chief Scientist, PureCarbon (chris@purecarbon.com)
The following article represents an efficient way of using dynamic
SQL and stored procedures for performing updates using
the EXECUTE IMMEDIATE .. USING .. statement. This
method is useful even when the bind list is not known in
advance.
Format: Microsoft Word, 34KB
DynamicSQL.doc
By Gil Asherie, Quest Software
Quest Software’s two database reorganization solutions, Space Manager™ and
LiveReorg™, provide a number of options and parameters to achieve optimal
reorganization speed in any environment. This paper reviews these settings and
discusses how to take advantage of them.
Format: Adobe PDF, 238KB
DBTuning_WP.pdf
By Melanie Kacerek, Quest Software
Database I/O contention is one of the most common problems in today's application
environments. You know you have it when:
- You dread the complaints from the user community when you start running a report
against their data during the day
- You try to postpone running reports just to avoid negative performance on your
production system
- You cringe as OLTP response times grow to unacceptable levels while running ad
hoc queries
This paper discusses some of the potential solutions to this problem with a focus on Quest Software's
SharePlex for Oracle.
Format: Adobe PDF, 45KB
Creating.pdf
By Eyal Aronoff, Quest Software
Increasing reliance on the availability of ERP environments and the
advent of “around the world, around the clock” business transactions via e-commerce
exposes organizations to a great risk. Losing access to the ERP system or the e-commerce
application for an extended period of time may cause the entire business to
collapse. This paper discusses some of the considerations you must take into account when designing a 24 x 7 application.
Format: Adobe PDF, 49KB
Building_WP.pdf
By Eyal Aronoff, Quest Software
This paper discusses three critical issues when considering replication techniques for
Oracle:
- Impact on the performance of the primary (possibly OLTP) application
- Time "criticality" and accuracy of the data on the secondary system
- The increase in complexity and management overhead with growth in both the size
and the number of the objects to replicate as well as the number of destinations
Format: Adobe PDF, 42KB
High_Speed_Replication.pdf
By Melanie Kacerek, Quest Software
Options abound to achieve high availability, scalability, and reliability. One of the more
interesting ways, the peer-to-peer configuration, gives users the ability to log into
separate systems with separate disks, to update the data on any system concurrently,
and to have an underlying technology keep all the data in sync.
Format: Adobe PDF, 44KB
PeertoPeer_Replication2.pdf
By Melanie Kacerek & Charles Zacks, Quest Software
Combining Hewlett Packard’s MC/ServiceGuard with Quest Software’s SharePlex for Oracle
offers a new level in application availability. In today’s fast-paced, e-commerce environment,
corporations cannot afford to take any chances. Just five minutes of unplanned downtime
could eliminate the profit margin. Integrating MC/ServiceGuard with SharePlex essentially
doubles your defense against disasters. This paper outlines the results of several tests, the purpose of which was to verify the reliable replication and fault tolerance of
SharePlex for Oracle in the MC/ServiceGuard environment.
Format: Adobe PDF, 170KB
Integrating_SharePlex.pdf
By Mike Ault
Taken from Mike Ault's 1997 Oracle Open World Presentation this paper will attempt to clear up the misconceptions about the Oracle DBA position, specify the levels within the DBA position and give some idea how to interview and be interviewed for Oracle DBA positions. Included in the zip file are interview questions for Oracle, DBA and Developer Candidates.
Format: zip
DBA.zip
Knowledge Xpert for Oracle (30 Day Trial Version)
Answers 70 to 80 percent of your daily Oracle administration questions with "how-to" techniques and insights on over
3,200 topics. Updated biannually with the knowledge of experts Mike Ault, Peter Corrigan, Mark
Gurry, Hugo Toledo, and other renowned authors. Includes over
200 time-saving administration scripts and utilities, plus the companion product Instant Messages.
Click Here to Download.
Knowledge Xpert for PL/SQL (30 Day Trial Version)
This product contains over 1,400 topics covering the full lifecycle
of Oracle PL/SQL development. Detailed topics are loaded with best practices
for understanding and implementing syntax, triggers, stored procedures,
DBMS_packages, and also includes compiling, testing, tuning and debugging
rules. With over 1000 reusable code examples, this customizable online
reference is designed to help you rapidly write and deploy SQL-based
and event-triggered PL/SQL code on both the client- and server-side
of Oracle applications. Click
Here to Download.
By Mike Ault
No one likes to admit it but 70% of major computer projects fail.
This failure may be due to inexperienced management, scope creep, invalid
specifications or insufficient funding. Whatever the cause if there
is a chance they will be reactivated, they must be shutdown and mothballed
properly. It is not a simple matter of pulling the plug, deleting the
code and walking away, it requires just as careful planning as a project
startup. This Word document will instruct you on how to go about the
process.
Format: zip, 8K
moth.zip
By Mike Ault
Many times we have the situation where a master table may be updated
and dependent tables are left without a link back to the master. In
this situation a cascade update option would be a nice one to have.
Unfortunately, Oracle Corp. doesn't provide this capability in a native
manner so a PL/SQL option must be developed.
Format: Microsoft Word, 22K
cascade2.doc
By Mark B. Wallace
This white paper contains information concerning Oracle Security from
a presentation delivered to the L.A. Oracle User Group. It has since
been enhanced with Oracle8 content.
Format: Microsoft Word 97, zipped
file.
oraclesec.zip
By Mike Ault
There is probably no more complex region of database management than
that of tuning. A properly tuned database shows good hit ratios (db
block buffer, library, etc.), low contention (db block, latch, lock,
redo, undo) and good query performance.
Many times to tune one area of the database, another has to take a
back seat and compromises must be made. Sometimes we can get everything
to work harmoniously together, but not often enough. Let's look at a
tuning methodology and discuss some particular areas where tuning is
important.
Format: zip
DBTune.zip
By Mike Ault
The
RevealNet Oracle Administrator package includes a number of SQL, PL/SQL
and Unix shell scripts. Chief among the SQL and PL/SQL scripts is the core
package, DBMS_REVEALNET. The DBMS_REVEALNET package has evolved over the
last few years from a loosely connected group of functions and procedures
to an integrated package of functions and procedures that can greatly
assist the DBA in the day-to-day database operations.
Format: doc
Using_DBMS_revealnet.doc
By Mike Ault
In releases of Oracle previous to ORACLE8 the most complex identifier we had to deal with was the ROWID pseudo column. While the ROWID is still present in ORACLE8 (indeed, it has been expanded) it is no longer the ultimate identifier for rows in and ORACLE8 object, the new king of the hill is the Object Identifier or
OID. In this paper Mike will present what an OID is and how they will be used in ORACLE8 for reference of rows in ORACLE8 objects.
Format: zip, 33KB
objectIDs.zip
By Mike Ault
Oracle8 has introduced many new multi-media datatypes, or LOB datatypes if you prefer, that make handling picture, video, audio or large character data sets much easier. In earlier releases before Oracle8 all multi-media types were placed into LONG RAW datatypes. With all of the limitations placed on LONG RAW by Oracle it was usually easier to keep the large object type data external to the database and just use Oracle as an indexing system to the file locations.
With Oracle8 this has all changed. Now the developer has several new LOB datatypes as well as procedures, cartridges and other developmental tools to help them cope with LOB types. Going out for the LOB has never been easier.
Format: zip, 627KB
LOB.zip
By David A. Anstey, and Mike Ault
A method, in the purely object-oriented paradigm, is the incorporation of a specific behavior assigned to an object or element. Fundamentally, it is recognized that the encapsulation of data with its associated behavior, is key to the effective modeling and implementation of complex systems. Part of the tremendous appeal of the object-oriented paradigm is this ability to encapsulate data and behavior together. Oracle's newest version of the database brings this sorely needed functionality to the object-relational paradigm. The concept behind methods is of crucial importance in the object-oriented world and will certainly prove to be equally so in implementations requiring Oracle8.
Two questions should immediately come to mind in any discussion about object functionality in Oracle. The first, most obvious one is: "How does it work"? The second, but no less important one will be: "How does this impact the way that database systems will be designed"? This paper will seek to describe the ways in which methods can be effectively incorporated into a working object-relational design.
Format: zip, 36KB
methods.zip
By Mike Ault
The database administrator's job is filled with repetitive tasks. Whether it be in the area of performance monitoring, resource usage or backup and recovery, there are many jobs that require repeating the same set of commands. Time was that you have to use operating system based tools such as cron on UNIX or the job queues on VMS to schedule any automated tasks against the database. The biggest problem with automated scripts was that many layers of error trapping had to be built in to prevent the script from carrying on even after a critical failure. Since early Oracle 7 releases the DBMS_JOB package and its associated job queues has been available for automating internal Oracle related tasks. This presentation will show how repetitive Oracle DBA tasks can be automated using PL/SQL packages, procedures and the DBMS_JOB job queues.
Format: doc, 57KB
Pres100.doc
By Mike Ault
Perhaps one of the least understood areas of Oracle Shared Global Area optimization is tuning the shared pool. The generally accepted tuning methodology involves throwing memory into the pool until the problem goes under. In this article we will examine the shared pool and define a method for tuning the shared pool that uses measurement, not guesswork to drive the tuning methodologies. In this part one of a two part series I will show how to monitor and tune the shared SQL areas of the shared pool.
Format: doc, 186KB
Diving Into the Shared Pool1.doc
By Mike Ault
This is the second part of the discussion on Shared Pools. This article will cover a discussion of what to pin and look at other areas of shared pool tuning such as multi-threaded server, hashing and the generalized tuning of the library and data dictionary cache areas.
Format: doc, 54KB
Diving Into the Shared Pool2.doc
By Mike Ault
Many times a beginning, or even an intermediate level DBA won't know what to monitor on an ongoing basis. In an attempt to make this monitoring of databases more routine and standard, Revealnet has provided a set of scripts that can be used as-is or modified to suit an individual DBAs needs. One of these script,
status.sql, groups some of the monitoring scripts into a set of daily reports.
Format: doc, 70KB
Article: eval_status.doc
Scripts: scripts.zip
By Mike Ault
Databases have evolved from being tens of megabytes to terabytes and even petabytes in size. Adding to the complexity of managing these large databases is the constraint of longer and longer required uptimes, upto and including 24X7 requirements. It can be difficult to provide proper support for large, 24X7 databases. Tuning, backup, and normal maintenance activities suddenly become impossible if the database can not be taken out of service for a moment, let alone the several days required for some maintenance activities. One possible solution to the 24X7 enigma is to use Oracle's parallel server technology. Oracle parallel server, coupled with the appropriate disk mirroring and shadowing allows for operations and administration activities to be accomplished without downtime. Parallel server also allows for application partitioning, load balancing and increase in the number of concurrent users that can access a single set of database files. Unfortunately, most parallel server implementation strategies assume you are starting with the design phase, unfortunately, many of us who wish to implement this technology have existing applications. Porting to parallel server is a challenge, hopefully this presentation will point out some of the pitfalls and make transition to parallel server easier.
Format: zip, 691KB
Pres720.zip
By Steven Feuerstein
Here's a handy Oracle8i utility that allows you to determine whether or not
two different tables are exactly the same -- in structure and in content
(data). It's good for 8i only because it relies on native dynamic SQL. You
could easily convert it to use DBMS_SQL to run in Oracle7 and Oracle8.
Perhaps a Pipeliner will do just that!
Format: zip, 2KB
tabeq.zip
By Thomas B. Cox with Christine Choi
This document gives details for performing daily, weekly, and monthly checks of the status of one
or more Oracle databases.
All SQL and PL/SQL code for the listed checks can be found in the appendix.
Format: doc, 72KB
dba_checklist14.doc
By Kurt Van Meerbeeck
Pretoria is a tool for manipulating Oracle indexfiles. Basically, Pretoria
parses the indexfile and performs a search and replace on the storage parameters.
It then separates table create statements and index create statements. These
files can then be used to pre-create all database segments, basically
reorganizing your database. Pretoria can also be used as a pretty printer (
hence the name, pretty oracle -> pretora -> pretoria ) for indexfiles. The zip file below contains the tool itself as well as a manual for using it. Kurt
has updated this tool as of July 25, 2001. He also decided to put the source
code into the public domain to facilitate its continued development. You can
track the project on SourceForge at
http://sourceforge.net/projects/pretoria/. Format: zip, 78KB
Pretoria.zip
By Emil R. Augustine
The purpose of this script is to reverse engineer the createdb.sql from a running database. It is
useful when a database had to be recreated.
(During database migrations using export/import or database reorganizations, for example.) The
output of this script ( with a few minor touch ups ) would be the only
script required to recreate the entire database minus the data, of course.
Format: SQL, 9KB
Generate_createdb.sql
By Les Smith
This script demonstrates how to use UTL_HTTP.REQUEST_PIECES to
retrieve a web page into a clob field.
The web page is saved twice to the same record. In one field the case is
preserved. In the other field the case is
all made lower to simplify searching. The lower case field is used for
searching, but the preserved case field is
used for display. Included in the package are some procedures using
DBMS_LOB
functions for searching and displaying the content.
Format: SQL, 7KB
utl_http_request_pieces.sql
For questions
or comments on this site: webmaster@quest-pipelines.com
All content Copyright
© Quest Software, Inc. All rights reserved.
|
