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 https://www.maketecheasier.com/sync-onedrive-linux/ 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: https://www.oracle.com/database/sqldeveloper/technologies/sqlcl/download/.
I chose /home/oracle as the installation directory; just update the script below if yours is different.

Steps:


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

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

Create the backup script "backup.sh" 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): ./backup.sh

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: https://community.oracle.com/thread/874166?start=0&tstart=0