본문 바로가기
Oracle/백업&복구

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

by 취미툰 2020. 1. 9.
반응형

DBOpen 상태의 경우 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 fileinactive상태로 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

 

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가 됩니다.

 

복구방법

- Current의 경우 alter database clear 명령어가 적용되지 않습니다. 아래와 같은 에러를 발생시키며 되지 않습니다. 따라서 alter system switch logfile을 통해 Current 상태인 log fileinactive상태로 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

 

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

반응형

댓글