Simple database (schema) backup using PL/SQL

Usually, database backups are the responsibility of DBAs, and not something that developers really care or think too much about. However, if you are a lone developer, or part of a small team that doesn't have the luxury of having a dedicated DBA, then you might have to deal with backups yourself.

There are several ways to make Oracle backups; what I will be concentrating on here is the "Data Pump Export" method. You may already be familiar with the command-line "expdp" command which allows you to create a dump (.dmp) file containing your database objects (schemas, tables, procedures, etc.).

To perform a regular backup, one could create a simple batch file to run "expdp" and schedule it using the Windows Task Scheduler or a Unix Cron job. While this certainly works, it means that you have one more "moving part" to think about (the OS scheduler), and you may have to hardcode database passwords in the batch file unless you use OS authentication.

However, the "expdp" command-line utility is actually just a "front-end" to a PL/SQL package called DBMS_DATAPUMP, as can be seen in this diagram:

This means that we can call the DBMS_DATAPUMP package directly from our own PL/SQL code to create dump files, and use DBMS_JOB to schedule this backup at regular intervals. We avoid the dependence on batch files, and we don't have to expose any passwords.

The API for DBMS_DATAPUMP is flexible and, as mentioned, supports everything that can be done from the command line (both exports and imports), but it can sometimes be a bit tricky to get the (filtering) syntax correct.

I've written a wrapper package called DATAPUMP_UTIL_PKG, which you can download as part of the Alexandria PL/SQL Utility Library. This package makes it really easy to perform the most common use case: Exporting a single schema to a file on disk. This can be accomplished with the following code:



-- export current schema to file, use default file name, and make the export compatible with XE 10g
-- include a custom message


begin
  debug_pkg.debug_on;
  datapump_util_pkg.export_schema_to_file ('DEVTEST_TEMP_DIR', p_version => '10.2', p_log_message => 'it is possible to include custom messages in the log');
end;
/

To schedule this as a regular job, use the following code:

declare

  l_job number;

  l_what varchar2(4000);

begin

  l_what := 'datapump_util_pkg.export_schema_to_file(''DEVTEST_TEMP_DIR'', p_version => ''10.2'');';

  -- run job at the end of each day

  dbms_job.submit (job => l_job, what => l_what, next_date => sysdate, interval => 'trunc(sysdate + 1) + 23/24');

  commit;

end;

/

For this to work, you also need to set up a directory on the database server:

-- run as SYS

create directory devtest_temp_dir as 'c:\some_folder\backup\';

grant read, write on directory devtest_temp_dir to your_database_schema;

Obviously, since the files are written to the database server itself, you need some kind of process to move the files to another server or storage location, so the backups don't disappear along with the database if the database server itself is somehow lost or corrupted. Unlike the "hot" database files, these dump files can be safely copied by normal backup software.

Another option is to upload the backup file from the database to some online ("cloud") storage service, such as Amazon S3. I will talk about that in my next blog post...

PS. Don't forget to regularly test a restore (import) of your backup files. The only thing worse than not having a backup is having a backup that can't be restored... !