如果出现错误，如何创建DBMS_DATAPUMP错误？

Question

当源表和目标表中的列不匹配时，DBMS_DATAPUMP不会失败。这意味着不会引发任何异常。我正在尝试使用GET_STATUS过程来了解是否有任何错误，但不幸的是似乎没有...

我的最终目标是让DBMS_DATAPUMP在导入失败时引发一个异常。不同的列是一个很容易使用的例子，因为我知道它应该失败。

以下是我当前的代码(我故意隐藏了模式名称)。我在两台服务器上使用的环境是相同的，只是我在源表中添加了一个额外的列。我还对表中的行数进行了计数。

connect schema/*@db1/db1
-- */

create table tmp_test_datapump as
 select u.*, cast(null as number) as break_it
   from user_tables u;

Table created.


select count(*) from tmp_test_datapump;

  COUNT(*)
----------
      1170

connect schema/*@db2/db2
-- */

set serveroutput on

create table tmp_test_datapump as
 select u.*
   from user_tables u;

Table created.

在尝试测试这一点时，DATAPUMP代码变得有点复杂。无限循环中的所有东西都可以删除，这将起到相同的作用。

declare
   l_handle number;
   l_status varchar2(255);
   l_job_state varchar2(4000);
   l_ku$status ku$_status1020;
begin
   l_handle := dbms_datapump.open( operation => 'IMPORT'
                                 , job_mode => 'TABLE'
                                 , remote_link => 'SCHEMA.DB.DOMAIN.COM'
                                 , job_name => 'JOB_TEST_DP'
                                 , version => 'COMPATIBLE' );
   dbms_datapump.set_parameter( handle => l_handle
                              , name => 'TABLE_EXISTS_ACTION'
                              , value => 'TRUNCATE');
   dbms_datapump.metadata_filter( handle => l_handle
                                , name => 'NAME_EXPR'
                                , value => 'IN (''TMP_TEST_DATAPUMP'')');
   dbms_datapump.start_job(handle => l_handle);

   while true loop
      dbms_datapump.wait_for_job(handle => l_handle,job_state => l_status);

      if l_status in ('COMPLETED','STOPPED') then
         exit;
      end if;

      dbms_datapump.get_status( handle => l_handle
                              , mask => dbms_datapump.KU$_STATUS_JOB_ERROR
                              , job_state => l_job_state
                              , status => l_ku$status);
      dbms_output.put_line('state: ' || l_job_state);
      if l_ku$status.error is not null and l_ku$status.error.count > 0 then

         for i in l_ku$status.error.first .. l_ku$status.error.last loop
            dbms_output.put_line(l_ku$status.error(i).logtext);
         end loop;
       end if;
   end loop;

end;
/

PL/SQL procedure successfully completed.

select count(*) from tmp_test_datapump;

  COUNT(*)
----------
        47

正如您所看到的，表中的记录数量是不同的；导入失败了，并且没有引发异常。Various、blogs和DBA.SE questions暗示可以进行某种错误捕获；但我似乎无法做到这一点。

如何在DBMS_DATAPUMP导入中捕获致命错误？

Answer 1

我正在使用dbms_datapump包，我知道。下面的过程是在一个表中搜索将要导出的架构。BACKUP_INFO_MOD是一个使用PRAGMA AUTONOMOUS TRANSACTION的过程，它在另一个表中生成日志。

this document的示例6.3对我帮助很大。下面是我的代码片段(带有附加注释)：

  CREATE OR REPLACE PROCEDURE BACKUP_EXECUTE (
    threads in number := 1 
  , dir in varchar2 := 'DATA_PUMP_DIR'
  ) AS 
    schemas varchar2(255);
    filename varchar2(255);
    path varchar2(255);
    errormsg varchar2(4000);

    handle number;
    job_state varchar2(30);
    --variables under this line are important to error handling
    logs ku$_LogEntry;
    lindx pls_integer;
    status ku$_Status;

    exporterr exception; --our exception to handle export errors

    [...]
  BEGIN    
    [...]
        schemas:=schema_list(indx).schema_name;

        --Full dir path for logs
        select directory_path into path from dba_directories where directory_name=dir;
        --If data not found then automatically raise NO_DATA_FOUND

        select to_char(sysdate, 'YYMMDDHH24MI-')||lower(schemas)||'.dmp' into filename from dual;

        backup_info_mod('insert',path||filename,schemas);

        begin --For inner exception handling on short fragment
          handle := dbms_datapump.open('EXPORT','SCHEMA');
          dbms_datapump.add_file(handle, filename, dir); --dump file
          dbms_datapump.add_file(handle, filename||'.log', dir,null,DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE); --export log file
          dbms_datapump.metadata_filter(handle, 'SCHEMA_EXPR', 'IN ('''||schemas||''')');
          dbms_datapump.set_parallel(handle,threads);

          backup_info_mod(file_name=>path||filename, curr_status=>'IN PROGRESS');

          dbms_datapump.start_job(handle);

        --If job didn't start due to some errors, then let's get some information
        exception
          when others then
            dbms_datapump.get_status(handle,8,0,job_state,status);
        --This will overwrite our job_state and status
        end;

        --Let's go handle error if job_state was overwritten
        if job_state is not null then
          raise exporterr;
        else
          job_state:='UNDEFINED';
        end if;

        --Checking in loop if errors occurred. I'm not using wait_for_job
        --because it didn't work out
        while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop
        --Like before, let's get some information
          dbms_datapump.get_status(handle,8,-1,job_state,status);

        --Looking for errors using mask
          if (bitand(status.mask,dbms_datapump.ku$_status_job_error) != 0) then          
        --If occurred: let's stop the export job and raise an error
            dbms_datapump.stop_job(handle);
            dbms_datapump.detach(handle);
            raise exporterr;
          exit;

          end if;
        end loop;

        backup_info_mod(file_name=>path||filename, curr_status=>'COMPLETED');

        dbms_datapump.detach(handle);

      exception
        when NO_DATA_FOUND then
          backup_info_mod('insert',null,schemas,'ERROR','No '||dir||' defined in dba_directories');

        when exporterr then
        --Let's get all error messages and write it to errormsg variable
          logs:=status.error;
          lindx:=logs.FIRST;

          while lindx is not null loop
            errormsg:=errormsg||logs(lindx).LogText;
            lindx:=logs.NEXT(lindx);

            if lindx is not null then
              errormsg:=errormsg||' | '; --Just to separate error messages
            end if;
          end loop;

          backup_info_mod(
            file_name=>path||filename,
            curr_status=>'ERROR',
            errormsg=>errormsg);

        /*when other then --TODO
          null;
          */
      end;

  END BACKUP_EXECUTE;

Answer 2

在为impdp创建日志时，您可以将datapump命令放在shell脚本中，并且在结束shell脚本之前，您可以检查日志中的IMP- error或ORA- error，如果为true，则警告用户查看日志文件中的错误。

Answer 3

yammy提供的document很好。

在使用dbms_datapump包导入DB转储时，我遇到了同样的问题。即使在导入过程中出现错误，作业也会被视为成功/完成。通过使用document中的代码示例，我可以在导入期间获得错误/日志消息。然后，我检查日志消息中是否有'ORA-‘，并在导入作业完成时抛出一个自定义错误。

以下是示例代码：

PROMPT CREATE OR REPLACE PROCEDURE import_schema

CREATE OR REPLACE PROCEDURE import_db_dump (
    dumpFilename        IN VARCHAR2)
IS
    handle NUMBER;              -- Handler of the job
    loopIdx NUMBER;             -- Loop index
    percentDone NUMBER;         -- Percentage of job complete
    jobState VARCHAR2(30);      -- To keep track of job state
    ku_logEntry ku$_LogEntry;   -- For WIP and error messages
    ku_jobStatus ku$_JobStatus; -- The job status from get_status
    ku_jobDescjd ku$_JobDesc;   -- The job description from get_status
    ku_Status ku$_Status;       -- The status object returned by get_status

    errorCount          NUMBER;
    import_error_found  EXCEPTION;

BEGIN
    handle := dbms_datapump.open (
    operation =>    'IMPORT',
    job_mode =>     'SCHEMA');

    dbms_output.put_line('Define table exists action: truncate');
    dbms_datapump.set_parameter (
        handle =>   handle,
        name =>     'TABLE_EXISTS_ACTION',
        value =>    'TRUNCATE');

    dbms_output.put_line('Define dumpfilename: ' || dumpFilename);
    dbms_datapump.add_file (
        handle =>       handle,
        filename =>     dumpFilename,
        filetype =>     dbms_datapump.ku$_file_type_dump_file);

    dbms_output.put_line('Start datapump job');
    dbms_output.put_line('==================');
    dbms_datapump.start_job (handle);

    -- Ref: http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_api.htm#SUTIL977

    -- The import job should now be running. In the following loop, the job is
    -- monitored until it completes. In the meantime, progress information is
    -- displayed.

    percentDone := 0;
    jobState := 'UNDEFINED';
    errorCount := 0;
    WHILE (jobState != 'COMPLETED') AND (jobState != 'STOPPED') LOOP
        dbms_datapump.get_status(handle,
               dbms_datapump.ku$_status_job_error +
               dbms_datapump.ku$_status_job_status +
               dbms_datapump.ku$_status_wip,
               -1, jobState, ku_Status);
        ku_jobStatus := ku_Status.job_status;

        -- If the percentage done changed, display the new value.
        IF ku_jobStatus.percent_done != percentDone THEN
          dbms_output.put_line('*** Job percent done = ' ||
                               to_char(ku_jobStatus.percent_done));
          percentDone := ku_jobStatus.percent_done;
        END IF;

        -- If any work-in-progress (WIP) or Error messages were received for the job,
        -- display them.

        IF (bitand(ku_Status.mask, dbms_datapump.ku$_status_wip) != 0) THEN
            ku_logEntry := ku_Status.wip;
        ELSE
            IF (bitand(ku_Status.mask,dbms_datapump.ku$_status_job_error) != 0) THEN
                ku_logEntry := ku_Status.error;
            ELSE
                ku_logEntry := null;
            END IF;
        END IF;

        IF ku_logEntry IS NOT NULL THEN
            loopIdx := ku_logEntry.FIRST;
            WHILE loopIdx IS NOT NULL LOOP
                dbms_output.put_line(ku_logEntry(loopIdx).LogText);
                IF INSTR(ku_logEntry(loopIdx).LogText, 'ORA-') > 0 THEN
                    errorCount := errorCount + 1;
                    dbms_output.put_line('^^^^---ERROR FOUND');
                END IF;
                loopIdx := ku_logEntry.NEXT(loopIdx);
            END LOOP;
        END IF;

    END LOOP;

    -- Indicate that the job finished and gracefully detach from it.
    dbms_output.put_line('Job has completed');
    dbms_output.put_line('Final job state = ' || jobState);
    dbms_datapump.detach(handle);

    IF errorCount > 0 THEN
        RAISE import_error_found;
    END IF;

EXCEPTION
    WHEN import_error_found THEN
        dbms_output.put_line('Error found when import. Number of error: ' || errorCount);
        RAISE;

    WHEN OTHERS THEN
        dbms_output.put_line('[Error Backtrace]');
        dbms_output.put_line(dbms_utility.format_error_backtrace());
        dbms_output.put_line('[Call Stack]');
        dbms_output.put_line(dbms_utility.format_call_stack());

        dbms_datapump.stop_job(handle);
        RAISE;
END;
/

希望这能有所帮助。