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



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>

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 된 데이터 상태로 돌아감.

       

블로그 이미지

운명을바꾸는자

IT와 함께 살아가는 삶

,