数据库备份
浏览数：26 /
时间：2015年06月12日
---恢复内容开始---
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 &
---恢复内容结束---
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
