EXP/IMP data migration

EXPIMP is a good migration tool for databases with small data volumes. The data migration process is as follows, which involves two major steps: export and import.

EXP/IMP is a good migration tool for databases with small data volumes. The data migration process is as follows, which involves two major steps: export and import.

Project Background:

The original database server runs on the HP DL388G7 server with 32 GB memory. Due to business growth, the memory is tight, and server hardware faults occur from time to time. As a single instance and single server, there is a single point of discovery, So we plan to take some measures to improve it:

1) upgrade Server Memory

2) set up two servers at the server operating system level

3) migrate database data to the new server

I have previously written an article about upgrading the server memory, entitled "considerations for upgrading the database server memory". The link is as follows:

----------------------------------------------------------

EXP/IMP is a good migration tool for databases with small data volumes. The data migration process is as follows, which involves two major steps: export and import.

1. Export Process 1. Prepare the export script:

More/exp20130118/exp. sh

Date

Expdp bv/bv32EBAI2 DIRECTORY = DATA_PUMP_DIR2 DUMPFILE = exp_2013-1-19.dmp LOGFILE = exp_2013-1-19.log SCHEMAS = bv

Date

2. Create an export directory:

The exp target directory is stored locally or in which directory? Create directory?

SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH

SYS DATA_PUMP_DIR/Oracle/product/10.2/db/rdbms/log/

SYS DATA_PUMP_DIR1/exp

SYS ORACLE_OCM_CONFIG_DIR/oracle/product/10.2/db/Cr/state

Create directory data_pump_dir2 as '/exp20130118 ';

3. Export the dmp File

Find a busy time and execute the export script.

Ii. Import process 1. Create tablespaces and users

Note:

1) Most articles on google said that if you use exp/imp to migrate data, you only need to create a user on the new server for import, however, when I create only one user for the first import, a large number of errors will be reported. Finally, I will create all the users. Let me know, thank you.

2) I am a little lazy here. I directly use toad to copy the statement for creating a user, and even the encrypted password is available for convenience.

Copy the creation table space and user script to Toad and paste them directly for initialization preparation. The tablespace mainly refers to the tablespaces related to users, which can be queried using SQL statements. Users have to read them one by one.

1. 1. Create a parameter file

Create profile LIMIT_SESSION LIMIT

SESSIONS_PER_USER 30

CPU_PER_SESSION DEFAULT

CPU_PER_CALL DEFAULT

CONNECT_TIME DEFAULT

IDLE_TIME DEFAULT

LOGICAL_READS_PER_SESSION DEFAULT

LOGICAL_READS_PER_CALL DEFAULT

COMPOSITE_LIMIT DEFAULT

PRIVATE_SGA DEFAULT

FAILED_LOGIN_ATTEMPTS DEFAULT

PASSWORD_LIFE_TIME DEFAULT

PASSWORD_REUSE_TIME DEFAULT

PASSWORD_REUSE_MAX DEFAULT

PASSWORD_LOCK_TIME DEFAULT

PASSWORD_GRACE_TIME DEFAULT

PASSWORD_VERIFY_FUNCTION DEFAULT;