불완전복구 시나리오 : 잘못된 tbs삭제를 복구하기(백업, 현재 어느 control file에도 삭제해버린 tbs의 정보가 없을때)

Oracle/백업&복구

불완전복구 시나리오 : 잘못된 tbs삭제를 복구하기(백업, 현재 어느 control file에도 삭제해버린 tbs의 정보가 없을때)

에몽이ㅋ 2012. 2. 6. 18:39

즉, 백업 후에 생성한 tablespace가 삭제된 경우에 복구하는 방법입니다.
(drop tablespace 명령어를 실행하면, 바로 controlfile에서 해당 tbs정보를 삭제하기때문에, 현재 control file도 사용할 수 없습니다.)

불완전복구 데모시나리오 2 : 잘못된 tbs삭제를 복구하기

시나리오 순서

1. 백업

2. tbs생성(TEST, test01.dbf) 후 자료 입력 한 후 commit 후 시간확인

3. 현재 DB의 파일 경로들을 살펴보기

4. log switch 몇번 후 datafile삭제

문제 : 백업된 control file에 생성된 tbs정보가 없음

5. shutdown 한 후 필요한 자료들을 임시경로에 restore하기

(백업된 데이터파일, 현재 redolog, 백업된 controlfile)

6. parameter에서 controlfile의 경로를 새로 지정하고 mount단계로 가서 alter database rename하기(복사하지 않는 자료들은 offline drop)

7. 6번적용되었는지 확인하기

8. 복구시도 후 복구가 되었으면, open 후 자료가 살아났나확인하기

1. 백업

SQL> !sh ~/main_backup.sh

set begin backup mode~~

real    0m0.572s
user    0m0.027s
sys     0m0.051s

end begin backup mode--

start file copy.…

real    0m34.695s
user    0m0.089s
sys     0m5.854s
end file copy~

set end backup mode~~

real    0m1.164s
user    0m0.030s
sys     0m0.041s
complete hot backup~!!

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

2. tbs생성(TEST, test01.dbf) 후 자료 입력 한 후 commit 후 시간확인

SQL> create tablespace TEST
  2  datafile '/home/oracle/oradata/testdb/test01.dbf' size 5M;

Tablespace created.

SQL> create table scott.t02
  2  (no number)
  3  tablespace TEST;

Table created.

SQL> insert into scott.t02 values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from scott.t02;

        NO
----------
         1

SQL> select to_char(sysdate,'rrrr-mm-dd:hh24:mi:ss') from dual;

SYSDATE
-------------------
2012-02-06:17:59:11

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

3. 현재 DB의 파일 경로들을 살펴보기

SQL> l
  1* select tablespace_name, file_name from dba_data_files

TABLESPACE_NAME                FILE_NAME
------------------------------ ---------------------------------------------
TEST                           /home/oracle/oradata/testdb/test01.dbf
EXAMPLE                        /home/oracle/oradata/testdb/example01.dbf
USERS                          /home/oracle/oradata/testdb/users01.dbf
SYSAUX                         /home/oracle/oradata/testdb/sysaux01.dbf
UNDOTBS1                       /home/oracle/oradata/testdb/undotbs01.dbf
SYSTEM                         /home/oracle/oradata/testdb/system01.dbf

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/home/oracle/oradata/testdb/redo03.log
/home/oracle/oradata/testdb/redo02.log
/home/oracle/oradata/testdb/redo01.log

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/home/oracle/oradata/testdb/control01.ctl


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

4. log switch 몇번 후 datafile삭제
문제 : 백업된 control file에 생성된 tbs정보가 없음

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL>
SQL> drop tablespace test;
**********실수로 tbs지우는 에러 발생**********

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

5. shutdown 한 후 필요한 자료들을 임시경로에 restore하기
(백업된 데이터파일, 현재 redolog, 백업된 controlfile)
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !mkdir ~/temp

SQL> !ls /backup/open/2012-02-06-17-56-22
2012-02-06-17-56-22.ctl  example01.dbf  sysaux01.dbf  system01.dbf  undotbs01.dbf  users01.dbf

SQL> !cp -av /backup/open/2012-02-06-17-56-22/sys* ~/temp/
`/backup/open/2012-02-06-17-56-22/sysaux01.dbf' -> `/home/oracle/temp/sysaux01.dbf'
`/backup/open/2012-02-06-17-56-22/system01.dbf' -> `/home/oracle/temp/system01.dbf'

SQL> !cp -av /backup/open/2012-02-06-17-56-22/undo* ~/temp/
`/backup/open/2012-02-06-17-56-22/undotbs01.dbf' -> `/home/oracle/temp/undotbs01.dbf'

SQL> !cp -av ~/oradata/testdb/*.log ~/temp/
`/home/oracle/oradata/testdb/redo01.log' -> `/home/oracle/temp/redo01.log'
`/home/oracle/oradata/testdb/redo02.log' -> `/home/oracle/temp/redo02.log'
`/home/oracle/oradata/testdb/redo03.log' -> `/home/oracle/temp/redo03.log'

SQL> !cp -av /backup/open/2012-02-06-17-56-22/*.ctl ~/temp/control01.ctl
`/backup/open/2012-02-06-17-56-22/2012-02-06-17-56-22.ctl' -> `/home/oracle/temp/control01.ctl'

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

6. parameter에서 controlfile의 경로를 새로 지정하고 mount단계로 가서 alter database rename하기
(복사하지 않는 자료들은 offline drop)

SQL> !vi $ORACLE_HOME/dbs/inittestdb.ora    <<
*.control_files='/home/oracle/temp/control01.ctl'

SQL> startup mount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1272600 bytes
Variable Size              96470248 bytes
Database Buffers           67108864 bytes
Redo Buffers                2920448 bytes
Database mounted.


SQL> select name from v$controlfile;

NAME
------------------------------------------------------------------------------------------------------------------------
/home/oracle/temp/control01.ctl

SQL> alter database rename file '/home/oracle/oradata/testdb/system01.dbf' to '/home/oracle/temp/system01.dbf';
SQL> alter database rename file '/home/oracle/oradata/testdb/sysaux01.dbf' to '/home/oracle/temp/sysaux01.dbf';
SQL> alter database rename file '/home/oracle/oradata/testdb/undotbs01.dbf' to '/home/oracle/temp/undotbs01.dbf';
SQL> alter database datafile '/home/oracle/oradata/testdb/users01.dbf' offline drop;
SQL> alter database datafile '/home/oracle/oradata/testdb/example01.dbf' offline drop;

Database altered.

SQL> alter database rename file '/home/oracle/oradata/testdb/redo01.log' to '/home/oracle/temp/redo01.log';
SQL> alter database rename file '/home/oracle/oradata/testdb/redo02.log' to '/home/oracle/temp/redo02.log';
SQL> alter database rename file '/home/oracle/oradata/testdb/redo03.log' to '/home/oracle/temp/redo03.log';

Database altered.

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

7. 6번적용되었는지 확인하기

SQL> l
  1  select b.NAME tbs_name, a.NAME data_file
  2  from v$datafile a, v$tablespace b
  3* where a.ts# = b.ts#
SQL> /

TBS_NAME                       DATA_FILE
------------------------------ ---------------------------------------------
SYSTEM                         /home/oracle/temp/system01.dbf
UNDOTBS1                       /home/oracle/temp/undotbs01.dbf
SYSAUX                         /home/oracle/temp/sysaux01.dbf
USERS                          /home/oracle/oradata/testdb/users01.dbf
EXAMPLE                        /home/oracle/oradata/testdb/example01.dbf

SQL> select member from v$logfile;

MEMBER
----------------------------------------------------------------------
/home/oracle/temp/redo03.log
/home/oracle/temp/redo02.log
/home/oracle/temp/redo01.log

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

8. 복구시도
************************
1) 없는 tablespace를 control file에 적용시키기 위해서 recover명령어를 써주기
SQL> recover database until time '2012-02-06:17:59:11' using backup controlfile;  <<
ORA-00279: change 741226 generated at 02/06/2012 17:56:22 needed for thread 1
ORA-00289: suggestion : /backup/arc/1_12_774410678.dbf
ORA-00280: change 741226 for thread 1 is in sequence #12


Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 6: '/home/oracle/oradata/testdb/test01.dbf'


ORA-01112: media recovery not started


SQL> select b.NAME tbs_name, a.NAME data_file
  2   from v$datafile a, v$tablespace b
  3  where a.ts# = b.ts#
  4  ;

TBS_NAME                       DATA_FILE
------------------------------ ---------------------------------------------
SYSTEM                         /home/oracle/temp/system01.dbf
UNDOTBS1                       /home/oracle/temp/undotbs01.dbf
SYSAUX                         /home/oracle/temp/sysaux01.dbf
USERS                          /home/oracle/oradata/testdb/users01.dbf
EXAMPLE                        /home/oracle/oradata/testdb/example01.dbf
TEST                           /home/oracle/product/10g/dbs/UNNAMED00006  <<

6 rows selected.
(recover 시도 후에 새로운 tbs의 list가 control file에 적용되었음을 확인)
************************

2) 작업을 복구할 새로운 파일의 경로 지정해주기
SQL> alter database create datafile '/home/oracle/product/10g/dbs/UNNAMED00006'
  2  as '/home/oracle/temp/test01.dbf';  <<

Database altered.
************************

3) 다시 복구시도
SQL> recover database until time '2012-02-06:17:59:11' using backup controlfile;  <<
ORA-00279: change 741328 generated at 02/06/2012 17:58:08 needed for thread 1
ORA-00289: suggestion : /backup/arc/1_12_774410678.dbf
ORA-00280: change 741328 for thread 1 is in sequence #12


Specify log: {=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
************************

4) open후 자료 확인
SQL> alter database open resetlogs;

Database altered.

SQL> select * from scott.t02;

        NO
----------
         1

SQL> select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME                FILE_NAME
------------------------------ ---------------------------------------------
TEST                           /home/oracle/temp/test01.dbf  <--- 복구확인
EXAMPLE                        /home/oracle/oradata/testdb/example01.dbf
USERS                          /home/oracle/oradata/testdb/users01.dbf
SYSAUX                         /home/oracle/temp/sysaux01.dbf
UNDOTBS1                       /home/oracle/temp/undotbs01.dbf
SYSTEM                         /home/oracle/temp/system01.dbf

6 rows selected.

*위 방법에서 포인트는 control file에 없던 TBS 정보를 어떻게 생성해주냐? 가 포인트입니다.(8. 복구시도)

저작자표시 비영리 동일조건

'Oracle > 백업&복구' 카테고리의 다른 글

2012.02.08 백업&복구 5번째(undo tablespace 장애 복구하기) ; undo 장애처리의 핵심설명, 예제2개 (0)	2012.02.12
복구시나리오 종합(drop tablespace장애(백업 전 생성, 백업 후 생성), 사용자 실수로 지운 drop table 복구 (logmnr error 포함) (2)	2012.02.08
recover database using backup controlfile until cancel; 명령어관련 (0)	2012.02.05
복구시 Clone DB만드는법, 만드는 이유, export, import 하는법 (0)	2012.02.03
복구 후 Temp Tablespace 사용하도록 지정하는 법 (0)	2012.02.03

현재글불완전복구 시나리오 : 잘못된 tbs삭제를 복구하기(백업, 현재 어느 control file에도 삭제해버린 tbs의 정보가 없을때)

에몽이's ORACLE!?

제 글이 도움되셨다면 덧글 남겨주고 가세요 ^^ 문의사항은 gyh214@naver.com <pre class="brush:sql"></pre>

리눅스, Constraint, tnsnames.ora, clone DB, ASM, flashback, oracle_network, oracle11g, rman, SQL, Solaris, oui, oel, raw device, Silent, listener.ora, datapump, EXCEPTIONS table, 노드추가, RAC,

Today :
Yesterday :

에몽이's ORACLE!?