'불완전백업'에 해당되는 글 1건

2012.06.02 HOT backup and archive를 이용한 recovery

HOT backup and archive를 이용한 recovery

1. IT Story/DB 2012. 6. 2. 18:01

0.TEST환경

1개의 vmwere 에서 TEST함
이전에 보낸 문서는 2개의 vmwere를 이용하여 TEST함.

1.TSET서버는 Archive 설정

Test서버는 Archive 설정이 않 되어 있어서 우선 alter system switch logfile; 을 3번정도 먼저 실행함.

SYS> Alter system switch logfile ;

System altered.

SYS> Alter system switch logfile ;

System altered.

SYS> Alter system switch logfile ;

System altered.

2.Hot backup 받기

현재 backup 상태확인!!
SYS>

SYS> SELECT * FROM V$BACKUP;

FILE# STATUS CHANGE# TIME

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

1 NOT ACTIVE 550922 2012-05-27 14:14:30

2 NOT ACTIVE 550943 2012-05-27 14:14:49

3 NOT ACTIVE 550928 2012-05-27 14:14:33

4 NOT ACTIVE 550937 2012-05-27 14:14:46

5 NOT ACTIVE 550948 2012-05-27 14:14:52

6 NOT ACTIVE 0

6 rows selected.
Tablespace 이름 별로 begin~end 를 걸어주어야 함!!

SYS> alter tablespace system begin backup;

Tablespace altered.

SYS> alter tablespace sysaux begin backup;

Tablespace altered.

SYS> alter tablespace users begin backup;

Tablespace altered.

SYS> alter tablespace undotbs1 begin backup;

Tablespace altered.

SYS> alter tablespace example begin backup;

Tablespace altered.

SYS> alter tablespace TS_AFS begin backup;

Tablespace altered.
컨트롤 파일과 HOT backup(데이터파일 리두로그 파일 이동) -> 현재 ACTIVE 상태확인! -> 파일 이동이 끝니면 모두 end 해주어야함.

SYS>

SYS> alter database backup controlfile to '/home/oracle/control01.ctl';

Database altered.

SYS>

SYS> SELECT * FROM V$BACKUP;

FILE# STATUS CHANGE# TIME

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

1 ACTIVE 551993 2012-05-27 14:29:55

2 ACTIVE 552010 2012-05-27 14:30:04

3 ACTIVE 551999 2012-05-27 14:29:58

4 ACTIVE 552004 2012-05-27 14:30:01

5 ACTIVE 552016 2012-05-27 14:30:08

6 ACTIVE 552022 2012-05-27 14:30:11

6 rows selected.
파일 이동 /u01/oradata/test

ocp@orcl : /u01/oradata/orcl> ls

arch1 control03.ctl redo02.log system01.dbf undotbs01.dbf

control01.ctl example01.dbf redo03.log temp01.dbf users01.dbf

control02.ctl redo01.log sysaux01.dbf ts_afs.dbf

ocp@orcl : /u01/oradata/orcl>

ocp@orcl : /u01/oradata/orcl>

ocp@orcl : /u01/oradata/orcl> cp *.log /u01/oradata/test

ocp@orcl : /u01/oradata/orcl>

ocp@orcl : /u01/oradata/orcl> cp *.dbf /u01/oradata/test

backup creuser.sql orcl_start.sh

ocp@orcl : /home/oracle>

ocp@orcl : /home/oracle> mv control* /u01/oradata/test

ocp@orcl : /home/oracle>

ocp@orcl : /home/oracle> cd /u01/oradata/test

ocp@orcl : /u01/oradata/test> ls

control01.ctl redo02.log system01.dbf undotbs01.dbf

example01.dbf redo03.log temp01.dbf users01.dbf

redo01.log sysaux01.dbf ts_afs.dbf

ocp@orcl : /u01/oradata/test>

ocp@orcl : /u01/oradata/test>
Hot backup을 완료.

SYS> SELECT * FROM V$BACKUP;

FILE# STATUS CHANGE# TIME

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

1 ACTIVE 551993 2012-05-27 14:29:55

2 ACTIVE 552010 2012-05-27 14:30:04

3 ACTIVE 551999 2012-05-27 14:29:58

4 ACTIVE 552004 2012-05-27 14:30:01

5 ACTIVE 552016 2012-05-27 14:30:08

6 ACTIVE 552022 2012-05-27 14:30:11

6 rows selected.

SYS> alter tablespace system end backup;

Tablespace altered.

SYS> SELECT * FROM V$BACKUP;

FILE# STATUS CHANGE# TIME

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

1 NOT ACTIVE 551993 2012-05-27 14:29:55

2 ACTIVE 552010 2012-05-27 14:30:04

3 ACTIVE 551999 2012-05-27 14:29:58

4 ACTIVE 552004 2012-05-27 14:30:01

5 ACTIVE 552016 2012-05-27 14:30:08

6 ACTIVE 552022 2012-05-27 14:30:11

6 rows selected.

SYS>

SYS> alter tablespace sysaux end backup;

Tablespace altered.

SYS> alter tablespace users end backup;

Tablespace altered.

SYS> alter tablespace undotbs1 end backup;

Tablespace altered.

SYS> alter tablespace example end backup;

Tablespace altered.

SYS> alter tablespace TS_AFS end backup;

Tablespace altered.

SYS> SELECT * FROM V$BACKUP;

FILE# STATUS CHANGE# TIME

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

1 NOT ACTIVE 551993 2012-05-27 14:29:55

2 NOT ACTIVE 552010 2012-05-27 14:30:04

3 NOT ACTIVE 551999 2012-05-27 14:29:58

4 NOT ACTIVE 552004 2012-05-27 14:30:01

5 NOT ACTIVE 552016 2012-05-27 14:30:08

6 NOT ACTIVE 552022 2012-05-27 14:30:11

6 rows selected.
로그파일 및 컨트롤 파일, 데이터파일 위치 확인

SYS>

SYS> select * from v$logfile;

GROUP# STATUS TYPE

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

MEMBER

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

IS_

---

3 ONLINE

/u01/oradata/orcl/redo03.log

NO

2 ONLINE

/u01/oradata/orcl/redo02.log

NO

1 ONLINE

/u01/oradata/orcl/redo01.log

NO

SYS> select member from v$logfile;

MEMBER

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

/u01/oradata/orcl/redo03.log

/u01/oradata/orcl/redo02.log

/u01/oradata/orcl/redo01.log

SYS> select * from v$controlfile;

STATUS

-------

NAME

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

IS_ BLOCK_SIZE FILE_SIZE_BLKS

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

/u01/oradata/orcl/control01.ctl

NO 16384 430

/u01/oradata/orcl/control02.ctl

NO 16384 430

/u01/oradata/orcl/control03.ctl

NO 16384 430

SYS>

SYS> select name from v$datafile;

NAME

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

/u01/oradata/orcl/system01.dbf

/u01/oradata/orcl/undotbs01.dbf

/u01/oradata/orcl/sysaux01.dbf

/u01/oradata/orcl/users01.dbf

/u01/oradata/orcl/example01.dbf

/u01/oradata/orcl/ts_afs.dbf

6 rows selected.
Scott 유저의 현재 데이터 상태확인

SYS> conn scott/tiger

Connected.

SCOTT> SCOTT>

SCOTT> select table_name from user_tables;

TABLE_NAME

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

DEPT

EMP

BONUS

SALGRADE

HOT backup 완료

3.Scott 유저에 데이터 추가

HOT backup recovery 전/후를 보기 위해서!!

SCOTT>

SCOTT> select table_name from user_tables;

TABLE_NAME

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

DEPT

EMP

BONUS

SALGRADE

REGIONS

LOCATIONS

DEPARTMENTS

JOBS

EMPLOYEES

JOB_HISTORY

JOB_GRADES

COUNTRIES

12 rows selected.

SCOTT>

4.Alter system switch logfile

SYS>

SYS> Alter system switch logfile ;

System altered.

SYS> Alter system switch logfile ;

System altered.

SYS>

해당 아카이브도 backup 후 같은 위치에 옮기기.(initSID.ora 설정 확인)

5.기존의 datafile들 삭제

ocp@orcl : /u01/oradata>

ocp@orcl : /u01/oradata> mv orcl orcl.bak

ocp@orcl : /u01/oradata> ls

badcmdfile.rcv flash_recovery_area goodcmdfile.rcv orcl.bak test

ocp@orcl : /u01/oradata>

ocp@orcl : /u01/oradata> cd test

ocp@orcl : /u01/oradata/test> ls

control01.ctl example01.dbf redo03.log temp01.dbf users01.dbf

control02.ctl redo01.log sysaux01.dbf ts_afs.dbf

control03.ctl redo02.log system01.dbf undotbs01.dbf

ocp@orcl : /u01/oradata/test>

6./u01/oradata/orcl->/u01/oradata/test로 복구

InitSID.ora 컨트롤 파일 및 아카이브 위치 변경

<변경 후>

*.control_files='/u01/oradata/orcl/control01.ctl','/u01/oradata/orcl/control02.ctl','/u01/oradata/orcl/control03.ctl'

<변경 전>

*.control_files='/u01/oradata/test/control01.ctl','/u01/oradata/test/control02.ctl','/u01/oradata/test/control03.ctl'

<아카이브 위치도 맞쳐주기>

*.log_archive_format='orcl_%t_%s_%r.arc'

*.log_archive_dest_1='location=/home/oracle/backup/archive'
Startup -> 에러 해결

SYS> startup

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1218316 bytes

Variable Size 75499764 bytes

Database Buffers 88080384 bytes

Redo Buffers bytes

Database mounted.

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SYS> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/u01/oradata/orcl/system01.dbf'
각 데이터파일의 위치를 재지정해줌.

SYS> ALTER DATABASE RENAME FILE '/u01/oradata/orcl/system01.dbf' to '/u01/oradataSYS> ALTER DATABASE RENAME FILE '/u01/oradata/orcl/system01.dbf' to '/u01/oradataALTER DATABASE RENAME FILE '/u01/oradata/orcl/system01.dbf' to '/u01/oradata/test/system01.dbf';

ALTER DATABASE RENAME FILE '/u01/oradata/orcl/sysaux01.dbf' to '/u01/oradata/testALTER DATABASE RENAME FILE '/u01/oradata/orcl/sysaux01.dbf' to '/u01/oradata/test/sysaux01.dbf';

ALTER DATABASE RENAME FILE '/u01/oradata/orcl/example01.dbf' to '/u01/oradata/tesALTER DATABASE RENAME FILE '/u01/oradata/orcl/example01.dbf' to '/u01/oradata/test/example01.dbf';

ALTER DATABASE RENAME FILE '/u01/oradata/orcl/temp01.dbf' to '/u01/oradata/test/tALTER DATABASE RENAME FILE '/u01/oradata/orcl/temp01.dbf' to '/u01/oradata/test/temp01.dbf';

ALTER DATABASE RENAME FILE '/u01/oradata/orcl/users01.dbf' to '/u01/oradata/test/ALTER DATABASE RENAME FILE '/u01/oradata/orcl/users01.dbf' to '/u01/oradata/test/users01.dbf';

ALTER DATABASE RENAME FILE '/u01/oradata/orcl/undotbs01.dbf' to '/u01/oradata/tesALTER DATABASE RENAME FILE '/u01/oradata/orcl/undotbs01.dbf' to '/u01/oradata/test/undotbs01.dbf';

ALTER DATABASE RENAME FILE '/u01/oradata/orcl/ts_afs.dbf' to '/u01/oradata/test/tALTER DATABASE RENAME FILE '/u01/oradata/orcl/ts_afs.dbf' to '/u01/oradata/test/ts_afs.dbf';

Database altered.

SYS> SYS>

ALTER DATABASE RENAME FILE '/u01/oradata/orcl/redo01.log' to '/u01/oradata/test/rALTER DATABASE RENAME FILE '/u01/oradata/orcl/redo01.log' to '/u01/oradata/test/redo01.log';

ALTER DATABASE RENAME FILE '/u01/oradata/orcl/redo02.log' to '/u01/oradata/test/rALTER DATABASE RENAME FILE '/u01/oradata/orcl/redo02.log' to '/u01/oradata/test/redo02.log';

ALTER DATABASE RENAME FILE '/u01/oradata/orcl/redo03.log' to '/u01/oradata/test/rALTER DATABASE RENAME FILE '/u01/oradata/orcl/redo03.log' to '/u01/oradata/test/redo03.log';

Database altered.

SYS> SYS>

Database altered.

SYS> SYS>

Database altered.

SYS> SYS>

Database altered.

SYS> SYS>

Database altered.

SYS> SYS>

Database altered.

SYS> SYS> SYS>

Database altered.

SYS> SYS>

Database altered.

SYS> SYS>

Database altered.
현재 상태확인 -> current 상태의 redolog 확인.

SYS> select * from v$backup;

FILE# STATUS CHANGE# TIME

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

1 ACTIVE 551993 2012-05-27 14:29:55

2 ACTIVE 552010 2012-05-27 14:30:04

3 ACTIVE 551999 2012-05-27 14:29:58

4 ACTIVE 552004 2012-05-27 14:30:01

5 ACTIVE 552016 2012-05-27 14:30:08

6 ACTIVE 552022 2012-05-27 14:30:11

6 rows selected.

SYS> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#

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

FIRST_TIME

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

1 1 17 10485760 1 YES INACTIVE 551980

2012-05-27 14:29:19

3 1 16 10485760 1 YES INACTIVE 551978

2012-05-27 14:29:18

2 1 18 10485760 1 NO CURRENT 551983

2012-05-27 14:29:25

SYS> select * from v$logfile;

GROUP# STATUS TYPE

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

MEMBER

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

IS_

---

3 ONLINE

/u01/oradata/test/redo03.log

NO

2 ONLINE

/u01/oradata/test/redo02.log

NO

1 ONLINE

/u01/oradata/test/redo01.log

NO
Recover database;

SYS> recover database using backup controlfile;

ORA-00279: change 551993 generated at 05/27/2012 14:29:55 needed for thread 1

ORA-00289: suggestion : /home/oracle/backup/archive/orcl_1_18_643126719.arc

ORA-00280: change 551993 for thread 1 is in sequence #18

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/u01/oradata/test/redo02.log

Log applied.

Media recovery complete.

SYS> alter database open resetlogs;

Database altered.

SYS>

SYS>

SYS> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS> startup

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1218316 bytes

Variable Size 75499764 bytes

Database Buffers 88080384 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

SYS>
Scott 유저 상태확인

SCOTT>

SCOTT> select table_name from user_tables;

TABLE_NAME

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

DEPT

EMP

BONUS

SALGRADE

SCOTT>

SCOTT>

SCOTT>
결론 : HOT backup 상태의 데이터가 들어가 있음.
발생된 에러들..
해당 에러는 TEST 서버에서 아카이브를 먼저 생성해 놓지 않고 Hot backup을 받은 뒤에 recovery를 하려고 했더니 해당 에러가 발생됨.
HOT backup을 하기전에 아카이브를 생성한 뒤(alter system switch logfile;) 백업을 받고 -> 데이터 변경 이후에 아카이브를 생성(alter system switch logfile;) 후 리커버리 -> hot backup 된 데이터 상태로 돌아감.

'1. IT Story > DB' 카테고리의 다른 글

Oracle RAC 11.2.0.3 with ASM / Vmware-Oracle 엔진 설치 및 ASM 구성 (0)	2013.02.09
Oracle RAC 11.2.0.3 with ASM / Vmware-리눅스설정2 (0)	2013.02.09
Oracle RAC 11.2.0.3 with ASM / Vmware-리눅스 설정1 (0)	2013.02.09
Oracle RAC 11.2.0.3 with ASM / Vmware-사전준비사항 (0)	2013.02.09
Oracle 10g Install -> Upgrade(10.2.0.4) -> DB설치(DBCA) -> Upgrad(10.2.0.5) [RHEL5] (0)	2012.03.26
Oracle 10g Install 설치 후(DBCA생성 전) -> Upgrade(10.2.0.4) -> DB설치(DBCA) [RHEL5] (0)	2012.03.26
Oracle 10g Install 설치 후(DBCA생성 전) 바로 Upgrade(10.2.0.4) [RHEL5] (0)	2012.03.26
Oracle 10g Install [RHEL5] (0)	2012.03.26