oracle 11g数据泵备份恢复

ORACLE11G数据泵备份恢复文档修改记录修改人修改时间修改内容2012-10-24生成初始报告目录ORACLE11G数据泵备份恢复..........................................................................................................11.原库准备工作...................................................................................................................21.1检查原库字符集........................................................................................................21.2检查源库表空间信息...............................................................................................21.3检查源系统用户及权限...........................................................................................31.4导出前检查及准备...................................................................................................31.5expdp按用户导出...................................................................................................41.6传输备份数据...........................................................................................................52.新库导入工作及检查.......................................................................................................52.1安装数据库软件........................................................................................................52.2DBCA建实例..............................................................................................................52.3建立表空间.............................................................................................................142.4创建role（不需要创建用户）............................................................................282.5导入数据.................................................................................................................30停止job..........................................................................................................................30同时禁用自动统计分析.................................................................................................31完成后启用jobs............................................................................................................312.6检查数据库对象.....................................................................................................312.7修改数据库为归档模式.........................................................................................322.8数据库参数修改.....................................................................................................322.9修改用户密码.........................................................................................................322.10检查对象...............................................................................................................321.原库准备工作1.1检查原库字符集查询oracleserver端的字符集有很多种方法可以查出oracleserver端的字符集，比较直观的查询方法是以下这种:SQLselectuserenv('language')fromdual;USERENV('LANGUAGE')----------------------------------------------------SIMPLIFIEDCHINESE_CHINA.ZHS16CGB231280查询oracleclient端的字符集在windows平台下，就是注册表里面相应OracleHome的NLS_LANG。还可以在dos窗口里面自己设置，比如:setnls_lang=SIMPLIFIEDCHINESE_CHINA.ZHS16CGB231280这样就只影响这个窗口里面的环境变量。如果检查的结果发现server端与client端字符集不一致，请统一修改为同server端相同的字符集。select*fromnls_database_parametersLanguage：指定服务器消息的语言，影响提示信息是中文还是英文Territory：指定服务器的日期和数字格式，Charset：指定字符集。如:AMERICAN_AMERICA.ZHS16GBK从NLS_LANG的组成我们可以看出，真正影响数据库字符集的其实是第三部分。所以两个数据库之间的字符集只要第三部分一样就可以相互导入导出数据，前面影响的只是提示信息是中文还是英文。1.2检查源库表空间信息SQLselectTABLESPACE_NAMEfromdba_tablespaces;TABLESPACE_NAME------------------------------------------------------------SYSTEMUNDOTBS1SYSAUXTEMPUSERSDATA_HOST_ADATA_IAS_ADIARYDATA2003DIARYDATA2004DIARYDATA2005DIARYDATA2006LESPACE_NAMEDIARYDATA2008DIARYDATA2007DRSYSFACT2003FACT2004FACT2005FACT2006FACT2007INDX_HOST_AINDX_IAS_ANEWRBS1.3检查源系统用户及权限createuser用户名IDENTIFIEDBY密码GRANTCREATEUSER,DROPUSER,ALTERUSER,CREATEANYVIEW,DROPANYVIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,DBA,CONNECT,RESOURCE,CREATESESSIONTO用户名字查看用户（不属于system表空间），以及schema下有所表的信息SQLselectusername,user_id,passwordfromdba_userswhereDEFAULT_TABLESPACE'SYSTEM';selectcount(table_name)fromdba_tableswhereowner='HOST';1.4导出前检查及准备1.4.1断session、停job停止应用服务器,检查数据库有无其他session连接setlinesize300colmachineformata30selectcount(*),machine,usernamefromgv$sessiongroupbymachine,usernameorderbymachine;altersystemsetjob_queue_processes=0scope=bothsid='*';确认有无jobs运行selectjobfromdba_jobs_running;altersystemsetjob_queue_processes=150scope=bothsid='*';此布在导出完成后执行1.4.2建导出目录创建目录su-oraclemkdir-p/home/data_space/expsqlplus/assysdbaSQLselectcount(*)fromv$sessionwhereusername='SETTLE';预期0SQLcreateorreplacedirectorydmp_diras'/home/data_space/exp';SQLgrantread,writeondirectorydmp_dirtopublic;1.5expdp按用户导出使用直接导出模式direct=yexp比传统模式导出快一倍oracle提供2种模式导出表数据，传统模式CONVENTIONALPATH和直接模式DIRECTPATH，有direct指定。setnls_lang=AMERICAN_AMERICA.AL32UTF8创建目录su-oraclemkdir-p/home/data_space/expsqlplus/assysdbaSQLselectcount(*)fromv$sessionwhereusername='SETTLE';预期0SQLcreateorreplacedirectorydmp_diras'/home/data_space/exp';SQLgrantread,writeondirectorydmp_dirtopublic;expdpzhengly/8605683directory=dmp_dirdumpfile=BAM_MDS_%U.dmplogfile=BAM_MDS.logparallel=10SCHEMAS=BAM_MDSexpdpzhengly/8605683directory=dmp_dirdumpfile=BAM_ORABAM