Skip to content

Create restore point without sysdba role

Create restore point without sysdba role published on Комментариев к записи Create restore point without sysdba role нет

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

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Primary Sidebar

Яндекс.Метрика