dump 迁移oracle db

分手后的思念是犯贱 2022-07-12 03:39 237阅读 0赞

\-- 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);