반응형

오라클 유용한 쿼리에 대해서 알아보도록 하자.

 

(1) DATABASE 관련 Query

 -- 현재 DATABASE의 상태정보를 조회한다.

 SELECT * FROM v$database

 

(2) TABLE SPACE 관련 Query

 

 -- 현재 TABLESPACE 의 상태 조회

 SELECT * FROM DBA_TABLESPACES;

 

 -- 특정 TABLESPACE 삭제

 a) DROP TABLESPACE :TABLESPACE_NAME INCLUDING CONTENTS CASCADE CONSTRAINTS

 b) 해당 물리 파일을 삭제한다.

 

 -- 해당 유저의 DEFAULT TABLESPACE 조회 명령어

 SELECT *   FROM user_users;

 

 -- 해당 유저의 DEFAULT TABLESPACE 변경(SYSDBA권한)

ALTER USER :USER_NAME DEFAULT TABLESPACE :TO_TABLESPACE

 

 -- 각 SEGMENT 별 TABLESPACE 정보 조회(SYSDBA권한)

SELECT OWNER,
 SEGMENT_NAME,
 SEGMENT_TYPE,
 TABLESPACE_NAME,
 BYTES
from  dba_segments
WHERE TABLESPACE_NAME IN (':TABLESPACENAME')

 

 -- TABLESPACE DATAFILE 추가(SYSDBA 권한)

ALTER TABLESPACE :TABLESPACE_NAME ADD DATAFILE 'F:\app\sungrangkong\oradata\orcl\IMS_DATA02.DBF' SIZE 30G AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED; 

 

 

(3) USER 관련 Query

 

 -- 특정사용자에게 부여된 권한 조회(SYSDBA권한)

SELECT * FROM DBA_SYS_PRIVS WHERE grantee = :USER_ID ORDER BY PRIVILEGE ASC

 

 -- 특정 사용자에게 권한 부여

GRANT :권한명  TO :USER_ID

 

 -- 권한 삭제

REVOKE :권한들(,단위구분) FROM :USER_ID

 

 

(4) 모니터링 Query

 

-- Table Space 사용중인 용량 조회

SELECT a.tablespace_name,
    a.total_gb || ' G' "Total(Gb)", 
       a.total || ' M' "Total(Mb)",
       (a.total - b.free) || ' M' "Used(Mb)",
       (nvl(b.free,0)) || ' M' "Free(Mb)",
       (round((a.total - nvl(b.free,0))*100/total,0)) || ' %' "Used(%)"
  FROM
       (SELECT tablespace_name,
           round((sum(bytes)/1024/1024/1024),0) AS total_gb ,
              round((sum(bytes)/1024/1024),0) AS total
         FROM dba_data_files
        GROUP BY tablespace_name
       ) a,
       (SELECT tablespace_name,
              round((sum(bytes)/1024/1024),0) AS free
         FROM dba_free_space
        GROUP BY tablespace_name
       ) b
 WHERE a.tablespace_name = b.tablespace_name(+)
ORDER BY a.tablespace_name;

 

     (실행결과)

     

 

 

 

-- 현재 실행중인 쿼리문과 프로세스아이디,시리얼번호,머신 알아내기

SELECT c.sql_text ,
       b.SID ,
       b.SERIAL# ,
       b.machine ,
       b.OSUSER ,
       a.spid,
       b.logon_time
  FROM v$process a,
       v$session b,
       v$sqltext c
 WHERE a.addr = b.paddr
       AND b.sql_hash_value = c.hash_value
ORDER BY c.PIECE

 

  (실행결과)

 

 

 

-- LOCK 걸린 Table 조회

 

SELECT s.username,
       s.sid,
       s.serial#,
       s.logon_time,
       DECODE(l.type, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) "LOCK LEVEL",
       o.owner,
       o.object_name,
       o.object_type
  FROM v$session s,
       v$lock l,
       dba_objects o
 WHERE s.sid = l.sid
       AND o.object_id = l.id1
       AND s.username IS NOT NULL

 

(실행결과)

 

 

 

 

 

 

 

 

- 2013.06.14 랑이씀 -

반응형

'DB > Oracle' 카테고리의 다른 글

ORA-19809 : 복구파일에 대한 한계를 초과함 에러 대처법  (0) 2013.06.13
ORACLE 11G 계정관리  (2) 2013.05.27
ORACLE 11G Database 생성 Script  (0) 2013.05.27
ORACLE 11G 소프트웨어만 설치  (0) 2013.05.27
ORACLE 11G 삭제 스샷(snapshot)  (15) 2013.05.22
반응형

오라클을 이용하여 Export / Import 시 자주 발생하는 ORA-19809 에러에 대해서 알아보도록 하자. 공식 에러는 다음과 같다.

 

ORA-19809: 복구 파일에 대한 한계를 초과함

 

IMPORT 시 SCRIPT에서 위와 같은 에러가 나온다면, 우선 에러의 원흉을 알아보도록 하자.

 

※ 우선 주의사항은 시스템의 아카이브 파일에 대한 것이니, SYS 또는 SYSTEM 계정으로 로그인 후 작성하도록 하자.

 

(1) 아카이브 파일에 대해서, 어떠한 에러인지 조회해 보도록 하자.

select dest_name,error from v$archive_dest;

 

 

 

 

 

: 위 예제를 보면, 각 archive file에 대한 Error 여부가 출력이 된다. 위 예제는 정상의

예제이고, 만약 에러가 있다면 Error 컬럼의 ROW에 “ORA-19809: 복구 파일에 대한

한계를 초과함“ 과 같이 에러가 출력이 될 것이다. 그렇다면, archive file 에 대한 에러가

있는 것으로 간주하면 된다.

 

(2) 오류 해결법

위와 같이 복구 파일에 대한 한계를 초과함 이란 에러가 발생 된다면, imp 유틸리티에서 데이터를 로드 하는 과정에서 redo log file을 switching 하여 archive log file로 만들 때 용량과다 문제로 인해 archive log file이 만들어지지 않기 때문에 발생하는 문제 이다. 이 문제를 해결하기위해서는 우선 기존 쌓여있는 archive log file을 다른곳으로 백업 후, no archive 모드로 import를 해야 한다.

 

A) 현재 Archive log 파일의 위치

SELECT * FROM v$recovery_file_dest;

 

(결과)

 

위 경로로 가서 해당 archive log file을 다른 곳으로 백업 시킨 후 삭제하면 우선은 ORA-19809에러는 나지 않을 것이다. 하지만, 원천적인 해결을 위해서는 IMP 유틸리티 가동순간에는 no archive 모드로 변경해야한다.

 

B) noarchive mode로 변경방법

 

SQL> archive log list; // 현재 모드를 조회한다.

데이터베이스 로그 모드 아카이브 모드

자동 아카이브 사용으로 설정됨

아카이브 대상 USE_DB_RECOVERY_FILE_DEST

가장 오래된 온라인 로그 순서 115

아카이브할 다음 로그 115

현재 로그 순서 117

SQL> shutdown immediate

데이터베이스가 마운트 해제되었습니다.

ORACLE 인스턴스가 종료되었습니다.

SQL> startup mount

ORACLE 인스턴스가 시작되었습니다.

 

Total System Global Area 6814535680 bytes

Fixed Size 2188688 bytes

Variable Size 3607104112 bytes

Database Buffers 3187671040 bytes

Redo Buffers 17571840 bytes

데이터베이스가 마운트되었습니다.

SQL> alter database noarchivelog; // noarchive mode로 변경

 

데이타베이스가 변경되었습니다.

 

SQL> archive log list; // 변경된 모드를 확인한다.

데이터베이스 로그 모드 아카이브 모드가 아님

자동 아카이브 사용 안함으로 설정됨

아카이브 대상 USE_DB_RECOVERY_FILE_DEST

가장 오래된 온라인 로그 순서 115

현재 로그 순서 117

SQL> alter database open;

 

데이타베이스가 변경되었습니다.

 

지금까지 ORA-19809 에러가 왜 발생하며, 해결법은 무엇인지 확인해 보았다.

 

 -2013.06.13 랑이씀-

반응형
저작자표시 비영리 변경금지

'DB > Oracle' 카테고리의 다른 글

오라클 유용한 쿼리  (1) 2013.06.14
ORACLE 11G 계정관리  (2) 2013.05.27
ORACLE 11G Database 생성 Script  (0) 2013.05.27
ORACLE 11G 소프트웨어만 설치  (0) 2013.05.27
ORACLE 11G 삭제 스샷(snapshot)  (15) 2013.05.22
반응형

Oracle 에서는 계정을 어떻게 관리하는지에 대해서 알아보도록 하자.

 

(1) 계정관리

 

a) 패스워드 파일 생성

$ orapwd file='P:\app\sungrangkong\product\11.2.0\dbhome_1\database\orapworcl' password=1234 entries=5

 

※ 패스워드 파일이란?

   : 오라클에 접속하는 방법은 크게 2가지 이다. 1번째로, 운영체제 인증방식과 2번째로 패스워드 인증방식이다.

 

- 운영체제 인증방식이란?

   : 운영체제 인증방식이란, 오라클 Dabase를 설치한 운영체제내의 계정으로 로그인 했을 때에는 별도 인증을

   거치지 않고, SYSDBA,SYSOPER 권한을 부여하는 방식이다.

 

- 패스워드 파일 인증이란?

   : 패스워드 파일에 등록된 오라클 유저에게만 인증을 하는 방식이다. 이 경우 , 오라클 listener를 동작시킬 때

   함께 인자로 넘겨주는 pfile 내부에 셋팅이 되어 있어야 한다. pfile 내부에 REMOTE_LOGIN_PASSWORDFILE

    의 값을 EXCLUSIVE로 설정해야 한다.

 

- 패스워드 파일 생성 방법은?

   : 오라클 유틸리티 중, orapwd 라는 유틸리티를 이용하여 생성한다.

 

- 패스워드 파일 삭제 방법은?

   : 간단하게 OS단에서 rm을 하면 된다.

- 패스워드 파일 수정 방법은?

   : 별도 수정방법이 있는 것이 아니라 db shutdown 상태에서 rm으로 패스워드 파일을 삭제후 새로 생성 한다.

     그리고 db를 재기동시 파일이 적용된다.

 

b) user 생성

$ orapwd file='P:\app\sungrangkong\product\11.2.0\dbhome_1\database\orapworcl' password=1234 entries=5

 

$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.1.0 Production on 월 5월 27 19:44:31 2013

 

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

 

 

다음에 접속됨:

Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

 

SQL> create user sungrangkong identified by 1234;

 

사용자가 생성되었습니다.

 

SQL> grant connect, resource to sungrangkong;

 

권한이 부여되었습니다.

 

$ sqlplus sungrangkong/1234

 

SQL*Plus: Release 11.2.0.1.0 Production on 월 5월 27 19:47:24 2013

 

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

 

 

다음에 접속됨:

Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

 

SQL> show user;

USER은 "SUNGRANGKONG"입니다

 

 

반응형
저작자표시

'DB > Oracle' 카테고리의 다른 글

오라클 유용한 쿼리  (1) 2013.06.14
ORA-19809 : 복구파일에 대한 한계를 초과함 에러 대처법  (0) 2013.06.13
ORACLE 11G Database 생성 Script  (0) 2013.05.27
ORACLE 11G 소프트웨어만 설치  (0) 2013.05.27
ORACLE 11G 삭제 스샷(snapshot)  (15) 2013.05.22
반응형

dbca를 이용하지 않고도, 오라클 Database를 구성할 수 있다. 이런 방법에 익숙해 진다면,

정말 좋은 DBA가 되지 않을까??? 

 

 

(1) 만들 SID를 설정한다.(여기서는 변수로 환경변수에 등록해둔다.)

 

$ ORACLE_SID = orcl

 

(2) 한글깨짐 방지를 위해 언어팩 설정을 한다.(locale 설정)

 

$ set NLS_LANG=AMERICAN_AMERICA.KO16KSC5601

 

(3) 오라클 Listener 설정

 

a) 오라클 SID 디렉터리 생성

 

: 오라클 홈디렉터리 이하에 SID와 같은 폴더를 생성한다.

 

 

// 홈디렉터리 이동

$ cd /cygdrive/p/app/sungrangkong/product/11.2.0/dbhome_1

 

// HOME 이하 oradata 이동

$ cd oradata

 

// SID명으로 폴더생성

$ mkdir orcl

 

 

b) pfile 생성

: 경로( 오라클 홈디렉터리/database/orcl ) 에 initSID.ora 명으로 pfile을 생성한다.

ex) SID 가 orcl인 경우 initorcl.ora 로 구성한다.

 

 

// 홈디렉터리 이동

$ cd /cygdrive/p/app/sungrangkong/product/11.2.0/dbhome_1

 

// HOME 이하 database 이동

$ cd database

 

// SID명으로 폴더생성

$ mkdir orcl

 

// SID 폴더로 이동

$ cd orcl

 

// vim 으로 pfile 생성

// 주의사항 control_files의 위치는 OS가 윈도우인 경우 윈도우 경로로 적어준다.

// 그 이유는, 추후 DOS에서 접속해도 이상없이 실행되기 위함이다.

$ vim initorcl.ora

 

db_name = orcl

db_block_size = 8192

processes = 100

 

shared_pool_size = 72M

db_cache_size = 64M

undo_management = auto

undo_tablespace = undotbs1

 

control_files = ('P:/app/sungrangkong/product/11.2.0/dbhome_1/oradata/orcl/control01.ctl',

'P:/app/sungrangkong/product/11.2.0/dbhome_1/oradata/orcl/control02.ctl')

 

remote_login_passwordfile=exclusive

 

 

c) listener file 생성

경로 : 오라클홈디렉터리/NETWORK/ADMIN

파일명 : listener.ora

 

 

// NETWORK/ADMIN 으로 이동

$cd /cygdrive/p/app/sungrangkong/product/11.2.0/dbhome_1/NETWORK/ADMIN

 

$vim listener.ora

 

LISTENER =

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=tcp)(HOST=220.149.217.141)(PORT=1521))

)

 

# SID_LIST_LISTENER는 LISTENER 에 연결하는 클라이언트에 제공할 수 있는 서비스 지정

# SID는 시스템 식별자 이며,

# ORCL 전역 데이터 베이스 이름,

# US.ORACLE.COM은 설치시에 데이터베이스에 할당된 전역 데이터베이스 도메인,

# ORCL 설치시에 데이터베이스에 할당된 인스턴스 이름,

# ORACLE_HOME은 오라클 데이터베이스가 설치된 경로 이다.

 

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=orcl)

(SID_NAME=orcl)

(ORACLE_HOME=P:/app/sungrangkong/product/11.2.0/dbhome_1)

)

)

 

# lsnrctl라는 유틸을 이용하면 리스너가 실행되는 동안 리스너의 설정을 변경할 수 있는데

# STOP,START,RELOAD,STATUS,SHOW(파라미터),SET(파라미터)등과 같은 작업을 제공하는 명령어라인 어플리케이션이다.

# 리스너의 세팅을 listener.ora에 기록할지를 오라클 넷 서비스에게 알려주는 역할을 한다.

SAVE_CONFIG_ON_STOP_LISTENER = ON

 

# 리스너의 로그파일의 위치를 지정한다.

LOG_FILE_LISTENER=lsnr.log

LOG_DIRECTORY_LISTENER = P:/app/sungrangkong/product/11.2.0/dbhome_1/NETWORK/log

 

# 오라클의 넷 컴포넌트들에 대한 부가적인 정보를 제공한다.

TRACE_FILE_LISTENER = lsnr

TRACE_DIRECTORY_LISTENER = P:/app/sungrangkong/product/11.2.0/dbhome_1/NETWORK/log

 

# 이 항목이 OFF이면 리스너에 어떤 일이 일어나도 추적파일에 정보를 기록하지 않는다.

# USER - 사용자 접속에 의해 발생한 오류에 대한 정보를 기록한다.

# ADMIN - 리스너의 설치와 설정에 관련된 문제만을 보여줄 수 있도록 추적정보를 기록한다.

# SUPPORT - 오라클 서비스지원(OSS:ORACLE SERVICE SUPPORT)을 호출할 때 이용된다.

# 이때 생성된 추적정보는 OSS로 전달되어 문제 해결에 이용될 수 있다.

TRACE_LEVEL_LISTENER = OFF

 

d) 인스턴스 생성

 

$ oradim -new -sid orcl -intpwd orcl -startmode auto -pfile /cygdrive/p/app/sungrangkong/product/11.2.0/dbhome_1/database/orcl/initorcl.ora

 

인스턴스가 생성되었습니다.

 

e) listner 시작

 

$ lsnrctl start

 

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 27-5월 -2013 13:57:31

 

Copyright (c) 1991, 2010, Oracle. All rights reserved.

 

시작 tnslsnr: 잠시만 기다리세요...

 

TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production

시스템 매개변수 파일은 P:\app\sungrangkong\product\11.2.0\dbhome_1\network\admin\listener.ora 입니다

p:\app\sungrangkong\product\11.2.0\diag\tnslsnr\sungrangkong-PC\listener\alert\log.xml (으)로 로그 메시지를 기록했습니다

리스닝이: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=220.149.217.141)(PORT=1521)))

 

(ADDRESS=(PROTOCOL=tcp)(HOST=220.149.217.141)(PORT=1521))에 연결되었습니다

리스너의 상태

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

별칭 LISTENER

버전 TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production

시작 날짜 27-5월 -2013 13:57:36

업타임 0 일 0 시간. 0 분. 9 초

트레이스 수준 off

보안 ON: Local OS Authentication

SNMP OFF리스너 매개변수 파일 P:\app\sungrangkong\product\11.2.0\dbhome_1\network\admin\listener.ora

리스너 로그 파일 p:\app\sungrangkong\product\11.2.0\diag\tnslsnr\sungrangkong-PC\listener\alert\log.xml

끝점 요약 청취 중...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=220.149.217.141)(PORT=1521)))

서비스 요약...

"orcl" 서비스는 1개의 인스턴스를 가집니다.

"orcl" 인스턴스(UNKNOWN 상태)는 이 서비스에 대해 1 처리기를 가집니다.

명령이 성공적으로 수행되었습니다

 

 

(4) Database 생성

 

a) sysdba 권한으로 로그인

 

$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.1.0 Production on 월 5월 27 13:58:58 2013

 

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

 

휴지 인스턴스에 접속되었습니다.

 

SQL>

 

b) nomount 모드로 이동

 

SQL> startup nomount

pfile=p:/app/sungrangkong/product/11.2.0/dbhome_1/database/orcl/initorcl.ora

ORACLE 인스턴스가 시작되었습니다.

 

Total System Global Area 175403008 bytes

Fixed Size 2173920 bytes

Variable Size 100664352 bytes

Database Buffers 67108864 bytes

Redo Buffers 5455872 bytes

SQL>

 

c) database 생성

 

SQL> create database orcl

logfile group 1 ('p:/app/sungrangkong/product/11.2.0/dbhome_1/oradata/orcl/redo1a.log') size 20m,

group 2 ('p:/app/sungrangkong/product/11.2.0/dbhome_1/oradata/orcl/redo2a.log') size 20m

datafile 'p:/app/sungrangkong/product/11.2.0/dbhome_1/oradata/orcl/system01.dbf' size 200m

autoextend on next 20m maxsize unlimited

sysaux datafile 'p:/app/sungrangkong/product/11.2.0/dbhome_1/oradata/orcl/sysaux01.dbf' size 200m

autoextend on next 20m maxsize unlimited

undo tablespace undotbs1 datafile 'p:/app/sungrangkong/product/11.2.0/dbhome_1/oradata/orcl/undotbs01.dbf' size 200m

autoextend on next 20m maxsize 2G

default temporary tablespace temp

tempfile 'p:/app/sungrangkong/product/11.2.0/dbhome_1/oradata/orcl/temp01.dbf' size 20m

autoextend on next 20m maxsize 2G

character set al32utf8

national character set al16utf16;

 

데이타베이스가 생성되었습니다.

 

(5) 딕셔너리 생성

 

a) 딕셔너리 생성 명렁문

 

SQL> @?/rdbms/admin/catalog.sql

뷰가 생성되었습니다.

.....

 

동의어가 생성되었습니다.

.....

 

권한이 부여되었습니다.

.....

PL/SQL 처리가 정상적으로 완료되었습니다.

 

 

TIMESTAMP

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

COMP_TIMESTAMP CATALOG 2013-05-27 14:08:18

 

SQL>

b) 딕셔너리 생성 확인

SQL> select username from dba_users;

 

USERNAME

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

SYS

SYSTEM

OUTLN

 

(6) 기본 Package 생성

 

a) 기본 Package 생성 명령어

: 해당 명령어 이후 약 5분정도 script를 실행한다. (로그도 엄청 떨어짐)

 

SQL> @?/rdbms/admin/catproc.sql

 

동의어가 생성되었습니다.

......

라이브러리가 생성되었습니다.

......

패키지가 생성되었습니다.

......

TIMESTAMP

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

COMP_TIMESTAMP CATPROC 2013-05-27 14:16:42

 

1개의 행이 선택되었습니다.

 

SQL>

SQL> SET SERVEROUTPUT OFF

......

SQL>

 

b) Packge 설치 확인

 

SQL> select username from dba_users;

 

USERNAME

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

OUTLN

SYS

SYSTEM

DIP

ORACLE_OCM

APPQOSSYS

DBSNMP

 

7 개의 행이 선택되었습니다.

 

(7) 유저 계정 비밀번호 변경

 

SQL> alter user sys identified by 1234;

 

사용자가 변경되었습니다.

 

SQL> alter user system identified by 1234;

 

사용자가 변경되었습니다.

 

SQL> alter user dbsnmp identified by 1234;

 

사용자가 변경되었습니다.

 

(8) 기타 필수 스크립트

 

: SYSTEM 계정으로 로그인해서 해야 함.

: 이건 에러가 많이 남 그래도 돌려야 함.

 

SQL> connect system/1234

SQL> @?/sqlplus/admin/pupbld.sql

 

 

 

관련된 만든 한글파일도 업로드 하겠음.

 

 

 

랑이스크립트.hwp

 

2013.05.27 랑이씀

반응형
저작자표시 비영리 변경금지

'DB > Oracle' 카테고리의 다른 글

ORA-19809 : 복구파일에 대한 한계를 초과함 에러 대처법  (0) 2013.06.13
ORACLE 11G 계정관리  (2) 2013.05.27
ORACLE 11G 소프트웨어만 설치  (0) 2013.05.27
ORACLE 11G 삭제 스샷(snapshot)  (15) 2013.05.22
ORACLE SPfile & Pfile (초기화 파라미터)  (0) 2013.05.19

+ Recent posts

티스토리툴바