[Tibero] Window서버 Tibero to Tibero 이관 하기

Window서버 Tibero to Tibero 이관 매뉴얼


-----------------------------------------------------------------------------------------------------------------------------------------------------------------
서버 정보

AS-IS 서버

서버IP : 
SYS 비번 :
DB버전 : 
TB_HOME : 소스디렉토리
TB_SID : 소스DB명

******************************************************************************************************************************************

TO-BE 서버

서버IP : 
SYS 비번 : 
DB버전 : 
TB_HOME : 타겟디렉토리
TB_SID : 타겟DB명


-----------------------------------------------------------------------------------------------------------------------------------------------------------------

tbsql tibero

Enter Password: 비번



set linesize 300
set pagesize 900
col owner for a10
col object_name for a50

****************************************************************************************************************************************** 데이터베이스 크기 확인 쿼리

SELECT SUM(BYTES/1024/1024/1024)
FROM DBA_SEGMENTS;

 

 

******************************************************************************************************************************************

데이터베이스 객체 정보 확인 쿼리

SELECT OWNER, OBJECT_TYPE, STATUS, COUNT(*)
FROM DBA_OBJECTS
GROUP BY OWNER, OBJECT_TYPE, STATUS;

 

개발기에 있는것중 기존 객체들을 다 날리고 운영기에 있는걸 밀어 넣는지? 아니면 개발기에 있는걸 살리고 운영기에 있는걸 넣는지?
꼭 담당자와 상의!!!!!!

 

******************************************************************************************************************************************
티베로 버전 확인

tbboot -version

tbboot -cs

 

******************************************************************************************************************************************

TB_HOME 디렉토리 확인 ,  TB_SID (DB명) 확인 , OPEN PORT 확인

echo %TB_HOME% 경로에 있는 config/%TB_SID%.tip 파일을 열어서 확인

 

******************************************************************************************************************************************
로그는 cmd 현재 디렉토리 위치에 떨어짐
소스 서버 이관용 데이터 EXPORT

tbexport username=sys password=소스비번 sid=소스DB명 file=소스디렉토리\export.dat full=y port=소스포트 log=소스디렉토리\export.log script=y

타겟 서버 백업용 데이터 EXPORT

tbexport username=sys password=타겟비번 sid=타겟DB명 file=타겟디렉토리\export_bak.dat full=y port=타겟포트 log=타겟디렉토리\export_bak.log script=y
******************************************************************************************************************************************
(타겟DB 사용자 계정삭제 하기로 했을경우)

타겟 서버에서 삭제할 사용자 계정들 확인

SELECT OWNER
FROM DBA_OBJECTS
GROUP BY OWNER;

drop user 사용자 계정명 cascade;

drop user 사용자 계정명 cascade;

******************************************************************************************************************************************
담당자에게 소스 서버 EXPORT된 데이터 파일을 타겟 서버로 옮기기 요청

타겟 서버에 EXPORT 데이터 IMPORT (옮긴 데이터파일쪽 디렉토리에서 명령문 실행)

tbimport username=sys password=타겟비번 sid=타겟DB명 file=D:\backup\export.dat full=y port=타겟포트 log=타겟디렉토리\import.log script=y ignore=y

******************************************************************************************************************************************

타겟 서버에서 삭제할 사용자 계정들 확인

SELECT OWNER
FROM DBA_OBJECTS
GROUP BY OWNER;

drop user 사용자 계정명 cascade;

drop user 사용자 계정명 cascade;

******************************************************************************************************************************************
Import한 사용자 계정들 소유자, 테이블이름 조회 쿼리

select owner, table_name 
from dba_tables
where owner in ('Import한 사용자 계정들')
order by 1,2;

******************************************************************************************************************************************
Import한 사용자 계정들 가진 테이블 count(*) 조회 쿼리

select 'select count(*) 
from '||owner||'.'||table_name||' union all ' from dba_tables
where owner in ('Import한 계정들')
order by owner, table_name ;

예시)
select count(*) from APBMADM.CPDS_HMCPART union all
select count(*) from APBMADM.CPDS_HMCPARTMASTER union all
select count(*) from APBMADM.CPDS_HMCPART_VER union all
select count(*) from APBMADM.CPDS_HMCUPGPART union all
select count(*) from APBMADM.CPDS_HMCUPGPARTMASTER union all
select count(*) from APBMADM.CPDS_HMCVCPART union all
select count(*) from APBMADM.CPDS_HMCVCPARTMASTER union all
select count(*) from APBMADM.CPDS_HOLDERTOCONTENT union all
select count(*) from APBMADM.CPDS_MODEL union all
select count(*) from APBMADM.CPDS_MODELMASTER union all
select count(*) from APBMADM.CPDS_SELPARTLINK ;

******************************************************************************************************************************************
Import한사용자 계정들 가진 인덱스 조회 쿼리

select owner, object_name, object_Type, status 
from dba_objects 
where owner in ('Import한 사용자 계정들')
and object_Type like '%INDEX%'
order by 1,2,3;

******************************************************************************************************************************************
Import한사용자 계정들 가진 기타 오브젝트 조회

select owner, object_name, object_Type, status 
from dba_objects 
where owner in ('Import한 계정들')
and object_Type not like '%INDEX%' 
and object_Type not like '%TABLE%'
order by 1,2,3;

******************************************************************************************************************************************
TIP

엑셀에서 데이터 - 텍스트 나누기 해주면 편함

NotePad++에서 Alt 마우스 드래그하면 한꺼번에 쿼리 작성할떄 편함

CMD에서 / 눌르면 직전에 사용한 쿼리 사용가능!