测试准备

2025-01-04 0 条评论 1243 次阅读

--从生产库做全库导出
expdp system/oracle@10.20.100.6/hhcs directory=dumpdir dumpfile=202408280930.dmp schemas=EPAYMENT2,FBEXCHANGE2,OPERATION,OPERATION2,RISK,RISK2,RISKSPEC,REPORT,SETTLE,SETTLEADMIN,SETTLEIN,SETTLEOUT,SYNC,SYNC2,SYNCDELTA,SYNCSPEC,TRADEADMIN logfile=202408280930.log

expdp system/oracle@10.20.100.6/hhcs directory=dumpdir dumpfile=202211011000.dmp full=y logfile=202211011000.log

--system执行
--执行下列脚本将清空原有数据库及删除表空间
drop user EPAYMENT2 cascade;
drop user FBEXCHANGE2 cascade;
drop user MIGRATE cascade;
drop user OPERATION cascade;
drop user OPERATION2 cascade;
drop user RISK cascade;
drop user RISK2 cascade;
drop user RISKSPEC cascade;
drop user REPORT cascade;
drop user SETTLE cascade;
drop user SETTLEADMIN cascade;
drop user SETTLEIN cascade;
drop user SETTLEOUT cascade;
drop user SYNC cascade;
drop user SYNC2 cascade;
drop user SYNCDELTA cascade;
drop user SYNCSPEC cascade;
drop user TRADEADMIN cascade;

drop tablespace TBS_EPAYMENT2 including contents and datafiles;
drop tablespace TBS_FBEXCHANGE2 including contents and datafiles;
drop tablespace TBS_MIGRATE including contents and datafiles;
drop tablespace TBS_RISK including contents and datafiles;
drop tablespace TBS_SETTLE including contents and datafiles;
drop tablespace TBS_SETTLEADMIN including contents and datafiles;
drop tablespace TBS_TRADEADMIN including contents and datafiles;

--system执行
--重新创建表空间并新建用户和赋权

CREATE TABLESPACE TBS_EPAYMENT2

LOGGING 
DATAFILE 
'/u02/oradata/hhcs/TBS_EPAYMENT2.dbf'
SIZE 4096M AUTOEXTEND
ON NEXT  4096M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT
SPACE MANAGEMENT  AUTO;

CREATE TABLESPACE TBS_FBEXCHANGE2

LOGGING 
DATAFILE 
'/u02/oradata/hhcs/TBS_FBEXCHANGE2.dbf'
SIZE 4096M AUTOEXTEND
ON NEXT  4096M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT
SPACE MANAGEMENT  AUTO;

CREATE TABLESPACE TBS_RISK

LOGGING 
DATAFILE 
'/u02/oradata/hhcs/TBS_RISK.dbf'
SIZE 4096M AUTOEXTEND
ON NEXT  4096M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT
SPACE MANAGEMENT  AUTO;

CREATE TABLESPACE TBS_SETTLE

LOGGING 
DATAFILE 
'/u02/oradata/hhcs/TBS_SETTLE.dbf'
SIZE 4096M AUTOEXTEND
ON NEXT  4096M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT
SPACE MANAGEMENT  AUTO;

CREATE TABLESPACE TBS_SETTLEADMIN

LOGGING 
DATAFILE 
'/u02/oradata/hhcs/TBS_SETTLEADMIN.dbf'
SIZE 30G AUTOEXTEND
ON NEXT  4096M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT
SPACE MANAGEMENT  AUTO;

ALTER TABLESPACE TBS_SETTLEADMIN ADD DATAFILE '/u02/oradata/hhcs/TBS_SETTLEADMIN01.DBF' SIZE 30G;
ALTER TABLESPACE TBS_SETTLEADMIN ADD DATAFILE '/u02/oradata/hhcs/TBS_SETTLEADMIN02.DBF' SIZE 30G;
ALTER TABLESPACE TBS_SETTLEADMIN ADD DATAFILE '/u02/oradata/hhcs/TBS_SETTLEADMIN03.DBF' SIZE 30G;
ALTER TABLESPACE TBS_SETTLEADMIN ADD DATAFILE '/u02/oradata/hhcs/TBS_SETTLEADMIN04.DBF' SIZE 30G;
ALTER TABLESPACE TBS_SETTLEADMIN ADD DATAFILE '/u02/oradata/hhcs/TBS_SETTLEADMIN05.DBF' SIZE 30G;
ALTER TABLESPACE TBS_SETTLEADMIN ADD DATAFILE '/u02/oradata/hhcs/TBS_SETTLEADMIN06.DBF' SIZE 30G;
ALTER TABLESPACE TBS_SETTLEADMIN ADD DATAFILE '/u02/oradata/hhcs/TBS_SETTLEADMIN07.DBF' SIZE 30G;
ALTER TABLESPACE TBS_SETTLEADMIN ADD DATAFILE '/u02/oradata/hhcs/TBS_SETTLEADMIN08.DBF' SIZE 30G;

CREATE TABLESPACE TBS_TRADEADMIN

LOGGING 
DATAFILE 
'/u02/oradata/hhcs/TBS_TRADEADMIN.dbf'
SIZE 4096M AUTOEXTEND
ON NEXT  4096M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT
SPACE MANAGEMENT  AUTO;

create user epayment2 identified by epayment2 default tablespace TBS_EPAYMENT2;
grant resource to epayment2;
grant connect to epayment2;

create user fbexchange2 identified by fbexchange2 default tablespace TBS_FBEXCHANGE2;
grant resource to fbexchange2;
grant connect to fbexchange2;

create user operation identified by operation default tablespace TBS_TRADEADMIN;
grant resource to operation;
grant connect to operation;

create user operation2 identified by operation2 default tablespace TBS_TRADEADMIN;
grant resource to operation2;
grant connect to operation2;

create user risk identified by risk default tablespace TBS_RISK;
grant resource to risk;
grant connect to risk;

create user risk2 identified by risk2 default tablespace TBS_RISK;
grant resource to risk2;
grant connect to risk2;

create user riskspec identified by riskspec default tablespace TBS_RISK;
grant resource to riskspec;
grant connect to riskspec;

create user report identified by report default tablespace TBS_SETTLEADMIN;
grant resource to report;
grant connect to report;
grant create view to report;

create user settle identified by settle default tablespace TBS_SETTLE;
grant resource to settle;
grant connect to settle;

create user settleadmin identified by settleadmin default tablespace TBS_SETTLEADMIN;
grant resource to settleadmin;
grant connect to settleadmin;
grant create view to settleadmin;

create user settlein identified by settlein default tablespace TBS_SETTLE;
grant resource to settlein;
grant connect to settlein;

create user settleout identified by settleout default tablespace TBS_SETTLE;
grant resource to settleout;
grant connect to settleout;

create user sync identified by sync default tablespace TBS_TRADEADMIN;
grant resource to sync;
grant connect to sync;

create user sync2 identified by sync2 default tablespace TBS_TRADEADMIN;
grant resource to sync2;
grant connect to sync2;

create user syncdelta identified by syncdelta default tablespace TBS_TRADEADMIN;
grant resource to syncdelta;
grant connect to syncdelta;

create user syncspec identified by syncspec default tablespace TBS_TRADEADMIN;
grant resource to syncspec;
grant connect to syncspec;

create user tradeadmin identified by tradeadmin default tablespace TBS_TRADEADMIN;
grant resource to tradeadmin;
grant connect to tradeadmin;

--sys执行这条需要sys用户单独执行
grant execute on DBMS_LOCK to settleadmin;
grant execute on DBMS_LOCK to fbexchange2;
grant execute on DBMS_LOCK to settle;

--system执行
grant drop any table to settleadmin;

--设置数据库字符集
export NLS_LANG=American_america.ZHS16GBK

--导入从生产库导出的全库备份
impdp system/oracle@hhcs directory=dumpdir dumpfile=2024.dmp schemas=EPAYMENT2,FBEXCHANGE2,OPERATION,OPERATION2,RISK,RISK2,RISKSPEC,REPORT,SETTLE,SETTLEADMIN,SETTLEIN,SETTLEOUT,SYNC,SYNC2,SYNCDELTA,SYNCSPEC,TRADEADMIN logfile=impdp.log

如果导入后出现ORA-39082: Object type PACKAGE_BODY:"REPORT"."PKGB_STATSREPORTMGRT" created with compilation warnings则在system用户下执行下面两句：
grant all on SettleAdmin.tmp_DepartMentAccount to report;
grant all on SettleAdmin.tmp_InstrumentCurrency to report;

导入完成进行赋权
--sys执行这条需要sys用户单独执行
grant execute on DBMS_LOCK to settleadmin;
grant execute on DBMS_LOCK to fbexchange2;
grant execute on DBMS_LOCK to settle;

--system执行
grant drop any table to settleadmin;

通过PSSQL 执行编译无效存储过程和包