Oracle数据库备份/迁移

1.  登陆29.1.20.16oracle服务器
   

2.  sqlplus / as sysdba;											*数据库管理员登陆
   

select * from dba_directories;									--查看目录
create or replace directory dpdata1 as '/home/oracle/dump/'; 	--新建或替换目录/home/oracle/dump/
grant read,write on directory dpdata1 to esbsg;				--给mjsg用户赋权

4.  expdp mjsg/mjsg@//29.1.20.16:1521/gzhdb schemas=mjsg dumpfile=expdp_sg_20181227.dmp DIRECTORY=dpdata1;
    *导出数据库
   

5.  将/home/oracle/dump/expdp_sg_20181227.dmp文件 保存到电脑本地目录下，如：C:\Users\Name\Desktop\123
   

6.  在本地Oracle数据库登陆管理员用户，创建 mjsg 表空间和 mjsg 用户，如下：
   

--执行之前请先确认表空间文件路径以及表空间大小
--创建治理平台表空间，注意修改 DATAFILE 路径
CREATE TABLESPACE MJSG DATAFILE 
  '$ORACLE_BASE/oradata/mjsg.dbf' SIZE 256M AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

--创建ESBDATA用户并授权
create user mjsg identified by mjsg
default tablespace MJSG 
temporary tablespace temp quota unlimited on mjsg;
--password expire;
grant create procedure to mjsg with admin option;
grant create sequence to mjsg;
grant create session to mjsg;
grant create synonym to mjsg;
grant create table to mjsg;
grant create trigger to mjsg;
grant create type to mjsg;
grant create view to mjsg;

grant select on V_$SQLAREA to mjsg;
grant select on V_$SESSION to mjsg;
grant select on V_$LOCKED_OBJECT to mjsg;
grant select on V_$PARAMETER to mjsg;
grant select on V_$PROCESS to mjsg;
grant select on V_$RECOVERY_FILE_DEST to mjsg;
grant select on DBA_FREE_SPACE to mjsg;
grant select on DBA_DATA_FILES to mjsg;

7. Win+R 输入cmd回车
   

8. sqlplus / as sysdba														--数据库管理员登陆
   

create or replace directory dpdata1 as 'C:\Users\Name\Desktop\123';	--创建路径为'C:\Users\Name\Desktop\123'的目录 dpdata1
select * from dba_directories;											--查看目录是否创建成功
grant read,write on directory dpdata1 to mjsg;							--赋予mjsg用户读写权限
exit--退出数据库管理员用户，并登陆mjsg用户

9. impdp mjsg/mjsg@//localhost:1521/orcl DIRECTORY=dpdata1 DUMPFILE=expdp_sg_20181227.dmp SCHEMAS=mjsg TABLE_EXISTS_ACTION=append
   *将mjsg数据库导入本地，TABLE_EXISTS_ACTION有4个属性，append追加，replace替换