CLONEDB 만들어서 논리적인 장애 복구하기(No archive log mode)

포스팅의 목적은 
만약에 DB가 운영중이고, shutdown을 못하는 상황인데, No archivelog mode 운영이라면 어떻게 논리적인 장애를 복구할까?  
(log switch 가 자주 일어나지 않는 환경이라고 가정하겠습니다)

(TESTDB : 원본DB, CLONE : 복제DB)

(http://gyh214.tistory.com/92 << cloneDB만드는법 포스팅)

포인트 :
Archive log mode경우는, 현재 log file을 Archive logfile로 떨어뜨려서 CLONEDB에서 archive logfile을 복구할때 사용하면 됩니다.
하지만, No archive log mode는 그것이 불가능하므로, 불완전복구할때, 현재DB의 redo logfile을 사용(글 3-2부분참조하세요)
(TEST환경이라서 여기선 그냥 해본 것이지만, 실제DB환경이라면, 아래 방법은 쓰시면 안됩니다.,
두군데에서 동시에 log file에 access하기때문에 문제가 발생할 수도 있습니다.) 

1. 상황확인

 
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /backup/arc
Oldest online log sequence     4
Current log sequence           6

SQL> select count(*) from scott.test01;

  COUNT(*)
----------
   2097152

SQL> select TABLE_NAME, TABLESPACE_NAME, OWNER from dba_tables
  2  where table_name='TEST01' and OWNER='SCOTT';

TABLE_NAME                     TABLESPACE_NAME                OWNER
------------------------------ ------------------------------ ------------------------------
TEST01                         TEST                           SCOTT

SQL> drop table scott.test01 purge;   << 논리적인 장애발생(실수로 테이블삭제)

Table dropped.

SQL> select * from scott.test01;
select * from scott.test01
                    *
ERROR at line 1:
ORA-00942: table or view does not exist  << 당연히 없습니다.

2. clonedb 만들기(clonedb의 모든 파일들을 /home/oracle/clone 안에 넣겠습니다.)

2-1. 파라미터파일 수정(spfile운영중이라면 pfile 만드신 후 clone 작업해주세요)

원래 파라미터 파일을 열어서

db_name=clone

control_files='/home/oracle/clone/control01.ctl'

로 수정하시고

**반드시 :wq! initclone.ora 로 저장하세요

 2-2. clonedb용 컨트롤파일생성스크립트 만들기

운영중인 DB에서

SQL> alter database backup controlfile to trace as '/home/oracle/clone/ctl.sql';

Database altered.

!vi /home/oracle/clone/ctl.sql
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "CLONE" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 1314
LOGFILE
  GROUP 1 (
    '/home/oracle/clone/redo01_a.log'
  ) SIZE 10M,
  GROUP 2 (
    '/home/oracle/clone/redo02_a.log'
  ) SIZE 10M,
  GROUP 3 (
    '/home/oracle/clone/redo03_a.log'
  ) SIZE 10M
DATAFILE
  '/home/oracle/clone/system01.dbf',
  '/home/oracle/clone/undo01.dbf',
  '/home/oracle/clone/sysaux01.dbf',
  '/home/oracle/clone/users01.dbf',
  '/home/oracle/clone/example01.dbf',
  '/home/oracle/clone/test01.dbf'
CHARACTER SET KO16MSWIN949

윗 부분만 남기고 다 삭제하신 후 수정하시고 저장
(REUSE --> SET으로 변경, "원DB이름" --> "CLONE"으로 변경)

2-3. backup된 datafile들을 /home/oracle/clone으로 복사

 cp -v /backup/*.dbf  ~/clone/

`/backup/example01.dbf' -> `/home/oracle/clone/example01.dbf'

`/backup/sysaux01.dbf' -> `/home/oracle/clone/sysaux01.dbf'

`/backup/system01.dbf' -> `/home/oracle/clone/system01.dbf'

`/backup/test01.dbf' -> `/home/oracle/clone/test01.dbf'

`/backup/undo01.dbf' -> `/home/oracle/clone/undo01.dbf'

`/backup/users01.dbf' -> `/home/oracle/clone/users01.dbf'

2-4. (여기서 부터는 새 터미널창입니다)

새 접속(새 터미널창)오픈하신 후, oracle 로그인하시고

export ORACLE_SID=clone

반드시 입력하세요.

login as: oracle

oracle@192.168.0.15's password:

Last login: Wed Feb 15 04:47:49 2012 from 192.168.0.10

[oracle@server15 ~]$ export ORACLE_SID=clone

[oracle@server15 ~]$ echo $ORACLE_SID

clone

2-5. 클론db에 컨트롤파일 생성하면서 mount까지 올라가기

SQL> @/home/oracle/clone/ctl
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1272600 bytes
Variable Size              62915816 bytes
Database Buffers          100663296 bytes
Redo Buffers                2920448 bytes

Control file created.

SQL> select name from v$database;

NAME
---------
CLONE

SQL> select status from v$instance;

STATUS
------------
MOUNTED

3. 원하는 자료 CLONEDB에 복구하기

3-1. 복구하기를 원하는 시간 찾아내기(logmnr 이용)

(3-1 작업은 원래DB에서 실행하세요)

(http://gyh214.tistory.com/102 참조)

SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME
--------
YES  << 다행히 YES입니다, NO면 못찾아낼 가능성이 높죠

SQL> show parameter utl

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines               string
utl_file_dir                         string      /backup/logmnr

SQL> exec dbms_logmnr_d.build('log.dat','/backup/logmnr'); << dictionary file 생성
PL/SQL procedure successfully completed.

SQL> @log

    GROUP# MEMBER                                         SEQUENCE# ARC STATUS
---------- --------------------------------------------- ---------- --- ----------------
         1 /home/oracle/oradata/testdb/redo01_a.log               4 YES INACTIVE
         1 /home/oracle/oradata/testdb/redo01_b.log               4 YES INACTIVE
         2 /home/oracle/oradata/testdb/redo02_a.log               5 YES INACTIVE
         2 /home/oracle/oradata/testdb/redo02_b.log               5 YES INACTIVE
         3 /home/oracle/oradata/testdb/redo03_a.log               6 NO  CURRENT
         3 /home/oracle/oradata/testdb/redo03_b.log               6 NO  CURRENT

6 rows selected.

SQL> exec dbms_logmnr.add_logfile('/home/oracle/oradata/testdb/redo01_a.log',1);
SQL> exec dbms_logmnr.add_logfile('/home/oracle/oradata/testdb/redo02_a.log',3);
SQL>  exec dbms_logmnr.add_logfile('/home/oracle/oradata/testdb/redo03_a.log',3);

PL/SQL procedure successfully completed.  << 현재 redolog 파일등록

SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'/backup/logmnr/log.dat');  << 분석하기

PL/SQL procedure successfully completed.

SQL> select TIMESTAMP, SQL_REDO, TABLE_NAME from v$logmnr_contents
  2  where table_name='TEST01';

TIMESTAMP           SQL_REDO                                      TABLE_NAME
------------------- --------------------------------------------- --------------------------------
2012-02-15:05:02:52 drop table scott.test01 purge;                TEST01
<< 찾았습니다. 2012-02-15:05:02:52 이전으로 돌리면 됩니다.

3-2. 찾아낸 시간으로 복구하기(cloneDB창에서하세요)

** 주의사항 : drop된지 얼마 지나지 않아서, log switch가 발생하지 않아서 어떻게든

현재 redo logfile안에 drop되기 이전 시점이 있다고 가정하겠습니다.

--> recover할때 파일이름을 원래 DB의 redo log file을 적어주세요

SQL> select name from v$database;

NAME
---------
CLONE

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> recover database until time '2012-02-15:05:02:51' using backup controlfile;
ORA-00279: change 884525 generated at 02/15/2012 04:58:46 needed for thread 1
ORA-00289: suggestion : /backup/arc/1_6_775131867.dbf
ORA-00280: change 884525 for thread 1 is in sequence #6


Specify log: {=suggested | filename | AUTO | CANCEL}
/home/oracle/oradata/testdb/redo01_a.log   << 원래 DB에서 seq# 4에 해당하는redolog
ORA-00310: archived log contains sequence 4; sequence 6 required << seq# 6이 필요하다고 요청합니다.
ORA-00334: archived log: '/home/oracle/oradata/testdb/redo01_a.log'


SQL> recover database until time '2012-02-15:05:02:51' using backup controlfile;
ORA-00279: change 884525 generated at 02/15/2012 04:58:46 needed for thread 1
ORA-00289: suggestion : /backup/arc/1_6_775131867.dbf
ORA-00280: change 884525 for thread 1 is in sequence #6


Specify log: {=suggested | filename | AUTO | CANCEL}
/home/oracle/oradata/testdb/redo03_a.log   
                       << 다시 recover실행 후 seq#6에 해당하는 logfile을 적어줍니다.
Log applied.
Media recovery complete.

SQL> @log  << 원래DB의 redo log를 초기화해버리면 안되므로, 마지막으로 확인하고 open하세요

    GROUP# MEMBER                                         SEQUENCE# ARC STATUS
---------- --------------------------------------------- ---------- --- ----------------
         1 /home/oracle/clone/redo01_a.log                        0 YES UNUSED
         2 /home/oracle/clone/redo02_a.log                        0 YES UNUSED
         3 /home/oracle/clone/redo03_a.log                        0 YES CURRENT

SQL> alter database open resetlogs;


SQL> select count(*) from scott.test01;

  COUNT(*)
----------
   2097152

4. 찾아낸 자료 원DB에 부어주기

4-1. export하기(cloneDB창에서 실행하세요)

SQL> !
[oracle@server15 clone]$ exp scott/tiger file='/home/oracle/clone/test01.dmp' tables=test01

Export: Release 10.2.0.5.0 - Production on Wed Feb 15 05:58:43 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in KO16MSWIN949 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                         TEST01     2097152 rows exported
Export terminated successfully without warnings.

4-2. import하기(원래 DB에서 실행하세요)

SQL> select name from v$database;

NAME
---------
TESTDB

SQL> !
[oracle@server15 ~]$
[oracle@server15 ~]$ imp scott/tiger file=/home/oracle/clone/test01.dmp ignore=y

Import: Release 10.2.0.5.0 - Production on Wed Feb 15 06:00:14 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
import done in KO16MSWIN949 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table                       "TEST01"     2097152 rows imported
Import terminated successfully without warnings.
 

5. 확인하기

SQL> select name from v$database;

NAME
---------
TESTDB

SQL> select count(*) from scott.test01;

  COUNT(*)
----------
    2097152

복구완료 : 어때요, 참 쉽죠?

결론은, 백업datafile을 이용해서 clonedb를 하나 만들고, recover할 때, redo log file을 사용하면 됩니다.
(물론 no archive log mode인데, log switch가 나버려서 덮어쓰여져 버리면 복구는 불가능하게 되겠죠)