Oracle database backup and recovery (expdp and impdp)

Oracle database backup and recovery (expdp and impdp)

Refer:

Oracle 11g beginner's Guide

The materials on the Internet are fragmented, and most of them cannot complete the required functions.

Preparations

1. configure it in the bin directory of the Environment Variable squadron. By default, the corresponding environment variables are automatically configured when the Oracle database is installed,

For example, D:/oracle/product/10.2.0/db_1/BIN

2. Check whether the expdp.exeand impdp.exe files exist in the binfolder of the Oracle installation folder.

3. Create an external directory.

Data pump requires that you create a directory for the data files and log files to be created and read.

Used external directory. When creating a directory object in oracle, you can use create directory

Statement.

[Instance]

1. Check whether the bin directory exists in the advanced environment variable-pathpath.

2. Check whether the expdp.exe1_impdp.exe file exists.

3. Create a directory

C:/> sqlplus/nolog
SQL> conn sys/sys as sysdba
SQL> create directory mypump as 'd:/app/temp ';
SQL> grant read, write on directory mypump to scot

Export data

[Instance]

1. Table mode Export

Expdp scott/scott_2009 directory = mypumpdumpfile = expdptab. dmp tables = dept, emp

(Select * from dba_tablespaces; altertablespace testspace online ;)

2. Export in schema mode

(The ORA-39083 error is caused by the user's permissions and the grant EXP_FULL_DATABASE to scott is executed before the expdp is exported ;)

Expdp system/system directory = mypumpdumpfile = expdp. dmp schemas = scott nologfile = y

3. Export tablespace data

Expdp system/system directory = mypumpdumpfile = expdpspace. dmp tablespaces = EPISCMCC_DTS

4. Full-Database Export

Expdpsystem/system directory = mypump dumpfile = expdp. dmp full = y

Data Import

1. Table mode Import

Impdpscott/scott_2009 directory = mypump dumpfile = expdptab. dmp tables = dept, emp

2. Import in schema mode

Impdpsystem/system directory = mypump dumpfile = expdp. dmp schemas = scott

3. tablespace Data Import

Impdp system/tiger directory = mypump dumpfile = expdspaces. dmp remap_tablespace = EPISCMCC_DTS: EPISCMCC_DTS table_exists_action = replace

4. Full-Database Import

Impdpsystem/system directory = mypump dumpfile = expdp. dmp full = y table_exists_action = replace

Specifically, when importing a tablespace and importing a full database, you need to create the tablespace and the corresponding tablespace as follows:

Before importing data to a database, you must create the corresponding tablespace and users in the new database.

In the source database, the tablespace is EPICMCC_DTS, and the user in the tablespace is EPICMCC.

/* Create a temporary tablespace */
Create temporary tablespace EPISCMCC_TEMP
Tempfile 'C: \ app \ z002w00r-e01 \ oradata \ orcl \ episcmcc_temp.dbf'
Size 50 m
Autoextend on
Next 50 m maxsize 20480 m
Extent management local

/* Create a tablespace */
Create tablespace EPISCMCC_DTS
Logging
Datafile 'C: \ app \ z002w00r-e01 \ oradata \ orcl \ episcmcc_dts.dbf'
Size 50 m
Autoextend on
Next 50 m maxsize 20480 m
Extent management local

/* Create a specified table space */
Create user EPISCMCC identified by tiger
Default tablespace EPISCMCC_DTS
Temporary tablespace EPISCMCC_TEMP

/* Authorize the user */
Grant connect, resource, dba to EPISCMCC

---------------------------- Lili split line ----------------------------

Oracle Import and Export expdp IMPDP details

Solution to Oracle 10g expdp export error ORA-4031

Oracle 10gr2 rac expdp error UDE-00008 ORA-31626

Use of expdp/impdp to back up databases in Oracle

Oracle backup recovery (expdp/impdp)