jump to navigation

transportable tablespace export by pl/sql stored procedure August 10, 2010

Posted by sendtoshailesh in Uncategorized.
trackback

In one of my development project, we had requirement that there should be stored procedure which can perform export of a particular tablespace. Same procedure can be triggred within database. I tried certain options. Following is the source code and the highlight of procedure:

1) accept two parameter – Tablespace name & UNIX script path ( for archival)

2) create & start to write the logfile of stored proc as an logfile of tts export job.

3) change tablespace read only

4) start the datapump tts export job for the tablespace.

5) Monitor the job progress and every thing report to logfile

6) prepare the list of datafile belongs to tablespace and write in another file – rdi_prestg_datafiles.lst

7) drop the datapump job once it is done.

8) Invoke the UNIX script file as the external job in DB scheduler for immediate run
UNIX script will take following parameter:
dbname, data dir path, dumpfile , stored proc logfile, datafile list file, trigger file

Unix script and database should exist on same host.

create or replace PROCEDURE sp_tts_export
(
v_tbs_name VARCHAR2,v_unix_job_path VARCHAR2)
AS
idx         NUMBER;       — Loop index
JobHandle   NUMBER;       — Data Pump job handle
PctComplete NUMBER;       — Percentage of job complete
JobState    VARCHAR2(30); — To keep track of job state
LogEntry ku$_LogEntry;    — For WIP and error messages
JobStatus ku$_JobStatus;  — The job status from get_status
Status ku$_Status;        — The status object returned by get_status
dts           VARCHAR2(140):=TO_CHAR(sysdate,’YYYYMMDDHH24MISS’);
exp_dump_file VARCHAR2(500):=v_tbs_name||’_tts_export_’||dts||’.dmp’;
exp_log_file  VARCHAR2(500):=v_tbs_name||’_tts_export_’||dts||’.log’;
exp_job_name  VARCHAR2(500):=v_tbs_name||’_tts_export_’||dts;
unix_job_name VARCHAR2(100):=’unix_tts_job’;
trigger_file  VARCHAR2(100):=v_tbs_name||’_tts_export_’||dts||’.trg’;
dp_dir        VARCHAR2(500):=’DATA_PUMP_DIR’;
data_dir      VARCHAR2(4000):=”;
log_file UTL_FILE.FILE_TYPE;
log_filename VARCHAR2(500):=exp_job_name||’_main’||’.log’;
err_log_file UTL_FILE.FILE_TYPE;
dbf_lst     UTL_FILE.FILE_TYPE;
dbf_lst_name VARCHAR2(100):=’rdi_prestg_datafiles.lst’;
v_db_name   VARCHAR2(1000);
v_username  VARCHAR2(30);
t_dir_name  VARCHAR2(4000);
t_file_name VARCHAR2(4000);
t_sep_pos   NUMBER;
t_dir       VARCHAR2(30):=’temp_rdi’;
v_sqlerrm   VARCHAR2(4000);
stmt        VARCHAR2(4000);
v_temp      VARCHAR2(4000);
FUNCTION get_file
(
filename VARCHAR2,
dir      VARCHAR2 := ‘TEMP’)
RETURN VARCHAR2
IS
contents VARCHAR2(32767);
file BFILE := BFILENAME(dir, filename);
BEGIN
DBMS_LOB.FILEOPEN(file, DBMS_LOB.FILE_READONLY);
contents := UTL_RAW.CAST_TO_VARCHAR2( DBMS_LOB.SUBSTR(file));
DBMS_LOB.CLOSE(file);
RETURN contents;
END;
BEGIN

log_file:=UTL_FILE.FOPEN (dp_dir, log_filename, ‘w’);
UTL_FILE.PUT_LINE(log_file,’#####################################################################’);
UTL_FILE.PUT_LINE(log_file,’REPORT: GENERATED ON ‘ || SYSDATE);
UTL_FILE.PUT_LINE(log_file,’#####################################################################’);
SELECT global_name,USER INTO v_db_name,v_username FROM global_name;

UTL_FILE.PUT_LINE(log_file,’Database:’||v_db_name);
UTL_FILE.PUT_LINE(log_file,’user running the job:’||v_username);
UTL_FILE.PUT_LINE(log_file,’for tablespace:’||v_tbs_name);
UTL_FILE.PUT_LINE(log_file,’#####################################################################’);
UTL_FILE.NEW_LINE (log_file);
stmt:=’ALTER TABLESPACE ‘||v_tbs_name || ‘ read only’;

SELECT status
INTO v_temp
FROM dba_tablespaces
WHERE tablespace_name=v_tbs_name;
IF v_temp NOT LIKE ‘%READ ONLY%’ THEN
EXECUTE immediate (stmt);
UTL_FILE.PUT_LINE(log_file,’ ‘||v_tbs_name || ‘ tablespace altered to read only mode.’);
ELSE
UTL_FILE.PUT_LINE(log_file,’ ‘||v_tbs_name || ‘ tablespace already in read only mode.’);
END IF;
UTL_FILE.NEW_LINE (log_file);
UTL_FILE.PUT_LINE(log_file,’#####################################################################’);
UTL_FILE.NEW_LINE (log_file);
UTL_FILE.PUT_LINE(log_file,’ Initiating the Datapump engine for TTS export…………..’);
JobHandle := DBMS_DATAPUMP.OPEN( operation => ‘EXPORT’ ,job_mode => ‘TRANSPORTABLE’ ,remote_link => NULL ,job_name => NULL
);
UTL_FILE.PUT(log_file,’Done’);
UTL_FILE.NEW_LINE (log_file);
UTL_FILE.PUT_LINE(log_file,’ Allocating dumpfile…………….’);
DBMS_DATAPUMP.ADD_FILE( handle => JobHandle ,filename => exp_dump_file ,directory => dp_dir ,filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE
— ,filesize => ‘100M’
);
UTL_FILE.PUT(log_file,’Done’);
UTL_FILE.NEW_LINE (log_file);
UTL_FILE.PUT_LINE(log_file,’ Allocating logfile…………….’);
DBMS_DATAPUMP.ADD_FILE( handle => JobHandle ,filename => exp_log_file ,directory => dp_dir ,filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE );
UTL_FILE.PUT(log_file,’Done’);
UTL_FILE.NEW_LINE (log_file);
UTL_FILE.PUT_LINE(log_file,’ Setting attributes…………….’);
DBMS_DATAPUMP.set_parameter(handle => JobHandle, name=>’TTS_FULL_CHECK’, value=>1);
DBMS_DATAPUMP.METADATA_FILTER( handle => JobHandle ,NAME => ‘TABLESPACE_EXPR’ ,VALUE => ‘IN (”’||v_tbs_name||”’)’
);
UTL_FILE.PUT(log_file,’Done’);
UTL_FILE.NEW_LINE (log_file);
UTL_FILE.PUT_LINE(log_file,’ Now starting datapump job…………….’);
DBMS_DATAPUMP.START_JOB(JobHandle);
UTL_FILE.PUT(log_file,’Done’);
UTL_FILE.NEW_LINE (log_file);
UTL_FILE.PUT_LINE(log_file,’ Monitoring the job…………….’);
————–Monitor the job
PctComplete    := 0;
JobState       := ‘UNDEFINED’;
WHILE(JobState != ‘COMPLETED’) AND (JobState != ‘STOPPED’)
LOOP
DBMS_DATAPUMP.GET_STATUS( handle => JobHandle ,mask => 15 — DBMS_DATAPUMP.ku$_status_job_error + DBMS_DATAPUMP.ku$_status_job_status + DBMS_DATAPUMP.ku$_status_wip
,timeout => NULL ,job_state => JobState ,status => Status );
JobStatus := Status.job_status;
— Whenever the PctComplete value has changed, display it
IF JobStatus.percent_done != PctComplete THEN
DBMS_OUTPUT.PUT_LINE(‘*** Job percent done = ‘ || TO_CHAR(JobStatus.percent_done));
PctComplete := JobStatus.percent_done;
END IF;
— Whenever a work-in progress message or error message arises, display it
IF (BITAND(Status.mask,DBMS_DATAPUMP.ku$_status_wip) != 0) THEN
LogEntry                                           := Status.wip;
ELSE
IF (BITAND(Status.mask,DBMS_DATAPUMP.ku$_status_job_error) != 0) THEN
LogEntry                                                 := Status.error;
ELSE
LogEntry := NULL;
END IF;
END IF;
IF LogEntry IS NOT NULL THEN
idx       := LogEntry.FIRST;
WHILE idx IS NOT NULL
LOOP
DBMS_OUTPUT.PUT_LINE(LogEntry(idx).LogText);
idx := LogEntry.NEXT(idx);
END LOOP;
END IF;
END LOOP;
–copy the datafiles to data dump dir

UTL_FILE.PUT(log_file,’Done’);
UTL_FILE.NEW_LINE (log_file);
UTL_FILE.PUT_LINE(log_file,’ Preparing the list of datafiles and tts dumpfile to dump directory in ‘|| dbf_lst_name ||’…………….’);
dbf_lst:=utl_file.fopen(dp_dir,dbf_lst_name,’w’);
FOR rec IN
(SELECT file_name FROM sys.dba_data_files WHERE tablespace_name=v_tbs_name
)
LOOP
utl_file.put_line(dbf_lst,rec.file_name);
END LOOP;
select directory_path into v_temp
from dba_directories
where directory_name=upper(dp_dir);

if substr(v_temp,length(v_temp),1)=’/’ THEN
v_temp:= v_temp || exp_dump_file;
else
v_temp:= v_temp || ‘/’ || exp_dump_file;
end if;

utl_file.put_line(dbf_lst,v_temp);

utl_file.fclose(dbf_lst);
UTL_FILE.PUT(log_file,’Done.’);
UTL_FILE.NEW_LINE (log_file);
–Delete trigger file if any
begin
UTL_FILE.FREMOVE(dp_dir,trigger_file);
exception
when others then
UTL_FILE.PUT_LINE(log_file,’ Unable to delete any previous trigger file’);
end;

begin
DBMS_SCHEDULER.DROP_JOB(unix_job_name);
exception
when others then
UTL_FILE.PUT_LINE(log_file,’ There is not any previous job to delete.’);
end;

UTL_FILE.PUT_LINE(log_file,’ Invoking the UNIX job to archive the tts files…………….’);

dbms_scheduler.create_job(
job_name => unix_job_name,
job_type => ‘EXECUTABLE’,
job_action => v_unix_job_path,
number_of_arguments => 6
);

dbms_scheduler.set_job_argument_value (
job_name => unix_job_name,argument_position => 1,
argument_value => substr(v_db_name,1,instr(v_db_name,’.’)-1)
);

select directory_path into data_dir from dba_directories where directory_name=dp_dir;

if substr(data_dir,length(data_dir),1)=’/’ then
data_dir:=substr(data_dir,1,length(data_dir)-1);
end if;

v_temp:=’ ‘||v_unix_job_path||’ ‘||substr(v_db_name,1,instr(v_db_name,’.’)-1)||’ ‘||data_dir||’ ‘||log_filename||’ ‘||exp_dump_file||’ ‘||trigger_file||’ ‘||dbf_lst_name;
dbms_output.put_line(v_temp);

dbms_scheduler.set_job_argument_value (
job_name => unix_job_name,
argument_position => 2,
argument_value => data_dir
);

dbms_scheduler.set_job_argument_value (
job_name => unix_job_name,
argument_position => 3,
argument_value => log_filename
);

dbms_scheduler.set_job_argument_value (
job_name => unix_job_name,
argument_position => 4,
argument_value => exp_dump_file
);

dbms_scheduler.set_job_argument_value (
job_name => unix_job_name,
argument_position => 5,
argument_value => trigger_file
);

dbms_scheduler.set_job_argument_value (
job_name => unix_job_name,
argument_position => 6,
argument_value => dbf_lst_name
);

— dbms_scheduler.enable(unix_job_name);
dbms_scheduler.run_job(unix_job_name);

loop
begin
select status into v_temp from (
select status from dba_scheduler_job_run_details
where job_name=upper(unix_job_name) order by actual_start_date desc)
where rownum<=1;
exception
when no_data_found then
dbms_output.put_line(unix_job_name||’ job is not yet in dictionaries’);
end;
if (v_temp like ‘%SUCCEEDED%’) or (v_temp like ‘%FAIL%’) then exit; end if;

DBMS_LOCK.sleep(seconds => 2);

end loop;

UTL_FILE.PUT(log_file,’Done.’);
UTL_FILE.NEW_LINE (log_file);

select ‘JOB Name:’||JOB_NAME || ‘ Status:’||status || ‘ Error:’|| error# || ‘ Run Duration:’||run_duration
into v_temp from (
select JOB_NAME,status,decode(to_char(error#),’0′,’No Error’,to_char(error#)) error#,run_duration from dba_scheduler_job_run_details
where job_name=upper(unix_job_name) order by actual_start_date desc)
where rownum<=1;
UTL_FILE.PUT_LINE(log_file,v_temp);

DECLARE
ex    BOOLEAN;
flen  NUMBER;
bsize NUMBER;
BEGIN
utl_file.fgetattr(dp_dir, trigger_file, ex, flen, bsize);
IF ex THEN
UTL_FILE.PUT_LINE(log_file,’trigger File Exists’);
ELSE
UTL_FILE.PUT_LINE(log_file,’trigger File Does Not Exist’);
END IF;
end;

UTL_FILE.NEW_LINE (log_file);
UTL_FILE.PUT_LINE(log_file,’ Altering tablespace to read write…………….’);
–EXECUTE immediate (‘ALTER TABLESPACE ‘||v_tbs_name || ‘ read write’);
UTL_FILE.PUT(log_file,’ Done’);
err_log_file:=utl_file.fopen(dp_dir, exp_log_file, ‘r’);
UTL_FILE.NEW_LINE (log_file);
UTL_FILE.PUT_LINE(log_file,’ content of export logfile…………….’);
LOOP
BEGIN
utl_file.get_line(err_log_file,v_sqlerrm);
IF v_sqlerrm IS NULL THEN
EXIT;
END IF;
UTL_FILE.PUT_LINE(log_file,v_sqlerrm);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
END LOOP;
utl_file.fclose(err_log_file);
utl_file.fclose(log_file);

SYSMAN.UTL_MAIL.SEND_ATTACH_VARCHAR2(
sender => ‘rdi_apps@sasbsp13.bsca.eds.com’,
recipients => ‘shailesh.mishra@blueshieldca.com’,
message =>
‘<HTML><BODY>See attachment</BODY></HTML>’,
subject => ‘TTS export log’,
mime_type => ‘text/html’,
attachment => get_file(log_filename),
att_inline => false,
att_mime_type => ‘text/plain’,
att_filename => ‘log.txt’);

EXCEPTION
WHEN OTHERS THEN
execute immediate(‘DROP DIRECTORY ‘||t_dir);
–execute immediate (‘ALTER TABLESPACE ‘||v_tbs_name || ‘ read write’);
UTL_FILE.NEW_LINE (log_file);
UTL_FILE.PUT_LINE(log_file,’ Following error occured during TTS export ‘);
v_sqlerrm := SQLERRM;
UTL_FILE.PUT_LINE(log_file,v_sqlerrm);
— DBMS_DATAPUMP.stop_job(jobHandle);
err_log_file:=utl_file.fopen(dp_dir, exp_log_file, ‘r’);
UTL_FILE.NEW_LINE (log_file);
UTL_FILE.PUT_LINE(log_file,’ content of export logfile…………….’);
loop
utl_file.get_line(err_log_file,v_sqlerrm);
if v_sqlerrm is null then
exit;
end if;
UTL_FILE.PUT_LINE(log_file,v_sqlerrm);
end loop;
UTL_FILE.NEW_LINE (log_file);
UTL_FILE.PUT_LINE(log_file,’####################################################################’);
utl_file.fclose(err_log_file);
utl_file.fclose(log_file);
SYSMAN.UTL_MAIL.SEND_ATTACH_VARCHAR2(
sender => ‘rdi_apps@sasbsp13.bsca.eds.com’,
recipients => ‘shailesh.mishra@blueshieldca.com’,
message =>
‘<HTML><BODY>See attachment</BODY></HTML>’,
subject => ‘TTS export log’,
mime_type => ‘text/html’,
attachment => get_file(log_filename),
att_inline => false,
att_mime_type => ‘text/plain’,
att_filename => ‘log.txt’);

END;




Advertisements

Comments»

1. samuel correa - April 4, 2011

Hi.

when I use:
attachment => get_file

I get the error:

PLS-00201: identifier ‘GET_FILE’ must be declared

Can you help me?

sendtoshailesh - April 5, 2011

get_file is user defined function which is supposed to open the file and return its LOB value.

samuel correa - April 5, 2011

mm… I see.

I take this opportunity to say what I need to do. I request to send emails from the database with an attachment file system, for example: / oracle / PMP / file.html.

I thought that with the utility UTL_MAIL could do, but I see complicated, could I use the function get_file you created? Could you help me?

2. samuel correa - April 5, 2011

Hi.

I already found the solution thanks to you.
I create the function get_file and UTL_MAIL.SEND_ATTACH_VARCHAR2 in one procedure just like you uses.

I want a thank you very much for help me.

sendtoshailesh - April 6, 2011

thats great !!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: