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');
