Oracle database migration
PL/SQL Release 11.2.0.4.0-Production export version, database address 10.36.40.53
PL/SQL Release 11.2.0.4.0-Production imported version, database address 10.36.40.74
10.36.40.53 Database address to be migrated
1. Create a directory
-- create a directory
Cd / home/oracle
Mkdir bak20170511
two。 Add permission
Chmod o+wr / home/oracle/bak20170511
3. Shell oracle permission, log in to dba
Su-oracle
Sqlplus / as sysdba
4. Give the created directory read and write permissions to the database users who need to migrate
Create or replace directory dumpdir as'/ home/oracle/bak20170511'
-Authorization
Grant read,write on directory dumpdir to mkt_standard
5. Query the current user's tablespace, temporary tablespace
Current username tablespace temporary tablespace
Select t.USERNAME,t.DEFAULT_TABLESPACE,t.TEMPORARY_TABLESPACE from user_users t
User name tablespace temporary tablespace
MKT_STANDARD MKT_STANDARD_DATA MKT_STANDARD_DATA_TEMP
6. Query the current database version
Select * from v$version
Version information
TNS for Linux: Version 11.2.0.4.0-Production
7. Exit sql command line mode and enter oracle permissions
Exit ()
7. Data export
User name and password sid import database version
Expdp mkt_standard/mkt_standard@10.36.40.53/orcl directory=dumpdir dumpfile=mkt_standard.dmp logfile=mkt_standard.log version=11.2.0.4.0
10.36.40.74 move into the database address
1. Create a directory
-- create a directory
Cd / home/oracle
Mkdir bak20170511
two。 Add permission
Chmod o+wr / home/oracle/bak20170511
3. Store the migrated data in a directory. If you are on the same network segment, you can use the following script
Scp-r root@10.36.40.53:/home/oracle/bak20170511/mkt_standard.dmp / home/oracle/bak20170511/
4. Add permissions to the data file
Chmod w+or / home/oracle/bak20170511/mkt_standard.dmp
5. Enter sql command line mode
Enter the oracle permission first
Su-oracle
Sql command line mode, dba permissions
Sqlplus / as sysdba
6. Give the created directory read and write permissions to the database users who need to migrate
Create or replace directory dumpdir as'/ home/oracle/bak20170511'
-Authorization
Grant read,write on directory dumpdir to mkt_standard
7. Query the current database version
Select * from v$version
Version information
TNS for Linux: Version 11.2.0.4.0-Production
8. Create a tablespace, consistent with the user tablespace name of the migrated database
Create tablespace MKT_STANDARD_DATA logging datafile 'MKT_STANDARD_DATA.DBF' size 500m autoextend on
9. Create a temporary tablespace that matches the name of the temporary tablespace for the migrated database user
Create temporary tablespace MKT_STANDARD_DATA_TEMP tempfile 'MKT_STANDARD_DATA_TEMP.DBF' size 500m autoextend on
10. Create a user and assign the tablespace and temporary tablespace to the user. The user name and password created are case-sensitive.
User name and password
Create user mkt_standard identified by mkt_standard
Default tablespace MKT_STANDARD_DATA temporary tablespace MKT_STANDARD_DATA_TEMP profile default
11. Give the user permission
Link permission
Grant connect to mkt_standard
Create tables, views, and other permissions
Grant resource to mkt_standard
System permissions
Grant unlimited tablespace to mkt_standard
twelve。 Exit sql command line mode and enter oracle permissions
Exit ()
13. Import data
Username and password sid export database version
Impdp mkt_standard/mkt_standard@10.36.40.74/orcl directory=dumpdir dumpfile=mkt_standard.dmp logfile=mkt_standard.log version=11.2.0.4.0
Delete temporary tablespace
Drop tablespace MKT_STANDARD_DATA_TEMP including contents and datafiles
Delete tablespace
Drop tablespace MKT_STANDARD_DATA including contents and datafiles
Delete user
Drop user MKT_STANDARD cascade
