Oracle 관리하기 PepperSavingBank

export & import 스크립트

● sys 또는 system 계정정보 없이 간단하게 sysdba권한 접속하기

$ sqlplus sys/system_password as sysdba

※ 권한에 제한이 있을 수 있음.

※ 아래 내용에서 C:\> 부분은 명령프롬프트에서 실행. SQL> 부분은 sqlplus “/as sysdba”로 접속하여 실행해야 하는 명령문.

● 일반 EXPDP 명령

EXPDP h552/h552 schemas=h552 DUMPFILE=H552_11G.DMP LOGFILE=H552_11G.log

● 11g이상에서 import할 대상이 하위 버전일 경우의 Export명령문(import대상 오라클의 버전 명시)

EXPDP h552/h552 schemas=h552 DUMPFILE=H552_10G.DMP LOGFILE=H552_10G.log VERSION=10.2

※ EXPDP, IMPDP 명령문 경우 Oracle 10 부터 사용가능

※ EXPDP 사용시 덤프파일 생성 경로 확인

SELECT DIRECTORY_PATH FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME = 'DATA_PUMP_DIR';

• EXPDP 사용시 조회된 위치에 export한 덤프파일이 생성된다. • IMPDP 사용시에도 해당 경로에 덤프파일을 옮겨놓고 import해야 한다.

● Oracle 9 이하에서 Export(유저별)

C:\> exp userid=h552/h552 owner=h552 file='D:\H552_11G.DMP' log='D:\H552_11G.LOG'

● Oracle 9 이하에서 Export(DB전체)

C:\> exp userid=h552/h552 file='D:\H552_11G.DMP' log='D:\H552_11G.LOG' full=y

DROP USER h551 CASCADE;
-- ORACLE12C에서는 오류가 나올 수 있음.alter session set "_ORACLE_SCRIPT"=true;
 
-- 'H5_DATA' 테이블스페이스 삭제(파일포함)
DROP TABLESPACE H5_DATA INCLUDING CONTENTS AND DATAFILES;
 
-- 'H5_INDX' 테이블스페이스 삭제(파일포함)
DROP TABLESPACE H5_INDX INCLUDING CONTENTS AND DATAFILES;
 
-- ※ 기존 tablepace H5_DATA, H5_INDX(H5_IDX)를 확인 후 Import할 곳에 생성해야 한다.
SELECT t.name ,d.name FROM  V$DATAFILE d, V$TABLESPACE t WHERE t.ts#=d.ts#;
-- * 조회시 테이블스페이스 이름 및 파일경로 확인가능

 CREATE TABLESPACE H5_DATA
	DATAFILE 'C:\app\virtual\oradata\orcl\H5_DATA.dbf'
	SIZE 1024M 
	AUTOEXTEND ON NEXT 100M;

CREATE TABLESPACE H5_INDX
	DATAFILE 'C:\app\virtual\oradata\orcl\H5_INDX.dbf'
	SIZE 1024M 
	AUTOEXTEND ON NEXT 100M;

Oracle 12c에서 USER명을 다룰 때, 다음 에러가 발생 할 수 있다.
ORA-65096: invalid common user or role name on oracle12c
해결책1 - USER명앞에 c##을 붙인다.
해결책2 - alter session set "_ORACLE_SCRIPT"=true;

-- default 비번 만료 기한 unlimited로 변경
ALTER profile DEFAULT LIMIT password_life_time unlimited;

-- 원하는 이름의 유저생성 및 기본 테이블스페이스 지정
CREATE USER c##h551 IDENTIFIED BY win1234 DEFAULT TABLESPACE H5_DATA TEMPORARY TABLESPACE temp;

-- 유저 권한 부여
GRANT CONNECT, RESOURCE, DBA TO c##h551;

-- 암호화 권한 부여
GRANT EXECUTE ON DBMS_OBFUSCATION_TOOLKIT TO c##h551;

-- 암호화 권한 부여
GRANT EXECUTE ON DBMS_CRYPTO TO c##h551;

-- 유저 unlock
ALTER USER c##h551 ACCOUNT UNLOCK ;

-- ※ IMPDP 사용 전 경로 확인
SELECT DIRECTORY_PATH FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME = 'DATA_PUMP_DIR';

• 해당 경로에 덤프파일을 옮겨놓고 import해야 한다

※ IMPDP 경우 Oracle 10 부터 사용가능

impdp h551/h551 DUMPFILE=H552_11G.DMP LOGFILE=H552_11G.log REMAP_SCHEMA=h552:h551 REMAP_TABLESPACE=(H5_DATA:H5_DATA, H5_INDX:H5_INDX)

※ REMAP_SCHEMA 와 REMAP_TABLESPACE 의 경우 EXPDP 환경과 같다(유저와 테이블스페이스 변경이 없음)면 굳이 사용하지 않아도 됨.

-- ● Oracle 9 이하 ===
imp userid=h552/h552 fromuser=h552 touser=h551 file=D:\H552_11G.DMP log=D:\H552_11G.LOG

CREATE USER psb IDENTIFIED BY win1234 DEFAULT TABLESPACE H5_DATA TEMPORARY TABLESPACE temp;
 
GRANT CONNECT, RESOURCE, DBA TO psb;
GRANT EXECUTE ON DBMS_OBFUSCATION_TOOLKIT TO psb;
GRANT EXECUTE ON DBMS_CRYPTO TO psb;
ALTER USER psb ACCOUNT UNLOCK ;

IMPDP h551/win1234 DUMPFILE=pepper181013.DMP LOGFILE=pepper181013_02.log REMAP_SCHEMA=h551:psb