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;