DBA DB이관 스크립트
DB CAFE
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
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'