Data migration steps of exp and imp of Oracle
Migrate AA database to BB database
1. AA database to view the user's default tablespace
Select username,default_tablespace from dba_users where username='AA_Username'
2. View the role of the user
Select * from user_role_privs
3. The location of the data file of the BB database
SQL > select name from v$datafile
NAME
D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ CCOMS\ SYSTEM01.DBF
D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ CCOMS\ UNDOTBS01.DBF
D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ CCOMS\ SYSAUX01.DBF
D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ CCOMS\ USERS01.DBF
D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ CCOMS\ EXAMPLE01.DBF
4. BB database creates default tablespaces for users, and data files are automatically extended.
SQL > create tablespace BB_default_space
2 datafile'D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ CCOMS\ BB_default_space.DBF'
3 size 5M autoextend on
The tablespace has been created.
5. Create a new user in BB database
SQL > create user BB_Username
2 identified by BB_Username000
3 default tablespace AA_default_space
The user has been created.
6. BB database is authorized to new users.
SQL > grant connect,resource,dba to BB_Username
Authorization successful.
7. BB database configuration listener.ora and tnsnames.ora
Configure ip so that it can be accessed from the outside
Listener.ora file:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\ oracle\ product\ 10.2.0\ db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP) (HOST = BB_IP) (PORT = 1521))
)
)
Tnsnames.ora file:
BB_Username =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = BB_IP) (PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = BB_Username)
)
)
Modify the listener configuration file. You need to restart the listener.
Turn off the listener: lsnrctl stop
Restart the listener: lsnrctl start
8. Configure tnsnames in another database (configured to facilitate the use of plsql). Ora
BB_Username =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = BB_IP) (PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = BB_Username)
)
)
9. Under which tablespaces are used to query users in AA database, the corresponding tablespaces are established.
Select distinct tablespace_name from user_segments
Where tablespace_name not in ('USERS','USERS02')
10. Generate the following script and import it into the BB database with plsql
CREATE TABLESPACE DRILL_LOB_SPACE DATAFILE'D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ BB_Username\ TEST_LOB_SPACE.DBF' size 5m autoextend on
CREATE TABLESPACE DH_LOB_SPACE DATAFILE'D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ BB_Username\ _ LOB_SPACE DATAFILE.DBF' size 5m autoextend on
CREATE TABLESPACE TEST_NORM_SPACE DATAFILE'D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ BB_Username\ TEST_NORM_SPACE DATAFILE.DBF' size 5m autoextend on
CREATE TABLESPACE LOG_LOB_SPACE DATAFILE'D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ BB_Username\ LOG_LOB_SPACE DATAFILE.DBF' size 5m autoextend on
CREATE TABLESPACE MLOG_NORM_SPACE DATAFILE'D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ BB_Username\ MLOG_NORM_SPACE.DBF' size 5m autoextend on
CREATE TABLESPACE MLOG_LOB_SPACE DATAFILE'D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ BB_Username\ MLOG_LOB_SPACE.DBF' size 5m autoextend on
CREATE TABLESPACE LOG_NORM_SPACE DATAFILE'D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ BB_Username\ LOG_NORM_SPACE.DBF' size 5m autoextend on
CREATE TABLESPACE HHH_NORM_SPACE DATAFILE'D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ BB_Username\ HHH_NORM_SPACE DATAFILE.DBF' size 5m autoextend on
CREATE TABLESPACE GPE_NORM_SPACE DATAFILE'D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ BB_Username\ HHDS_NORM_SPACE DATAFIL.DBF' size 5m autoextend on
CREATE TABLESPACE HHDS_LOB_SPACE DATAFILE'D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ BB_Username\ HHDS_LOB_SPACE DATAFILE.DBF' size 5m autoextend on
CREATE TABLESPACE PUB_NORM_SPACE DATAFILE'D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ BB_Username\ PUB_NORM_SPACE DATAFILE.DBF' size 5m autoextend on
CREATE TABLESPACE PUB_LOB_SPACE DATAFILE'D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ BB_Username\ BBII_LOB_SPACE DATAFILE.DBF' size 5m autoextend on
11. Export data from AA database
Exp AA_Username/AA_Password@AA_Database file=F:\ AA_Databaseoraclebak\ AA_Database14.dump log=F:\ AA_Databaseoraclebak\ AA_Database14.log
12. Import data from BB database: no user authorization is required
Imp BB_Username/BB_Password@BB_Database FROMUSER=AA_Username TOUSER=BB_Username ignore=yes grants=no file=F:\ AA_Databaseoraclebak\ AA_Database14.dump log=F:\ AA_Databaseoraclebak\ IMP20100121BB_Username.log
