'recovery'에 해당되는 글 1건

  1. 2014.11.01 recovery 이론 및 dump파일 내용
2014. 11. 1. 01:58

recovery 이론 및 dump파일 내용

DB/oralce

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




저작자표시

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

ora-01190  (0) 2014.11.04
archive log mode 아카이브 로그 모드 장애 복구  (0) 2014.11.03
오라클이 시작되는 원리  (0) 2014.10.31
오라클 실습시 용량부족으로 디스크 붙이는 방법  (0) 2014.10.31
backup  (0) 2014.10.30
Posted by 성장하는yw

티스토리툴바