오라클] 온라인 백업중 장애(end backup 안되었을 때)

2019.02.27 07:00




매일 밤 실행되는 DB서버 온라인 백업이 fail되었다. 


원인은 백업서버 HBA카드쪽 장애. 


서버에서 디바이스 날렸다가 다시 잡았더니 장애는 해결되었다. 




ㅇ 백업서버 HBA카드 장애 


1
2
3
4
5
6
7
8
9
10
11
12
13
[backup:/] errpt
 
ID         시간 소인  T C 자원 이름      설명
 
D1E21BA3   0225102719 I S errdemon       요청된 크기로 확장된 로그 파일
5537AC5F   0225102719 P H rmt10          테이프 드라이브 장애
B8FBD189   0225102719 T S fscsi3         소프트웨어 프로그램 오류
5537AC5F   0225102619 P H rmt9           테이프 드라이브 장애
B8FBD189   0225102619 T S fscsi3         소프트웨어 프로그램 오류
5537AC5F   0225102619 P H rmt10          테이프 드라이브 장애
B8FBD189   0225102619 T S fscsi3         소프트웨어 프로그램 오류
5537AC5F   0225102619 P H rmt9           테이프 드라이브 장애
......
cs



정작 문제는 HBA카드 장애 때문에 DB온라인 백업이 fail되었다는 점.


DB의 alert로그를 확인해보니 begin backup은 찍혔는데

end backup이 없다. 



1
2
3
4
5
6
7
8
9
10
11
12
13
dev@oradev:/oradev/app/oracle/admin/DEV/bdump>tail -100 alert_DEV.log
 
 
Mon Feb 25 08:46:56 2019
alter tablespace DEV_INDEX01 begin backup
Mon Feb 25 08:46:57 2019
ARC0: Completed archiving  log 3 thread 1 sequence 325070
Mon Feb 25 08:47:00 2019
Completed: alter tablespace DEV_INDEX01 begin backup   ==>> end backup 없음
Mon Feb 25 09:01:47 2019
Thread 1 advanced to log sequence 325074
 
 
cs



** begin 백업으로 시작한 백업이 종료되지 않으면 shutdown normal/immediate 안되고 

  shutdown abort로만 종료 가능하다고 한다. 

  (이 경우 DB 재시작시 복구 process가 필요하다는 의미이고)




그래서 현재 백업중인 테이블스페이스가 어떤 것들인지 v$backup을 조회해 보았다. 




1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
SQL> col TABLESPACE_NAME format a20
SQL> 
SQL> select a.tablespace_name, b.*
  2  from dba_data_files a, v$backup b
  3  where a.file_id = b.file#;
 
TABLESPACE_NAME           FILE# STATUS                CHANGE# TIME
-------------------- ---------- ------------------ ---------- ---------
SYSTEM                        1 NOT ACTIVE         6.0503E+12 24-FEB-19
UNDOTBS1                      2 NOT ACTIVE         6.0504E+12 24-FEB-19
INDX                          3 NOT ACTIVE         6.0504E+12 24-FEB-19
TOOLS                         4 NOT ACTIVE         6.0505E+12 25-FEB-19
USERS                         5 NOT ACTIVE         6.0505E+12 25-FEB-19
DEV_DATA01                   6 NOT ACTIVE         6.0506E+12 25-FEB-19
DEV_DATA01                   7 NOT ACTIVE         6.0506E+12 25-FEB-19
DEV_DATA01                   8 NOT ACTIVE         6.0506E+12 25-FEB-19
DEV_DATA01                   9 NOT ACTIVE         6.0506E+12 25-FEB-19
DEV_DATA01                  10 NOT ACTIVE         6.0506E+12 25-FEB-19
DEV_DATA01                  11 NOT ACTIVE         6.0506E+12 25-FEB-19
DEV_DATA01                  12 NOT ACTIVE         6.0506E+12 25-FEB-19
DEV_DATA01                  13 NOT ACTIVE         6.0506E+12 25-FEB-19
DEV_INDEX01                 14 ACTIVE             6.0513E+12 25-FEB-19
DEV_INDEX01                 15 ACTIVE             6.0513E+12 25-FEB-19
DEV_INDEX01                 16 ACTIVE             6.0513E+12 25-FEB-19
DEV_INDEX01                 17 ACTIVE             6.0513E+12 25-FEB-19
DEV_INDEX01                 18 ACTIVE             6.0513E+12 25-FEB-19
DEV_INDEX01                 19 ACTIVE             6.0513E+12 25-FEB-19
....
cs



DEV_INDEX01 테이블스페이스만 백업중이다(ACTIVE)




강제로 end backup 시켜준다. 



1
2
3
SQL> alter tablespace DEV_INDEX01 end backup;
 
Tablespace altered.
cs




이제 alert log에도 end backup이 찍혔고, v$backup도 전부 not active로 바뀌었다. 


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
dev@oradev:/oradev/app/oracle/admin/DEV/bdump>tail -100 alert_DEV.log
 
Mon Feb 25 10:31:54 2019
alter tablespace DEV_INDEX01 end backup
Mon Feb 25 10:31:54 2019
Completed: alter tablespace DEV_INDEX01 end backup
 
 
SQL> col TABLESPACE_NAME format a20
SQL> 
SQL> select a.tablespace_name, b.*
  2  from dba_data_files a, v$backup b
  3  where a.file_id = b.file#;
 
TABLESPACE_NAME           FILE# STATUS                CHANGE# TIME
-------------------- ---------- ------------------ ---------- ---------
SYSTEM                        1 NOT ACTIVE         6.0503E+12 24-FEB-19
UNDOTBS1                      2 NOT ACTIVE         6.0504E+12 24-FEB-19
INDX                          3 NOT ACTIVE         6.0504E+12 24-FEB-19
TOOLS                         4 NOT ACTIVE         6.0505E+12 25-FEB-19
USERS                         5 NOT ACTIVE         6.0505E+12 25-FEB-19
DEV_DATA01                   6 NOT ACTIVE         6.0506E+12 25-FEB-19
DEV_DATA01                   7 NOT ACTIVE         6.0506E+12 25-FEB-19
DEV_DATA01                   8 NOT ACTIVE         6.0506E+12 25-FEB-19
DEV_DATA01                   9 NOT ACTIVE         6.0506E+12 25-FEB-19
DEV_DATA01                  10 NOT ACTIVE         6.0506E+12 25-FEB-19
DEV_DATA01                  11 NOT ACTIVE         6.0506E+12 25-FEB-19
DEV_DATA01                  12 NOT ACTIVE         6.0506E+12 25-FEB-19
DEV_DATA01                  13 NOT ACTIVE         6.0506E+12 25-FEB-19
DEV_INDEX01                 14 NOT ACTIVE         6.0513E+12 25-FEB-19
DEV_INDEX01                 15 NOT ACTIVE         6.0513E+12 25-FEB-19
DEV_INDEX01                 16 NOT ACTIVE         6.0513E+12 25-FEB-19
DEV_INDEX01                 17 NOT ACTIVE         6.0513E+12 25-FEB-19
DEV_INDEX01                 18 NOT ACTIVE         6.0513E+12 25-FEB-19
DEV_INDEX01                 19 NOT ACTIVE         6.0513E+12 25-FEB-19
DEV_INDEX01                 20 NOT ACTIVE         6.0513E+12 25-FEB-19
 
cs


또루아빠 SQL(오라클,mysql) , , , , , , ,