用数据泵技术实现逻辑备份

from:https://blog.csdn.net/weixin_41078837/article/details/80618916

逻辑备份概述

逻辑备份时创建数据库对象的逻辑副本，并存入一个二进制转储文件的过程。从本质上来讲逻辑备份与恢复实际就是对数据库事实数据的导入和导出。

导出：

导出就是数据库的逻辑备份，实质是读取一个数据库记录并将这个记录集写入一个文件（扩展名通常是dmp），这些记录的导出与物理位置无关

导入：

导入即数据库的逻辑恢复，实质是读取被导出的二进制转储文件并将其恢复到数据库。

使用数据泵技术导入/导出

数据泵（DATA PUMP）是一种在数据库之间或在数据库与操作系统之间高速传输数据的技术。数据泵工具运行在服务器上，数据库管理员需要指定数据库目录来保存转储的数据。

连接oracle 数据库

 

创建一个操作目录

 

Default Locations for Dump, Log, and SQL Files

Because Data Pump is server-based rather than client-based, dump files, log files, and SQL files are accessed relative to server-based directory paths. Data Pump requires that directory paths be specified as directory objects. A directory object maps a name to a directory path on the file system. DBAs must ensure that only approved users are allowed access to the directory object associated with the directory path.

The following example shows a SQL statement that creates a directory object named dpump_dir1 that is mapped to a directory located at /usr/apps/datafiles.

SQL> CREATE DIRECTORY dpump_dir1 AS '/usr/apps/datafiles';

The reason that a directory object is required is to ensure data security and integrity. For example:

• If you were allowed to specify a directory path location for an input file, then you might be able to read data that the server has access to, but to which you should not.

• If you were allowed to specify a directory path location for an output file, then the server might overwrite a file that you might not normally have privileges to delete.

On UNIX and Windows operating systems, a default directory object, DATA_PUMP_DIR, is created at database creation or whenever the database dictionary is upgraded. By default, it is available only to privileged users. (The user SYSTEM has read and write access to the DATA_PUMP_DIR directory, by default.)

If you are not a privileged user, then before you can run Data Pump Export or Data Pump Import, a directory object must be created by a database administrator (DBA) or by any user with the CREATE ANYDIRECTORY privilege.

After a directory is created, the user creating the directory object must grant READ or WRITE permission on the directory to other users. For example, to allow the Oracle database to read and write files on behalf of user hr in the directory named by dpump_dir1, the DBA must execute the following command:

SQL> GRANT READ, WRITE ON DIRECTORY dpump_dir1 TO hr;

Note that READ or WRITE permission to a directory object only means that the Oracle database can read or write files in the corresponding directory on your behalf. You are not given direct access to those files outside of the Oracle database unless you have the appropriate operating system privileges. Similarly, the Oracle database requires permission from the operating system to read and write files in the directories.

Data Pump Export and Import use the following order of precedence to determine a file's location:

1. If a directory object is specified as part of the file specification, then the location specified by that directory object is used. (The directory object must be separated from the file name by a colon.)

2. If a directory object is not specified as part of the file specification, then the directory object named by the DIRECTORY parameter is used.

3. If a directory object is not specified as part of the file specification, and if no directory object is named by the DIRECTORY parameter, then the value of the environment variable, DATA_PUMP_DIR, is used. This environment variable is defined using operating system commands on the client system where the Data Pump Export and Import utilities are run. The value assigned to this client-based environment variable must be the name of a server-based directory object, which must first be created on the server system by a DBA. For example, the following SQL statement creates a directory object on the server system. The name of the directory object is DUMP_FILES1, and it is located at '/usr/apps/dumpfiles1'.

   SQL> CREATE DIRECTORY DUMP_FILES1 AS '/usr/apps/dumpfiles1';
   

   Then, a user on a UNIX-based client system using csh can assign the value DUMP_FILES1 to the environment variable DATA_PUMP_DIR. The DIRECTORY parameter can then be omitted from the command line. The dump file employees.dmp, and the log file export.log, are written to '/usr/apps/dumpfiles1'.

   %setenv DATA_PUMP_DIR DUMP_FILES1
   %expdp hr TABLES=employees DUMPFILE=employees.dmp
   

4. If none of the previous three conditions yields a directory object and you are a privileged user, then Data Pump attempts to use the value of the default server-based directory object, DATA_PUMP_DIR. This directory object is automatically created at database creation or when the database dictionary is upgraded. You can use the following SQL query to see the path definition for DATA_PUMP_DIR:

   SQL> SELECT directory_name, directory_path FROM dba_directories
   2 WHERE directory_name='DATA_PUMP_DIR';
   

   If you are not a privileged user, then access to the DATA_PUMP_DIR directory object must have previously been granted to you by a DBA.

   Do not confuse the default DATA_PUMP_DIR directory object with the client-based environment variable of the same name.

授予用户操作dump_dir目录的权限

 

使用EXPDP命令导出数据（可以按照表导出，按照用户模式导出，按照表空间导出和全库导出），使用IMPDP命令导入数据（可以按照表导入，按照用户模式导入，按照表空间导出和全库导入）。

使用基于命令行的数据泵技术对数据库实施导入和导出。

SQL>col DIRECTORY_NAME for a20

SQL>col DIRECTOR_PATH for a60

SQL>col OWNER for a8

 

创建测试用户tom并授权

 

导出SCOTT用户下的emp和dept表

[oracle@dbserver~]$ expdp scott/oracle directory=dump_dir dumpfile=scotttab.dmp tables=emp,dept

 

以SCOTT用户连接，删除SCOTT用户下的EMP表

 

导入emp表

[oracle@dbserver~]$ impdp scott/oracle directory=dump_dir dumpfile=scotttab.dmp tables=emp

 

emp表已经导入成功了。

 

将导出的SCOTT用户下的DEPT表和EMP表导入到tom用户下

[oracle@dbserver~]$ impdp system/oracle11g directory=dump_dir dumpfile=scotttab.dmptables=scott.emp,scott.dept REMAP_SCHEMA=SCOTT:TOM

 

查看导入的结果，使用tom用户连接

 

导出表空间

 

在xx表空间上创建一个表aa，并为表插入记录

 

导出表空间

 

删除表空间xx的同时删除数据文件

 

aa表没有了。

 

导入表空间

导入表空间之前，需要创建一个xx表空间

 

导入表空间xx

 

验证，aa表恢复回来了。

 

导出全库

[oracle@dbserverorcl]$ expdp system/oracle11g directory=dump_dir dumpfile=full.dmp full=y

 

 

导入全库

[oracle@dbserverorcl]$ impdp system/oracle11g directory=dump_dir dumpfile=full.dmp full=y