/*
* Script: ImportSchemaWithMultiDump.sql
* Purpose: Performs a Data Pump schema import in Oracle RDS 19.8 from a dump file.
* Runs in background by detaching after job start, with robust error handling.
*
* Parameters:
* - DUMPFILE_NAME: Name of the dump file
* - DIRECTORY_NAME: Oracle directory (e.g., DATA_PUMP_DIR)
* - LOGFILE_NAME: Log file name
*
* Dependencies:
* - Privileges: EXECUTE ON DBMS_DATAPUMP, UTL_FILE; READ, WRITE ON DIRECTORY_NAME
*
* Usage:
* 1. Update DUMPFILE_NAME, DIRECTORY_NAME, LOGFILE_NAME.
* 2. Run in SQL*Plus or SQL Developer: @ImportSchemaWithMultiDump.sql
* 3. Monitor job in background via DBA_DATAPUMP_JOBS or log file.
*
* Notes:
* - Runs in background by detaching after job start.
* - Compatible with Oracle RDS 19.8, no S3 dependency.
* - Verify dump file with RDSADMIN.RDS_FILE_UTIL.LISTDIR before running.
*
* Created: 2025-09-17
* Author: Grok (xAI)
*/
set serveroutput on ;
DECLARE
ind NUMBER; -- Loop index
spos NUMBER; -- String starting position
slen NUMBER; -- String length for output
h1 NUMBER; -- Data Pump job handle
percent_done NUMBER; -- Percentage of job complete
job_state VARCHAR2(30); -- To keep track of job state
le ku$_LogEntry; -- For WIP and error messages
js ku$_JobStatus; -- The job status from get_status
jd ku$_JobDesc; -- The job description from get_status
sts ku$_Status; -- The status object returned by get_status
BEGIN
h1 := DBMS_DATAPUMP.OPEN(operation => 'IMPORT', job_mode => 'SCHEMA', job_name=>null);
DBMS_DATAPUMP.ADD_FILE(handle => h1, filename =>'DUMPFILE_NAME', directory =>'DIRECTORY_NAME', filetype => dbms_datapump.ku$_file_type_dump_file);
DBMS_DATAPUMP.ADD_FILE(handle => h1, filename => 'LOGFILE_NAME', directory => 'DIRECTORY_NAME', filetype => dbms_datapump.ku$_file_type_log_file);
DBMS_DATAPUMP.SET_PARAMETER(h1,'TABLE_EXISTS_ACTION','REPLACE');
begin
dbms_datapump.start_job(h1);
dbms_output.put_line('Data Pump job started successfully in background');
dbms_datapump.detach(h1); -- Detach to run in background
exception
when others then
if sqlcode = dbms_datapump.success_with_info_num
then
dbms_output.put_line('Data Pump job started with info available:');
dbms_datapump.get_status(h1,
dbms_datapump.ku$_status_job_error,0,
job_state,sts);
if (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0)
then
le := sts.error;
if le is not null
then
ind := le.FIRST;
while ind is not null loop
dbms_output.put_line(le(ind).LogText);
ind := le.NEXT(ind);
end loop;
end if;
end if;
dbms_datapump.detach(h1); -- Detach even on success_with_info
else
if h1 is not null then
dbms_datapump.detach(h1); -- Ensure detach on failure
end if;
raise;
end if;
end;
/*
-- Monitoring loop disabled for background execution. Use separate session to monitor.
percent_done := 0;
job_state := 'UNDEFINED';
while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop
dbms_datapump.get_status(h1,
dbms_datapump.ku$_status_job_error +
dbms_datapump.ku$_status_job_status +
dbms_datapump.ku$_status_wip,-1,job_state,sts);
js := sts.job_status;
if js.percent_done != percent_done
then
dbms_output.put_line('*** Job percent done = ' ||
to_char(js.percent_done));
percent_done := js.percent_done;
end if;
if (bitand(sts.mask,dbms_datapump.ku$_status_wip) != 0)
then
le := sts.wip;
else
if (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0)
then
le := sts.error;
else
le := null;
end if;
end if;
if le is not null
then
ind := le.FIRST;
while ind is not null loop
dbms_output.put_line(le(ind).LogText);
ind := le.NEXT(ind);
end loop;
end if;
end loop;
dbms_output.put_line('Job has completed');
dbms_output.put_line('Final job state = ' || job_state);
dbms_datapump.detach(h1);
*/
exception
when others then
dbms_output.put_line('Exception in Data Pump job');
if h1 is not null then
dbms_datapump.get_status(h1,dbms_datapump.ku$_status_job_error,0,
job_state,sts);
if (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0)
then
le := sts.error;
if le is not null
then
ind := le.FIRST;
while ind is not null loop
spos := 1;
slen := length(le(ind).LogText);
if slen > 255
then
slen := 255;
end if;
while slen > 0 loop
dbms_output.put_line(substr(le(ind).LogText,spos,slen));
spos := spos + 255;
slen := length(le(ind).LogText) + 1 - spos;
end loop;
ind := le.NEXT(ind);
end loop;
end if;
end if;
dbms_datapump.detach(h1); -- Ensure final detach
end if;
END;
/
Example Fix
If your script looks like this:
powershell
$token = $env:ID_TOKEN # Assuming the OIDC token is set in an environment variable
Connect-AzAccount -ApplicationId "75d97745-e27c-4159-9621-cc8ec7022a7" -FederatedToken $token -TenantId "3e"Update it to:
powershell
$token = $env:ID_TOKEN
$tenantId = "<your-tenant-id>" # Replace with your actual tenant ID (e.g., 12345678-1234-1234-1234-1234567890ab)
Connect-AzAccount -ApplicationId "75d97745-e27c-4159-9621-cc8ec7022a7" -FederatedToken $token -TenantId $tenantId===========================================================
SET SERVEROUTPUT ON SIZE UNLIMITED;
DECLARE
-- Configurable parameters
v_owner VARCHAR2(128) := 'HEMANT'; -- Job owner
v_job_name VARCHAR2(128) := 'SYS_IMPORT_SCHEMA_01'; -- Job name from DBA_DATAPUMP_JOBS
v_detach_after_monitor BOOLEAN := TRUE; -- TRUE to detach, FALSE to stay attached
-- Data Pump variables
h1 NUMBER;
job_state VARCHAR2(30);
sts ku$_Status;
le ku$_LogEntry;
ind NUMBER;
percent_done NUMBER := 0;
js ku$_JobStatus;
BEGIN
-- Attach to the job
h1 := DBMS_DATAPUMP.ATTACH(job_name => v_job_name, job_owner => v_owner);
DBMS_OUTPUT.PUT_LINE('Attached to job: ' || v_job_name);
-- Monitor job
job_state := 'UNDEFINED';
WHILE job_state NOT IN ('COMPLETED', 'STOPPED') LOOP
DBMS_DATAPUMP.GET_STATUS(
handle => h1,
mask => DBMS_DATAPUMP.KU$_STATUS_JOB_ERROR + DBMS_DATAPUMP.KU$_STATUS_JOB_STATUS + DBMS_DATAPUMP.KU$_STATUS_WIP,
timeout => -1,
job_state => job_state,
status => sts
);
js := sts.job_status;
-- Display progress
IF js.percent_done != percent_done THEN
DBMS_OUTPUT.PUT_LINE('Percent done: ' || js.percent_done || '%');
percent_done := js.percent_done;
END IF;
-- Display errors or work-in-progress messages
IF BITAND(sts.mask, DBMS_DATAPUMP.KU$_STATUS_WIP) != 0 THEN
le := sts.wip;
ELSIF BITAND(sts.mask, DBMS_DATAPUMP.KU$_STATUS_JOB_ERROR) != 0 THEN
le := sts.error;
ELSE
le := NULL;
END IF;
IF le IS NOT NULL THEN
ind := le.FIRST;
WHILE ind IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE('Log: ' || SUBSTR(le(ind).LogText, 1, 255));
ind := le.NEXT(ind);
END LOOP;
END IF;
DBMS_LOCK.SLEEP(5); -- Poll every 5 seconds
END LOOP;
DBMS_OUTPUT.PUT_LINE('Job state: ' || job_state);
-- Detach or stay attached based on parameter
IF v_detach_after_monitor THEN
DBMS_DATAPUMP.DETACH(h1);
DBMS_OUTPUT.PUT_LINE('Detached from job');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error monitoring job ' || v_job_name || ': ' || SQLERRM);
IF h1 IS NOT NULL THEN
DBMS_DATAPUMP.DETACH(h1);
END IF;
RAISE;
END;
/
No comments:
Post a Comment