oracle数据库备份与恢复

一、使用数据泵备份数据
1.创建目录对象并授权
CONNECT sys/password AS sysdba
CREATE DIRECTORY dir1 AS 'e:\dump1';
CREATE DIRECTORY dir2 AS 'e:\dump2';
GRANT READ, WRITE ON DIRECTORY dirdp1 TO scott, hr;
GRANT READ, WRITE ON DIRECTORY dirdp2 TO scott, hr;

2.命令行下估计转储文件的大小
Cmd,进入命令行
expdp scott/tiger SCHEMAS=scott ESTIMATE_ONLY=y ESTIMATE=statistics NOLOGFILE=y
---------------------------------
导出表
expdp scott/tiger DIRECTORY=dirdp1 DUMPFILE=tab_emp_dept.dmp LOGFILE=dirdp2:tab_emp_dept.log TABLES=emp,dept

expdp scott/tiger PARFILE=e:\dump1\par_emp_deptno20.txt
par_emp_deptno20.txt
DIRECTORY=dirdp1
DUMPFILE=tab_emp_dept.dmp
LOGFILE=dirdp2:tab_emp_dept.log
TABLES=emp,dept
---------------------------------
导出方案
expdp scott/tiger DIRECTORY=dirdp1 PARFILE=e:\dump1\par_scott.txt
par_scott.txt
DUMPFILE=scott_%U.dmp
Logfile=scott.log
Schemas=scott
Filesize=100K
Job_name=schemas_scott
----------------------------------
导出表空间
expdp scott/tiger PARFILE=e:\dump1\par_users_mytbs01.txt
par_users_mytbs01.txt
DUMPFILE=dirp1:tabs.dmp
Logfile=dirdp2:tabs.log
Tablespaces=users,mytabl01
----------------------------------
导入表
CONNECT scott/tiger
impdp scott/tiger DUMPFILE=dirdp1:tab_emp_dept.dmp LOGFILE=dirdp2:imp_tab_emp.log TABLES=emp
----
导入方案
CONNECT sys/password AS sysdba
1)创建方案，并授予相应的权限
SYS用户创建SCOTT用户，并给其授予权限
sqlplus sys/password AS sysdba
CREATE USER scott IDENTIFIED BY tiger
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users;
GRANT connect, resource TO scott;
GRANT unlimited tablespace TO scott;
GRANT read, write ON DIRECTORY dirdp1 TO scott;
GRANT read, write ON DIRECTORY dirdp2 TO scott;
ALTER USER scott QUOTA UNLIMITED ON mytbs01;
GRANT create view TO scott;
2)导入方案
impdp scott/tiger DIRECTORY=dirdp1 DUMPFILE=scott_01.dmp,scott_02.dmp,scott_03.dmp LOGFILE=imp_scott.log
3)导入表空间
CONNECT sys/password AS sysdba
1)创建表空间并分配表空间
CONNECT sys/password AS sysdba
CREATE TABLESPACE mytbs01
DATAFILE 'e:\mytbs01_1.dbf' SIZE 5M AUTOEXTEND ON NEXT 3M MAXSIZE UNLIMITED;
ALTER USER scott QUOTA UNLIMITED ON mytbs01;
2)导入表空间
impdp scott/tiger DUMPFILE=dirdp1:tbs_users_mytbs01.dmp LOGFILE=dirdp2:imp_tbs_mytbs01.log TABLESPACES=mytbs01

2.在OEM中导出导入数据
以SYSTEM用户、Normal连接身份登陆OEM，出现“数据库”主页的“主目录”属性页。单击“维护”->“实用程序”->“导出到文 件”，输入Oracle主机的用户名和密码（如果有提示: 用户口令错误，须修改本地用户策略，方法如下：控制面板－－管理工具－－本地安全策略－－本地策略－－用户权限分配
在作为批处理登录策略中加下当前登录用户的名称。）选择导出类型，选择导出的内容，导出……
    导入功能与此类似。