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 import version, database address 10.36.40.74

10.36.40.53 the database address that needs to be migrated

1. [[email protected] ~]# Create directory

--Create a directory

Create or replace directory Dumpdir as '/home/oracle/bak20170511 ';

2. Add Permissions

chmod w+or/home/oracle/bak20170511

3. Shell Oracle permissions, Login DBA

Su-oracle

Sqlplus/as SYSDBA

4. Assign the created directory to the database user who needs to migrate read and write permissions

--Authorization

Grant Read,write on directory Dumpdir to Mkt_standard;

5. Querying the table space of the current user, temporary tablespace

--Current user name tablespace temporary table space

Select T.username,t.default_tablespace,t.temporary_tablespace from User_users t;

User name tablespace temporary table space

Mkt_standard Mkt_standard_data Mkt_standard_data_temp

6. Querying 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 ();

Su-oracle

7. Data export

User name password SID Import database version

EXPDP mkt_standard/[email PROTECTED]/ORCL directory=dumpdir dumpfile=mkt_standard.dmp logfile=mkt_standard.log version=11.2.0.4.0;

10.36.40.74 the address of the migration database

1. [[email protected] ~]# Create directory

--Create a directory

Create or replace directory Dumpdir as '/home/oracle/bak20170511 ';

2. Add Permissions

chmod w+or/home/oracle/bak20170511

3. Store the migrated data in the directory, if the same network segment can use the following script

scp-r [Email protected]:/home/oracle/bak20170511/mkt_standard.dmp/home/oracle/bak20170511/

4. Adding permissions to a data file

chmod w+or/home/oracle/bak20170511/mkt_standard.dmp

5. Enter SQL command line mode

Enter Oracle Permissions First

Su-oracle

SQL command-line mode, DBA authority

Sqlplus/as SYSDBA

6. Querying the current database version

SELECT * from V$version;

Version information

TNS for Linux:version 11.2.0.4.0-production

7. Create a tablespace that is consistent with the migrated database user table space name

Create tablespace mkt_standard_data logging datafile ' Mkt_standard_data. DBF ' size 500M autoextend on;

8. Create a temporary tablespace that is consistent with the migrated database user Temp table space name

Create temporary tablespace mkt_standard_data_temp tempfile ' mkt_standard_data_temp. DBF ' size 500M autoextend on;

9. Create the user, and assign the tablespace and temporary tablespace to the user, the created user name, the password is case sensitive

User name password

Create user Mkt_standard identified by Mkt_standard

Default tablespace mkt_standard_data temporary tablespace mkt_standard_data_temp profile default;

10. Granting user Privileges

Link permissions

Grant connect to Mkt_standard;

Create a table, view, and other permissions

Grant resource to Mkt_standard;

System permissions

Grant unlimited tablespace to Mkt_standard;

11. Exit SQL command line mode and enter Oracle permissions

Exit ();

Su-oracle

12. Import data

User name password SID Export database version

IMPDP mkt_standard/[email PROTECTED]/ORCL directory=dumpdir dumpfile=mkt_standard.dmp logfile=mkt_standard.log version=11.2.0.4.0;

To delete a temporary table space

Drop tablespace mkt_standard_data_temp including contents and datafiles;

Delete Table space

Drop tablespace mkt_standard_data including contents and datafiles;

Delete User

Drop user Mkt_standard cascade;

This article is from the "Lonice" blog, make sure to keep this source http://wox666.blog.51cto.com/5141734/1924925

Oracle Database Migration