'clone db'에 해당되는 글 1건

  1. 2012.08.08 clone DB를 이용한 incomplete recovery
ORACLE/Backup & Recovery2012. 8. 8. 11:10

clone DB를 이용한 incomplete recovery

반응형

● 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
반응형
저작자표시 비영리

'ORACLE > Backup & Recovery' 카테고리의 다른 글

오라클 duplicate (복제) 시에 에러  (0) 2023.05.23
RAC to clonedb(single) 복구  (2) 2014.08.15
복구에 필요한 정보 보기  (0) 2011.11.23
Clone DB를 이용한 복구  (0) 2011.08.31
Clone Database를 이용한 복구 (RMAN을 이용할 경우)  (0) 2011.08.31
Posted by [PineTree]

티스토리툴바