How to migrate Oracle Database on Premises to Oracle AWS RDS instance with imp utility.

How to migrate the  on premises to oracle rds instance with imp utility.

Prerequisites :
endpoint of the rds instance : orcl.cjbogiknindg.ap-west-1.rds.amazonaws.com
install the sql client(12.1.0.2.0) with same oracle database version 12.1.0.2.0
Note * Client version should be same as rds oracle version.


Step 1 configure the tns in ec2 instance
D:\app\client\Administrator\product\12.1.0\client_1\network\admin

create a tnsnames.ora file with below mention information. Here host name is your RDS endpoint and sid is your database name
ORCL =
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=orcl.cjbogiknindg.ap-west-1.rds.amazonaws.com)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
 (CONNECT_DATA=(SID=orcl))
   
    )
  )
Microsoft Windows [Version 10.0.14393]
(c) 2016 Microsoft Corporation. All rights reserved.

Step 2 after configure the tnsnames.ora file then check the tnsping.

C:\Users\Administrator>tnsping orcl

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 - Production on 16-JUN-2017 15:39:45

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
D:\app\client\Administrator\product\12.1.0\client_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=orcl.cjbogiknindg.ap-west-1.rds.amazonaws.com) (PORT=1521)) (CONNECT_DATA= (SERVER=dedicated) (CONNECT_DATA=(SID=orcl))))
OK (90 msec)
Step 3 Connect with RDS instance and create user , tablespace as per need.
 connect with master user of rds instance.

Syntax to connect with oracle database.

sqlplus 'orcl@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl.cjbogiknindg.ap-west-1.rds.amazonaws.com)(PORT=1521))(CONNECT_DATA=(SID=orcl)))'

sqlplus orcl/****@orcl.cjbogiknindg.ap-west-1.rds.amazonaws.com:1521/cokedb

create a user as per need.

3.1 create user abhi identified by oven default tablespace users temporary tablespace temp;

3.2 alter user abhi quota unlimited on users;

step 4 Copy the backup of oracle database to EC2 instance and import the database backup on the RDS instance with the help of IMPORT Utility.

syntax imp user/pass@host:port/dbname file=backup_location fromuser=oldschemauser  touser=newschemauser


C:\Users\Administrator>imp orcl/*****@orcl.cjbogiknindg.ap-west-1.rds.amazonaws.com:1521/orcl file=D:\Backup\test16Jun2017.dmp fromuser=thakur touser=abhi

Import: Release 12.1.0.2.0 - Production on Sun Jun 18 18:51:23 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

Export file created by EXPORT:V12.01.00 via conventional path

Warning: the objects were exported by abhi, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing test's objects into test
. . importing table                       "TEAM_AREA"         17 rows imported
. . importing table                 "T_PARAM"          2 rows imported
About to enable constraints...
Import terminated successfully with warnings.

Step 4 After Import the database check the tables in the database.
       Connect with abhi and check the data.

sqlplus abhi/****@orcl.cjbogiknindg.ap-west-1.rds.amazonaws.com:1521/orcl


C:\Users\Administrator>sqlplus abhi/****@orcl.cjbogiknindg.ap-west-1.rds.amazonaws.com:1521/orcl

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 19 18:17:05 2017

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

Last Successful login time: Mon Jun 19 2017 18:14:47 +05:30

Connected to:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

SQL> show user
USER is "ABHI"
SQL>

SQL> select count (*) from tab;

  COUNT(*)
----------
       2