Migrate from On-Premise 11g (11.2.0.4) to Oracle 18c Autonomous Transaction Processing (ATP) Database using Export/Import EXPDP IMPDP

  • Create Oracle Autonomous Transaction Processing Database(ATP) in your Oracle Cloud Account.
  • Create wallet and down the wallet to your bastion host from where you will run the import command.
  • Make sure you create a folder for the wallet, unzip the wallet zip file and set TNS_ADMIN parameter
PATH=$PATH:$HOME/.local/bin:$HOME/bin:/u01/app/oracle/18.3.0/bin
ORACLE_HOME=/u01/app/oracle/18.3.0
ORACLE_BASE=/u01/app/oracle
TNS_ADMIN=/home/opc/orawallet
export ORACLE_HOME ORACLE_BASE TNS_ADMIN

cd /home/opc/orawallet
unzip Wallet_easyoradba.zip

sqlplus admin/**********@easyoradba_high

  • Export Schema from 11g Source Database using expdp utility
expdp system@swx directory=export_dir logfile=export.log dumpfile=expswx.dmp schemas=swx parallel=16
  • Create a Bucket in Oracle Cloud Account Object Storage, lets call it ‘easyoradba-migrate’
  • Upload DMP file from Step 2 to your Oracle Cloud Bucket file name eg “expswx.dmp”
  • Create Pre-Authenticate Request to get read access on the object expswx.dmp, this is required to run the import in ATP
  • Logback in to Oracle Cloud ATP with Admin user and Create a new user called ‘SWX’ and grant few privileges to it
create user swx IDENTIFIED by Abcde1234$## ;
grant create session to swx;
grant dwrole to swx;
GRANT UNLIMITED TABLESPACE TO swx;
  • Create Credentials to run the Import using DBMS_CLOUD package
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'SWX',
username => 'SWX',
password => '*********'
);
END;
/
-- If you need to re-create, drop the old one and add it again --
BEGIN
DBMS_CLOUD.drop_credential(credential_name => 'ABC');
END;
/
  • Run the Import using impdp command
impdp admin@easyoradba_high directory=data_pump_dir credential=swx dumpfile= https://objectstorage.ap-sydney-1.oraclecloud.com/p/YcRteyozj7l7EpFfu6Zr1TWjw7mYeM97JBL96VASXsM/n/sdpxrcjhpsnk/b/easyoradba-migrate/o/exp_swx.dmp parallel=16 remap_tablespace=swx:data encryption_pwd_prompt=yes partition_options=merge transform=segment_attributes:n transform=dwcs_cvt_iots:y exclude=cluster,indextype,materialized_view,materialized_view_log,materialized_zonemap,db_link schemas=swx 

It will ask for the encryption password which we created earlier

The import is now completed and all the objects of on-premise Source schema SWX are now under SWX schema in Target Oracle ATP

Category: CloudDatabaseOracle

Tags:

Leave a Reply

Article by: Shadab Mohammad