RMAN - 복구 (불완전 복구)

불완전 복구도 마찬가지

장애난 시간을 찾아 그시간으로 복구, resetlogs로 open

1. Drop table 복구 - 원래 경로 사용

잘못된 drop user, undate, delete 장애들도 같은 방법으로 복구 가능

RMAN> run {

2> shutdown immediate ;

3> startup mount ;

4> sql 'alter session set nls_date_format="YYYY-MM-DD:HH24:MI:SS"' ;

5> set until time='시간' ;

6> restore database ;

7> recover database ;

8> alter database open resetlogs ;

9> }

sql 'alter session set nls_date_format="YYYY-MM-DD:HH24:MI:SS"' ; 꼭 해줘야함

시간 format이 다르기 때문에

매번 입력하기 귀찮으면 .bash_profile에 

export NLS_DATE_FORMAT='YYYY-MM-DD:HH24:MI:SS' 

를 입력해 주면 된다.

2. Drop table 복구 - 임시 경로 사용

필요한 파일들만 복원하고 나머지는 복원하지 않는 방법

잊지 말것들은

시간 format 바꿔주기

control file, redo log file 현재 쓰고 있는 것 사용해서 rename 작업해주기

RMAN> run {

2> sql "alter database rename file ''/data2/vol3/redo01_a.log''

3> to ''/data/temp/redo01_a.log''";

4> sql "alter database rename file ''/data2/vol3/redo02_a.log''

5> to ''/data/temp/redo02_a.log''";

6> sql "alter database rename file ''/data2/vol3/redo03_a.log''

7> to ''/data/temp/redo03_a.log''";

8> sql "alter database rename file ''/data2/vol5/redo01_c.log''

9> to ''/data/temp/redo01_c.log''";

10> sql "alter database rename file ''/data2/vol5/redo02_c.log''

11> to ''/data/temp/redo02_c.log''";

12> sql "alter database rename file ''/data2/vol5/redo03_c.log''

13> to ''/data/temp/redo03_c.log''";

14>

15> set newname for datafile 1 to '/data/temp/system01.dbf' ;

16> set newname for datafile 2 to '/data/temp/sysaux01.dbf' ;

17> set newname for datafile 3 to '/data/temp/undotbs01.dbf' ;

18> set newname for datafile 5 to '/data/temp/example01.dbf' ;

19> sql "alter database datafile ''/data2/vol1/users01.dbf'' offline drop" ;

20>

21> restore database skip tablespace 'users' ;

22> switch datafile all ;

23>

24> sql 'alter session set nls_date_format="YYYY-MM-DD:HH24:MI:SS"' ;

25> set until time='2013-02-19:09:57:57' ;

26>

27> recover database skip tablespace 'users' ;

28> alter database open resetlogs ;

29> }

3. Drop table 복구 - 증분 백업 파일 활용

RMAN> run {

2> startup mount ;

3> sql "alter database rename file ''/data2/vol3/redo01_a.log'' to ''/data/temp/redo01_a.log''";

4> sql "alter database rename file ''/data2/vol3/redo02_a.log'' to ''/data/temp/redo02_a.log''";

5> sql "alter database rename file ''/data2/vol3/redo03_a.log'' to ''/data/temp/redo03_a.log''";

6> sql "alter database rename file ''/data2/vol5/redo01_c.log'' to ''/data/temp/redo01_c.log''";

7> sql "alter database rename file ''/data2/vol5/redo02_c.log'' to ''/data/temp/redo02_c.log''";

8> sql "alter database rename file ''/data2/vol5/redo03_c.log'' to ''/data/temp/redo03_c.log''";

9>

10> set newname for datafile 1 to '/data/temp/system01.dbf' ;

11> set newname for datafile 2 to '/data/temp/sysaux01.dbf' ;

12> set newname for datafile 3 to '/data/temp/undotbs01.dbf' ;

13> set newname for datafile 5 to '/data/temp/example01.dbf' ;

14> sql "alter database datafile ''/data2/vol1/users01.dbf'' offline drop" ;

15>

16> restore database skip tablespace 'users' ;

17> switch datafile all ;

18>

19> sql 'alter session set nls_date_format="YYYY-MM-DD:HH24:MI:SS"' ;

20> set until time='2013-02-19:10:38:23' ;

21>

22> recover database skip tablespace 'users' ;

23> alter database open resetlogs ;

24> }

별거 없음 똑같음

4. Drop tablespace 복구하기

RMAN> run {

2> shutdown immediate ;

3> startup nomount ;

4> sql 'alter session set nls_date_format="YYYY-MM-DD:HH24:MI:SS"' ;

5> restore controlfile from '/data2/backup/rman/02o2bu90_1_1_20130219' ;

6>

7> alter database mount ;

8> sql 'alter session set nls_date_format="YYYY-MM-DD:HH24:MI:SS"' ;

9> set until time='2013-02-19:11:31:42' ;

10>

11> restore database ;

12> recover database ;

13> }

여기에 마지막에 sql 'alter database open resetlogs' ;

추가 해도 됨

startup nomount ;

restore controlfile from '/data2/backup/rman/02o2bu90_1_1_20130219' ;

--> drop tablespace 장애는 control file도 백업파일을 갖고와서 복구하기 때문에

nomount로 시작, 백업 control file을 restore 한다.

해당 데이터파일 백업파일이 없을 경우는 

해당 데이터파일 set newname 작업 대신 

sql "alter database create datafile ''경로'' as ''새경로''" ; 작업을 추가하면 된다.

똑같은 장애 임시경로에서 복구하기

우선 redo log file 현재 쓰고 있는 것 복사

parameter file에서 control file 경로 바꿔주고

control file 이랑, data file은 백업 파일 restore

장애 시간 전으로 복구

RMAN> run {

2> startup nomount ;

3> sql 'alter session set nls_date_format="YYYY-MM-DD:HH24:MI:SS"' ;

4> restore controlfile from '/data2/backup/rman/02o2d5aa_1_1_20130219' ;

5>

6> alter database mount ;

7> sql "alter database rename file ''/data2/vol3/redo01_a.log'' to ''/data/temp/redo01_a.log''";

8> sql "alter database rename file ''/data2/vol3/redo02_a.log'' to ''/data/temp/redo02_a.log''";

9> sql "alter database rename file ''/data2/vol3/redo03_a.log'' to ''/data/temp/redo03_a.log''";

10> sql "alter database rename file ''/data2/vol4/redo01_b.log'' to ''/data/temp/redo01_b.log''";

11> sql "alter database rename file ''/data2/vol4/redo02_b.log'' to ''/data/temp/redo02_b.log''";

12> sql "alter database rename file ''/data2/vol4/redo03_b.log'' to ''/data/temp/redo03_b.log''";

13>

14> set newname for datafile 1 to '/data/temp/system01.dbf' ;

15> set newname for datafile 2 to '/data/temp/sysaux01.dbf' ;

16> set newname for datafile 3 to '/data/temp/undotbs01.dbf' ;

17> set newname for datafile 6 to '/data/temp/test01.dbf' ;

18> sql "alter database datafile ''/data2/vol1/users01.dbf'' offline drop" ;

19> sql "alter database datafile ''/data2/vol1/example01.dbf'' offline drop" ;

20>

21> restore database skip tablespace 'users','example' ;

22> switch datafile all ;

23>

24> sql 'alter session set nls_date_format="YYYY-MM-DD:HH24:MI:SS"' ;

25> set until time='2013-02-19:22:45:03' ;

26>

27> recover database skip tablespace 'users','example' ;

28> alter database open resetlogs ;

29> }