4、逻辑备份

[TOC]
#### 4.2.1 expdp导出

##### 1. 创建备份目录

```bash
su - oracle
mkdir /home/oracle/backup
```

##### 2. 数据库赋权

```bash
su - oracle
sqlplus / as sysdba
SELECT * FROM dba_directories; -- 查询是否已经创建的DIRECTORY
CREATE DIRECTORY backup AS '/home/oracle/backup';
GRANT read, write ON DIRECTORY backup TO system;
EXIT;
```

##### 3. 备份导出

###### 1) 导整个数据库

```bash
expdp '"/ as sysdba"' directory=backup dumpfile=expdp.orcl_%U.dmp logfile=db_all.log full=y content=all exclude=statistics compression=ALL parallel=10 cluster=n
```

###### 2) 按用户导

```bash
expdp scott/tiger@orcl directory=backup dumpfile=expdp.scott_%U.dmp logfile=scott_all.log schemas=scott compression=ALL parallel=10 cluster=n
```

###### 3) 按表名导出

```bash
expdp scott/scott@orcl DIRECTORY=backup dumpfile=expdp_scott_emp.dmp TABLES=emp cluster=n
```

###### 4) 按查询条件导

```bash
expdp scott/scott@orcl DIRECTORY=backup dumpfile=expdp_scott_emp.dmp TABLES=emp query='WHERE deptno=20' cluster=n
```

###### 5) 按表空间导

```bash
expdp '"/ as sysdba"' directory=backup dumpfile=expdp.tablespace_%U.dmp logfile=tablespace_all.log tablespaces=example compression=ALL parallel=10 cluster=n
```

#### 4.2.2 impdp导入

##### 1. 创建备份目录

```bash
su - oracle
mkdir /home/oracle/backup
```

##### 2. 数据库赋权

```bash
su - oracle
sqlplus / as sysdba
SELECT * FROM dba_directories; -- 查询已经创建的DIRECTORY
CREATE DIRECTORY backup AS '/home/oracle/backup';
GRANT read, write ON DIRECTORY backup TO system;
EXIT;
```

##### 3. 备份导入

###### 1) 导整个数据库

```bash
impdp '"/ as sysdba"' directory=backup dumpfile=expdp.orcl_%U.dmp logfile=db_all.log full=y parallel=10 cluster=n
```

###### 2) 按用户导

```bash
impdp scott/tiger@orcl directory=backup dumpfile=expdp.scott_%U.dmp logfile=scott_all.log schemas=scott parallel=10 cluster=n
```

###### 参数说明

- `remap_schema=scott:scott1`: 变更用户名
- `remap_tablespace=users:scott`: 变更表空间

#### 4.2.3 终止imp/exp和expdp/impdp

##### 4.2.3.1 终止imp/exp

```bash
ps -ef | grep imp
kill -9 <pid>
kill -9 <ppid>
```

##### 4.2.3.2 终止expdp/impdp

###### 1. 查询job

```bash
sqlplus / as sysdba
SELECT owner_name, job_name, state FROM dba_datapump_jobs;
```

###### 2. 登录并终止job

```bash
expdp system/system attach=SYS_EXPORT_FULL_01
Export> KILL_JOB
是否确实要停止此作业 ([Y]/N): YES
```

FYF

导航1234

最近发表

友情链接