recovery 이론 및 dump파일 내용

DB/oralce

recovery 이론 및 dump파일 내용

성장하는yw 2014. 11. 1. 01:58

dump file이 저장될 장소를 확인하는 명령어

SYS>show parameter user_dump_dest;

NAME TYPE VALUE

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

user_dump_dest string /app/oracle/diag/rdbms/testdb/testdb/trace

위 trace 경로에는 많은 파일이 있어서 생성되어도 찾기가 어려움

그래서 구분하기 쉽게 identifier을 추가한 후 dump를 수행함

SYS>alter session set tracefile_identifier='AAA';

Session altered.

SYS>oradebug setmypid;

Statement processed.

SYS>oradebug dump controlf 3;

Statement processed.

SYS>!

[oracle@localhost ~]$ cd /app/oracle/diag/rdbms/testdb/testdb/trace/

[oracle@localhost trace]$ ls -lSh *AAA*

-rw-r----- 1 oracle oinstall 44K Nov 1 00:33 testdb_ora_4412_AAA.trc

-rw-r----- 1 oracle oinstall 169 Nov 1 00:33 testdb_ora_4412_AAA.trm

컨트롤 파일 dump가 완성됨

968줄이나 됨.

복구와 관련된 내용만 살펴보자.

[oracle@localhost trace]$ vi testdb_ora_4412_AAA.trc

Trace file /app/oracle/diag/rdbms/testdb/testdb/trace/testdb_ora_4412_AAA.trc

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

With the Partitioning option

ORACLE_HOME = /app/oracle/product/11g

System name: Linux

Node name: localhost.localdomain

Release: 2.6.18-238.el5

Version: #1 SMP Tue Jan 4 15:24:05 EST 2011

Machine: i686

VM name: VMWare Version: 6

Instance name: testdb

Redo thread mounted by this instance: 1

Oracle process number: 19

Unix process pid: 4412, image: oracle@localhost.localdomain (TNS V1-V3)

... 중간 생략 ...

*** 2014-11-01 00:22:07.177

*** SESSION ID:(1.5) 2014-11-01 00:22:07.177

*** CLIENT ID:() 2014-11-01 00:22:07.177

*** SERVICE NAME:(SYS$USERS) 2014-11-01 00:22:07.177

*** MODULE NAME:(sqlplus@localhost.localdomain (TNS V1-V3)) 2014-11-01 00:22:07.177

*** ACTION NAME:() 2014-11-01 00:22:07.177

***************************************************************************

DATABASE ENTRY

***************************************************************************

(size = 316, compat size = 316, section max = 1, section in-use = 1,

last-recid= 0, old-recno = 0, last-recno = 0)

(extent = 1, blkno = 1, numrecs = 1)

09/27/2014 13:35:14

DB Name "TESTDB" DATABASE NAME 임

Database flags = 0x00404001 0x00001200

Controlfile Creation Timestamp 09/27/2014 13:35:15

Incmplt recovery scn: 0x0000.00000000

Resetlogs scn: 0x0000.000bc19f Resetlogs Timestamp 09/27/2014 13:35:17

Prior resetlogs scn: 0x0000.00000001 Prior resetlogs Timestamp 09/05/2010 15:39:48

Redo Version: compatible=0xb200000

#Data files = 5, #Online files = 5

Database checkpoint: Thread=1 scn: 0x0000.0014161e DATABASE 전체의 Chekcpoint SCN

Threads: #Enabled=1, #Open=1, Head=1, Tail=1

enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000

... 중간 생략 ...

***************************************************************************

LOG FILE RECORDS 복구에 필요한 Redo Log File 관련 내용

***************************************************************************

(size = 72, compat size = 72, section max = 16, section in-use = 3,

last-recid= 3, old-recno = 0, last-recno = 0)

(extent = 1, blkno = 10, numrecs = 16)

LOG FILE #1:

name #3: /app/oracle/oradata/testdb/redo01.log 1번 그룹의 파일 경로

Thread 1 redo log links: forward: 2 backward: 0

siz: 0x19000 seq: 0x00000016 hws: 0xd bsz: 512 nab: 0x18247 flg: 0x1 dup: 1

Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00132247

Low scn: 0x0000.00137883 10/26/2014 16:59:37

Next scn: 0x0000.00139a51 10/26/2014 21:04:30

LOG FILE #2:

name #2: /app/oracle/oradata/testdb/redo02.log 2번 그룹의 파일 경로

Thread 1 redo log links: forward: 3 backward: 1

siz: 0x19000 seq: 0x00000017 hws: 0xd bsz: 512 nab: 0x108a8 flg: 0x1 dup: 1

Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00137883

Low scn: 0x0000.00139a51 10/26/2014 21:04:30

Next scn: 0x0000.0014161d 10/31/2014 23:27:36

LOG FILE #3:

name #1: /app/oracle/oradata/testdb/redo03.log 3번 그룹의 파일 경로

Thread 1 redo log links: forward: 0 backward: 2

siz: 0x19000 seq: 0x00000018 hws: 0x2 bsz: 512 nab: 0xffffffff flg: 0x8 dup: 1

Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00139a51

Low scn: 0x0000.0014161d 10/31/2014 23:27:36

Next scn: 0xffff.ffffffff 01/01/1988 00:00:00

***************************************************************************

DATA FILE RECORDS Data file 관련 내용

***************************************************************************

(size = 520, compat size = 520, section max = 100, section in-use = 5,

last-recid= 38, old-recno = 0, last-recno = 0)

(extent = 1, blkno = 11, numrecs = 100)

DATA FILE #1:

name #7: /app/oracle/oradata/testdb/system01.dbf

creation size=0 block size=8192 status=0xe head=7 tail=7 dup=1

tablespace 0, index=1 krfil=1 prev_file=0

unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00

Checkpoint cnt:139 scn: 0x0000.0014161e 10/31/2014 23:27:36

Stop scn: 0xffff.ffffffff 10/27/2014 03:12:19

Creation Checkpointed at scn: 0x0000.00000007 09/05/2010 15:39:53

thread:0 rba:(0x0.0.0)

데이터 파일안에는 두가지 scn이 있음

ckeckpoint cnt : 현재 데이터 파일에 저장 되어 있는 scn 번호

stop scn : 추가로 작업이 진행될 때 추가되는 scn을 의미

현재까지 저장완료된 scn은 몇 번인지 알 수 있지만,

신규로 추가되는 scn은 현재 작업 중이기 때문에 몇 번까지 들어올지 모름

-> 오라클 DB가 open 상태이면, stop scn을 임시로 무한대(0xffff.fffffff)로 설정해둠

DB 정상종료가되거나 OFFLINE 되는 경우에

checkpoint 를 발생시켜 checkpoint scn / stop scn을 동일하게 만들고 해당 데이터 파일을 닫음

만약에 운영 중 DB가 장애가 생겨서 shutdown abort 된 상황

- checkpoint scn / stop scn의 값이 동기화 되지 않은 상황에서 종료

... 중간 생략 ...

Hot Backup end marker scn: 0x0000.00000000

aux_file is NOT DEFINED

Plugged readony: NO

Plugin scnscn: 0x0000.00000000

Plugin resetlogs scn/timescn: 0x0000.00000000 01/01/1988 00:00:00

Foreign creation scn/timescn: 0x0000.00000000 01/01/1988 00:00:00

Foreign checkpoint scn/timescn: 0x0000.00000000 01/01/1988 00:00:00

Online move state: 0

DATA FILE #2:

name #6: /app/oracle/oradata/testdb/sysaux01.dbf

creation size=0 block size=8192 status=0xe head=6 tail=6 dup=1

tablespace 1, index=2 krfil=2 prev_file=0

unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00

Checkpoint cnt:139 scn: 0x0000.0014161e 10/31/2014 23:27:36

Stop scn: 0xffff.ffffffff 10/27/2014 03:12:19

Creation Checkpointed at scn: 0x0000.00000883 09/05/2010 15:39:57

thread:0 rba:(0x0.0.0)

enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000

... 중간 생략 ...

Hot Backup end marker scn: 0x0000.00000000

aux_file is NOT DEFINED

Plugged readony: NO

Plugin scnscn: 0x0000.00000000

Plugin resetlogs scn/timescn: 0x0000.00000000 01/01/1988 00:00:00

Foreign creation scn/timescn: 0x0000.00000000 01/01/1988 00:00:00

Foreign checkpoint scn/timescn: 0x0000.00000000 01/01/1988 00:00:00

Online move state: 0

DATA FILE #3:

name #5: /app/oracle/oradata/testdb/undotbs01.dbf

creation size=0 block size=8192 status=0xe head=5 tail=5 dup=1

tablespace 2, index=3 krfil=3 prev_file=0

unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00

Checkpoint cnt:67 scn: 0x0000.0014161e 10/31/2014 23:27:36

Stop scn: 0xffff.ffffffff 10/27/2014 03:12:19

Creation Checkpointed at scn: 0x0000.000bb6b4 09/05/2010 16:17:54

thread:0 rba:(0x0.0.0)

enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000

... 중간 생략 ...

Hot Backup end marker scn: 0x0000.00000000

aux_file is NOT DEFINED

Plugged readony: NO

Plugin scnscn: 0x0000.00000000

Plugin resetlogs scn/timescn: 0x0000.00000000 01/01/1988 00:00:00

Foreign creation scn/timescn: 0x0000.00000000 01/01/1988 00:00:00

Foreign checkpoint scn/timescn: 0x0000.00000000 01/01/1988 00:00:00

Online move state: 0

DATA FILE #4:

name #4: /app/oracle/oradata/testdb/users01.dbf

creation size=0 block size=8192 status=0xe head=4 tail=4 dup=1

tablespace 4, index=4 krfil=4 prev_file=0

unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00

Checkpoint cnt:138 scn: 0x0000.0014161e 10/31/2014 23:27:36

Stop scn: 0xffff.ffffffff 10/27/2014 03:12:19

Creation Checkpointed at scn: 0x0000.000049c6 09/05/2010 15:40:06

thread:0 rba:(0x0.0.0)

enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000

... 중간 생략 ...

Hot Backup end marker scn: 0x0000.00000000

aux_file is NOT DEFINED

Plugged readony: NO

Plugin scnscn: 0x0000.00000000

Plugin resetlogs scn/timescn: 0x0000.00000000 01/01/1988 00:00:00

Foreign creation scn/timescn: 0x0000.00000000 01/01/1988 00:00:00

Foreign checkpoint scn/timescn: 0x0000.00000000 01/01/1988 00:00:00

Online move state: 0

DATA FILE #5:

name #9: /app/oracle/oradata/testdb/example01.dbf

creation size=12800 block size=8192 status=0xe head=9 tail=9 dup=1

tablespace 6, index=6 krfil=5 prev_file=0

unrecoverable scn: 0x0000.00141a59 10/31/2014 23:27:50

Checkpoint cnt:63 scn: 0x0000.0014161e 10/31/2014 23:27:36

Stop scn: 0xffff.ffffffff 10/27/2014 03:12:19

Creation Checkpointed at scn: 0x0000.000c2efc 09/27/2014 13:37:19

thread:1 rba:(0x1.b3fa.10)

enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000

... 중간 생략 ...

***************************************************************************

TEMP FILE RECORDS

***************************************************************************

(size = 56, compat size = 56, section max = 100, section in-use = 1,

last-recid= 10, old-recno = 0, last-recno = 0)

(extent = 1, blkno = 90, numrecs = 100)

TEMP FILE #1: External File #201

name #8: /app/oracle/oradata/testdb/temp01.dbf

creation size=2560 block size=8192 status=0x1e head=8 tail=8 dup=1

tablespace 3, index=5 krfil=1 prev_file=0

unrecoverable scn: 0x0000.000bc288 09/27/2014 13:35:35

저작자표시