oracle数据库生产,生产环境oracle 数据库迁移操作

#######################10.1.18.150/152#########################

create or replace directory expdp as '/bakcup5t';

grant read,write on directory expdp to ISCML,ISCAS,ISC_UAP_TEMP,ISC;

expdp iscml/iscml directory=expdp dumpfile =expdp_ISCML20151204.dmp logfile=expdp_ISCML20151204.log;

expdp isc_uap_temp/isc_uap_temp directory=expdp dumpfile =expdp_isc_uap_temp20151204.dmp logfile=expdp_isc_uap_temp20151204.log;

expdp isc/isc directory=expdp dumpfile =expdp_isc20151204.dmp logfile=expdp_isc220151204.log;

expdp iscas/iscas directory=expdp dumpfile =expdp_iscas20151204.dmp logfile=expdp_iscas20151204.log tables=OPERATE_OBJ,OPERATE_TYPE,SAS_REPORT_FILES_LOG,SAS_STANDARD_LOG_TYPE,SAS_STATISTICAL_RULES,SAS_STATISTICS_DAY_LOGIN_COUNT,SAS_STATISTICS_DAY_LOGIN_USERS,SAS_STATISTICS_SP_VISIT_COUNT,SAS_STANDARD_DB_LOG_201512;

########expdp iscas/tyqx_iscas directory=expdp dumpfile =expdp_iscasmeta20151204.dmp logfile=expdp_iscas20151204.log content=metadata_only;

#####################################10.4.34.145isc##########################################

mount -t nfs -o rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,vers=3,timeo=600,actimeo=0 10.4.35.245:/TYZYDisk5T /bakcup5t

drop user isc_uap_temp cascade;

drop user isc cascade;

drop user iscml cascade;

create user isc_uap_temp identified by tyqx_isc_uap_temp default tablespace  isc_uap_temp_tbs account unlock;

create user isc identified by tyqx_isc default tablespace  isc_tbs account unlock;

create user iscml identified by tyqx_iscml default tablespace  isc_tbs account unlock;

grant CREATE JOB,DROP ANY MATERIALIZED VIEW,UPDATE ANY TABLE,UNLIMITED TABLESPACE,ALTER SESSION,CREATE ANY VIEW,ALTER SYSTEM,GLOBAL QUERY REWRITE,CREATE ANY MATERIALIZED VIEW,ON COMMIT REFRESH,DROP ANY VIEW,EXECUTE ANY PROCEDURE,CONNECT,EXP_FULL_DATABASE,IMP_FULL_DATABASE,RESOURCE to isc;

grant EXECUTE ANY PROCEDURE,UNLIMITED TABLESPACE,CONNECT,RESOURCE to iscml;

grant UNLIMITED TABLESPACE,CONNECT,RESOURCE to isc_uap_temp;

create or replace directory expdp as '/bakcup5t';

grant read,write on directory expdp to ISCML,ISC_UAP_TEMP,ISC;

impdp iscml/tyqx_iscml  dumpfile =expdp_ISCML20151204.dmp logfile=impdp_ISCML20151204.log directory=expdp;

impdp isc_uap_temp/tyqx_isc_uap_temp  dumpfile =expdp_isc_uap_temp20151204.dmp logfile=impdp_isc_uap_temp20151204.log directory=expdp;

impdp isc/tyqx_isc  dumpfile =expdp_isc20151204.dmp logfile=impdp_isc_uap_temp20151204.log directory=expdp;

######################################10.4.34.156iscsj#################################################

mount -t nfs -o rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,vers=3,timeo=600,actimeo=0 10.4.35.245:/TYZYDisk5T /bakcup5t

drop user iscas cascade;

create user iscas identified by tyqx_iscas default tablespace  iscas_tbs account unlock;

grant UNLIMITED TABLESPACE,CONNECT,RESOURCE to iscas;

create or replace directory expdp as '/bakcup5t';

grant read,write on directory expdp to iscas;

impdp iscas/tyqx_iscas directory=expdp dumpfile =expdp_iscas20151204.dmp logfile=impdp_iscas20151204.log;

1.打开归档

alter system set log_archive_dest_1="location=+ARCH" scope=spfile;

shutdown immediate;                  ---两节点

startup mount;                       ---两节点

alter database archivelog;

alter database open;                 ---两节点

archive log list

###isc       CREATE JOB,DROP ANY MATERIALIZED VIEW,UPDATE ANY TABLE,UNLIMITED TABLESPACE,ALTER SESSION,CREATE ANY VIEW,ALTER SYSTEM,GLOBAL QUERY REWRITE,CREATE ANY MATERIALIZED VIEW,ON COMMIT REFRESH,DROP ANY VIEW,EXECUTE ANY PROCEDURE,

CONNECT,EXP_FULL_DATABASE,IMP_FULL_DATABASE,RESOURCE

####iscml     EXECUTE ANY PROCEDURE,UNLIMITED TABLESPACE,

CONNECT,RESOURCE

####isc_uap_temp      UNLIMITED TABLESPACE

CONNECT,RESOURCE

###iscas            UNLIMITED TABLESPACE

CONNECT,RESOURCE

########审计用户中需要导出的表：

OPERATE_OBJ,OPERATE_TYPE,SAS_REPORT_FILES_LOG,SAS_STANDARD_LOG_TYPE,SAS_STATISTICAL_RULES,SAS_STATISTICS_DAY_LOGIN_COUNT,SAS_STATISTICS_DAY_LOGIN_USERS,SAS_STATISTICS_SP_VISIT_COUNT,SAS_STANDARD_DB_LOG_201512

其他表可只导表结构，不导表数据