create procedure to create\drop restore point:
create or replace procedure sys.restore_point_proc ( g_operation in varchar2 DEFAULT 'LIST', g_rp_name in varchar2 DEFAULT NULL) as l_rp_check varchar2(20); e_rp_name_t EXCEPTION; e_rp_name_f EXCEPTION; e_not_valid_input EXCEPTION; e_rp_check EXCEPTION; begin select count(*) into l_rp_check from v$restore_point where upper(name)=upper(g_rp_name); CASE upper(g_operation) when 'CREATE' THEN if l_rp_check >0 then dbms_output.put_line ('Restore point already exists'); raise e_rp_name_t; end if; dbms_scheduler.set_job_argument_value ('RESTORE_POINT_JOB',1,'/oracle/scripts/create_restore_point.sh'); dbms_scheduler.set_job_argument_value ('RESTORE_POINT_JOB',2,g_rp_name); dbms_scheduler.run_job('RESTORE_POINT_JOB'); select count(*) into l_rp_check from v$restore_point where upper(name)=upper(g_rp_name); if l_rp_check =0 then dbms_output.put_line ('Restore point not created'); raise e_rp_check; end if; WHEN 'DELETE' THEN if l_rp_check =0 then dbms_output.put_line ('Restore point does not exists'); raise e_rp_name_f; end if; dbms_scheduler.set_job_argument_value ('RESTORE_POINT_JOB',1,'/oracle/scripts/drop_restore_point.sh'); dbms_scheduler.set_job_argument_value ('RESTORE_POINT_JOB',2,g_rp_name); dbms_scheduler.run_job('RESTORE_POINT_JOB'); select count(*) into l_rp_check from v$restore_point where upper(name)=upper(g_rp_name); if l_rp_check >0 then dbms_output.put_line ('Restore point not deleted'); raise e_rp_check; end if; WHEN 'LIST' THEN for rec in ( select name,GUARANTEE_FLASHBACK_DATABASE,TIME as time# from sys.v_$restore_point) loop dbms_output.put_line ('name: '||rec.name||' GUARANTEE:' ||rec.GUARANTEE_FLASHBACK_DATABASE||' creation time:' ||rec.time# ); end loop; ELSE RAISE e_not_valid_input; END CASE; EXCEPTION WHEN e_rp_name_t THEN RAISE_APPLICATION_ERROR(-20001,'Restore point already exist'); WHEN e_rp_name_f THEN RAISE_APPLICATION_ERROR(-20001,'Restore point does not exist'); WHEN e_not_valid_input THEN RAISE_APPLICATION_ERROR(-20001,'Input must be CREATE or DELETE'); WHEN e_rp_check THEN RAISE_APPLICATION_ERROR(-20001,'Operation with restore point did not succeed'); END;
create scheduler job to call os scripts
begin dbms_scheduler.create_job ( job_name =>'RESTORE_POINT_JOB', job_type =>'executable', job_action =>'/bin/sh', number_of_arguments=>2, auto_drop =>false, enabled =>false); end; /
create and assign credentials and pin scheduler job to first node ( where scripts placed )
exec sys.dbms_scheduler.create_credential( username => 'oracle', password => 'OS_ORACLE_PASSWORD', credential_name => '"SYS"."RP_CREDENTIAL"' ); exec DBMS_SCHEDULER.set_attribute( name => '"SYS"."RESTORE_POINT_JOB"', attribute => 'credential_name', value => '"SYS"."RP_CREDENTIAL"'); exec dbms_scheduler.set_attribute('RESTORE_POINT_JOB','instance_id',1);
drop_restore_point.sh
#!/bin/bash . /home/oracle/.bash_profile sqlplus / as sysdba << EOF drop restore point $1; exit; EOF
create_restore_point.sh
#!/bin/bash . /home/oracle/.bash_profile sqlplus / as sysdba << EOF create restore point $1 guarantee flashback database; exit; EOF
so, after all create and delete restore point looks like :
exec sys.restore_point_proc('CREATE','BEFORE_RELEASE_TEST4'); exec sys.restore_point_proc('DELETE','BEFORE_RELEASE_TEST4');