dump 迁移oracle db-白红宇

dump 迁移oracle db

阅读量：4069 次

发布时间：2019-05-25

本文共 2452 字，大约阅读时间需要 8 分钟。

-- 1:创建表空间

-- data -> /data1, index -> /data3，请按需要修改----可以用df -h 查看有幾個data

select 'CREATE TABLESPACE '||tablespace_name||' DATAFILE '||case instr(upper(tablespace_name),'IND') when 0 then '''/data1' else '''/data3' end

||'/oradata/shpnf1qa/'||lower(tablespace_name)||'01.dbf'' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE 8G;' as tbs

from dba_tablespaces

where tablespace_name not in('SYSTEM','SYSAUX','UNDOTBS1','TEMP','OGG')

-- 2 创建角色

select 'CREATE ROLE '||role||' NOT IDENTIFIED;'

from dba_roles where role not in(

select ''''||role||''',' from dba_roles; -- 先用此SQL跑新DB，把结果填到下面，再去旧DB跑整个SQL

)and role not in 'GGS_GGSUSER_ROLE'

-- 3 导出旧DB的Schemas（metadata only）,请务必注意ORACLE_SID，同Server多版本ORACLE的还要注意ORACLE_HOME

select count(*)

--,get_charcount(wm_concat(username),','),wm_concat(username)

from dba_users where username not in(

select ''''||username||''',' from dba_users; -- 先用此SQL跑新DB，把结果填到下面，再去旧DB跑整个SQL

） and username not in('OGG','PERFSTAT');

select get_charcount('xxx',',') CHAR_CNT

from dual

-- dump前请检查新旧DB上是否有DUMP_DIR目录，没有就创建并授权

CREATE OR REPLACE DIRECTORY DUMP_DIR AS '/u02/dmp';

GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO system

GRANT READ, WRITE ON DIRECTORY DUMP_DIR TO public

expdp system/pegadb*system schemas=xxx directory=DUMP_DIR content=metadata_only dumpfile=20130708_shpndb0_schemas.dmp logfile=20130708_shpndb0_schemas.log

impdp system/pegadb*system schemas=xxx directory=DUMP_DIR content=metadata_only dumpfile=20130708_shpndb0_schemas.dmp logfile=20130708_shpndb0_schemas.imp.log

-- 4 创建同义词

select 'create public synonym '||synonym_name||' for '||table_owner||'.'||table_name||';'

from dba_synonyms where table_owner in ('TP','ET_EAI','TSP_PADB')

-- 5 授权系统表to Public

grant select on dba_tab_privs to public;

grant select on dba_source to public;

grant select on dba_jobs to public;

grant select on v_$lock to public;

grant select on v_$session to public;

-- grant sys table to MONDB

select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';'

from dba_tab_privs where grantee='MONDB';

搜出結果然後在新DB上執行

-- 6 编译失效Objects

GRANT SELECT ON SYS.DBA_OBJECTS TO PUBLIC;

GRANT SELECT ON SYS.DBA_LOG_GROUPS TO PUBLIC;

EXEC dbms_utility.compile_schema('OGG',false);

EXEC dbms_utility.compile_schema('TP',false);

EXEC dbms_utility.compile_schema('ET_EAI',false);

EXEC dbms_utility.compile_schema('TSP_PADB',false);

EXEC dbms_utility.compile_schema('MONDB',false);

EXEC dbms_utility.compile_schema('PUBLIC',false);

转载地址：http://ykhji.baihongyu.com/

你可能感兴趣的文章