|
안녕하세요. OCM 카페쥔장 민연홍이랍니다.
http://cafe.daum.net/oraclemanager
SCN이 틀렸을 경우에 복구하는 방법인데요.
정말 위급한 복구 상황에 닥쳤을 경우에 유용하겠지요.
일반적인 복구의 개념이 아니라..
어거지로 어떻게든 복구해서 올려야 하는 상황에 사용하시길
바랍니다. 꼭 정말 위험한 상황에서 사용해야지.
일반적인 복구 상황에서 사용하시면 안됩니다.
아래에서.. 복구 작업이 끝난이후 꼭 해야 하는 것은..
DB재생성후 export후 import 하는 것이
권장된답니다.
그리고 tempfile 추가하는 것도 잊지 말구요.
undo도 깨뜨리고 하니 새로 생성하셔야 합니다.
최악의 복구시나리오시에.. 그래도 해야 할 것은.. system
tablespace는 무조건 백업해두어야 합니다. system tablespace가 깨지면
'dul' 유틸을 통해서 블록을 읽으는 복구도 불가능하다고 하네요.
system tablespace의 bootstrap이 깨지면 너무 오래된 백업이라면
object를 생성한 dictionary 정보가 없으니 당연히
못쓰겠지요..
아래에서도 마지막에 만든 k4 라는 테이블은 데이터는
있는데
테이블은 dictionary 정보에 없는 기이한 일이 될 수도
있겠지요.
system을 먼저 cp 백업하고. 테이블만들고 datafile을
백업한다면요..
_corrupted_rollback_segments=true =>
rollback segment가 깨졌음, commit을 했다고 오라클을 속임,
_offline_rollback_segments 강제로 rollback segment를 offline
시킴(_corrupted~, _offline~ 은 같이사용
_allow_resetlogs_corruption=true
=>redo log 의 한계범위 내에서 데이터파일의 SCN이 틀어져 있거나
또는 recover시에 archive를 찾지 못해서 fail났을 경우, 즉 current
redo log를 통해서 복구가 불가능할 경우, 억지로 current redo log를
reset log 시키고 open하는 파라미터
ORA- 10501 event를 통해서 adjust scn은 아래와
같은 레벨이 깨졌을
경우
레벨을올려가면서 복구하는
방법입니다.
10501, 00000, "periodically check selected
heap"
// *Cause:
// *Action:
// Level: 0x01 PGA
// 0x02 SGA
// 0x04 UGA
// 0x08 current call
// 0x10 user call
// 0x20 large allocation pool
begin backup을 하지 않고 cp로 복사해서 백업을 했을 경우 복구
방법 , archive도 없는 상태이며 SCN이 모두 틀림
1. 트랜잭션이 있는 상태에서 cp로 백업
vi kkk.sql
create table k1 tablespace data02 as select * from
dba_segments;
insert into k1 select * from k1;
commit;
create table k2 tablespace data01 as select * from
dba_objects;
insert into k2 select * from k2;
insert into k1 select * from k1;
commit;
create table k3 tablespace users as select * from
dba_extents;
insert into k3 select * from k3;
commit;
vi kkk2.sql
insert into k1 select * from k1;
insert into k2 select * from k2;
insert into k3 select * from k3;
commit;
SQL> @kkk
@kkk2
@kkk2
@kkk2
@kkk2
@kkk2
@kkk2
@kkk2
SQL> !cp /data1/oradata/PROD/*.dbf PROD_CP
SQL> alter database backup controlfile to
'/data1/oradata/PROD_CP/control01.ctl';
SQL> !cp /data1/oradata/PROD/system01.dbf PROD_CP
SQL> create table k4 tablespace users as select * from
dba_extents;
- archive도 없음
mv archive archive3
2. cp로 백업한 것을 restore함(datafile, controlfile만 존재, redo
log는 없음)
mv PROD PROD_CP_ORG
mv PROD_CP PROD
3. recovery status 확인
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-01589: must use RESETLOGS or NORESETLOGS option for database
open
4. 복구 시도
archive 가 없으므로 controlfile trace에서 RESETLOGS ARCHIVELOG
=> RESETLOGS NOARCHIVELOG로 변경
(1) 일반적인 복구 시도했으나 archive가 없음
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be
done
SQL> recover database until cancel using backup
controlfile;
ORA-00279: change 5531180 generated at 11/11/2005 00:20:07 needed
for thread 1
ORA-00289: suggestion : /data1/oradata/archive/1_37.arc
ORA-00280: change 5531180 for thread 1 is in sequence #37
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log
'/data1/oradata/archive/1_37.arc'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get
error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/data1/oradata/PROD/system01.dbf'
(2) resetlog open도 불가능함
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/data1/oradata/PROD/system01.dbf'
(3) hidden parameter 적용
initPROD.ora 파일에 아래를 추가
_offline_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3
$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)
_corrupted_rollback_segments=true
_allow_resetlogs_corruption=true
(4) controlfile 재생성
SQL> alter database backup controlfile to trace;
SQL> show parameter user_dump_dest
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
user_dump_dest string
/data1/oradata/admin/PROD/udump
- 트레이스에서 RESETLOGS ARCHIVELOG => RESETLOGS NOARCHIVELOG
로 변경
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;
(5) DB재기동후 controlfile 재생성
SQL> startup nomount
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
SQL>
SQL> CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS
NOARCHIVELOG
2 -- SET STANDBY TO MAXIMIZE PERFORMANCE
3 MAXLOGFILES 16
4 MAXLOGMEMBERS 2
5 MAXDATAFILES 30
6 MAXINSTANCES 3
7 MAXLOGHISTORY 337
8 LOGFILE
9 GROUP 1 '/data1/oradata/PROD/log01a.log' SIZE 50M,
10 GROUP 2 '/data1/oradata/PROD/log02a.log' SIZE 50M,
11 GROUP 3 '/data1/oradata/PROD/log03b.log' SIZE 50M
12 -- STANDBY LOGFILE
13 DATAFILE
14 '/data1/oradata/PROD/system01.dbf',
15 '/data1/oradata/PROD/undotbs.dbf',
16 '/data1/oradata/PROD/users01.dbf',
17 '/data1/oradata/PROD/data01_02.dbf',
18 '/data1/oradata/PROD/data02_02.dbf',
19 '/data1/oradata/PROD/data01_03.dbf',
20 '/data1/oradata/PROD/data01_04.dbf',
21 '/data1/oradata/PROD/data01_01.dbf',
22 '/data1/oradata/PROD/data02_01.dbf',
23 '/data1/oradata/PROD/users02.dbf'
24 CHARACTER SET KO16KSC5601;
Control file created.
(6) db를 resetlogs open하려하나 DB가 DOWN 됨
SQL> recover database until cancel using backup
controlfile;
ORA-00279: change 5531180 generated at 11/11/2005 00:20:07 needed
for thread 1
ORA-00289: suggestion : /data1/oradata/archive/1_37.arc
ORA-00280: change 5531180 for thread 1 is in sequence #37
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get
error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/data1/oradata/PROD/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
(7) alert log 분석
- system tablespace에서 메모리를 관리할 때 오라클 내부적으로
사용하는 bootstrap 깨짐
- 600 [4000] 에러가 발생, hidden parameter 을 써서 rollback
semgent를 offline , corrupt
시켰기 때문에 발생하는 것으로 판단됨
ARC1: Media recovery disabled
Fri Nov 11 00:48:00 2005
Errors in file
/data1/oradata/admin/PROD/udump/prod_ora_12450.trc:
ORA-00600: internal error code, arguments: [4000], [5], [], [], [],
[], [], []
Fri Nov 11 00:48:04 2005
Errors in file
/data1/oradata/admin/PROD/udump/prod_ora_12450.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [4000], [5], [], [], [],
[], [], []
Fri Nov 11 00:48:04 2005
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 12450
ORA-1092 signalled during: alter database open resetlogs...
(8) DB재기동 및 controlfile , redo를 모두 지움, controlfile
재생성
SQL> startup nomount
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
SQL> !rm /data1/oradata/PROD/*.log
SQL> !rm /data1/oradata/PROD/*.ctl
SQL>
SQL> CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS
NOARCHIVELOG
2 -- SET STANDBY TO MAXIMIZE PERFORMANCE
3 MAXLOGFILES 16
4 MAXLOGMEMBERS 2
5 MAXDATAFILES 30
6 MAXINSTANCES 3
7 MAXLOGHISTORY 337
8 LOGFILE
9 GROUP 1 '/data1/oradata/PROD/log01a.log' SIZE 50M,
10 GROUP 2 '/data1/oradata/PROD/log02a.log' SIZE 50M,
11 GROUP 3 '/data1/oradata/PROD/log03b.log' SIZE 50M
12 -- STANDBY LOGFILE
13 DATAFILE
14 '/data1/oradata/PROD/system01.dbf',
15 '/data1/oradata/PROD/undotbs.dbf',
16 '/data1/oradata/PROD/users01.dbf',
17 '/data1/oradata/PROD/data01_02.dbf',
18 '/data1/oradata/PROD/data02_02.dbf',
19 '/data1/oradata/PROD/data01_03.dbf',
20 '/data1/oradata/PROD/data01_04.dbf',
21 '/data1/oradata/PROD/data01_01.dbf',
22 '/data1/oradata/PROD/data02_01.dbf',
23 '/data1/oradata/PROD/users02.dbf'
24 CHARACTER SET KO16KSC5601;
Control file created.
SQL>
SQL> alter session set events '10015 trace name adjust_scn level
1';
Session altered.
SQL> recover database until cancel using backup
controlfile;
ORA-00279: change 5531188 generated at 11/11/2005 00:58:07 needed
for thread 1
ORA-00289: suggestion : /data1/oradata/archive/1_1.arc
ORA-00280: change 5531188 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get
error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/data1/oradata/PROD/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
(9) adjust_scn level을 늘려가면서 재시도
QL> startup 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>
SQL> alter session set events '10015 trace name adjust_scn level
2';
Session altered.
SQL> recover database;
Media recovery complete.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database
recovery
SQL> alter database open;
Database altered.
|
