首页 > 代码库 > 数据库备份

数据库备份

2024-08-10 09:39:20   230人阅读

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

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  &
 
 
 

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

数据库备份


联系
我们