sqlplus는 접속되는데 DBMS에서는 접속불가인 경우

 

먼저 요약글, 뒤에서 상세하게 서술

 

====================================================

요약 : 

 

명령프롬프트에서 sqlplus를 통해 접속은 되는데 dbms에서는 접속이 안되는 경우가 있다.

 ( sqlplus로 접속하는 방법은 구글링하면 많이 나오니 생략 )

 

이 경우에는 설정파일을 수정하면 된다.( listener.ora, tnsnames.ora )

 

설정파일을 수정하였다면 해당 내용을 dbms에 알맞게 적용시키면 된다.

 

 

 

본인은 oracle developer 와 orange4oracle 두개의 툴과 명령프롬프트(cmd)를 통한 sqlplus 접속을 통해서

 

각자 비교하면서 조정하였다.

 

 

 

간혹 리스너로그파일 사이즈가 4기가 이상일 때도 동일하게 장애가 발생한다. 이 때는 해당 로그파일을 제거해주면 된다(제거해도 자동으로 다시 생김)

 

 

 

계정권한, 방화벽은 위의 문제에 대한 해결책이 아니지만 그래도 함께 적어보겠다.

 

계정권한 부여

참고 : https://jaoracleva.tistory.com/4

HR, SCOTT 계정 사용하기

 

 

5. HR계정 사용하기

 

HR계정이란 oracle xe에서 기본적으로 생성되는 학습용계정이다.

이 계정은 기본적으로 lock되어있는 DB이다.

이 계정을 사용하기 위해선 unlock시켜줘야 한다.

 

*hr계정을 unlock하는법

 

1) conn hr/블라블라 -> locked되어있는 메세지가 출력된다.

2) conn sys/as sysdba로 접속

3) alter user hr account unlock; -> 계정을 잠그는 명령은 unlock을 lock으로 변경

 

*사용자(hr)가 소유한 모든 테이블 보기

select table_name from user_tables;

 

*HR DB의 employees 테이블 정보보기

select * from employees;

select first_name, last_name from employees;

//first_name, last_name 이 두개를 보겠다

 

*employees테이블의 컬럼정보보기

desc employees;

 

6. 현재 접속해있는 사용자 보기

 

show user;

 *전체 사용자 정보 보기 (sys계정으로 접속해야 한다)

select username, default_tablespace from dba_users;

 

 *conn hr/as sysdba(hr계정으로 sysdba에 접속)

 

7. scott계정생성(버전에 따라 있을 수 있고 없을 수 있다)

 

1) scott user 존재유무 확인법

conn scott/임의의 pw 또는 alter user scott account unlock;

   만약 invalid user~~가 출력되면 없는 계정이란 의미이다.

 

2) scott user 생성(scott사용자가 없을때)

-sys권한으로 접속해서 scott user 생성

create user scott identified by tiger    //scott계정의 비밀번호는 tiger

default tablespace users              //user에 임시생성

temporary tablespace temp;          //temp라는 임시 템퍼로리에 입력

 

-사용자를 삭제하는법

drop user scott; //scott계정을 완전히 삭제한다. 이 구문을 쓰면 계정복구는 불가능

 

3) scott 사용자에게 권한부여법

grant connect, resource to scott;

//이 작업을 완료해야 scott사용자로 접속가능

*이 작업을 실행해도 스캇계정에 접속이 안될때가 있는데

 sys계정으로 접속후 scott계정의 비밀번호를 재설정해주면 접속이 가능하다.

 

4) scott계정에 샘플데이터 생성법

-scott내 테이블 목록확인

select * from tab;

 

-scott에 학습용 오브젝트(테이블...등등) 일괄생성

[sql>]conn /as sysdba

[sql>]@C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\scott.sql;

//@-실행하라(뒷내용을[scott.sql]). system유저 상태에서 실행할것

//실행하면 scott유저로 자동접속이 되어있음(show user;->"SCOTT")

select * from tab;

 

//실행후 scott유저로 접속이 안될시 비밀번호 변경하면 접속가능

select username from all_users;

alter user scott identified by tiger;

 

desc tab;//탭 내용 보기

select tname from tab;//tname 테이블만 보겠다

 

5) scott 정보 조회하기

desc emp; //emp테이블에 있는 컬럼정보조회

select * from emp; // 사원정보조회

select ename from emp; //사원명만 조회

 

 

 

 

방화벽은 아래 블로그를 참고하면 된다.

 

참고 : https://bloodygale.tistory.com/entry/Oracle-%EC%9C%88%EB%8F%84%EC%9A%B0-%EB%B0%A9%ED%99%94%EB%B2%BD%EC%97%90-%EC%98%A4%EB%9D%BC%ED%81%B4-%EC%A0%91%EA%B7%BC-%ED%97%88%EC%9A%A9-%EC%B2%98%EB%A6%AC

[Oracle] 윈도우 방화벽에 오라클 접근 허용 처리

 

 

 

 

 

 

 

=========================================================

상세(의식의 흐름 주의)

 

 

1. 작업중 신규개발 및 테스트를 위해, 테스트용 프로젝트를 만들 필요성을 느낌.

테스트용 프로젝트에 맞춰서 테스트용 데이터베이스 생성작업도 병행하게됨.

 

 

2. 오라클 db 생성 ( 참고 : https://raccoonjy.tistory.com/20 )

 

이걸 이용하면 편하게 db 생성가능

 

 

3. 생성한 db정보를 바탕으로 oracle sql developer에서 계정을 만들려고함.

바로 위의 단계2에서 db생성할때 scott계정의 락을 해제할 수 있는데, 해제 했는지 안했는지 잊어버림..

그래서 sqlplus를 이용해서 scott 계정의 락해제 및 권한부여 작업을 진행

(캡쳐로 보여드리기 위해서 scott계정으로 대체합니다. 기존 계정은 캡쳐로 보여드릴수가 없네요)

 

자세한건 위에 요약글에 적어놓은 계정권한 부여 문단 참고. 대충 create user scott identified by tiger 이런 작업.

 

 

4. 계정생성 및 conn scott/tiger 해서 show user까지 성공하는걸 확인함.

그래서 oracle sql developer에서 db접속하려 했는데 갑자기 에러출력.

 

상태: 실패 -테스트 실패: IO 오류: The Network Adapter could not establish the connection

 

 

 

5. sqlplus가 되는데 developer는 안될수가 있나?? listener와 tns 설정파일을 확인했는데도 뭐가 문제인지 못찾음.

그래서 다른 db툴인 orange4oracle로 접속시도해봄.

 

orange4oracle에서 tns 설정부분으로 감

 

여기서 tns 설정 작성해도 되고, 위에 있는 파일경로를 통해 tns파일을 찾아가서 직접 수정해도 됨

 

 

 

 

6. 수정했는데도 안됨. 엥? 근데 invalid value error가 뜨네

 

 

 

7. sysdba로 정보바꾼 후 로그인 성공

 

 

8. 동일한 value로 orange와 sqlplus에서 로그인이 됨 -> 100% tns 설정파일 문제 혹은 developer 문제 중 하나라고 확답을 얻어냄.

developer를 다시 설치하는게 더 번거롭고 오래걸리므로 tns 설정파일 문제로 잠정적 결론을짓고 다시 확인해봄.

 

근데 아무리봐도 문제가 없어보임

 

 

 

9. 혹시 설정파일 경로문제인가 싶어서 서비스에 들어가봄

 

 

10. 오라클 에이전트가 2개 실행중인걸 확인함. 예전에 설치할때 2개 설치했던것 때문에 이렇게 됨

저기서 listner 우클릭 후 속성에 들어가서 경로를 확인했더니 역시 home1과 home2 설정파일이 다름.

양쪽 모두 제대로 수정해줌

 

 

 

11. 해결완료

 

참고로 ora-12541 tns 리스너가 없습니다 라고 출력되는 에러도 위와 동일한 프로세스로 해결 가능.

 

그리고 포트와 서비스이름(혹은 sid)에 신경써서 입력할것. 저기 틀려도 상태: 실패 -테스트 실패: IO 오류: The Network Adapter could not establish the connection 에러가 출력됨.