Real DBA’s do it in Parallel – backing up oracle standard edition in parallel

Oh FFS not again!

Rocking up to the office for what I hoped to be a boring morning I find that yet again one of our database backups is still running from the night before. And I’m not happy

Sadly it’s all down to two things. Our sales and marketing idiots still think GDPR is a strange country just east of the Berlin Wall and still insist we collect everything from an inside leg measurement to the last STD fessed up to by our website visitors. That leads to massive databases mostly full of unusable garbage. The second is a lack of cash as not everyone can afford the stupidly high fees for oracle enterprise and so rather than spend money on IT our directors prefer “conferences”, mostly with their PA’s so thus leaving us stuck with oracle standard edition

I’d had enough so decided to have a play. Firing up my favourite VM and a search engine I vowed to make our backups on standard edition quicker and rapidly found something called the Oracle RMAN API. Essentally this is comprised of the internal PL/SQL packages that our favourite backup tool – RMAN uses behind the scenes. The catch is that it’s completely undocumented and oracle really don’t like you using it. However I do have it on good authority that this is used by various third-party backup tools.

Well I’m a big enough tool so thought I’d have a go myself. Here’s what I came up with. Sorry but this post is going to be a bit long

create or replace package rman_backup_restore authid current_user as

gv_parallel constant pls_integer := 4; 
-- Number of datafiles to back up in parallel

procedure create_backup_job(p_job_name in varchar2,
p_job_command in varchar2);

procedure backup_datafile(p_fileno in pls_integer,
p_tag in varchar2,
p_backup_file in varchar2);

procedure backup_database(p_backup_dir in varchar2);

end rman_backup_restore;

So what does it do? Well it’s actually pretty simple.

The backup_datafile procedure takes a file# from v$datafile and calls the RMAN API to back it up

The create_backup_job calls the previous procedure to create an ad-hoc dbms_scheduler job to back the datafile up in the background

The backup_database procedure puts the two together. It loops through v$datafile and creates parallel backup jobs for every datafile it finds with the number of parallel jobs limited by the package variable gv_parallel.

Perhaps it’s simpler if I post the code for the package body, sorry but there is quite a lot of it

create or replace package body rman_backup_restore as

procedure create_backup_job(p_job_name in varchar2,
p_job_command in varchar2)
is

/* Create scheduler job to run a backup/restore 
command in the background */

begin
dbms_scheduler.create_job 
( 
job_name => p_job_name, 
job_type => 'PLSQL_BLOCK', 
job_action => 'begin '||p_job_command||' end;', 
start_date => sysdate, 
enabled => TRUE, 
auto_drop => TRUE );
end create_backup_job;

procedure backup_datafile(p_fileno in pls_integer,
p_tag in varchar2,
p_backup_file in varchar2)
is

/* Backup an individual datafile */

v_device varchar2(4000);
v_set_stamp number;
v_set_count number;
v_pieceno binary_integer;
v_done boolean;
v_handle varchar2(4000);
v_comment varchar2(4000);
v_media varchar2(4000);
v_concur boolean:=FALSE;
v_archlog_failover boolean:=false;
v_deffmt binary_integer:=0; -- Don't back up to Flash Recovery Area
v_recid number;
v_stamp number;
v_tag varchar2(100);
v_docompress boolean:=true;

begin

sys.dbms_backup_restore.backupSetDataFile(set_stamp=>v_set_stamp,
set_count=>v_set_count,
nochecksum=>FALSE,
tag=>p_tag,
incremental=>FALSE,
backup_level=>0);

v_device:=sys.dbms_backup_restore.deviceAllocate; 

sys.dbms_backup_restore.backupDataFile(p_fileno);

sys.dbms_backup_restore.backupPieceCreate( 
fname=>p_backup_file,
pieceno=>v_pieceno,
done=>v_done,
handle=>v_handle,
comment=>v_comment,
media=>v_media,
concur=>v_concur,
params=>NULL,
media_pool=>NULL,
reuse=>FALSE,
archlog_failover=>v_archlog_failover,
deffmt=>v_deffmt,
recid=>v_recid,
stamp=>v_stamp,
tag=>v_tag,
docompress=>v_docompress
);

sys.dbms_backup_restore.deviceDeallocate();
sys.dbms_backup_restore.backupCancel;

end backup_datafile;

procedure backup_database(p_backup_dir in varchar2) 
is
/* Backup entire database */

v_limit pls_integer := rman_backup_restore.gv_parallel;
type t_dfile_type is table of v$datafile%ROWTYPE;
t_dfile t_dfile_type;
v_run_count number := 0;
v_done BOOLEAN := FALSE;
v_msg varchar2(2000);
v_counter number := 1;
v_backdir varchar2(512) := p_backup_dir;
v_job_sql varchar2(2000);
type t_output is table of dba_scheduler_job_run_details%ROWTYPE;
out_tab t_output;
v_start date := sysdate;
v_out pls_integer;
v_end date;


cursor c_out(p_start in date) is 
select * from dba_scheduler_job_run_details 
where actual_start_date >= p_start
and job_name like 'BACKUP%' and status = 'FAILED'
order by actual_start_date;


cursor c_datafiles is 
select * from v$datafile where status != 'OFFLINE' order by file# asc;

begin

dbms_output.put_line
('Backup Started: '||to_char(v_start,'dd-mon-YYYY HH24:MI:ss'));
dbms_output.put_line
('****************************************************************');

open c_datafiles;

loop

fetch c_datafiles bulk collect into t_dfile limit v_limit; 

v_done := FALSE;

for i in 1..t_dfile.count 
loop
v_job_sql := 'rman_backup_restore.backup_datafile(
'||t_dfile(i).file#||','||''''||
'BACKUP_'||to_char(sysdate,'DD-MON-YYYY')||''''||','||
''''||v_backdir||'DATAFILE_BACKUP_'||
to_char(sysdate,'DDMONYYYYHH24MI')||'_'||v_counter||'.bkp'||''''||');'; 
dbms_output.put_line('Backup of datafile '||t_dfile(i).file#||' Started');
rman_backup_restore.create_backup_job('BACKUP_JOB_'||v_counter,v_job_sql);

dbms_lock.sleep(5); -- Give dbms_scheduler a chance to wake up

v_counter := v_counter + 1; 
end loop;

-- Now block waiting for backup jobs to finish

while v_done = FALSE loop
select count(*) into v_run_count 
from dba_scheduler_running_jobs 
where job_name like 'BACKUP_JOB_%'; 
if v_run_count = 0 then
v_done := TRUE;
else
dbms_lock.sleep(60);
end if;
end loop; 

exit when c_datafiles%NOTFOUND;

end loop; 


close c_datafiles;

-- Do auto backup of controlfile + spfile if enabled
sys.dbms_backup_restore.doautobackup(v_out);
v_end := sysdate;


dbms_output.put_line(
'Backup Completed: '||to_char(v_end,'dd-mon-YYYY HH24:MI:ss'));
dbms_output.put_line(
'****************************************************************');

-- Report backup status

open c_out(v_start);
fetch c_out bulk collect into out_tab;

if out_tab.count != 0 then
for i in 1..out_tab.count
loop
dbms_output.put_line(out_tab(i).additional_info);
end loop;
dbms_output.put_line(' *****Backup Jobs completed with Errors*****');
else
dbms_output.put_line(' *****Backup Jobs completed successfully*****');
end if;

close c_out;

end backup_database;

end rman_backup_restore;

/

 

And Here’s what you get as the output:

SQL> set serveroutput on size 1000000;
SQL> exec rman_backup_restore.backup_database('/tmp/');
Backup Started: 20-oct-2018 00:30:21
****************************************************************
Backup of datafile 1 Started
Backup of datafile 3 Started
Backup of datafile 4 Started
Backup of datafile 5 Started
Backup of datafile 6 Started
Backup of datafile 7 Started
Backup of datafile 8 Started
Backup of datafile 9 Started
Backup of datafile 10 Started
Backup of datafile 11 Started
Backup of datafile 12 Started
Backup Completed: 20-oct-2018 00:36:22
****************************************************************
*****Backup Jobs completed successfully*****

PL/SQL procedure successfully completed.

 

So does it work? Believe it or not yes it does. I’ve tested it now on two extremes, an old clapped out 11g database and a shiny new 18c CDB. In both cases the backup gets recorded in the catalog and this buggy homebrew solution can be used to restore from, which of course is the real test

[oracle@orrible2 tmp]$ rman target /

Recovery Manager: Release 18.0.0.0.0 
- Production on Sat Oct 20 01:33:44 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1510520821)

RMAN> list backup of database;

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 62.75M DISK 00:00:25 20-OCT-18 
BP Key: 1 Status: AVAILABLE Compressed: YES Tag: BACKUP_20-OCT-2018
Piece Name: /tmp/DATAFILE_BACKUP_20OCT20180012_4.bkp
List of Datafiles in backup set 1
Container ID: 2, PDB Name: PDB$SEED
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
5 Full 1546228 29-JUL-18 NO /u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 11.13M DISK 00:00:12 20-OCT-18 
BP Key: 2 Status: AVAILABLE Compressed: YES Tag: BACKUP_20-OCT-2018
Piece Name: /tmp/DATAFILE_BACKUP_20OCT20180013_7.bkp
List of Datafiles in backup set 2
Container ID: 2, PDB Name: PDB$SEED
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name


etc.....

 

So yes it works. It’s still buggy but feel free to copy/paste or otherwise steal it. Sadly a few disclaimers

It’s pants PL/SQL. Doesn’t handle errors at all and could do with a bit of tuning but hey it’s a first attempt so don’t be too harsh

Depending on your oracle version you do have to mess about granting select or execute access on a number of dictionary views or built in packages. Ten minutes with a chainsaw and your nearest Oracle sales scumbag should be enough to figure out what you need

I haven’t yet figured out how to back up archivelogs. That needs some clever coding to work out what needs backing up or what has already been backed up

I haven’t tested this with an external RMAN catalog. This code assumes you’re using the controlfile to store backup information

You can’t use this technique for a restore. You’re still stuck with the highly crippled version of RMAN that comes with standard edition, I’ll leave it for you dear beautiful, intelligent reader to work out why

Oracle probably won’t support you. Although it doesn’t do anything that oracle don’t do themselves it does rely on a totally undocumented PLSQL API that you aren’t supposed to know about. IMHO oracle support don’t even support oracle products very well so no great loss there

Oracle 12c can and does have a similar trick of using DBMS_SCHEDULER for backups. However it still doesn’t do parallel backups on standard edition and doesn’t work across oracle versions

Nope, no warranty or support at all. Use it at your own risk

Enjoy though