안녕하세요. OCM 카페쥔장 민연홍입니다.

http://cafe.daum.net/oraclemanager

실제로 발생했던 사항을 가지고 한번 복구 테스트를 해보지요.

한번 offline backup을 수행 했으나.. 에러로 인해서 FAIL나고 스크립트에 위해서

자동기동..

백업 관리자는 FAIL을 찾지 못하고 두번째 백업을 했으나 또 일부만 백업됨..

그러나 1차백업 및 2차백업을 restore한 상황

실환경 몇테라짜리 DSS환경 noarchive log mode DB 에서 일어났었던 상황이라고 합니다.

이런 경우 어떻게 복구할지 알아두면 좋겠지요.

백업을 두번해서 복구하는 방법
PROD DB의 일부를 두번백업하고 RESTORE후 복구 테스트

1. 디렉토리 확인
원본DB /data1/oradata/PROD
백업할 디렉토리 /data1/oradata/PROD_BACKUP

2.백업을 두번수행
- 1차 백업후 DB기동
SQL> !mkdir /data1/oradata/PROD_BACKUP
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------

/data1/oradata/PROD/system01.dbf
/data1/oradata/PROD/undotbs.dbf
/data1/oradata/PROD/users01.dbf
/data1/oradata/PROD/data01_02.dbf

- 여기까지 1차백업 대상
/data1/oradata/PROD/data02_02.dbf
/data1/oradata/PROD/data01_03.dbf
/data1/oradata/PROD/data01_04.dbf
/data1/oradata/PROD/data01_01.dbf
/data1/oradata/PROD/data02_01.dbf
/data1/oradata/PROD/users02.dbf

- 여기까지 2차백업 대상
10 rows selected.

SQL>

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !cp /data1/oradata/PROD/system01.dbf /data1/oradata/PROD_BACKUP/

SQL> !cp /data1/oradata/PROD/undotbs.dbf /data1/oradata/PROD_BACKUP/

SQL> !cp /data1/oradata/PROD/users01.dbf /data1/oradata/PROD_BACKUP/

SQL> !cp /data1/oradata/PROD/data01_02.dbf /data1/oradata/PROD_BA

--DB를 내리고 2차 백업

SQL> startup
ORACLE instance started.

Total System Global Area 101799240 bytes
Fixed Size 456008 bytes
Variable Size 50331648 bytes
Database Buffers 50331648 bytes
Redo Buffers 679936 bytes
Database mounted.
Database opened.

트랜잭션까지 일으킴

SQL> create table kkk2 tablespace data01 as select * from dba_objects;

Table created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> !cp /data1/oradata/PROD/data02_02.dbf /data1/oradata/PROD_BACKUP

SQL> !cp /data1/oradata/PROD/data01_03.dbf /data1/oradata/PROD_BACKUP

SQL> !cp /data1/oradata/PROD/data01_04.dbf /data1/oradata/PROD_BACKUP

SQL> !cp /data1/oradata/PROD/data01_01.dbf /data1/oradata/PROD_BACKUP

SQL> !cp /data1/oradata/PROD/data02_01.dbf /data1/oradata/PROD_BACKUP

SQL> !cp /data1/oradata/PROD/users02.dbf /data1/oradata/PROD_BACKUP

- redo log까지 백업
SQL> !cp /data1/oradata/PROD/log* /data1/oradata/PROD_BACKUP'

- control file 까지 백업
SQL> !cp /data1/oradata/PROD/control* /data1/oradata/PROD_BACKUP

3. 두번 백업한 데이터를 restore

- PROD DB를 PROD_ORG로 백업
$ mv PROD PROD_ORG

- 두번백업한 PROD_BACKUP 을 PROD 로 restore
$ mv PROD_BACKUP PROD

4. DB기동을 하였으나 안됨
sqlplus '/as sysdba'

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Nov 8 17:00:11 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 101799240 bytes
Fixed Size 456008 bytes
Variable Size 50331648 bytes
Database Buffers 50331648 bytes
Redo Buffers 679936 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/data1/oradata/PROD/system01.dbf'

- alert log에 는 아래와 같이 나옴

Tue Nov 8 17:09:07 2005
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.4.0.
System parameters with non-default values:
control_files = /data1/oradata/PROD/control01.ctl, /data1/oradata/PROD/control02.ctl
compatible = 9.2.0
log_archive_start = TRUE
log_archive_dest_1 = location=/data1/oradata/archive
log_archive_format = %t_%s.arc
db_files = 1000
undo_management = AUTO
remote_login_passwordfile= EXCLUSIVE
background_dump_dest = /data1/oradata/admin/PROD/bdump
user_dump_dest = /data1/oradata/admin/PROD/udump
core_dump_dest = /data1/oradata/admmin/PROD/cdump
db_name = PROD
pga_aggregate_target = 1000000000
workarea_size_policy = auto
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
Tue Nov 8 17:09:09 2005
ARCH: STARTING ARCH PROCESSES
ARC0 started with pid=8
ARC0: Archival started
ARC1 started with pid=9
Tue Nov 8 17:09:09 2005
ARCH: STARTING ARCH PROCESSES COMPLETE
Tue Nov 8 17:09:09 2005
ARC1: Archival started
Tue Nov 8 17:09:09 2005
ARC1: Thread not mounted
Tue Nov 8 17:09:09 2005
ARC0: Thread not mounted
Tue Nov 8 17:09:09 2005
ALTER DATABASE MOUNT
Tue Nov 8 17:09:13 2005
Successful mount of redo thread 1, with mount id 4280692789.
Tue Nov 8 17:09:13 2005
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE MOUNT
Tue Nov 8 17:09:14 2005
ALTER DATABASE OPEN
ORA-1113 signalled during: ALTER DATABASE OPEN...

5. 복구상황 체크

- recover 상황 체크
복구 상황을 분석한 결과 current log에서 log switch 가 되지 않았음을 확인
v$recover_file의 CHANGE# > redo log minimum FIRST_CHANGE# 이므로 데이터파일은 복구될 수 있다.

SQL> @r
SQL>
SQL> doc
DOC> (1) current log
DOC>#
SQL> set line 150
SQL> col member format a40
SQL>
SQL> SELECT V1.GROUP#, MEMBER, V1.STATUS, SEQUENCE#, FIRST_CHANGE#
2 FROM V$LOG V1, V$LOGFILE V2
3 WHERE V1.GROUP# = V2.GROUP# ;

GROUP# MEMBER STATUS SEQUENCE# FIRST_CHANGE#
---------- ---------------------------------------- ---------------- ---------- -------------
1 /data1/oradata/PROD/log01a.log INACTIVE 62 5388299
2 /data1/oradata/PROD/log02a.log CURRENT 63 5388301
3 /data1/oradata/PROD/log03b.log INACTIVE 61 5388188

SQL>
SQL> doc
DOC> (2) datafile list for recovery
DOC>#
SQL> set line 150
SQL> col tname format a10
SQL> col file_name format a40
SQL> col error format a10
SQL>
SQL> select t.name tname, d.name file_name, r.change#,d.status, d.enabled, OFFLINE_CHANGE#,
2 ONLINE_CHANGE#, to_char(ONLINE_TIME,'RR/MM/DD:HH24:MI:SS') ONTIME
3 from v$tablespace t, v$datafile d, v$recover_file r
4 where t.ts#=d.ts# and d.file#=r.file#;

TNAME FILE_NAME CHANGE# STATUS ENABLED OFFLINE_CHANGE# ONLINE_CHANGE# ONTIME
---------- ---------------------------------------- ---------- ------- ---------- --------------- -------------- -----------------
SYSTEM /data1/oradata/PROD/system01.dbf 5388428 SYSTEM READ WRITE 889577 889578 05/05/11:18:44:27
UNDOTBS /data1/oradata/PROD/undotbs.dbf 5388428 ONLINE READ WRITE 889577 889578 05/05/11:18:44:27
USERS /data1/oradata/PROD/users01.dbf 5388428 ONLINE READ WRITE 889577 889578 05/05/11:18:44:27
DATA01 /data1/oradata/PROD/data01_02.dbf 5388428 ONLINE READ WRITE 0 0

SQL>
SQL> doc
DOC> (2) archive list for recovery
DOC>#
SQL> col name format a40
SQL> select a.name, a.sequence# , a.first_change#, to_char(a.first_time,'YYYY/MM/DD:HH24:MI:SS') fst_time,
2 to_char(a.next_time,'YYYY/MM/DD:HH24:MI:SS') next_time
3 from v$archived_log a, v$recovery_log r
4 where a.sequence#=r.sequence#;

no rows selected

SQL>
SQL>
SQL> doc
DOC> (3) tablespace & file status
DOC>#
SQL> select t.name, d.name, d.status, d.enabled, h.error header_err
2 from v$tablespace t, v$datafile d, v$datafile_header h
3 where t.ts#=d.ts# and h.file#=d.file#;

NAME NAME STATUS ENABLED HEADER_ERR
---------------------------------------- ---------------------------------------- ------- ---------- ------------------
SYSTEM /data1/oradata/PROD/system01.dbf SYSTEM READ WRITE
UNDOTBS /data1/oradata/PROD/undotbs.dbf ONLINE READ WRITE
USERS /data1/oradata/PROD/users01.dbf ONLINE READ WRITE
USERS /data1/oradata/PROD/users02.dbf ONLINE READ WRITE
DATA01 /data1/oradata/PROD/data01_02.dbf ONLINE READ WRITE
DATA01 /data1/oradata/PROD/data01_01.dbf ONLINE READ WRITE
DATA01 /data1/oradata/PROD/data01_04.dbf ONLINE READ WRITE
DATA01 /data1/oradata/PROD/data01_03.dbf ONLINE READ WRITE
DATA02 /data1/oradata/PROD/data02_02.dbf ONLINE READ WRITE
DATA02 /data1/oradata/PROD/data02_01.dbf ONLINE READ WRITE

10 rows selected.

SQL> doc
DOC> (4) recovery file status
DOC>#
SQL> select * from V$RECOVERY_FILE_STATUS ;

no rows selected

SQL> doc
DOC> (5) recovery progress
DOC>#
SQL> select * from v$recovery_progress;

no rows selected

SQL>
SQL> doc
DOC> (6) recovery status
DOC>#
SQL> select * from v$recovery_status;

no rows selected

SQL>
SQL> doc
DOC> (7) cf recovery need log check
DOC>#
SQL>
SQL> col name format a40
SQL> select name, sequence# , first_change#, to_char(first_time,'YYYY/MM/DD:HH24:MI:SS') fst_time,
2 to_char(next_time,'YYYY/MM/DD:HH24:MI:SS') next_time
3 from v$archived_log
4 where next_change# >= (select min(change#) from v$recover_file) and
5 next_change# <= (select max(change#) from v$recover_file);

no rows selected

6. controlfile trace를 생성 및 수정
- 컨트롤 파일 재생성 ( RESETLOGS NOARCHIVELOG 로 변경)

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS NOARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 3
MAXLOGHISTORY 337
LOGFILE
GROUP 1 '/data1/oradata/PROD/log01a.log' SIZE 50M,
GROUP 2 '/data1/oradata/PROD/log02a.log' SIZE 50M,
GROUP 3 '/data1/oradata/PROD/log03b.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/data1/oradata/PROD/system01.dbf',
'/data1/oradata/PROD/undotbs.dbf',
'/data1/oradata/PROD/users01.dbf',
'/data1/oradata/PROD/data01_02.dbf',
'/data1/oradata/PROD/data02_02.dbf',
'/data1/oradata/PROD/data01_03.dbf',
'/data1/oradata/PROD/data01_04.dbf',
'/data1/oradata/PROD/data01_01.dbf',
'/data1/oradata/PROD/data02_01.dbf',
'/data1/oradata/PROD/users02.dbf'
CHARACTER SET KO16KSC5601
;

- 복구 상황 체크
SQL> @r
DOC>(1) current log
DOC>#

GROUP# MEMBER STATUS SEQUENCE# FIRST_CHANGE#
---------- ---------------------------------------- ---------------- ---------- -------------
1 /data1/oradata/PROD/log01a.log UNUSED 0 0
2 /data1/oradata/PROD/log02a.log UNUSED 0 0
3 /data1/oradata/PROD/log03b.log INVALIDATED 0 0

DOC> (2) datafile list for recovery
DOC>#

TNAME FILE_NAME CHANGE# STATUS ENABLED OFFLINE_CHANGE# ONLINE_CHANGE# ONTIME
---------- ---------------------------------------- ---------- ------- ---------- --------------- -------------- -----------------
SYSTEM /data1/oradata/PROD/system01.dbf 5388428 SYSTEM DISABLED 0 0
UNDOTBS /data1/oradata/PROD/undotbs.dbf 5388428 RECOVER DISABLED 0 0
USERS /data1/oradata/PROD/users01.dbf 5388428 RECOVER DISABLED 0 0
USERS /data1/oradata/PROD/users02.dbf 5388598 RECOVER DISABLED 0 0
DATA01 /data1/oradata/PROD/data01_02.dbf 5388428 RECOVER DISABLED 0 0
DATA01 /data1/oradata/PROD/data01_01.dbf 5388598 RECOVER DISABLED 0 0
DATA01 /data1/oradata/PROD/data01_04.dbf 5388598 RECOVER DISABLED 0 0
DATA01 /data1/oradata/PROD/data01_03.dbf 5388598 RECOVER DISABLED 0 0
DATA02 /data1/oradata/PROD/data02_02.dbf 5388598 RECOVER DISABLED 0 0
DATA02 /data1/oradata/PROD/data02_01.dbf 5388598 RECOVER DISABLED 0 0

10 rows selected.

DOC> (2) archive list for recovery
DOC>#

no rows selected

DOC> (3) tablespace & file status
DOC>#

NAME NAME STATUS ENABLED HEADER_ERR
---------------------------------------- ---------------------------------------- ------- ---------- ------------------
SYSTEM /data1/oradata/PROD/system01.dbf SYSTEM DISABLED
UNDOTBS /data1/oradata/PROD/undotbs.dbf RECOVER DISABLED
USERS /data1/oradata/PROD/users01.dbf RECOVER DISABLED
USERS /data1/oradata/PROD/users02.dbf RECOVER DISABLED
DATA01 /data1/oradata/PROD/data01_02.dbf RECOVER DISABLED
DATA01 /data1/oradata/PROD/data01_01.dbf RECOVER DISABLED
DATA01 /data1/oradata/PROD/data01_04.dbf RECOVER DISABLED
DATA01 /data1/oradata/PROD/data01_03.dbf RECOVER DISABLED
DATA02 /data1/oradata/PROD/data02_02.dbf RECOVER DISABLED
DATA02 /data1/oradata/PROD/data02_01.dbf RECOVER DISABLED

10 rows selected.

DOC> (4) recovery file status
DOC>#

no rows selected

DOC> (5) recovery progress
DOC>#

no rows selected

DOC> (6) recovery status
DOC>#

no rows selected

DOC> (7) cf recovery need log check
DOC>#

no rows selected

7. _allow_resetlogs_corruption=true

- db를 올릴려고 하면 에러가 난다.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/data1/oradata/PROD/system01.dbf'

- init파라미터에 _allow_resetlogs_corruption=true 로 설정후 재기동 하고 resetlogs open 한다.

SQL> shutdown abort
sORACLE instance shut down.
SQL> tartup mount
ORACLE instance started.

Total System Global Area 101799240 bytes
Fixed Size 456008 bytes
Variable Size 50331648 bytes
Database Buffers 50331648 bytes
Redo Buffers 679936 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.

8. alert log의 scn을 체크

- resetlog open을 하고 운영하다가 DB가 깨지면

resetlogs 이전의 DB백업으로 restore후 복구해야겠지요.

recover database until scn XXXX 에서 XXX는 아래의 UNTIL CHANGE 의

번호를 써야만 복구가 됩니다. 단.. 주의할 것은 현재의 controlfile을 백업한 후에

resetlogs open하기전의 컨트롤파일을 재생성해주거나 restore하구요.

그리고 resetlogs open하고 나서 현재의 controlfile 을 restore후 복구해주어야

합니다.;;;

alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 5388598
Resetting resetlogs activation ID 0 (0x0)
Tue Nov 8 17:53:40 2005
Assigning activation ID 4280668818 (0xff25d292)
Thread 1 opened at log sequence 1
Current log# 3 seq# 1 mem# 0: /data1/oradata/PROD/log03b.log
Successful open of redo thread 1.
Tue Nov 8 17:53:40 2005