HighLight in your life~

 안녕하세요... 하이라이터입니다.
 오늘은 윈도우 7을 사용할 때 발생하는 한 문제에 대해서 해결해보도록 하겠습니다.

 윈도우 7 환경에서 oracle DB를 backup할 때 아래와 같은 메시지가 발생할 수 도 있습니다.

1. 아카이브 모드 설정

SQL> select * from v$log;
SQL> archive log list
SQL> shutdown immediate
SQL> exit
[C:\] edit  c:\oracle\product\10.2.0\oradata\orcl\INITorcl.ORA

 (추가)
  log_archive_dest = 'c:\oracle\product\10.2.0.\db_1\database\archive'
  log_archive_format = '%t_%r_%s.arc'

[C:\] sqlplus /nolog

SQL> conn sys/oracle as sysdba;
SQL> startup mount
SQL> alter database archivelog;
SQL> archive log list
SQL> alter database open;

위의 경우는 spfile이 없는 경우 적용이 가능하다.
만약 spfile이 있다면 그 전 spfile을 적용하여 나타난다. 변경한 pfile으로 spfile을 새로 만들어 주어야 한다.

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

2. 아카이브 모드 확인 하기

SQL> ARCHIVE LOG LIST
SQL> SET LINESIZE 1200
SQL> SELECT GROUP#, SEQUENCE#, ARCHIVED, STATUS FROM V$LOG;
SQL> SELECT ARCHIVER FROM V$INSTANCE;

SQL> ALTER SYSTEM SWITCH LOGFILE;

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

3. 기타 등등
SQL> select spid, name from v$process, v$bgprocess where paddr(+)=addr;

SQL> create temporary tablespace temp2
tempfile 'C:\oracle\product\10.2.0\oradata\orcl\temp02.dbf' size 3m;

SQL> select tablespace_name, file_name from dba_temp_files;

SQL> select * from dba_tablespaces;

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

4. 관련 URL

http://ttend.tistory.com/717

http://www.gurubee.net/lecture/1873

http://oracle-childschool.tistory.com/20

[C:\] sqlplus "/as sysdba"

SQL> SPOOL C:\oracle_files.txt

1. 모든 데이터 파일의 구조 분석
 SQL> COL TABLESPACE_NAME FORMAT A15
 SQL> COL FILE_NAME FORMAT A50
 SQL> SELECT file_id, file_name, tablespace_name FROM dba_data_files;

2. 모든 컨트롤 파일의 구조 분석
 SQL> COL NAME FORMAT A50
 SQL> SELECT * FROM v$controlfile;

3. 모든 리두로그 파일의 구조 분석
 SQL> COL MEMBER FORMAT A50
 SQL> SELECT * FROM v$logfile;

SQL> SPOOL OFF
SQL> EXIT
[C:\] EDIT C:\oracle_files.txt ---> 물리적 구조 정보 참조가 가능하다.
 
 
P.S.  컨트롤 파일 백업

SQL> alter database backup controlfile to trace;

1. Data dictionary
 ---> 데이타 딕셔너리는 데이터베이스 자원을 효율적으로 관리하기 위한 다양한 정보를 저장하는 시스템 테이블이다. 데이터 딕셔너리는 사용자가 테이블을 생성하거나 사용자를 변경하는 등의 작업을 할 때 데이터베이스 서버에 의해 자동으로 갱신되는 테이블로 사용자는 데이터 딕셔너리의 내용을 직접 수정하거나 삭제 할 수 없다.
 ---> 이러한 데이터 딕셔너리르 사용자가 조회해 보면 시스템이 직접 관리하는 테이블이기에 암호같은 기호만 보여 질 뿐 내용을 알 수 없다.

 2. Data dictionary view
 데이터 딕셔너리 원 테이블은 직접 조회시 의미있는 자료 조회가 불가능하기에 오라클은 사용자가 이해할 수 있는 데이터를 산출해 줄 수 있는 데이터 딕셔너리에 파생하 데이터 딕셔너리 뷰를 제공한다.

1) USER_데이타 딕셔너리
 SHOW USER;
 DESC USER_TABLES;
 SELECT table_name FROM user_tables ORDER BY table_name DESC;

USER_TABLES(USER_INDEXES) : 해당 User가 소유한 테이블 정보
SELECT * FROM USER_TABLES;

2) ALL_데이타 딕셔너리
 DESC all_tables;
 SELECT owner, table_name FROM all_tables;

ALL_TABLES(ALL_INDEXES) : dictionary에 등록된 모든 테이블을 User별로
SELECT * FROM ALL_TABLES WHERE OWNER='로그온아이디(대문자)';
SELECT * FROM ALL_TABLES

3) DBA_데이타 딕셔너리
 CONN SYSTEM/MANAGER
 SELECT owner, table_name FROM dba_tables WHERE owner='SYSTEM';

4) ALL_PART_TABLES : 파티션 테이블 검색
SELECT * FROM ALL_PART_TABLES;

5) SELECT * FROM ALL_OBJECTS;

6) 파티션 인덱스 상태 체크
- SELECT i.table_name, i.index_name, ip.partition_name, i.status AS index_status, ip.status AS partition_status
FROM user_indexes i
JOIN user_ind_partitions ip
ON (i.index_name=ip.index_name)
ORDER BY i.table_name, i.index_name, ip.partition_name;

- 파티션 인덱스는 rebuild가 안된다. 다시 생성해주어야 한다. DROP하고 다시 CREATE해야 한다.

1. 해당 유저가 가진 테이블의 개수
SELECT count(*) FROM user_tables;

2. 해당 DB의 CHARACTERSET 확인
SELECT parameter, value FROM nls_database_parameters WHERE parameter='NLS_CHARACTERSET';
SELECT parameter, value FROM nls_database_parameters WHERE parameter like '%CHARACTER%';

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

3. CHARACTERSET 재설정하기
http://angdekim.egloos.com/3619358

[출처 : 서책임님 자료 및 웹검색 자료]

오라클 설치후 CHARACTER SET이 맞지 않는 경우 SELECT후 한글이 깨지거나

한글컬럼에 INSERT 할 경우 INSERT에 실패하는 경우가 종종 발생한다.

크게 봤을 때 두가지 케이스 인데

한가지는 서버의 CHARACTER SET 문제, 다른 한가지는 클라이언트 피씨의 레지스트리 문제이다.

MSTR데모를 만들다 위와 같은 문제가 발생하여 다음과 같이 처리 하였다.

오라클은 10g R2버젼을 사용하였다.

먼저 CHARACTER SET을 확인한다.

select * from v$nls_parameters;

DW권한으로 DB에 접속한다.

[oracle@XXX]$sqlplus/nolog

SQL>conn /as sysdba
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER DATABASE OPEN;

-->한글 CHARACTER SET은 KO16KSC5601을 사용한다.

SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE KO16KSC5601 ;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

-->CHARACTER SET을 확인한다.

SQL> SELECT * FROM sys.props$ where name='NLS_CHARACTERSET';

정상적으로 작업하였다면 변경되어 있을 것이다.
하지만 여기까지 작업을 한 이후 다시 한글 데이터를 조회하였으나
여전히 한글이 깨져있었다.
원인을 찾아보니 NLS_LANGUAGE와 NLS_TERRITORY가
각각 AMERICAN, AMERICA로 설정되어 있었기 때문이다.

따라서 다음과 같이 설정을 해주니 한글이 정상적으로 출력되었다.

DBA권한으로 다시 로그인한다.

conn /as sysdba

설정을 확인한다.

SELECT NAME, VALUE$ FROM PROPS$
WHERE NAME = 'NLS_LANGUAGE' OR
NAME = 'NLS_TERRITORY OR
NAME  = 'NLS_CHARACTERSET';

다음과 같이 UPDATE 하였다.

UPDATE props$ SET VALUES$='KOREAN' WHERE NAME ='NLS_LANGUAGE';
UPDATE props$ SET VALUES$='KOREA' WHERE NAME ='NLS_TERRITORY';

commit;

다시 확인해보면 변경되어 있을 것이다.

SELECT NAME, VALUE$ FROM PROPS$
WHERE NAME = 'NLS_LANGUAGE' OR
NAME = 'NLS_TERRITORY OR
NAME  = 'NLS_CHARACTERSET';

정상적으로 변경되었다면 오라클을 재시작한다.

shutdown immediate
startup

모든 작업이 완료되었다.

이제 한글 데이터가 들어있는 테이블을 조회해본다.

여기서 유의할점은 이미 한글이 insert되어 있다면 역시나 깨져서 보일 것이다.

따라서 기존 데이터를 삭제한후 다시 Insert를 해야하는 번거로움이 발생한다.

이부분은 어떤면에서는 상당히 이슈가 될 수 있으므로 정책을 세워줄 필요가 있을 듯 싶다.

 1. 테이블스페이스 크기 조절
  ---> 테이블스페이스 생성시 용량이 부족할 경우 자동으로 용량이 증가하는 옵션(autoextend)이 존재한다. 하지만, 이 옵션을 사용하지 않고 테이블스페이스를 생성했는데, 부득이하게 용량을 조절해야 할 경우가 발생할 수 있다. 대부분의 경우 데이터의 양이 급격히 늘어나서 발생하지만, 간혹 용량을 줄이기 위해 사용할 때가 있으므로 자동증가 옵션만 믿고 크기 조절에 대해서 잊어보리면 안될 것이다.

 alter database datafile '[데이터파일명]' resize [크기(예. 300M)];

 2. Table/Index의 테이블스페이스 변경
  ---> 사용자가 테이블을 생성하거나 인덱스 등을 생성할 때 별다른 옵션을 주지 않는다면 사용자에게 부여된 기본 테이블스페이스에 해당 객체를 생성하게 된다. 그러나, 상황에 따라 테이블이나 인덱스가 저장된 테이블스페이스를 다른 테이블스페이스로 변경할 수도 있다.

 alter table [테이블명] move tablespace [테이블스페이스명];
alter index [인덱스명] rebuild tablespace [테이블스페이스명];

 
 Tablespace란? DBMS에 저장되는 자료가 있는 물리적인 파일이다.
각 개체마다 테이블스페이스를 지정해줄 수 있고, 사용자에게 기본으로 사용할 테이블스페이스를 지정할 수도 있다. 또한 오라클에서는 작업을 위한 Temp 테이블스페이스도 존재하는데, 이 역시 별도로 사용자가 생성하고 지정할 수 있다. 테이블스페이스는 각각이 하나의 파일이기 때문에 생성시 물리적으로 저장될 공간을 지정할 수 있다. 이러한 특징 때문에 디스크 부하를 고려하여 여러 위치에 테이블스페이스를 생성하여 사용할 수 있다.

 보통 오라클에서는 /usr/local/oracle/oradata/[SID]/ 디렉토리에 기본 테이블스페이스를 생성하며, Oracle Express Edition에서는 /usr/lib/oracle/xe/oradata/XE/ 디렉토리에 기본 테이블스페이스를 저장한다.

 1. 테이블스페이스 생성
  ---> 테이블스페이를 생성할 경우 여러 옵션이 사용될 수 있으나 테이블스페이스의 기본 용량을 자동으로 늘려주는 옵션(autoextend) 등은 기본으로 사용하는 것이 편하나, 대부분의 회사에서는 사용하지 않는다.

create tablespace [테이블스페이스명]
datafile '[테이블스페이스경로(예. /usr/lib/oracle/xe/oradata/XE/)]/[테이블스페이스명].dbf'
size [용량(예. 1M)]
autoextend on
extent management local autoallocate;

 2. Temp 테이블스페이스 생성
  ---> Temp 테이블스페이스의 최소 용량은 1 M 이상이다. 그러므로 처음 생성시 2M로 생성한 뒤 자동증가 옵션을 사용해야 한다.
 

create temporary tablespace [테이블스페이스명]
tempfile '[Temp 테이블스페이스경로(예. /usr/lib/oracle/xe/oradata/XE/)]/[테이블스페이스명].dbf'
size [용량(예. 2M)]
autoextend on
extent management local;

3. 테이블스페이스 삭제
 ---> 특별한 경우가 아니면 테이블스페이스의 모든 내용을 자동으로 지우고 파일까지 깔끔하게 삭제할 수 있다. 이 옵션이 바로 "INCLUDING CONTENTS AND DATAFILES" 이다.

 drop tablespace [테이블스페이스명]
including contents and datafiles
cascade constraints;

4. 테이블스페이스 데이터파일 추가 및 RESIZE

SELECT FILE_NAME, BYTES/1024/1024/1024||' GB' FROM DBA_DATA_FILES X WHERE TABLESPACE_NAME='USERS' ORDER BY FILE_NAME;

ALTER TABLESPACE USERS ADD DATAFILES '/oradata/users02.df' SIZE 10G;

ALTER DATAFILE '/oradata/users02.df' RESIZE 30G;

1. 테이블스페이스에 대한 데이타 파일의 정보 조회
 1) SELECT * FROM DBA_DATA_FILES;

 2) SELECT SEGMENT_NAME, TABLESPACE_NAME, BYTES, BLOCKS, FILE_ID FROM DBA_EXTENTS
WHERE OWNER = 'SCOTT';

 2. 테이블스페이스에 대한 자동 메모리 확장
CREATE TABLESPACE "DATA_XOSL2_D_P"
LOGGING
DATAFILE
'D:\DATABASE\SLXO2P\DATAFILES\DATA_XOSL2_D_P01.ORA' SIZE 4M
 AUTOEXTEND ON
NEXT 2M
MAXSIZE 32M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
 ---> 4M부터 시작해서 2M씩 증가해서 32M까지 자동 증가하게

3. 테이블스페이스 삭제하기
DROP TABLESPACE DATA_XOSL2_D_P;
  ---> 실제 데이타파일은 삭제 되지 않았다. 해당 파일을 지워주어야 한다.
 
만약 해당 데이타파일도 함께 삭제하고 싶으면 아래와 같이 입력해주면 된다.
DROP TABLESPACE DATA_XOSL2_D_P INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

아래의 구문들은 본인이 참고하기 위한 것입니다. 만약 Test를 위한 것이라면 아래의 구문으로 Test하시기 바랍니다.
1. Test 테이블 스페이스 생성하기
CREATE TABLESPACE "DATA_TEST"
LOGGING
DATAFILE
'D:\DATABASE\SLXO2P\DATAFILES\DATA_TEST01.DBF' SIZE 4M,
'D:\DATABASE\SLXO2P\DATAFILES\DATA_TEST01.DBF' SIZE 4M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

 ---> AUTO 옵션을 주면 할당된 용량이 다 차면, 자동으로 용량을 늘려준다.

2. 본인이 참고하기 위해 만든 것들
1) 데이타가 들어갈 테이블 스페이스 생성하기
CREATE TABLESPACE "DATA_XOSL2_D_P"
LOGGING
DATAFILE
'D:\DATABASE\SLXO2P\DATAFILES\DATA_XOSL2_D_P01.ORA' SIZE 4000M REUSE,
'D:\DATABASE\SLXO2P\DATAFILES\DATA_XOSL2_D_P02.ORA' SIZE 4000M REUSE,
'D:\DATABASE\SLXO2P\DATAFILES\DATA_XOSL2_D_P03.ORA' SIZE 4000M REUSE,
'D:\DATABASE\SLXO2P\DATAFILES\DATA_XOSL2_D_P04.ORA' SIZE 4000M REUSE,
'D:\DATABASE\SLXO2P\DATAFILES\DATA_XOSL2_D_P05.ORA' SIZE 4000M REUSE,
'D:\DATABASE\SLXO2P\DATAFILES\DATA_XOSL2_D_P06.ORA' SIZE 4000M REUSE,
'D:\DATABASE\SLXO2P\DATAFILES\DATA_XOSL2_D_P07.ORA' SIZE 4000M REUSE,
'D:\DATABASE\SLXO2P\DATAFILES\DATA_XOSL2_D_P08.ORA' SIZE 4000M REUSE
DEFAULT STORAGE ( INITIAL 2048K NEXT 2048K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 10);

2) 위의 데이타 테이블 스페이스에 대한 인덱스 테이블 스페이스 생성하기
CREATE TABLESPACE "INDX_XOSL2_D_P"
LOGGING
DATAFILE
'E:\DATABASE\SLXO2P\DATAFILES\INDX_XOSL2_D_P01.ORA' SIZE 4000M REUSE,
'E:\DATABASE\SLXO2P\DATAFILES\INDX_XOSL2_D_P02.ORA' SIZE 4000M REUSE,
'E:\DATABASE\SLXO2P\DATAFILES\INDX_XOSL2_D_P03.ORA' SIZE 4000M REUSE,
'E:\DATABASE\SLXO2P\DATAFILES\INDX_XOSL2_D_P04.ORA' SIZE 4000M REUSE,
'E:\DATABASE\SLXO2P\DATAFILES\INDX_XOSL2_D_P05.ORA' SIZE 4000M REUSE,
'E:\DATABASE\SLXO2P\DATAFILES\INDX_XOSL2_D_P06.ORA' SIZE 4000M REUSE,
'E:\DATABASE\SLXO2P\DATAFILES\INDX_XOSL2_D_P07.ORA' SIZE 4000M REUSE,
'E:\DATABASE\SLXO2P\DATAFILES\INDX_XOSL2_D_P08.ORA' SIZE 4000M REUSE,
'E:\DATABASE\SLXO2P\DATAFILES\INDX_XOSL2_D_P09.ORA' SIZE 4000M REUSE,
'E:\DATABASE\SLXO2P\DATAFILES\INDX_XOSL2_D_P10.ORA' SIZE 4000M REUSE
DEFAULT STORAGE ( INITIAL 2048K NEXT 2048K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 10);

3) 위의 임시 테이블 스페이스 생성하기
CREATE TEMPORARY
TABLESPACE "DATA_TEMP" TEMPFILE
'D:\DATABASE\SLXO2P\DATAFILES\DATA_TEMP01.ORA' SIZE 4096M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K;