Skip to Main Content

Backup script

Example of a backup script under Linux (CentOS/RedHat).
This script exports all data 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.
Script can be run as both oracle and root users.

APEXExport requires some setup (java, environment) before you can use it properly. More info on that can be found here.

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 oracle or root:

Backup with parameter file

Variation on the script above, using a parameter file.
This script exports all data from a set of schemas, excluding a number of tables. The rest is the same as above.

Change this line: To:
Create the parameter file backup.parm:

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