Export database Oracle PL/SQL

Question

I want to export the full Oracle database automatically each day. I know in order to export it, we use the commande bellow:

exp userid=user/pass full=yes file='expdat.dmp' log='file.log'

but i want to know how to do it automatically. And is there a way to do it with pl/sql?

[EDIT] I created a .bat file containing the command below. and i created dbms_scheduler job as following:

 BEGIN
 dbms_scheduler.create_credential(
      credential_name  =>  'c',
      username         =>  'user',
      password         =>  'password');

    SYS.DBMS_SCHEDULER.CREATE_JOB( job_name => 'ex_job_1',
        job_type => 'EXECUTABLE',
        job_action => 'C:\WINDOWS\system32\cmd.exe',
        job_class => 'DEFAULT_JOB_CLASS',
        comments => 'Job to call batch script on Windows',
        auto_drop => FALSE,
        number_of_arguments => 3,
        enabled => FALSE,
        credential_name=>'c'
        );

    SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE( job_name => 'ex_job_1', argument_position => 1, argument_value => '/q'); 
    SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE( job_name => 'ex_job_1', argument_position => 2, argument_value => '/c'); 
    SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE( job_name => 'ex_job_1', argument_position => 3, argument_value => '"C:\Backup_folder\export_database.bat"'); 
    SYS.DBMS_SCHEDULER.ENABLE( 'ex_job_1' ); 

DBMS_SCHEDULER.RUN_JOB('ex_job_1');


    END;

and it gives me the following errors:

Error Report -
ORA-06550: Line 2, column 17:
PLS-00302: The 'CREATE_CREDENTIAL' component must be declared
ORA-06550: Line 2, column 2:
PL / SQL: Statement ignored
ORA-06550: Line 7, column 5:
PLS-00306: number or wrong argument types in call to 'CREATE_JOB'
ORA-06550: Line 7, column 5:
PL / SQL: Statement ignored
06550. 00000 - "line% s, column% s: \ n% s"
* Cause: Usually a PL / SQL compilation error.
*Action:

I'm stuck, what should i do?

PS:A schema export might be enough for what i need.

DBMS_SCHEDULER to schedule a job. You can invoke Host/OS jobs. Your issue will be hiding the username /password if you call a script to do the exp. — TenG
– TenG, Commented May 10, 2017 at 16:51
@APC yes i want it to be exported every day, in a second thought, an export or the scheme might be enough. — zenami
– zenami, Commented May 11, 2017 at 16:34

Matthew McPeak · Accepted Answer · 2017-05-10 17:11:04Z

1

The Oracle-supplied package DBMS_DATAPUMP provides a PL/SQL interface to that functionality.

See: https://docs.oracle.com/database/121/ARPLS/d_datpmp.htm#ARPLS66053

Also, there are some helpful examples here, though, unfortunately, none covers the case of a FULL export: https://docs.oracle.com/database/121/SUTIL/GUID-5AAC848B-5A2B-4FD1-97ED-D3A048263118.htm#SUTIL977

answered May 10, 2017 at 17:11

Matthew McPeak

18.1k3 gold badges32 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

zenami · Accepted Answer · 2017-05-15 15:47:45Z

I managed to do the daily export of the full database using dbms_scheduler and dbms_datapump with the following code:

begin
  dbms_scheduler.create_schedule(
       schedule_name   => 'daily_back',
       repeat_interval => 'FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN;BYHOUR=8;BYMINUTE=0;BYSECOND=0',
       start_date => SYSTIMESTAMP
  );

  dbms_scheduler.create_program
    (  program_name   => 'backup_database',
       program_type   => 'PLSQL_BLOCK',
       program_action => 'DECLARE
                          handle NUMBER;
                          nom_job varchar(25) := to_char(SYSDATE,''DD-MM-YYYY_HH24-MI-SS'');
                          nom_dump varchar(35) := ''EXPORT_''|| nom_job || ''.DMP'';
                          nom_log varchar(35) := ''EXPORT_''|| nom_job || ''.LOG'';
                          BEGIN
                          execute immediate ''create or replace directory '' || ''BACKUP_FOLDER'' ||
                                            '' as '''''' || ''C:\BACKUP_FOLDER\BACKUP'' || '''''''';
                          handle := SYS.DBMS_DATAPUMP.OPEN(operation => ''EXPORT'',job_mode  => ''FULL'',remote_link => NULL,job_name  => nom_job,version=> ''10.0.0'');
                          SYS.DBMS_DATAPUMP.ADD_FILE(handle=> handle,filename  => nom_dump,directory => ''BACKUP_FOLDER'',filetype  => 1);
                          SYS.DBMS_DATAPUMP.ADD_FILE(handle=> handle,filename=> nom_log,directory => ''BACKUP_FOLDER'',filetype  => 3);
                          SYS.DBMS_DATAPUMP.START_JOB(handle => handle,skip_current => 0,abort_step => 0);
                          SYS.DBMS_DATAPUMP.DETACH(handle=> handle); END;',
       enabled        => TRUE
    );

  dbms_scheduler.create_job (
    job_name=>'daily_backup',
    program_name =>'backup_database',
    schedule_name=> 'daily_back',
    enabled      => true 
  );

end;

I hope this would help someone in the future.

Collectives™ on Stack Overflow

Export database Oracle PL/SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related