"DBA DB이관 스크립트"의 두 판 사이의 차이
DB CAFE
(새 문서: == DBA DB 이관 절차 == === TOBE 테이블스페이이스 생성 (ASIS DB는 DB링크 이용 ) === -- TS 생성 SELECT 'CREATE TABLESPACE '||tablespace_name||' DATAFILE SIZE 10...) |
(→DBA DB 이관 절차) |
||
2번째 줄: | 2번째 줄: | ||
=== TOBE 테이블스페이이스 생성 (ASIS DB는 DB링크 이용 ) === | === TOBE 테이블스페이이스 생성 (ASIS DB는 DB링크 이용 ) === | ||
− | + | <source lang=sql> | |
-- TS 생성 | -- 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;' | 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;' | ||
14번째 줄: | 14번째 줄: | ||
) | ) | ||
; | ; | ||
− | + | </source> | |
=== TOBE 테이블스페이이스 RESIZE === | === TOBE 테이블스페이이스 RESIZE === | ||
− | + | <source lang=sql> | |
SELECT TABLESPACE_NAME, SIZEMB, FREEMB | SELECT TABLESPACE_NAME, SIZEMB, FREEMB | ||
, 'ALTER TABLESPACE '||TABLESPACE_NAME||' RESIZE '||SIZEMB||'M;' | , 'ALTER TABLESPACE '||TABLESPACE_NAME||' RESIZE '||SIZEMB||'M;' | ||
38번째 줄: | 38번째 줄: | ||
WHERE C.TABLESPACE_NAME LIKE 'TS%' | WHERE C.TABLESPACE_NAME LIKE 'TS%' | ||
; | ; | ||
− | + | </source> | |
=== ASIS <=> TOBE 유저 비교 === | === ASIS <=> TOBE 유저 비교 === | ||
− | + | <source lang=sql> | |
SELECT USERNAME,DEFAULT_TABLESPACE | SELECT USERNAME,DEFAULT_TABLESPACE | ||
FROM DBA_USERS@DL_DBCAFE_DEV_DBCAFE_DBA | FROM DBA_USERS@DL_DBCAFE_DEV_DBCAFE_DBA | ||
47번째 줄: | 47번째 줄: | ||
FROM DBA_USERS | FROM DBA_USERS | ||
; | ; | ||
− | + | </source> | |
==== ASIS 사용자 비교후 생성 ==== | ==== ASIS 사용자 비교후 생성 ==== | ||
− | + | <source lang=sql> | |
select USERNAME,DEFAULT_TABLESPACE | 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')||';' | , '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')||';' | ||
60번째 줄: | 60번째 줄: | ||
order by 1 | order by 1 | ||
); | ); | ||
− | + | </source> | |
==== ASIS 사용자명 조회 ==== | ==== ASIS 사용자명 조회 ==== | ||
+ | <source lang=sql> | ||
SELECT USERNAME | SELECT USERNAME | ||
FROM DBA_USERS@DL_DBCAFE_DEV_DBCAFE_DBA -- ASIS | FROM DBA_USERS@DL_DBCAFE_DEV_DBCAFE_DBA -- ASIS | ||
ORDER BY USERNAME | ORDER BY USERNAME | ||
; | ; | ||
− | + | </source> | |
=== ASIS <=> TOBE TS 비교 | === ASIS <=> TOBE TS 비교 | ||
+ | <source lang=sql> | ||
-- ASIS TS 쿼터 조회 | -- ASIS TS 쿼터 조회 | ||
SELECT * | SELECT * | ||
77번째 줄: | 79번째 줄: | ||
SELECT * | SELECT * | ||
FROM DBA_TABLESPACES; | FROM DBA_TABLESPACES; | ||
− | + | </source> | |
=== ASIS <=> TOBE 사용자 권한 비교 === | === ASIS <=> TOBE 사용자 권한 비교 === | ||
− | + | <source lang=sql> | |
-- 전환 <==> TOBE 운영 DB간 권한 비교 | -- 전환 <==> TOBE 운영 DB간 권한 비교 | ||
SELECT GRANTEE,PRIVILEGE,ADMIN_OPTION | SELECT GRANTEE,PRIVILEGE,ADMIN_OPTION | ||
113번째 줄: | 115번째 줄: | ||
) | ) | ||
; | ; | ||
− | + | </source> | |
=== 데이터 전환 === | === 데이터 전환 === | ||
==== 전환계정 DBA권한 부여 ==== | ==== 전환계정 DBA권한 부여 ==== | ||
+ | <source lang=sql> | ||
GRANT ALTER ANY PROCEDURE TO MIGCOMMON WITH ADMIN OPTION ; | GRANT ALTER ANY PROCEDURE TO MIGCOMMON WITH ADMIN OPTION ; | ||
GRANT ALTER ANY TABLE TO MIGCOMMON; | GRANT ALTER ANY TABLE TO MIGCOMMON; | ||
130번째 줄: | 133번째 줄: | ||
GRANT CREATE DATABASE LINK TO MIGCOMMON; | GRANT CREATE DATABASE LINK TO MIGCOMMON; | ||
GRANT UNLIMITED TABLESPACE TO MIGCOMMON; | GRANT UNLIMITED TABLESPACE TO MIGCOMMON; | ||
− | + | </source> | |
==== 전환 <==> TOBE 운영 오브젝트 건수 비교 ==== | ==== 전환 <==> TOBE 운영 오브젝트 건수 비교 ==== | ||
+ | <source lang=sql> | ||
-- 1.USER별 TYPE 건수 | -- 1.USER별 TYPE 건수 | ||
-- 전환 DB | -- 전환 DB | ||
158번째 줄: | 162번째 줄: | ||
) | ) | ||
; | ; | ||
+ | </source> | ||
==== 전환 대상 테이블 초기화 ==== | ==== 전환 대상 테이블 초기화 ==== | ||
− | + | <source lang=sql> | |
-- TRUNCATE TABLE | -- TRUNCATE TABLE | ||
SELECT OBJECT_TYPE,OWNER,OBJECT_NAME | SELECT OBJECT_TYPE,OWNER,OBJECT_NAME | ||
167번째 줄: | 172번째 줄: | ||
OWNER = 'ERPAPP' | OWNER = 'ERPAPP' | ||
AND OBJECT_TYPE = 'TABLE'; | AND OBJECT_TYPE = 'TABLE'; | ||
− | + | </source> | |
==== 데이터 펌프 ==== | ==== 데이터 펌프 ==== | ||
− | + | <source lang=sql> | |
-- datapump 잡 확인 | -- datapump 잡 확인 | ||
SELECT * | SELECT * | ||
179번째 줄: | 184번째 줄: | ||
SELECT * | SELECT * | ||
FROM DBA_DATAPUMP_sessions; | FROM DBA_DATAPUMP_sessions; | ||
− | + | </source> | |
==== 시퀀스 생성 ==== | ==== 시퀀스 생성 ==== | ||
− | + | <source lang=sql> | |
-- 사용자별 시퀀스 생성 | -- 사용자별 시퀀스 생성 | ||
SELECT 'ALTER SEQUENCE '||SEQUENCE_OWNER||'.'||SEQUENCE_NAME||' INCREMENT BY -'||LAST_NUMBER||';' DDL | SELECT 'ALTER SEQUENCE '||SEQUENCE_OWNER||'.'||SEQUENCE_NAME||' INCREMENT BY -'||LAST_NUMBER||';' DDL | ||
201번째 줄: | 206번째 줄: | ||
WHERE SEQUENCE_OWNER = 'DBCAFE' | WHERE SEQUENCE_OWNER = 'DBCAFE' | ||
; | ; | ||
+ | </source> |
2024년 6월 22일 (토) 10:31 판
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'
;