행위

DBA DB이관 스크립트

DB CAFE

Dbcafe (토론 | 기여)님의 2024년 6월 22일 (토) 10:31 판 (→‎DBA DB 이관 절차)
(차이) ← 이전 판 | 최신판 (차이) | 다음 판 → (차이)
thumb_up 추천메뉴 바로가기

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'
;
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=DBA_DB이관_스크립트&oldid=11953"