oracle 11g数据泵备份恢复.docx
- 文档编号:4042518
- 上传时间:2022-11-27
- 格式:DOCX
- 页数:47
- 大小:744.30KB
oracle 11g数据泵备份恢复.docx
《oracle 11g数据泵备份恢复.docx》由会员分享,可在线阅读,更多相关《oracle 11g数据泵备份恢复.docx(47页珍藏版)》请在冰豆网上搜索。
oracle11g数据泵备份恢复
ORACLE11G数据泵备份恢复
文档修改记录
修改人
修改时间
修改内容
2012-10-24
生成初始报告
目录
ORACLE11G数据泵备份恢复1
1.原库准备工作2
1.1检查原库字符集2
1.2检查源库表空间信息2
1.3检查源系统用户及权限3
1.4导出前检查及准备3
1.5expdp按用户导出4
1.6传输备份数据5
2.新库导入工作及检查5
2.1安装数据库软件5
2.2DBCA建实例5
2.3建立表空间14
2.4创建role(不需要创建用户)28
2.5导入数据30
停止job30
同时禁用自动统计分析31
完成后启用jobs31
2.6检查数据库对象31
2.7修改数据库为归档模式32
2.8数据库参数修改32
2.9修改用户密码32
2.10检查对象32
原库准备工作
1.1检查原库字符集
查询oracleserver端的字符集
有很多种方法可以查出oracleserver端的字符集,比较直观的查询方法是以下这种:
SQL>selectuserenv('language')fromdual;
USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIEDCHINESE_CHINA.ZHS16CGB231280
查询oracleclient端的字符集
在windows平台下,就是注册表里面相应OracleHome的NLS_LANG。
还可以在dos窗口里面自己设置,比如:
setnls_lang=SIMPLIFIEDCHINESE_CHINA.ZHS16CGB231280
这样就只影响这个窗口里面的环境变量。
如果检查的结果发现server端与client端字符集不一致,请统一修改为同server端相同的字符集。
select*fromnls_database_parameters
Language:
指定服务器消息的语言,影响提示信息是中文还是英文
Territory:
指定服务器的日期和数字格式,
Charset:
指定字符集。
如:
AMERICAN_AMERICA.ZHS16GBK
从NLS_LANG的组成我们可以看出,真正影响数据库字符集的其实是第三部分。
所以两个数据库之间的字符集只要第三部分一样就可以相互导入导出数据,前面影响的只是提示信息是中文还是英文。
1.2检查源库表空间信息
SQL>selectTABLESPACE_NAMEfromdba_tablespaces;
TABLESPACE_NAME
------------------------------------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
DATA_HOST_A
DATA_IAS_A
DIARYDATA2003
DIARYDATA2004
DIARYDATA2005
DIARYDATA2006
LESPACE_NAME
DIARYDATA2008
DIARYDATA2007
DRSYS
FACT2003
FACT2004
FACT2005
FACT2006
FACT2007
INDX_HOST_A
INDX_IAS_A
NEWRBS
1.3检查源系统用户及权限
createuser用户名IDENTIFIEDBY密码
GRANTCREATEUSER,DROPUSER,ALTERUSER,CREATEANYVIEW, DROPANYVIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE, DBA,CONNECT,RESOURCE,CREATESESSIONTO用户名字
查看用户(不属于system表空间),以及schema下有所表的信息
SQL>selectusername,user_id,passwordfromdba_userswhereDEFAULT_TABLESPACE<>'SYSTEM';
selectcount(table_name)fromdba_tableswhereowner='HOST';
1.4导出前检查及准备
1.4.1断session、停job
停止应用服务器,检查数据库有无其他session连接
setlinesize300
colmachineformata30
selectcount(*),machine,usernamefromgv$sessiongroupbymachine,usernameorderbymachine;
altersystemsetjob_queue_processes=0scope=bothsid='*';
确认有无jobs运行
selectjobfromdba_jobs_running;
altersystemsetjob_queue_processes=150scope=bothsid='*';此布在导出完成后执行
1.4.2建导出目录
创建目录
su-oracle
mkdir-p/home/data_space/exp
sqlplus/assysdba
SQL>selectcount(*)fromv$sessionwhereusername='SETTLE';
预期0
SQL>createorreplacedirectorydmp_diras'/home/data_space/exp';
SQL>grantread,writeondirectorydmp_dirtopublic;
1.5expdp按用户导出
使用直接导出模式direct=yexp比传统模式导出快一倍oracle提供2种模式导出表数据,传统模式CONVENTIONALPATH和直接模式DIRECTPATH,有direct指定。
setnls_lang=AMERICAN_AMERICA.AL32UTF8
创建目录
su-oracle
mkdir-p/home/data_space/exp
sqlplus/assysdba
SQL>selectcount(*)fromv$sessionwhereusername='SETTLE';
预期0
SQL>createorreplacedirectorydmp_diras'/home/data_space/exp';
SQL>grantread,writeondirectorydmp_dirtopublic;
expdpzhengly/8605683directory=dmp_dirdumpfile=BAM_MDS_%U.dmplogfile=BAM_MDS.logparallel=10SCHEMAS=BAM_MDS
expdpzhengly/8605683directory=dmp_dirdumpfile=BAM_ORABAM_%U.dmplogfile=BAM_ORABAM.logparallel=10SCHEMAS=BAM_ORABAM
expdpzhengly/8605683directory=dmp_dirdumpfile=BAM_ORASDPM_%U.dmplogfile=BAM_ORASDPM.logparallel=10SCHEMAS=BAM_ORASDPM
expdpzhengly/8605683directory=dmp_dirdumpfile=BAM_SOAINFRA_%U.dmplogfile=BAM_SOAINFRA.logparallel=10SCHEMAS=BAM_SOAINFRA
expdpzhengly/8605683directory=dmp_dirdumpfile=EAIUSER_%U.dmplogfile=EAIUSER.logparallel=10SCHEMAS=EAIUSER
expdpzhengly/8605683directory=dmp_dirdumpfile=EXPCMI_%U.dmplogfile=EXPCMI.logparallel=10SCHEMAS=EXPCMI
expdpzhengly/8605683directory=dmp_dirdumpfile=EXPEDW_%U.dmplogfile=EXPEDW.logparallel=10SCHEMAS=EXPEDW
expdpzhengly/8605683directory=dmp_dirdumpfile=EXPESTORE_%U.dmplogfile=EXPESTORE.logparallel=10SCHEMAS=EXPESTORE
expdpzhengly/8605683directory=dmp_dirdumpfile=EXPHCSP_%U.dmplogfile=EXPHCSP.logparallel=10SCHEMAS=EXPHCSP
expdpzhengly/8605683directory=dmp_dirdumpfile=EXPLED_%U.dmplogfile=EXPLED.logparallel=10SCHEMAS=EXPLED
expdpzhengly/8605683directory=dmp_dirdumpfile=GUESTCP_%U.dmplogfile=GUESTCP.logparallel=10SCHEMAS=GUESTCP
expdpzhengly/8605683directory=dmp_dirdumpfile=GUESTCST_%U.dmplogfile=GUESTCST.logparallel=10SCHEMAS=GUESTCST
expdpzhengly/8605683directory=dmp_dirdumpfile=HP_DBSPI_%U.dmplogfile=HP_DBSPI.logparallel=10SCHEMAS=HP_DBSPI
expdpzhengly/8605683directory=dmp_dirdumpfile=LDAPUSER_%U.dmplogfile=LDAPUSER.logparallel=10SCHEMAS=LDAPUSER
expdpzhengly/8605683directory=dmp_dirdumpfile=MGMT_VIEW_%U.dmplogfile=MGMT_VIEW.logparallel=10SCHEMAS=MGMT_VIEW
expdpzhengly/8605683directory=dmp_dirdumpfile=SADMIN_%U.dmplogfile=SADMIN.logparallel=10SCHEMAS=SADMIN
expdpzhengly/8605683directory=dmp_dirdumpfile=SIEBEL_%U.dmplogfile=SIEBEL.logparallel=10SCHEMAS=SIEBEL
expdpzhengly/8605683directory=dmp_dirdumpfile=SSA_%U.dmplogfile=SSA.logparallel=10SCHEMAS=SSA
expdpzhengly/8605683directory=dmp_dirdumpfile=UCMDIC_%U.dmplogfile=UCMDIC.logparallel=10SCHEMAS=UCMDIC
expdpzhengly/8605683directory=dmp_dirdumpfile=UCMDSP_%U.dmplogfile=UCMDSP.logparallel=10SCHEMAS=UCMDSP
expdpzhengly/8605683directory=dmp_dirdumpfile=UCMEXT_%U.dmplogfile=UCMEXT.logparallel=10SCHEMAS=UCMEXT
expdpzhengly/8605683directory=dmp_dirdumpfile=UCMTRS_%U.dmplogfile=UCMTRS.logparallel=10SCHEMAS=UCMTRS
一般情况要把exp时的字符集设成目标数据库的字符集。
然后imp到目标数据库
1.6传输备份数据
拷贝数据到目标系统
导入前,挂载nfs文件系统
mount10.135.29.131:
/export/Data_SPACE/SPACE/home/data_space
新库导入工作及检查
2.1安装数据库软件
安装11g软件
2.2DBCA建实例
dbca注意选择与源库相应的字符集SIMPLIFIEDCHINESE_CHINA.ZHS16CGB231280
select*fromnls_database_parameters查看字符集
selectuserenv('language')fromdual;
数据文件目录指定为/oracle/product/11.2.0/ucm/datafile
统一密码oracle
2.3建立表空间
根据源库应用表空间名称及大小,规划11g表空间、生成创建表空间脚本:
CREATETABLESPACEAUDTBSDATAFILE
'/oracle/product/11.2.0/ucm/datafile/audtbs01.dbf'SIZE8192MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/audtbs.1643.768389863'SIZE2048MAUTOEXTENDOFF
LOGGING
ONLINE
PERMANENT
EXTENTMANAGEMENTLOCALAUTOALLOCATE
BLOCKSIZE8K
SEGMENTSPACEMANAGEMENTAUTO
FLASHBACKON;
CREATETABLESPACEDATA01DATAFILE
'/oracle/product/11.2.0/ucm/datafile/data01.521.761332091'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.511.761332089'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.510.761332089'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.523.761332091'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.517.761332091'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.529.761332093'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.527.761332091'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.518.761332091'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.560.761332787'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.565.761332827'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.593.761333161'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.589.761333117'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.587.761333097'SIZE3512MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.577.761332947'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.566.761332849'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.567.761332865'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.596.761333187'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.597.761333191'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.591.761333143'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.578.761332967'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.568.761332865'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.576.761332947'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.600.761333225'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.598.761333193'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.595.761333177'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.579.761332971'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.594.761333173'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.590.761333141'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.604.761333245'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.603.761333237'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.606.761333313'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.584.761333031'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.599.761333223'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.605.761333311'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.608.761333335'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.610.761333371'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.612.761333387'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.585.761333071'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.602.761333229'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.607.761333325'SIZE4096MAUTOEXTENDOFF,
'/oracle/product/11.2.0/ucm/datafile/data01.614.761333409'SIZE4096MAUTOEXTENDOFF,
'/o
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 11g数据泵备份恢复 11 数据 备份 恢复