5 Home
izzy edited this page 2 years ago

What is DBAHelper?

DBAHelper is a collection of scripts to ease the day-to-day work of an Oracle DBA. Not everything can be done automatically (otherwise we'ld lose our jobs ;) – but certain tasks re-occur in the same order very often. In these cases we like to save some keystrokes and let the scripts do the job.

What scripts are available with DBAHelper?

Shell scripts

Script Explanation
Generate_createdb.sh Reverse engineer the database creation script from a running instance
ExportOracleDB Create a full database backup for the local instance specified by ORACLE_SID using the exp utility
analobj.sh Analyze tables and/or indices of a given schema and, for tables, report concerning chained/migrated rows
idxmove.sh Move all indices from one tablespace to another
idxrebuild_all.sh Rebuild all indices for a given tablespace (or for the entire database) and adjust storage settings for faster access and less fragmentation
idxrebuild_inv.sh Rebuild all invalid indices for a given tablespace (or for the entire database)
lazywaste.sh Show resources wasted by "lazy" (long time inactive) sessions
sqltune Run an SQL Tuning Advisor Task and display results (for Oracle 10g and higher)
tabmove.sh Move all tables from a given tablespace into another tablespace
tabreorg.sh Re-organize all tables where the amount of chained/migrated rows exceeds a given treshhold and adjusts their storage settings for faster access and less fragmentation
undo_used.sh List up undo records used by current processes to identify the "eaters" (Oracle 9i and higher)
rman/rman.sh A front-end to the Oracle Recovery Manager aka RMAN to ease its use.

SQL scripts

These are plain SQL scripts located in the sql/ subdirectory. You can run them as-is, or adjust them to your needs.

Script Explanation
alertlog.sql Query the Oracle Database alert log via SQL for ORA- errors
db_simple_check.sql database QuickCheck (e.g. to run prior to and after applying a patch to see if (new) errors show up)
deallocate_unused_partitions.sql finds empty partitions and tries to de-allocate their unused extents, saving some space
users_and_privs.sql List all users with their privs recursively (i.e. resolve GRANTs given via roles). Excludes Oracle managed users (SYS, SYSTEM etc.)

Other things

There is some additional „material” available in the other sub directories:

Directory Explanation
ohctl/ Oracle Home ConTroL. Managing your Oracle Homes using Golden Images
resman/ Oracle Resource Manager. Readme contains a short description of the setup via GUI (Oracle Enterprise Manager), and also explains the scripts in the directory which can be used via e.g. SQL*Plus – to make setups reproducible and consistent, and avoid fiddling with the GUI (especially if you have no Enterprise Manager maintaining the database)
rman/ the „RMAN framework”, a console based front-end for the Oracle Recovery Manager
ts_mig/ scripts to move all objects stored in one tablespace to another (e.g. for reorganization)

Is there more Information available?

There is an article available on the RMan Framework (rman/rman.sh).