DB Open & No Archive Mode상태에서 복구

DB가 Open 상태의 경우 alter database clear unarchived logfile group n의 명령어를 통해서 복구할 수 있습니다.

Current 멤버 손상 복구

▶ 리두로그 파일 확인

sql> select group#,sequence#,members,status from v$log;

GROUP# SEQUENCE# MEMBERS STATUS

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

1 1134 2 INACTIVE

2 1136 2 INACTIVE

3 1137 2 CURRENT

4 1135 2 INACTIVE

4 rows selected.

sql> select * from v$logfile order by 1;

GROUP# STATUS TYPE MEMBER IS_

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

1 (null) ONLINE /app/oracle/oradata/ysbae/redo01.log NO

1 (null) ONLINE /app/oracle/oradata/ysbae/redo01_02.log NO

2 (null) ONLINE /app/oracle/oradata/ysbae/redo02.log NO

2 (null) ONLINE /app/oracle/oradata/ysbae/redo02_02.log NO

3 (null) ONLINE /app/oracle/oradata/ysbae/redo03_02.log NO

3 (null) ONLINE /app/oracle/oradata/ysbae/redo03.log NO

4 (null) ONLINE /app/oracle/oradata/ysbae/redo04_02.log NO

4 (null) ONLINE /app/oracle/oradata/ysbae/redo04.log NO

8 rows selected.

-3번이 current이고 2개의 멤버를 가지고 있습니다.

▶ redo.sql파일을 생성하여 redo의 상태를 쉽게 파악합니다.

$vi redo.sql

Col member for a30

select a.group#,a.type,a.member,b.status,b.sequence#

from v$logfile a, v$log b

where a.group#=b.group#

order by 1;

▶ redo03_02.log를 물리적으로 삭제한다.

sql> !rm /app/oracle/oradata/ysbae/redo03_02.log

sql> !ls /app/oracle/oradata/ysbae/redo*

/app/oracle/oradata/ysbae/redo01_02.log /app/oracle/oradata/ysbae/redo02_02.log /app/oracle/oradata/ysbae/redo03.log /app/oracle/oradata/ysbae/redo04.log

/app/oracle/oradata/ysbae/redo01.log /app/oracle/oradata/ysbae/redo02.log /app/oracle/oradata/ysbae/redo04_02.log

▶ log switch를 발생시켜 에러 발생여부를 확인합니다.

sql> alter system switch logfile;

System altered.

총 4번 수행

-멤버가 삭제되었을때에는 이상없이 작동합니다.

▶ alert log를 확인합니다.

Thread 1 advanced to log sequence 1138 (LGWR switch)

Current log# 1 seq# 1138 mem# 0: /app/oracle/oradata/ysbae/redo01.log

Current log# 1 seq# 1138 mem# 1: /app/oracle/oradata/ysbae/redo01_02.log

Thread 1 advanced to log sequence 1139 (LGWR switch)

Current log# 4 seq# 1139 mem# 0: /app/oracle/oradata/ysbae/redo04.log

Current log# 4 seq# 1139 mem# 1: /app/oracle/oradata/ysbae/redo04_02.log

Thread 1 advanced to log sequence 1140 (LGWR switch)

Current log# 2 seq# 1140 mem# 0: /app/oracle/oradata/ysbae/redo02.log

Current log# 2 seq# 1140 mem# 1: /app/oracle/oradata/ysbae/redo02_02.log

Thread 1 cannot allocate new log, sequence 1141

Checkpoint not complete

Current log# 2 seq# 1140 mem# 0: /app/oracle/oradata/ysbae/redo02.log

Current log# 2 seq# 1140 mem# 1: /app/oracle/oradata/ysbae/redo02_02.log

Thread 1 advanced to log sequence 1141 (LGWR switch)

Current log# 3 seq# 1141 mem# 0: /app/oracle/oradata/ysbae/redo03.log

Current log# 3 seq# 1141 mem# 1: /app/oracle/oradata/ysbae/redo03_02.log

…생략

- redo03_02.log가 삭제된 상태이지만 DB에서는 이상없이 작동하고 있습니다.

복구방법1

- Current의 경우 alter database clear 명령어가 적용되지 않습니다. 아래와 같은 에러를 발생시키며 되지 않습니다. 따라서 alter system switch logfile을 통해 Current 상태인 log file을 inactive상태로 switch 후 진행해야 합니다.

Currnet리두로그그룹에 clear 명령어 사용시 아래와 같은 에러가 발생함.

ERROR at line 1:

ORA-01624: log 1 needed for crash recovery of instance orcl (thread 1)

ORA-00312: online log 1 thread 1: '/app/oracle/oradata/orcl/redo01.log'

ORA-00312: online log 1 thread 1: '/app/oracle/oradata/orcl/redo01_01.log'

▶ alter database clear unarchived logfile group n를 실행한다.

sql> alter system switch logfile;

sql> alter database clear unarchived logfile group 3;

Database altered.

sql> @redo

GROUP# TYPE MEMBER STATUS SEQUENCE#

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

1 ONLINE /app/oracle/oradata/ysbae/redo01.log INACTIVE 1178

1 ONLINE /app/oracle/oradata/ysbae/redo01_02.log INACTIVE 1178 2 ONLINE /app/oracle/oradata/ysbae/redo02.log CURRENT 1181 2 ONLINE /app/oracle/oradata/ysbae/redo02_02.log CURRENT 1181

3 ONLINE /app/oracle/oradata/ysbae/redo03_02.log UNUSED 0

3 ONLINE /app/oracle/oradata/ysbae/redo03.log UNUSED 0

4 ONLINE /app/oracle/oradata/ysbae/redo04_02.log INACTIVE 1180

4 ONLINE /app/oracle/oradata/ysbae/redo04.log INACTIVE 1180

▶ 물리적으로 디렉토리를 확인한다.

sql> !ls /app/oracle/oradata/ysbae/redo*

/app/oracle/oradata/ysbae/redo01_02.log /app/oracle/oradata/ysbae/redo02_02.log /app/oracle/oradata/ysbae/redo03_02.log /app/oracle/oradata/ysbae/redo04_02.log

/app/oracle/oradata/ysbae/redo01.log /app/oracle/oradata/ysbae/redo02.log /app/oracle/oradata/ysbae/redo03.log /app/oracle/oradata/ysbae/redo04.log

- 삭제했던 redo03_02.log가 자동적으로 생성된 것을 확인 할 수 있다.

▶ log switch 후 redo 상태를 확인한다.

sql> alter system switch logfile;

4회 수행

Sql> @redo

GROUP# TYPE MEMBER STATUS SEQUENCE#

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

1 ONLINE /app/oracle/oradata/ysbae/redo01.log ACTIVE 1183

1 ONLINE /app/oracle/oradata/ysbae/redo01_02.log ACTIVE 1183

2 ONLINE /app/oracle/oradata/ysbae/redo02.log ACTIVE 1185

2 ONLINE /app/oracle/oradata/ysbae/redo02_02.log ACTIVE 1185

3 ONLINE /app/oracle/oradata/ysbae/redo03_02.log CURRENT 1182

3 ONLINE /app/oracle/oradata/ysbae/redo03.log CURRENT 1182

4 ONLINE /app/oracle/oradata/ysbae/redo04_02.log ACTIVE 1184

4 ONLINE /app/oracle/oradata/ysbae/redo04.log ACTIVE 1184

복구방법2

- redo log member 삭제의 경우와 다중화 되어있는 경우, 남아있는 redo log 파일을 물리적으로 복사하여 생성해주면 복구가 가능합니다. 같은 그룹의 redo log 파일은 크기도 같고, 안의 내용도 미러링이 되기 때문입니다.

▶ 다중화된 redo log file을 물리적으로 복사해서 생성

Sql> !cp /app/oracle/oradata/ysbae/redo03.log /app/oracle/oradata/ysbae/redo03_02.log

sql> @redo

GROUP# TYPE MEMBER STATUS SEQUENCE#

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

1 ONLINE /app/oracle/oradata/ysbae/redo01.log ACTIVE 1183

1 ONLINE /app/oracle/oradata/ysbae/redo01_02.log ACTIVE 1183

2 ONLINE /app/oracle/oradata/ysbae/redo02.log ACTIVE 1185

2 ONLINE /app/oracle/oradata/ysbae/redo02_02.log ACTIVE 1185

3 ONLINE /app/oracle/oradata/ysbae/redo03_02.log CURRENT 1182

3 ONLINE /app/oracle/oradata/ysbae/redo03.log CURRENT 1182

4 ONLINE /app/oracle/oradata/ysbae/redo04_02.log ACTIVE 1184

4 ONLINE /app/oracle/oradata/ysbae/redo04.log ACTIVE 1184

▶ 물리적으로 디렉토리를 확인

sql> !ls /app/oracle/oradata/ysbae/redo*

/app/oracle/oradata/ysbae/redo01_02.log /app/oracle/oradata/ysbae/redo02_02.log /app/oracle/oradata/ysbae/redo03_02.log /app/oracle/oradata/ysbae/redo04_02.log

/app/oracle/oradata/ysbae/redo01.log /app/oracle/oradata/ysbae/redo02.log /app/oracle/oradata/ysbae/redo03.log /app/oracle/oradata/ysbae/redo04.log

- 삭제했던 redo03_02.log를 수동으로 생성하였고, 잘 작동하는 것을 확인할 수 있습니다.

Current

그룹 손상 복구

▶ redo.sql을 실행하여 현재 리두의 상태를 파악한다.

sql> @redo

GROUP# TYPE MEMBER STATUS SEQUENCE#

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

1 ONLINE /app/oracle/oradata/ysbae/redo01.log ACTIVE 1192

1 ONLINE /app/oracle/oradata/ysbae/redo01_02.log ACTIVE 1192

2 ONLINE /app/oracle/oradata/ysbae/redo02.log ACTIVE 1193

2 ONLINE /app/oracle/oradata/ysbae/redo02_02.log ACTIVE 1193

3 ONLINE /app/oracle/oradata/ysbae/redo03_02.log CURRENT 1194

3 ONLINE /app/oracle/oradata/ysbae/redo03.log CURRENT 1194

4 ONLINE /app/oracle/oradata/ysbae/redo04_02.log ACTIVE 1191

4 ONLINE /app/oracle/oradata/ysbae/redo04.log ACTIVE 1191

8 rows selected.

- 3번 그룹이 current이고 2개의 멤버가 있습니다.

▶ redo03*.log를 물리적으로 삭제

sql> !rm -rf /app/oracle/oradata/ysbae/redo03*

sql> !ls /app/oracle/oradata/ysbae/redo*

/app/oracle/oradata/ysbae/redo01_02.log /app/oracle/oradata/ysbae/redo01.log /app/oracle/oradata/ysbae/redo02_02.log /app/oracle/oradata/ysbae/redo02.log /app/oracle/oradata/ysbae/redo04_02.log /app/oracle/oradata/ysbae/redo04.log

▶ log switch를 발생시켜 에러 발생여부를 확인

sql> alter system switch logfile;

System altered.

총 4번 수행

-그룹이 삭제되었을때는 이상없이 작동합니다.

▶ alert log를 확인

Checkpoint not complete

Current log# 3 seq# 1194 mem# 0: /app/oracle/oradata/ysbae/redo03.log

Current log# 3 seq# 1194 mem# 1: /app/oracle/oradata/ysbae/redo03_02.log

Thread 1 advanced to log sequence 1195 (LGWR switch)

Current log# 4 seq# 1195 mem# 0: /app/oracle/oradata/ysbae/redo04.log

Current log# 4 seq# 1195 mem# 1: /app/oracle/oradata/ysbae/redo04_02.log

Thread 1 cannot allocate new log, sequence 1196

Checkpoint not complete

Current log# 4 seq# 1195 mem# 0: /app/oracle/oradata/ysbae/redo04.log

Current log# 4 seq# 1195 mem# 1: /app/oracle/oradata/ysbae/redo04_02.log

Thread 1 advanced to log sequence 1196 (LGWR switch)

Current log# 1 seq# 1196 mem# 0: /app/oracle/oradata/ysbae/redo01.log

Current log# 1 seq# 1196 mem# 1: /app/oracle/oradata/ysbae/redo01_02.log

Thread 1 cannot allocate new log, sequence 1197

Checkpoint not complete

Current log# 1 seq# 1196 mem# 0: /app/oracle/oradata/ysbae/redo01.log

Current log# 1 seq# 1196 mem# 1: /app/oracle/oradata/ysbae/redo01_02.log

Thread 1 advanced to log sequence 1197 (LGWR switch)

Current log# 2 seq# 1197 mem# 0: /app/oracle/oradata/ysbae/redo02.log

Current log# 2 seq# 1197 mem# 1: /app/oracle/oradata/ysbae/redo02_02.log

Thread 1 cannot allocate new log, sequence 1198

Checkpoint not complete

Current log# 2 seq# 1197 mem# 0: /app/oracle/oradata/ysbae/redo02.log

Current log# 2 seq# 1197 mem# 1: /app/oracle/oradata/ysbae/redo02_02.log

Mon Jul 29 17:02:27 2019

Thread 1 advanced to log sequence 1198 (LGWR switch)

Current log# 3 seq# 1198 mem# 0: /app/oracle/oradata/ysbae/redo03.log

Current log# 3 seq# 1198 mem# 1: /app/oracle/oradata/ysbae/redo03_02.log

- alert log에서도 특이사항없이 log switch가 됩니다.

복구방법

▶ alter database clear unarchived logfile group n를 실행

sql> alter system switch logfile;

sql> alter database clear unarchived logfile group 3;

Database altered.

sql> @redo

GROUP# TYPE MEMBER STATUS SEQUENCE#

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

1 ONLINE /app/oracle/oradata/ysbae/redo01.log INACTIVE 1196

1 ONLINE /app/oracle/oradata/ysbae/redo01_02.log INACTIVE 1196

2 ONLINE /app/oracle/oradata/ysbae/redo02.log INACTIVE 1197

2 ONLINE /app/oracle/oradata/ysbae/redo02_02.log INACTIVE 1197

3 ONLINE /app/oracle/oradata/ysbae/redo03_02.log UNUSED 0

3 ONLINE /app/oracle/oradata/ysbae/redo03.log UNUSED 0

4 ONLINE /app/oracle/oradata/ysbae/redo04_02.log CURRENT 1199

4 ONLINE /app/oracle/oradata/ysbae/redo04.log CURRENT 1199

▶ 물리적으로 디렉토리를 확인

sql> !ls /app/oracle/oradata/ysbae/redo*

/app/oracle/oradata/ysbae/redo01_02.log /app/oracle/oradata/ysbae/redo02_02.log /app/oracle/oradata/ysbae/redo03_02.log /app/oracle/oradata/ysbae/redo04_02.log

/app/oracle/oradata/ysbae/redo01.log /app/oracle/oradata/ysbae/redo02.log /app/oracle/oradata/ysbae/redo03.log /app/oracle/oradata/ysbae/redo04.log

- 삭제했던 redo03*.log들이 자동적으로 생성된 것을 확인 할 수 있다.

▶ log switch 후 redo 상태를 확인

sql> alter system switch logfile;

4회 수행

sql> @redo

GROUP# TYPE MEMBER STATUS SEQUENCE#

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

1 ONLINE /app/oracle/oradata/ysbae/redo01.log INACTIVE 1201

1 ONLINE /app/oracle/oradata/ysbae/redo01_02.log INACTIVE 1201

2 ONLINE /app/oracle/oradata/ysbae/redo02.log INACTIVE 1202

2 ONLINE /app/oracle/oradata/ysbae/redo02_02.log INACTIVE 1202

3 ONLINE /app/oracle/oradata/ysbae/redo03_02.log INACTIVE 1200

3 ONLINE /app/oracle/oradata/ysbae/redo03.log INACTIVE 1200

4 ONLINE /app/oracle/oradata/ysbae/redo04_02.log CURRENT 1203

4 ONLINE /app/oracle/oradata/ysbae/redo04.log CURRENT 1203

-정상적으로 돌아온 것을 확인할 수 있습니다.

저작자표시

'Oracle > 백업&복구' 카테고리의 다른 글

Recyclebin사용하여 테이블 복구하기 (0)	2021.09.01
[시나리오]CloneDB 생성으로 Drop Table 복구 (0)	2020.03.24
[EXPDP/IMPDP]REMAP_DATA (0)	2020.02.04
Redo log 관리 - 리두 로그 그룹 증가 & 멤버 추가 (0)	2020.01.08

DB장이

DB Open & No Archive Mode상태에서 복구 - Current 상태

Current

'Oracle > 백업&복구' 카테고리의 다른 글

댓글

티스토리툴바

DB Open & No Archive Mode상태에서 복구 - Current 상태

Current

'Oracle > 백업&복구' 카테고리의 다른 글

관련글

댓글

티스토리툴바