부릉부릉~

< listener.ora >
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = oracle_home)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = AA)
      (ORACLE_HOME = oracle_home)
      (SID_NAME = AA)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST=hostname or ip)(PORT=port))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
  )

위와 같은 상태에서 lsnrctl status로 리스너 상태 확인시 아래와 같이 UNKOWN 하나만 뜨는경우..
(아악.. Status가 READ가 되야 TNS에서 읽어오지..ㅠ)
Service "AA" has 1 instance(s).
  Instance "AA", status UNKNOWN, has 1 handler(s) for this service...
(+ LISTENER에 등록안된 INSTATNCE가 계속 뜨는 경우 포함..)


조치방법.
1. listener.ora 파일 확인
2. 외부시스템에서 해당 tnsping 날려보기
3. init[SID].ora 파일 확인 (name 부분)
4. 프로세스가 내가 설정한 SID의 ORACLE PROCESS만 떠있는지 확인한다.
   (난 2개 띄워져 있었음..ㅠ)
5. spfile[SID].ora(동적환경설정파일) local_listener 설정해주기.
   (이진 파일이므로 직접수정 못하므로 우선, create spfile from pfile; 로 spfile생성 후 아래 쿼리로 각 값 수정)
   alter system set LOCAL_LISTENER="(ADDRESS=(PROTOCOL=TCP)(HOST=hostname or ip)(PORT=port))"


ㅠ.ㅠ

3. SPFILE

- SPFILE : 일반적으로 spfileSID.ora 라는 파일명을 갖는다.
- Oracle 서버 실행시 초기값을 SPFILE 이 참조되기 때문에 중요하다.
- Oracle 9i 부터 새롭게 소개된 SPFILE은 텍스트 파일이 아닌 2진파일로 구성된 바이너리 파일로 직접 수정 할 수 없다.
- SPFILE은 한번 생성되면 Oracle 서버에서 관리되며 데이터베이스가 운영중인 상태에서도 변경된 파라미터를 인스턴스에 동적으로 반영시킬수 있다.
- 파일 경로 : G:\oracle\ora92\database\spfileSID.ORA

3-1. 새로운 SPFILE 생성

- 2-1.에서 생성한 PFILE을 이용해 SPFILE을 만들어 본다. (이미 SPFILE이 있기 때문에 꼭 실행시켜 줄 필요는 없다.)
우선 SPFILE을 생성 및 수정하기 전에 G:\oracle\ora92\database\SPFILEORACLE9.ORA 파일을 백업해둔다.

SQL> create spfile='G:\oracle\ora92\database\SPFILEORACLE9.ORA'
  2  from pfile='G:\oracle\ora92\admin\sample\pfile\initoracle9.ora';
create spfile='G:\oracle\ora92\database\SPFILEORACLE9.ORA'
*
1행에 오류:
ORA-32002: 인스턴스가 이미 사용 중인 SPFILE을 생성할 수 없음 - Oracle 데이타베이스 실행 중에는 SPFILE을 생성할 수 없다.

SQL> shutdown - Oracle 데이타베이스를 중지시켜준다.
데이터베이스가 닫혔습니다.
데이터베이스가 마운트 해제되었습니다.
ORACLE 인스턴스가 종료되었습니다.

SQL> create spfile='G:\oracle\ora92\database\SPFILEORACLE9.ORA'
  2  from pfile='G:\oracle\ora92\admin\sample\pfile\initoracle9.ora';

파일이 생성되었습니다.

SQL> startup - Oracle 데이타베이스를 재시작해준다.
ORACLE 인스턴스가 시작되었습니다.

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
데이터베이스가 마운트되었습니다.
데이터베이스가 열렸습니다.

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

3-2. SPFILE 수정

- SPFILE이 2진 파일이기 때문에 직접 열어보거나 수정 할 수 없기 때문에 텍스트 형태인 PFILE로 변환하여 수정할수 있다.
그러므로 SPFILE 을 수정하기 위해서는
Oracle 데이타베이스 중지 ---> 기존의 SPFILE로 PFILE 생성 ---> PFILE 편집 ---> 새로운 SPFILE 생성 ---> Oracle 데이타베이스 재시작
의 단계를 거쳐야 한다.

SQL> show user
USER은 "SYS"입니다
SQL> show sga

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes

1. 파라미터 파일의 종류

- 오라클 서버가 운영되려면 인스턴스가 메모리에 할당 되어야 하며, 이를 위해서는 파라미터 파일이 필요하다.

오라클 서버의 파라미터 파일은 과거부터 계속 사용해왔던 PFILE 과
Oracle 9i부터 새롭게 추가된 SPFILE 의 두가지 형식이 있다.

- 파라미터 파일에는 다음과 같은 내용이 저장되어 있다.
1) 인스턴스 설정을 위한 파라미터
2) 인스턴스와 연관된 데이터베이스 이름
3) SGA 구성 정보
4) 리두 로그파일의 아카이브 여부
5) 컨트롤 파일의 위치 및 이름
6) 언두 세그먼트 관련 정보

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

2. PFILE

- PFILE : 일반적으로 initSID.ora 라는 파일명을 갖는다. (SID 는 오라클의 SID값에 따라 틀려진다.)
- Oracle에서 예전부터 사용해 오던 파라미터 파일로
일반 텍스트 형식의 파이로 일반 편집기를 이용하여 수정이 가능하며, 데이터베이스가 시작되어 인트턴스가 메모리에 할당 될 때 딱 한번만 읽는다.
- 파일 경로 : G:\oracle\ora92\admin\sample\pfile\initsmpl.ora - pfile의 샘플파일이다. spfileSID.ora 를 이용해서 initSID.ora 파일을 생성해주면 된다.

2-1. PFILE 생성

- Oracle 9i를 설치완료후에는 PFILE이 샘플파일로 되어 있다. SPFILE을 이용해 PFILE을 생성해 준다.
우선 SPFILE을 생성 및 수정하기 전에 G:\oracle\ora92\admin\sample\pfile\initsmpl.ora 파일을 백업해둔다.

C:\>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on 일 Jan 11 15:55:06 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> conn /as sysdba
연결되었습니다.

SQL> create pfile='G:\oracle\ora92\admin\sample\pfile\initoracle9.ora'
  2  from spfile='G:\oracle\ora92\database\SPFILEORACLE9.ORA ';

파일이 생성되었습니다.

SQL>

- 참고      - 경로를 안적어 주어도 되지만 pfile을 확인시 제대로 생성이 안되었을 경우에는 경로를 적어주면 된다.
SQL> CREATE PFILE FROM SPFILE;
SQL> CREATE SPFILE FROM PFILE;

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

Oracle 10g에 들어와서 FLASHBACK TABLE이라는 기능이 생겼는데 기존에 drop table로 엉뚱한 테이블을 날려서 가슴졸인 개발자가 많았나보다(실제로 그런일로 고생하는 사람을 보기도 했다.).

유용한 기능이지만 이런 기능이 있는지 모르는 사람의 경우 table을 drop할때마다 이상한 이름의 테이블이 하나씩 생겨서 당황할 수 도 있을것같다.

table을 drop하면 'BIN$' 로 시작하는 이상한 이름의 테이블이 생기는데 이 테이블을 drop해보면 다음과같은 메시지를 만나게 된다.



오라클에 Recycle Bin이 있었나 하고 찾아봤더니 이게 바로 새로생긴 FLASHBACK TABLE이라는 기능이란다.

show recyclebin명령으로 확인할수가 있는데


MEMBER테이블이 2번 drop되어있고 PICTURES 테이블이 1번 drop되어 있다. 여기서 PICTURES 테이블을 다시 살리고 싶으면



휴지통을 완전히 비우고 싶으면



그리고 다음과 같이하면 휴지통에 가지 않고 바로 삭제된다.

1. FOREIGN KEY 정보 보기

SELECT  A.TABLE_NAME AS "TABLE_NAME"
, A.CONSTRAINT_NAME AS "KEY_NAME"
, B.TABLE_NAME AS "REFERENCING_TABLE"
, B.CONSTRAINT_NAME AS "FOREIGN_KEY_NAME"
, B.STATUS AS "FK_STATUS"
FROM USER_CONSTRAINTS A, USER_CONSTRAINTS B 
WHERE A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME
  AND B.CONSTRAINT_TYPE = 'R'
ORDER BY 1, 2, 3, 4;

 

2. FOREIGN KEY 해제

SELECT  'ALTER TABLE ' || B.OWNER || '.' || B.TABLE_NAME || ' DROP CONSTRAINT ' || B.CONSTRAINT_NAME || ';'
FROM USER_CONSTRAINTS A, USER_CONSTRAINTS B 
WHERE A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME
  AND B.CONSTRAINT_TYPE = 'R'
  AND A.TABLE_NAME = 'table_name';

 

3. CONSTRAINT_TYPE 종류

C   Check on a table Column
O   Read Only on a view Object
P   Primary Key Object
R   Referential AKA Foreign Key Column
U   Unique Key Column
V   Check Option on a view Object

7. 리두 로그의 양
  대용량 DML에서는 실제 작업에 의해 발생하는 리두 로그를 기록하는 부분에 의해 성능이 저하된다. 테이블 및 인덱스 생성 옵션 중 NOLOGGING옵션이 존재한다. 해당 옵션을 설정한 후 DML 작업을 수행하면 데이터 변경에 의해 발생하는 로그는 리두 로그 버퍼에 기록하지 않게 된다. 단지 오라클 내부에서 사용하는 기본적인 로그만을 생성하게 된다. 따라서 대용량 DML에서 리두 로그의 양을 최소화하여 성능을 향상시킬 수 있게 된다. 모든 DML에 대해서 해당되는 사항은 아니며 다음과 같은 경우에 해당된다.
          - 다이렉트 로딩(Direct loading) 작업(/*+ append*/, /*+ parallel*/를 사용하는 Insert작업)
          - CREATE TABLE ..... AS SELECT 또는 CREATE INDEX 문
          - ALTER TABLE ..... MOVE PARTITION
          - ALTER INDEX ..... REBUILD PARTITION
  NOLOGGING 상태로 위의 명령을 수행하게 되면 변경되는 데이터에 대한 리두 로그 정보가 기록되지 않으므로 디스크 I/O감소에 의한 수행 속도가 향상된다. NOLOGGING 옵션을 다음과 같은 방법으로 설정할 수 있다.

          SQL> CREATE TABLE NAME_1 NOLOGGING AS SELECT * FROM NAME;
          SQL> ALTER TABLE NAME_1 NOLOGGING;

  NOLOGGING옵션을 사용하면 DML 작업과 테이블 및 인덱스 변경 작업을 보다 빠르게 수행할 수 있다. 하지만 작업에 대한 로그가 리두 로그 파일에 기록되지 않기 때문에 장애시 해당 세그먼트를 복구할 수 없다는 단점이 있게 된다. 그러므로 NOLOGGING 사용시 작업 수행 속도와 복구 가능 여부에 대한 충분한 고려가 필요하다.

8. 데이터 블록 크기와 성능
  현재는 디스크의 속도가 월등히 좋아졌기 때문에 데이터 블록 크기를 어떻게 설정하는가에 따른 성능 차이는 거의 없다. 데이터 블록의 크기보다는 디스크 I/O 횟수에 의해 성능이 좌우되기 쉽다. 요즘은 대부분의 시스템이 대용량 데이터베이스로 변하고 있기 때문에 보통의 경우 데이터 블록 크기를 크게 설정하게 된다. 데이터 블록의 크기는 2,4,8,16,32 및 64KB 등으로 설정될 수 있다.

9. PCTFREE 옵션의 설정
  업무 특성상 데이터 INSERT만 발생하고 UPDATE는 발생하지 않는 테이블이라면 PCTFREE 값을 0으로 설정하여 데이터 블록 공간을 보다 효율적으로 활용할 수 있을 것이다.

10. 행 이전 제거
  행 이전이 많이 발생된 테이블에 대해 행 이전을 제거하기 위해서는 EXPORT/IMPORT 또는 CREATE TABLE AS SELECT 명령으로 테이블을 재구성해야한다. 테이블을 재구성하면 기존에 발생한 행 이전은 완전히 해소되므로 행 이전이 많이 발생하는 테이블에 대해서는 주기적인 테이블 재구성을 수행하여 행 이전을 해결하도록 해야 한다.

고수위(High Water Mark, HWM) : 세그먼트 생성 이후 최대 사용량을 표시하는 세그먼트 구성요소

11. 고수위와 성능
  테이블에 저장된 데이터 겅수는 얼마 되지 않지만 테이블 전체 스캔을 수행하는 시간이 오래 소요되는 경우가 있다. 이것은 앞에서 언급했던 전체 스캔시 고수위 밑에 있는 모든 데이터 블록을 엑세스하기 때문이다. 이런 경우에는 EXPORT/IMPORT eHSMS CREATE TABLE AS SELECT 명령 등으로 테이블 재구성을 수행하거나 온라인 세그먼트 축소를 수행하도록 한다.
  또한, 테이블의 형태만 필요할 경우 DELETE를 수행하지 말고 TRUNCATE를 수행하여 고수위 및 이미 할당된 익스텐트를 할당 해제하는 것이 좋다.

12. 다이렉트 로딩(Direct Loading)
  로딩이란 데이터를 해당 테이블에 저장하는 것을 의미한다. 로딩에는 일반 로딩과 다이렉트 로딩이 존재한다. 다이렉트 로딩은 /*+  APPEND*/라는 힌트를 이용하여 INSERT를 수행하는 것으로 일반 로딩보다 빠른 성능을 보장하게 된다. 다이렉트 로딩은 고 비용의 고수위 이동을 수행하지 않고 바로 테이블에 모든 데이터를 저장하는 방법으로 다음과 같이 수행한다.
          SQL> INSERT /*+ APPEND*/ INTO 사원_임시 SELECT * FROM 사원;

13. 인덱스 세그먼트 축소와 인덱스 재구성
  인덱스 재구성시 여유 공간이 부족하다면 우선 먼저 인덱스 세그먼트 축소를 수행하는 것이 공간적인 면에서는 유리할 수 있다. 인덱스 세그먼트 축소 후 크기가 작아진 인덱스로 재구성을 수행한다면 보다 적은 공간으로 인덱스 재구성을 수행할 수 있다. 하지만 이경우 인덱스 재구성만을 수행하는 것보다 더 많은 작업시간이 필요하며, 인덱스 세그먼트 축소에 의한 부하가 발생 할 수 있기 때문에 많은 고려 및 테스트를 수행해야 한다.

 

[출처]초보자를 위한 Oracle 10g_권순용, 김지한, 황오현저

1. alertSID.log 파일의 관리
  오라클이 오랜 기간 동안 서비스를 하게 되면 alertSID.log 파일의 크기는 매우 커지게 된다. 그렇기 때문에 해당 파일 시스템에 여유공간이 부족해 질 수 있다. 이때 해당 alertSID.log파일을 삭제하게되면 오라클에서는 alertSID.log 파일은 필수 파일이므로 바로 재생성을 하게 된다. 따라서 크기가 너무 커진 alertSID.log 파일은 삭제를 하거나 참조를 위해 다른 곳으로 옮겨 놓으면 alertSID.log는 다시 생성되어 데이터베이스의 모든변경을 기록하게 된다.

2. 시작과 종료
  하위의 시작 단계에서 상위의 어느 시작 단계로든 이행을 가능하나 역순으로는 불가능하다. 즉, SHUTDOWN -> NOMOUNT -> MOUNT -> OPEN은 가능하나 OPEN -> MOUNT -> NOMOUNT 는 불가능 하다.

3. 데이터베이스 기동과 롤백
  비정상 종료 시점에 수행되던 작업은 데이터베이스 재기동시 롤백 작업을 수행하게 된다. 이것은 오라클의 모든 버전에서 동일하다. 하지만 오라클 8버전 이전은 데이터베이스를 오픈하지 않고 롤백을 수행했지만 그 이후 버전은 오라클을 오픈하고 내부적으로 SMON백그라운드 프로세스에 의해 롤백을 수행하게 된다. 이는 오라클 버전이 높아지면서 서비스 정지 시간을 최소화하겠다는 의미가 된다.

4. 빠른종료
  보통의 경우 데이터베이스를 종료하기 위해서는 SHUTDOWN IMMEDIATE를 수행하게 된다. IMMEDIATE는 수행중인 작업에 대해 강제 종료를 하며 롤백을 수행하게 된다. 이를 좀더 빠르게 수행하고자 하다면 SHUTDOWN IMMEDIATE를 수행하기 전에 운영체제에 존재하는 서버 프로세스를 강제 종료하여 빠르게 롤백을 수행하게 한다면 좀더 빠른 종료를 수행할 수 있을 것이다.

5. 리두 로그 파일 사용량 측정
  데이터베이스를 운영하다 보면 아카이브 로그 모드 적용을 위해 리두 로그 파일 저장에 필요한 파일 시스템 크기 계산 및 시간대별 또는 요일별 데이터베이스 사용량 파악 등을 위해 리두 로그 파일의 생성추이를 확인해야 할 경우가 자주 발생한다. 이 경우 다음과 같이 V$LOG_HISTORY 동적 성능 뷰를 검색하여 확인할 수 있다.


  FIRST_TIME컬럼은 DATE 타입이므로 원하는 값을 추출하기 위해서는 TO_CHAR() 함수를 적용해야 한다. 위와 같이 수행하면 하루 동안 생성된 리두 로그 파일의 개수를 확인할 수 있을 것이다.

6. 아카이브와 데이터베이스 행(Hang)
  아카이브 로그 모드에서 로그를 기록하고자 하는 리두 로그 파일의 아카이브가 완료되지 않아서 행(Hang) 현상이 발생할 경우 해결 방법은 다음과 같다.
     1. ARCH 백그라운드 프로세스의 개수 증가
     2. 리구 로그 그룹의 개수 및 크기 증가
  리두 로그 그룹의 개수 및 크기를 증가시킨다는 것은 리두 로그 그룹 순환이 가급적 늦게 발생하게 하는 방법이다. 즉, 로그 스위치에 의해 체크포인트 및 아카이브가 수행된 리두 로그 그룹이 다시 사용되기까지의 소요시간을 더 많이 확보하면 체크포인트 및 아카이브가 종료될 가능성은 더 높아진다는 것을 이용한 것이다 이를 이용하여 리두 로그 그룹의 개수 및 크기를 증가시키면 데이터베이스 행을 감소시킬 수 있게 된다.
  데이터베이스 행 현상이 발생하는 원인은 보통 디스크 I/O속도와 리두 로그 생성 속도간의 차이 때문이다. 이 경우 ARCH프로세스 수를 증가시키는 것은 큰 효과가 없을 수 있으므로 리두 로그 그룹의 개수와 크기를 증가시키는 방식을 권장한다.

SELECT SEGMENT_NAME,
             PARTITION_NAME,
             SEGMENT_TYPE,
             TABLESPACE_NAME,
             BYTES,  --이 놈이 용량.ㅋ
             BLOCKS,
             EXTENTS
FROM    USER_SEGMENTS

에러 메시지는 다음과 같다.
 
12542, 00000, "TNS:address already in use"
*Cause: Specified listener address is already being used.
*Action: Start your listener with a unique address.

부가적으로 TNS-12560, 512 error 가 동반된다.

12560, 00000, "TNS:protocol adapter error"
*Cause: A generic protocol adapter error occurred.
*Action: Check addresses used for proper protocol specification. Before

reporting this error, look at the error stack and check for lower level
transport errors.For further details, turn on tracing and reexecute the
operation. Turn off tracing when the operation is complete.

00512, 00000, "Address already in use"
*Cause: Specified listener address is already being used.
*Action: Start your listener with an unused address.

혹은

% lsnrctl start

LSNRCTL for SVR4: Version 2.0.15.0.0 - Production on 26-SEP-94 11:10:11
Copyright (c) Oracle Corporation 1993. All rights reserved.
Starting /solar/7016/bin/tnslsnr: please wait...

과 같은 상태에서 Hang 이 걸리기도 한다.

조치 방법
=========

1. 이미 Listener 가 기동되어 있는지 확인한다.

$ ps -ef | grep tnslsnr
또는
$ lsnrctl status
로 확인

기동이 되어 있다면 lsnrctl Utility 로 종료시킨다.

$ lsnrctl stop

이때 정상적으로 종료가 되지 않는다면 kill 명령을 이용하여 Process 를 제거한다.

$ ps -aef | grep tnslsnr

다음과 같이 나타나다고 하면,

oracle 9798 1 80 11:08:18 ?
0:03 /solar/7016/bin/tnslsnr LISTENER
oracle 9823 8531 5 11:11:37 pts/24
0:00 grep tnslsnr

% kill -9 9798

2. /etc/services 파일을 열어 listener 가 사용하는 Port 와 중복되게 지정된 항목이 있는지 확인한다.

3. Listener.ora 파일에 다음 파라미터가 설정되어 있는지 확인하기 바란다.

USE_CKPFILE_LISTENER = TRUE

위에서 listener.ora 의 Port 를 1522 로 변경했다 하더라도 위의 파라미터가 지정되어 있다면 전혀 문제가 해결되지 않는다.

USE_CKPFILE_LISTENER = FALSE

로 변경한다. 위 파라미터로 생성된 Checkpoint 파일도 삭제한다.

$ cd $ORACLE_HOME/network/admin
$ mv listener.ckp

========================
* USE_CKPFILE_LISTENER *
========================

SQL*NET 2.3.X 버전에서 적용되는 파라미터이다.
이 파라미터는 Checkpoint 를 발생하도록 한다. 디폴트는 FALSE 이다.
TRUE 로 셋팅을 하면 마지막으로 Listener를 stop 했을때의 listener.ora
파일의 내용을 listener.ckp 파일에 그대로 paste 한다.
Listener 기동시 listener.ckp 파일과 listener.ora 파일을 비교하여
변경사항이 있으면 변경된 내용이 적용되어 기동되지 않는다.
 
4. Listener 를 재기동한다.

$ lsnrctl start

# TNS- 12542 에러의 메시지만을 보면 TNS:address already in use 로 나와 Server ip address 가 충돌이 나는 것으로 볼 수 있지만 ip address 가 충돌이 난다면 Booting 시 이미 문제가 발생한다.
이것은 대부분 listener 의 Port 를 다른 프로세스나 OS 에서 이미 할당받아 사용하는 것으로 볼 수 있다. Listener 의 Port 를 변경하면 해결된다.
Listener 의 Port 를 변경할 수 없는 상황이라면 System Rebooting 을 하는 것도 한가지 방법이 될 수 있다.
System Rebooting 후에도 계속해서 발생한다면 다른 프로세스가 listener의 Port 를 분명히 사용하고 있는 것이다. System 엔지니어와 상담하여 어느 프로세스인지 확인이 필요하다.