EXPDP (export) and IMPDP (import) of Oracle databases under Linux servers

Source: Internet
Author: User
Tags create directory dba

Immediately following the previous article, the Oracle database schema has been created, and my requirement is to migrate the databases on the old server to the new database .

This will use the IMPDP (import) operation.

To implement IMPDP (import) work on a new database,

First you need to do EXPDP (export) operation from the old database, the command is as follows:

EXPDP user name/password schemas=iirspublish dumpfile= export the database name. DMP directory= Directory name logfile= export log. Log

Note: schemas, it's easy to understand it with user users, and you can have a set of non-interfering objects under each mode (user).

If you want to access other schema objects, you need to specify the schema name, which is actually specifying username.

Here are some of the steps I've practiced in my actual work,

Preparation: First connect to the Linux server with Xshell and switch to Oracle account,

[Email protected] ~]# su-oracle

Last Login:fri Jul 13:49:22 CST on PTS/1 ( successful switchover )

[Email protected] ~]$ sqlplus sys/as sysdba

Switch to

Sql>

Then execute the following command in turn:

1. SYS User Login Create directory

Sql>create directory dir_iirsuserbaknew as '/u01/app/oracle/oradata '; (Create a directory and assign a storage path)

2. Create TABLE space under SYS user

Sql>create tablespace iirsuser datafile '/u01/app/oracle/oradata/iirsuser01 ' size 100m autoextend on next 100m, '/u01/ App/oracle/oradata/iirsuser02 ' size 100m autoextend on next 100m Extent management local uniform size 200k; (Create table space, set file autogrow Space size)

3. User created under SYS user

Sql>create user Iirsuser identified by "Iirsuser" Default tablespace iirsuser temporary tablespace temp; (Create a user name, set a password, and indicate the default tablespace and temporary table space)

Sql>grant Dba,connect,resource to Iirsuser; (Grant DBA, Foundation, development rights to the user)

4, sys user under

Sql>grant read,write on the directory dir_iirsuserbaknew to Iirsuser; (Grant users Read and write permissions to the directory)

5, DOS under IMPDP restore

[[Email protected] ~] $IMPDP iirsuser/"Iirsuser" @orcl directory=dir_iirsuserbaknew dumpfile= Expiirspublish20170814.dmp Remap_tablespace=iirspublish:iirsuser Remap_schema=iirspublish:iirsuser logfile= Impdbiirspublish20170814.log transform=oid:n

The 5th step requires the exit (exit) SQL command line to execute under the Oracle account.

What you need to explain is:

IMPDP username/password @sid directory= directory dumpfile= The instance name EXPDP the old database. DMP remap_tablespace= Old Database space name: New database space name remap_schema= old database user name: New database user name logfile=impdb log name transform= This parameter can be removed and applied to the applicable object's metadata transformation

It should be emphasized that:

Question 1: If you forget the directory you built during the operation, you can use the command (SELECT * from dba_directories;) to query;

Issue 2: If you want to delete a newly restored DB instance, you need to do the following

1), first delete the user

-----drop USER C#iirsuser CASCADE;

2), second Delete table space including tablespace content and data files

-----Drop tablespace iirsuser including contents and datafiles cascade constraints;

3), finally delete the directory

-----Drop directory Dir_iirsuserbaknew;

EXPDP (export) and IMPDP (import) of Oracle databases under Linux servers

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.