【每日一记5】第20天 oracle 用exp/imp备份与恢复整个数据库

exp与imp是较老的导出导入技术，适合11g及之前的版本
(查看数据库字符集：select * from props$ ，查看环境变量里的字符集NLS_LANG是否与数据库里的一致，不一致的话要先设置为一致)

测试用数据：

create tablespace mydata

datafile '/oracle/app/oracle/oradata/cdedb/mydata01.dbf'

size 5m

autoextend on

next 5m

extent management local;

create temporary tablespace cdetmp tempfile '/oracle/app/oracle/oradata/cdedb/cdetmp01.dbf' size 10m autoextend off;

create user cde identified by 111

default tablespace mydata

temporary tablespace cdetmp;

grant dba to cde;

conn cde/111@cdedb

create table people(

name varchar2(10) not null,age int,sex varchar2(10)

);

insert into people (name,age,sex) values ('daen',19,'男');

insert into people (name,age,sex) values ('jj',15,'女');

insert into people (name,age,sex) values ('rr',10,'女');

1、全库备份

exp system/whoami buffer=65536 feedback=100000 full=y file=exp.dmp log=exp.log

2、模拟出人为故障：(删除用户，删除表空间与数据库文件)

drop user cde cascade;

drop tablespace mydata including contents and datafiles;

3、恢复过程：

3.1、创建表空间、用户、授权

create tablespace mydata

datafile '/oracle/app/oracle/oradata/cdedb/mydata01.dbf'

size 5m

autoextend on

next 5m

extent management local;

create temporary tablespace cdetmp tempfile '/oracle/app/oracle/oradata/cdedb/cdetmp01.dbf' size 10m autoextend off;

create user cde identified by 111

default tablespace mydata

temporary tablespace cdetmp;

grant dba to cde;

3.2、开始恢复

imp system/whoami fromuser=cde touser=cde commit=y buffer=65536 feedback=100000 ignore=y  full=y file=exp.dmp log=imp.log

4、编译无效对象

@$ORACLE_HOME/rdbms/admin/utlrp.sql

5、验证一下

select * from people;