Schema copying with Data Pump

Description

CERN IT-DB group has implemented fully automatic solution for copying content of a database schema from one database system to another. Basic functionality of this tool can be shared within end database users in order to allow them an integration of theirs data among multiple databases without an interaction with service administrators.

Requirements and limitations

In order to use the tool

• the data manager tool has to be installed on databases (source and target)
  • it is installed by default on all databases, but only on demand - a user has to request for it before first usage via SNOW
  • to verify if it is installed check for existence of CERNDB_DPUSER public package (see Examples section). If it does not exist it means that tool requires installation
  • the installation can be done only by DBA
• the list of target databases (where the content of schema can be copied) is limited and can be checked in CERNDB_DPMGR.allowed_targets view
• tables with LONG data type are not supported (skipped)!
  • each skipped table is signaled in the log by error ORA-31679
  • tables with LONG types can by copied using different mode not available to end users, therefore contact oracle support
• only one schema per call can be copied
• schema with the same name has to exists on target database (no name remapping is possible).
  • it does not have to be empty. But in such case the previous content will be removed.
  • if schema does not exists on target system it has to be precreated using FIM(https://account.cern.ch/account/Services/MyServices.aspx) by the user

User interface

The data pump manager tool is available (if installed on the system) to all users that can can login through following public interface (PL/SQLpackage):

PACKAGE CERNDB_DPUSER AS 

    procedure CP_SCHEMA(
        target_database varchar2
        ,overwrite boolean default false
        ,flashback_time timestamp default null
        ,tablespace_remapping varchar2 default null
        ,exclude varchar2 default null
        ,include varchar2 default null
        ,overwrite_method varchar2 default 'RECREATE'
    );
    
    procedure SHOW_LOG(
        job_name varchar2 default null
    );

END CERNDB_DPUSER;


--views

view DP_TARGET_DATABASE
     DATABASE_NAME  NOT NULL VARCHAR2(50)


view USER_DP_TARGET_DATABASES
     JOB_NAME                              NOT NULL VARCHAR2(50)
     ACTION_TYPE                        VARCHAR2(20)
     TARGET_DATABASE           VARCHAR2(100)
     START_TIME                           DATE
     END_TIME                               DATE
     FLASHBACK_TIME               TIMESTAMP(6)
     VIA_DBLINK                            VARCHAR2(5)
     DUMP_FILES                          VARCHAR2(1000)

Interface description

• CERNDB_DPUSER (package) -- public to all schema package for schema copying
  • CP_SCHEMA (procedure) -- copies full or selected content of calling schema to another database. Possible values for the argument on current system can be looked up in DP_TARGET_DATABASES view
    • target_database (argument)-- target database name (mandatory)
    • overwrite (argument)-- safety flag to avoid overriding objects in target schema. If user intends to delete all target schema content the value should be set to true. Otherwise it should be set to false (default value), procedure will fail if target schema is not empty
    • flashback_time (argument)-- a time from which consistent snapshot of schema's tables should be taken at source database. The value of parameter should be <= current time. If argument is not specified it is always initialized with current time. There are limitation in flashback retention time, not any time from past can be specified. Normally flashback data are available from last few hours only, it is up to a schema activity and configuration of a database
    • tablespace_remapping (argument) -- specifies coma separated list of tablespace remappings [..,source tablespace : target tablespace,..]. The default value is null which means that objects will be copied into the same tablespaces (they need to be created before) as on the source. Example: tablespace_remapping=>'DATA01:DATA02,CMS_DATA01:DATA02'
    • exclude (argument) -- filtering out specified object types and names to be copied. By default no filtering is applied. For more info see Data filtering section.
    • include (argument) -- alternative filtering method - limits set of objects being copied to the argument specification. By default no filtering is applied. For more info see Data filtering section
    • overwrite_method (argument) -- a method of cleaning target schema before copying the current content from source. There are to ways:
      • RECREATE (argument value)-- delete whole schema and create it again. This ensures having exact same copy of tables and metadata of the schema (no orphan objects from previous content of target schema). In order to drop target schema all its session has to be closed before hand. Password, tablespaces and quotas will remain unchanged after recreation
      • TRUNCATE (argument value)-- updates (cleans and copies) content of tables without dropping the schema. Some of schema's metadata objects are pre-cleaned (SEQUENCE, RULE, PROCEDURE, PACKAGE, MATERIALIZED VIEW, SYNONYM, VIEW, FUNCTION, TYPE) in order to allow new versions to be copied from source.
  • SHOW_LOG (procedure)-- displays content of the data pump jobs. IMPORTANT: before running procedure with SQLPlus it is required to enable server output by running: set serveroutput on
    • job_name (argument)-- name of the job that log should be displayed. If not specified last created job name will be used
• DP_TARGET_DATABASE (view) -- displays possible destinations for copying schemas
  • DATABASE_NAME (column) -- name of the database, exact name has to be passed as an argument to CP_SCHEMA procedure
• USER_DP_TARGET_DATABASES (view) -- contains list of jobs (including history) that were created by a user
  • JOB_NAME (column) -- name of a job
  • ACTION_TYPE (column) -- type of the action associated to a job
    • COPY (column value) -- direct schema copying between databases
    • EXPORT (column value) -- dumping schema content to dumpfile (action not available everywhere)
    • IMPORT (column value) -- loading schema content from dumpfile to target database (action not available everywhere)
  • TARGET_DATABASE -- target database name. NULL if action_type=EXPORT
  • START_TIME -- time when job started
  • END_TIME -- time when job finished. NULL if job still running or failed
  • FLASHBACK_TIME -- falshback time used by the job
  • VIA_DBLINK -- flag indicating if copying was perform directly over the network (TRUE value) or (FALSE value) by using dumpfiles (export&import). This option is not available on all systems.
  • DUMP_FILES -- name of the dummpfiles, created or used by the jobs. NULL if VIA_DBLINK = TRUE.

Data filtering

* User can limit set of data to be copied using one of two methods: inclusion and exclusion or both at a time. * There are two (optional) arguments in cp_schema procedure that control both methods. include and exclude. If there are not specified whole schema content is copied by default * The filtering can be specified by on two levels: object type and object name. * The syntax of include and exclude parameters is the same: object_type[:name_clause]; object_type[:name_clause]

• name clause is optional and if it is not specified all objects of the type will apply for a filter
• possible values for object_type: https://twiki.cern.ch/twiki/pub/DB/CERNDB_DPUSER/datapump_object_types.txt
• possible name clause
  • "IN" , list of objects (can be a query); example: TABLE:”IN (‘EMP’,'DEPT’)” ; TABLE:”IN (select table_name from dba_tables where COMPRESSION='ENABLED' )”
  • "=" , one object name; example: INDEX:”= ‘MY_INDX’”
  • "LIKE" , objects with a name like... ; example: PROCEDURE:”LIKE ‘MY_PROC_%’”
  • "<",">" , objects with a names that start with characters greater then...; example: TABLE:”> ‘E’”

Examples

Checking if datapump tool is installed on a database

1) login to any schema on the database

> sqlplus CMS_COND_42X_GEOMETRY@d3r

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 10 11:36:59 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management and Real Application Testing options

SQL>

2) check if CERNDB_DPUSER package exists from all_objects view. You can use following query

select decode(count(1),2,'INSTALLED','NOT INSTALLED') as STATUS from all_objects where object_name='CERNDB_DPUSER' and owner in('CERNDB_DPMGR','PUBLIC') and status='VALID';

STATUS
-------------
INSTALLED

Full schema copy

• EXAMPLE EXPLANATION
  • copied schema name: CMS_COND_42X_GEOMETRY
  • source database: d3r
  • source database: test11g

2) login to the schema which needs to be copied. If using SQLPlus:

> sqlplus CMS_COND_42X_GEOMETRY@d3r

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 10 11:36:59 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management and Real Application Testing options

SQL>

3) check if target database is registered in the system (if not ask CERN Oracle support to register it). If using SQLPlus: (e.g. desired target database = test11g)

select * from CERNDB_DPMGR.allowed_targets;

DATABASE_NAME
--------------------------------------------------
TEST11G

4) run copying job. When procedure is finished without an error the schema is copied.

SQL> exec cerndb_dpuser.cp_schema('test11g');

5) check progress of a job with another session by executing show_log procedure connected to the same schema

> sqlplus CMS_COND_42X_GEOMETRY@d3r

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 10 11:36:59 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management and Real Application Testing options

SQL> set serveroutput on;
SQL> set lines 10000;
SQL> exec cerndb_dpuser.show_log
-----------------------------------------------------------------------------------
IMPORT LOG OF CMS_COND_42X_GEO6270849673670@TEST11G :
-----------------------------------------------------------------------------------
Starting "DPMGR"."CMS_COND_42X_GEO6270849673670":
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.75 MB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"CMS_COND_42X_GEOMETRY" already exists
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "CMS_COND_42X_GEOMETRY"."ORA_C_PCALOGEOMETRY"       7 rows
. . imported "CMS_COND_42X_GEOMETRY"."ORA_CLASS_VERSION"       4 rows
. . imported "CMS_COND_42X_GEOMETRY"."ORA_CONTAINER"          2 rows
. . imported "CMS_COND_42X_GEOMETRY"."ORA_C_COND_IOVSEQUENCE"       7 rows
. . imported "CMS_COND_42X_GEOMETRY"."ORA_C_COND_IOVSEQU_A0"       7 rows
. . imported"CMS_COND_42X_GEOMETRY"."ORA_DB"                 1 rows
. . imported "CMS_COND_42X_GEOMETRY"."ORA_MAPPING_ELEMENT"      20 rows
. . imported "CMS_COND_42X_GEOMETRY"."ORA_MAPPING_VERSION"       2 rows
. . imported "CMS_COND_42X_GEOMETRY"."ORA_NAMING_SERVICE"       7 rows
. . imported "CMS_COND_42X_GEOMETRY"."ORA_SEQUENCE"           4 rows
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/EVENT/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "DPMGR"."CMS_COND_42X_GEO6270849673670" completed with 1 error(s) at 16:34:46

Partial schema copy

Follow all steps from Full schema copy example except point 4 where cerndb_dpuser.cp_schema procedure call has to have additional parameters: *a) Excluding schema's indexes (except PK) and database links and statistics

cerndb_dpuser.cp_schema('test11g',exclude=>'INDEX;DB_LINK;STATISTICS');

*b) Excluding schema's indexes (except PK) and tables with name ATABLE and BTABLE

cerndb_dpuser.cp_schema('test11g',exclude=>'INDEX;TABLE:IN (''ATABLE'',''BTABLE'')');

*c) Excluding schema's JOBs (DBMS_JOB only), PL/SQL packages, procedures and functions

cerndb_dpuser.cp_schema('test11g',exclude=>'JOB;PACKAGE,PROCEDURE,FUNCTION');

*c) Excluding schema's SCHEDULER JOBs (only)

cerndb_dpuser.cp_schema('test11g',exclude=>'PROCOBJ:IN (SELECT NAME FROM sys.OBJ$ WHERE TYPE# IN (47,48,66,67,68,69,71,72,74))');

*e) Copy tables that starts with string TEST:

cerndb_dpuser.cp_schema('test11g',include=>'TABLE: like ''TEST%'' ');

*f) Copy tables that starts with string TEST and exclude constraints and statistics on them:

cerndb_dpuser.cp_schema('test11g',include=>'TABLE: like ''TEST%'' ', exclude=> 'TABLE/CONSTRAINT;STATISTICS');

Displaying user's jobs history

1) login to the schema on source database

> sqlplus CMS_COND_42X_GEOMETRY@d3r

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 10 11:36:59 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management and Real Application Testing options

SQL>

2) query user_dp_jobs view for details about jobs run. E.g displaying jobs from last month:

SQL> col ACTION_TYPE format a10
SQL> col DUMP_FILES format a10
SQL> col VIA_DBLINK format a10
SQL> col FLASHBACK_TIME format a30
SQL> col TARGET_DATABASE format a10
SQL> col JOB_NAME format a40

SQL> select * from user_dp_jobs where start_time>sysdate-30;

JOB_NAME                                 ACTION_TYP TARGET_DAT START_TIME           END_TIME             FLASHBACK_TIME                 VIA_DBLINK DUMP_FILES
---------------------------------------- ---------- ---------- -------------------- -------------------- ------------------------------ ---------- ----------
CMS_COND_42X_GEO6270849640501            COPY       test11g    06-JUL-2012 15:22:18 06-JUL-2012 15:22:52 06-JUL-12 03.22.17.000000 PM   TRUE
CMS_COND_42X_GEO6270849658018            COPY       test11g    06-JUL-2012 15:42:14 06-JUL-2012 15:42:35 06-JUL-12 03.42.12.000000 PM   TRUE
CMS_COND_42X_GEO6270849662162            COPY       test11g    06-JUL-2012 15:43:18 06-JUL-2012 15:43:49 06-JUL-12 03.43.16.000000 PM   TRUE
CMS_COND_42X_GEO6270849673670            COPY       test11g    06-JUL-2012 16:34:34 06-JUL-2012 16:34:47 05-JUL-12 04.34.32.000000 PM   TRUE

3) check log for historical jobs using _show_log_

SQL> set serveroutput on
SQL> set lines 10000
SQL> exec cerndb_dpuser.show_log('CMS_COND_42X_GEO6270849640501');
-----------------------------------------------------------------------------------
IMPORT LOG OF CMS_COND_42X_GEO6270849640501@TEST11G :
-----------------------------------------------------------------------------------
Starting "DPMGR"."CMS_COND_42X_GEO6270849640501":
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.75 MB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"CMS_COND_42X_GEOMETRY" already exists
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "CMS_COND_42X_GEOMETRY"."ORA_C_PCALOGEOMETRY"       7 rows
. . imported "CMS_COND_42X_GEOMETRY"."ORA_CLASS_VERSION"       4 rows
. . imported "CMS_COND_42X_GEOMETRY"."ORA_CONTAINER"          2 rows
. . imported "CMS_COND_42X_GEOMETRY"."ORA_C_COND_IOVSEQUENCE"       7 rows
. . imported "CMS_COND_42X_GEOMETRY"."ORA_C_COND_IOVSEQU_A0"       7 rows
. . imported "CMS_COND_42X_GEOMETRY"."ORA_DB"                 1 rows
. . imported "CMS_COND_42X_GEOMETRY"."ORA_MAPPING_ELEMENT"      20 rows
. . imported "CMS_COND_42X_GEOMETRY"."ORA_MAPPING_VERSION"       2 rows
. . imported "CMS_COND_42X_GEOMETRY"."ORA_NAMING_SERVICE"       7 rows
. . imported "CMS_COND_42X_GEOMETRY"."ORA_SEQUENCE"           4 rows
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/EVENT/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "DPMGR"."CMS_COND_42X_GEO6270849640501" completed with 1 error(s) at 15:22:51


PL/SQL procedure successfully completed.

• datapump_objetc_types.txt: datapump_objetc_types.txt