clone DB를 이용한 incomplete recovery 실습1 : user managed

● clone DB를 이용한 incomplete recovery 실습1

  ◎ User Managed

실습 순서

1. 실습을 위한 환경 설정
   ① whole backup
   ② 테이블 생성 -> 데이터 입력 -> 테이블 삭제 (recovery 확인용)
2. clone DB를 이용한 복구
   ① control file 및 parameter file 준비
   ② datafile backup, online redo log file, archive log file 복사
   ③ startup nomount -> control file 생성 -> 불완전 복구
   ④ clone DB에서 export -> devdb로 import
   ⑤ clone database 종료 및 삭제

    1. 실습을 위한 환경 설정 : ① whole backup       

OS] export ORACLE_SID=devdb
OS] sqlplus / as sysdba

-- 기존의 backup 및 archived log file 삭제
SQL> !rm -rf $ORACLE_BASE/oradata/devdb_backup
SQL> !rm -rf $ORACLE_BASE/oradata/devdb_arch

-- archive log file 위치를 다시 설정
SQL> !mkdir $ORACLE_BASE/oradata/devdb_arch
SQL> alter system set log_archive_dest_1 = 'location=$ORACLE_BASE/oradata/devdb_arch/';
SQL> alter system switch logfile;
SQL> !ls $ORACLE_BASE/oradata/devdb_arch

  1_2_706222899.dbf

-- 정상종료 및 whole backup
SQL> shutdown immediate
SQL> !mkdir $ORACLE_BASE/oradata/devdb_backup
SQL> !cp $ORACLE_BASE/oradata/devdb/* $ORACLE_BASE/oradata/devdb_backup
SQL> startup
SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
devdb 

    1. 실습을 위한 환경 설정 : ② 테이블 생성 -> 데이터 입력 -> 테이블 삭제 (recovery 확인용)

  SQL> drop table phil.t1 purge;

drop table phil.t1 purge
                *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> create table phil.t1 (no number);
SQL> insert into phil.t1 values (1000);
SQL> insert into phil.t1 values (2000);
SQL> commit;

SQL> select * from phil.t1;

        NO
----------
      1000
      2000

SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;

SQL> !date                         

  2009. 12. 21. (월) 22:56:38 KST            -- 반드시 확인할 것.

SQL> alter system switch logfile;
SQL> drop table phil.t1 purge;
SQL> select * from phil.t1;

select * from phil.t1
                   *
ERROR at line 1:
ORA-00942: table or view does not exist

    2. clone DB를 이용한 복구 : ① control file 및 parameter file 준비

   SQL> !mkdir $ORACLE_BASE/oradata/devdb_clone

  SQL> alter database backup controlfile to trace;                 -- control file을 재생성할 수 있는 스크립트를 생성.

SQL> select d.value||'/'||p.value||'_ora_'||s.spid||'.trc' as trace_file_name
     from (select value from v$parameter where name = 'instance_name') p,
          (select value from v$parameter where name = 'user_dump_dest') d,
          (select spid  from v$process   where addr = (select paddr from v$session
                                                       where sid = (select sid from v$mystat
                                                                    where rownum = 1))) s;

     TRACE_FILE_NAME
     --------------------------------------------------------------------
     /u01/app/oracle/product/10.2.0/db_1/rdbms/log/devdb_ora_24567.trc

-- 컨트롤 파일 생성을 위한 준비
SQL> !cp /u01/app/oracle/product/10.2.0/db_1/rdbms/log/devdb_ora_24567.trc $ORACLE_BASE/oradata/devdb_clone/control_file_create.sql

-- 파라미터 파일을 위한 준비
SQL> create pfile='$ORACLE_HOME/dbs/initC1.ora' from spfile;

SQL> !ls $ORACLE_BASE/oradata/devdb_clone

  control_file_create.sql 

-- 다음처럼 control file 생성을 위한 명령을 편집한다.
SQL> !vi $ORACLE_BASE/oradata/devdb_clone/control_file_create.sql

CREATE CONTROLFILE set DATABASE "C1" RESETLOGS  ARCHIVELOG    -- set 부분, DB 이름 부분
    MAXLOGFILES 16
    MAXLOGMEMBERS 2
    MAXDATAFILES 30
    MAXINSTANCES 1
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/u01/app/oracle/oradata/devdb_clone/redo01_a.log',       -- devdb_clone 경로
    '/u01/app/oracle/oradata/devdb_clone/redo01_b.log'
  ) SIZE 20M,
  GROUP 2 (
    '/u01/app/oracle/oradata/devdb_clone/redo02_a.log',
    '/u01/app/oracle/oradata/devdb_clone/redo02_b.log'
  ) SIZE 20M
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/devdb_clone/system01.dbf',
  '/u01/app/oracle/oradata/devdb_clone/undotbs01.dbf',
  '/u01/app/oracle/oradata/devdb_clone/sysaux01.dbf',
  '/u01/app/oracle/oradata/devdb_clone/undo01.dbf',
  '/u01/app/oracle/oradata/devdb_clone/user_tab_ts.dbf',
  '/u01/app/oracle/oradata/devdb_clone/user_ind_ts.dbf'
CHARACTER SET US7ASCII
;

위의 내용만 두고 아래쪽은 모두 삭제한다.

SQL> -- 다음처럼 pfile을 편집한다.
SQL> !vi $ORACLE_HOME/dbs/initC1.ora

*.compatible='10.2.0'
*.control_files='/u01/app/oracle/oradata/devdb_clone/control01.ctl'
*.db_block_size=4096
*.db_name='C1'
*.log_archive_dest_1='location=$ORACLE_BASE/oradata/devdb_clone/'
*.pga_aggregate_target=60M
*.sga_target=200M
*.undo_management='auto'
*.undo_tablespace='undotbs01'

    2. clone DB를 이용한 복구 : ② datafile backup, online redo log file, archive log file 복사

  -- 백업해둔 데이터 파일 복사
  SQL> !ls $ORACLE_BASE/oradata/devdb_clone

control_file_create.sql
SQL> !cp $ORACLE_BASE/oradata/devdb_backup/*.dbf $ORACLE_BASE/oradata/devdb_clone
SQL> !ls $ORACLE_BASE/oradata/devdb_clone

control_file_create.sql  sysaux01.dbf  system01.dbf  temp01.dbf  undo01.dbf  undotbs01.dbf  user_ind_ts.dbf  user_tab_ts.dbf

-- 사용중인 online redo log 파일 복사
SQL> !cp $ORACLE_BASE/oradata/devdb/*.log $ORACLE_BASE/oradata/devdb_clone
SQL> !ls $ORACLE_BASE/oradata/devdb_clone

control_file_create.sql  redo01_b.log  redo02_b.log  system01.dbf  undo01.dbf     user_ind_ts.dbf
redo01_a.log             redo02_a.log  sysaux01.dbf  temp01.dbf    undotbs01.dbf  user_tab_ts.dbf

-- archived redo log 파일 복사
SQL> !cp $ORACLE_BASE/oradata/devdb_arch/* $ORACLE_BASE/oradata/devdb_clone
SQL> !ls $ORACLE_BASE/oradata/devdb_clone

1_19_706222899.dbf  1_22_706222899.dbf  control_file_create.sql  redo02_a.log  system01.dbf  undotbs01.dbf
1_20_706222899.dbf  1_23_706222899.dbf  redo01_a.log             redo02_b.log  temp01.dbf    user_ind_ts.dbf
1_21_706222899.dbf  1_24_706222899.dbf  redo01_b.log             sysaux01.dbf  undo01.dbf    user_tab_ts.dbf

SQL> exit

    2. clone DB를 이용한 복구 : ③ startup nomount -> control file 생성 -> 불완전 복구

[oracle@ora10gr2 oradata]$ export ORACLE_SID=C1
[oracle@ora10gr2 oradata]$ sqlplus / as sysdba

SQL> startup nomount
SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
C1               STARTED

SQL> @ $ORACLE_BASE/oradata/devdb_clone/control_file_create.sql
SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
C1               MOUNTED

SQL> -- 위에서 확인했던 날짜와 시간을 잘 확인해서 아래 명령을 완성한다.
SQL> recover database until time '2009-12-21 22:56:38' using backup controlfile;

ORA-00279: change 273942 generated at 12/21/2009 21:22:11 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/oradata/devdb_clone/1_14_706222899.dbf
ORA-00280: change 273942 for thread 1 is in sequence #14

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
--> auto 입력

... 생략 ...

ORA-00308: cannot open archived log
'/u01/app/oracle/oradata/devdb_clone/1_19_706222899.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

SQL> -- online redo log 파일을 계속 적용해야 하므로 파일 이름을 확인해서 사용한다.
SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/devdb_clone/redo01_a.log
/u01/app/oracle/oradata/devdb_clone/redo01_b.log
/u01/app/oracle/oradata/devdb_clone/redo02_a.log
/u01/app/oracle/oradata/devdb_clone/redo02_b.log

SQL> recover database until time '2009-12-21 22:56:38' using backup controlfile;

ORA-00279: change 274060 generated at 12/21/2009 21:34:32 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/oradata/devdb_clone/1_19_706222899.dbf
ORA-00280: change 274060 for thread 1 is in sequence #19

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
--> /u01/app/oracle/oradata/devdb_clone/redo01_a.log 입력

SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
C1               MOUNTED

SQL> alter database open resetlogs;
SQL> select * from phil.t1;

        NO
----------
      1000
      2000

SQL> exit

    2. clone DB를 이용한 복구 : ④ clone DB에서 export -> devdb로 import

 

[oracle@ora10gr2 devdb_clone]$ exp phil/phil file=t1.dmp tables=t1

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

[oracle@ora10gr2 devdb_clone]$ export ORACLE_SID=devdb
[oracle@ora10gr2 devdb_clone]$ sqlplus phil/phil

SQL> select * from phil.t1;

select * from phil.t1
                   *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> ! imp phil/phil file=t1.dmp tables=t1;

SQL> select * from phil.t1;

        NO
----------
      1000
      2000

SQL> exit

    2. clone DB를 이용한 복구 : ⑤ clone database 종료 및 삭제

[oracle@ora10gr2 devdb_clone]$ cd
[oracle@ora10gr2 ~]$ export ORACLE_SID=C1
[oracle@ora10gr2 ~]$ sqlplus / as sysdba

SQL> shutdown abort
SQL> exit

[oracle@ora10gr2 ~]$ ls /u01/app/oracle/oradata
[oracle@ora10gr2 ~]$ rm -rf /u01/app/oracle/oradata/devdb_clone