MIGRATION 절차

1.EMTEST DB의 환경 설정 확인
 1)캐릭터셋 확인
select * from nls_database_parameters;
 
 2)user별 object 확인
select owner, object_type, count(*) from dba_objects
     where object_type in ('TABLE', 'INDEX', 'VIEW', 'PROCEDURE', 'FUNCTION')
     group by owner, object_type
     order by 1, 2;
    
  3)테이블스페이스 사용량 및 DB전체 사용량확인
column tablespace_name heading Tablespace format a19
column object_count heading 'Objects|(#)' format 999990
column mb heading Mb format 9999990
column sum(frags) heading 'Frags|(#)' format 9999
column avail heading 'Max|(Mb)' format 999999.99
column free heading 'Free|(%)' format 9999999.9
column bytesize heading 'Size|(Mb)' format 9999999
column byteused heading 'Used|(Mb)' format 9999990
column bytefree heading 'Free|(Mb)' format 9999999
column init_ext heading 'Initial|(K)' format 999999
column next_ext heading 'Next|(K)' format 999999
break on report
compute sum of object_count bytesize byteused bytefree on report

select tablespace_name,sum(obj_cnt) object_count,sum(ini_ext) init_ext,sum(nex_ext) next_ext,sum(byte)/1048576 bytesize,
 (sum(byte)/1048576)- (sum(fbyte)/1048576) byteused,sum(fbyte)/1048576 bytefree, sum(frags), sum(largest)/1048576 avail,
 (sum(fbyte)/decode(sum(byte),0,1,sum(byte)) )*100 free
from (select tablespace_name,0 obj_cnt,0 ini_ext,0 nex_ext, 0 byte, sum(bytes) fbyte, count(*) frags, max(bytes) largest
from dba_free_space
group by tablespace_name
union
select tablespace_name,0, 0, 0, sum(bytes), 0, 0, 0 from dba_data_files
group by tablespace_name
union
select tablespace_name, 0, initial_extent/1024 ini_ext, next_extent/1024 nex_ext, 0,0,0,0
from dba_tablespaces
union
select tablespace_name, count(*) obj_cnt, 0, 0, 0, 0, 0, 0
from dba_segments
group by tablespace_name)
group by tablespace_name ;
tti off

 4) user별 default_tablespace 확인
select username, default_tablespace, temporary_tablespace from dba_users
     where username not in ('SYS', 'SYSTEM');
 
 5) tablespace 생성 구문 추출
 set lines 200
 set pages 200
 col file_name for a50
 col tablespace_name for a20
 col Mbytes for a20
 spool create_tablespace.sql
 set echo off;
 set heading off;
 select ' create tablespace ' || tablespace_name ||
     ' datafile ''' || file_name || ''' size ' || bytes/1024/1024 || 'M autoextend off; '
     from dba_data_files
     where tablespace_name not in ('SYSTEM', 'USER', 'RBS', 'TOOLS', 'TEMP')
     and tablespace_name not like '%TMP%'
     and tablespace_name not like '%TEMP%';
 
 6) user 생성 구문 추출
 select 'CREATE USER ' || username || ' IDENTIFIED BY ' || username ||
' DEFAULT TABLESPACE ' || default_tablespace ||
' TEMPORARY TABLESPACE ' || temporary_tablespace ||';'
from dba_users
where username not in ('SYS','SYSTEM');

  7) user별 권한 생성 추출
select ' grant connect, resource to ' || username ||';'
     from dba_users
     where username not in ('SYS', 'SYSTEM');
    

2.EMTEST DB EXPORT FULL 백업 수행
 exp system/oracle file=emctl_full.dmp log=emctl_exp.log full=y
 
3.신규DB(EMTEST1)에 user,tablespace 생성
 1-5),6),7) 스크립트 참조
 
4.EMTEST EXPORT FULL 백업 받은 dumpfile을 이용해 신규DB(EMTEST1) user별로 IMPORT 수행
 1)user별 import 구문 추출
set pages 200
set lines 200
col file_name for a50
col tablespace_name for a20
col Mbytes for a20
spool import_user.sh
set echo off;
set heading off;
select 'imp system/oracle file=' || username || '.dmp fromuser=' || username ||
' touser=' || username || ' log=' || username || '.log'
from dba_users
where username not in ('SYS', 'SYSTEM');

5.IMPORT 수행 후 각 user별 data확인