The gains and pains of dbms_datapump. Part II

In my first post about dbms_datapump I showed you some basics about this usefull but sometimes hard to use package. Usually the next question after “how can I use this package?” is “how can I use it with a different user than SYS?”.

The target is to install the procedure of my last post under a separate user. The database link as well should connect to a separate user.

Which privileges does a user need to install my procedure?

CREATE PROCEDUE of course this is needed

IMP_FULL_DATABASE is the basic role to import stuff via dbms_datapump

CREATE TABLE is needed by dbms_datapump, because it uses a table to save some information during execution

with CREATE TABLE comes the need for QUOTA on the DEFAULT TABLESPACE

DROP and ALTER USER are specific to my procedure, because I drop the user and alter its password

CREATE SESSION is not necessarely needed. I would prefer to write a wrapper procedure around CopySchemaBlog and grant the privileges to execute it to my Application User or whatever user is best to run my jobs with

GRANT SELECT ON sys.dba_users is needed to verify whether the user, which is going to be copied, already exists

EXECUTE ON sys.dbms_flashback is needed for the procedure to be valid

In the source database we need a user with some different privileges.

EXP_FULL_DATABASE is the basic privilege needed to export data of different users with dbms_datapump

FLASHBACK ANY TABLE is needed to do a consistent datapump export using FLASHBACK_SCN parameter. With this parameter set, dbms_datapump uses Flashback Query to export a consistent set of data. No Flashback Logs are required, because Flashback Query uses the UNDO tablespace and not Flashback Logs to get the before image of changed blocks

CREATE SESSION is needed to connect via database link to the remote site

SELECT ON sys.v_$instance is needed for my procedure to be able to verify from which instance the data is copied. This privilege is not needed by dbms_datapump

EXECUTE ON sys.dbms_flashback is needed to get the current system change number

This is the script for the user on the source database.

CREATE USER source_user IDENTIFIED BY &pw
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;

--privileges for dbms_datapump
GRANT EXP_FULL_DATABASE TO source_user;
GRANT FLASHBACK ANY TABLE TO source_user;
GRANT CREATE SESSION TO source_user;

ALTER USER source_user DEFAULT ROLE ALL;

GRANT SELECT ON sys.v_$instance TO source_user;
GRANT EXECUTE ON sys.dbms_flashback TO source_user;

This is the script to install the user and the CopySchemaBlog procedure on the destination database.

CREATE USER target_user IDENTIFIED BY &pw
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;

--privileges for dbms_datapump
GRANT IMP_FULL_DATABASE TO target_user;
GRANT DROP USER TO target_user;
GRANT ALTER USER TO target_user;
GRANT CREATE SESSION TO target_user;
GRANT CREATE TABLE TO target_user;
--GRANT FLASHBACK ANY TABLE TO target_user;

ALTER USER target_user DEFAULT ROLE ALL;
ALTER USER target_user QUOTA UNLIMITED ON users;

GRANT SELECT ON sys.dba_users TO target_user;
GRANT EXECUTE ON sys.dbms_flashback TO target_user;

CREATE OR REPLACE PROCEDURE target_user.CopySchemaBlog (
  sourceSchema      IN VARCHAR2,
  destinationSchema IN VARCHAR2,
  databaseLink      IN VARCHAR2,
  newPassword       IN VARCHAR2,
  debug             IN BOOLEAN := FALSE)
IS
  handle                  NUMBER;
  message                 VARCHAR2(2000);
  flashbackSCN            NUMBER;
  logfileName             VARCHAR2(200);
  destinationSchemaExists NUMBER;
  jobState                user_datapump_jobs.state%TYPE;
BEGIN
  dbms_output.put_line('verify whether destination schema '||destinationSchema||' already exists');
  SELECT COUNT(username)
    INTO destinationSchemaExists
    FROM dba_users
      WHERE username = destinationSchema
      AND username NOT IN ('SYS','SYSTEM','DBSNMP','DIP','EXFSYS','OUTLN','TSMSYS');
  IF ( destinationSchemaExists > 0 ) THEN
    dbms_output.put_line('drop user '||destinationSchema);
    EXECUTE IMMEDIATE 'DROP USER '||destinationSchema||' CASCADE';
  END IF;

  dbms_output.put_line('open datapump job over database link '||databaseLink);
  handle := dbms_datapump.open (
    operation =>    'IMPORT',
    job_mode =>     'SCHEMA',
    remote_link =>  databaseLink);

  IF debug = TRUE THEN
    dbms_output.put_line('set logfile parameter');
    SELECT 'datapump_'||to_char(SYSDATE,'YYYYMMDD')||'.log'
      INTO logfileName
      FROM dual;
    dbms_datapump.add_file (
      handle =>     handle,
      filename =>   logfileName,
      directory =>  'DATA_PUMP_DIR',
      filetype =>   dbms_datapump.ku$_file_type_log_file);
  END IF;

  dbms_output.put_line('set FLASHBACK_SCN parameter');
  dbms_datapump.set_parameter(
    handle =>   handle,
    name =>     'FLASHBACK_SCN',
    value =>    dbms_flashback.get_system_change_number);

  dbms_output.put_line('define SCHEMA_LIST');
  dbms_datapump.metadata_filter(
    handle =>   handle,
    name =>     'SCHEMA_LIST',
    value =>    ''''||sourceSchema||'''');

  dbms_output.put_line('define schema remapping');
  dbms_datapump.metadata_remap(
    handle =>     handle,
    name =>       'REMAP_SCHEMA',
    old_value =>  sourceSchema,
    value =>      destinationSchema);

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

  dbms_output.put_line('wait for job to finish');
  dbms_datapump.wait_for_job(handle, jobState);

  dbms_output.put_line('set new password for destination schema '||destinationSchema);
  EXECUTE IMMEDIATE 'ALTER USER '||destinationSchema||' IDENTIFIED BY '||newPassword;
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line('-------------------------------------------------------------------------------------');
  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_output.put_line('-------------------------------------------------------------------------------------');

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

I tested the hole process on 11gR2 by installing source and target user in one database. Then I created a user called ORIGINAL.

SQL> CREATE USER original IDENTIFIED BY test1234;

User created.

SQL> GRANT connect, resource TO original;

Grant succeeded.

SQL> CREATE TABLE original.t (x NUMBER);

Table created.

SQL> INSERT INTO original.t VALUES(1);

1 row created.

SQL> commit;

Commit complete.

Afterwards I logged in as TARGET_USER an created a database link pointing to the same database (GLOBAL_NAMES=FALSE) but connected as SOURCE_USER.

CREATE DATABASE LINK blog CONNECT TO source_user IDENTIFIED BY test1234 USING 'dar112.ltdar.trivadis.com';

Now everything was prepared to execute a test run.

SQL> set serveroutput on
SQL> exec CopySchemaBlog(sourceSchema => 'ORIGINAL', destinationSchema => 'COPY', databaseLink => 'blog', newPassword => 'test1234', debug => TRUE);
verify whether destination schema COPY already exists
open datapump job over database link blog
set logfile parameter
set FLASHBACK_SCN parameter
define SCHEMA_LIST
define schema remapping
start datapump job
wait for job to finish
set new password for destination schema COPY

PL/SQL procedure successfully completed.

The logfile had the following content

Starting "TARGET_USER"."SYS_IMPORT_SCHEMA_02":
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "COPY"."T"                                       1 rows
Job "TARGET_USER"."SYS_IMPORT_SCHEMA_02" successfully completed at 23:56:11

Everything ran fine, so it’s proven that the privileges are correct. My code should work fine for all versions of 10g and 11g (but I only tested it with 11gR2 and partially with 10gR2 and 11gR1).

About these ads