Oracle maintenance commands and cronjobs
This page is for Oracle database administrators who keep an Exponential schema healthy. The ezoracle extension ships
console commands that wrap the usual Oracle maintenance, and cronjobs that run them on a schedule. This page answers:
which commands exist, what each one does, how to schedule them and where they report. Source: the "Console commands"
and "Cronjobs" sections of extension/ezoracle/INSTALL (version 2.3.7) and
9.6 Maintenance.
Contents
- How the commands behave
- The console commands
- Scripts in bin/php
- Cronjobs
- A routine for an Oracle site
- Checklist
How the commands behave
php bin/php/console ext:ezoracle:<command> [-s <siteaccess>] [options]Every command has --help, prints one PASS, WARN, FAIL or INFO line per check, and ends with exit status 1 when a
check failed, so it can guard a script. Commands that change something take --dry-run. V$ and DBA views need
SELECT_CATALOG_ROLE; without it the affected checks say so.
The console commands
| Command | What it does |
|---|---|
health | Connection, versions, character set, invalid objects, tablespaces, sessions, blocking locks, recycle bin, sequences, statistics |
gather-stats | DBMS_STATS.GATHER_SCHEMA_STATS ([StatisticsSettings]) |
recompile | DBMS_UTILITY.COMPILE_SCHEMA for invalid objects |
purge-recyclebin | PURGE RECYCLEBIN |
sequence-sync | Sequences behind their tables' ids; --fix moves them |
schema-diff | The database against share/db_schema.dba (--file=<dba>), with the upgrade SQL |
indexes | Index report; --rebuild [--index=<name>] [--online]; --monitor-on, --monitor-off, --usage |
report | --sizes (tables with index and LOB space), --top-sql (from V$SQL) |
datapump | --export=<file.dmp> and --import=<file.dmp> through DBMS_DATAPUMP into the directory object of [DataPumpSettings] Directory (default DATA_PUMP_DIR; the user needs READ and WRITE on it); --table-exists-action, --remap-schema |
ci-indexes | Linguistic indexes for OracleCaseInsensitive (--create, --drop) |
The server tools work as well: expdp <user>@<db> schemas=<user> directory=DATA_PUMP_DIR dumpfile=<file.dmp> and
impdp for the restore (Backups per engine).
Scripts in bin/php
extension/ezoracle/bin/php/ holds the scripts behind the commands and a few more:
| Script | Use |
|---|---|
ora-grant.php | Prints the grants for a new account (Setting up Oracle) |
ora-update-seqs.php | Moves sequences from a login string, like sequence-sync --fix |
mysql2oracle-schema.php, mysql2oracle-data.php | Move a MySQL site into Oracle; also ext:ezoracle:mysql2oracle-schema and -data (Moving a site to another engine) |
ora-drop-schema.php | Drops the schema's objects; destructive, read its --help first |
oracleclusterbinarypurge.php | Purges expired files of the DFS cluster when the Oracle backend is used (Clustering) |
The extension's INSTALL also describes extension/ezoracle/bin/shell/ora-initialize.sh, an older automated
initialization path. The current installers (exp:install --db=oracle, the wizard, the kickstarter) are the
documented way for a new site.
Cronjobs
The extension adds cronjob parts in its own settings/cronjob.ini.append.php:
| Part | Scripts |
|---|---|
ezoracle | statistics, recycle bin, sequences, health |
ezoraclehealth | health alone |
ezoracle_statistics, ezoracle_recyclebin, ezoracle_sequences | one job each |
Each job does nothing until it is switched on in settings/override/ezoracle.ini.append.php:
[CronjobSettings]
GatherStatistics=enabled
HealthMonitor=enabled
HealthMailReceivers[]=[email protected]
PurgeRecycleBin=enabled
SequenceCheck=enabled
SequenceFix=disabledand in the crontab, for example:
15 3 * * * cd /path/to/installation && php runcronjobs.php -q -s <admin siteaccess> ezoracle
0 * * * * cd /path/to/installation && php runcronjobs.php -q -s <admin siteaccess> ezoraclehealthReports go to var/log/ezoracle-<job>.log; the health monitor mails its report to HealthMailReceivers[] when a
check warns or fails. Use a team address, not a personal one.
A routine for an Oracle site
| When | What |
|---|---|
| Hourly | ezoraclehealth |
| Nightly | ezoracle (statistics, recycle bin, sequence check) |
| After an upgrade | schema-diff, recompile, sequence-sync |
| After an import or a restore | sequence-sync --fix --dry-run, then without --dry-run |
| Monthly | report --sizes, indexes --usage |
Checklist
- To do:
SELECT_CATALOG_ROLEgranted, sohealthandreportsee everything. - To do: Cron switches enabled in the override; both crontab lines installed.
- To do:
HealthMailReceivers[]is a team mailbox. - To do: Data Pump directory object exists and the account can read and write it.
Related pages
- Setting up Oracle with ezoracle
- Oracle: persistent connections, DRCP and tuning
- Housekeeping: cronjobs and queue tables
- Free documentation: this page is free to copy, translate and improve under the GNU FDL 1.3.
Become an Exponential Partner
Partners share their information, provide funding, contribute back and develop the products with us, and that is how Exponential grows. A partner website and dashboard are planned for mid-2027: please become a partner today to speed up our growth rate and land new features faster.