How to run the operating system script from Oracle or Not quite the traditional way to backup

Some time ago it was necessary to make a backup of the database on the RAC. The task is, in fact, quite trivial, if not for a few but: the customer is paranoid about security (not surprising and understandable), the customer does not have a DBA (well, it just so happened) and I have access only to the server console with the base data. Thus, we had to abandon the use of a beautiful and convenient Enterprise Manager and come up with banal console scripts. And this is where the snag occurred.

The standard solution is to write backup scripts, configure cron, which will call them on a schedule, and calm down on this until the base drops. But since we are dealing with a cluster of several nodes, the question arose on which of them to pull backups. At all - somehow a bit too much. On one thing - what if it is she who falls and all the rest remain in the ranks? One by one - what if it’s the one whose turn it was to make the backup that falls? On any third server - there is no extra piece of hardware for an additional database.

Then I remembered one curious thing, which appeared in version 11.2 and with which, among other things, I had long wanted to find out. Namely, external tables with the ability to pre-process the included file.
')
It is clear that the invented solution is the misuse of a curious feature. But it turned out funny.

At first - scripts (RMAN script can be any, for example let it be like this).

 /u01/app/oracle/rman_backup/backup.rmn 
 RUN { BACKUP SPFILE FORMAT '+FRA/racdb/backupset/spfile_%d_%s_%T' DATABASE INCLUDE CURRENT CONTROLFILE FORMAT '+FRA/racdb/backupset/data_%d_%s_%T' ARCHIVELOG ALL FORMAT '+FRA/racdb/backupset/arc_%d_%s_%T'; BACKUP DEVICE TYPE DISK FORMAT '/backup/data_%d_%s_%T_%U' BACKUPSET COMPLETED AFTER 'SYSDATE - 1/24'; CROSSCHECK BACKUP; CROSSCHECK BACKUPSET; CROSSCHECK ARCHIVELOG ALL; DELETE NOPROMPT EXPIRED ARCHIVELOG ALL; DELETE NOPROMPT EXPIRED BACKUP; DELETE NOPROMPT OBSOLETE; } 

 /u01/app/oracle/rman_backup/run_backup.sh #!/bin/bash /u01/app/oracle/product/11.2.0/dbhome_1/bin/rman target / cmdfile=$* 

The backup scripts (run_backup.sh and backup.rmn) should be added to all existing cluster nodes along the same path (in this example, / u01 / app / oracle / rman_backup /). And the path to the rman utility should be fully specified.

Then the interesting begins.

We provide the database with access to the directory in which the backup scripts live.

 $ sqlplus / as sysdba SQL> create or replace directory bkp_dir as '/u01/app/oracle/rman_backup/'; 

Create a user who will pull the backup scripts, and give him the necessary privileges.

 $ sqlplus / as sysdba SQL> create user bkp identified by rmanbkp; SQL> grant connect to bkp; SQL> grant create table to bkp; SQL> grant create procedure to bkp; SQL> grant create type to bkp; SQL> grant create job to bkp; SQL> grant read, write, execute on directory bkp_dir to bkp; 

We create an external table, the main meaning of which is to run a backup.

 SQL> connect bkp/rmanbkp SQL> create table bkp ( 2 line varchar2(255) 3 ) 4 organization external ( 5 type oracle_loader 6 default directory BKP_DIR 7 access parameters ( 8 records delimited by newline 9 preprocessor BKP_DIR:'run_backup.sh' 10 fields terminated by ';' ldrtrim 11 reject rows with all null fields 12 ) 13 location ('backup.rmn') 14 ) 15 reject limit unlimited; 

Then - a snooper, which will twitch on a schedule (in the second part, where the success check and notifying the DBA of the results, you can, for example, send a message to the desired address, but this is a slightly different story).

 SQL> create or replace procedure run_backup is 2 arrBackupLog backup_log; 3 4 cursor curBackupErrorsExist is 5 select 1 from dual 6 where exists ( 7 select 1 from table (arrBackupLog) 8 where column_value like 'RMAN-%' 9 ); 10 11 nDummy number(1); 12 bErrorsFound boolean; 13 begin 14 -- run weekly rman backup script 15 select line 16 bulk collect into arrBackupLog 17 from bkp; 18 -- report backup 19 open curBackupErrorsExist; 20 fetch curBackupErrorsExist into nDummy; 21 bErrorsFound := curBackupErrorsExist%found; 22 close curBackupErrorsExist; 23 24 if bErrorsFound then 25 dbms_output.put_line ('BACKUP FAILED'); 26 else 27 dbms_output.put_line ('BACKUP SUCCEEDED'); 28 end if; 29 end run_backup; 30 / 

And finally, the job, which will pull our storage.

 SQL> begin 2 dbms_scheduler.create_job ( 3 job_name => 'DAILY_BACKUP' 4 , job_type => 'STORED_PROCEDURE' 5 , job_action => 'run_backup' 6 , start_date => systimestamp 7 , repeat_interval => 'FREQ=DAILY;BYHOUR=04;BYMINUTE=0;BYSECOND=0' 8 , comments => 'Run daily RMAN backup' 9 , enabled => true 10 ); 11 end; 12 / 

Such is the thing. Oracle, it turns out, and so can.

Sources.
Oracle® Database Utilities 11g Release 2 (11.2)
Preprocess External Tables, Arup Nanda
Using the Oracle Database 11g Release 2 Preprocessor