가브레

보통 오라클을 사용하시다보면 TOAD, GOLDEN , SQL Developer 등을 사용하게 됩니다.

이번 방법은 ui 를 통해 index rebuild 하는 방법이 아닌 sql문을 통해 인덱스 리빌드 방법에 대해서 알려드리겠습니다.

일단 cmd 창을 키고 index rebuild 가 필요한 계정에 접속 합니다.

Microsoft Windows [Version 10.0.15063]
(c) 2017 Microsoft Corporation. All rights reserved.

C:\Users\>sqlplus 해당하는계정/해당하는계정비밀번호 

SQL*Plus: Release 11.2.0.2.0 Production on 수 5월 29 19:25:30 2019

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL>

이후에

 1. select index_name from user_indexes;
   // 인덱스 조회하는 명령어 

2. alter index 인덱스명 rebuild
   // 인덱스 리빌드하는 명령어 

명령어를 통해 1번으로 인덱스를 조회 후 2번으로 인덱스를 리빌드 합니다.

1.

SQL> select index_name from user_indexes;

INDEX_NAME
------------------------------------------------------------
PK_TEST1
IDX_TEST2
PK_TEST3
IDX_TEST4
IDX_TEST5
PK_TEST6
PK_TEST7
SYS_IL0000032323C00004$$
PK_TEST8
PK_TEST9
PK_TEST10

INDEX_NAME
------------------------------------------------------------
PK_TESTINFO
PK_TESTINFO2
PK_TESTINFO3
PK_TESTINFO_TEST
PK_TESTINFOTEST 
PK_TESTINFO4

17 rows selected.

2.

SQL> ALTER index PK_TEST1 rebuild;

Index altered.

인덱스 리빌드 완료 입니다.

하지만 이럴경우 17개의 인덱스를 일일이 쳐야 하기 때문에 좀더 쉬운 SQL 문이 있습니다.

3. 

select 'alter index '||index_name||' rebuild ;' from user_indexes;
    
// 인덱스 리빌드 조회 + 인덱스 리빌드 실행시 사용되는 명령어 자동 생성 쿼리 (1,2 번 따로 진행 안하고 3번 부터 진행 해도 문제 없음)

3번을 sql에 칠 경우

'ALTERINDEX'||INDEX_NAME||'REBUILD;'
--------------------------------------------------------------------------------
alter index PK_RTBS rebuild ;
alter index IDX_TTBERGTDT_AVRTIME rebuild ;
alter index IDX_THDGHDT_COUNT rebuild ;
alter index IDX_TIDFGDYR_LIST rebuild ;
alter index PK_TGST rebuild ;
alter index PK_THIS rebuild ;
alter index PK_TJTRST rebuild ;
alter index SYS_IL0000032323C00004$$ rebuild ;
alter index PK_TGDFGO rebuild ;
alter index PK_TVBTRYT rebuild ;
alter index PK_TBDFBGYNFO rebuild ;

'ALTERINDEX'||INDEX_NAME||'REBUILD;'
--------------------------------------------------------------------------------
alter index PK_ rebuild ;
alter index PK_HFGUTRNGFO rebuild ;
alter index PK_TDHGFHFGINFO rebuild ;
alter index PK_DFHFGHFGO_NS rebuild ;
alter index PK_TSGHSHGLINFO rebuild ;
alter index PK_HDGHFFT rebuild ;

17 rows selected.

이런식으로 자동으로 명령어를 복사 붙혀넣기 할수있게 나옵니다.

저 내용들을 긁어서 실행하면 자동으로 인덱스 리빌드가 진행 됩니다.

4. 마지막으로 COMMIT; 은 필수 입니다.

SQL> COMMIT;

Commit complete.

월요일 부터 전화 폭탄이 터지기 시작 했습니다.

제일 먼저 걸려온 전화로는 "시스템이 작동이 안되고 있습니다." 바로 확인하려 접속하자마자 반겨주는 에러창들

이렇게 다수의 에러창이 발생하고 있었습니다. 처음에는 당황했습니다. 

리스너가 죽은건가 ? tnsname 이 바뀐건가?? tns로그가 쌓인게 터진건가?

이렇게 생각하면서 하나하나 차근차근 진행중 찾게되었습니다.

일단.. 서버에 들어가볼수 있을까? 싶어 접속 시도를 해보았지만..

네 그렇습니다. 접속이 안됩니다.

lsnrctl status 명령어로 살펴보아도 .. 문제는없었습니다.

리스너를 재 시작 해보아도 같은 에러가 발생하기에 오라클을 강제로 재시작 하려고 서비스 창을 들어간 순간...

네.. 그렇습니다. 서비스가 죽어있었습니다. 일단.. 서비스를 오른쪽 키로 실행 버튼을 눌러 실행 시켰습니다.

무슨 문제일까 싶어 윈도우 이벤트 로그를 분석 해보았습니다.

원인은 가상 메모리 부족으로 판단하고 있습니다.

많은 경고들과 함께..

죽었습니다.. 휴일이라 오라클 서비스가 죽었던게 월요일 되어서야 확인이 된겁니다.

오라클 재시작 , 리스너 재시작 후 정상가동을 확인 하였습니다.

이런경우 여유가 되시면 가상메모리를 가장 많이 차지하는 프로그램을 점검 후 서버 재부팅을 하여 사용해보시면 될거 같습니다.

기존에 존재하는 서버램이 8GB 였는데 이번에는 서버에 8GB 를 추가로 달면서 16GB 램으로 변경 되었습니다.

하지만 오라클은 8GB 때 깔아놓은 것이라 기본적으로 50% 인 4GB램 으로 세팅 해놓은 상황이라 이번에는 8GB로 할당 하려 합니다.

SGA 설정을 통해 기존에 4GB로 할당해놧던 램을 8GB로 변경하는 명령어 입니다. (오라클 11G는 자동할당 메모리를 사용하기에 SGA_MAX_TARGET, SGA_TARGET 등을 수정할 필요 없이 alter system set memory_max_target=메모리양 scope=spfile; 명령어를 통해 간편하게 수정 하도록 하겠습니다.

위와같이 show parameter memory; 를 통해 조회시 3872MB 메모리가 잡혀있는것을 확인 하실수 있습니다.

이것을 8192MB 로 바꿔보겠습니다. (8GB)

ALTER SYSTEM 명령어 뒤에 scope에 대하여

memory : 변경이 현재 상태에만 영향영향을 미침(1회성) db가 restartup 되면, 이전 값으로 돌아감 

spfile : 변경 내용을 spfile에만 저장하고 현재 상태에는 영향을 미치지 않게 합니다. Static Parameter의 경우는 이 scope만 지정 가능 합니다.             Restart없이 반영하는 것은 불가능

both : 변경 내용을 현재 상태에도 바로 반영하고, spfile에도 반영시켜, 이후 rebooting 시에도 영향을 미치도록 합니다.

shutdown immediate; 입력 후 시작 -> 검색 -> service 를 입력 합니다.

서비스 클릭 후 OracleService 로 시작하는 서비스 오른쪽 클릭 후 중지

중지가 완료 되면 시작 을 눌러 다시 실행중 으로 변경 합니다.

두개 명령어로 조회를 해보면 최대 메모리가 8GB 로 할당 된것을 볼수 있습니다.

이번에 고객사에서 연락이 왔다. " 서버 상태가 좋지 않으니 서버를 새로 구성해야 겠습니다. "

나는 그리하여 기존에 납품나간 서버를 분석(?) 하고 있었다.

데이터를 최종적으로 백업 받으려고 EXPORT를 진행 한 순간 로그에 에러가 쌓이면서 백업이 Export 가 되지 않아 에러 내용을 보는순간 캐릭터 셋 문제인것을 확인 했습니다.

당시 Export시 화면

EXP-00008: ORACLE error 6552 encountered

ORA-06552: PL/SQL: Compilation unit analysis terminated

ORA-06553: PLS-553: character set name is not recognized

EXP-00000: Export terminated unsuccessfully

원인 : 캐릭터셋(문자셋) 중복 으로 인해  발생한 문제

조회결과

이 서버의 경우는 KO16MSWIN949 , US7ASCII 두 캐릭터셋이 중복되어 발생한 문제였습니다.

TYPES_USED_IN 이 두가지 이상 중복된경우 문제가 발생합니다.

KO16MSWIN949                             CLOB         

US7ASCII                                 CLOB  

KO16MSWIN949                             CHAR   

US7ASCII                                 CHAR

KO16MSWIN949                             VARCHAR2

KO16MSWIN949 , US7ASCII 이두가지 에서 문제가 발생한것 입니다.

해결방법

완료시 정상 캐릭터셋

저는 문제없이 해결이 완료 되었으나 이 작업을 시도중 에러가 생기실수 있습니다.

에러에 대해서는 차후에 포스팅 하겠습니다.

오랜만에  글을 쓰게 되었습니다.. (그동안 바쁘기도 하고 서버가 터지는 일이 많이 줄어서..)

앞선 글에 ORA-01653 오류 대처에 대한 정보만 남겼는데 더 간단하고 쉽게 설명하기 위해서 글을 다시 씁니다.

(사실 최근에 이 문제로 한번 더 터졌습니다 서버가..)

고객사에서 기능들이 작동하지 않는다고 차장님에게 전화가 왔습니다.

차장님이 확인 후 혹시 이 문제에 대한 해결방법을 알고있냐고 물었고 발생한 에러를 보니

ORA-00604 : error occurred at recursive SQL level 1

ORA-01653 : unable to extend table SYS.AUD$ by 8192 in tablespace SYSTEM

ORA-02002 : error while writing to audit trail

ORA-00604 : error occurred at recursive SQL level 1

ORA-01653 : unable to extend table SYS.AUD$ by 8192 in tablespace SYSTEM

에러가 뿜어져 나오고 있었습니다.. 

이 에러에 원인을 알고 있던 저는 바로 자리에서 원격을 접속하여 (윈도우버전)

 

sqlplus /nolog     - sqlplus 로 패스워드없이 접속

 

conn /as sysdba     - sysdba 계정권한으로 로그인

 

truncate table SYS.AUD$;     -테이블 잘라내기

 

commit;    -저장

 

명령어를 입력 후 서버데몬을 시작하니

정상 작동 되었습니다.

이 문제가 리눅스 버전에서도 발생 할수 있기 때문에 리눅스에서 발생시 해결 방법을 알려드립니다.

리눅스 터미널에 접속하여 (리눅스버전)

 

su - oracle 

 

sqlplus /nolog

 

connect /as sysdba 

 

truncate table SYS.AUD$;

 

commit;

 

명령어를 입력하면 정상적으로 오라클 이용이 가능 합니다.

추가로 윈도우 에서는 이렇게 지속적으로 명령어 치기가 힘든경우 윈도우 배치파일 과 작업 스케줄러 를 이용하면 더욱 쉽게 사용 가능 합니다.

추가적인 방법은 차후에 또 다른 글로 설명 드리겠습니다.

ORA-01653: unable to extend table SYS.AUD$ by 8192 in tablespace 에러가 오늘 아침에 발생 했습니다.

기숙사에서 자고있는와중에 시스템들이 동작하지 않는다고 연락이 와서 저번처럼 업체 네트워크 문제인지 확인해달라고 하여 부랴부랴 노트북이 있는 회사로 가려하던중 같은 연락을 받으신 대리님이 " 내가 들어보니 SYS.AUD 테이블스페이스 용량 꽉 찬거 같으니까 오라클 재시작하고 truncate 쓰고와 " 라고 말씀하셨고 내려가서 확인해보니 위와 같은 사진과 함께 오라클 연결이 되지 않았습니다.

그래서 대리님이 말하신대로 truncate를 하려하던 도중 또다른 에러를 만났습니다.

ORA-00054: 리소스가 사용 중이어서 NOWAIT가 지정되었거나 시간 초과가 만료된 상태로 획득합니다. 

대략 난감 했었습니다. 빠르게 구글링 해본 결과

"사용중인 테이블에LOCK이 걸린 상태에서 발생하는 오류"
"Commit" 으로 해결

누군가 작업을 하고 commit 를 안친건지 의아해 하던 도중 대리님이 " 너 오라클 서비스 안껏다켰지? " 라고 말씀하셨습니다.

네 맞습니다 저는 오라클 서비스를 재시작 하지 않고 명령어를 치고있던 중이였습니다.

사실 이 문제는 commit 한번 과 truncate table SYS.AUD$; 로 해결할수 있는 문제 였을지도 모르겠습니다.

하지만 무슨 작업을 누가 어떻게 한지 확인을 못한 상황이여서 commit 보단 오라클 서비스 재시작을 선택 한거 같습니다.

만약 commit 을 입력 했더라도 그대로 안되는거라면 다음과 같은 방법을 이용 합니다.

SELECT

    S.SID,

    S.SERIAL#

FROM

    (

        SELECT SID

        FROM V$LOCK

        WHERE ID1 IN (SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME = 'IP_NMCBOARD_T')

    ) X

    JOIN V$SESSION S ON X.SID = S.SID;

SID SERIAL#

--- -------

5   1

6   1

2. 다음 명령으로 SESSION들을 KILL한다.

SQL> ALTER SYSTEM KILL SESSION '5, 1';

SQL> ALTER SYSTEM KILL SESSION '6, 1';

Windows Server 2012 R2 64Bit 윈도우에 Oracle Database Express Edition 11g Release 2 64Bit 설치 후 프로그램 테스트 완료 후 월요일이 되었습니다.

회사에서 Oracle Database Express Edition 11g Release 2 32Bit 가 더 안정적으로 작동 하니 32Bit 설치를 요청하여 다시 테스트 하자는 이야기가 있었고 저는 그에따라 Windows Server 2012 R2 64Bit에 Oracle Database Express Edition 11g Release 2 32Bit 설치를 시작 했습니다. 
일단 Oracle Database Express Edition 11g Release 2 64Bit 를 제거 하겠습니다.

** 설치 당시 SETUP 파일을 가지고 있는 경우 SETUP 파일로도 언인스톨이 가능 합니다.

1. 오라클 설치당시 받았던 버전의 셋업파일을 실행 합니다.

2. Repair , Remove 선택 창이 나타납니다. -> Remove 선택 후 Next -> finish

** 설치 당시 SETUP 파일이 없는경우

서비스에 등록되어있는 오라클 관련 서비스 제거

1. 시작 -> 실행 -> CMD(관리자권한실행) 창에 5개의 서비스 제거 명령어를 입력

C:\Users\Administrator> sc delete "OracleJobSchedulerXE"

C:\Users\Administrator> sc delete "OracleServiceXE"

C:\Users\Administrator> sc delete "OracleXEClrAgent"
C:\Users\Administrator> sc delete "OracleMTSRecoveryService"

C:\Users\Administrator> sc delete "OracleXETNSListener"

2. oraclexe 폴더를 삭제

3. 시작 -> 실행 -> regedit(관리자권한) 실행

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ 

HKEY_LOCAL_MACHINE\SOFTWARE\odbc\odbcinst.ini\Oracle in XE 관련 폴더 삭제

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\OracleXE 관련 폴더 삭제

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\OracleXE 관련 폴더 삭제

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\OracleXE 관련 폴더 삭제

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Application\oracle 관련 폴더 삭제

4.서버 재부팅

2018-05-21 업체에서 프로그램 사용중 ORA-12571: TNS:패킷 기록자 실패. 라는 alert 창이 화면에 떠있다며 연락이 왔습니다.

DB 서버가 터진줄 알고 부랴부랴 원격을 통해 들어가보니 DB 서버는 정상 동작중이고 특정PC 1대 에서만 ORA-12571: TNS:패킷 기록자 실패. alert 창을 확인하였고, 프로그램 상에서 로그 확인 결과  Network Connection Fail 이 발생되었던 것을 확인 했습니다. 즉 PC에 인터넷이 잠시동안 끊기면서 생긴 에러로 추정 됩니다.
제가 업무를 하면서 처음 보았던 ORA-12571 ERROR 이기에 검색 등을 통해서 무엇이 문제인지 파악 하던 도중 최근에 sqlnet.ora에 적용 하였던 SQLNET.EXPIRE_TIME=3 문구가 ORA-12571 관련 에러로 보이기 시작해 찾아보기 시작 하였습니다. 

출처: http://soul0.tistory.com/211 [돌이의 소소한 이야기★]
SQLNET.EXPIRE_TIME=3 즉 3분 동안 PC에서 DB 쪽에 신호를 보내지 않아 끊기면서 발생한 에러였던 것입니다. 그 이유인 즉슨 3분 이상 동안 인터넷 연결이 끊어지면서 서버와 통신 하지 못하고 ORA-12571 얼럿창이 발생 하였던 문제였습니다. 더 자세한 문제 파악을 위해 고객사에 네트워크 작업 여부를 문의 해놓은 상태 입니다.
인터넷에서는 SQLNET.EXPIRE_TIME=3 을 주석처리 하거나 0 으로 바꾸라는 이야기를 보았는데요, 사실상 이러면 SQLNET.EXPIRE_TIME=3 를 쓰는 의미가 없기에 따로 바꾸거나 다른 조치를 취하지는 않았습니다.