DBA DB이관 스크립트

1 DBA DB 이관 절차[편집]

1.1 TOBE 테이블스페이이스 생성 (ASIS DB는 DB링크 이용 )[편집]

-- TS 생성 SELECT 'CREATE TABLESPACE '||tablespace_name||' DATAFILE SIZE 1024M AUTOEXTEND ON NEXT 50M MAXSIZE 30G LOGGING ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;'

 FROM (

SELECT tablespace_name

 FROM DBA_TABLESPACES@DL_DBCAFE_DEV_DBCAFE_DBA -- DB링크 이용  ASIS DB조회

minus SELECT tablespace_name

 FROM DBA_TABLESPACES

order by 1 )

1.2 TOBE 테이블스페이이스 RESIZE[편집]

SELECT TABLESPACE_NAME, SIZEMB, FREEMB

    , 'ALTER TABLESPACE '||TABLESPACE_NAME||' RESIZE '||SIZEMB||'M;'
 FROM (
       SELECT B.TABLESPACE_NAME, TBS_SIZE SIZEMB, A.FREE_SPACE FREEMB
         FROM (  SELECT TABLESPACE_NAME
                      , ROUND (SUM (BYTES) / 1024 / 1024, 2)     AS FREE_SPACE
                   FROM DBA_FREE_SPACE@DL_DBCAFE_DEV_DBCAFE_DBA
               GROUP BY TABLESPACE_NAME
              ) A
            , (  SELECT TABLESPACE_NAME, SUM (BYTES) / 1024 / 1024 AS TBS_SIZE
                   FROM DBA_DATA_FILES@DL_DBCAFE_DEV_DBCAFE_DBA
                  GROUP BY TABLESPACE_NAME
                  UNION
                 SELECT TABLESPACE_NAME, SUM (BYTES) / 1024 / 1024 TBS_SIZE
                   FROM DBA_TEMP_FILES@DL_DBCAFE_DEV_DBCAFE_DBA
                  GROUP BY TABLESPACE_NAME
               ) B
         WHERE A.TABLESPACE_NAME(+) = B.TABLESPACE_NAME
      ) C
WHERE C.TABLESPACE_NAME LIKE 'TS%'

1.3 ASIS <=> TOBE 유저 비교[편집]

SELECT USERNAME,DEFAULT_TABLESPACE

 FROM DBA_USERS@DL_DBCAFE_DEV_DBCAFE_DBA

MINUS SELECT USERNAME,DEFAULT_TABLESPACE

 FROM DBA_USERS

1.3.1 ASIS 사용자 비교후 생성[편집]

select USERNAME,DEFAULT_TABLESPACE

    , 'CREATE USER '||USERNAME||' IDENTIFIED BY "dev01!" DEFAULT TABLESPACE '||DEFAULT_TABLESPACE||' TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK;GRANT CONNECT,RESOURCE TO '||USERNAME||';ALTER USER '||USERNAME||' DEFAULT ROLE ALL;ALTER USER '||USERNAME||' QUOTA UNLIMITED ON '||DEFAULT_TABLESPACE||';ALTER USER '||USERNAME||' QUOTA UNLIMITED ON '||REPLACE(DEFAULT_TABLESPACE,'D01','I01')||';' 
from (    
   SELECT USERNAME,DEFAULT_TABLESPACE
     FROM DBA_USERS@DL_DBCAFE_DEV_DBCAFE_DBA
   minus   
   SELECT USERNAME,DEFAULT_TABLESPACE
     FROM DBA_USERS
   order by 1

);

1.3.2 ASIS 사용자명 조회[편집]

SELECT USERNAME

 FROM DBA_USERS@DL_DBCAFE_DEV_DBCAFE_DBA -- ASIS
ORDER BY USERNAME

=== ASIS <=> TOBE TS 비교 -- ASIS TS 쿼터 조회 SELECT *

 FROM DBA_TS_QUOTAS@DL_DBCAFE_DEV_DBCAFE_DBA
ORDER BY USERNAME

-- TOBE TS 쿼터 조회 SELECT *

 FROM DBA_TABLESPACES;

1.4 ASIS <=> TOBE 사용자 권한 비교[편집]

-- 전환 <==> TOBE 운영 DB간 권한 비교 SELECT GRANTEE,PRIVILEGE,ADMIN_OPTION

     , 'GRANT '||  PRIVILEGE || ' TO ' ||  GRANTEE ||';'
 FROM (

-- ASIS

       SELECT GRANTEE,PRIVILEGE,ADMIN_OPTION
         FROM DBA_SYS_PRIVS@DL_DBCAFE_MIG_DBCAFE_MIG
        WHERE GRANTEE IN ('AMEX_OBT','AMEX_OBT_APP','AMEX_OBT_DEV','AMEX_DBCAFE','AMEX_DBCAFE_APP','AMEX_DBCAFE_DEV','BIZWMSG','DORMCUST','EDI','ERPAPP','ERPHR','ETAX','CYKIM','ERD_ADM','JJOBS','MIGBASEDATA','MIGCOMMON','OBT','OBTHT','OBT_DEV','OBT_APP','DBCAFE','DBCAFE_DEV','DBCAFE_APP','WEB','WEB_DEV','WEB_APP')

        MINUS

-- TOBE

       SELECT GRANTEE,PRIVILEGE,ADMIN_OPTION
         FROM DBA_SYS_PRIVS
        WHERE GRANTEE IN ('AMEX_OBT','AMEX_OBT_APP','AMEX_OBT_DEV','AMEX_DBCAFE','AMEX_DBCAFE_APP','AMEX_DBCAFE_DEV','BIZWMSG','DORMCUST','EDI','ERPAPP','ERPHR','ETAX','CYKIM','ERD_ADM','JJOBS','MIGBASEDATA','MIGCOMMON','OBT','OBTHT','OBT_DEV','OBT_APP','DBCAFE','DBCAFE_DEV','DBCAFE_APP','WEB','WEB_DEV','WEB_APP')
     );

-- 개발 <==> TOBE 운영 DB간 비교

SELECT GRANTEE,PRIVILEGE,ADMIN_OPTION

     , 'GRANT '||  PRIVILEGE || ' TO ' ||  GRANTEE ||';'
 FROM (
       SELECT GRANTEE,PRIVILEGE,ADMIN_OPTION
         FROM DBA_SYS_PRIVS@DL_DBCAFE_DEV_DBCAFE_DBA
        WHERE GRANTEE IN ('AMEX_OBT','AMEX_OBT_APP','AMEX_OBT_DEV','AMEX_DBCAFE','AMEX_DBCAFE_APP','AMEX_DBCAFE_DEV','BIZWMSG','DORMCUST','EDI','ERPAPP','ERPHR','ETAX','CYKIM','ERD_ADM','JJOBS','MIGBASEDATA','MIGCOMMON','OBT','OBTHT','OBT_DEV','OBT_APP','DBCAFE','DBCAFE_DEV','DBCAFE_APP','WEB','WEB_DEV','WEB_APP')
        MINUS 
       SELECT GRANTEE,PRIVILEGE,ADMIN_OPTION
         FROM DBA_SYS_PRIVS
        WHERE GRANTEE IN ('AMEX_OBT','AMEX_OBT_APP','AMEX_OBT_DEV','AMEX_DBCAFE','AMEX_DBCAFE_APP','AMEX_DBCAFE_DEV','BIZWMSG','DORMCUST','EDI','ERPAPP','ERPHR','ETAX','CYKIM','ERD_ADM','JJOBS','MIGBASEDATA','MIGCOMMON','OBT','OBTHT','OBT_DEV','OBT_APP','DBCAFE','DBCAFE_DEV','DBCAFE_APP','WEB','WEB_DEV','WEB_APP')
)

1.5 데이터 전환[편집]

1.5.1 전환계정 DBA권한 부여[편집]

GRANT ALTER ANY PROCEDURE TO MIGCOMMON WITH ADMIN OPTION ; GRANT ALTER ANY TABLE TO MIGCOMMON; GRANT CREATE ANY TABLE TO MIGCOMMON; GRANT CREATE ANY VIEW TO MIGCOMMON; GRANT DELETE ANY TABLE TO MIGCOMMON; GRANT DROP ANY TABLE TO MIGCOMMON; GRANT EXECUTE ANY PROCEDURE TO MIGCOMMON; GRANT INSERT ANY TABLE TO MIGCOMMON; GRANT SELECT ANY TABLE TO MIGCOMMON; GRANT UPDATE ANY TABLE TO MIGCOMMON; GRANT CREATE DATABASE LINK TO MIGCOMMON; GRANT UNLIMITED TABLESPACE TO MIGCOMMON;

1.5.2 전환 <==> TOBE 운영 오브젝트 건수 비교[편집]

-- 1.USER별 TYPE 건수 -- 전환 DB SELECT OWNER,OBJECT_NAME,OBJECT_TYPE

 FROM (
 -- 전환 DB 
       SELECT OWNER,OBJECT_NAME,OBJECT_TYPE
       FROM dba_objects@DL_DBCAFE_MIG_DBCAFE_MIG
       WHERE OWNER IN (
                       select USERNAME
                         from dba_users@DL_DBCAFE_MIG_DBCAFE_MIG 
                        where USERNAME IN ('AMEX_OBT','AMEX_OBT_APP','AMEX_OBT_DEV','AMEX_DBCAFE','AMEX_DBCAFE_APP','AMEX_DBCAFE_DEV','BIZWMSG','DORMCUST','EDI','ERPAPP','ERPHR','ETAX','CYKIM','ERD_ADM','JJOBS','MIGBASEDATA','MIGCOMMON','OBT','OBTHT','OBT_DEV','OBT_APP','DBCAFE','DBCAFE_DEV','DBCAFE_APP','WEB','WEB_DEV','WEB_APP')
                      )
          AND  OBJECT_NAME NOT LIKE 'SYS%'
       MINUS

-- TOBE 운영 DB

       SELECT OWNER,OBJECT_NAME,OBJECT_TYPE
       FROM dba_objects
       WHERE OWNER IN (
                       select USERNAME
                         from dba_users 
                        where USERNAME IN ('AMEX_OBT','AMEX_OBT_APP','AMEX_OBT_DEV','AMEX_DBCAFE','AMEX_DBCAFE_APP','AMEX_DBCAFE_DEV','BIZWMSG','DORMCUST','EDI','ERPAPP','ERPHR','ETAX','CYKIM','ERD_ADM','JJOBS','MIGBASEDATA','MIGCOMMON','OBT','OBTHT','OBT_DEV','OBT_APP','DBCAFE','DBCAFE_DEV','DBCAFE_APP','WEB','WEB_DEV','WEB_APP')
                      )
          AND  OBJECT_NAME NOT LIKE 'SYS%'

)

1.5.3 전환 대상 테이블 초기화[편집]

-- TRUNCATE TABLE SELECT OBJECT_TYPE,OWNER,OBJECT_NAME

    , 'TRUNCATE '||OBJECT_TYPE||' '||OWNER||'.'||OBJECT_NAME||';' DDL -- || DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINT',) ||' ;' DDL
 FROM DBA_OBJECTS 
WHERE --OBJECT_NAME LIKE '%TR_RFND_TGT%'
      OWNER = 'ERPAPP'
  AND OBJECT_TYPE = 'TABLE';

1.5.4 데이터 펌프[편집]

-- datapump 잡 확인 SELECT *

 FROM DBA_DATAPUMP_jOBS;

-- datapump 잡 세션 확인 SELECT *

 FROM DBA_DATAPUMP_sessions;

1.5.5 시퀀스 생성[편집]

-- 사용자별 시퀀스 생성 SELECT 'ALTER SEQUENCE '||SEQUENCE_OWNER||'.'||SEQUENCE_NAME||' INCREMENT BY -'||LAST_NUMBER||';' DDL

    , ' DROP SEQUENCE '||SEQUENCE_OWNER||'.'||SEQUENCE_NAME||';      
        CREATE SEQUENCE '||SEQUENCE_OWNER||'.'||SEQUENCE_NAME||'
              INCREMENT BY 1
             START WITH '||MIN_VALUE||'
               MAXVALUE '||MAX_VALUE||'
               MINVALUE '||MIN_VALUE||'
    '||DECODE(CYCLE_FLAG,'Y','CYCLE','NOCYCLE')||' CACHE '||CACHE_SIZE||'
                NOORDER
                 NOKEEP
                 GLOBAL;
         ' 
    , A.*
 FROM DBA_SEQUENCES A
WHERE SEQUENCE_OWNER = 'DBCAFE'