PostgreSQL dblink 기본 사용법

PostgreSQL 에서 처음 dblink 를 사용하는 경우에는 dblink 라이브러리를 확장모듈에 로딩을 해놔야 함.  안그러면 알수 없는 함수라는 오류가 표시됨.

-- dblink 로딩 (최초1회 > PgAdmin 에서 해당 DB의 확장모듈에 표시됨)

create extension dblink;

-- DB 연결

select dblink_connect('Test연결1', 'hostaddr=127.0.0.1 port=5432 dbname=test user=test1 password=pwd1');

-- 쿼리

SELECT * FROM 

    dblink('Test연결1', 

   'SELECT "Seq" FROM dbo."Table1" WHERE "CreateDate" < now() - INTERVAL ''2 year''') as t1("Seq" integer) ;

-- DB 연결 해제

select dblink_disconnect('Test연결1');

dblink 의 단점일수도 있고 장점일수도 있긴한데,. 가져올려는 테이블 컬럼을 명시적으로 지정해줘야 하고, row를 임시 저장하는 개체(테이블)에도 명시적을 컬럼 타입을 지정해줘야 함.

컬럼 갯수가 몇개 안되면 상관없는데, 컬럼이 많으면 이것 또한 상당한 노가다 작업이 필요해 진다. 그래서 차라리 각기 호출하여 웹/백엔드 코드단에서 처리하는게 더 편할수도 있음..

그런점에서 본다면,. 확실히 Microsoft SQL Server 가 편하긴 함.

PostgreSQL 백업 및 복원 예 (일반적인 논리 백업)

■ 백업

명령행 실행시 패스워드 없이 할려면, pgpass.conf 에 작업 계정에 대한 인증 설정이 미리 되어 있어야 함.

(원격)

pg_dump --host localhost --port 5432 --username "postgres" --password "" --dbname "test" --format custom --blobs --verbose --file "test.backup"

(로컬)

pg_dump --username "postgres" --no-password --dbname "test" --blobs --verbose --format=c --file "test.backup"

■ 복원

유의) 복원 대상에 세션이 물려 있는 경우에는, 복원시 대상 개체의 drop, create 쿼리 실행 오류가 발생하고, 그걸 무시하고 계속 진행하는 경우 기존 테이블에 데이터가 중복 복원 추가됨. 그러므로, 세션이 일체 물려 있지 않은 상태에서 복원 작업을 진행해야 함.

SELECT pg_terminate_backend(pid) FROM pg_stat_activity  WHERE pid <> pg_backend_pid() AND datname = 'test';

(DB 복원으로 할 경우. 대상 이름이 다른 경우 미리 생성후)

pg_restore --clean --create --no-privileges --no-owner --username "postgres" --dbname "test_new" --disable-triggers --format=c --verbose "test.backup"

--clean --create 파라미터를 주는 경우 백업에 있는 DB명으로 동일 복원. 하지만 복원 대상과 동일 DB 명을 주면 에러를 뿌림. 

pg_restore 내부 로직이 문제인지 모르겠으나,.

test 를 삭제후 test 에 복원하려면, 다른 DB명으로 복원 지정(생성)후 하면, 원래 test 를 삭제/복원. 동일하게 test 를 지정하면 DB drop,create 오류나고, 생성 안해두면 DB 없다고 또 복원 안됨..;;

(다른 DB에 단순 복원으로 할 경우. 대상 이름이 다른 경우 미리 생성후)

pg_restore --no-privileges --no-owner --username "postgres" --dbname "test_new" --format=c --verbose "test.backup"

테이블의 경우 개체가 이미 있는 경우, 거기에 레코드 추가 insert 방식이므로, 데이터 중복 발생 유의.

(대상 DB에, 대상 테이블 복원으로 할 경우)

pg_restore --clean --no-privileges --no-owner --username "postgres" --dbname "test_new" --table "table1" --disable-triggers --format=c --verbose "test.backup"

--clean 파라미터 주면, 대상 개체를 먼저 삭제후 복원. 파라미터 없는 상태에서 중복 이름 있는 경우, 데이터 중복 복원해 버리므로 유의 필요.

RMAN – 데이터베이스 복구/이전

# 재설치 복원
# 서버 이전 복원 (단순 복사 / RMAN)

동일 경로

1. 기존 인스턴스 DB 의 DataFile/ControlFile/RedoLog 를 원래 경로에 복사(파티션 백업 복원)

다른 경로

1. 기존 인스턴스 DB 의 DataFile/ControlFile/RedoLog 를 다른 경로에 복사(파티션 백업 복원)

 

[다른 경로(서버)의 백업 파일만 가져와서 복원]

RMAN> list backup;  -- 현재 카다로그의 백업셋 정보 확인
RMAN> crosscheck backup; -- 비교 검증
RMAN> delete expired backup; -- 기존 백업 'EXPIRED' 상태 제거

채널 ORA_DISK_1 사용 중

백업 조각 목록
BP 키  BS 키  Pc# Cp# 상태      장치 유형 조각 이름
------- ------- --- --- ----------- ----------- ----------
1       15      1   1   EXPIRED     DISK        D:\ORABACKUP\ORCL_01SEH05C_1_1_20170915

 

RMAN> catalog start with 'D:\orabackup2'; -- 백업 경로 변경

D:\orabackup2 패턴과 일치하는 모든 파일 검색 중

데이터베이스에서 알 수 없는 파일 목록
=====================================
파일 이름: D:\orabackup2\ORCL_01SEH05C_1_1_20170915

위의 파일을 분류하겠습니까? (YES 또는 NO 입력) y
파일 분류 중...
분류 작업 완료

 

RMAN> list backup;  -- 복사된 백업파일의 백업셋 정보 다시 확인

 

BS 키  유형 LV 크기       장치 유형 경과된 시간 완료 시간
------- ---- -- ---------- ----------- ------------ ---------
25      Full    9.42M      DISK        00:00:00     17/09/29
        BP 키: 25   상태: AVAILABLE  압축됨: NO  태그: TAG20170929T144247
        조각 이름: D:\ORABACKUP2\ORCL_0FSFM8OB_1_1_20170929
  포함된 SPFILE: 수정 시간: 17/09/29
  SPFILE db_unique_name: TESTDB
  포함된 제어 파일: Ckp SCN: 4648574      Ckp 시간: 17/09/29

 

RMAN>  list incarnation;  -- DB ID 확인
shutdown abort;
startup nomount;
restore controlfile from 'D:\ORABACKUP2\ORCL_0FSFM8OB_1_1_20170929';
alter database mount;

run {
restore database;
recover database noredo;
alter database open resetlogs;
}

 

 

RMAN – 파라미터파일(spfile) 복구

# 백업셋 조회

RMAN> list backup;
RMAN> list backupset; 

# 제어 파일 복구

RMAN> restore spfile;  -- 데이터베이스가 unmount 상태일 때
RMAN> restore spfile to 'D:\oradata2\spfile_temp.ora';  -- 데이터베이스가 운영중인 경우에는 다른 경로에 복원
RMAN> restore spfile to pfile 'D:\oradata2\init_temp.ora';  -- spfile 을 특정 위치 pfile 로 복원
RMAN> restore spfile from 'D:\ORABACKUP\ORCL_0FSFM8OB_1_1_20170929' -- 특정 백업 조각에서 복원

 

 

RMAN – 제어파일(Control) 복구

# 백업셋 조회

RMAN> list backup;
RMAN> list backupset; 

 

# 제어 파일 복구

RMAN> restore controlfile;  -- 데이터베이스가 unmount 상태일 때
RMAN> restore controlfile from tag TAG20170929T144247; -- 특정 TAG 위치 백업셋으로 복원
RMAN> restore controlfile  from 'D:\ORABACKUP\ORCL_0FSFM8OB_1_1_20170929' -- 특정 백업 조각에서 복원
RMAN> restore controlfile to 'D:\oradata2';  -- 데이터베이스가 운영중인 경우에는 다른 경로에 복원

 

 

RMAN – 테이블/레코드 복구

# 백업셋 조회

RMAN> list backupset; 
RMAN> list backup of database;
RMAN> list backup of datafile "D:\oradata\orcl\test.DBF";
RMAN> list backup of tablespace test;
RMAN> list copy of tablespace "test";

 

-- 일반적인 방법  --

# 별도 데이터베이스에 테이블스페이스/데이터 복원

# 별도 위치에 복원된 데이터에서 특정 테이블 데이터 export

# 원본 데이터베이스의 대상 테이블 or 레코드 삭제후 import

 

-- RMAN 버젼(12c)에서 recover table 지원시  --

RMAN> recover table 대상테이블 until time "to_date('2017-10-10:22:30:50','yyyy-mm-dd:hh24:mi:ss')"  auxiliary  destination 'd:/data/temp';  -- 지정된 시점으로  복원

RMAN> recover table 대상테이블 until scn 10345 auxiliary  destination 'd:/data/temp'; -- 백업 지점으로 테이블 전체 복원

RMAN> recover table 대상테이블 until time "to_date('2017-10-10:22:30:50','yyyy-mm-dd:hh24:mi:ss')"  auxiliary  destination 'd:/data/temp'  remap table scott.test1:test1_temp;  -- 기존 테이블과 별도로 다른 테이블 이름으로 복원

 

RMAN - 테이블스페이스 복구

# 백업셋 조회

RMAN> list backupset;
RMAN> list backup of database;
RMAN> list backup of datafile "D:\oradata\orcl\USERS01.DBF";
RMAN> list backup of tablespace "USERS";
RMAN> list copy of tablespace "USERS";
RMAN> report schema;  -- 테이블스페이스 정보 확인

 

# 테이블 스페이스 파일 손상/삭제 시  (오프라인 가능시)

RMAN> sql 'alter tablespace users offline immediate'; -- 대상 테이블스페이스 오프라인
RMAN> restore tablespace "USERS"; -- 파일 복구 (가장 마지막 백업본으로 복원. 백업셋/카피 모두 포함)
RMAN> recover tablespace "USERS"; -- 테이블스페스 복구
RMAN> sql 'alter tablespace users online'; -- 다시 온라인

*  백업셋/이미지 목록에서 마지막 백업본 확인 필요 (마지막 백업 이후 수정된 데이터는?)

 

# 테이블 스페이스 파일 손상/삭제시 (오프라인 불가시)

RMAN> shutdown abort;  -- 인스턴스 강제 종료
RMAN> startup mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open;

 

# 테이블 스페이스 파일을 다른 파일명 or 경로에 복원하기 

RMAN> run {
sql 'alter tablespace users offline immediate'; -- 대상 테이블스페이스 오프라인
set newname for datafile 4 to 'D:\app\Administrator\oradata\temp\users01.dbf';  -- 대상 파일 번호의 Datafile 정보 변경
restore tablespace "USERS";
switch datafile 4;
recover tablespace "USERS";
sql 'alter tablespace users online';
}

 

RMAN - 설정 및 백업

\dbhome_1\BIN>rman target system/◯◯◯◯
RMAN> connect target /   -- 현재 계정이 권한이 있는 경우

 

RMAN> show all;   -- 현재 기본 설정값 조회/설정  

# 백업파일 저장 위치 및 파일명 포맷  기본값 설정
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'D:/orabackup/orcl_%U_%T';

# 전체 데이터 백업 최적화 (이미 백업된 동일한 백업본이 있는 경우 건너뛰기)
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;  

# 로그 삭제 정책
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 0 TIMES TO 'DISK';

# 백업 보전 정책 (2가지중 한가지 선택 적용)
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; (백업후 7일간 보존)
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 5; (최근 백업셋 5개 보존)

 

 

 

RMAN> report schema;  -- DB 테이블스페이스/파일 목록 조회

db_unique_name TESTDB을(를) 가진 데이터베이스에 대한 데이터베이스 스키마 보고서입니다.

영구 데이터 파일 목록
===========================
파일 크기(MB) 테이블스페이스 RB 세그먼트 데이터 파일 이름
---- -------- -------------------- ------- ------------------------
1 700 SYSTEM *** D:\APP\ADMINISTRATOR\ORADATA\TESTDB\SYSTEM01.DBF
2 780 SYSAUX *** D:\APP\ADMINISTRATOR\ORADATA\TESTDB\SYSAUX01.DBF
3 85 UNDOTBS1 *** D:\APP\ADMINISTRATOR\ORADATA\TESTDB\UNDOTBS01.DBF
4 5 USERS *** D:\APP\ADMINISTRATOR\ORADATA\TESTDB\USERS01.DBF

임시 파일 목록
=======================
파일 크기(MB) 테이블스페이스 최대 크기(MB) 임시 파일 이름
---- -------- -------------------- ----------- --------------------
1 197 TEMP 32767 D:\APP\ADMINISTRATOR\ORADATA\TESTDB\TEMP01.DBF

 

RMAN> backup database; -- 모든 Data 백업 (저장 경로는 전역값 참조)
RMAN> backup as compressed backupset  format 'D:/orabackup/orcl_data_%U_%T' database;
(압축,특정경로 지정시)
RMAN> backup tablespace users format 'D:/orabackup/orcl_data_users_%U_%T';
(특정 테이블스페이스만)
RMAN> backup archivelog all; -- 모든 아카이브로그 백업
RMAN> backup as compressed backupset format 'D:/orabackup/orcl_arc_%U_%T' archivelog all;
RMAN> backup archivelog all delete all input;
(로그 백업후 해당 로그 파일 삭제 - 미지정시 기본 정책 적용)
RMAN> copy datafile 'D:/oradata/testdb/USERS01.DBF' to 'D:/orabackup/USERS01.DBF';
(이미지 복사. 활용방법)

차등/증분/블록레벨

backup을(를) 17/09/15에서 시작 중
채널을 할당했습니다: ORA_DISK_1
ORA_DISK_1 채널: SID=1 장치 유형=DISK
ORA_DISK_1 채널: 전체 데이터 파일 백업 집합을 시작하는 중입니다.
ORA_DISK_1 채널: 백업 집합에서 데이터 파일을 지정하는 중입니다.
입력 데이터 파일 파일 번호=00002 이름=D:\APP\ADMINISTRATOR\ORADATA\TESTDB\SYSAUX01.DBF
입력 데이터 파일 파일 번호=00001 이름=D:\APP\ADMINISTRATOR\ORADATA\TESTDB\SYSTEM01.DBF
입력 데이터 파일 파일 번호=00003 이름=D:\APP\ADMINISTRATOR\ORADATA\TESTDB\UNDOTBS01.DBF
입력 데이터 파일 파일 번호=00004 이름=D:\APP\ADMINISTRATOR\ORADATA\TESTDB\USERS01.DBF
채널 ORA_DISK_1: 조각 1을(를) 17/09/15에서 시작 중
채널 ORA_DISK_1: 조각 1을(를) 17/09/15에서 완료
조각 핸들=D:\ORABACKUP\ORCL_01SEH05C_1_1_20170915 태그=TAG20170915T112948 주석=NONE
채널 ORA_DISK_1: 백업 집합 완료. 경과된 시간: 00:00:25
ORA_DISK_1 채널: 전체 데이터 파일 백업 집합을 시작하는 중입니다.
ORA_DISK_1 채널: 백업 집합에서 데이터 파일을 지정하는 중입니다.
백업 집합에 현재 제어 파일을 포함하는 중입니다.
백업 집합에 현재 SPFILE을 포함하는 중입니다.
채널 ORA_DISK_1: 조각 1을(를) 17/09/15에서 시작 중
채널 ORA_DISK_1: 조각 1을(를) 17/09/15에서 완료
조각 핸들=D:\ORABACKUP\ORCL_02SEH065_1_1_20170915 태그=TAG20170915T112948 주석=NONE
채널 ORA_DISK_1: 백업 집합 완료. 경과된 시간: 00:00:01
backup을(를) 17/09/15에서 완료

 

RMAN> list backupset; -- 백업셋 조회 (백업정보는 기본으로 컨트롤파일에 저장)

백업 집합 목록
===================

BS 키 유형 LV 크기 장치 유형 경과된 시간 완료 시간
------- ---- -- ---------- ----------- ------------ ---------
1 Full 1.21G DISK 00:00:19 17/09/15
BP 키: 1 상태: AVAILABLE 압축됨: NO 태그: TAG20170915T112948
조각 이름: D:\ORABACKUP\ORCL_01SEH05C_1_1_20170915
백업 집합 1의 데이터 파일 목록
파일 LV 유형 Ckp SCN Ckp 시간 이름
---- -- ---- ---------- -------- ----
1 Full 3705783 17/09/15 D:\APP\ADMINISTRATOR\ORADATA\TESTDB\SYSTEM01.DBF
2 Full 3705783 17/09/15 D:\APP\ADMINISTRATOR\ORADATA\TESTDB\SYSAUX01.DBF
3 Full 3705783 17/09/15 D:\APP\ADMINISTRATOR\ORADATA\TESTDB\UNDOTBS01.DBF
4 Full 3705783 17/09/15 D:\APP\ADMINISTRATOR\ORADATA\TESTDB\USERS01.DBF

BS 키 유형 LV 크기 장치 유형 경과된 시간 완료 시간
------- ---- -- ---------- ----------- ------------ ---------
2 Full 9.36M DISK 00:00:02 17/09/15
BP 키: 2 상태: AVAILABLE 압축됨: NO 태그: TAG20170915T112948
조각 이름: D:\ORABACKUP\ORCL_02SEH065_1_1_20170915
포함된 SPFILE: 수정 시간: 17/09/14
SPFILE db_unique_name: TESTDB
포함된 제어 파일: Ckp SCN: 3705791 Ckp 시간: 17/09/15

 

RMAN> CROSSCHECK BACKUPSET; -- 백업셋 정보와 파일간 일관성 체크 
RMAN> CROSSCHECK COPY;

RMAN> DELETE NOPROMPT OBSOLETE; -- 사용되지 않는 레코드 삭제 (불일치, 미존재등)
RMAN> DELETE NOPROMPT EXPIRED BACKUP;  -- 만기된 레코드 삭제
RMAN> DELETE NOPROMPT EXPIRED COPY;
RMAN> DELETE EXPIRED ARCHIVELOG ALL;

RMAN> validate database;  -- 백업 유효성 확인

 

# SQL Developer DBA 를 이용하면 GUI 를 이용하여 명령어를 손쉽게 생성 가능

* RUN{} 스크립트를 만들어서  배치 실행 가능
* 리스너 등 그외 서버 설정 파일등은 별도 백업을 진행해야 한다 

 

Oracle-RMAN-1.png
Oracle-RMAN-3.png
Oracle-RMAN-2.png

Oracle spfile 복구

파라미터 설정을 잘못한 경우 오류가 발생하여, DB 시작이 안되는 경우에 활용

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORA-16032: parameter ◯◯◯◯ destination string cannot be translated
ORA-09291: sksachk: invalid device specified for archive destination
OSD-04018: ?????? ???????? ???? ?????? ???????? ?? ????????.
O/S-Error: (OS 123) ???? ????, ???????? ???? ???? ???? ?????? ?????? ??????????????.

 

SQL> conn system/◯◯◯◯ as sysdba

[pfile 지정하여 마운트 - 기본 설치값 또는 백업용 지정]
SQL> startup mount pfile=D:\app\Administrator\product\11.2.0\dbhome_1\database\INITTESTDB.ORA
ORACLE 인스턴스가 시작되었습니다.  

[오류가 있는 spfile 을 참조하여 pfile 생성 - 생성된 pfile 에서 오류 부분 수정]
SQL> create pfile from spfile;
파일이 생성되었습니다. (경로는,  설치 기본 경로를 참조 "dbhome_1\database\")

[수정된 pfile 을 참조하여 다시 spfile 생성]
SQL> create spfile from pfile; 
파일이 생성되었습니다.

[백업 보관용으로 pfile 생성]
SQL> create pfile from spfile;

SQL> shutdown immediate
SQL> startup

 

 

Oracle-spfile-1.png

Oracle Archive Log Mode 설정

# SQL Plus

[DB 확인]
SQL> select name from v$database;
NAME
---------
TESTDB

[Archive Log 설정 확인]
SQL> archive log list;
ORA-01031: 권한이 불충분합니다
SQL> conn system/passwd as sysdba
연결되었습니다.
SQL> archive log list;
데이터베이스 로그 모드 아카이브 모드가 아님
자동 아카이브 사용 안함으로 설정됨
아카이브 대상 USE_DB_RECOVERY_FILE_DEST
가장 오래된 온라인 로그 순서 135
현재 로그 순서 137

[Archive Log 속성  적용 - 특정 경로, 파일명 필요시]
SQL> alter system set log_archive_dest_1='location=D:\app\Administrator\oradata\testdb\archive1' scope=spfile;
SQL> alter system set log_archive_dest_2='location=D:\app\Administrator\oradata\testdb\archive2' scope=spfile;
SQL> alter system set log_archive_format="%s_%t_%r.arc" scope=spfile;

[DB 종료]
SQL> shutdown immediate;

[DB Mount]
SQL> startup mount;

[Archive Log 설정  확인]
SQL> archive log list;
데이터베이스 로그 모드 아카이브 모드가 아님
자동 아카이브 사용 안함으로 설정됨
아카이브 대상 D:\app\Administrator\oradata\testdb\archive2
가장 오래된 온라인 로그 순서 135
현재 로그 순서 137

[DB Archive Log Mode 설정]
SQL> alter database archivelog;

데이타베이스가 변경되었습니다.

[Archive Log 설정  확인]
SQL> archive log list;
데이터베이스 로그 모드 아카이브 모드
자동 아카이브 사용으로 설정됨
아카이브 대상 D:\app\Administrator\oradata\testdb\archive2
가장 오래된 온라인 로그 순서 135
아카이브할 다음 로그 137
현재 로그 순서 137

[DB 활성]
SQL> alter database open;
데이타베이스가 변경되었습니다.

[Archive Log 파일 변경 테스트]
SQL> alter system switch logfile;
시스템이 변경되었습니다.

 

 

Oracle-ArchiveLog-1.png

Oracle Export, Import (백업/복원)

# Export 백업

exp userid=system/manager file='C:\full.dmp' full=y
exp userid=system/manager@SIDName file='C:\full.dmp' full=y (특정SID백업)
exp userid=scott/tiger file='C:\scott.dmp' (특정 소유자 개체 전체)
exp userid=system/manager file='C:\data.dmp' TABLESPACES=USERS (특정 테이블스페이스)
exp userid=system/manager file='C:\data.dmp' TABLES=(a,b) (특정 테이블)

 

# Import 복원

imp userid=system/system file='C:\full.dmp' full=y
imp userid=system/system@SIDName file='C:\full.dmp' full=y (특정SID복원)
- 복원하려는 시스템에 동일한 SID 가 존재해야 함

imp userid=system/manager owner=scott file='C:\scott.dmp'
- 복원하려는 시스템에 동일한 DB 계정 및 TableSpace 가 존재해야 함 (SID는 상관없음)

imp userid=system/system@SIDName file='C:\full.dmp' FROMUSER=scott TOUSER=scott2 tables=(a,b) 
- 특정 소유자 테이블을 다른 소유자 테이블로 복원

impdp  file=blog_data.dmp directory=mydir remap_tablespace=USERS:DATA
- 다른 테이블스페이스에 복원

exp --------------------------------------------------------------------------
USERID 사용자 이름/비밀번호    FULL 전체 파일 익스포트(N)
BUFFER 데이터 버퍼 크기    OWNER 소유자 사용자 이름 목록
FILE 출력 파일(EXPDAT.DMP)     TABLES 테이블 이름 목록
COMPRESS 하나의 확장 영역으로 임포트(Y)     RECORDLENGTH IO 레코드 길이
GRANTS 권한 부여 익스포트(Y)    INCTYPE 증분 익스포트 유형
INDEXES 인덱스 익스포트(Y)     RECORD 증분 익스포트 추적(Y)
DIRECT 직접 경로(N)     TRIGGERS 트리거 익스포트(Y)
LOG 화면 출력의 로그 파일     STATISTICS 객체 분석(ESTIMATE)
ROWS 데이터 행 익스포트(Y)    
CONSISTENT 테이블간 일관성(N)     CONSTRAINTS 제약 조건 익스포트(Y)
QUERY 테이블의 부분 집합을 엑스포트하는 데 사용하는 select 절
TTS_FULL_CHECK TTS에 대한 전체 또는 부분 종속성 검사 수행
TABLESPACES 익스포트할 테이블스페이스 목록

imp --------------------------------------------------------------------------
USERID 사용자명/비밀번호 FULL 파일 전체 임포트 (N)
BUFFER 데이터 버퍼의 크기 FROMUSER 소유 사용자명 목록
FILE 입력 파일들 (EXPDAT.DMP) TOUSER 사용자명의 목록
SHOW 파일 목차만 목록 (N) TABLES 테이블명 목록
IGNORE 생성 오류 무시 (N) 
GRANTS 권한부여 임포트 (Y) INCTYPE 증분 임포트 유형
INDEXES 인덱스 임포트 (Y) COMMIT 배열 삽입 커밋 (N)
ROWS 데이터 행 임포트 (Y) 
LOG 화면 출력의 로그 파일 CONSTRAINTS 제약조건을 임포트 (Y)
DESTROY 테이블스페이스 데이터 파일 겹쳐쓰기(N)
INDEXFILE 지정된 파일에 테이블/인덱스 정보 쓰기
DATA_ONLY 데이터만 임포트(N)

 

Oracle Listener / Tnsnames / Sqlnet 설정

listener.ora (서버) / tnsnames.ora (클라이언트)  / sqlnet.ora 파일 편집시, 포맷 오류 또는 오타등이 발생할수 있으므로 직접 편집 보다는 아래와 같은 툴을 활용하여 수정

- Oracle Net Configuration Assistant
- Oracle Net Manager
- 파일 위치 ex, File : \app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN

 

# 서버 서비스 제어 \dbhome_1\BIN>lsnrctl
  LSNRCTL> stop  (설정값 변경후에는 재시작 권장)
  LSNRCTL> start
  LSNRCTL> service
  LSNRCTL> status

 

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=WIN-9IHO67M3NVF)(PORT=1521)))
서비스 요약...
"CLRExtProc" 서비스는 1개의 인스턴스를 가집니다.
"CLRExtProc" 인스턴스(UNKNOWN 상태)는 이 서비스에 대해 1 처리기를 가집니다.
"testdb" 서비스는 1개의 인스턴스를 가집니다.
"testdb" 인스턴스(UNKNOWN 상태)는 이 서비스에 대해 1 처리기를 가집니다.
명령이 성공적으로 수행되었습니다

 

# 클라이언트 확인 \dbhome_1\BIN>tnsping.exe testdb

별칭 분석을 위해 TNSNAMES 어댑터 사용
DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = WIN-9IHO67M3NVF)(PORT = 1521)) (CONNECT_DATA = (SE
VER = SHARED) (SERVICE_NAME = testdb)))에 접속하려고 시도하는 중
확인(0밀리초)

 

Oracle Net Manager - 리스너 설정등

- 프로파일
- 서비스 이름 지정
- 리스너 설정
   (ex, File : \app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
   Service : OracleOraDb11g_home1TNSListener)

 

oracle-Net-Manager-1.png

Oracle Net Configuration Assistant - 리스너 구성 등

Oracle Net Configuration Assistant 를 활용하면, 리스너 구성 등 연결, 네트워크 구성등을 할수 있습니다. 물론 환경설정 파일을 직접 수정해도 되지만 오타 또는 포맷에 맞지 않는 경우 오류가 발생하므로, 기본 제공되는 툴을 활용하는 것도  좋은 방법입니다.

리스너 : 추가, 재구성, 삭제, 이름 바꾸기
   (ex, File : \app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
   Service : OracleOraDb11g_home1TNSListener)

 

 

oracle-Net-Configuration-1.png

Oracle Database 추가/구성/삭제 - Database Configuration Assistant

Database Configuration Assistant 를 사용하여, 인스턴스를 추가로 설치하거나 이미 설치된 인스턴스를 제거할수 있습니다. 

 

oracle-Configuration-1.png

Oracle Database 설정 - Administration Assistant

Oracle 설치 완료후, 설치된 인스턴스의 설정을 변경하고자 하는 경우.
* 물론 쿼리 명령어를 사용해도 되지만, 편리한 도구가 있으면 먼저 활용해 보는것도...

 

 

- Oracle 홈 : 기본 SID, control 파일 위치등 Oracle 작동 환경 설정값 
- OS 데이터베이스 관리자/운영자 (Oracle 제어 가능한 Windows 인증 계정)
- 인스턴스의 시작/정지/옵션/프로세스. Windows 인증 계정, Role 설정

 

ex, 
아래와 같은 오류시 현재 sqlplus 를 실행하는 계정이 윈도우 "ora_dba" 그룹에 포함되어 있지 않아서 권한이 없는 경우로, Administration Assistant 의 "OS 데이터베이스  관리자 - 컴퓨터" 에 계정을 추가해 주면 됨.

SQL> conn /as sysdba;
ERROR:
ORA-01031: insufficient privileges

"ora_dba" 그룹에 기본으로 포함되어 있는 계정 : LocalSystem, Oracle 설치시 사용된 계정

 

 

oracle-Administration-1.png

MSSQL DB 분리시 연결 해제/차단

여러곳에서 연결을 해서 사용중인 DB 의 분리는, 계속해서 외부나 기타 App 에서 연결을 시도하기 때문에 단일 사용자 모드로 변경해도 오류가 발생하면서 진행되지 않을수 있음.

ALTER DATABASE 대상DB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
=> 다중 접속 서비스의 DB의 경우, 단일로 변경되어도 이미 그 단일 1개 연결이 즉시 되어 있어서 DB 분리 에러가 발생함

메시지 3703, 수준 16, 상태 2, 줄 1
데이터베이스 '대상DB'은(는) 현재 사용 중이므로 분리할 수 없습니다.

 

1. 방화벽을 통해서  SQL Port 를 차단하여 서버 외부 연결 방지
2. MSSQL 서비스외, SQLAgent 등 SQL 관련 다른 서비스를 모두 중지
3. 작업모니터 또는 프로세스 Kill 쿼리를 사용하여 대상 DB 연결 프로세스 모두 종료
4. DB 분리  EXEC sp_detach_db '대상DB', 'true';

 

* 주의 : Management Studio 와 같은 도구 이용시, DB목록을 열거 후  대상 DB를 선택하면 1개 연결이 되버리므로, DB 분리 쿼리문 연결 외  다른 연결이 되지 않도록 주의 필요

 

---

- 멀티 접속으로 변경시 ALTER DATABASE 대상DB SET MULTI_USER WITH ROLLBACK IMMEDIATE

- 아래와 같은 프로세스 kill 쿼리문과 함께 DB 분리 프로시져 쿼리문을 바로 붙여서 같이 실행

USE [master]
GO
SET NOCOUNT ON

DECLARE @DBName VARCHAR(50);
DECLARE @spidstr VARCHAR(8000);
DECLARE @ConnKilled SMALLINT

SET @ConnKilled = 0
SET @spidstr = ''
SET @DBName = '대상DB'

IF db_id(@DBName) < 4 

BEGIN 
           PRINT 'Connections to system databases cannot be killed'
           RETURN
END

SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses WHERE dbid=db_id(@DBName)

IF LEN(@spidstr) > 0

BEGIN
           EXEC(@spidstr)
           SELECT @ConnKilled = COUNT(1)  FROM master..sysprocesses
           WHERE dbid=db_id(@DBName)
END

 

MSSQL LocalSystem 계정으로 로그온

psexec -i -s "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

이 방법은, NT AUTHORITY\SYSTEM 계정이 SQL 계정 목록에 포함되어 있고, 적절한 권한이 할당 되어 있는 경우에, 사용자 계정 정보 없이 LocalSystem 계정으로 Management Studio 를 이용하여 SQL 에 접근하는 방법. (주로 관리자 계정 정보를 알수 없는 경우에 활용)

psexec 가 간단하지만, 그외 LocalSystem 계정으로 실행되는 여러가지 프로세스 또는 서비스를 활용해도 가능함.

 

 

 

SQL, Get Remote Client IP Address

SELECT *  FROM sys.sysprocesses
spid, dbid, hostname, net_address(Mac), loginname

SELECT  * FROM sys.dm_exec_connections
session_id(spid), client_net_address(IP)

SELECT    
      sys.sysprocesses.hostname,
      sys.sysprocesses.net_address,
      sys.sysprocesses.spid,
      sys.sysprocesses.loginame,
      sys.dm_exec_connections.client_net_address,
      sys.dm_exec_connections.client_tcp_port
FROM         sys.sysprocesses INNER JOIN                      
      sys.dm_exec_connections ON sys.sysprocesses.spid = sys.dm_exec_connections.session_id
WHERE     (sys.sysprocesses.spid = 93)

 

Result.
HOSTNAME   E76A9F1FADEG 93  wareway    ◯◯◯.◯◯◯.◯◯◯.◯◯◯  2405

 

 

SQL Server Web Edition 라이선스 제한 조건

Microsoft 에서는 SPLA 라이선스를 통해서 공급 가능한 SQL Server 버젼으로 Web Edition 을 제공합니다. Standard 버젼에 비해 매우 저렴하며, 미러링/복제등 DB서버간 연동 기능등이 아닌 일반적인 쿼리 기반인 경우 문제가 없을 정도로 일반적으로 많이 사용되지 않은 불필요한 기능을 뺀 제품 입니다. 하지만,.! 이 제품 사용시 사용 조건이 너무 까다로워서 실제로 많은 사용은 어려울것으로 보입니다.

사용조건 :
본 소프트웨어는 공동 및 인터넷 액세스를 지원하는 용도로만 사용됩니다.
- 웹 페이지
- 웹 사이트
- 웹 응용 프로그램
- 웹 서비스
비즈니스 응용 프로그램 계열(예: CRM, ERP 및 기타 프로그램)을 지원하는 데는 사용되지 않습니다.

위와 같은 문서상 내용으로는 웹서버 연동 DB 로 사용에는 별다른 문제가 없을것으로 해석되지만, 한국 MS 의 입장에서는 그게 아닌것 같습니다.

한국 MS 의 해석은,
- 웹사이트를 운영에만 사용하되, 가급적 로그인 기반이 아닌 단순한 페이지 용도로만 사용하기를 권장.
- 웹사이트이지만, 사이트 형식이 로그인 기반으로 상당 가능하도록 되어 있는 경우 라이센스 위반.
- Web Edition 을 사용하기전에,. 사전에 개발된 웹사이트를 MS 에 심사받아서 허가된 경우에만 가능.
위와 같은 추가적인 해석 조건을 달았습니다.

확실하지는 않지만, 소비자 입장에서 볼때에는 Web Edition 은 말그대로 별 쓸모도 없는 상품이며, 이를 미끼로 Standard 버젼을 사용할수 밖에 없도록 하여 매출 증대를 목적으로 하는 전략 같습니다.

최근의 소프트웨어는 오픈소스가 대세이며, 패키지 판매 자체가 아니라 그 서비스를 기반으로 수익을 가져가는 모델인데 Microsoft 에서는 여전히 기존 방식을 고수할 뿐만 아니라 더 강화를 하고 있는 모습니다.

이럴 경우, 소비자에게, 엄청난 소프트웨어 비용 투자 보다는 오픈소스쪽으로 선회할 가능성을 MS 에서 오히려 더 열어주고 더욱이 방향 제시까지 해주는 것 같은 느낌마져 들 정도입니다. Microsoft 에서 오픈소스 대비 총소유 비용에 대해서 강점을 얘기하지만 실제에서는 MS 얘기와는 다르게 호스팅 시장에서는 대부분 상당 낮습니다.

아무튼 Microsoft 의 라이선스 정책은 많은 아쉬움이 있습니다.