ITPub博客

首页 > 数据库 > Oracle > OCP_04 expdp impdp

OCP_04 expdp impdp

原创 Oracle 作者:Michael_DD 时间:2014-02-28 15:04:22 0 删除 编辑
1.备份:逻辑备份(impdp/expdp,DB服务器)、物理备份(RMAN)

逻辑备份:

目录服务:
expdp
select BYTES/1024/1024/1024 from dba_segments where segment_name='EMP' and owner='HR'
df -h
mkdir /u01/dump 
--查看os 有无读写权限
create directory dump as '/u01/dump';
select * from dba_directories;
grant read,write on directory dump  to hr;
expdp

1.导出hr.emp表
/u01/dump/exp.par
userid=system/oracle
directory=dump
dumpfile=emp.dmp
tables=hr.emp
logfile=exp.log
expdp parfile=/u01/dump/exp.par

2.导出整个schema
/u01/dump/exp.par
userid=system/oracle
directory=dump
dumpfile=hr.dmp
schemas=hr
logfile=exp.log
expdp parfile=/u01/dump/exp.par

ORA-04031: unable to allocate 16 bytes of shared memory ("shared pool","select /*+ index(idl","KGLH0^d23710ff","kglHeapInitialize:temp")

SQL>  alter system set memory_max_target=600M scope=spfile;

System altered.

SQL> alter system set memory_target=600M scope=spfile;

System altered.


用于改参数变更失败,改错参数导致数据库启不来,还原到以前的参数或者重新修改:
create pfile='/tmp/tk.ora' from spfile;
vi /tmp/tk.ora
startup pfile='/tmp/tk.ora' nomount;
create spfile from pfile='/tmp/tk.ora';
shutdown immediate;
startup;


3.导出所有对象
/u01/dump/exp.par
userid=system/oracle
directory=dump
dumpfile=full.dmp
full=y
logfile=full.log
expdp parfile=/u01/dump/exp.par

ORA-39181: Only partial table data may be exported due to fine grain ac
cess control on "OE"."PURCHASEORDER"

grant exempt access policy to system;

4.只导出package 和procedure

userid=system/oracle
directory=dump
dumpfile=full1.dmp
full=y
include=PACKAGE,PROCEDURE
logfile=full1.log

5.只导出数据(不导出结构)
userid=system/oracle
directory=dump
dumpfile=dataonly.dmp
tables=hr.jobs,hr.employees
include=table_data
logfile=dataonly.log

6.不导出统计信息
userid=system/oracle
directory=dump
dumpfile=ex.dmp
schemas=hr
exclude=STATISTICS
logfile=ex.log

7.只导出结构,不导出数据
userid=system/oracle
directory=dump
dumpfile=a.dmp
full=y
CONTENT=METADATA_ONLY
logfile=a.log

8.
compression
userid=system/oracle
directory=dump
dumpfile=c.dmp
full=y
compression=ALL
logfile=c.log

9.  11.2.0.3.0 ---10.2.0.4.0 从高版本到低版本加兼容参数,【10G exp 导出,导入11g(imp)】
expdp version---->10G 报错
version=10.2.0.4.0
-----------------------------------------------------------------------------------------------

impdp
1.导入schema
drop user hr cascade;
imp.par
userid=system/oracle
directory=dump
dumpfile=c.dmp
schemas=hr
logfile=imp.log

2.导入table
drop table emp purge;
userid=system/oracle
directory=dump
dumpfile=c.dmp
tables=hr.emp
logfile=emp.log

3.表存在
TABLE_EXISTS_ACTION
 APPEND(追加记录), REPLACE(drop table/create table), [SKIP] and TRUNCATE(清空数据)

userid=system/oracle
directory=dump
dumpfile=c.dmp
tables=hr.emp
TABLE_EXISTS_ACTION=APPEND
logfile=emp.log

4.生产环境表存在,先导入到另一个新的用户下,再通过程序处理。
create user hr2 identified by hr2;
grant dba to hr2
userid=system/oracle
directory=dump
dumpfile=c.dmp
tables=hr.emp
REMAP_SCHEMA=hr:hr2
logfile=emp.log


hr.emp ----> hr.emp1  
userid=system/oracle
directory=dump
dumpfile=c.dmp
tables=hr.emp
REMAP_TABLE=hr.emp:emp1
logfile=emp.log


TRANSFORM = 
SEGMENT_ATTRIBUTES|STORAGE|OID|PCTSPACE:{y|n|v}[:object type]

--------------------------------------------------------------------------------

外部表

使用 ORACLE_LOADER 填充外部表 
dump ----/u01/dump
cp /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml  /u01/dump/lsnr.log

create table lsnr_log ( text varchar2(4000) ) 
  organization external ( 
  type oracle_loader 
  default directory dump 
  access parameters ( 
  records delimited by newline 
  nobadfile 
  nodiscardfile 
  nologfile 
  ) 
  location('lsnr.log') 
  ) 
  reject limit unlimited; 


select * from lsnr_log where lower(text) like '%host_id%'

使用 ORACLE_DATAPUMP 填充外部表 
CREATE TABLE emp_ext
  (first_name, last_name, department_name)
ORGANIZATION EXTERNAL
  (
    TYPE ORACLE_DATAPUMP
    DEFAULT DIRECTORY dump
    LOCATION ('emp1.dmp')
  )
PARALLEL
AS
SELECT e.first_name,e.last_name,d.department_name
FROM   employees e, departments d
WHERE  e.department_id = d.department_id AND
       d.department_name in
                     ('Marketing', 'Purchasing');


假设到另外一台机器:

mkdir -p /u02/dump
cp /u01/dump/emp1.dmp  /u02/dump
create directory dump1 as '/u02/dump';
grant all on directory dump1 to hr;

CREATE TABLE extemp
  (first_name varchar2(20), last_name varchar2(50), department_name varchar2(50))
ORGANIZATION EXTERNAL
  (
    TYPE ORACLE_DATAPUMP
    DEFAULT DIRECTORY dump1
    LOCATION ('emp1.dmp')
  );

select * from extemp;

expdp help=y | more

flashback
rman --ASM,GRID infrastructure


show parameter dump  查看alert 日志

shutdown abort 

#ps -ef | grep ora

#ipcs

smon 进程做实例恢复

io

ls -la   查看更加清楚

chown -R oracle:oinstall /u01   赋oracle用户对文件/u01操作的权限

oracle 用户下进行导入导出
#impdp parfile=/u01/dump/imp.par

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29500582/viewspace-1097121/,如需转载,请注明出处,否则将追究法律责任。

上一篇: OCP_03
下一篇: OCP_05
请登录后发表评论 登录
全部评论

注册时间:2014-02-22

  • 博文量
    326
  • 访问量
    2461420