--- Arup Nanda 의 사이트에 자주 들어가봅니다. DBA 로서의 장애 Case 를 대응하는 절차가 비교적 자세히 나와있더군요. 요즘 일이 많아서 빠르게 쓰다보니 오역과 중간중간 빼먹는게 많은건 이해해주시길...아래는 실제로 DMA (direct memory access)를 사용해서 처리한 사례더군요..

Diagnosing Library Cache Latch Contention: A Real Case Study

어느날 DW 서버가 갑자기 다운됐다. 그래서 데이타베이스를 올리고 다시 구동했지만 모든 접속을 시도하면
hang 이 걸리는 것이였다. 따라서 접속은 실패하고 DBA는 접속된 세션들이 구동되고 있는지 어떤지를 확인할 수 조차도
없었다. DBA 는 WAIT 이벤트를 체크했다 (그러나 로긴조차도 안되기 때문에 실행할수 없었다)
흥미롭게도 CPU는 70% 정도의 수준이였고 이는 낮시간대의 일반적인 수치이다. 그리고 I/O또한 약 90%로서 이또한
일반적인 수치였다.

따라서 전형적인 방법인 system 관리자에게 보고하고 재부팅을 하는 방법을 사용했다. 재부팅은 30분정도
소요되었고 그 후로 10여분간은 모든것이 정상적으로 보였다. 그러나 얼마되지 않아 아까와같은 똑같은 문제에 부딪쳤다- 데이타베이스가 먹통이 되어버린 것이였다.

이것 때문에 DBA가 나에게 도움을 요청하였다. 이 블로그에서는 내가 이후의 30분동안에 어떻게 수행했고 문제를
해결했는지에 대해서 기술해보도록 하겠다.

증상

(1) 데이타베이스 접속 Hanging
(2) SQL*PLUS AS SYSDBA 로 접속해도 동일한 HANGING 현상: 증상을 확인할 수도 없는 상태
(3) 시스템은 아무때나 리부팅할 수 없는 상태

Action

여기서 접속이 불가능할 때 데이타베이스 인스턴스에서 바로 써먹을 수 있는 꼼수가 존재한다.
대부분의 사람들은 SQL*Plus 에서 "prelim" 이라고 불리는 옵션을 잘알지 못한다. 이 옵션은 세션을 열지 않고
SGA에 바로 접속한수 있는 옵션이다. (10g 이상에서만 가능) 

(1) 먼저 SQL*plus 를 실행시키고 아래의 명령문을 실행했다.

$ sqlplus -prelim / AS SYSDBA
SQL>

명심하라. "Oracle Database 10.2.0.3 에 접속" 과 다르다. 지금 보이는 SQL> 프롬프트는 실제로는
데이타베이스에 접속한 상태가 아니다.

(2) 그다음 SGA 를 분석하기 위한 "oradebug" 를 사용하였다.

SQL> oradebug setmypid
SQL> oradebug hanganalyze 12

이 명령은 USER_DUMP_DEST 에 trace파일을 생성한다. 이 파일을 가장 최근에 생겨났기 때문에
쉽게 찾을 수 있다. 심지어는 내가 파일을 찾지 못해도 process ID를 사용해서 파일을 찾을 수 있다.
내가 찾은 파일은 프로세스ID 가 13392인 crmprd1_ora_13392.trc 였다.

(3) 파일을 조사하니 다음과 같았다.

*** 2008-08-23 01:21:44.200
==============
HANG ANALYSIS:
==============
Found 163 objects waiting for
<0/226/17/0x1502dab8/16108/no>
Open chains found:
Chain 1 : :
<0/226/17/0x1502dab8/16108/no>
<0/146/1/0x1503e898/19923/latch:>

이 파일을 많은 것을 말해준다. SID 146 에 Serial# 1 이 library cache latch 를 대기하고 있는 것을 보여준다.(맨마지막줄)
그리고 blocking 세션은 SID 226 Serial# 17 로 나와있다. 

나는 일단 이 OS process ID 인 16108 과 19923 을 기록해두었다.

(4) 다음으로 위의 두개의 OS PID 명으로 되어 있는 TRACE 파일을 체크했다.

crmprd1_ora_16108.trc
crmprd1_ora_19923.trc

(5) 먼저 BLOCKER인 첫번째 파일을 열었다. 다음 몇줄의 예이다.

*** 2008-08-23 01:08:18.840
*** SERVICE NAME:(SYS$USERS) 2008-08-23 01:08:18.781
*** SESSION ID:(226.17) 2008-08-23 01:08:18.781
LIBRARY OBJECT HANDLE: handle=c0000008dc703810 mtx=c0000008dc703940(8000) cdp=32737
name=UPDATE DW_ETL.FRRS_PROFILER SET CONSUMER_LINK = :"SYS_B_0", ADDRESS_LINK = :"SYS_B_1", ADDRESS_MATCH = :"SYS_B_2", PROC
ESSED=:"SYS_B_3" WHERE RNUM = :"SYS_B_4"
hash=a029fce7bb89655493e7e51a544592a4 timestamp=08-23-2008 00:10:23
namespace=CRSR flags=RON/KGHP/TIM/OBS/PN0/MED/KST/DBN/MTX/[504100d0]
kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch#=10 hpc=0058 hlc=0058
lwt=c0000008dc7038b8[c0000008dc7038b8,c0000008dc7038b8] ltm=c0000008dc7038c8[c0000008dc7038c8,c0000008dc7038c8]
pwt=c0000008dc703880[c0000008dc703880,c0000008dc703880] ptm=c0000008dc703890[c0000008dc703890,c0000008dc703890]
ref=c0000008dc7038e8[c0000008dc7038e8,c0000008dc7038e8] lnd=c0000008dc703900[c0000008dc703900,c0000008dc703900]
LOCK OWNERS:
lock user session count mode flags
---------------- ---------------- ---------------- ----- ---- ------------------------
c0000008d079f1b8 c0000006151744d8 c0000006151744d8 16 N [00]
c0000008d4e90c40 c0000006151bcb58 c0000006151bcb58 16 N [00]
c0000008d0812c40 c0000008151a0438 c0000008151a0438 16 N [00]

 
(6) 이것은 디버깅을 위해 보물과 같았다. 첫번째에 SID와 Serial#(226,17) 를 확인할수 있다.
이를 사용해서 정확한 SQL문장을 볼수 있다. 또한 락에 대한 전체적인 상황을 볼 수 있다. 락의 자세한 사항은 신경쓰지 않아도 되지만 SID 226 이 전체 세션의 대기를 유발시키는 것이라는 충분한 정보를 제공해주었다.

(7) 나의 조사는 여기서 그치지 않고  이 대기를 유발하는 세션을 찾기를 시도했다. 따라서 나는 파일의 "PROCESS STATE" 이라는 섹션을 조사했다. 다음은 이 파일의 일부분이다.

PROCESS STATE
-------------
Process global information:
process: c00000081502dab8, call: c000000817167890, xact: 0000000000000000, curses: c00000081519ef88, usrses: c000000815
19ef88
----------------------------------------
SO: c00000081502dab8, type: 2, owner: 0000000000000000, flag: INIT/-/-/0x00
(process) Oracle pid=370, calls cur/top: c000000817167890/c000000817167890, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 115 0 4
last post received-location: kslpsr
last process to post me: c000000615002038 1 6
last post sent: 0 0 24
last post sent-location: ksasnd
last process posted by me: c000000615002038 1 6
(latch info) wait_event=0 bits=20
holding (efd=4) c0000008d7b69598 Child library cache level=5 child#=10
Location from where latch is held: kglhdgc: child:: latch
Context saved from call: 13
state=busy, wlstate=free
waiters [orapid (seconds since: put on list, posted, alive check)]:
291 (197, 1219468295, 197)
279 (197, 1219468295, 197)
374 (197, 1219468295, 197)
267 (197, 1219468295, 197)
372 (197, 1219468295, 197)
... several lines sniped ...
307 (15, 1219468295, 15)
181 (6, 1219468295, 6)
waiter count=58
Process Group: DEFAULT, pseudo proc: c0000008e03150d8
O/S info: user: oracrmp, term: UNKNOWN, ospid: 16108
OSD pid info: Unix process pid: 16108, image: oracle@sdwhpdb1

(8) 이파일은 내가 알기를 원하는 것을 모두 말해준다. 여기에 SID 226 에 의해서 발생하는 CACHE LATCH
로 인해서 대기하는 58 session들이 있다. 여기서 OS PROCESS ID 와 BLOCKING 세션의 SQL 문장을 알 수 있다.

(9) 나는 application 사용자가 어떠한 것을 실행했는지를 조사해봤더니 사용자는 loop를 돌면서 처리하는
update 문장을 실행시킨 것이였다. 그리고 그게 다가 아니라 다른 8개의 thread 에서 실행을 하였다.(역: 아마도 화면상에서 처리 가 되지 않으니 화면을 새로고쳐서 계속해서 8번을 처리 버튼을 누른것으로 생각됨)
의심할 여지가 없이 library cache latch 경합에 걸렸다. 모든 세션은 각각의 덤프 정보를 남겼다.
그리고 나는 같은 문장을 실행한 파일을 디렉토리에서 조사해보기로 했다.

$ grep “UPDATE DW_ETL” *.trc

(10) 나는 9개 이상의 세션(프로세스) 파일을 찾았다. 이중 한개의 파일의 일부분이다.

350 (167, 1219470122, 167)
197 (167, 1219470122, 167)
waiter count=185
Process Group: DEFAULT, pseudo proc: c0000008e03150d8
O/S info: user: oracrmp, term: UNKNOWN, ospid: 16114

이 프로세스 한개가 185개 waiter 를 가졌다!!!

$ kill -9

(12) 위의 명령으로 몇개의 프로세스를 죽인 후에야 데이타베이스는 응답하기 시작했다. 모든 프로세스를 죽인 후에는 데이타베이스 wait event 가 완벽히 정상적으로 돌아왔다.

(1) Hang 이라고 생각되면 너무 그것에 대해 불안해하지 마라. 세션은 언제나 어떤것을 대기한다. 드물게 행을 만날 뿐이다.

v$session (10g) 이나  v$session_wait 의 EVENT 컬럼을 조회해서 대기하는 것이 무엇인지를 먼저 체크하라.

(2) 데이타베이스에 로긴하지 못해 정보를 얻을 수 없을 때는 oradebug 명령을 사용한다.

(3) oradebug 를 사용할때 SQL*Plus 를 이용한다. 로긴하지 못할때 "sqlplus -prelim " 로 SQL prompt 를 얻을 수 있을 것이다.

(4) oradebug setmypid  이용해서 oradebug 세션을 시작하고 oradebug hanganalyze  로 모든 hang 과 관련되어 있는

문제에 대한 덤프를 생성한다.

(5) oradebug help 를 사용해서 oradebug 커맨드의 모든것을 볼 수 있다.

Oradebug 사용법
- 적절한 권한을 가진 DB USER 로 sqlplus 로 접속
- 반드시 덤프할 오라클 프로세스를 지정한 후 사용
- SYNTAX : SQL>oradebug command <option>

일반 유저도 Try

SQL> show user
USER is "SCOTT"
SQL> oradebug setmypid
ORA-01031: insufficient privileges

SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> oradebug setmypid
Statement processed.

==> 자신의 process ID 지정 해서 dump

일반 유저를 찾아서 지정 해보기

보통은[AIX 환경] ps aux | sort -k3 으로 cpu 과도 사용 Unix process ID 를 찾은 후

지정 해서 dump 를 떨군 후 분석을 하면 될듯

## Scott User 의 Process ID 찾기
SQL> select username, sid, serial#,PADDR from v$session where username ='SCOTT';

USERNAME                              SID    SERIAL# PADDR
------------------------------ ---------- ---------- --------
SCOTT                                  28         54 46CB5768

SQL> select * from v$process where addr = '46CB5768';

ADDR            PID SPID      USERNAME           SERIAL#
-------- ---------- --------- --------------- ----------
TERMINAL                       PROGRAM
------------------------------ ------------------------------------------------
TRACEID
--------------------------------------------------------------------------------
B LATCHWAI LATCHSPI
- -------- --------
46CB5768         29 283314    oracle                   2
pts/8                          oracle@seldw (TNS V1-V3)

## Unix 환경에서 파악

[CRAFT]seldw:/app/oracle/tg> ps -ef| grep 283314
  oracle 282448 290026   0 10:45:26  pts/7  0:00 grep 283314
  oracle 283314 284036   0 10:42:54      -  0:00 oracleCRAFT (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

SQL> oradebug unlimit
Statement processed.
==> Dump 화일 무제한으로 설정
SQL> oradebug setospid 283314
Oracle pid: 29, Unix process pid: 283314, image: oracle@seldw (TNS V1-V3)

==> Scott Process ID 를 지정 해서 dump
SQL> oradebug tracefile_name
Statement processed.
==> dump 화일명 체크

==> 나오지 않는다.

SQL> oradebug dump errorstack 3
Statement processed.
==> dump 화일에 실제 Write 되도록 command 를 날리기

SQL> oradebug tracefile_name
/app/oracle/admin/CRAFT/udump/ora_283314_craft.trc
==> 이제 화일명이 보임

# Event 로 Trace 걸기
SQL> oradebug setospid 283314
Oracle pid: 29, Unix process pid: 283314, image: oracle@seldw (TNS V1-V3)

# 10046 Event 에 대해서 Trace 생성도록 설정 하기
SQL> oradebug event 10046 trace name context forever, level 12
Statement processed.
SQL> oradebug event 10046 trace name context off
Statement processed.
SQL> oradebug tracefile_name
/app/oracle/admin/CRAFT/udump/ora_283314_craft.trc

CASE 1 : 특정 프로세스가 SPIN 또는 HANG
SQL> oradebug dump errorstack 3 .. 3분단위 3번수행
SQL> oradebug dump processstate 10 .. 비교1
SQL> oradebug event 942 errorstack 10 .. 비교2

CASE 1 은 특정 프로세스가 SPIN 또는 HANG 으로 보이는 경우입니다.
(1) Oradebug setospid 해당 프로세스를 덤프대상으로 지정하고
(2) Oradebug dump errorstack 3 으로 ERRORSTACK 을 2-3번 떠서
(3) CALL STACK 부분이 변하고 있는지 비교해봅니다.
변하고 있으면 SPIN 이고, 변하지 않고 있으면 HANG 이라고 결론 내릴 수 있습니다.
ERRORSTACK LEVEL 3 에 PROCESSSTATE DUMP 가 포함되므로
PROCESSSTATE DUMP 를 별도로 수행할 필요가 없습니다.
EVENT Command 에서 ERRORSTACK 를 설정할 때와 비교해보면, EVENT Command 는 해당 에러가 발생하는
시점에 에러스택이 생성되는 것이고, DUMP Command 는 Oradebug Command 를 수행하자마자 에러스택이
생성됩니다.

CASE 2 : 데이터베이스 SPIN 또는 HANG
SQL> oradebug dump systemstate 10 .. 3분간격 3번수행
= alter session set events 'immediate trace name SYSTEMSTATE level 10';

케이스 두번째, 드디어 SYSTEMSTATE DUMP 입니다.
이 Command 는 아마도 oradebug 에서 가장 많이 사용되는 명령어로
alter session set events 'immediate trace name SYSTEMSTATE level 10'; 과 같습니다.
보시다시피 Oradebug Command 가 훨씬 간단하고 Rule 만 알면 외울 필요도 없습니다.
인스턴스 HANG 시 3분 간격으로 3번을 수행한 결과가 있어야 Slow Performance 인지,

진짜 HANG 이였는지 판단할 수 있습니다.

SQL> oradebug dump systemstate 10
ORA-00074: no process has been specified
SQL> alter session set events 'immediate trace name systemstate level 10';

Session altered.

CASE 3 : 프로세스 메모리가 비정상 증가하는 경우
SQL> oradebug dump heapdump 5 .. PGA+UGA

CASE 4 : SGA 부족으로 ORA-4031 가 발생하는 경우
SQL> oradebug dump heapdump 2 .. SGA
event="4031 trace name HEAPDUMP level 2" in initSID.ora

CASE 6 : 리커버리시 데이터파일 상태 불일치 에러시
SQL> oradebug dump controlf 10
SQL> oradebug dump file_hdrs 10
==>테스트시  임의의 프로세스 지정을 해야 trace 화일이 생성 된다.

SQL> oradebug dump controlf 10
ORA-00074: no process has been specified
SQL> oradebug setospid 283314
Oracle pid: 29, Unix process pid: 283314, image: oracle@seldw (TNS V1-V3)
SQL> oradebug dump controlf 10
Statement processed.
SQL> oradebug tracefile_name
/app/oracle/admin/CRAFT/udump/ora_283314_craft.trc
SQL> oradebug dump file_hdrs 10
Statement processed.
SQL> exit

SQL> oradebug hanganalyze 3
Hang Analysis in /app/oracle/admin/CRAFT/udump/ora_89222_craft.trc
프로세스 또는 인스턴스 HANG 진단 및 분석시 유용
HANGANALYZE [level]
1-2 Only HANGANALYZE output, no process dump
3 Level 2 + HANG 으로 추정되는 프로세스 덤프
4 Level 3 + WAIT CHAIN 의 BLOCKER 프로세스
5 Level 4 + WAIT CHAIN 의 모든 프로세스
10 모든 프로세스 덤프

SQL> oradebug hanganalyze 3 .. 권장레벨, 또는 1
Hang Analysis in /home/ora920/ora920_1190.trc
HANGANALYZE TRACEFILE SECTIONS 설명
 CYCLES : Deadlock 관계 세션들의 CHAIN
 BLOCKER OF MANY SESSIONS : 10개 이상의 세션을 blocking 하는 BLOCKER 제시
 OPEN CHAINS : 1개 이상의 타 세션들을 blocking 하는 세션이 포함된 WAIT CHAIN
 OTHER CHAINS : OPEN CHAIN 의 세션들과 간접적으로 관련있는 프로세스 리스트

EXTRA INFORMATION : 덤프 레벨에 따른 프로세스 Errorstack 등의 추가 정보
STATE OF NODES : 모든 세션들 DEPENDENCY GRAPH
  IN_HANG - HANG
  IGN - IGNORE
  LEAF - A waiting leaf node
  LEAF_NW - A running leaf node
  NLEAF - STUCK
세션 STATE 설명입니다.
IN_HANG : 심각한 상태로, 이 상태의 세션은 DEADLOCK 가능성이 있습니다 .
IGN and IGN_DMP : IDLE 상태이므로 무시하셔도 됩니다.
LEAF and LEAF_NW : 이 상태로 Wait Chain 의 가장 앞에 있으면,

                             바로 이 세션이 Blocker 세션입니다.
NLEAF : STUCK 세션으로, 다른 세션이 리소스를 잡고 안 놓아 주는 상태

           로 Performance 이슈일가능성이 높습니다.
 
DB HANG 이것만은 알아두세요!!!
데이터베이스 HANG : DB 연결될 때
SQL> oradebug setmypid

자신의 Process ID 지정 아마도, trace file 생성을 위해서 임의로 지정하는 듯
SQL> oradebug unlimit

Trace file 무한으로 설정
SQL> oradebug hanganalyze 1 

빨리 Blocker 찾으세요

Trace 화일을 통해서 문제의 Process ID 를 서치

심도 있게 더 깊이 분석시 아마도 setospid를 통해서 Blocker ID 를 찾은수

다시 trace 를 시도 하면 될듯
SQL> oradebug dump systemstate 10 ..

다른세션에서 3분3번

데이터베이스 HANG : DB 연결안 될 때
$ dbx .a PID $ORACLE_HOME/bin/oracle .. Oracle PID
dbx) call ksudss(10) or print ksudss(10)
dbx) detach

RAC에서 다른 Instance와의 연관된 내용까지 분석하려면 다음과 같은 명령문을 사용해야 한다.

SQL> oradebug setinst all
SQL> oradebug --g def hanganalyze 1

이런 경우에 시도해 볼 수 있는 것이 ErrorStack 덤프입니다. ErrorStack 덤프를 진단 이벤트와 함께 사용하면 에러를 일으키는 SQL 문장이 트레이스 파일에 기록되도록 할 수 있습니다.

간단한 예를 설명해 보겠습니다. 우선 작은 크기(10m)의 테이블스페이를 만듭니다.

• http://tech.e2sn.com/oracle/troubleshooting/how-to-read-errorstack-output

출처 : http://ukja.tistory.com/307

복구 관리자 (recovery manager:rman)는 데이터베이스의 백업과 복구에 관련된 정보를 저장하고 필요한 경우 백업과 복구 절차를 수행해 주는 유틸리티이다. 데이터베이스, 테이블스페이스, 데이터파일, 컨트롤 파일, 아카이브 파일별로 백업할 수 있으며, 백업 시 사용되지 않는 블록을 제외하고 백업할 수 도 있다. 또한 백업 시 손상된 블록의 사용 가능 여부를 확인해 주기도 한다.

rman 툴은 자체적인 스크립트 해석 인터프리터를 가진 명령언어 인터프리터(command language interpreter)이므로 입력된 명령을 해석하여 실행하게 된다. 예를 들어, backup, restore, copy, recover와 같은 명령을 rman 툴에서 실행할 수 있는 것이다.

rman의 형식은 다음과 같다.

RMAN  [TARGET connectStringSpec
      ¦ { CATALOG connectStringSpec }
      ¦ LOG ['] filename ['] [APPEND ]
      ......
      ]...
      

connectStringSpec::=
  ['] [userid] [/ [password]] [@net_service_name] ['] 

【예제】 ☜ rman 시각과 끝
$ rman

RMAN> exit

$ 

그 차이점은 RMAN은 SYSDBA 권한을 가지고 타킷과 보조 데이터베이스에 접속해야 하는데 AS SYSDBA 키워드는 사용하지 않아도 묵시적으로 가진 것으로 간주하기 때문에 의도적으로 표시하지 않아도 된다는 점만 다르다.

【예제】☜ target DB에 관리자로 접속 
$ rman

RMAN> connect target /

connected to target database: ORCL (DBID=1204356616)

RMAN> exit

Recovery Manager complete.
$

【예제】☜ log 파일을 지정하면서 접속 
$ rman TARGET / LOG /tmp/msglog.log APPEND
RMAN> exit
$

【예제】

RMAN> CONNECT TARGET
RMAN> BACKUP DATABASE;

RMAN> BACKUP DATABASE
2>  INCLUDE CURRENT
3>  CONTROLFILE
4>  ;

RMAN이 백업과 복구를 실행하는 구성을 미리 작성한 디폴트 configure가 준비되어 적용된다.
configure란 백업장치를 지정하고, 백업 장치에 접속(이를 channel이라 부름)의 구성을 설정하는 것이다.
현재 작성된 configure를 다음과 같이 확인할 수 있다.

$ rman TARGET /
RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BZIP2'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/export/home/oracle/app/oracle/product/110.1/dbs/snapcf_orcl.f'; # default

RMAN> 

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # backup goes to disk
CONFIGURE DEVICE TYPE sbt PARALLELISM 2; # 2 channels used in in channel
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/disk1/%U' # 1st channel to disk1
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '/disk2/%U' # 2nd channel to disk2
BACKUP DATABASE; # backup - 1st channel goes to disk1 and 2nd to disk2

BACKUP
 FORMAT 'AL_%d/%t/%s/%p'
 ARCHIVELOG LIKE '%arc_dest%';

BACKUP
 TAG 'weekly_full_db-bkup'
 DATABASE MAXSETSIZE 10M;

1) RMAN을 시작하고 타킷 DB에 접속한다.
$ rman TARGET /

2) BACKUP DATABASE 명령을 실행한다.
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

데이터베이스는 반드시 mount 상태이어야 한다.

1) RMAN을 시작하고 타킷 DB에 접속한다.
$ rman TARGET /

2) shutdown하고 mount한다.
RMAN> SHUTDOWN IMMEDIATE
RMAN> STARTUP FORCE DBA;
RMAN> SHUTDOWN IMMEDIATE
RMAN> STARTUP MOUNT;

3) BACKUP DATABASE를 실행한다.(아래 둘 중 하나)
RMAN> BACKUP DATABASE;
RMAN> BACKUP AS COPY DATABASE;

4) 데이터베이스를 open한다.
RMAN> ALTER DATABASE OPEN;

     RECOVER COPY OF DATABASE
       WITH TAG 'incr_update';
     BACKUP
       INCREMENTAL LEVEL 1
       FOR RECOVER OF COPY WITH TAG 'incr_update'
       DATABASE;

   BACKUP VALIDATE CHECK LOGICAL
     DATABASE ARCHIVELOG ALL;

    #my_commnad_file.txt
    BACKUP DATABASE PLUS ARCHIVELOG;
    LIST BACKUP;
    EXIT;

Starting backup at 04-JAN-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 04-JAN-10
channel ORA_DISK_1: finished piece 1 at 04-JAN-10
piece handle=/export/home/oracle/flash_recovery_area/ORCL/backupset/2010_01_04/o1_mf_nnsnf_TAG20100104T130430_5n2t6gt7_.bkp tag=TAG20100104T130430 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 04-JAN-10

RMAN>

SQL> select name,scn, time, database_incarnation#,
  2  guarantee_flashback_database
  3  from v$restore_point
  4  where guarantee_flashback_database='YES';

     RUN 
        {
         SET NEWNAME FOR DATAFILE '/disk1/oradata/prod/users01.dbf'
           TO '/disk2/users01.dbf';
         RESTORE TABLESPACE users;
         SWITCH DATAFILE ALL;
         RECOVER TABLESPACE users;
        }

출처 : http://radiocom.kunsan.ac.kr/lecture/oracle/backup_restore/rman.html

1. 오라클 10g 의 신기능 Data Pump 의 소개

1) 개요

DB에 있는 데이터를 운영체제의 파일시스템으로 내보내는 과정을 export 라 하고, 반대로 파일 시스템을 DataBase로 들여오는 것을 Import 라고 한다. 기존의 방식을 Export/Import 라고 하고

오라클 10g에서는 이를 Data Pump 라고 통칭한다.

오라클 Data Pump는 export/import에 대한 강력한 기능들을 추가한 Utility 이다.

[ Data Pump 사용시  주의사항]

export/import 와 Data Pump는 서로 호화되지 않습니다.

즉 Export 유틸리티를 이용하여 백업 받은 파일은 Data Pump를 통해 Import 할 수 없으며, 마찬가지로

Data Pump 를 통해 export 된 데이터는 Import 유틸리티를 통해 Import 할 수 없습니다.

2) Data Pump export/Import 의 이점

■ JOB 콘트롤 기능 : Interactive mode를 통하여 Data Pump작업을 통제 할 수 있습니다. 작업을

                                중단시키고 재 시작할 수 있으며 동적으로 dump file을 할당 할 수 있습니다.

                                에러가 나더라도 작업이 중지될 뿐 언제든지 원인을 수정하고 재수행 할 수

                                있습니다.

■ 병렬수행지원 : Parallel 파라미터를 이용하여 프로세스의 Data Pump 작업의 프로세스를 병렬화

                          할 수 있습니다. 병렬화 된 프로세스는 여러개의 데이터 파일에 각각 데이터를

                          쓰거나 여러개의 데이터 파일로 부터 데이터를 읽어 데이터베이스에 저장합니다.

                          병렬수행이 가능함으로 이전 보다 훨씬 강력한 기능을 제공합니다.

■ 작업에 필요한 디스크 공간을 미리 예상 : Estimate 파라미터를 이용하여 작업 시작전에 필요한

                          물리적인 공간을 미리 예측 할 수 있다.

■ 원격지 수행 : DB Link 를 통하여 원격지 데이터에 대한 Data Pump export/import 를 수행 할 수

                      있다

■ Remapping 지원 : 유저 스키마, 테이블스페이스, 데이터파일 등과 같은 정보들이 Data Pump

                                export/import 시에 변경 할 수 있습니다. 이러한 기능은 데이터 마이그레이션

                                시에 보다 많은 유연성을 제공하는데 큰 역활을 합니다

3) Data Access 방법

■ Direct-path : Direct I/O를 사용하여 OS영역의 메모리를 사용하지 않고 데이터 파일에 direct로

                        쓰게 되는 방법입니다. 메모리 사용이 적고 속도가 빠르며, 데이터 컨버전에 시간이

                        걸리지 않습니다.

[ Direct-path 가 되지 않는 경우 ]

- 클러스터 테이블인 경우

- 테이블에 활성화된 트리거가 존재할 경우

- 글로벌 인덱스를 가진 테이블이 하나의 파티션에 존재 할 경우

- LOB컬럼에 있는 도메인 인덱스

- insert 모드에서 fine_grained access control 이 enable인 경우

- BFILE을 가진 테이블인 경우

■ External Tables : 메타 데이터를 데이터베이스 내에 저장하고 데이터는 파일시스템 존재하게

                       만들어 데이터를 저장하는 방법이며 대용량 데이터를 export/import 할때

                       사용합니다.

[ External Table이란? ]

- Create TABLE ~~ ORGANIZATION EXTERNAL 문을 통해 만들어진 테이블

- 실질적인 데이터베이스 내에 존재하는 것이 아니라 물리적 디스크 공간에 논리적 공간을 할당 받아 데이터를 저장하며, 파일 형태로 존재합니다.

- 저장되는 데이터는 READ ONLY 데이터이며 인덱스를 생성할 수 없습니다.

- DML 작업을 수행 할 수 없습니다.

- META-DATA in DATABASE, DATA in OS라고 압축 설명 할 수있습니다.

3) Data Pump 의 권한설정

- 시스템에 설정된 EXP_FULL_DATABASE, IMP_FULL_DATABASE 롤을 부여함으로써 가능함

   (1) 사용자 생성 : create 문을 사용하여 사용자를 생성합니다. 패스워드는 imsi00 으로 하며,

        default tablespace는 USERS 테이블스페이스로 합니다.

        create user zoom identified by imsi00

        default tablespace users

        temporary tablespace temp

        /

   (2) 권한부여 : grant 문을 이용하여 zoom 유저에 접속(connect) 과 자원사용(resource) 에

        대한 권한을 부여한다

        grant connect, resource to zoom;

   (3) 모든 테이블에 대한 select 권한부여

        grant select any table to zoom;

   (4) zoom유저에 EXP_FULL_DATABASE, IMP_FULL_DATABASE 권한 부여

        grant EXP_FULL_DATABASE, IMP_FULL_DATABASE to zoom;

4) Data Pump 파일 오브젝트

4-1) Data Pump 가 사용하는 파일의 종류

■ Dump File : 테이블로 부터 데이터 또는 메터 데이터를 로드하여 저장된 파일

■ Log File : Data Pump 작업 중에 발생하는 메시지나 결과를 기록하는 파일

■ SQL File : Data Pump는 SQLFILE 이라는 옵션을 사용합니다. 이옵션을 사용 할 경우

                  Data Pump Import 작업이 수행되는 동안 DDL문을 수행 할 수있게 해줍니다

4-2) Data Pump 디렉토리 오브젝트

Data Pump는 사용자별 디렉토리 접근 권한을 설정할 수 있다

디렉토리 권한설정

(1) 사용중인 디렉토리 오브젝토 조회

      SQL> select * from dba_directories;

(2) 디렉토리 오브젝트 추가

      SQL> create directory datapump_dir1 as '/temporary/ora_tmp';

(3) 디렉토리 오브젝트 권한추가(zoom 유저에게 datapump_dir1 디렉토리 read, write 권한부여)

      SQL> grant read, write on directory datapump_dir1 to zoom;

(4) Export Pump 실행

     $expdp zoom/imsi00 directory=datapump_dir1 Tables=EMP dumpfile=zoom_dump01.dmp

* Data Dump 시 마다 디렉토리 오브젝트를 추가하지 않고 묵시적으로 추가

$DATA_DUMP_DIR=datapump_dir1;

$export DATA_DUMP_DIR;

2. Data Pump Export

1) Data Pump Export 사용하기

■ Command-line 이용하기

   $expdp zoom/imsi00 directory=datapump_dir1 Tables=EMP dumpfile=zoom_dump01.dmp

    - 비교적 작은 수의 옵션들이 사용되거나 간단한 구문일 때 사용합니다

    - 복잡하고 옵션이 많게 되면 수정하거나 잘못 타이핑 할때 시간이 많이 걸리게 됩니다

■ 파라미터 파일 이용하기

    zoom.par 파일을 생성하여 다음과 같이 설정합니다

    SCHEMAS = SCOTT

    DIRECTORY=datapump_dir

    DUMPFILE=zoom01.dmp

    LOGFILE=zoom_dump.log

    $expdp zoom/imsi00 PARFILE=zoom.par

2) Data Pump Export 모드

■ Full Export 모드 : Full 파라미터를 사용합니다. 데이터베이스 전체를 export 받을 수 있습니다.

                            한가지 주의 할 점은 EXPORT_FULL_DATABASE 롤이 Full Export 받고자 하는

                            사용자에게 부여되어 있어야 한다는 점입니다

■ 스키마 모드 : Schemas 파라미터를 사용합니다.

                           하나의 유저가 소유하고 있는 데이터 및 오브젝트 전체를 export 받고자 할때

                          사용할 수있는 모드입니다.

■ 테이블스페이스 모드 : TableSpace 파라메터를 사용합니다. 하나 이상의 테이블스페이스에

                           대해 해당 테이블스페이스에 속한 모든 테이블을 받을 수 있습니다.

                           만약 TRANSPORT_TABLESPACES 파라미터를 이용한다면, 테이블 뿐 아니라

                           테이블스페이스의 메터데이터 까지 export 받게 되어 다른 서버에 dump 파일을

                           카피 한 후 import 하게 되면 테이블스페이스 및 테이블이 자동으로 생성됩니다.

■ 테이블 모드 : TABLES 파라미터를 사용합니다.

                       하나 이상의 테이블을 export 받을 때 사용합니다

3) Data Pump Export 파라미터

■ 파일 및 디렉토리 관련 파라미터

- DIRECTORY : 디렉토리 오브젝트를 참조하는 DIRECTORY 파라미터를 이용하여 덤프파일의

                      위치 및 로그파일의 위치를 지정 할 수 있습니다.

                      DIRECTORY=directory_object_name 형식으로 사용 할 수있습니다.

- DUMPFILE : Export를 받아 파일시스템에 저장될 덤프파일의 이름을 지정하는 파라미터이빈다.

                    파라메터를 사용할때 다음을 기억하시고 사용하시면 됩니다.

                    + %U 를 사용하여  여러개의 덤프 파일을 구분 할 수 있습니다

                       DUMPFILE=ZOOM_DUMO_%U.DMP라고 파라메터를 정의합니다.  만약 덤프

                       파일이 10개가 생성된다고 가정하면 ZOOM_DUMO_01.dmp 부터

                       ZOOM_DUMO_10.dmp 까지 %U 부분이 자동 증가하여 파일을 구분하여 줍니다.

                       %U의 범위는 01 ~ 99 까지 입니다

                   + ',' 를 이용하여 여러개의 파일명을 구분할 수 있습니다.

                      예를들어 다음과 같이

                      DUMPFILE=ZOOM_DUMO_01.dmp, ZOOM_DUMO_02.dmp,ZOOM_DUMO_03.dmp

                   + 만약 DUMPFILE 파라메터를 지정하지 않는다면 expdat.dmp 라는 파일명으로

                      오라클이 자동 선언하게 됩니다.                 

- FILESIZE  : export 받는 1개 파일의 최대크기를 지정하는 파라미터 입니다.

                   만약 export 받을 총 데이터량이 10GB이고 FILESIZE를 1GB로 지정하였다면 1GB

                   크기의 dump file이 10개 만들어 지게 됩니다.

                   FILESIZE=N [ BYTES | KILOBYTES | MEGABYTES | GIGABYTES ] 입니다.

- PARFILE : 파일에 파라메터들을 저장해 두고 Data Pump를 이용할때 마다 참조하여 작업을

                 수행하고 싶을때 PARFILE 파라메터를 사용할 수 있습니다

                  PARFILE=filename.par 형식으로 사용할 수 있으며, 파일 확장자는 아무런 영향을

                  미치지 않습니다

- LOGFILE & NOLOGFILE : 로그 파일명을 지정하는 파라메터 입니다

                  LOGFILE=logfile_name 형식으로 사용 하시면 됩니다. 파라미터를 설정하지 않는다면

                  export.log 라는 파일명으로 로그가 자동으로 실행한 위치의 디렉토리에 남게 됩니다.

                  로그파일을 남기고 싶지 않을때는 NOLOGFILE 파라미터를 사용하시면 됩니다.

- COMPRESSION : 오라클에서 EXPORT 시에 메타데이터는 압축을 하여 파일에 저장하게 됩니다.

                  COMPRESSION 파라메터를 사용하지 않을 경우에는 덤프파일 내에 메타데이터가

                  압축되어 보관됩니다.

                  COMPRESSION 파라메터에는 METADATA_ONLY, NONE 두개의 옵션이 있으며,

                  METADATA_ONLY는 파라메터를 사용하지 않으면 디폴트로 인식되는 옵션입니다.

                  COMPRESSION=option_name 으로 사용하면 됩니다.

                  #expdp zoom/imsi00 DIRECTORY=/oracle/expdir DUMPFILE=dump.dmp

                                                COMPRESSION=NONE

■ Export 모드 관련 파라미터

- FULL

- SCHEMAS

- TABLES

- TABLESPACES

- TRANSPORT_FULL_CHECK : TRANSPORT_FULL_CHECK 파라메터는 export 작업 시에

                          테이블스페이스 내에 존재하는 테이블과 인덱스의 의존선을 검사 할 것인지

                          하지 않을 것인지를 설정하는 파라미터로 'Y' 또는 'N' 두개이 값만을 허용하는

                          파라메터 입니다.

(1) 'Y' 일 경우, TABLESPACE 내에 테이블만 있고, 인덱스가 없다면 작업은 실패한다.

      반드시 INDEX도 같은 테이블스페이스내에 존재 해야 합니다.

(2) 'Y' 일 경우, TABLESPACE내에 인덱스만 존재하고 테이블이 없다면 작업은 실패합니다.

       반드시 TABLE 또한 존재해야 합니다

(3) 'N' 일 경우, TABLESPACE내에 테이블만 있고 인덱스가 없다면 작업은 성공합니다.

(4) 'N' 일 경우, TABLESPACE내에 인덱스만 있고 테이블이 없다면 작업은 성공합니다.

■ Export 필터링 관련 파라미터

- CONTENT : 3개의 옵션을 가질 수 있으며 옵션들은 다음과 같습니다

   + ALL : 테이블과 메터데이터를 포함한 모든것을 포함시키겠다는 옵션

      # expdp zoom/imsi00 DUMPFILE=datadump.dmp CONTENT=ALL

   + DATA_ONLY : 테이블 데이터만 포함 시키겠다는 옵션

     #  expdp zoom/imsi00 DUMPFILE=datadump.dmp CONTENT=DATA_ONLY

   + METADATA_ONLY : 메타데이터 만을 포함하겠다는 옵션

     #  expdp zoom/imsi00 DUMPFILE=datadump.dmp CONTENT=METADATA_ONLY

- EXCLUDE & INCLUDE : 원하는 오브젝트를 선택하여 받을 수 있습니다.

                                     오라클에서 오브젝트란 유저스키마, 테이블, 인덱스, 프로시져 등을

                                     통칭해서 오브젝트라고 부릅니다.

                                     [exclude | include]=object_name 조건 형식으로 사용 할 수 있음

                                     파라미터 사용방법은 아래와 같습니다.

        + SCOTT 유저와 관련된 모든것을 export 받고 싶은데, BONUS 테이블은 제외하고 받고

          싶은 경우

          #expdp zoom/imsi00 dumfile=ex_dump.dmp schemas=scott exclude=TABLE:"='BONUS'"

        + SCOTT 유저와 관련된 모든것을 export 받고 싶은데 EMP 테이블의 인덱스는 받고

          싶지 않은경우

         #expdp zoom/imsi00 dumfile=ex_dump.dmp schemas=scott exclude=INDEX:\"='EMP%'\"

- QUERY : 테이블 내에 있는 데이터 중 특정 조건에 만족하는 데이터만을 export 받고자 할때

                사용하는 파라메터 입니다.

                사용방법은 다음과 같습니다

                QUERY=SCHEMA.TABLE:"조건" 이며 다음과  같은 예입니다.

                # expdp zoom/imsi00 dumpfile=ex_dump.dmp table=emp

                                               QUERY=SCOTT.EMP:'where sal > 1200'

- SAMPLE : 오라클 10g에서 새롭게 지원하는 기능 중 하나로써 테이블의 데이터를 export 할때

                  퍼센트를 정하여 지정된 퍼센트 만큼의 데이터를 샘플링해서 뽑을때 사용하는

                  옵션입니다

               # expdp zoom/imsi00 DIRECTORY=datadump_dir1 DUMPFILE=ex_dump.dmp

                                              SAMPLE=scott.emp:20

                  //scott 유저의 EMP테이블의 데이터중 20%만을 export 하게 됩니다

                     입력 가능한 퍼센트의 범위는 0.000001 ~ 100 까지 입니다.

■ 네트워크 링크 파라미터

- 원격지 데이터베이스에 있는 데이터에 접근하여 로컬 데이터베이스 머신에 export 된 덤프파일을

   저장하고자 할때 사용하는 파라메터

- 먼저, 원격지 데이터베이스의 테이블에 대한 DB_LINK 를 만들어 놓아야 함

  # expdp zoom/imsi00 DIRECTORY=datadump_dir1 DUMPFILE=ex_dump.dmp

                                 NETWORK_LINK=EMP@link_b_scott LOGFILE=datapump.log

■ 암호화 관련 파라미터

- export 되는 데이터 중 일부 컬럼이 암호화 되어 있고, 중요한 데이터 일 경우 사용하는 파라미터

- export 시에 암호를 설정하여 export된 데이터가 위변조 되지 못하게 설정할 수 있음

  # expdp zoom/imsi00 TABLES=EMP DUMPFILE=ex_dump.dmp

                                 ENCRYPTION_PASSWORD=******

■ JOB관련 파라미터

- JOB : JOB파라미터를 설정하면 Data Dump 작업을 오라클에서 자동 할당하지 않고

           JOB파라메터에서 주어진 이름으로 등록 되게 됩니다. 작업 마스터 테이블에 작업명이

           등록되어 작업에 대한 정보들을 JOB 파라미터에 등록된 이름으로 조회 할 수있습니다.

- STATUS : STATUS파라미터는 Data Pump Export 시에 작업의 갱신 된 내용을 STATUS에

                 설정된 크기의 시간 간격으로 진행상태를 보고 받고자 할때 사용하는 파라메터 입니다.

                 STATUS = 30 이면 30초 간격으로 작업결과를 갱신하여 보여주게 됩니다.

                 만약 이 파라미터를 설정하지 않으면 디폴트는 0입니다. 디폴트로 설정하게 되면

                 거의 실시간으로 작업정보를 보여주게 됩니다.

- FLASHBACK_SCN : System Change Number(SCN) 은 시스템의 테이블이나 오브젝트가

                변경되었을때 변경되는 SCN값을 가집니다. FLASHBACK_SCN 파라메터를 이용하여

                SCN 값을 지정할 경우에 파라미터에서 설정한 SCN 기준 이전까지의상태를 받게 됩니다

                # expdp zoom/imsi00 DIRECTORY=datadump_dir DUMPFILE=ex_dump.dmp

                                               FLASHBACK_SCN=120001

- FLASHBACK_TIME : FLASHBACK_TIME은 번호 대신 시간값을 가집니다.

                FLASHBACK_TIME 파라미터를 사용하면 파라미터에 지정된 시간까지의 변경

                사항만을 Export 합니다.FLASHBACK_TIME 의 값은 TIMESTAMP 형식의 값을 가지며

                TO_TIMESTAMP 함수를 사용하여 설정 할 수 있습니다.

- PARALLEL : PARALLEL 파라메터를 사용할 경우 export 작업 시에 프로세스를 설정된

                숫자만큼 만들어 수행함으로써 작업의 속도를 향상 시킬 수 있습니다. 디폴트 값은 1로

                설정되어 있으며 주의할점은 parallel 이 지정된 갯수 만틈의 dumpfile 을 지정해 주어야

                한다. 앞서 본 %U를 사용하면 지정된 갯수만큼 자동으로 파일을 만들어 줍니다

                # expdp zoom/imsi00 DIRECTORY=datadump_dir DUMPFILE=ex_dump%U.dmp

                                               PARALLEL=3

                위와 같이 설정하게되면 ex_dump01.dmp, ex_dump02.dmp, ex_dump03.dmp 3개의

                덤프파일이 생성됩니다.

                # expdp zoom/imsi00 DIRECTORY=datadump_dir

                  DUMPFILE=(ex_dump01.dmp, ex_dump02.dmp, ex_dump03.dmp) PARALLEL=3

                 위와 같이 %U를 사용하지 않고, 사용자가 직접 3개의 파일명을 ',' 구분하여 입력해도

                 상관 없습니다.

- ATTACH : ATTACH 파라미터를 이용하여 interactive mode 로 들어 갈 수 있습니다.

                 오라클에서는 작업을 제어하고 모니터링 하기 위해 interactive mode를 제공합니다.

                 interactive mode로 들어가는 방법은 두가지가 있으며 다음과 같습니다.

                 + Crtl + C 를 누름으로써 들어가는 방법

                   # expdp zoom/imsi00 directory=datadump_dir  table=scott.emp

                                                 dumpfile=datadump.dmp logfile=datapump.log

                                                 jobname=myjob            

                                                작업로그.....

                                                .................==> 작업에 대한 로그가 떨어졌을때 Crtl + C

                   export>                  ==> 이와 같이 프롬프트 상태로 떨어지게 됩니다.

                   로그가 멈춘다고 해서 작업이 중단된게 아니라 이상태에서 interactive mode 명령을

                   사용하여 작업을 모니터링 하고 제어 할 수 있습니다

                   # expdp zoom/imsi00 attach=schema.jobname 형식으로 원하는 작업의

                   interactive mode로 들어갈 수 있습니다.

                   < InterActive Mode 명령어 >            

3. Data Pump Import

■ 필터링 관련 파라미터

- CONTENT : CONTENT 파라미터는 DATA_ONLY, METADATA_ONLY, ALL 3개의 값을 가짐

   #impdp zoom/imsi00 directory=datapump_dir dumpfile=datapump.dmp schemas=scott

                                CONTENT=DATA_ONLY

- INCLUDE : INCLUDE=OBJECT_NAME:"='조건'" 형식으로 사용할 수 있으며 오브젝트의

                  종류에는 앞서 본것처럼 TABLE, INDEX, PROCEDURE, FUNCTION 등이 있습니다.

   #impdp zoom/imsi00 directory=datapump_dir dumpfile=datapump.dmp schemas=scott

                                INCLUDE=TABLE:"='SAL'"

     ==>SCOTT 유저의 테이블을 import 하되 SAL테이블 만 포함 시키라는 옵션명령이 됩니다.

- EXCLUDE : EXCLUDE=OBJECT_NAME:"='조건'" 형식으로 사용할 수 있으며 오브젝트의

                  종류에는 앞서 본것처럼 TABLE, INDEX, PROCEDURE, FUNCTION 등이 있습니다.

   #impdp zoom/imsi00 directory=datapump_dir dumpfile=datapump.dmp schemas=scott

                                 EXCLUDE=TABLE:"='SAL'"

     ==> SCOTT 유저의 테이블을 import 하되 SAL테이블을 제외한 나머지 테이블을 import 하라는

           명령

- TABLE_EXISTS_ACTION : imprt 시에 중요한 명령입니다

                                       우리가 Data Pump를 통해 작업하게 될 경우 같은 이름의 테이블이

                                       존재할 때가 있습니다. 만약 테이블이 존재 하더라도 import 하고자 하는

                                       데이터의 row수가 다를수도 있고 같을 수도있습니다. 즉 테이블은 존재

                                       하더라고 데이터의 내용은 차이가 나는거죠.

                                       이러한 경우 사용할 수 있는 유용한 파라메터가

                                       TABLE_EXISTES_ACTION 입니다. TABLE_EXISTES_ACTION파라메터

                                       는 SKIP, APPEND, TRUNCATE, REPLACE 의 값을 가질 수 있으며

                                       각값의 의미는 다음과 같습니다.

                                       + SKIP : 같은 테이블을 만나면 지나치고, 다음 테이블을 import 합니다

                                       + APPEND : 같은 테이블을 만나면 기존테이블에 추가하여 import

                                       + TRUNCATE : 같은 테이블을 만나면 기존테이블을 truncate하고

                                                             새로운 데이터를 import

                                       + REPLACE : 같은 테이블을 만날 경우 기존 테이블을 drop 하고

                                                           테이블을 재생성 한 후 import 합니다

   #impdp zoom/imsi00 directory=datapump_dir dumpfile=datapump.dmp schemas=scott

                                 TABLE_EXISTS_ACTION=SKIP

■ JOB 관련 파라미터

- JOB_NAME, STATUS, PARALLEL 파라메터를 export와 같은 방법으로 사용

- PARALLEL 작업시에 dumpfile 갯수를 %U 사용하여 지정하여 주거나 명시적으로 ','를 사용하여

   PARALLEL 갯수만큼 파일을 지정해야 함

■ 리맵핑 관련 파라미터

- REMAP_SCHEMA : A 유저 스키마로 export 받은 데이터를 B 유저 스키마로 import 하고자 할때

                              사용합니다.

   #impdp zoom/imsi00 directory=datapump_dir dumpfile=datapump.dmp schemas=scott

                                REMAP_SCHEMA=SCOTT:ZOOM

    ==> 위와 같이 수행한후 TABLE 의 OWNER 를 조회한다면 ZOOM 유저의 소유로 테이블이

          등록되었음을 확인 할 수 있다.

- REMAP_DATAFILE : 전체 데이터베이스 시스템을 Data Pump를 통하여 옮기고자 할때

                               Export 된 dumpfile에는 DataFile 정보까지 포함하게 됩니다. 하지만 서로다른

                              시스템 경로상에 존재하지 않는 경로이기 때문에 Import 에 실패하게 됩니다.

                              이러한 경우 사용할 수 있는 파라미터가 REMAP_DATAFILE 입니다. export 된

                              dumpfile이 datafile 정보를 포함할 경우에만 해당합니다.

   #impdp zoom/imsi00 directory=datapump_dir dumpfile=datapump.dmp schemas=scott

                              REMAP_DATAFILE='/db1/data/lvol01':'/db2/data/lvol01',

                                                          '/db1/data/lvol02':'/db2/data/lvol02'

- REMAP_TABLESPACE : export 받은 데이터 속한 tablespace 에서 다른 tablespace로

                                     remapping하고자 하는 경우 사용할 수 있는 파라메터 입니다.

   #impdp zoom/imsi00 directory=datapump_dir dumpfile=datapump.dmp schemas=scott

                              REMAP_TABLESPACE='SCOTT_TSB':'ZOOM_TSB'

■ 네트워크 링크 파라미터

export 와 마찬가지로 DBLINK를 이용하여 원격지 데이터베이스에 대해 import 작업을 수행 할 수있습니다.

■ InterActive Mode 파라미터

export 와 마찬가지로 Ctrl+C를 통해 interActive Mode로 진입 할 수 있으며 작업을 통제 할 수있습니다

4. Data Pump 모니터링 하기

1) Data Pump 모니터링 하기 관련조회 테이블 및 VIEW

■ DBA_DATAPUMP_JOBS 현재 실행중인 작업의 속성들

   SQL> SELECT * FROM DBA_DATAPUMP_JODS;

            OWNER_NAME = DB작업계정

            JOB_NAME = 작업의 명칭

            JOB_MODE = FULL, TABLE, INDEX, TABLESPACE 등이 있음

            STATE = EXECUTING(수행중), DEFINING, UNDEFINING, NOT RUNNING 의 값을 가짐

■ PUMP Session 확인

■ Data Pump의 모니터링

    SQL> SELECT OPNAME, TARGET_DESC, SOFAR, TOTALWORK,

             (SOFAR/TOTALWORK*100) PER

              FROM V$SESSION_LONGOPS; 로 조회하면 Data Pump 의 모니터링을 할 수 있습니다

              OPNAME = JOBNAME 과 같음

              TOTALWORK = 총 수행하여야 할 용량을 가리키며, 단위는 Megabytes

              SOFAR = 현재 수행한 용량을 가리키며 단위는 Megabytes

              TARGET_DESC = 작업의 종류를 말함

                                       import/export  값이 될 수 있음

출처 : http://cafe.naver.com/ocmkorea.cafe?iframe_url=/ArticleRead.nhn%3Farticleid=1388

GRANT

Purpose

Use the GRANT statement to grant:

• System privileges to users and roles.

• Roles to users and roles. Both privileges and roles are either local, global, or external. Table 18-1 lists the system privileges (organized by the database object operated upon). Table 18-2 lists Oracle Database predefined roles.

• Object privileges for a particular object to users, roles, and PUBLIC. Table 18-3 summarizes the object privileges that you can grant on each type of object. Table 18-4 lists object privileges and the operations that they authorize.

Notes on Authorizing Database Users You can authorize database users through means other than the database and the GRANT statement.

• Many Oracle Database privileges are granted through supplied PL/SQL and Java packages. For information on those privileges, please refer to the documentation for the appropriate package.

• Some operating systems have facilities that let you grant roles to Oracle Database users with the initialization parameter OS_ROLES. If you choose to grant roles to users through operating system facilities, then you cannot also grant roles to users with the GRANT statement, although you can use the GRANT statement to grant system privileges to users and system privileges and roles to other roles.

Additional Topics

• Prerequisites

• Syntax

• Semantics

• Listings of System and Object Privileges

• Examples

Prerequisites

To grant a system privilege, you must either have been granted the system privilege with the ADMIN OPTION or have been granted the GRANT ANY PRIVILEGE system privilege.

To grant a role, you must either have been granted the role with the ADMIN OPTION or have been granted the GRANT ANY ROLE system privilege, or you must have created the role.

To grant an object privilege, you must own the object, or the owner of the object must have granted you the object privileges with the GRANT OPTION, or you must have been granted the GRANT ANY OBJECT PRIVILEGE system privilege. If you have the GRANT ANY OBJECT PRIVILEGE, then you can grant the object privilege only if the object owner could have granted the same object privilege. In this case, the GRANTOR column of the DBA_TAB_PRIVS view displays the object owner rather than the user who issued the GRANT statement.

Syntax

grant::=

(grant_system_privileges::=, grant_object_privileges::=)

grant_system_privileges::=

(grantee_clause ::=)

grant_object_privileges::=

(on_object_clause ::=, grantee_clause ::=)

on_object_clause ::=

grantee_clause ::=

Semantics

grant_system_privileges

Use these clauses to grant system privileges.

system_privilege

Specify the system privilege you want to grant. Table 18-1 lists the system privileges, organized by the database object operated upon.

• If you grant a privilege to a user, then the database adds the privilege to the user's privilege domain. The user can immediately exercise the privilege.

• If you grant a privilege to a role, then the database adds the privilege to the privilege domain of the role. Users who have been granted and have enabled the role can immediately exercise the privilege. Other users who have been granted the role can enable the role and exercise the privilege.

  See Also:

  Granting a System Privilege to a User: Example and "Granting System Privileges to a Role: Example"

• If you grant a privilege to PUBLIC, then the database adds the privilege to the privilege domains of each user. All users can immediately perform operations authorized by the privilege.

Oracle Database provides the ALL PRIVILEGES shortcut for granting all the system privileges listed in Table 18-1, except the SELECT ANY DICTIONARY privilege.

role

Specify the role you want to grant. You can grant an Oracle Database predefined role or a user-defined role. Table 18-2 lists the predefined roles.

• If you grant a role to a user, then the database makes the role available to the user. The user can immediately enable the role and exercise the privileges in the privilege domain of the role.

• If you grant a role to another role, then the database adds the privilege domain of the granted role to the privilege domain of the grantee role. Users who have been granted the grantee role can enable it and exercise the privileges in the granted role's privilege domain.

• If you grant a role to PUBLIC, then the database makes the role available to all users. All users can immediately enable the role and exercise the privileges in the privilege domain of the role.

  See Also:

  "Granting a Role to a Role: Example" and CREATE ROLE for information on creating a user-defined role

IDENTIFIED BY Clause

Use the IDENTIFIED BY clause to specifically identify an existing user by password or to create a nonexistent user. This clause is not valid if the grantee is a role or PUBLIC. If the user specified in the grantee_clause does not exist, then the database creates the user with the password and with the privileges and roles specified in this clause.

WITH ADMIN OPTION

Specify WITH ADMIN OPTION to enable the grantee to:

• Grant the role to another user or role, unless the role is a GLOBAL role

• Revoke the role from another user or role

• Alter the role to change the authorization needed to access it

• Drop the role

If you grant a system privilege or role to a user without specifying WITH ADMIN OPTION, and then subsequently grant the privilege or role to the user WITH ADMIN OPTION, then the user has the ADMIN OPTION on the privilege or role.

To revoke the ADMIN OPTION on a system privilege or role from a user, you must revoke the privilege or role from the user altogether and then grant the privilege or role to the user without the ADMIN OPTION.

grantee_clause

TO grantee_clause identifies users or roles to which the system privilege, role, or object privilege is granted.

Restriction on Grantees A user, role, or PUBLIC cannot appear more than once in TO grantee_clause.

PUBLIC Specify PUBLIC to grant the privileges to all users.

Restrictions on Granting System Privileges and Roles Privileges and roles are subject to the following restrictions:

• A privilege or role cannot appear more than once in the list of privileges and roles to be granted.

• You cannot grant a role to itself.

• You cannot grant a role IDENTIFIED GLOBALLY to anything.

• You cannot grant a role IDENTIFIED EXTERNALLY to a global user or global role.

• You cannot grant roles circularly. For example, if you grant the role banker to the role teller, then you cannot subsequently grant teller to banker.

grant_object_privileges

Use these clauses to grant object privileges.

object_privilege

Specify the object privilege you want to grant. You can specify any of the values shown in Table 18-3. See also Table 18-4.

Restriction on Object Privileges A privilege cannot appear more than once in the list of privileges to be granted.

ALL [PRIVILEGES]

Specify ALL to grant all the privileges for the object that you have been granted with the GRANT OPTION. The user who owns the schema containing an object automatically has all privileges on the object with the GRANT OPTION. The keyword PRIVILEGES is provided for semantic clarity and is optional.

column

Specify the table or view column on which privileges are to be granted. You can specify columns only when granting the INSERT, REFERENCES, or UPDATE privilege. If you do not list columns, then the grantee has the specified privilege on all columns in the table or view.

For information on existing column object grants, query the USER_, ALL_, or DBA_COL_PRIVS data dictionary view.

on_object_clause

The on_object_clause identifies the object on which the privileges are granted. Directory schema objects and Java source and resource schema objects are identified separately because they reside in separate namespaces.

If you can make this grant only because you have the GRANT ANY OBJECT PRIVILEGE system privilege--that is, you are not the owner of object, nor do you have object_privilege on object WITH GRANT OPTION--then the effect of this grant is that you are acting on behalf of the object owner. The *_TAB_PRIVS data dictionary views will reflect that this grant was made by the owner of object.

WITH GRANT OPTION

Specify WITH GRANT OPTION to enable the grantee to grant the object privileges to other users and roles.

Restriction on Granting WITH GRANT OPTION You can specify WITH GRANT OPTION only when granting to a user or to PUBLIC, not when granting to a role.

WITH HIERARCHY OPTION

Specify WITH HIERARCHY OPTION to grant the specified object privilege on all subobjects of object, such as subviews created under a view, including subobjects created subsequent to this statement.

This clause is meaningful only in combination with the SELECT object privilege.

object Specify the schema object on which the privileges are to be granted. If you do not qualify object with schema, then the database assumes the object is in your own schema. The object can be one of the following types:

• Table, view, or materialized view

• Sequence

• Procedure, function, or package

• User-defined type

• Synonym for any of the preceding items

• Directory, library, operator, or indextype

• Java source, class, or resource

You cannot grant privileges directly to a single partition of a partitioned table.

DIRECTORY directory_name Specify a directory schema object on which privileges are to be granted. You cannot qualify directory_name with a schema name.

JAVA SOURCE | RESOURCE The JAVA clause lets you specify a Java source or resource schema object on which privileges are to be granted.

Listings of System and Object Privileges

Note 1: Oracle Database treats a Java class, source, or resource as if it were a procedure for purposes of granting object privileges.

Note 2: Job scheduler objects are created using the DBMS_SCHEDULER package. After these objects are created, you can grant the EXECUTE object privilege on job scheduler classes and programs. You can grant ALTER privilege on job scheduler jobs, programs, and schedules.

Note 3: The DELETE, INSERT, and UPDATE privileges can be granted only to updatable materialized views.

Examples

Granting a System Privilege to a User: Example To grant the CREATE SESSION system privilege to the sample user hr, allowing hr to log on to Oracle Database, issue the following statement:

GRANT CREATE SESSION 
   TO hr; 

Granting System Privileges to a Role: Example The following statement grants appropriate system privileges to a data warehouse manager role, which was created in the "Creating a Role: Example":

GRANT
     CREATE ANY MATERIALIZED VIEW
   , ALTER ANY MATERIALIZED VIEW
   , DROP ANY MATERIALIZED VIEW
   , QUERY REWRITE
   , GLOBAL QUERY REWRITE
   TO dw_manager
   WITH ADMIN OPTION;

The dw_manager privilege domain now contains the system privileges related to materialized views.

Granting a Role with the Admin Option: Example To grant the dw_manager role with the ADMIN OPTION to the sample user sh, issue the following statement:

GRANT dw_manager 
   TO sh 
   WITH ADMIN OPTION; 

User sh can now perform the following operations with the dw_manager role:

• Enable the role and exercise any privileges in the privilege domain of the role, including the CREATE MATERIALIZED VIEW system privilege

• Grant and revoke the role to and from other users

• Drop the role

Granting Object Privileges to a Role: Example The following example grants the SELECT object privileges to a data warehouse user role, which was created in the "Creating a Role: Example":

GRANT SELECT ON sh.sales TO warehouse_user;

Granting a Role to a Role: Example The following statement grants the warehouse_user role to the dw_manager role. Both roles were created in the "Creating a Role: Example":

GRANT warehouse_user TO dw_manager; 

The dw_manager role now contains all of the privileges in the domain of the warehouse_user role.

Granting an Object Privilege on a Directory: Example To grant READ on directory bfile_dir to user hr, with the GRANT OPTION, issue the following statement:

GRANT READ ON DIRECTORY bfile_dir TO hr
   WITH GRANT OPTION;

Granting Object Privileges on a Table to a User: Example To grant all privileges on the table oe.bonuses, which was created in "Merging into a Table: Example", to the user hr with the GRANT OPTION, issue the following statement:

GRANT ALL ON bonuses TO hr 
   WITH GRANT OPTION; 

The user hr can subsequently perform the following operations:

• Exercise any privilege on the bonuses table

• Grant any privilege on the bonuses table to another user or role

Granting Object Privileges on a View: Example To grant SELECT and UPDATE privileges on the view emp_view, which was created in "Creating a View: Example", to all users, issue the following statement:

GRANT SELECT, UPDATE 
   ON emp_view TO PUBLIC; 

All users can subsequently query and update the view of employee details.

Granting Object Privileges to a Sequence in Another Schema: Example To grant SELECT privilege on the customers_seq sequence in the schema oe to the user hr, issue the following statement:

GRANT SELECT 
   ON oe.customers_seq TO hr; 

The user hr can subsequently generate the next value of the sequence with the following statement:

SELECT oe.customers_seq.NEXTVAL 
   FROM DUAL; 

Granting Multiple Object Privileges on Individual Columns: Example To grant to user oe the REFERENCES privilege on the employee_id column and the UPDATE privilege on the employee_id, salary, and commission_pct columns of the employees table in the schema hr, issue the following statement:

GRANT REFERENCES (employee_id), 
      UPDATE (employee_id, salary, commission_pct) 
   ON hr.employees
   TO oe; 

The user oe can subsequently update values of the employee_id, salary, and commission_pct columns. User oe can also define referential integrity constraints that refer to the employee_id column. However, because the GRANT statement lists only these columns, oe cannot perform operations on any of the other columns of the employees table.

For example, oe can create a table with a constraint:

CREATE TABLE dependent 
   (dependno   NUMBER, 
    dependname VARCHAR2(10), 
    employee   NUMBER 
   CONSTRAINT in_emp REFERENCES hr.employees(employee_id) );

The constraint in_emp ensures that all dependents in the dependent table correspond to an employee in the employees table in the schema hr.

/etc/cron.daily 에 파일을 만들어 두거나

crond 에 등록해서 사용하시면 됩니다.

[기능요약]

1.root 계정으로 전체 디비를 디비별로 백업 가능함
2.옵션으로 테이블 별 백업 가능함.
3.보존 설정 기간 이후 자동 삭제

--------------------------------------------source----------------------------------------------------------

#!/bin/sh

############################################################################
#
# By 질주본능 2009/06/03
#
#    V 2.0
#
# 데이터 베이스별 혹은 테이블 별로 백업 받을 수 있도록 변경했으며
# 30 LINE 에 명기된 배열에 속하는 디비들만 테이블 별로 백업 받는다.
#
# 데이터베이스 단위로 받은 파일은 DB. 으로 시작하고
# 테이블 단위는 TB. 으로 시작하는 파일명을 갖는다.
#
# 백업 파일은 dump.gz 로 형성 되며 설정된 보존 기간이 지나면 자동 삭제 된다.
#
#############################################################################

##### SET VARIABLES ############ 전체를 백업 받으려면 여기에 root 계정을 입력한다.
DBHOST="yourhost"
DBUSER="yourid"
DBPWD="yourpassword"

#백업 경로
BACKUPDIR="/home/your_backup_dir/"
#로그 작성 경로
LOGDIR="/home/your_backup_log_dir/"
#로그 파일 앞에 붙을 특정 텍스트
LOGNAME="log_"

###### SET DB NAMES which BACKUP by TABLES ###### 여기 표시된 DB 들은 테이블 별로 백업된다.
DB_TBARR=(dbname1 dbname2 dbname3)

#백업 옵션을 원하는 대로 설정
#routine 옵션은 5버전 이상에서만 사용(function 등을 백업함)
OPTIONS="--skip-comments --default-character-set=euckr --routines"

##### SET CHARGE LIMIT ########## 백업 파일 저장 기간(일단위)
CHARGE_LMT=3

##### DELETE OLD_BACKUP FILES #######
find ${BACKUPDIR} -name "*.gz" -mtime +${CHARGE_LMT} -exec rm -rf {} \;

##### START LOG CREATE ##########
YMD_S=`date +%Y%m%d%H%M%S`
touch ${LOGDIR}${LOGNAME}${YMD_S}.start

##### DB LIST GET ###############
#IF YOU WANT TO EXCEPT SOME DATABASE ADD THIS
#grep -v Database | grep -v somename | grep -v somename`
#

#grep -v dbname 하면 백업 시 제외 된다.

RESULT=`mysql -u$DBUSER -p$DBPWD -h$DBHOST -e "show databases" | grep -v Database`

for DB in $RESULT; do

    sw=0
    ##### compare DB to TBARR #####
    for i in ${DB_TBARR[@]}; do
        if [ $i = $DB ]; then
            let "sw=sw+1"
        fi
        done

    if [ $sw = 0 ]; then
        # BACKUP by DB
        FNAME=${BACKUPDIR}DB.${DB}_${YMD_S}.dump
        #echo $FNAME
        touch $FNAME
        echo "set foreign_key_checks=0;" >> $FNAME
        mysqldump -u$DBUSER -p$DBPWD -h$DBHOST $OPTIONS $DB >> $FNAME
        echo "set foreign_key_checks=1;" >> $FNAME
        gzip --rsyncable $FNAME

    else
        # BACKUP by TABLES
                RESULT_TB=`mysql -u$DBUSER -p$DBPWD -h$DBHOST $DB -e"show tables" | grep -v heap | grep -v Tables_in `
                for TB in $RESULT_TB; do

                        FNAME=${BACKUPDIR}TB.${DB}.${TB}_${YMD_S}.dump
            #echo $FNAME
            touch $FNAME
            echo "set foreign_key_checks=0;" >> $FNAME
            mysqldump -u$DBUSER -p$DBPWD -h$DBHOST $OPTIONS $DB $TB >> $FNAME
            echo "set foreign_key_checks=1;" >> $FNAME
            gzip --rsyncable $FNAME

                done        
    fi

done

##### END LOG CREATE ###########
YMD_E=`date +%Y%m%d%H%M%S`

touch ${LOGDIR}${LOGNAME}${YMD_E}.end