数据库备份

---恢复内容开始---

1. exp/imp

1)用户备份模式(逻辑备份)

exp user/pwd@SID  owner:user  rows=y  compress=n  buffer=65536  feedback=100000  volsize=0  file=user.dmp  log=user.log ;

 

exp crm_base/crm_base owner=user  file=crm_base.dmp log=crm_base.log   

nohup imp \'system/system123 as sysdba\'   file=crm_channel.dmp fromuser=channel_poc touser=crm_channel log=crm_channel.log &

 

用户模式导出导入之前，可以先按原有数据库建立表空间和用户，直接导入就不会有表空间的问题了

SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name)

FROM DBA_TABLESPACES TS;

 

2. expdp/impdp

1)用户导入导出模式

nohup  expdp  \'system/system123@SID as sysdba\'   directory=dir_name   dumpfile=user.dmp    schemas=user   logfile=user.log &

nohup  impdp  \'system/system123@SID as sysdba\'    directory=DIR_SHUAIJG   dumpfile=user.dmp   remap_schema=fromuser:touser  logfile=user.log &

--Remap_tablespace:from tbs:to tbs

 

 

2）全库导出导入模式

expdp system/system@oradb directory=expdp_dir dumpfile=expdp.dmp  full=y  logfile=user.log;

impdp system/system@oradb directory=expdp_dir dumpfile=expdp.dmp  full=y  logfile=user.log;

 

3）表导出模式

expdp system/system@oradb directory=expdp_dir dumpfile=expdp.dmp tables=scott.emp, scott.dept  logfile=user.log

impdp system/system@oradb directory=expdp_dir dumpfile=expdp.dmp tables=scott.emp, scott.dept  logfile=user.log

注：使用query条件的语句时最好采用parfile方式可以按条件导出数据

注：追加数据

impdp system/system@oradb directory=expdp_dir  dumpfile=expdp.dmp  schemas=scott  table_exists_action=append 

 

 

4）表空间导出模式

expdp system/system@oradb directory=expdp_dir dumpfile=expdp.dmp tablespaces=tbs1,tbs2.dmp tablespaces=tbs1,tbs2

impdp system/system@oradb directory=expdp_dir dumpfile=expdp.dmp tablespaces=tbs1,tbs2

 

5）查询创建db目录

select * from dba_directories;

create or replace directory dir_name as ‘dir_path’;

grant read,write on directory dir_name to public;

 

3.常见问题

--字符集

select userenv ('language') from dual;       字符集问题 ，修改导入数据库字符集再改过来

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses AL32UTF8 character set (possible charset conversion )

export client uses ZHS16GBK character set (possible charset conversion )

 

--导入导出的schema是啥

select * from CRM_SO.SYS_EXPORT_SCHEMA_04

 

--存储过程执行不起来

 

--表空间不一致，通过转移表空间实现

 

--缺表，通过sql查询用户下有哪些表，更家里对比

 

 

=============================================================================

 

crm_base

expdp  crm_base/crm_base directory=DIR_SHUAIJG  dumpfile=crm_base.dmp    schemas=crm_base  logfile=crm_base.log;

 

-- Create the user 

create user CRM_BASE

  identified by  crm_base   

  default tablespace DATA_BASE

  temporary tablespace TEMP

  profile DEFAULT;

-- Grant/Revoke role privileges 

grant connect to CRM_BASE;

grant dba to CRM_BASE;

-- Grant/Revoke system privileges 

grant unlimited tablespace to CRM_BASE;

 

nohup imp \'system/system123 as sysdba\'   file=base_poc.dmp fromuser=base_poc touser=crm_base log=crm_base_imp.log  &

---

crm_so

看有没有这个用户9841000691 和其他缺失的表，如果有上传

停服务，drop用户，新建用户

导入

 

 

nohup expdp crm_so/crm_so directory=DIR_SHUAIJG dumpfile=crm_so.dmp schemas=crm_so logfile=crm_so_exp.log & 

 

-- Create the user 

create user CRM_SO

  identified by  crm_so   

  default tablespace DATA_SO

  temporary tablespace TEMP

  profile DEFAULT;

-- Grant/Revoke role privileges 

grant connect to CRM_SO;

grant dba to CRM_SO;

-- Grant/Revoke system privileges 

grant unlimited tablespace to CRM_SO;

 

nohup imp \'system/system123 as sysdba\'   file=so_poc.dmp fromuser=so_poc touser=crm_so log=crm_so_imp.log  &

---

party

nohup expdp crm_party/crm_party directory=DIR_SHUAIJG dumpfile=crm_party.dmp schemas=crm_party logfile=crm_party_exp.log & 

 

-- Create the user 

create user CRM_PARTY

  identified by  crm_party   

  default tablespace DATA_PARTY

  temporary tablespace TEMP

  profile DEFAULT;

-- Grant/Revoke role privileges 

grant connect to CRM_PARTY;

grant dba to CRM_PARTY;

-- Grant/Revoke system privileges 

grant unlimited tablespace to CRM_PARTY;

 

nohup imp \'system/system123 as sysdba\'   file=party_poc.dmp fromuser=party_poc  touser=crm_party log=crm_party_imp.log  &

---

product

nohup expdp crm_product/crm_product directory=DIR_SHUAIJG dumpfile=crm_product.dmp schemas=crm_product logfile=crm_product_exp.log &

-- Create the user 

create user CRM_PRODUCT

  identified by  crm_product   

  default tablespace DATA_PRODUCT

  temporary tablespace TEMP

  profile DEFAULT;

-- Grant/Revoke role privileges 

grant connect to CRM_PRODUCT;

grant dba to CRM_PRODUCT;

-- Grant/Revoke system privileges 

grant unlimited tablespace to CRM_PRODUCT;

 

nohup imp \'system/system123 as sysdba\'   file=product.dmp fromuser=product  touser=crm_product log=crm_product_imp.log  &

---

comframe--工作流

nohup expdp \'system/system123 as sysdba\'  directory=DIR_SHUAIJG dumpfile=crm_comframe.dmp  schemas=crm_comframe logfile=crm_comframe_exp.log & 

 

-- Create the user 

create user CRM_COMFRAME

  identified by  crm_comframe  

  default tablespace DATA_COMFRAME

  temporary tablespace TEMP

  profile DEFAULT;

-- Grant/Revoke role privileges 

grant connect to CRM_COMFRAME;

grant dba to CRM_COMFRAME;

-- Grant/Revoke system privileges 

grant unlimited tablespace to CRM_COMFRAME;

 

nohup imp \'system/system123 as sysdba\'   file=comframe_poc.dmp  fromuser=comframe_poc  touser=crm_comframe  log=crm_comframe_imp.log  &

 

---

selfcare 

nohup expdp crm_selfcare/crm_selfcare  directory=DIR_SHUAIJG dumpfile=crm_selfcare.dmp schemas=crm_selfcare logfile=crm_selfcare_exp.log & 

 

-- Create the user 

create user CRM_SELFCARE

  identified by  

crm_selfcare  

  default tablespace DATA_SELFCARE

  temporary tablespace TEMP

  profile DEFAULT;

-- Grant/Revoke role privileges 

grant connect to CRM_SELFCARE;

grant dba to CRM_SELFCARE;

-- Grant/Revoke system privileges 

grant unlimited tablespace to CRM_SELFCARE;

 

nohup imp \'system/system123 as sysdba\'   file=selfcaredev_poc.dmp fromuser=selfcaredev_poc touser=crm_selfcare  log=crm_selfcare_imp.log  &

 

---

crm_channel

expdp   crm_channel/crm_channel  directory=DIR_SHUAIJG dumpfile=crm_channel.dmp    schemas=crm_channel  logfile=crm_channel.log;

 

-- Create the user 

create user CRM_CHANNEL

  identified by  crm_channel 

  default tablespace DATA_CHANNEL

  temporary tablespace TEMP

  profile DEFAULT;

-- Grant/Revoke role privileges 

grant connect to CRM_CHANNEL;

grant dba to CRM_CHANNEL;

-- Grant/Revoke system privileges 

grant unlimited tablespace to CRM_CHANNEL;

 

impdp crm_channel/crm_channel  directory=DIR_SHUAIJG dumpfile=crm_channel.dmp    remap_schema=crm_channel:crm_channel logfile=crm_channel.log;

---

crm_sec

expdp   crm_sec/crm_sec directory=DIR_SHUAIJG dumpfile=crm_sec.dmp    schemas=crm_sec  logfile=crm_sec_exp.log;

 

-- Create the user 

create user CRM_SEC

  identified by  crm_sec

  default tablespace DATA_SEC

  temporary tablespace TEMP

  profile DEFAULT;

-- Grant/Revoke role privileges 

grant connect to CRM_SEC;

grant dba to CRM_SEC;

-- Grant/Revoke system privileges 

grant unlimited tablespace to CRM_SEC;

 

nohup imp \'system/system123 as sysdba\'   file=sec_poc.dmp fromuser=sec_poc touser=crm_sec log=crm_sec_imp.log  &

 

---

crm_cs

nohup expdp  \'system/system123 as sysdba\'  directory=DIR_SHUAIJG dumpfile=crm_cs.dmp    schemas=crm_cs  logfile=crm_cs_exp.log &

 

-- Create the user 

create user CRM_CS

  identified by  crm_cs

  default tablespace DATA_CS

  temporary tablespace TEMP

  profile DEFAULT;

-- Grant/Revoke role privileges 

grant connect to CRM_CS;

grant dba to CRM_CS;

-- Grant/Revoke system privileges 

grant unlimited tablespace to CRM_CS;

 

nohup imp \'system/system123 as sysdba\'   file=cs_poc.dmp fromuser=cs_poc touser=crm_cs log=crm_cs_imp.log  &

 

 

 

---恢复内容结束---