How to run the autobcpdpORA.pl script to use Oracle Data Pump
Article ID: 203737
Updated On:
Products
Issue/Introduction
The standard $AUTOSYS/dbobj/ORA/autobcpORA.pl script uses the Oracle Client's native 'imp' and 'exp' utilities.
Oracle Data Pump, using 'impdp' and 'expdp', is much faster. For this, you can use the $AUTOSYS/dbobj/autobcpdpORA.pl script.
Environment
Oracle 12 or higher
Resolution
These are the steps that you need to ask your Oracle DBA to perform first:
1) Please make sure the tablespace names for your Primary and Secondary DBs are the same (default names are AEDB_DATA and AEDB_INDEX).
Note: If the tablespace names are not the same, you may need to drop the DB tables, the 'aedbadmin' and 'autosys' DB users and the existing DATA and INDEX tablespaces on your secondary DB and then run the $AUTOSYS/dbobj/ORA/CreateAEDB.pl script to recreate the tablespaces with the same names. This also recreates the tables, users and roles.
2) Create a database link on the Primary DB server, the db link will point to the Secondary DB server:
CREATE PUBLIC DATABASE LINK autosys_link CONNECT TO aedbadmin IDENTIFIED BY <password> USING '<secondary_db_name>';
CREATE OR REPLACE DIRECTORY autobcpdump AS '<path_to_oradata>';
GRANT READ, WRITE ON DIRECTORY autobcpdump TO aedbadmin;
Test the db link, this should work:
select count(*) FROM ujo_alamode@autosys_link;
3) Create a database link on the Secondary DB server , the db link will point to the Primary DB server:
CREATE PUBLIC DATABASE LINK autosys_link CONNECT TO aedbadmin IDENTIFIED BY <password> USING '<primary_db_name>';
CREATE OR REPLACE DIRECTORY autobcpdump AS '<path_to_oradata>';
GRANT READ, WRITE ON DIRECTORY autobcpdump TO aedbadmin;
Test the db link, this should work:
select count(*) FROM ujo_alamode@autosys_link;
4) Once the above is done, you can run the autobcpdp script :
cd $AUTOSYS/dbobj/ORA
perl autobcpdpORA.pl
=======
Example
=======
Primary DB name: MN1AEDB1
Secondary DB name: MN1AEDB2
1) On the Primary DB server:
$ export ORACLE_SID=MN1AEDB1
$ sqlplus /nolog
SQL*Plus: Release 12.2.0.1.0 Production on Mon Nov 16 06:35:15 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> CREATE PUBLIC DATABASE LINK autosys_link CONNECT TO aedbadmin IDENTIFIED BY aedbadmin USING 'MN1AEDB2';
Database link created.
SQL> CREATE OR REPLACE DIRECTORY autobcpdump AS '/app/oracle/oradata';
Directory created.
SQL> GRANT READ, WRITE ON DIRECTORY autobcpdump TO aedbadmin;
Grant succeeded.
SQL> select count(*) FROM ujo_alamode@autosys_link;
COUNT(*)
----------
30
SQL>
2) On the Secondary DB server:
$ export ORACLE_SID=MN1AEDB2
$ sqlplus /nolog
SQL*Plus: Release 12.2.0.1.0 Production on Mon Nov 16 06:31:00 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> CREATE PUBLIC DATABASE LINK autosys_link CONNECT TO aedbadmin IDENTIFIED BY aedbadmin USING 'MN1AEDB1';
Database link created.
SQL> CREATE OR REPLACE DIRECTORY autobcpdump AS '/app/oracle/oradata';
Directory created.
SQL> GRANT READ, WRITE ON DIRECTORY autobcpdump TO aedbadmin;
Grant succeeded.
SQL> select count(*) FROM ujo_alamode@autosys_link;
COUNT(*)
----------
30
SQL>
3) Run 'autobcpdpORA.pl' on the Primary AutoSys server:
$ cd $AUTOSYS/dbobj/ORA
$ perl autobcpORAdp.pl
******** IMPORTANT NOTE **********
This utility will import data to destination server using network link.
Make sure you have created network link in target server pointing to source.
Also, make sure an Oracle directory is created on target server pointing to
a dump location. Data on target instance will be erased.
***********************************
autobcpORAdp: Enter the mode of database authentication (Certificate:1 | [Password:0]) > 0
autobcpORAdp: DEST INSTANCE [AEDB]? > MN1AEDB2
autobcpORAdp: DEST aedbadmin's password [aedbadmin]? >
autobcpORAdp: Network link to SOURCE in DEST INSTANCE [db_link]? > autosys_link
autobcpORAdp: Oracle Database dump directory name in DEST INSTANCE [autobcpdump]? >
autobcpORAdp: Path for Oracle (i.e. ORACLE_HOME) [/usr/lib/oracle/12.2/client]? >
autobcpORAdp: Current data in MN1AEDB2 server will be deleted.
autobcpORAdp: Are you sure (y|n)? > y
autobcpORAdp: Trying to access the destination table (ujo_alamode) using network link autosys_link
autobcpORAdp: Trying to access the destination Data Pump Directory with AUTOBCPDUMP
autobcpORAdp: Databases and Link OK
autobcpORAdp: Importing data to MN1AEDB2 from Source using autosys_link...
index returned is not zero
Before: /usr/lib/oracle/12.2/client\bin\impdp
After: /usr/lib/oracle/12.2/client/bin/impdp
Import: Release 12.2.0.1.0 - Production on Mon Nov 16 07:23:55 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "AEDBADMIN"."SYS_IMPORT_SCHEMA_01": aedbadmin/********@MN1AEDB2 NETWORK_LINK=autosys_link DIRECTORY=autobcpdump TABLE_EXISTS_ACTION=REPLACE exclude=view,package,function,procedure,db_link,user,statistics,type_spec
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 123.0 MB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "AEDBADMIN"."UJO_JOB" 151213 rows
. . imported "AEDBADMIN"."UJO_JOB_STATUS" 151209 rows
. . imported "AEDBADMIN"."UJO_SCHED_INFO" 151212 rows
. . imported "AEDBADMIN"."UJO_PROC_EVENT" 40834 rows
. . imported "AEDBADMIN"."UJO_COMMAND_JOB" 151152 rows
. . imported "AEDBADMIN"."UJO_JOB_TREE" 151209 rows
. . imported "AEDBADMIN"."UJO_EXTENDED_JOBRUN_INFO" 20338 rows
. . imported "AEDBADMIN"."UJO_JOB_RUNS" 10192 rows
. . imported "AEDBADMIN"."UJO_REP_HOURLY" 10707 rows
. . imported "AEDBADMIN"."UJO_GLOBBLOB" 1 rows
. . imported "AEDBADMIN"."UJO_BLOBS" 1 rows
. . imported "AEDBADMIN"."UJO_JOBBLOB" 0 rows
. . imported "AEDBADMIN"."UJO_META_PROPERTIES" 1185 rows
. . imported "AEDBADMIN"."UJO_TELEMETRY_DATA" 367 rows
. . imported "AEDBADMIN"."UJO_AFM" 2 rows
. . imported "AEDBADMIN"."UJO_AFM_STRINGS" 0 rows
. . imported "AEDBADMIN"."UJO_AGENT_ALIAS" 7 rows
. . imported "AEDBADMIN"."UJO_ALAMODE" 30 rows
. . imported "AEDBADMIN"."UJO_ALARM" 28 rows
. . imported "AEDBADMIN"."UJO_ASBNODE" 0 rows
. . imported "AEDBADMIN"."UJO_ASEXT_CONFIG" 1 rows
. . imported "AEDBADMIN"."UJO_AUDIT_ALAMODE" 0 rows
. . imported "AEDBADMIN"."UJO_AUDIT_INFO" 10 rows
. . imported "AEDBADMIN"."UJO_AUDIT_MSG" 72 rows
. . imported "AEDBADMIN"."UJO_AVG_JOB_RUNS" 1199 rows
. . imported "AEDBADMIN"."UJO_CALENDAR" 2639 rows
. . imported "AEDBADMIN"."UJO_CALENDAR_DESC" 1 rows
. . imported "AEDBADMIN"."UJO_CHASE" 35 rows
. . imported "AEDBADMIN"."UJO_CHKPNT_RSTART" 0 rows
. . imported "AEDBADMIN"."UJO_COMM_RECV_SEQ" 125 rows
. . imported "AEDBADMIN"."UJO_COMM_SEND_ALIAS" 26 rows
. . imported "AEDBADMIN"."UJO_COMM_SEND_NODES" 33 rows
. . imported "AEDBADMIN"."UJO_COMM_SEND_SEQ" 53 rows
. . imported "AEDBADMIN"."UJO_COMM_SEND_SEQ_LOCK" 0 rows
. . imported "AEDBADMIN"."UJO_CONFIG" 1 rows
. . imported "AEDBADMIN"."UJO_CONNECTIONPROFILE" 0 rows
. . imported "AEDBADMIN"."UJO_CRED" 2 rows
. . imported "AEDBADMIN"."UJO_CYCLE" 0 rows
. . imported "AEDBADMIN"."UJO_EVENT" 11 rows
. . imported "AEDBADMIN"."UJO_EVENT2" 0 rows
. . imported "AEDBADMIN"."UJO_EXT_CALENDAR" 0 rows
. . imported "AEDBADMIN"."UJO_EXT_EVENT" 0 rows
. . imported "AEDBADMIN"."UJO_EXT_JOB" 0 rows
. . imported "AEDBADMIN"."UJO_FILE_WATCH_JOB" 3 rows
. . imported "AEDBADMIN"."UJO_FTP_JOB" 3 rows
. . imported "AEDBADMIN"."UJO_GENERIC_JOB" 0 rows
. . imported "AEDBADMIN"."UJO_GLOB" 8 rows
. . imported "AEDBADMIN"."UJO_HA_PROCESS" 3 rows
. . imported "AEDBADMIN"."UJO_HDFS_JOB" 0 rows
. . imported "AEDBADMIN"."UJO_HIVE_JOB" 0 rows
. . imported "AEDBADMIN"."UJO_I5_JOB" 0 rows
. . imported "AEDBADMIN"."UJO_INFORMATICA_JOB" 0 rows
. . imported "AEDBADMIN"."UJO_JOBTYPE" 0 rows
. . imported "AEDBADMIN"."UJO_JOB_COND" 2000 rows
. . imported "AEDBADMIN"."UJO_JOB_RESOURCE_DEP" 0 rows
. . imported "AEDBADMIN"."UJO_KEYMASTER" 3 rows
. . imported "AEDBADMIN"."UJO_LAST_EOID_COUNTER" 1 rows
. . imported "AEDBADMIN"."UJO_MACHINE" 8 rows
. . imported "AEDBADMIN"."UJO_MA_PROCESS" 3 rows
. . imported "AEDBADMIN"."UJO_MICRO_FOCUS_JOB" 0 rows
. . imported "AEDBADMIN"."UJO_MONBRO" 0 rows
. . imported "AEDBADMIN"."UJO_MONITOR_OBJECT_JOB" 0 rows
. . imported "AEDBADMIN"."UJO_MONITOR_WINEVENT_LOG" 0 rows
. . imported "AEDBADMIN"."UJO_MSG_ACK" 0 rows
. . imported "AEDBADMIN"."UJO_NEXT_OID" 8 rows
. . imported "AEDBADMIN"."UJO_OOZIE_JOB" 0 rows
. . imported "AEDBADMIN"."UJO_ORAAPPS" 0 rows
. . imported "AEDBADMIN"."UJO_ORAAPPS_PROPS" 0 rows
. . imported "AEDBADMIN"."UJO_ORAAPPS_STEPS" 0 rows
. . imported "AEDBADMIN"."UJO_PATCH" 0 rows
. . imported "AEDBADMIN"."UJO_PEOPLESOFT_JOB" 0 rows
. . imported "AEDBADMIN"."UJO_PIG_JOB" 0 rows
. . imported "AEDBADMIN"."UJO_REAL_RESOURCE" 28 rows
. . imported "AEDBADMIN"."UJO_REP_DAILY" 448 rows
. . imported "AEDBADMIN"."UJO_REP_MONTHLY" 14 rows
. . imported "AEDBADMIN"."UJO_REP_WEEKLY" 65 rows
. . imported "AEDBADMIN"."UJO_REQ_JOB" 1 rows
. . imported "AEDBADMIN"."UJO_RESTART" 0 rows
. . imported "AEDBADMIN"."UJO_RESWAIT_QUE" 0 rows
. . imported "AEDBADMIN"."UJO_SAP_ARCSPEC" 0 rows
. . imported "AEDBADMIN"."UJO_SAP_INFODETAIL" 0 rows
. . imported "AEDBADMIN"."UJO_SAP_JOB" 0 rows
. . imported "AEDBADMIN"."UJO_SAP_JOBSTEP" 0 rows
. . imported "AEDBADMIN"."UJO_SAP_PRTSPEC" 0 rows
. . imported "AEDBADMIN"."UJO_SAP_RECIPIENT" 0 rows
. . imported "AEDBADMIN"."UJO_SERVICE_DESK" 0 rows
. . imported "AEDBADMIN"."UJO_SNMP_JOB" 0 rows
. . imported "AEDBADMIN"."UJO_SPARK_JOB" 0 rows
. . imported "AEDBADMIN"."UJO_SQLAGENT_JOB" 0 rows
. . imported "AEDBADMIN"."UJO_SQL_JOB" 0 rows
. . imported "AEDBADMIN"."UJO_SQL_PROC_PARMS" 0 rows
. . imported "AEDBADMIN"."UJO_SQOOP_JOB" 0 rows
. . imported "AEDBADMIN"."UJO_STRINGS" 0 rows
. . imported "AEDBADMIN"."UJO_SYS_HA_STATE" 1 rows
. . imported "AEDBADMIN"."UJO_TIMEZONES" 375 rows
. . imported "AEDBADMIN"."UJO_UNINOTIFY" 0 rows
. . imported "AEDBADMIN"."UJO_VIRT_RESOURCE" 0 rows
. . imported "AEDBADMIN"."UJO_VIRT_RESOURCE_LOOKUP" 0 rows
. . imported "AEDBADMIN"."UJO_VIRT_RESOURCE_STATUS" 0 rows
. . imported "AEDBADMIN"."UJO_WAIT_QUE" 0 rows
. . imported "AEDBADMIN"."UJO_WEB_SERVICES" 0 rows
. . imported "AEDBADMIN"."UJO_WEB_SERVICES2" 0 rows
. . imported "AEDBADMIN"."UJO_WOL_JOB" 0 rows
. . imported "AEDBADMIN"."UJO_WORKFLOW" 1 rows
. . imported "AEDBADMIN"."UJO_WS_CRITERIA" 0 rows
. . imported "AEDBADMIN"."UJO_WS_INFO" 0 rows
. . imported "AEDBADMIN"."UJO_WS_PARM" 0 rows
. . imported "AEDBADMIN"."UJO_WS_SECURITY" 0 rows
. . imported "AEDBADMIN"."UJO_ZOS_CONDCODES" 0 rows
. . imported "AEDBADMIN"."UJO_ZOS_DSN_TRIGGER" 0 rows
. . imported "AEDBADMIN"."UJO_ZOS_JOB" 0 rows
. . imported "AEDBADMIN"."UJO_INTCODES" 114 rows
. . imported "AEDBADMIN"."UJO_META_AFM_TYPES" 11 rows
. . imported "AEDBADMIN"."UJO_META_ENUMERATIONS" 587 rows
. . imported "AEDBADMIN"."UJO_META_PARSE_EXTRUNTIME" 7 rows
. . imported "AEDBADMIN"."UJO_META_PARSE_KEYWORDS" 7 rows
. . imported "AEDBADMIN"."UJO_META_PRESENTATION" 30 rows
. . imported "AEDBADMIN"."UJO_META_RESTART_AFM_TYPES" 2 rows
. . imported "AEDBADMIN"."UJO_META_ROOT_KEYWORDS" 38 rows
. . imported "AEDBADMIN"."UJO_META_RULES" 548 rows
. . imported "AEDBADMIN"."UJO_META_TYPES" 124 rows
. . imported "AEDBADMIN"."UJO_META_WS_ELEMENTS" 53 rows
. . imported "AEDBADMIN"."UJO_TEMP_JOBRUNS" 0 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "AEDBADMIN"."SYS_IMPORT_SCHEMA_01" successfully completed at Mon Nov 16 07:24:49 2020 elapsed 0 00:00:57
autobcpORAdp: Complete.
Feedback
