Skip to Main Content

Backup script

Example of a backup script under Linux (CentOS/RedHat/Oracle).
This script exports all data (exluding some specific tables) from a set of schemas, exports a few Apex applications and their workspace, makes a .tar.gz file out of all these files plus the backup script file(s), and copies the resulting .tar.gz file to a remote backup location.
The script can be run as root - user oracle is probably also possible but root is easier.

Note that this script copies the export file to OneDrive, which is what I happen to use, but of course any other (network/cloud) storage option is fine.
If you want to use OneDrive, the info at is very helpful.

The script now uses SQLcl. In Apex 22.1 APEXExport has been deprecated (and later it was desupported). So while it still seems to work I have switched to SQLcl. And once again, I spent way too much time figuring out how to do that, just because Oracle (and every other website I looked at) refuses to supply a working example...

Info on downloading and installing SQLcl:
I chose /home/oracle as the installation directory; just update the script below if yours is different.


Create the parameter file "backup.parm" for expdp:

Create the parameter file "backup.sqlcl" for SQLcl:

Create the backup script "" itself:
Note that (depending on the database version you use) you may have to change the values for ORACLE_HOME & APEX_HOME, and also change the CLASSPATH path to point to another version of the ojdbc jar file.

Execute as OS user root (oracle may also work, see above): ./

Restore using database files

You can rebuild a database very easily (and fast) on a new server, by restoring its data files from the "oradata" directory. Nothing new, but I was quite amazed at how smoothly this works.

Provided you took care of these steps, restore is a breeze:
  • Your backup of the datafiles in the "oradata" directory was made with the DB shut down normally.
  • The server you are restoring to is either (a backup of) the exact same server you backed up, or a new server that has Oracle installed in the exact same way (same oracle home, instance name, etc.). Hostname, network configuration etc. can be different.
As an example, I did the following to restore an XE 11g database (from a dead server to a new virtual machine):
  • Stop the database:
    As root: /etc/init.d/oracle-xe stop
  • Move the new ("empty") database files to /tmp, just in case:
    As oracle: cd /u01/app/oracle/oradata/XE
    As oracle: mkdir /tmp/xe_new_backup
    As oracle: mv *.dbf /tmp/xe_new_backup/
  • Restore the original data files and make sure oracle:dba is the owner (make sure oracle:dba is correct for you, change to the owner of the new ("empty") data files if needed):
    As root: cd /u01/app/oracle/oradata/XE
    As root: cp /backup/oradata/XE/*.dbf .
    As root: chown oracle:dba *.dbf
  • Finalize:
    As oracle: cd /u01/app/oracle/oradata/XE
    As oracle: sqlplus sys/password as sysdba
    As oracle: SQL> startup mount
    As oracle: SQL> recover database until cancel
    As oracle: SQL> alter database open resetlogs;
  • Stop & start database:
    As root: /etc/init.d/oracle-xe stop
    As root: /etc/init.d/oracle-xe start
More info: