"DBA DB이관 스크립트"의 두 판 사이의 차이
DB CAFE
(→DBA DB 이관 절차) |
|||
207번째 줄: | 207번째 줄: | ||
; | ; | ||
</source> | </source> | ||
+ | [[category:oracle]] |
2024년 7월 23일 (화) 18:18 기준 최신판
thumb_up 추천메뉴 바로가기
- 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'
;