目录
一、Linux环境导出、导入
--原环境导出
1、创建备份路径
SQL> create directory backup_mes as '/backup/expdp';
2、导出数据库
[oracle@localhost:/home/oracle]$expdp sys/oracle dumpfile=MESPRD_20210104130011.dmp logfile=MESPRD_20210104130011.dmp.log DIRECTORY=backup_mes full=y compression=all;
--原环境导入
3、删除原文件及用户
drop tablespace MES_PRD including contents and datafiles; -- 删除表空间 drop tablespace MES_MM including contents and datafiles; drop tablespace MES_MM_TEMP including contents and datafiles; drop tablespace MES_PRD_TEMP_NEW including contents and datafiles; drop user mes_prd cascade; --删除用户
4、创建数据表空间
SQL> CREATE tablespace MES_PRD DATAFILE '/oracle/app/oracle/oradata/mesorcl/MES_PRD_DATA.DBF' size 10240m autoextend on next 1000m; SQL> alter tablespace MES_PRD add DATAFILE '/oracle/app/oracle/oradata/mesorcl/MES_PRD_DATA01.dbf' size 10240m autoextend on next 1000m; SQL> alter tablespace MES_PRD add DATAFILE '/oracle/app/oracle/oradata/mesorcl/MES_PRD_DATA02.dbf' size 10240m autoextend on next 1000m; SQL> alter tablespace MES_PRD add DATAFILE '/oracle/app/oracle/oradata/mesorcl/MES_PRD_DATA03.dbf' size 10240m autoextend on next 1000m; SQL> alter tablespace MES_PRD add DATAFILE '/oracle/app/oracle/oradata/mesorcl/MES_PRD_DATA04.dbf' size 10240m autoextend on next 1000m; SQL> alter tablespace MES_PRD add DATAFILE '/oracle/app/oracle/oradata/mesorcl/MES_PRD_DATA05.dbf' size 10240m autoextend on next 1000m; SQL> alter tablespace MES_PRD add DATAFILE '/oracle/app/oracle/oradata/mesorcl/MES_PRD_DATA06.dbf' size 10240m autoextend on next 1000m; SQL> alter tablespace MES_PRD add DATAFILE '/oracle/app/oracle/oradata/mesorcl/MES_PRD_DATA07.dbf' size 10240m autoextend on next 1000m; SQL> alter tablespace MES_PRD add DATAFILE '/oracle/app/oracle/oradata/mesorcl/MES_PRD_DATA08.dbf' size 10240m autoextend on next 1000m; SQL> alter tablespace MES_PRD add DATAFILE '/oracle/app/oracle/oradata/mesorcl/MES_PRD_DATA09.dbf' size 10240m autoextend on next 1000m;
5、创建临时表空间
SQL> CREATE tablespace MES_MM logging DATAFILE '/oracle/app/oracle/oradata/mesorcl/MES_MM_DATA.DBF' size 10240m autoextend on next 1000m; SQL> CREATE temporary tablespace MES_MM_TEMP tempfile '/oracle/app/oracle/oradata/mesorcl/MES_MM_TEMP.DBF' size 10240m autoextend on next 1000m; SQL> CREATE temporary tablespace MES_PRD_TEMP_NEW tempfile '/oracle/app/oracle/oradata/mesorcl/MES_PRD_TEMP_NEW.DBF' size 10240m autoextend on next 1000m;
6、创建用户并赋予权限
SQL> create user mes_prd identified by "123" default tablespace MES_PRD temporary tablespace MES_PRD_TEMP_NEW profile DEFAULT; SQL> grant read,write on directory backup_mes to mes_prd ; SQL> grant connect,dba,resource to mes_prd ; SQL> grant unlimited tablespace to mes_prd with admin option;
7、导入数据
（1）文件指定导入
[oracle@localhost:/home/oracle]$impdp mes_prd/*** dumpfile=MESPRD_20210104130011.dmp logfile=MESPRD_20210104130011.dmp.log DIRECTORY=backup_mes SCHEMAs=mes_prd exclude=statistics
（2）指定顺序导入
impdp system/oracle dumpfile=MESROCL_20221019040001_expdp_%U.dmp logfile=MESORCL_20221019.dmp.log DIRECTORY=backup_mes SCHEMAs=mes_prd exclude=statistics
8、处理函数与过程编译
ALTER FUNCTION MES_PRD.HD_FN_OA_ISVERIFY compile; ALTER FUNCTION MES_PRD.ISNUMERIC compile; ALTER FUNCTION MES_PRD.MES_DZSTEPDATA compile; ALTER FUNCTION MES_PRD.MES_PRDGDCXML compile; ALTER FUNCTION MES_PRD.MES_PRDJCX compile; ALTER FUNCTION MES_PRD.MES_PRDMK compile; ALTER FUNCTION MES_PRD.MES_PRDPBCWXML compile; ALTER FUNCTION MES_PRD.MES_PRDUNITXML compile; ALTER FUNCTION MES_PRD.MES_PRDXML compile; ALTER FUNCTION MES_PRD.MES_PRDXUHAOXML compile; ALTER FUNCTION MES_PRD.ZM_EIP_LOG compile; ALTER FUNCTION MES_PRD.ZM_EIP_TASK_SUCCESS compile; ALTER FUNCTION MES_PRD.ZM_EIP_XML_DATA compile; ALTER FUNCTION MES_PRD.ZM_MES_PLAN_SOPFITEM compile; ALTER FUNCTION MES_PRD.ZM_MES_PRDSUBMIT compile; ALTER FUNCTION MES_PRD.ZM_MES_PRDSUBMIT_HIS compile; ALTER FUNCTION MES_PRD.ZM_MES_PRDSUBMIT_ITEM compile; ALTER FUNCTION MES_PRD.ZM_MES_PRD_JIGONG compile; ALTER FUNCTION MES_PRD.ZM_MES_SO_PFGXMAT compile; ALTER FUNCTION MES_PRD.ZM_MES_SO_PFGXMAT_R compile; ALTER FUNCTION MES_PRD.ZM_MES_SO_PFITEM compile; ALTER FUNCTION MES_PRD.ZM_MES_SO_PFITEMSEC compile; ALTER PROCEDURE MES_PRD.HD_SP_HR_COLLECTATTEND compile; ALTER PROCEDURE MES_PRD.HD_SP_HR_COLLECTDAYSALARY compile; ALTER PROCEDURE MES_PRD.HD_SP_HR_COLLECTPIECEWORK compile; ALTER PROCEDURE MES_PRD.HD_SP_HR_GETDAYSALARYLIST compile; ALTER PROCEDURE MES_PRD.HD_SP_HR_RGZFT compile; ALTER PROCEDURE MES_PRD.HD_SP_OA_GETJIABANVERIFYLIST compile; ALTER PROCEDURE MES_PRD.HD_SP_OA_GETPERFORMLOGSETTIONL compile; ALTER PROCEDURE MES_PRD.HD_SP_OA_SUBMITJIABAN compile; ALTER PROCEDURE MES_PRD.HD_SP_OA_TERMINATEJIABAN compile; ALTER PROCEDURE MES_PRD.HD_SP_OA_VERIFYJIABAN compile; ALTER PROCEDURE MES_PRD.HD_SP_PRD_DFFT2 compile; ALTER PROCEDURE MES_PRD.HD_SP_SD_GETGONGYILIST compile; ALTER PROCEDURE MES_PRD.MES_DEPT_STATICFORPRODUCT compile; ALTER PROCEDURE MES_PRD.MES_DRAWPRDXML compile; ALTER PROCEDURE MES_PRD.MES_INTEGRATEDKANBAN01 compile; ALTER PROCEDURE MES_PRD.MES_UPDATE_BANPGW compile; ALTER PROCEDURE MES_PRD.PROC_BK_MES_BACKUPS_ROLLBACK compile; ALTER PROCEDURE MES_PRD.PROC_CHECKFLOWSTATE compile; ALTER PROCEDURE MES_PRD.PROC_CREATEFLOWSTATE compile; ALTER PROCEDURE MES_PRD.P_ARCHIVING_TASK compile; ALTER PROCEDURE MES_PRD.P_PROWERSHARE_ARCHIVING_TASK compile; ALTER PROCEDURE MES_PRD.P_RESEND_TASK compile; ALTER PROCEDURE MES_PRD.P_RESEND_TASK_NEW compile; ALTER PROCEDURE MES_PRD.PROC_MES_TASKSCHEDULE compile;
二、Windows环境导出、导入
1、创建备份路径
SQL> create directory backup_mes as 'F:\DBBackup\MES_PRD';
2、导出数据库
[oracle@localhost:/home/oracle]$expdp sys/oracle dumpfile=MESPRD_20210104130011.dmp logfile=MESPRD_20210104130011.dmp.log DIRECTORY=backup_mes full=y compression=all;
3、创建数据表空间
SQL> CREATE tablespace MES_PRD logging DATAFILE'D:\app\MesData\MES_PRD_DATA.DBF' size 10240m autoextend on next 1000m; SQL> alter tablespace MES_PRD add DATAFILE'D:\app\MesData\MES_PRD_DATA01.DBF' size 10240m autoextend on next 1000m; SQL> alter tablespace MES_PRD add DATAFILE'D:\app\MesData\MES_PRD_DATA02.DBF' size 10240m autoextend on next 1000m; SQL> alter tablespace MES_PRD add DATAFILE'D:\app\MesData\MES_PRD_DATA03.DBF' size 10240m autoextend on next 1000m; SQL> alter tablespace MES_PRD add DATAFILE'D:\app\MesData\MES_PRD_DATA04.DBF' size 10240m autoextend on next 1000m; SQL> alter tablespace MES_PRD add DATAFILE'D:\app\MesData\MES_PRD_DATA05.DBF' size 10240m autoextend on next 1000m; SQL> alter tablespace MES_PRD add DATAFILE'D:\app\MesData\MES_PRD_DATA06.DBF' size 10240m autoextend on next 1000m; SQL> alter tablespace MES_PRD add DATAFILE'D:\app\MesData\MES_PRD_DATA07.DBF' size 10240m autoextend on next 1000m; SQL> alter tablespace MES_PRD add DATAFILE'D:\app\MesData\MES_PRD_DATA08.DBF' size 10240m autoextend on next 1000m; SQL> alter tablespace MES_PRD add DATAFILE'D:\app\MesData\MES_PRD_DATA09.DBF' size 10240m autoextend on next 1000m;
4、创建临时表空间
SQL> CREATE tablespace MES_MM logging DATAFILE'D:\app\MesData\MES_MM_DATA.DBF' size 10240m autoextend on next 1000m; SQL> CREATE temporary tablespace MES_MM_TEMP tempfile 'D:\app\MesData\MES_MM_TEMP.DBF' size 10240m autoextend on next 1000m; SQL> CREATE temporary tablespace MES_PRD_TEMP_NEW tempfile 'D:\app\MesData\MES_PRD_TEMP_NEW.DBF' size 10240m autoextend on next 1000m;
5、创建用户并赋予权限
SQL> create user mes_prd identified by "123" default tablespace MES_PRD temporary tablespace MES_PRD_TEMP_NEW profile DEFAULT; SQL> grant read,write on directory backup_mes to mes_prd ; SQL> grant connect,dba,resource to mes_prd ; SQL> grant unlimited tablespace to mes_prd with admin option;
6、导入数据
[oracle@localhost:/home/oracle]$impdp mes_prd/*** dumpfile=MESPRD_20210104130011.dmp logfile=MESPRD_20210104130011.dmp.log DIRECTORY=backup_mes SCHEMAs=mes_prd exclude=statistics
7、处理函数与过程编译
ALTER FUNCTION MES_PRD.HD_FN_OA_ISVERIFY compile; ALTER FUNCTION MES_PRD.ISNUMERIC compile; ALTER FUNCTION MES_PRD.MES_DZSTEPDATA compile; ALTER FUNCTION MES_PRD.MES_PRDGDCXML compile; ALTER FUNCTION MES_PRD.MES_PRDJCX compile; ALTER FUNCTION MES_PRD.MES_PRDMK compile; ALTER FUNCTION MES_PRD.MES_PRDPBCWXML compile; ALTER FUNCTION MES_PRD.MES_PRDUNITXML compile; ALTER FUNCTION MES_PRD.MES_PRDXML compile; ALTER FUNCTION MES_PRD.MES_PRDXUHAOXML compile; ALTER FUNCTION MES_PRD.ZM_EIP_LOG compile; ALTER FUNCTION MES_PRD.ZM_EIP_TASK_SUCCESS compile; ALTER FUNCTION MES_PRD.ZM_EIP_XML_DATA compile; ALTER FUNCTION MES_PRD.ZM_MES_PLAN_SOPFITEM compile; ALTER FUNCTION MES_PRD.ZM_MES_PRDSUBMIT compile; ALTER FUNCTION MES_PRD.ZM_MES_PRDSUBMIT_HIS compile; ALTER FUNCTION MES_PRD.ZM_MES_PRDSUBMIT_ITEM compile; ALTER FUNCTION MES_PRD.ZM_MES_PRD_JIGONG compile; ALTER FUNCTION MES_PRD.ZM_MES_SO_PFGXMAT compile; ALTER FUNCTION MES_PRD.ZM_MES_SO_PFGXMAT_R compile; ALTER FUNCTION MES_PRD.ZM_MES_SO_PFITEM compile; ALTER FUNCTION MES_PRD.ZM_MES_SO_PFITEMSEC compile; ALTER PROCEDURE MES_PRD.HD_SP_HR_COLLECTATTEND compile; ALTER PROCEDURE MES_PRD.HD_SP_HR_COLLECTDAYSALARY compile; ALTER PROCEDURE MES_PRD.HD_SP_HR_COLLECTPIECEWORK compile; ALTER PROCEDURE MES_PRD.HD_SP_HR_GETDAYSALARYLIST compile; ALTER PROCEDURE MES_PRD.HD_SP_HR_RGZFT compile; ALTER PROCEDURE MES_PRD.HD_SP_OA_GETJIABANVERIFYLIST compile; ALTER PROCEDURE MES_PRD.HD_SP_OA_GETPERFORMLOGSETTIONL compile; ALTER PROCEDURE MES_PRD.HD_SP_OA_SUBMITJIABAN compile; ALTER PROCEDURE MES_PRD.HD_SP_OA_TERMINATEJIABAN compile; ALTER PROCEDURE MES_PRD.HD_SP_OA_VERIFYJIABAN compile; ALTER PROCEDURE MES_PRD.HD_SP_PRD_DFFT2 compile; ALTER PROCEDURE MES_PRD.HD_SP_SD_GETGONGYILIST compile; ALTER PROCEDURE MES_PRD.MES_DEPT_STATICFORPRODUCT compile; ALTER PROCEDURE MES_PRD.MES_DRAWPRDXML compile; ALTER PROCEDURE MES_PRD.MES_INTEGRATEDKANBAN01 compile; ALTER PROCEDURE MES_PRD.MES_UPDATE_BANPGW compile; ALTER PROCEDURE MES_PRD.PROC_BK_MES_BACKUPS_ROLLBACK compile; ALTER PROCEDURE MES_PRD.PROC_CHECKFLOWSTATE compile; ALTER PROCEDURE MES_PRD.PROC_CREATEFLOWSTATE compile; ALTER PROCEDURE MES_PRD.P_ARCHIVING_TASK compile; ALTER PROCEDURE MES_PRD.P_PROWERSHARE_ARCHIVING_TASK compile; ALTER PROCEDURE MES_PRD.P_RESEND_TASK compile; ALTER PROCEDURE MES_PRD.P_RESEND_TASK_NEW compile; ALTER PROCEDURE MES_PRD.PROC_MES_TASKSCHEDULE compile;
文章评论