오라클 9i 이전과 이후에 temporary tablespace 설정이 다르며, 아래 사항은 9i 이후 설정방법 입니다.

temp tablespace 관련 테이블 : V$TEMPFILE, dba_temp_files, V$TEMP_EXTENT_POOL


1. temp tablespace 설정 상태 확인
SQL> select * from dba_temp_files;

FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS    RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- -----------
/data/ORCL/datafile/o1_mf_temp_3vsro6sw_.tmp
         1 TEMP                           1066401792     130176 AVAILABLE            1 YES 3.4360E+10    4194302           80 1065353216      130048


2. temp tablespace 사용량 및 용량 확인
SED,(B.BYTES-A.BYTES_USED)/1024/1024 || ' M' "FREE", ROUND(A.BYTES_USED/B.BYTES SQL> SELECT B.NAME ,B.BYTES/1024/1024 || ' M' TOTAL SELECT B.NAME ,B.BYTES/1024/1024 || ' M' TOTAL ,A.BYTES_USED/1024/1024 || ' M' USED,(B.BYTES-A.BYTES_USED)/1024/1024 || ' M' "FREE", ROUND(A.BYTES_USED/B.BYTES * 100) || '%' "USAGE" FROM
(SELECT BYTES_USED,FILE_ID FROM V$TEMP_EXTENT_POOL)A, (SELECT BYTES,FILE#,NAME FROM V$TEMPFILE)B WHERE A.FILE_ID = B.FILE#;


3. temp tablespace 사용중인 세션 확인
set line 150
col username format a10
col osuser format a10
col tablespace format a15
SELECT b.tablespace,
b.segfile#,
b.segblk#,
b.blocks,
a.sid,
a.serial#,
c.spid,
a.username,
a.osuser,
a.status
FROM v$session a,
v$sort_usage b,
v$process c
WHERE a.saddr = b.session_addr and a.paddr=c.addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;



[참고 사이트]
http://blog.naver.com/PostView.nhn?blogId=dangtong76&logNo=140042613114
http://dbrang.tistory.com/564
http://blog.daum.net/liberalis/12917306
https://forums.oracle.com/forums/thread.jspa?threadID=427050

2011/09/09 16:41 2011/09/09 16:41

http://www.oracleclub.com/lecture/1200


사용방법
rawtohex(DBMS_CRYPTO.Hash(to_clob(passwd),2))

두번째 파라미터
1 : md4, 2 : md5, 3 : sh1 암호화 방식을 의미

넣을때 암호화하여 넣고, 비교할 때 문자열을 암호화 하여 비교 한다.
문자열 암호화 : rawtohex(DBMS_CRYPTO.Hash(to_clob(to_char('inputpw')),2))


2011/08/30 18:07 2011/08/30 18:07

출처 : http://nemesisx.blog.me/10077782380
Oracle 11g R2 Download

http://www.oracle.com/technology/software/products/database/index.html



/etc/sysctl.conf 파일을 열어 다음 내용을 추가한다.

kernel.shmmax = 536870912
kernel.shmall = 2097152

fs.aio-max-nr = 1048576
fs.file-max = 6815744

kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default=262144
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=1048586

kernel.shmax와 kernel.shmall은 Default값이 있어 기존것으로 설정했다.

기본값이 있을 경우 위값보다 높으면 상관없다.



/sbin/sysctl -p 실행



/etc/security/limits.conf 파일을 열어 아래내용 추가

     oracle  soft  nproc   2047
     oracle  hard  nproc   16384
     oracle  soft  nofile  1024
     oracle  hard  nofile  65536



/etc/pam.d/login 파일을 열어 아래내용 추가

session    required     pam_limits.so



 /etc/selinux/config 파일을 열어 SELINUX를 disabled 시켜준다.

셋팅값을 적용하기 위해 OS를 리부팅 시킨다.



부팅후 CentOS 시디나 이미지를 마운트 시킨다.

/media/CentOS_5.4_Final/CentOS로 가서 필요한 패키지를 설치한다.

인터넷에 연결된다면 yum install을 이용해 설치해도 상관없다.



                        필요한 패키지 목록

      • compat-libstdc++-33-3.2.3-61
      • elfutils-libelf-0.125-3.el5
      • elfutils-libelf-devel-0.125-3.el5
      • glibc-2.5-12
      • glibc-devel-2.5-12
      • glibc-common-2.5-12
      • gcc-4.1.1-52.el5
      • gcc-c++-4.1.1-52.el5
      • kernel-headers
      • libgcc-4.1.1-52.el5
      • libaio-0.3.106-3.2
      • libaio-devel-0.3.106-3.2
      • libstdc++-4.1.1-52.el5
      • libstdc++-devel-4.1.1-52.el5
      • unixODBC-2.2.11-7.1
      • unixODBC-devel-2.2.11-7.1
      • sysstat-7.0.0-3.el5
      • binutils-2.17.50.0.6-2.el5
      • make-3.81-1.1

        패키지 설치 명령어

  • rpm -Uvh compat-libstdc++-33*
  • rpm -Uvh elfutils-libelf*
  • rpm -Uvh glibc-2.*
  • rpm -Uvh glibc-devel-2.*
  • rpm -Uvh glibc-common-2.*
  • rpm -Uvh glibc-headers-2.*
  • rpm -Uvh gcc-4.*
  • rpm -Uvh gcc-c++-4.*
  • rpm -Uvh ksh*
  • rpm -Uvh libgcc-4.*
  • rpm -Uvh libaio-0.*
  • rpm -Uvh libaio-devel-0.*
  • rpm -Uvh libgomp-4.*
  • rpm -Uvh libstdc++-4.*
  • rpm -Uvh libstdc++-devel-4.*
  • rpm -Uvh unixODBC-2.*
  • rpm -Uvh unixODBC-devel-2.*
  • rpm -Uvh sysstat-7.*
  • rpm -Uvh binutils-2.*
  • rpm -Uvh make-3.*


oinstall과 dba그룹을 만들고 oracle 계정을 만든다.



/oracle 디렉토리를 만든다.

오라클 설치할 기본 디렉토리는 /oracle/11g다.

소유자를 oracle로 변경하고 쓰기가 가능하도록 퍼미션을 변경한다.



oracle 계정으로 접속하여(su - oracle)

 .bash_profile 파일에 다음내용을 추가한다.

SID의 경우 NEMI라는 이름으로 지정하였다.

ORACLE_HOSTNAME=localhost.localdomain; export ORACLE_HOSTNAME
ORACLE_BASE=/oracle/11g; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=NEMI; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH


SAMBA나 FTP(oracle계정)를 통해 Oracle 11g R2 zip파일 2개를 서버로 전송한다.


아래와 같이 압축을 푼다.

unzip linux.x64_11gR2_database_1of2.zip
unzip linux.x64_11gR2_database_2of2.zip


이제부터 xming을 통해 Install을 할 것이다.

xming은 원격으로 GUI용 Linux프로그램을 윈도우화면에서 볼 수 있게 해주는 프로그램이다.

xming이 아니더라도 CentOS 메인 화면에서 설치 할 수 있다.


참고로 export LANG=c 를 꼭 해주어야 글자가 안깨진다.

압축푼 폴더에서 ./runInstaller 를 실행시키자.



Email을 입력하면 보안이슈 관련 내용을 보내주는 것 같다.

크게 필요없다면 체크박스를 해제하고 Next를 누른다.



데이터베이스까지 생성하기 때문에 첫번째를 선택한다.



서버용 설치이므로 Server Class를 선택



RAC를 통해 이중화 구성을 하지 않으므로 싱글인스턴스 선택



기본 설치인 Typical Install 선택



설치 위치를 정할 수 있는 화면이다.

글로벌 데이터베이스명과 패스워드를 입력한다.



오라클 인벤토리 설치 위치를 정하는 화면



오라클 설치중에서 제일 중요한 부분이다.

다른 부분보다 파라미터와 패키지는 fail로 나오면 안된다.

파라미터값이 fail일 경우 권장값으로 변경하고

패키지가 fail일 경우 rpm이나 yum을 통해 반드시 설치한다.

패키지의 경우 무시하면 설치 중 에러가 난다.


테스트용 서버라 메모리와 Swap 사이즈는 무시하고 설치하였다.

ignore All를 체크하면 무시하고 설치가 진행된다.



최종 설정 정보를 확인한다.

특이사항이 없으면 Finish를 누른다.



오라클 11g 설치가 진행된다.



데이터베이스 생성 화면



설치가 완료되었다.



Password Management를 누르면 유저 잠금여부와 암호를 지정할 수 있다.



root계정으로 접속하여 2개의 스크립트를 실행하자.

 


최종적으로 설치가 완료된다.



sqlplus로 접속하여 인스턴스를 확인해 보자.

2011/07/13 12:01 2011/07/13 12:01

import 시 아래와 같은 에러가 발생한다면 파일이 깨진 것일수도 있고 FTP 업로드시 바이너리로 업로드 되지 않아
에러가 발생할 수도 있다.

이번 경우는 디스크장애로 인하여 복구업체에서 데이터 복구시 파일이깨져 아래와 같이 오류가 발생하였다.

illegal lob length marker 48373
 bytesread = 00000000000
 TABLE = KI_SADAYLSISE
IMP-00098: INTERNAL ERROR: impgst2
IMP-00028: partial import of previous table rolled back: 14490 rows rolled back
IMP-00008: unrecognized statement in the export file:
IMP-00008: unrecognized statement in the export file:
2011/06/20 13:15 2011/06/20 13:15
우선 imp-0008 에러의 정의에 대해서 말씀드리겠습니다.
아래와 같이 export file이 corrupted 되거나 import의 인터널버그일 경우에
발생합니다. 경험으로는 export file을 다른서버로 옮길 때에 ftp를
사용하는데 default는 ASCII mode인데 그대로 ftp로 옮겼을 경우에는
export dump file이 corrupted됩니다. binary file로 dump file을 옮겨야하겠습니다

IMP-00008: "unrecognized statement in the export file: \n %s"
Cause: Unrecognized statement in export file. This could be due to
corrupted export file or Import internal bug.
Action: If the export file is corrupted, retry with a new export file.
Else report this as Import internal error.

이외에 나오는 에러는 없는지요?
검색을 해보기에는 imp-0008이 발생하는 버그 및 problem으로 등록된 것이
아래의 경우에 나와있습니다.

1) You may have hit Bug 257475.

This bug only occurs if you are exporting snapshots that contain references
to database links. Performing a 'strings' command on the export file will
confirm this.

2) export dump file을 분할했는데 import시 순서에 맞지 않게 지정했을 경우
IMP-00047: unexpected file sequence number; expected 2 but found 3
IMP-00132: first file in the multi-file export is exp_f1.dmp
IMP-00008: unrecognized statement in the export file:
...
IMP-00047: unexpected file sequence number; expected 3 but found 2
IMP-00132: first file in the multi-file export is exp_f1.dmp
IMP-00008: unrecognized statement in the export file:
...

3) export dump file을 분할했는데 import시에 잘못된 이름을 넣었을 경우
- specifying a non existing filename results in:
IMP-00002: failed to open [name_of_file] for read
- specifying a wrong filename results in:
IMP-00048: mismatched file header
IMP-00008: unrecognized statement in the export file:
or import will abort with:
IMP-00048: mismatched file header
IMP-00009: abnormal end of export file
Import terminated successfully with warnings.

3) 테이프 장치를 통해서 export file을 직접 direct로 export할 경우
테이프장치는 ANSI-labeled tapes만 지원됩니다.

" Export file created by via conventional path
. importing CE's objects into CE
IMP-00008: unrecognized statement in the export file:
00
"CREATE SEQUENCE "ASI_CE_ID_SEQ" MINVALUE 1 MAXVALUE
99999999999999999999999"
"9999 INCREMENT BY 1 START WITH 925520 CACHE 20 NOO"
IMP-00008: unrecognized statement in the export file:"

You can see this via the output of SHOW=Y on the import from the tape
directly
The Oracle Export utility is only supported for ANSI-labeled tapes.

4) license_max_users 파라미터에 지정된 값보다 많은 유저를 import하려
할 경우, default는 0으로 license limit을 안합니다.

In this case it was caused by the init.ora parameter license_max_users. The
parameter was set to 55. After increasing the parameter to the correct number
of users, the import was successful.

5) buffer부족시
만약 IMP-00032까지 나올 경우에는 buffer를 충분히 주고 import합니다.
ex) imp system/manager file=test.dmp full=y log=test.log buffer=
10000000

Export file created by EXPORT:V08.00.05 via conventional path
.
import done in US7ASCII character set and US7ASCII NCHAR character set
. importing OPS$ORACLE_CDR's objects into SYSTEM
IMP-00032: SQL statement exceeded buffer length
IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file

6) Bug 4656020 - Export / import of schema based XMLType table fails
10.2.0.2 버젼에서 XML을 사용시 IMPORT시에 문제발생
10.2.0.3에서 FIX됨

Import of XML based tables may fail processing ALTER TABLE statements
subsequent to the CREATE TABLE for the subject tables. Import may also
issue "IMP-00008: unrecognized statement in the export file" errors.

7) Bug 2750878 IMPORT does not work for Schema based XMLTable in multibyte
9.2버젼에서 XML데이터를 import시에 발생
IMPORT does not work for Schema based XMLTable with a multibyte database.
Import fails with errors like:

IMP-00017: following statement failed with ORACLE error 6550:
IMP-00003: ORACLE error 6550 encountered
ORA-06550: line 13, column 14:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the
IMP-00008: unrecognized statement in the export file:

글 수정:
민천사 (민연홍)

제가 도서관에서 공부중이라..;; 도서관pc실..
이제 공부하러 가야한답니다.~ 저녁때나 글 올릴 수 있겠네요.

2011/06/16 20:43 2011/06/16 20:43

rman
connect target /
delete archivelog all completed before 'sysdate -3';

2011/05/26 17:17 2011/05/26 17:17
-- 락걸린 테이블 확인

SELECT  do.object_name,  do.owner,  do.object_type,  do.owner,
  vo.xidusn,  vo.session_id,  vo.locked_mode
FROM
  v$locked_object vo ,  dba_objects do
WHERE   vo.object_id = do.object_id ;
 
 
--해당테이블이 락에 걸렸는지.. 

SELECT   A.SID,  A.SERIAL#,  B.TYPE,  C.OBJECT_NAME
FROM   V$SESSION A,  V$LOCK B,  DBA_OBJECTS C
WHERE   A.SID=B.SID AND  B.ID1=C.OBJECT_ID
   AND  B.TYPE='TM'  AND  C.OBJECT_NAME IN ('테이블명');
   
 
 /* 락발생 사용자와 sql, object 조회 */

SELECT   distinct x.session_id,  a.serial#,
  d.object_name,  a.machine,  a.terminal,
  a.program,  b.address,  b.piece,  b.sql_text
FROM  v$locked_object x,  v$session a,  v$sqltext b,  dba_objects d
WHERE  x.session_id = a.sid  and
  x.object_id = d.object_id  and
  a.sql_address = b.address 
order by b.address,b.piece;

 
/* 락 발생 사용자확인 */

SELECT   distinct x.session_id,  a.serial#,
  d.object_name,  a.machine,  a.terminal,  a.program,
  a.logon_time ,  'alter system kill session ''' || a.sid || ',  ' || a.serial# || ''';'
FROM   gv$locked_object x, gv$session a,  dba_objects d
WHERE   x.session_id = a.sid  and  x.object_id = d.object_id
order by logon_time; 


/* 접속 사용자 제거 */

--alter system kill session 'session_id,serial#';
alter system kill session '26,6044';


/* 현재 접속자의 sql 분석 */

SELECT   distinct a.sid,  a.serial#,
  a.machine,  a.terminal,  a.program,
  b.address,  b.piece,  b.sql_text
FROM   v$session a,  v$sqltext b
WHERE   a.sql_address = b.address
order by a.sid, a.serial#,b.address,b.piece

출처 : http://unions5.tistory.com/70


[추가]
락걸린 세션 확인

select a.sid, a.serial#
from v$session a, v$lock b, dba_objects c
where a.sid=b.sid and
b.id1=c.object_id and
b.type='TM'

2011/05/21 17:06 2011/05/21 17:06

프로시저를 사용하기 전에 프로시전 사용권한이 있어야 함.

1. 프로시저 생성방법

create or replace procedure test
is
begin
insert into test values("aaa");
commit;
end test;
/

2. 프로시저 실행방법

exec test;

3. 프로시저 확인 방법
3.1 사용자 프로시저 확인
select *
from user_source
where type=upper('procedure');

3.2 사용자 프로시저 소스 확인
select *
from user_source
where name=upper('test');

3.3 전체 사용자 프로시저 확인
select *
from all_procedures
where owner=upper('user_id');

3.4 전체 사용자 프로시저 소스 확인
select *
from all_source
where name=upper('test') and owner=upper('user_id');

2011/05/21 17:04 2011/05/21 17:04

사용자 삭제 -> 테이블 스페이스 삭제(실제 데이타파일은 삭제 안되어짐) -> 데이터파일 이동
-> 테이블 스페이스 생성 -> 유저 생성 -> 권한 주기  -> 복원

drop user USERNAME cascade; <= 사용자 삭제
drop tablespace TABLESPACE; <= tablespace 삭제

* 테이블 스페이스 삭제시 정상적으로 삭제가 되었는지 확인방법
select * from dba_data_files where file_name='/data/ORCL/datafile/TABLESPACE.dbf'

mv /data/ORCL/datafile/TABLESPACE.dbf /data/rm_data 로 임시로 옮겨 둠.

* drop user시 기접속이 있는 경우 세션 KILL를 먼저 실행하여 작업 전 접속이 없도록 해야 함.

3. tablespace 및 사용자 생성
CREATE TABLESPACE TABLESPACE DATAFILE '/data/ORCL/datafile/TABLESPACE.dbf' size 200M autoextend off EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

create user USERNAME identified by USERNAME default tablespace TABLESPACE profile BUSINESS;

grant connect,resource to USERNAME;

4. 복원
imp system fromuser=DDEV_192 touser=USERNAME  file=20110217.DMP log=logfile.log

5. 특정 테이블 테이터만 복원(테이블 생성은 수동으로 해야함)
imp system file=20110217.DMP fromuser=이전계정 touser=USERNAME log=logfile2.log tables=\(TABLENAME\) ignore=y

2011/02/27 00:28 2011/02/27 00:28

처음 ORA-3136을 접했을때 생각해봤던 건데 도움이 될까 해서 정리해서 올립니다.


10g가 되면서 inbound_connect_timeout 이 60초로 바뀌게 되었고 그로 인해서 가끔씩 alert에 제목과 같은


메지시가 나오곤 합니다.


우선 ORA-3136이 발생되는 경우를 살펴보면


CASE 1


[email protected]/oracle> sqlplus /@rac1
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jan 7 13:33:27 2009
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:


이상태에서 타이핑 없이 대기를 하게되면 60초 후에 alert log에 ORA-3136 이 떨어지게 됩니다.


그렇다면 그 60초 동안 어떤일이 발생하는가?


1. ps -ef|grep LOCAL=NO 로 확인해 보면 process가 하나 늘어나 있는 것을 볼 수 있습니다.


2. v$process 나 v$resource_limit를 보면 process가 하나 늘어나 있는 것을 볼 수 있습니다.


위를 통해서 60초를 강제로 설정해 놓은 이유를 알 수 있습니다.


누군가 악의적인 목적으로 저런식의 접속을 한다음 끊지 않으면 설정해 놓은 process개수를 다 채워버리게 되고


정상적인 connection이 처리되지 못하는 문제가 있겠죠. 따라서 60초를 주기로 oracle에서는 저런 process를 강제로


죽이는 것입니다.


ORA-3136을 발생시키지는 않지만 유사한 경우가 있습니다.


CASE 2


[email protected]/oracle> telnet 192.168.0.11 1521
Trying 192.168.0.11...
Connected to 192.168.0.11 (192.168.0.11).
Escape character is '^]'.


위와 같이 했을 경우에는 다음과 같은 현상이 나타나게 됩니다.


1. ps -ef|grep LOCAL=NO 로 확인해 보면 process하나 늘어나 있는 것을 볼 수 있습니다.


2. v$process 나 v$resource_limit를 보면 변화가 없습니다.


즉 OS상에서는 process가 뜨지만 oracle의 view에는 반영이 되지 않는 상황입니다.


이러한 상황도 system resource를 사용하는 것이기 때문에 그다지 좋은 상황은 아니겠죠.



위에 유사한 두가지 CASE에 대해서 설명을 했는데 ORA-3136 에 대해서 좀 찾아보신 분들은 알겠지만 이 메세지를 안나오게


하기위해서는 sqlnet.ora 에 SQLNET.INBOUND_CONNECT_TIMEOUT = 0 설정을 하거나 listener.ora에


INBOUND_CONNECT_TIMEOUT_<listener_name> = 0 을 설정하라고 합니다.


제가 테스트 해본 결과에 의하면


sqlnet.ora - SQLNET.INBOUND_CONNECT_TIMEOUT = 0 : sqlplus /@RAC1 에만 영향을 줍니다.

listener.ora - INBOUND_CONNECT_TIMEOUT_<listener_name> = 0  : telnet 192.168.0.11 1521 에만 영향을 줍니다.


즉 APP의 접속 형태에 따라 sqlner.ora와 listener.ora를 취사선택하거나 둘다 설정을 해야할 필요성이 있는 것입니다.



INBOUND_CONNECT_TIMEOUT을 설정할때는 위와 같은 내용을 인지하고 설정을 하셔야 할것으로 생각됩니다.


무작정 ERROR를 보이지 않게 하기 위해서 설정을 한다면 보안상 심각한 문제를 야기할 수도 있으니까요.


즉 ORA-3136이 발생하게 되면 connection이 늦게 처리되는 APP에 대해서 먼저 확인하고 60초 안에 접속이 이루어 지도록


APP단의 수정을 해야 하는 것이 우선이라고 생각합니다.


## 10.2.0.4 linux에서 테스트한 결과 입니다. 버전에 따라 차이가 있을 수 있음을 .. 이해하시길..

2011/02/19 17:38 2011/02/19 17:38