Nightly Full Export Without Showing SYSTEM Password Using Data Pump

In addition to RMAN backup I have a full export batch running every night on all databases. The SYSTEM password has been kept in the batch script. Whoever can logon to the system and read the file knows the SYSTEM password.

In 10g with DBMS_DATAPUMP called from a simple PL/SQL package I can schedule the nightly export job without any batch scripts, using DBMS_SCHEDULER.

– run as SYS:
GRANT EXECUTE ON dbms_lock TO system;

– run as SYSTEM to create directory
CREATE DIRECTORY full_exp AS ‘F:OraExp{SID}’;

– Create Stored Procedure for Full Export
CREATE OR REPLACE PACKAGE sp_fullexport
IS
PROCEDURE exp;
END sp_fullexport;

CREATE OR REPLACE PACKAGE BODY sp_fullexport
IS
h1 NUMBER := NULL; PROCEDURE exp
IS
BEGIN

– remove old export
BEGIN
UTL_FILE.FREMOVE (’FULL_EXP’,'fullexp.dmp’);
EXCEPTION
…
END;

– wait until the file is removed
BEGIN
DBMS_LOCK.sleep(30);
END;

– do the export
BEGIN
h1 := DBMS_DATAPUMP.open (
operation => ‘EXPORT’,
job_mode => ‘FULL’,
job_name => ‘fullexpjob’,
version => ‘COMPATIBLE’);

DBMS_DATAPUMP.add_file(
handle => h1,
filename => ‘FULLEXP.LOG’,
directory => ‘FULL_EXP’,
filetype => 3);

DBMS_DATAPUMP.add_file(
handle => h1,
filename => ‘FULLEXP.DMP’,
directory => ‘FULL_EXP’,
filetype => 1);

DBMS_DATAPUMP.START_JOB(h1);

EXCEPTION
…
END;
END;
END sp_fullexport;

/* run procedure
exec sp_fullexport.exp;
*/

After the package is created, a scheduler job is needed to run the export procedure every night

BEGIN
DBMS_SCHEDULER.CREATE_JOB_CLASS (
job_class_name => ‘backup’,
resource_consumer_group => ‘DEFAULT_CONSUMER_GROUP’,
comments => ‘Backup, Export etc’);
END;

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => ‘full_export’,
job_type => ‘STORED_PROCEDURE’,
job_action => ‘SP_FULLEXPORT.EXP’,
start_date=>’30-MAR-06 07.00.00 PM Europe/Zurich’,
repeat_interval => ‘FREQ=DAILY;BYHOUR=19′,
end_date=>’30-MAR-22 07.00.00 PM Europe/Zurich’,
job_class => ‘backup’,
comments => ‘Full Export’);
END;
/

BEGIN
DBMS_SCHEDULER.ENABLE (’full_export’);
END;
/

/* Run job directly without scheduling
exec DBMS_SCHEDULER.RUN_JOB (’full_export’);
*/