RMAN之一:快速入门
来源:互联网 发布:arena仿真优化 编辑:程序博客网 时间:2024/05/19 01:10
1、数据导出基础
(1)创建datapump导出文件的目录对象并为相应用户授予权限。
出于安全考虑,不允许oracle用户直接在OS上进行文件的操作,而应通过directory对象指定。
SQL> create directory dpump_dir1 AS '/opt/oracle/admin/orcl11g/dpdump'; Directory created. SQL> grant read, write on directorydpump_dir1 to scott; Grant succeeded.
(2)导出
[oracle@lujinhong dpdump]$ expdpscott/tiger directory=DPUMP_DIR1 dumpfile=scott.dmp nologfile=y Export: Release 11.2.0.1.0 - Productionon Tue Apr 23 20:24:44 2013 Copyright (c) 1982, 2009, Oracle and/orits affiliates. All rights reserved. Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Miningand Real Application Testing optionsStarting"SCOTT"."SYS_EXPORT_SCHEMA_01": scott/******** directory=DPUMP_DIR1dumpfile=scott.dmp nologfile=yEstimate in progress using BLOCKSmethod...Processing object typeSCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method:192 KBProcessing object typeSCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object typeSCHEMA_EXPORT/TABLE/TABLEProcessing object typeSCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object typeSCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object typeSCHEMA_EXPORT/TABLE/COMMENTProcessing object typeSCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object typeSCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported"SCOTT"."DEPT" 5.937 KB 4 rows. . exported"SCOTT"."EMP" 8.570 KB 14 rows. . exported"SCOTT"."SALGRADE" 5.867 KB 5 rows. . exported"SCOTT"."BONUS" 0 KB 0 rowsMaster table"SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded******************************************************************************Dump file set forSCOTT.SYS_EXPORT_SCHEMA_01 is: /opt/oracle/admin/orcl11g/dpdump/scott.dmpJob"SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at20:25:08
注意:directory=DPUMP_DIR1中的DPUMP_DIR1应该在dba_directories中存在,否则应该在第一步中先创建然后授权。
2、数据导入基础
若即将导入的用户或者表空间不存在,则必须先创建用户及表空间。
[oracle@lujinhong dpdump]$ impdp sys/Lu123456 directory=dpump_dir1dumpfile=scott.dmp Import: Release 11.2.0.1.0 - Productionon Tue Apr 23 20:27:30 2013 Copyright (c) 1982, 2009, Oracle and/orits affiliates. All rights reserved. UDI-28009: operation generated ORACLEerror 28009ORA-28009: connection as SYS should beas SYSDBA or SYSOPER Username: sys as sysdbaPassword: Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Miningand Real Application Testing optionsMaster table"SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloadedStarting"SYS"."SYS_IMPORT_FULL_01": sys/******** AS SYSDBA directory=dpump_dir1 dumpfile=scott.dmpProcessing object typeSCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object typeSCHEMA_EXPORT/TABLE/TABLEORA-39151: Table"SCOTT"."DEPT" exists. All dependent metadata and data willbe skipped due to table_exists_action of skipORA-39151: Table "SCOTT"."EMP"exists. All dependent metadata and data will be skipped due totable_exists_action of skipORA-39151: Table"SCOTT"."BONUS" exists. All dependent metadata and datawill be skipped due to table_exists_action of skipORA-39151: Table"SCOTT"."SALGRADE" exists. All dependent metadata and datawill be skipped due to table_exists_action of skipProcessing object typeSCHEMA_EXPORT/TABLE/TABLE_DATAProcessing object typeSCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object typeSCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object typeSCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object typeSCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object typeSCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSJob "SYS"."SYS_IMPORT_FULL_01"completed with 4 error(s) at 20:27:41由于原来已经存在这些表,因此先删除再导入:SQL> drop table emp; Table dropped. SQL> select * from emp;select * from emp *ERROR at line 1:ORA-00942: table or view does not exist [oracle@lujinhongdpdump]$ impdp sys/Lu123456 directory=dpump_dir1 dumpfile=scott.dmpschemas=scott Import:Release 11.2.0.1.0 - Production on Tue Apr 23 20:31:37 2013 Copyright (c)1982, 2009, Oracle and/or its affiliates. All rights reserved. UDI-28009:operation generated ORACLE error 28009ORA-28009:connection as SYS should be as SYSDBA or SYSOPER Username: sysas sysdbaPassword: Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith thePartitioning, OLAP, Data Mining and Real Application Testing optionsMaster table"SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloadedStarting"SYS"."SYS_IMPORT_SCHEMA_01": sys/******** AS SYSDBA directory=dpump_dir1dumpfile=scott.dmp schemas=scottProcessingobject type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessingobject type SCHEMA_EXPORT/TABLE/TABLEORA-39151:Table "SCOTT"."DEPT" exists. All dependent metadata anddata will be skipped due to table_exists_action of skipORA-39151:Table "SCOTT"."BONUS" exists. All dependent metadata anddata will be skipped due to table_exists_action of skipORA-39151:Table "SCOTT"."SALGRADE" exists. All dependent metadata anddata will be skipped due to table_exists_action of skipProcessingobject type SCHEMA_EXPORT/TABLE/TABLE_DATA. .imported "SCOTT"."EMP" 8.570 KB 14 rowsProcessingobject type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessingobject type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessingobject type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessingobject type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessingobject type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSJob"SYS"."SYS_IMPORT_SCHEMA_01" completed with 3 error(s) at20:31:44
3、导出、导入参数
不管是expdp还是impdp均存在大量的参数,以对导入导出过程进行灵活的配置,详见《数据库管理艺术》第14章。
(1)重映射参数【导入参数】
一般情况下,不管使用哪个用户进行导入,导入的数据均归属到数据原有的用户名下。如使用sys用户进行导入操作,但数据是从scott导出的,则数据将被导入至scott模式下。除非使用remap_schema参数。
[oracle@datatest_db1_160 irms_gd]$ impdpsystem/Lu123456 directory=dpump_dir dumpfile=scott.dmp remap_schema=scott:test_datapump
类似的参数还有remap_table,remap_tablespace,remap_datafile, remap_data。
4、实例
从项目中把表名中包含BTS的表导出,然后导入至测试库中。
(1)导出
SQL>select * from dba_directories;bash-4.1$ expdpHN_CM_IRMS_35/HN_CM_IRMS_351 directory=EXPDIR DUMPFILE=HN.DMPinclude=TABLE\:\"LIKE \'%BTS%\'\"(2)导入
[oracle@lujinhong /]$ impdp HN_CM_IRMS_35/HN_CM_IRMS_351directory=dpump_dir1 dumpfile=HN1.DMP5、项目导出语句
#!/bin/bashexport ORACLE_BASE=/opt/ORACLEexport ORACLE_SID=IRMSexportORACLE_HOME=/opt/ORACLE/irms/product/10.2.0/db_1exportPATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:$HOME/bin:/usr/bin/X11:/sbin:export PATH=$ORACLE_HOME/bin:$PATHuser='system'passwd='Ora_8109#'datestr=`date +"%Y%m%d"`expdp ${user}/${passwd}directory=expdp_bak dumpfile=XZ_CM_IRMS_35_${datestr}.dmp logfile=XZ_CM_IRMS_35_${datestr}.log schemas=XZ_CM_IRMS_35 exclude=statistics
在导入时,可以使用replace参数,这样的话就不需要先手工删除原有数据,再进行导入。
0 0
- RMAN之一:快速入门
- RMAN之一:快速入门
- RMAN快速入门指南
- RMAN快速入门指南
- Oracle RMAN快速入门
- Oracle RMAN快速入门指南
- Oracle RMAN快速入门指南
- Oracle RMAN快速入门指南
- Oracle RMAN快速入门指南
- Oracle RMAN快速入门指南
- Oracle RMAN快速入门指南
- Oracle RMAN快速入门指南
- Smarty快速入门之一
- JDBC之一:快速入门
- JDBC之一:快速入门
- (转)Oracle RMAN快速入门指南
- (转)Oracle RMAN快速入门指南
- RMAN快速入门指南(转载)
- VIM默认配置
- SDWebImage图片的处理
- Linux shell脚本的字符串截取
- 枚举是个球
- 通过java程序获得系统常用配置
- RMAN之一:快速入门
- android.view.WindowManager$BadTokenException: Unable to add window(转)
- NYOJ 55 懒省事的小明
- springMVC系列之与spring3.2 , hibernate4.1.6整合——08
- extern "C" __declspec(dllexport) __declspec(dllimport) 和 def
- GunStreet移动SDK支付(弱联网)
- 黑马程序员_07_异常
- (tandard c libraries translation )flock
- UML之状态图