Chp1. Recovery Principle

0 Dump File 저장위치 Parameter 확인

orcl@SYS>show parameter user_dump_dest;

NAME                                     TYPE         VALUE

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

user_dump_dest                             string         /u01/app/oracle/diag/rdbms/orcl/orcl/trace

1 해당 경로에 많은 TRC 파일중 구분하기위해 구분자를 삽입

orcl@SYS>show parameter identifier;

NAME                                     TYPE         VALUE

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

tracefile_identifier                     string

**현제 identifier가 없음을 알수있다 ***

orcl@SYS>alter session set tracefile_identifier='AAA'

orcl@SYS>show parameter identifier

NAME                                     TYPE         VALUE

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

tracefile_identifier                     string         AAA

2 control file

현제 Current Porcess를 debug 한다

orcl@SYS>oradebug setmypid;

Statement processed.

orcl@SYS>oradebug dump controlf 3;

Control file 을 dump 떠서 user_dump_dest 경로에 저장하게 하는 명령어 뒤의 숫자는 level 로

1= 파일 헤더만

2= db정보 ㅡ 체크포인트 기록만

3= 모든 레코드 타입 , 최초 , 마지막 기록만

4= 3번까지 기록과 최근 4개 기록

5+= 4 +  순환기록까지

3 Trace File 생성 확인

$ cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace/

$ ls -lSh *AAA*

-rw-r-----. 1 oracle oinstall 47K Jul 13 09:49 orcl_ora_1851_AAA.trc

-rw-r-----. 1 oracle oinstall 144 Jul 13 09:49 orcl_ora_1851_AAA.trm

4 생성된 Dump 파일 내용 확인

$ vi orcl_ora_1851_AAA.trc

Trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_1851_AAA.trc

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1

System name:        Linux

Node name:        s-oel7-11204-2

Release:        3.8.13-35.3.1.el7uek.x86_64

Version:        #2 SMP Wed Jun 25 15:27:43 PDT 2014

Machine:        x86_64

Instance name: orcl

Redo thread mounted by this instance: 1

Oracle process number: 21

Unix process pid: 1851, image: oracle@s-oel7-11204-2 (TNS V1-V3)

*** 2018-07-13 09:49:03.422

*** SESSION ID:(125.25) 2018-07-13 09:49:03.422

*** CLIENT ID:() 2018-07-13 09:49:03.422

*** SERVICE NAME:(SYS$USERS) 2018-07-13 09:49:03.422

*** MODULE NAME:(sqlplus@s-oel7-11204-2 (TNS V1-V3)) 2018-07-13 09:49:03.422

*** ACTION NAME:() 2018-07-13 09:49:03.422

Processing Oradebug command 'setmypid'

*** 2018-07-13 09:49:03.423

Oradebug command 'setmypid' console output: <none>

*** 2018-07-13 09:49:23.771

Processing Oradebug command 'dump controlf 3'

DUMP OF CONTROL FILES, Seq # 1906 = 0x772

 V10 STYLE FILE HEADER:

Compatibility Vsn = 186647552=0xb200400

Db ID=1507867182=0x59e03a2e, Db Name='ORCL'

Activation ID=0=0x0

Control Seq=1906=0x772, File size=594=0x252

File Number=0, Blksiz=16384, File Type=1 CONTROL

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

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)

 06/29/2018 14:14:06

 DB Name "ORCL"

---Database Name 지정

 Database flags = 0x00404000 0x00001000

 Controlfile Creation Timestamp  06/29/2018 14:14:07

 Incmplt recovery scn: 0x0000.00000000

 Resetlogs scn: 0x0000.000e2006 Resetlogs Timestamp  06/29/2018 14:14:13

 Prior resetlogs scn: 0x0000.00000001 Prior resetlogs Timestamp  08/24/2013 11:37:30

 Redo Version: compatible=0xb200400

 #Data files = 6, #Online files = 6

 Database checkpoint: Thread=1 scn: 0x0000.0013c11f

---Database 전체의 Checkpoint SCN

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

 enabled  threads:  01000000 00000000 00000000 00000000 00000000 00000000

  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 

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

CHECKPOINT PROGRESS RECORDS

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

 (size = 8180, compat size = 8180, section max = 11, section in-use = 0,

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

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

THREAD #1 - status:0x2 flags:0x0 dirty:1770

low cache rba:(0x11.10d3.0) on disk rba:(0x11.97f9.0)

on disk scn: 0x0000.0013ca25 07/13/2018 09:49:13

resetlogs scn: 0x0000.000e2006 06/29/2018 14:14:13

heartbeat: 981344561 mount id: 1509163580

---중간 생략---

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

EXTENDED DATABASE ENTRY

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

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

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

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

Control AutoBackup date(dd/mm/yyyy)=29/ 6/2018

Next AutoBackup sequence= 0

Database recovery target inc#:2, Last open inc#:2

flg:0x0, flag:0x0

Change tracking state=0, file index=0, checkpoint count=0scn: 0x0000.00000000

Flashback log count=0, block count=0

Desired flashback log size=0 blocks

Oldest guarantee restore point=0

Highest thread enable/disable scn: 0x0000.00000000

Number of Open thread with finite next SCN in last log: 0

Number of half-enabled redo threads: 0

Sum of absolute file numbers for files currently being moved online: 0

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

REDO THREAD RECORDS

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

 (size = 256, compat size = 256, section max = 8, section in-use = 1,

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

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

THREAD #1 - status:0xf thread links forward:0 back:0

 #logs:3 first:1 last:3 current:2 last used seq#:0x11

 enabled at scn: 0x0000.000e2006 06/29/2018 14:14:13

 disabled at scn: 0x0000.00000000 01/01/1988 00:00:00

 opened at 07/13/2018 09:17:53 by instance orcl

Checkpointed at scn:  0x0000.0013c11f 07/13/2018 09:17:53

 thread:1 rba:(0x11.2.10)

 enabled  threads:  01000000 00000000 00000000 00000000 00000000 00000000

  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 

 log history: 16

 restore point keep sequence: 0

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

LOG FILE RECORDS --------------복구에 팔요한 Logfile 내용

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

 (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: /u01/app/oracle/oradata/orcl/redo01.log----------1번그룹 파일 경로

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

 siz: 0x19000 seq: 0x00000010 hws: 0x5 bsz: 512 nab: 0xcdd1 flg: 0x0 dup: 1

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

 Low scn: 0x0000.00132537 07/12/2018 09:16:07

 Next scn: 0x0000.0013c11e 07/13/2018 09:17:53

LOG FILE #2:

  name #2: /u01/app/oracle/oradata/orcl/redo02.log ------------2번그룹 파일경로

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

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

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

 Low scn: 0x0000.0013c11e 07/13/2018 09:17:53

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

LOG FILE #3:

  name #1: /u01/app/oracle/oradata/orcl/redo03.log ---------3번그룹 파일 경로

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

 siz: 0x19000 seq: 0x0000000f hws: 0x6 bsz: 512 nab: 0xc263 flg: 0x0 dup: 1

 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00121f17

 Low scn: 0x0000.00128555 07/11/2018 09:07:52

 Next scn: 0x0000.00132537 07/12/2018 09:16:07

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

DATA FILE RECORDS -----Data file 관련 사항들

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

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

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

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

DATA FILE #1:

  name #7: /u01/app/oracle/oradata/orcl/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:123 scn: 0x0000.0013c11f 07/13/2018 09:17:53

 Stop scn: 0xffff.ffffffff 07/12/2018 17:55:38

5 Checkpoint Management

현재 Chkpoint SCN = 현재까지 데이터가 들어간 SCN번호

Stop SCN = Open 상태에서 trace 를 제작했을때 언제까지 SCN이 상승할수 있을지 모르기 때문에 0xfff ---무한대로 제공

DB의 정상종료시에는 Current SCN 과 Stop SCN이 모두 같게 만들고 Datafile 을 닫는다

****운영중인 DB에서 Shutdown Abort 등이 일어나면 Checkpoint SCN과 Stop SCN 이 다른상태이다***

5.1 정상 종료된 Dump 상황

1 DB 정상종료

orcl@SYS>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

2 DB Mount 상태 진입

orcl@SYS>startup mount;

ORACLE instance started.

Total System Global Area 2655657984 bytes

Fixed Size                    2256192 bytes

Variable Size                  637534912 bytes

Database Buffers         1996488704 bytes

Redo Buffers                   19378176 bytes

Database mounted.

3 Trace file destination 확인 및 identifier 설정

orcl@SYS>show parameter user_dump_dest

NAME                                     TYPE         VALUE

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

user_dump_dest                             string         /u01/app/oracle/diag/rdbms/orcl/orcl/trace

orcl@SYS>alter session set tracefile_identifier='BBB';

Session altered.

4 Trace File 생성 및 확인

orcl@SYS>oradebug setmypid;

Statement processed.

orcl@SYS>oradebug dump controlf 3;

Statement processed.

$ cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace/

$ ls -lSh *BBB*

-rw-r-----. 1 oracle oinstall 47K Jul 13 11:06 orcl_ora_3622_BBB.trc

-rw-r-----. 1 oracle oinstall  99 Jul 13 11:06 orcl_ora_3622_BBB.trm

$ vi orcl_ora_3622_BBB.trc

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

DATA FILE RECORDS ---데이터 파일들 기록상황

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

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

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

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

DATA FILE #1:

  name #7: /u01/app/oracle/oradata/orcl/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:124 scn: 0x0000.0013d499 07/13/2018 10:59:56

 Stop scn: 0x0000.0013d499 07/13/2018 10:59:56

 Creation Checkpointed at scn:  0x0000.00000007 08/24/2013 11:37:33

 DATA FILE #2:

  name #6: /u01/app/oracle/oradata/orcl/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:124 scn: 0x0000.0013d499 07/13/2018 10:59:56

 Stop scn: 0x0000.0013d499 07/13/2018 10:59:56

 Creation Checkpointed at scn:  0x0000.0000072a 08/24/2013 11:37:37

 ***정상종료시 chepoint SCN과 STOP SCN 이 동일하다***

5.2 비정상 종료된 Dump 상황

1 DB Shutdown Abort 및 DB Mount 상태 진입

orcl@SYS>shutdown abort

ORACLE instance shut down.

orcl@SYS>exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

orcl@SYS>startup mount;

ORACLE instance started.

Total System Global Area 2655657984 bytes

Fixed Size                    2256192 bytes

Variable Size                  637534912 bytes

Database Buffers         1996488704 bytes

Redo Buffers                   19378176 bytes

Database mounted.

2 Trace File identifier 설정 및 Dump File 생성 & 확인

orcl@SYS>alter session set tracefile_identifier='CCC';

Session altered.

orcl@SYS>oradebug setmypid;

Statement processed.

orcl@SYS>oradebug dump controlf 3;

Statement processed.

$ ls -lSh *CCC*

-rw-r-----. 1 oracle oinstall 47K Jul 13 11:33 orcl_ora_4013_CCC.trc

-rw-r-----. 1 oracle oinstall 109 Jul 13 11:33 orcl_ora_4013_CCC.trm

3 Trace File 확인

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

DATA FILE RECORDS ---DATA File 확인

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

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

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

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

DATA FILE #1:

  name #7: /u01/app/oracle/oradata/orcl/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:125 scn: 0x0000.0013d49c 07/13/2018 11:18:21

 Stop scn: 0xffff.ffffffff 07/13/2018 10:59:56

 Creation Checkpointed at scn:  0x0000.00000007 08/24/2013 11:37:33

 thread:0 rba:(0x0.0.0)

DATA FILE #2:

  name #6: /u01/app/oracle/oradata/orcl/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:125 scn: 0x0000.0013d49c 07/13/2018 11:18:21

 Stop scn: 0xffff.ffffffff 07/13/2018 10:59:56

 Creation Checkpointed at scn:  0x0000.0000072a 08/24/2013 11:37:37

 thread:0 rba:(0x0.0.0)

 enabled  threads:  00000000 00000000 00000000 00000000 00000000 00000000

 enabled  threads:  00000000 00000000 00000000 00000000 00000000 00000000

***stop scn 이 0xfffff인건 비정상 종료를 의미***

4 복구방법

startup시에 data file records부분의 cheekpoint cnt 번호화 scn을 확인하고

같으면 정상 , 다르면 비정상 종료 (instance crash) 상태라고 판단하고 복구작업시작 ,만약 여기가다르면

비정상이다 , 하지만 같다고해서 정상은 아닌게 실제 데이터파일의 cheekpoint scn과 conrtrol file의 scn은 다를수있기때문데

따라서 먼저 이걸 확인하고 같을때는 각 데이터파일의 헤더의 shkpoint scn이 controlfile  헤더의

번호와 같은지 확인하고 다르면 복구를 시도한다

복구시도시 부족한 파일의 리스트중  scn이 가장 낮은것을 확인후 control file 안의 logfile record 를 찾아간다

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

LOG FILE RECORDS

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

 (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: /u01/app/oracle/oradata/orcl/redo01.log

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

 siz: 0x19000 seq: 0x00000010 hws: 0x6 bsz: 512 nab: 0xcdd1 flg: 0x0 dup: 1

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

 Low scn: 0x0000.00132537 07/12/2018 09:16:07

 Next scn: 0x0000.0013c11e 07/13/2018 09:17:53

LOG FILE #2:

  name #2: /u01/app/oracle/oradata/orcl/redo02.log

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

 siz: 0x19000 seq: 0x00000011 hws: 0x5 bsz: 512 nab: 0xffffffff flg: 0x8 dup: 1

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

 Low scn: 0x0000.0013c11e 07/13/2018 09:17:53

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

LOG FILE #3:

  name #1: /u01/app/oracle/oradata/orcl/redo03.log

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

 siz: 0x19000 seq: 0x0000000f hws: 0x7 bsz: 512 nab: 0xc263 flg: 0x0 dup: 1

 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00121f17

 Low scn: 0x0000.00128555 07/11/2018 09:07:52

 Next scn: 0x0000.00132537 07/12/2018 09:16:07

Instance Recovery

low scan 부분을 찾아서 필요한 내용이 들어있는 파일을 찾아낸다

알맞는 file 을 찾게되면 가장낮은 scn 번호부터 roll forward 하고

commit 되지않은 transaction을 찾기위해 undo를 찾아서 roll back하게됨

Instance Recovery

database가 비정상 종료 이후 startup 시에 자동으로 recovery하는것

이때는 online redo log만 사용됨

Media Recovery

file 제거 , disk fail 작업이 발생해서 수동으로 Backup 된 파일 ,Archive Log File로

복구하는과정