A: 원본서버 B: 테스트서버
환경 : Windows 2003 R2
오라클 : 10.2.0.1 ==> 패치셋 적용후 10.2.0.5.....
캐릭터셋 : AMERICAN_AMERICA.US7ASCII
DB이전 작업 순서... 아래...
*** 오라클 10g 새로 설치하고 데이터 덤프하기 ***
1. D:\DR_PLUS\Oradata\10201_database_win32\database\autorun 실행...
서버이름을 TEST.. 암호도 TEST
설치경로는 C드라이브로 변경
2. D:\DR_PLUS\Oradata\p8202632_10205_WINNT\Disk1\setup.exe 실행...
경로를 위 버젼에서 설치한 경로로 변경해준다.. db_2를 db_1으로 ...
2-1. 패치 후 DB가 제대로 올라오지 않는다... 아래 명령문을 실행해 준다.
* /as sysdba 접속
* startup upgrade;
* @c:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catupgrd.sql
-- 컴 성능에 따라 30분에서 1시간 남짓 걸린다.
* shutdown immediate;
* startup;
* select status from v$instance;
3. 캐릭터셋 바꾸기 ( AMERICAN_AMERICA.US7ASCII )
-- 레지스트리에서 바꿨더라도 db에서는 안바뀌어 있다.
-- KO16MSWIN949 요걸로 되어있다.
-- 'NLS_CHARACTERSET' = US7ASCII 로 변경한다.
-- 조회하기
SELECT NAME,VALUE$ FROM SYS.PROPS$
WHERE NAME='NLS_LANGUAGE'
OR NAME='NLS_TERRITORY'
OR NAME='NLS_CHARACTERSET';
SQL> /AS SYSDBA
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL> ALTER DATABASE OPEN;
SQL> alter database character set internal_use US7ASCII;
SQL> SHUTDOWN IMMEDIATE; -- or NORMAL
SQL> STARTUP;
-- ( 캐릭터셋 바꾸고 나면 SQLPLUS와 오라클 에러메세지에서 한글이 깨진다 )
해결방법은... IMPDP로 테이블 스페이스 다 올려놓고...
나중에 다시 KO16MSWIN949 로 캐릭터셋 바꿔야 함.
4. 테이블 스페이스 생성...(MTS_EDI, MTS_PMPA, MTS_OCS, MTS_EDI_IDX, MTS_PMPA_IDX, MTS_OCS_IDX)
CREATE TABLESPACE MTS_EDI
DATAFILE 'D:\DR_PLUS\ORADATA\MTS_EDI.DBF' SIZE 2000M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M;
CREATE TABLESPACE MTS_EDI_IDX
DATAFILE 'D:\DR_PLUS\ORADATA\MTS_EDI_IDX.DBF' SIZE 500M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M;
CREATE TABLESPACE MTS_PMPA
DATAFILE 'D:\DR_PLUS\ORADATA\MTS_PMPA.DBF' SIZE 2000M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M;
ALTER TABLESPACE MTS_PMPA ADD DATAFILE 'D:\DR_PLUS\ORADATA\MTS_PMPA1.DBF' SIZE 2000M
CREATE TABLESPACE MTS_PMPA_IDX
DATAFILE 'D:\DR_PLUS\ORADATA\MTS_PMPA_IDX.DBF' SIZE 2000M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M;
CREATE TABLESPACE MTS_OCS
DATAFILE 'D:\DR_PLUS\ORADATA\MTS_OCS.DBF' SIZE 2000M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M;
ALTER TABLESPACE MTS_OCS ADD DATAFILE 'D:\DR_PLUS\ORADATA\MTS_OCS1.DBF' SIZE 2000M
ALTER TABLESPACE MTS_OCS ADD DATAFILE 'D:\DR_PLUS\ORADATA\MTS_OCS2.DBF' SIZE 2000M
CREATE TABLESPACE MTS_OCS_IDX
DATAFILE 'D:\DR_PLUS\ORADATA\MTS_OCS_IDX.DBF' SIZE 2000M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M;
ALTER TABLESPACE MTS_OCS ADD DATAFILE 'D:\DR_PLUS\ORADATA\MTS_OCS_IDX1.DBF' SIZE 2000M
5. 계정 생성 및 권한주기..
CREATE USER mts_edi
IDENTIFIED BY penta
DEFAULT TABLESPACE MTS_EDI
GRANT connect, resource, dba to mts_edi;
CREATE USER mts_pmpa
IDENTIFIED BY penta
DEFAULT TABLESPACE MTS_PMPA
GRANT connect, resource, dba to mts_pmpa;
CREATE USER mts_ocs
IDENTIFIED BY penta
DEFAULT TABLESPACE MTS_OCS
GRANT connect, resource, dba to mts_ocs;
6. impdp expdp 폴더 할당하기( A, B 서버 모두 설정 )
--원본 서버에 접속하여 아래 명령어 실행.( /as sysdba )
create directory expdp_dir as 'c:\backup';
grant read,write on directory expdp_dir to mts_edi;
grant read,write on directory expdp_dir to mts_pmpa;
grant read,write on directory expdp_dir to mts_ocs;
-- TEST서버에서 작업.
create directory expdp_dir as 'd:\dr_plus';
grant read,write on directory expdp_dir to mts_edi;
grant read,write on directory expdp_dir to mts_pmpa;
grant read,write on directory expdp_dir to mts_ocs;
create directory impdp_dir as 'd:\dr_plus';
grant read,write on directory impdp_dir to mts_edi;
grant read,write on directory impdp_dir to mts_pmpa;
grant read,write on directory impdp_dir to mts_ocs;
7. 데이터를 가져올 서버에서 EXPORT 받기 ( expdp )
expdp 사용자/암호@SID directory=디렉토리명 dumpfile=파일명 EXCLUDE=TABLE:\"IN \(\'테이블명1\', \'테이블명2\'\)\"
expdp mts_edi/penta directory=expdp_dir dumpfile=fulledi.dmp
expdp mts_pmpa/penta directory=expdp_dir dumpfile=fullpmpa.dmp
expdp mts_ocs/penta directory=expdp_dir dumpfile=fullocs.dmp EXCLUDE=TABLE:\"IN \(\'opd_order_backup\', \'ipd_order_update_backup\'\)\"
8. IMPDP
IMPDP MTS_EDI/PENTA DIRECTORY=impdp_dir dumpfile = fulledi.dmp Logfile = edilog.log remap_schema=mts_edi:mts_edi remap tablespace=mts_edi:mts_edi exists_table_action=replace
IMPDP mts_pmpa/PENTA DIRECTORY=impdp_dir dumpfile = fullpmpa.dmp Logfile = edilog.log remap_schema=mts_edi:mts_edi remap tablespace=mts_edi:mts_edi exists_table_action=replace
IMPDP mts_pmpa/penta DIRECTORY=impdp_dir dumpfile = fullocs.dmp Logfile = edilog.log remap_schema=mts_edi:mts_edi remap tablespace=mts_edi:mts_edi exists_table_action=replace
** 만일 expdp 나 impdp 실행시 ORA-39213:metadata processing is not available 오류 발생시
sqlplus /as sysdba 접속하여 아래 쿼리문 실행.
sql> execute dbms_metadata_util.load_stylesheets
* 캐릭터셋을 다시 바꿔줘야 한글이 제대로 보여진다.(이것때문에 이틀간 씨름했음.ㅠㅠ.)
SQL> /AS SYSDBA
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL> ALTER DATABASE OPEN;
SQL> alter database character set internal_use KO16MSWIN949;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
