Oracle Db Export and Import in Windows

1: Export db from oracle server

exp SYSTEM/password@127.0.0.1:1521/XE owner=SYSTEM file=C:\Temp\test.dmp

SYSTEM is db admin user name

password is db admin user pw

127.0.0.1 is local address, 1521 is listen port, XE is service name

test.dmp is exported file

 

2: Make sure the new PC installed Oracle DB server first, and try below command do import

imp comfort/password@XE file=C:\Temp\XEPDB1.dmp log=C:\Temp\impXPDB1.log full=y

If hit any error here, eg: user name is not created.

ORA-65096: invalid common user or role name | SOLUTION with TERMINAL

So we start to create a user named comfort with below command:

step 1:
SYS /AS SYSDBA
step 2:
ALTER SESSION SET"_ORACLE_SCRIPT"=TRUE;
step 3(user name: comfort, pw: password):
CREATE USER comfort IDENTIFIED BY password;
step 4("comfort" is user name):
grant dba to comfort;

3: do import again with same command(use new create account):

imp comfort/password@XE file=C:\Temp\XEPDB1.dmp log=C:\Temp\impXPDB1.log full=y

 

4: connect to db with new account "comfort" and check db data

That is all for Oracle DB migration

 

Some additional infor here:

change default Oracle db listen port:

1. From your PC, click Start | Run
2. In the Run dialog box, type "regedit" and press Return
3. The registry will now be displayed
4. Expand the folder called [HKEY_LOCAL_MACHINE]\SOFTWARE\ORACLE

From my labtop i can find listener.ora & tnsnames.ora, change port here:
C:\app\SkyR\product\18.0.0\dbhomeXE\network\admin

Modify oracle db service name
run sqlplus from command windows, key in user name and pw: comfort/password
SQL> show parameter service_name;
SQL> alter system set service_names='XEPDB1';